-- Phase 3: TED becomes a structured projection on top of the generic DOC document root.
-- Additive migration; legacy TED tables remain in place for compatibility.

SET search_path TO TED, DOC, public;

CREATE TABLE IF NOT EXISTS TED.ted_notice_projection (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    document_id UUID NOT NULL UNIQUE REFERENCES DOC.doc_document(id) ON DELETE CASCADE,
    legacy_procurement_document_id UUID UNIQUE REFERENCES TED.procurement_document(id) ON DELETE SET NULL,
    notice_id VARCHAR(100),
    publication_id VARCHAR(50),
    notice_url VARCHAR(255),
    ojs_id VARCHAR(20),
    contract_folder_id VARCHAR(100),
    notice_type VARCHAR(50) NOT NULL DEFAULT 'OTHER',
    notice_subtype_code VARCHAR(10),
    sdk_version VARCHAR(20),
    ubl_version VARCHAR(10),
    language_code VARCHAR(10),
    issue_datetime TIMESTAMP WITH TIME ZONE,
    publication_date DATE,
    submission_deadline TIMESTAMP WITH TIME ZONE,
    buyer_name TEXT,
    buyer_country_code VARCHAR(10),
    buyer_city VARCHAR(255),
    buyer_postal_code VARCHAR(100),
    buyer_nuts_code VARCHAR(10),
    buyer_activity_type VARCHAR(50),
    buyer_legal_type VARCHAR(50),
    project_title TEXT,
    project_description TEXT,
    internal_reference VARCHAR(500),
    contract_nature VARCHAR(50) NOT NULL DEFAULT 'UNKNOWN',
    procedure_type VARCHAR(50) DEFAULT 'OTHER',
    cpv_codes VARCHAR(100)[],
    nuts_codes VARCHAR(20)[],
    estimated_value NUMERIC(20,2),
    estimated_value_currency VARCHAR(3),
    total_lots INTEGER NOT NULL DEFAULT 0,
    max_lots_awarded INTEGER,
    max_lots_submitted INTEGER,
    regulatory_domain VARCHAR(50),
    eu_funded BOOLEAN NOT NULL DEFAULT FALSE,
    created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX IF NOT EXISTS idx_ted_notice_projection_publication_id
    ON TED.ted_notice_projection(publication_id);
CREATE INDEX IF NOT EXISTS idx_ted_notice_projection_notice_type
    ON TED.ted_notice_projection(notice_type);
CREATE INDEX IF NOT EXISTS idx_ted_notice_projection_buyer_country
    ON TED.ted_notice_projection(buyer_country_code);
CREATE INDEX IF NOT EXISTS idx_ted_notice_projection_publication_date
    ON TED.ted_notice_projection(publication_date DESC);
CREATE INDEX IF NOT EXISTS idx_ted_notice_projection_document
    ON TED.ted_notice_projection(document_id);
CREATE INDEX IF NOT EXISTS idx_ted_notice_projection_legacy_doc
    ON TED.ted_notice_projection(legacy_procurement_document_id);

CREATE TABLE IF NOT EXISTS TED.ted_notice_lot (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    notice_projection_id UUID NOT NULL REFERENCES TED.ted_notice_projection(id) ON DELETE CASCADE,
    lot_id VARCHAR(50) NOT NULL,
    internal_id TEXT,
    title TEXT,
    description TEXT,
    cpv_codes VARCHAR(100)[],
    nuts_codes VARCHAR(20)[],
    estimated_value NUMERIC(20,2),
    estimated_value_currency VARCHAR(3),
    duration_value DOUBLE PRECISION,
    duration_unit VARCHAR(20),
    submission_deadline TIMESTAMP WITH TIME ZONE,
    eu_funded BOOLEAN NOT NULL DEFAULT FALSE,
    created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT uq_ted_notice_lot_projection_lot UNIQUE (notice_projection_id, lot_id)
);

CREATE INDEX IF NOT EXISTS idx_ted_notice_lot_projection
    ON TED.ted_notice_lot(notice_projection_id);

CREATE TABLE IF NOT EXISTS TED.ted_notice_organization (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    notice_projection_id UUID NOT NULL REFERENCES TED.ted_notice_projection(id) ON DELETE CASCADE,
    org_reference VARCHAR(50),
    role VARCHAR(50),
    name TEXT,
    company_id VARCHAR(1000),
    country_code VARCHAR(10),
    city VARCHAR(255),
    postal_code VARCHAR(255),
    street_name TEXT,
    nuts_code VARCHAR(10),
    website_uri TEXT,
    email VARCHAR(255),
    phone VARCHAR(50),
    created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT uq_ted_notice_org_projection_ref UNIQUE (notice_projection_id, org_reference)
);

CREATE INDEX IF NOT EXISTS idx_ted_notice_org_projection
    ON TED.ted_notice_organization(notice_projection_id);
CREATE INDEX IF NOT EXISTS idx_ted_notice_org_country
    ON TED.ted_notice_organization(country_code);
