-- TED Procurement Document Database Schema
-- Author: Martin.Schweitzer@procon.co.at and claude.ai
-- Description: PostgreSQL schema for storing EU eForms procurement notices with vector search support

-- Create TED schema if it doesn't exist
CREATE SCHEMA IF NOT EXISTS ted;

-- Set search path to use TED schema
SET search_path TO ted, public;

-- Enable required PostgreSQL extensions (wenn Berechtigung vorhanden)
-- Falls Extensions nicht erstellt werden können, müssen diese vom DBA manuell erstellt werden
DO $$
BEGIN
    CREATE EXTENSION IF NOT EXISTS pgcrypto SCHEMA public;
EXCEPTION
    WHEN insufficient_privilege THEN
        RAISE NOTICE 'Skipping pgcrypto extension creation (insufficient privileges)';
    WHEN duplicate_object THEN
        RAISE NOTICE 'Extension pgcrypto already exists';
END
$$;

DO $$
BEGIN
    CREATE EXTENSION IF NOT EXISTS vector SCHEMA public;
EXCEPTION
    WHEN insufficient_privilege THEN
        RAISE NOTICE 'Skipping vector extension creation (insufficient privileges)';
    WHEN duplicate_object THEN
        RAISE NOTICE 'Extension vector already exists';
    WHEN undefined_file THEN
        RAISE WARNING 'Extension vector not available - install pgvector on the database server';
END
$$;

DO $$
BEGIN
    CREATE EXTENSION IF NOT EXISTS pg_trgm SCHEMA public;
EXCEPTION
    WHEN insufficient_privilege THEN
        RAISE NOTICE 'Skipping pg_trgm extension creation (insufficient privileges)';
    WHEN duplicate_object THEN
        RAISE NOTICE 'Extension pg_trgm already exists';
END
$$;

-- Enum types for notice classifications
CREATE TYPE notice_type AS ENUM (
    'CONTRACT_NOTICE',           -- cn-standard, cn-social, etc.
    'PRIOR_INFORMATION_NOTICE',  -- pin-*
    'CONTRACT_AWARD_NOTICE',     -- can-*
    'MODIFICATION_NOTICE',       -- mod-*
    'OTHER'
);

CREATE TYPE contract_nature AS ENUM (
    'SUPPLIES',
    'SERVICES', 
    'WORKS',
    'MIXED',
    'UNKNOWN'
);

CREATE TYPE procedure_type AS ENUM (
    'OPEN',
    'RESTRICTED',
    'COMPETITIVE_DIALOGUE',
    'INNOVATION_PARTNERSHIP',
    'NEGOTIATED_WITHOUT_PUBLICATION',
    'NEGOTIATED_WITH_PUBLICATION',
    'OTHER'
);

CREATE TYPE vectorization_status AS ENUM (
    'PENDING',
    'PROCESSING',
    'COMPLETED',
    'FAILED',
    'SKIPPED'
);

