-- RFQ Core Schema
-- Date: 2025-09-26

-- Enable pgcrypto for gen_random_uuid (safe if already enabled)
DO $$ BEGIN
  CREATE EXTENSION IF NOT EXISTS pgcrypto;
EXCEPTION WHEN others THEN
  -- ignore in case of limited permissions
  NULL;
END $$;

-- Headers
CREATE TABLE IF NOT EXISTS rfq_headers (
  rfq_id              uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  org_id              uuid NULL,
  department_id       uuid NULL,
  type                text NOT NULL CHECK (type IN ('RFQ','EOI')),
  rfq_number          text NULL,
  numbering_format    text NULL,
  title               text NOT NULL,
  category            text NULL,
  currency            text NULL,
  incoterms           text NULL,
  summary_markdown    text NULL,
  summary_html        text NULL,
  publish_at          timestamptz NULL,
  due_at              timestamptz NOT NULL,
  status              text NOT NULL DEFAULT 'draft' CHECK (status IN ('draft','open','paused','closed','awarded','archived')),
  created_by          uuid NULL,
  created_at          timestamptz NOT NULL DEFAULT now(),
  updated_at          timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX IF NOT EXISTS idx_rfq_headers_status_due ON rfq_headers(status, due_at);
CREATE INDEX IF NOT EXISTS idx_rfq_headers_org_type ON rfq_headers(org_id, type);
CREATE UNIQUE INDEX IF NOT EXISTS uq_rfq_org_number ON rfq_headers(org_id, rfq_number) WHERE rfq_number IS NOT NULL;

-- Sections (instantiated from templates)
CREATE TABLE IF NOT EXISTS rfq_sections (
  rfq_section_id    uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  rfq_id            uuid NOT NULL REFERENCES rfq_headers(rfq_id) ON DELETE CASCADE,
  section_key       text NULL,
  heading           text NULL,
  body_markdown     text NULL,
  body_html         text NULL,
  sort_order        int  NOT NULL DEFAULT 0,
  include_in_print  boolean NOT NULL DEFAULT true,
  include_in_email  boolean NOT NULL DEFAULT true,
  created_at        timestamptz NOT NULL DEFAULT now(),
  updated_at        timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX IF NOT EXISTS idx_rfq_sections_rfq ON rfq_sections(rfq_id);

-- Attachments at RFQ instance level
CREATE TABLE IF NOT EXISTS rfq_attachments (
  rfq_attachment_id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  rfq_id            uuid NOT NULL REFERENCES rfq_headers(rfq_id) ON DELETE CASCADE,
  label             text NULL,
  filetype          text NULL,
  file_url          text NULL,
  include_when      text NOT NULL DEFAULT 'always',
  sort_order        int  NOT NULL DEFAULT 0,
  created_at        timestamptz NOT NULL DEFAULT now(),
  updated_at        timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX IF NOT EXISTS idx_rfq_attachments_rfq ON rfq_attachments(rfq_id);

-- Items
CREATE TABLE IF NOT EXISTS rfq_items (
  id                 bigserial PRIMARY KEY,
  rfq_id             uuid NOT NULL REFERENCES rfq_headers(rfq_id) ON DELETE CASCADE,
  description        text NOT NULL,
  quantity           numeric(14,4) NULL,
  uom                text NULL,
  required_specs     jsonb NULL,
  delivery_terms     text NULL,
  delivery_location  text NULL,
  is_optional        boolean NOT NULL DEFAULT false,
  item_attachments   jsonb NULL,
  created_at         timestamptz NOT NULL DEFAULT now(),
  updated_at         timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX IF NOT EXISTS idx_rfq_items_rfq ON rfq_items(rfq_id);
CREATE INDEX IF NOT EXISTS idx_rfq_items_specs ON rfq_items USING GIN(required_specs);

-- Invites per vendor
CREATE TABLE IF NOT EXISTS rfq_invites (
  invite_id        uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  rfq_id           uuid NOT NULL REFERENCES rfq_headers(rfq_id) ON DELETE CASCADE,
  vendor_id        uuid NOT NULL REFERENCES vendors(vendor_id) ON DELETE CASCADE,
  contact_id       uuid NULL REFERENCES vendor_contacts(vendor_contact_id) ON DELETE SET NULL,
  vendor_user_id   uuid NULL REFERENCES users(user_id) ON DELETE SET NULL,
  channel          text NULL,
  status           text NOT NULL DEFAULT 'invited' CHECK (status IN ('invited','delivered','opened','responded','declined')),
  sent_at          timestamptz NULL,
  delivered_at     timestamptz NULL,
  opened_at        timestamptz NULL,
  responded_at     timestamptz NULL,
  declined_reason  text NULL,
  reminder_count   int NOT NULL DEFAULT 0,
  last_reminder_at timestamptz NULL,
  created_at       timestamptz NOT NULL DEFAULT now()
);

CREATE UNIQUE INDEX IF NOT EXISTS uq_rfq_invite_unique ON rfq_invites(rfq_id, vendor_id);
CREATE INDEX IF NOT EXISTS idx_rfq_invites_status ON rfq_invites(rfq_id, status);

-- Vendor quotes (header)
CREATE TABLE IF NOT EXISTS vendor_quotes (
  quote_id       uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  rfq_id         uuid NOT NULL REFERENCES rfq_headers(rfq_id) ON DELETE CASCADE,
  invite_id      uuid NULL REFERENCES rfq_invites(invite_id) ON DELETE SET NULL,
  vendor_id      uuid NOT NULL REFERENCES vendors(vendor_id) ON DELETE CASCADE,
  currency       text NULL,
  total_amount   numeric(14,2) NULL,
  tax_total      numeric(14,2) NULL,
  delivery_days  int NULL,
  valid_thru     date NULL,
  notes          text NULL,
  attachments    jsonb NULL,
  revision_no    int NOT NULL DEFAULT 1,
  submitted_at   timestamptz NULL,
  status         text NOT NULL DEFAULT 'draft' CHECK (status IN ('draft','submitted','revised','withdrawn','final')),
  created_at     timestamptz NOT NULL DEFAULT now(),
  updated_at     timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX IF NOT EXISTS idx_vendor_quotes_rfq_vendor ON vendor_quotes(rfq_id, vendor_id);
CREATE INDEX IF NOT EXISTS idx_vendor_quotes_status ON vendor_quotes(rfq_id, status);

-- Vendor quote lines
CREATE TABLE IF NOT EXISTS vendor_quote_items (
  quote_item_id  uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  quote_id       uuid NOT NULL REFERENCES vendor_quotes(quote_id) ON DELETE CASCADE,
  rfq_item_id    bigint NULL REFERENCES rfq_items(id) ON DELETE SET NULL,
  is_alternate   boolean NOT NULL DEFAULT false,
  alt_label      text NULL,
  unit_price     numeric(14,4) NULL,
  tax            numeric(14,4) NULL,
  lead_time_days int NULL,
  notes          text NULL
);

CREATE INDEX IF NOT EXISTS idx_vendor_quote_items_quote ON vendor_quote_items(quote_id);

-- Awards
CREATE TABLE IF NOT EXISTS rfq_awards (
  award_id      uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  rfq_id        uuid NOT NULL REFERENCES rfq_headers(rfq_id) ON DELETE CASCADE,
  scope         text NOT NULL CHECK (scope IN ('header','item')),
  rfq_item_id   bigint NULL REFERENCES rfq_items(id) ON DELETE SET NULL,
  vendor_id     uuid NOT NULL REFERENCES vendors(vendor_id) ON DELETE RESTRICT,
  notes         text NULL,
  created_at    timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX IF NOT EXISTS idx_rfq_awards_rfq ON rfq_awards(rfq_id);

-- Events (audit)
CREATE TABLE IF NOT EXISTS rfq_events (
  event_id    bigserial PRIMARY KEY,
  rfq_id      uuid NOT NULL REFERENCES rfq_headers(rfq_id) ON DELETE CASCADE,
  actor_id    uuid NULL,
  actor_type  text NOT NULL DEFAULT 'internal',
  type        text NOT NULL,
  payload     jsonb NOT NULL DEFAULT '{}'::jsonb,
  created_at  timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX IF NOT EXISTS idx_rfq_events_rfq ON rfq_events(rfq_id, created_at);
CREATE INDEX IF NOT EXISTS idx_rfq_events_payload_gin ON rfq_events USING GIN(payload);

-- Sequences for numbering per org/type/year
CREATE TABLE IF NOT EXISTS numbering_sequences (
  org_id   uuid NOT NULL,
  type     text NOT NULL,
  year     int  NOT NULL,
  next_seq int  NOT NULL DEFAULT 1,
  CONSTRAINT numbering_sequences_unique UNIQUE (org_id, type, year)
);

