-- RFQ Communications Schema
-- Date: 2025-09-27

DO $$ BEGIN
  CREATE EXTENSION IF NOT EXISTS pgcrypto;
EXCEPTION WHEN others THEN
  NULL;
END $$;

-- Add Q&A cutoff at RFQ level (can be overridden/changed later per RFQ)
DO $$ BEGIN
  ALTER TABLE rfq_headers ADD COLUMN IF NOT EXISTS qa_cutoff_at timestamptz NULL;
EXCEPTION WHEN undefined_table THEN
  NULL;
END $$;

-- Conversations per RFQ
CREATE TABLE IF NOT EXISTS rfq_conversations (
  conversation_id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  rfq_id          uuid NOT NULL REFERENCES rfq_headers(rfq_id) ON DELETE CASCADE,
  audience        text NOT NULL CHECK (audience IN ('internal','vendor','all_vendors')),
  vendor_id       uuid NULL REFERENCES vendors(vendor_id) ON DELETE CASCADE,
  subject         text NULL,
  locked_at       timestamptz NULL,
  created_by      uuid NULL,
  created_at      timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS idx_rfq_conversations_rfq ON rfq_conversations(rfq_id);
CREATE INDEX IF NOT EXISTS idx_rfq_conversations_scope ON rfq_conversations(rfq_id, audience);
CREATE INDEX IF NOT EXISTS idx_rfq_conversations_vendor ON rfq_conversations(rfq_id, vendor_id) WHERE vendor_id IS NOT NULL;

-- Messages
CREATE TABLE IF NOT EXISTS rfq_messages (
  message_id        uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  conversation_id   uuid NOT NULL REFERENCES rfq_conversations(conversation_id) ON DELETE CASCADE,
  rfq_id            uuid NOT NULL REFERENCES rfq_headers(rfq_id) ON DELETE CASCADE,
  parent_message_id uuid NULL REFERENCES rfq_messages(message_id) ON DELETE SET NULL,
  origin_message_id uuid NULL REFERENCES rfq_messages(message_id) ON DELETE SET NULL,
  author_type       text NOT NULL CHECK (author_type IN ('internal','vendor')),
  author_user_id    uuid NULL,
  vendor_id         uuid NULL REFERENCES vendors(vendor_id) ON DELETE SET NULL,
  category          text NOT NULL DEFAULT 'note' CHECK (category IN ('note','question','answer','announcement','addendum','clarification','reply')),
  status            text NOT NULL DEFAULT 'approved' CHECK (status IN ('pending','approved','rejected')),
  is_anonymous      boolean NOT NULL DEFAULT false,
  body_markdown     text NOT NULL,
  body_html         text NULL,
  created_at        timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS idx_rfq_messages_conv ON rfq_messages(conversation_id, created_at);
CREATE INDEX IF NOT EXISTS idx_rfq_messages_rfq ON rfq_messages(rfq_id, created_at);
CREATE INDEX IF NOT EXISTS idx_rfq_messages_status ON rfq_messages(status);

-- Message attachments (DMS-backed links)
CREATE TABLE IF NOT EXISTS rfq_message_attachments (
  message_attachment_id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  message_id            uuid NOT NULL REFERENCES rfq_messages(message_id) ON DELETE CASCADE,
  filetype              text NULL,
  file_url              text NULL,
  label                 text NULL
);
CREATE INDEX IF NOT EXISTS idx_rfq_message_attachments_msg ON rfq_message_attachments(message_id);