-- Main procurement document table
CREATE TABLE procurement_document (
    -- Primary key using UUID
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    
    -- Document hash for idempotent processing (SHA-256 of XML content)
    document_hash VARCHAR(64) NOT NULL UNIQUE,
    
    -- TED/eForms identifiers
    notice_id VARCHAR(100),                    -- e.g., "9f87fd31-2c94-45cb-92ca-a3f876252149"
    publication_id VARCHAR(50),                -- e.g., "00786665-2025"
    ojs_id VARCHAR(20),                        -- e.g., "229/2025" (Official Journal Supplement)
    contract_folder_id VARCHAR(100),           -- Contract folder grouping
    
    -- Document metadata
    notice_type notice_type NOT NULL DEFAULT 'OTHER',
    notice_subtype_code VARCHAR(10),           -- e.g., "16" for Contract Notice Standard
    sdk_version VARCHAR(20),                   -- e.g., "eforms-sdk-1.13"
    ubl_version VARCHAR(10),                   -- e.g., "2.3"
    language_code VARCHAR(10),                 -- Primary language (e.g., "POL")
    
    -- Timestamps from document
    issue_date DATE,
    issue_time TIME,
    publication_date DATE,
    submission_deadline TIMESTAMP WITH TIME ZONE,
    
    -- Contracting authority information
    buyer_name TEXT,
    buyer_country_code VARCHAR(10),            -- ISO 3166-1 alpha-3 (e.g., "POL")
    buyer_city VARCHAR(255),
    buyer_postal_code VARCHAR(20),
    buyer_nuts_code VARCHAR(10),               -- NUTS region code (e.g., "PL415")
    buyer_activity_type VARCHAR(50),           -- e.g., "health", "defence"
    buyer_legal_type VARCHAR(50),              -- e.g., "body-pl"
    
    -- Procurement project information
    project_title TEXT,
    project_description TEXT,
    internal_reference VARCHAR(100),           -- Buyer's internal reference
    contract_nature contract_nature NOT NULL DEFAULT 'UNKNOWN',
    procedure_type procedure_type DEFAULT 'OTHER',
    
    -- Classification
    cpv_codes VARCHAR(100)[],                  -- Common Procurement Vocabulary codes
    nuts_codes VARCHAR(20)[],                  -- All NUTS codes for delivery locations
    
    -- Financial information (if available)
    estimated_value DECIMAL(20, 2),
    estimated_value_currency VARCHAR(3),
    
    -- Lot information
    total_lots INTEGER DEFAULT 0,
    max_lots_awarded INTEGER,
    max_lots_submitted INTEGER,
    
    -- Legal basis
    regulatory_domain VARCHAR(50),             -- e.g., "32014L0024" (EU directive reference)
    eu_funded BOOLEAN DEFAULT FALSE,
    
    -- Textual representation for vectorization
    -- Contains extracted and normalized text content for semantic search
    text_content TEXT,
    
    -- Original XML document stored in native PostgreSQL XML type
    xml_document XML NOT NULL,
    
    -- Vector embedding for semantic search (1024 dimensions for multilingual-e5-large)
    content_vector vector(1024),
    
    -- Vectorization tracking
    vectorization_status vectorization_status DEFAULT 'PENDING',
    vectorization_error TEXT,
    vectorized_at TIMESTAMP WITH TIME ZONE,
    
    -- Processing metadata
    source_filename VARCHAR(500),
    source_path TEXT,
    file_size_bytes BIGINT,
    
    -- Audit fields
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    processing_duration_ms INTEGER
);

-- Table for storing lot details (denormalized for search performance)
CREATE TABLE procurement_lot (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    document_id UUID NOT NULL REFERENCES procurement_document(id) ON DELETE CASCADE,
    
    lot_id VARCHAR(50) NOT NULL,               -- e.g., "LOT-0001"
    internal_id VARCHAR(100),                  -- Buyer's internal lot reference
    
    title TEXT,
    description TEXT,
    
    cpv_codes VARCHAR(100)[],
    nuts_codes VARCHAR(20)[],
    
    estimated_value DECIMAL(20, 2),
    estimated_value_currency VARCHAR(3),
    
    duration_value INTEGER,
    duration_unit VARCHAR(20),                 -- e.g., "MONTH", "DAY"
    
    submission_deadline TIMESTAMP WITH TIME ZONE,
    
    eu_funded BOOLEAN DEFAULT FALSE,
    
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    
    UNIQUE(document_id, lot_id)
);

-- Table for organizations mentioned in notices
CREATE TABLE organization (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    document_id UUID NOT NULL REFERENCES procurement_document(id) ON DELETE CASCADE,

    org_reference VARCHAR(100),                -- Internal reference (e.g., "ORG-0001")
    role VARCHAR(100),                         -- e.g., "buyer", "review-body", "ted-esen"

    name TEXT,                                 -- Full organization name
    company_id TEXT,                           -- Tax/registration ID (can be very long)

    country_code VARCHAR(10),
    city TEXT,                                 -- City name (can be extremely long)
    postal_code TEXT,                          -- Address/postal code (can contain full addresses)
    street_name TEXT,                          -- Street address
    nuts_code VARCHAR(10),

    website_uri TEXT,
    email VARCHAR(255),
    phone VARCHAR(100),                        -- International phone numbers

    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,

    UNIQUE(document_id, org_reference)
);

