-- Phase 1: Generic DOC persistence backbone for the Procon Document Intelligence Platform
-- This migration is additive and intentionally does not modify the existing TED runtime tables.

CREATE SCHEMA IF NOT EXISTS DOC;

SET search_path TO TED, DOC, public;

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
    IF NOT EXISTS (SELECT 1 FROM pg_type WHERE typname = 'doc_document_visibility') THEN
        CREATE TYPE DOC.doc_document_visibility AS ENUM ('PUBLIC', 'TENANT', 'SHARED', 'RESTRICTED');
    END IF;
END
$$;

DO $$
BEGIN
    IF NOT EXISTS (SELECT 1 FROM pg_type WHERE typname = 'doc_document_type') THEN
        CREATE TYPE DOC.doc_document_type AS ENUM (
            'TED_NOTICE', 'EMAIL', 'MIME_MESSAGE', 'PDF', 'DOCX', 'HTML',
            'XML_GENERIC', 'TEXT', 'MARKDOWN', 'ZIP_ARCHIVE', 'GENERIC_BINARY', 'UNKNOWN'
        );
    END IF;
END
$$;

DO $$
BEGIN
    IF NOT EXISTS (SELECT 1 FROM pg_type WHERE typname = 'doc_document_family') THEN
        CREATE TYPE DOC.doc_document_family AS ENUM ('PROCUREMENT', 'MAIL', 'ATTACHMENT', 'KNOWLEDGE', 'GENERIC');
    END IF;
END
$$;

DO $$
BEGIN
    IF NOT EXISTS (SELECT 1 FROM pg_type WHERE typname = 'doc_document_status') THEN
        CREATE TYPE DOC.doc_document_status AS ENUM ('RECEIVED', 'CLASSIFIED', 'EXTRACTED', 'REPRESENTED', 'INDEXED', 'FAILED', 'ARCHIVED');
    END IF;
END
$$;

DO $$
BEGIN
    IF NOT EXISTS (SELECT 1 FROM pg_type WHERE typname = 'doc_source_type') THEN
        CREATE TYPE DOC.doc_source_type AS ENUM ('TED_PACKAGE', 'MAIL', 'FILE_SYSTEM', 'REST_UPLOAD', 'MANUAL_UPLOAD', 'ZIP_CHILD', 'API', 'MIGRATION');
    END IF;
END
$$;

DO $$
BEGIN
    IF NOT EXISTS (SELECT 1 FROM pg_type WHERE typname = 'doc_content_role') THEN
        CREATE TYPE DOC.doc_content_role AS ENUM (
            'ORIGINAL', 'NORMALIZED_TEXT', 'OCR_TEXT', 'HTML_CLEAN',
            'EXTRACTED_METADATA_JSON', 'THUMBNAIL', 'DERIVED_BINARY'
        );
    END IF;
END
$$;

DO $$
BEGIN
    IF NOT EXISTS (SELECT 1 FROM pg_type WHERE typname = 'doc_storage_type') THEN
        CREATE TYPE DOC.doc_storage_type AS ENUM ('DB_TEXT', 'DB_BINARY', 'FILE_PATH', 'OBJECT_STORAGE', 'EXTERNAL_REFERENCE');
    END IF;
END
$$;

DO $$
BEGIN
    IF NOT EXISTS (SELECT 1 FROM pg_type WHERE typname = 'doc_representation_type') THEN
        CREATE TYPE DOC.doc_representation_type AS ENUM ('FULLTEXT', 'SEMANTIC_TEXT', 'SUMMARY', 'TITLE_ABSTRACT', 'CHUNK', 'METADATA_ENRICHED');
    END IF;
END
$$;

DO $$
BEGIN
    IF NOT EXISTS (SELECT 1 FROM pg_type WHERE typname = 'doc_embedding_status') THEN
        CREATE TYPE DOC.doc_embedding_status AS ENUM ('PENDING', 'PROCESSING', 'COMPLETED', 'FAILED', 'SKIPPED');
    END IF;
END
$$;

DO $$
BEGIN
    IF NOT EXISTS (SELECT 1 FROM pg_type WHERE typname = 'doc_distance_metric') THEN
        CREATE TYPE DOC.doc_distance_metric AS ENUM ('COSINE', 'L2', 'INNER_PRODUCT');
    END IF;
END
$$;

