-- Meshkee CMS — invoice / template payment methods (نحوه ی پرداخت)

-- ---------------------------------------------------------------------------
-- template payment methods (duplicatable free-text)
-- ---------------------------------------------------------------------------
CREATE TABLE invoice_template_payment_methods (
  id           BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  template_id  BIGINT NOT NULL,
  text         TEXT NOT NULL,
  sort_order   INT NOT NULL DEFAULT 0,
  created_at   TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at   TIMESTAMPTZ NOT NULL DEFAULT NOW(),

  CONSTRAINT invoice_template_payment_methods_template_id_fkey
    FOREIGN KEY (template_id) REFERENCES invoice_templates (id) ON DELETE CASCADE,
  CONSTRAINT invoice_template_payment_methods_text_nonempty
    CHECK (char_length(trim(text)) > 0)
);

CREATE INDEX idx_invoice_template_payment_methods_template_id
  ON invoice_template_payment_methods (template_id, sort_order);

CREATE TRIGGER invoice_template_payment_methods_set_updated_at
  BEFORE UPDATE ON invoice_template_payment_methods
  FOR EACH ROW
  EXECUTE FUNCTION set_updated_at();

-- ---------------------------------------------------------------------------
-- invoice payment methods (duplicatable free-text)
-- ---------------------------------------------------------------------------
CREATE TABLE invoice_payment_methods (
  id           BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  invoice_id   BIGINT NOT NULL,
  text         TEXT NOT NULL,
  sort_order   INT NOT NULL DEFAULT 0,
  created_at   TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at   TIMESTAMPTZ NOT NULL DEFAULT NOW(),

  CONSTRAINT invoice_payment_methods_invoice_id_fkey
    FOREIGN KEY (invoice_id) REFERENCES invoices (id) ON DELETE CASCADE,
  CONSTRAINT invoice_payment_methods_text_nonempty
    CHECK (char_length(trim(text)) > 0)
);

CREATE INDEX idx_invoice_payment_methods_invoice_id
  ON invoice_payment_methods (invoice_id, sort_order);

CREATE TRIGGER invoice_payment_methods_set_updated_at
  BEFORE UPDATE ON invoice_payment_methods
  FOR EACH ROW
  EXECUTE FUNCTION set_updated_at();