CREATE EXTENSION IF NOT EXISTS vector;

CREATE TABLE IF NOT EXISTS schema_migrations (
    version text PRIMARY KEY,
    applied_at timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS knowledge_documents (
    id uuid PRIMARY KEY,
    external_id text NOT NULL UNIQUE,
    title text NOT NULL,
    language text NOT NULL CHECK (language IN ('ru','kk')),
    region_code text NOT NULL DEFAULT 'global' CHECK (length(region_code) > 0),
    category text NOT NULL DEFAULT 'general',
    source_type text NOT NULL,
    source_uri text,
    source_hash text NOT NULL,
    status text NOT NULL DEFAULT 'published' CHECK (status IN ('draft','published','archived')),
    content text NOT NULL,
    question text,
    short_answer text,
    full_answer text,
    keywords text[] NOT NULL DEFAULT '{}',
    source text,
    metadata jsonb NOT NULL DEFAULT '{}',
    valid_from timestamptz,
    valid_to timestamptz,
    priority int NOT NULL DEFAULT 0,
    created_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS knowledge_chunks (
    id uuid PRIMARY KEY,
    document_id uuid NOT NULL REFERENCES knowledge_documents(id) ON DELETE CASCADE,
    chunk_index int NOT NULL,
    language text NOT NULL CHECK (language IN ('ru','kk')),
    region_code text NOT NULL,
    title text NOT NULL,
    content text NOT NULL,
    content_hash text NOT NULL,
    token_estimate int NOT NULL DEFAULT 0,
    char_count int NOT NULL DEFAULT 0,
    embedding vector(1536),
    embedding_model text NOT NULL DEFAULT 'fake',
    embedding_provider text NOT NULL DEFAULT 'fake',
    metadata jsonb NOT NULL DEFAULT '{}',
    tsv tsvector,
    created_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now(),
    UNIQUE(document_id, chunk_index)
);

CREATE TABLE IF NOT EXISTS knowledge_ingest_runs (
    id uuid PRIMARY KEY,
    source_path text NOT NULL,
    status text NOT NULL,
    documents_seen int NOT NULL DEFAULT 0,
    documents_ingested int NOT NULL DEFAULT 0,
    chunks_created int NOT NULL DEFAULT 0,
    skipped int NOT NULL DEFAULT 0,
    error text,
    started_at timestamptz NOT NULL DEFAULT now(),
    finished_at timestamptz
);

CREATE TABLE IF NOT EXISTS knowledge_search_logs (
    id uuid PRIMARY KEY,
    call_id text,
    query text NOT NULL,
    language text NOT NULL,
    region_code text NOT NULL,
    result_count int NOT NULL,
    top_score double precision,
    cross_language_fallback_used boolean NOT NULL DEFAULT false,
    created_at timestamptz NOT NULL DEFAULT now()
);