CREATE TABLE IF NOT EXISTS boqs (id uuid PRIMARY KEY DEFAULT gen_random_uuid(), title varchar(255) NOT NULL, reference varchar(100) UNIQUE NOT NULL, boq_date date NOT NULL DEFAULT CURRENT_DATE, status varchar(20) NOT NULL DEFAULT 'draft', grand_total numeric(18,2) NOT NULL DEFAULT 0, created_by uuid REFERENCES users(id), locked_at timestamptz, locked_by uuid REFERENCES users(id), created_at timestamptz NOT NULL DEFAULT now(), updated_at timestamptz NOT NULL DEFAULT now());
CREATE TABLE IF NOT EXISTS boq_lines (id uuid PRIMARY KEY DEFAULT gen_random_uuid(), boq_id uuid NOT NULL REFERENCES boqs(id) ON DELETE CASCADE, rate_item_id uuid REFERENCES market_rate_items(id), custom_item_flag boolean NOT NULL DEFAULT false, description text NOT NULL, unit varchar(80), quantity numeric(18,4) NOT NULL CHECK(quantity>=0), rate numeric(18,2) NOT NULL CHECK(rate>=0), line_total numeric(18,2) NOT NULL DEFAULT 0, sort_order integer NOT NULL DEFAULT 0);
CREATE TABLE IF NOT EXISTS rfqs (id uuid PRIMARY KEY DEFAULT gen_random_uuid(), boq_id uuid NOT NULL REFERENCES boqs(id), reference varchar(100) UNIQUE NOT NULL, issued_date timestamptz, due_date timestamptz, status varchar(30) NOT NULL DEFAULT 'draft', created_by uuid REFERENCES users(id), created_at timestamptz NOT NULL DEFAULT now(), updated_at timestamptz NOT NULL DEFAULT now());
CREATE TABLE IF NOT EXISTS rfq_vendors (id uuid PRIMARY KEY DEFAULT gen_random_uuid(), rfq_id uuid NOT NULL REFERENCES rfqs(id) ON DELETE CASCADE, vendor_id uuid NOT NULL REFERENCES vendors(id), status varchar(30) NOT NULL DEFAULT 'issued', issued_at timestamptz, UNIQUE(rfq_id,vendor_id));
CREATE TABLE IF NOT EXISTS quotations (id uuid PRIMARY KEY DEFAULT gen_random_uuid(), rfq_id uuid NOT NULL REFERENCES rfqs(id), vendor_id uuid NOT NULL REFERENCES vendors(id), status varchar(30) NOT NULL DEFAULT 'draft', total numeric(18,2) NOT NULL DEFAULT 0, submitted_at timestamptz, submitted_by uuid REFERENCES users(id), remarks text, created_at timestamptz NOT NULL DEFAULT now(), updated_at timestamptz NOT NULL DEFAULT now(), UNIQUE(rfq_id,vendor_id));
CREATE TABLE IF NOT EXISTS quotation_lines (id uuid PRIMARY KEY DEFAULT gen_random_uuid(), quotation_id uuid NOT NULL REFERENCES quotations(id) ON DELETE CASCADE, boq_line_id uuid NOT NULL REFERENCES boq_lines(id), quoted_rate numeric(18,2) NOT NULL CHECK(quoted_rate>=0), line_total numeric(18,2) NOT NULL DEFAULT 0, UNIQUE(quotation_id,boq_line_id));
CREATE TABLE IF NOT EXISTS quotation_attachments (id uuid PRIMARY KEY DEFAULT gen_random_uuid(), quotation_id uuid NOT NULL REFERENCES quotations(id) ON DELETE CASCADE, original_name text NOT NULL, stored_name text NOT NULL, storage_path text NOT NULL, mime_type varchar(255), size_bytes bigint NOT NULL, uploaded_by uuid REFERENCES users(id), uploaded_at timestamptz NOT NULL DEFAULT now());
CREATE TABLE IF NOT EXISTS awards (id uuid PRIMARY KEY DEFAULT gen_random_uuid(), rfq_id uuid NOT NULL REFERENCES rfqs(id), winning_quotation_id uuid NOT NULL REFERENCES quotations(id), approved_by uuid REFERENCES users(id), approval_date timestamptz NOT NULL DEFAULT now(), remarks text, created_at timestamptz NOT NULL DEFAULT now());