-- Processing log for tracking and debugging
CREATE TABLE processing_log (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    document_id UUID REFERENCES procurement_document(id) ON DELETE SET NULL,
    document_hash VARCHAR(64),

    event_type VARCHAR(50) NOT NULL,           -- e.g., "RECEIVED", "VALIDATED", "PARSED", "STORED", "VECTORIZED", "ERROR"
    event_status VARCHAR(20) NOT NULL,         -- e.g., "SUCCESS", "FAILURE", "SKIPPED"

    message TEXT,
    error_details TEXT,

    source_filename VARCHAR(500),

    duration_ms INTEGER,

    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- TED Daily Package Tracking Table
CREATE TABLE ted_daily_package (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),

    -- Package identifier (YYYYSSSSS format, e.g., 202400001)
    package_identifier VARCHAR(20) NOT NULL UNIQUE,

    -- Year and serial number
    year INTEGER NOT NULL,
    serial_number INTEGER NOT NULL,

    -- Download information
    download_url VARCHAR(500) NOT NULL,
    file_hash VARCHAR(64),                     -- SHA-256 hash for idempotency

    -- Processing statistics
    xml_file_count INTEGER,
    processed_count INTEGER DEFAULT 0,
    failed_count INTEGER DEFAULT 0,

    -- Status
    download_status VARCHAR(30) NOT NULL DEFAULT 'PENDING',  -- PENDING, DOWNLOADING, DOWNLOADED, PROCESSING, COMPLETED, FAILED, NOT_FOUND
    error_message TEXT,

    -- Timestamps
    downloaded_at TIMESTAMP WITH TIME ZONE,
    processed_at TIMESTAMP WITH TIME ZONE,

    -- Performance metrics
    download_duration_ms BIGINT,
    processing_duration_ms BIGINT,

    -- Audit fields
    created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,

    -- Unique constraint on year + serial
    UNIQUE(year, serial_number)
);

-- Indexes for efficient querying

-- Hash lookup for idempotent processing (most critical)
CREATE INDEX idx_doc_hash ON procurement_document(document_hash);

-- Publication/notice ID lookups
CREATE INDEX idx_doc_publication_id ON procurement_document(publication_id);
CREATE INDEX idx_doc_notice_id ON procurement_document(notice_id);

-- Date range queries
CREATE INDEX idx_doc_publication_date ON procurement_document(publication_date);
CREATE INDEX idx_doc_issue_date ON procurement_document(issue_date);
CREATE INDEX idx_doc_submission_deadline ON procurement_document(submission_deadline);

-- Geographic searches
CREATE INDEX idx_doc_buyer_country ON procurement_document(buyer_country_code);
CREATE INDEX idx_doc_buyer_nuts ON procurement_document(buyer_nuts_code);
CREATE INDEX idx_doc_nuts_codes ON procurement_document USING GIN(nuts_codes);

-- Classification searches
CREATE INDEX idx_doc_notice_type ON procurement_document(notice_type);
CREATE INDEX idx_doc_contract_nature ON procurement_document(contract_nature);
CREATE INDEX idx_doc_procedure_type ON procurement_document(procedure_type);
CREATE INDEX idx_doc_cpv_codes ON procurement_document USING GIN(cpv_codes);

-- Full-text search on textual content
CREATE INDEX idx_doc_text_content_trgm ON procurement_document USING GIN(text_content gin_trgm_ops);

-- Vector similarity search using IVFFlat index (efficient for approximate nearest neighbor)
-- Lists parameter: sqrt(number_of_vectors) for optimal performance
CREATE INDEX idx_doc_vector ON procurement_document USING ivfflat (content_vector vector_cosine_ops) WITH (lists = 100);

-- Vectorization status for async processing
CREATE INDEX idx_doc_vectorization_status ON procurement_document(vectorization_status) WHERE vectorization_status IN ('PENDING', 'PROCESSING');

-- Lot indexes
CREATE INDEX idx_lot_document ON procurement_lot(document_id);
CREATE INDEX idx_lot_cpv_codes ON procurement_lot USING GIN(cpv_codes);

-- Organization indexes
CREATE INDEX idx_org_document ON organization(document_id);
CREATE INDEX idx_org_country ON organization(country_code);

-- Processing log indexes
CREATE INDEX idx_log_document ON processing_log(document_id);
CREATE INDEX idx_log_created ON processing_log(created_at);
CREATE INDEX idx_log_event_type ON processing_log(event_type);

-- TED daily package indexes
CREATE INDEX idx_package_identifier ON ted_daily_package(package_identifier);
CREATE INDEX idx_package_year_serial ON ted_daily_package(year, serial_number);
CREATE INDEX idx_package_status ON ted_daily_package(download_status);
CREATE INDEX idx_package_downloaded_at ON ted_daily_package(downloaded_at);

-- Trigger to update updated_at timestamp
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$ language 'plpgsql';

