-- terms_notes: default terms & conditions / notes per document type.
-- One row per type (PO, INVOICE, ...). Idempotent.

BEGIN;

CREATE TABLE IF NOT EXISTS terms_notes (
  id         BIGSERIAL PRIMARY KEY,
  type       VARCHAR(30) NOT NULL,
  notes      TEXT NOT NULL,
  is_active  BOOLEAN NOT NULL DEFAULT TRUE,
  created_by BIGINT,
  updated_by BIGINT,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  deleted_at TIMESTAMPTZ
);

CREATE UNIQUE INDEX IF NOT EXISTS terms_notes_type_key ON terms_notes(type);

ALTER TABLE terms_notes DROP CONSTRAINT IF EXISTS terms_notes_type_check;
ALTER TABLE terms_notes
  ADD CONSTRAINT terms_notes_type_check
  CHECK (type IN ('PO', 'INVOICE'));

ALTER TABLE terms_notes DROP CONSTRAINT IF EXISTS fk_terms_notes_created_by;
ALTER TABLE terms_notes
  ADD CONSTRAINT fk_terms_notes_created_by FOREIGN KEY (created_by) REFERENCES users(id);
ALTER TABLE terms_notes DROP CONSTRAINT IF EXISTS fk_terms_notes_updated_by;
ALTER TABLE terms_notes
  ADD CONSTRAINT fk_terms_notes_updated_by FOREIGN KEY (updated_by) REFERENCES users(id);

COMMIT;

-- Verify:
-- SELECT * FROM terms_notes;