DO $$
BEGIN
    IF NOT EXISTS (SELECT 1 FROM pg_type WHERE typname = 'doc_relation_type') THEN
        CREATE TYPE DOC.doc_relation_type AS ENUM ('CONTAINS', 'ATTACHMENT_OF', 'EXTRACTED_FROM', 'DERIVED_FROM', 'PART_OF', 'VERSION_OF', 'RELATED_TO');
    END IF;
END
$$;

CREATE TABLE IF NOT EXISTS DOC.doc_tenant (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_key VARCHAR(120) NOT NULL UNIQUE,
    display_name VARCHAR(255) NOT NULL,
    description TEXT,
    active BOOLEAN NOT NULL DEFAULT TRUE,
    created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS DOC.doc_document (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    owner_tenant_id UUID REFERENCES DOC.doc_tenant(id),
    visibility DOC.doc_document_visibility NOT NULL,
    document_type DOC.doc_document_type NOT NULL,
    document_family DOC.doc_document_family NOT NULL,
    status DOC.doc_document_status NOT NULL DEFAULT 'RECEIVED',
    title VARCHAR(1000),
    summary TEXT,
    language_code VARCHAR(16),
    mime_type VARCHAR(255),
    business_key VARCHAR(255),
    dedup_hash VARCHAR(64),
    created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS DOC.doc_source (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    document_id UUID NOT NULL REFERENCES DOC.doc_document(id) ON DELETE CASCADE,
    source_type DOC.doc_source_type NOT NULL,
    external_source_id VARCHAR(500),
    source_uri TEXT,
    source_filename VARCHAR(1000),
    parent_source_id UUID,
    import_batch_id VARCHAR(255),
    received_at TIMESTAMP WITH TIME ZONE,
    created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS DOC.doc_content (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    document_id UUID NOT NULL REFERENCES DOC.doc_document(id) ON DELETE CASCADE,
    content_role DOC.doc_content_role NOT NULL,
    storage_type DOC.doc_storage_type NOT NULL,
    mime_type VARCHAR(255),
    charset_name VARCHAR(120),
    text_content TEXT,
    binary_ref TEXT,
    content_hash VARCHAR(64),
    size_bytes BIGINT,
    created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS DOC.doc_text_representation (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    document_id UUID NOT NULL REFERENCES DOC.doc_document(id) ON DELETE CASCADE,
    content_id UUID REFERENCES DOC.doc_content(id) ON DELETE SET NULL,
    representation_type DOC.doc_representation_type NOT NULL,
    builder_key VARCHAR(255),
    language_code VARCHAR(16),
    token_count INTEGER,
    char_count INTEGER,
    chunk_index INTEGER,
    chunk_start_offset INTEGER,
    chunk_end_offset INTEGER,
    is_primary BOOLEAN NOT NULL DEFAULT FALSE,
    text_body TEXT NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS DOC.doc_embedding_model (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    model_key VARCHAR(255) NOT NULL UNIQUE,
    provider VARCHAR(120) NOT NULL,
    display_name VARCHAR(255),
    dimensions INTEGER NOT NULL,
    distance_metric DOC.doc_distance_metric NOT NULL DEFAULT 'COSINE',
    query_prefix_required BOOLEAN NOT NULL DEFAULT FALSE,
    active BOOLEAN NOT NULL DEFAULT TRUE,
    created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS DOC.doc_embedding (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    document_id UUID NOT NULL REFERENCES DOC.doc_document(id) ON DELETE CASCADE,
    representation_id UUID NOT NULL REFERENCES DOC.doc_text_representation(id) ON DELETE CASCADE,
    model_id UUID NOT NULL REFERENCES DOC.doc_embedding_model(id),
    embedding_status DOC.doc_embedding_status NOT NULL DEFAULT 'PENDING',
    token_count INTEGER,
    embedding_dimensions INTEGER,
    error_message TEXT,
    embedded_at TIMESTAMP WITH TIME ZONE,
    embedding_vector public.vector,
    created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS DOC.doc_relation (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    parent_document_id UUID NOT NULL REFERENCES DOC.doc_document(id) ON DELETE CASCADE,
    child_document_id UUID NOT NULL REFERENCES DOC.doc_document(id) ON DELETE CASCADE,
    relation_type DOC.doc_relation_type NOT NULL,
    sort_order INTEGER,
    relation_metadata TEXT,
    created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT chk_doc_relation_no_self CHECK (parent_document_id <> child_document_id)
);

CREATE UNIQUE INDEX IF NOT EXISTS idx_doc_tenant_key ON DOC.doc_tenant(tenant_key);
CREATE INDEX IF NOT EXISTS idx_doc_tenant_active ON DOC.doc_tenant(active);

CREATE INDEX IF NOT EXISTS idx_doc_document_type ON DOC.doc_document(document_type);
CREATE INDEX IF NOT EXISTS idx_doc_document_family ON DOC.doc_document(document_family);
CREATE INDEX IF NOT EXISTS idx_doc_document_status ON DOC.doc_document(status);
CREATE INDEX IF NOT EXISTS idx_doc_document_visibility ON DOC.doc_document(visibility);
CREATE INDEX IF NOT EXISTS idx_doc_document_owner_tenant ON DOC.doc_document(owner_tenant_id);
CREATE INDEX IF NOT EXISTS idx_doc_document_dedup_hash ON DOC.doc_document(dedup_hash);
CREATE INDEX IF NOT EXISTS idx_doc_document_business_key ON DOC.doc_document(business_key);
CREATE INDEX IF NOT EXISTS idx_doc_document_created_at ON DOC.doc_document(created_at DESC);

CREATE INDEX IF NOT EXISTS idx_doc_source_document ON DOC.doc_source(document_id);
CREATE INDEX IF NOT EXISTS idx_doc_source_type ON DOC.doc_source(source_type);
CREATE INDEX IF NOT EXISTS idx_doc_source_external_id ON DOC.doc_source(external_source_id);
CREATE INDEX IF NOT EXISTS idx_doc_source_received_at ON DOC.doc_source(received_at DESC);
CREATE INDEX IF NOT EXISTS idx_doc_source_parent_source ON DOC.doc_source(parent_source_id);

CREATE INDEX IF NOT EXISTS idx_doc_content_document ON DOC.doc_content(document_id);
CREATE INDEX IF NOT EXISTS idx_doc_content_role ON DOC.doc_content(content_role);
CREATE INDEX IF NOT EXISTS idx_doc_content_hash ON DOC.doc_content(content_hash);
CREATE INDEX IF NOT EXISTS idx_doc_content_storage_type ON DOC.doc_content(storage_type);

CREATE INDEX IF NOT EXISTS idx_doc_text_repr_document ON DOC.doc_text_representation(document_id);
CREATE INDEX IF NOT EXISTS idx_doc_text_repr_content ON DOC.doc_text_representation(content_id);
CREATE INDEX IF NOT EXISTS idx_doc_text_repr_type ON DOC.doc_text_representation(representation_type);
CREATE INDEX IF NOT EXISTS idx_doc_text_repr_primary ON DOC.doc_text_representation(is_primary);

CREATE UNIQUE INDEX IF NOT EXISTS idx_doc_embedding_model_key ON DOC.doc_embedding_model(model_key);
CREATE INDEX IF NOT EXISTS idx_doc_embedding_model_active ON DOC.doc_embedding_model(active);

CREATE INDEX IF NOT EXISTS idx_doc_embedding_document ON DOC.doc_embedding(document_id);
CREATE INDEX IF NOT EXISTS idx_doc_embedding_repr ON DOC.doc_embedding(representation_id);
CREATE INDEX IF NOT EXISTS idx_doc_embedding_model ON DOC.doc_embedding(model_id);
CREATE INDEX IF NOT EXISTS idx_doc_embedding_status ON DOC.doc_embedding(embedding_status);
CREATE INDEX IF NOT EXISTS idx_doc_embedding_embedded_at ON DOC.doc_embedding(embedded_at DESC);

CREATE INDEX IF NOT EXISTS idx_doc_relation_parent ON DOC.doc_relation(parent_document_id);
CREATE INDEX IF NOT EXISTS idx_doc_relation_child ON DOC.doc_relation(child_document_id);
CREATE INDEX IF NOT EXISTS idx_doc_relation_type ON DOC.doc_relation(relation_type);

COMMENT ON SCHEMA DOC IS 'Generic document platform schema introduced in Phase 1';
COMMENT ON TABLE DOC.doc_document IS 'Canonical document root with optional owner tenant and mandatory visibility';
COMMENT ON TABLE DOC.doc_content IS 'Stored payload variants for a canonical document';
COMMENT ON TABLE DOC.doc_text_representation IS 'Search-oriented text representations derived from document content';
COMMENT ON TABLE DOC.doc_embedding IS 'Embedding lifecycle separated from document structure';