CREATE TRIGGER update_procurement_document_updated_at
    BEFORE UPDATE ON procurement_document
    FOR EACH ROW
    EXECUTE FUNCTION update_updated_at_column();

CREATE TRIGGER update_ted_daily_package_updated_at
    BEFORE UPDATE ON ted_daily_package
    FOR EACH ROW
    EXECUTE FUNCTION update_updated_at_column();

-- Helper function for semantic search with filtering
CREATE OR REPLACE FUNCTION search_documents_semantic(
    query_vector vector(1024),
    similarity_threshold FLOAT DEFAULT 0.7,
    country_filter VARCHAR(10) DEFAULT NULL,
    notice_type_filter notice_type DEFAULT NULL,
    contract_nature_filter contract_nature DEFAULT NULL,
    cpv_prefix VARCHAR(20) DEFAULT NULL,
    date_from DATE DEFAULT NULL,
    date_to DATE DEFAULT NULL,
    result_limit INTEGER DEFAULT 20
)
RETURNS TABLE (
    id UUID,
    publication_id VARCHAR(50),
    project_title TEXT,
    buyer_name TEXT,
    buyer_country_code VARCHAR(10),
    publication_date DATE,
    similarity FLOAT
) AS $$
BEGIN
    RETURN QUERY
    SELECT 
        pd.id,
        pd.publication_id,
        pd.project_title,
        pd.buyer_name,
        pd.buyer_country_code,
        pd.publication_date,
        1 - (pd.content_vector <=> query_vector) AS similarity
    FROM procurement_document pd
    WHERE 
        pd.content_vector IS NOT NULL
        AND (1 - (pd.content_vector <=> query_vector)) >= similarity_threshold
        AND (country_filter IS NULL OR pd.buyer_country_code = country_filter)
        AND (notice_type_filter IS NULL OR pd.notice_type = notice_type_filter)
        AND (contract_nature_filter IS NULL OR pd.contract_nature = contract_nature_filter)
        AND (cpv_prefix IS NULL OR EXISTS (
            SELECT 1 FROM unnest(pd.cpv_codes) code WHERE code LIKE cpv_prefix || '%'
        ))
        AND (date_from IS NULL OR pd.publication_date >= date_from)
        AND (date_to IS NULL OR pd.publication_date <= date_to)
    ORDER BY similarity DESC
    LIMIT result_limit;
END;
$$ LANGUAGE plpgsql;

-- Statistics view for monitoring
CREATE VIEW document_statistics AS
SELECT 
    COUNT(*) AS total_documents,
    COUNT(*) FILTER (WHERE vectorization_status = 'COMPLETED') AS vectorized_documents,
    COUNT(*) FILTER (WHERE vectorization_status = 'PENDING') AS pending_vectorization,
    COUNT(*) FILTER (WHERE vectorization_status = 'FAILED') AS failed_vectorization,
    COUNT(DISTINCT buyer_country_code) AS unique_countries,
    COUNT(DISTINCT notice_type) AS notice_types,
    MIN(publication_date) AS earliest_publication,
    MAX(publication_date) AS latest_publication,
    AVG(total_lots) AS avg_lots_per_notice,
    SUM(total_lots) AS total_lots
FROM procurement_document;

COMMENT ON TABLE procurement_document IS 'Main table storing EU eForms procurement notices from TED';
COMMENT ON COLUMN procurement_document.document_hash IS 'SHA-256 hash of XML content for idempotent processing';
COMMENT ON COLUMN procurement_document.content_vector IS '1024-dimensional vector from multilingual-e5-large model';
COMMENT ON COLUMN procurement_document.text_content IS 'Normalized text for vectorization and full-text search';

COMMENT ON TABLE ted_daily_package IS 'Tracking table for TED daily package downloads';
COMMENT ON COLUMN ted_daily_package.package_identifier IS 'Unique package identifier in YYYYSSSSS format';
COMMENT ON COLUMN ted_daily_package.file_hash IS 'SHA-256 hash for idempotency checking';
COMMENT ON COLUMN ted_daily_package.download_status IS 'Current status: PENDING, DOWNLOADING, DOWNLOADED, PROCESSING, COMPLETED, FAILED, NOT_FOUND';
COMMENT ON COLUMN organization.postal_code IS 'Postal code or ZIP code. Extended to 255 chars to handle multi-line addresses from TED data.';
