beteran-server-service/migrations/202207011110_member_bank_deposit/up.sql

CREATE TYPE member_bank_deposit_state AS ENUM ('application', 'pending', 'complete');


CREATE TABLE IF NOT EXISTS member_bank_deposits (
  id                UUID DEFAULT uuid_generate_v4(),
  member_id         UUID NOT NULL,
  name              TEXT NOT NULL,
  amount            DOUBLE PRECISION NOT NULL,
  memo              TEXT,
  state             MEMBER_BANK_DEPOSIT_STATE DEFAULT 'application',
  state_changed_at  BIGINT NOT NULL DEFAULT (extract(epoch from now()) * 1000),
  created_at        BIGINT NOT NULL DEFAULT (extract(epoch from now()) * 1000),
  updated_at        BIGINT NOT NULL DEFAULT (extract(epoch from now()) * 1000),
  PRIMARY KEY (id),
  CONSTRAINT fk_member_bank_deposits_member_id
    FOREIGN KEY(member_id) 
	    REFERENCES members(id)

);

CREATE INDEX idx_member_bank_deposits_member_id ON member_bank_deposits (member_id);

-- trigger (updated_at)
CREATE TRIGGER tg_member_bank_deposits_updated_at
  BEFORE UPDATE
  ON member_bank_deposits
  FOR EACH ROW
  EXECUTE PROCEDURE update_updated_at_column();

-- trigger (state_changed_at)
CREATE TRIGGER tg_member_bank_deposits_state_changed_at
  BEFORE UPDATE
  ON member_bank_deposits
  FOR EACH ROW
  EXECUTE PROCEDURE update_state_changed_at_column();