-- V3Q.AI PostgreSQL Schema Setup
-- Fully idempotent — safe to run multiple times.
-- Three schemas: v3q_config (metadata), v3q_data (synced/imported data), v3q_results (query results)

-- ============================================================
-- Schema: v3q_config — system configuration and metadata
-- ============================================================
CREATE SCHEMA IF NOT EXISTS v3q_config;

-- Registered database connections
CREATE TABLE IF NOT EXISTS v3q_config.database_connections (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name            TEXT NOT NULL UNIQUE,
    db_type         TEXT NOT NULL CHECK (db_type IN ('sqlserver', 'sybase', 'hana', 'postgresql', 'mysql', 'oracle', 'db2')),
    source_system   TEXT CHECK (source_system IN ('sap_ecc', 'sap_b1', 'hubspot', 'infor', 'excel', 'other')),
    connection_string TEXT NOT NULL,
    is_active       BOOLEAN NOT NULL DEFAULT true,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at      TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- Registered tables (from remote DBs, synced tables, or Excel imports)
CREATE TABLE IF NOT EXISTS v3q_config.table_registry (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    connection_id   UUID REFERENCES v3q_config.database_connections(id) ON DELETE CASCADE,
    source_schema   TEXT,
    source_table    TEXT NOT NULL,
    local_table     TEXT,
    display_name    TEXT,
    business_description TEXT,
    source_system   TEXT,
    row_count       BIGINT,
    is_synced       BOOLEAN NOT NULL DEFAULT false,
    sync_schedule   TEXT,
    last_synced_at  TIMESTAMPTZ,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
    UNIQUE(connection_id, source_schema, source_table)
);

-- Column metadata for registered tables
CREATE TABLE IF NOT EXISTS v3q_config.column_registry (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    table_id        UUID NOT NULL REFERENCES v3q_config.table_registry(id) ON DELETE CASCADE,
    column_name     TEXT NOT NULL,
    data_type       TEXT NOT NULL,
    nullable        BOOLEAN NOT NULL DEFAULT true,
    is_primary_key  BOOLEAN NOT NULL DEFAULT false,
    max_length      INTEGER,
    numeric_precision INTEGER,
    numeric_scale   INTEGER,
    display_name    TEXT,
    business_description TEXT,
    sample_values   TEXT,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
    UNIQUE(table_id, column_name)
);

-- Cross-database join definitions
CREATE TABLE IF NOT EXISTS v3q_config.cross_db_joins (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    left_table_id   UUID NOT NULL REFERENCES v3q_config.table_registry(id) ON DELETE CASCADE,
    left_column     TEXT NOT NULL,
    right_table_id  UUID NOT NULL REFERENCES v3q_config.table_registry(id) ON DELETE CASCADE,
    right_column    TEXT NOT NULL,
    join_type       TEXT NOT NULL DEFAULT 'INNER' CHECK (join_type IN ('INNER', 'LEFT', 'RIGHT', 'FULL')),
    confidence      REAL NOT NULL DEFAULT 1.0,
    is_auto_detected BOOLEAN NOT NULL DEFAULT false,
    description     TEXT,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- Successful query patterns for learning
CREATE TABLE IF NOT EXISTS v3q_config.query_patterns (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    nl_question     TEXT NOT NULL,
    sql_template    TEXT NOT NULL,
    tables_used     TEXT[] NOT NULL,
    success_count   INTEGER NOT NULL DEFAULT 1,
    last_used_at    TIMESTAMPTZ NOT NULL DEFAULT now(),
    created_at      TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- Audit log for all Claude API interactions
CREATE TABLE IF NOT EXISTS v3q_config.audit_log (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    event_type      TEXT NOT NULL,
    direction       TEXT NOT NULL CHECK (direction IN ('outbound', 'inbound')),
    payload_hash    TEXT NOT NULL,
    payload_size    INTEGER NOT NULL,
    contains_data   BOOLEAN NOT NULL DEFAULT false,
    metadata        JSONB,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- Application settings (key-value)
CREATE TABLE IF NOT EXISTS v3q_config.app_settings (
    key             TEXT PRIMARY KEY,
    value           JSONB NOT NULL,
    updated_at      TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- Seed domain knowledge (SAP-specific learnings)
INSERT INTO v3q_config.app_settings (key, value) VALUES
('domain_knowledge', '[
  "The SAP installation uses Sybase as its database (not HANA). Sybase has a non-standard query language.",
  "Empty fields in Sybase are often NOT NULL but contain one or more space characters instead. Always check for both NULL and TRIM() when filtering.",
  "DUNS numbers are NOT always 9 digits — some are shorter or longer. Do not validate by length alone.",
  "For SUPPLIERS (LFA1/LIFNR tables): the DUNS number column is called KRAUS.",
  "For CUSTOMERS (KNA1/KUNNR tables): the DUNS number column is called LOCCO.",
  "The SAP installation has multiple mandants (clients). Only Mandant 001 (MANDT=001) is relevant for analysis.",
  "When querying suppliers: filtering by MANDT=001 reduces from ~88k to ~37k records. Always filter by MANDT=001.",
  "Common SAP column abbreviations: MANDT=Client/Mandant, BUKRS=Company Code, LIFNR=Vendor Number, KUNNR=Customer Number, VBELN=Document Number, ERDAT=Created Date, AEDAT=Changed Date, NETWR=Net Value, WAERS=Currency",
  "SAP table naming: LFA1=Vendor Master, KNA1=Customer Master, EKKO=Purchase Order Header, EKPO=Purchase Order Item, VBAK=Sales Order Header, VBAP=Sales Order Item, BSEG=Accounting Document Segment, BKPF=Accounting Document Header"
]')
ON CONFLICT (key) DO NOTHING;

-- ============================================================
-- Schema: v3q_data — synced and imported data tables live here
-- ============================================================
CREATE SCHEMA IF NOT EXISTS v3q_data;

-- Excel import tracking
CREATE TABLE IF NOT EXISTS v3q_data.excel_imports (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    original_filename TEXT NOT NULL,
    sheet_name      TEXT,
    local_table     TEXT NOT NULL UNIQUE,
    row_count       INTEGER,
    column_count    INTEGER,
    column_types    JSONB,
    imported_at     TIMESTAMPTZ NOT NULL DEFAULT now(),
    imported_by     TEXT
);

-- ============================================================
-- Schema: v3q_results — query results and conversations
-- ============================================================
CREATE SCHEMA IF NOT EXISTS v3q_results;

-- Conversations
CREATE TABLE IF NOT EXISTS v3q_results.conversations (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    title           TEXT,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at      TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- Messages within conversations
CREATE TABLE IF NOT EXISTS v3q_results.messages (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    conversation_id UUID NOT NULL REFERENCES v3q_results.conversations(id) ON DELETE CASCADE,
    role            TEXT NOT NULL CHECK (role IN ('user', 'assistant', 'system')),
    content         TEXT NOT NULL,
    sql_query       TEXT,
    has_results     BOOLEAN NOT NULL DEFAULT false,
    result_table    TEXT,
    result_csv_path TEXT,
    row_count       INTEGER,
    chart_config    JSONB,
    execution_time_ms INTEGER,
    model_used      TEXT,
    tokens_used     INTEGER,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- Indexes (IF NOT EXISTS requires PG 9.5+)
CREATE INDEX IF NOT EXISTS idx_messages_conversation ON v3q_results.messages(conversation_id, created_at);
CREATE INDEX IF NOT EXISTS idx_audit_log_created ON v3q_config.audit_log(created_at);
CREATE INDEX IF NOT EXISTS idx_query_patterns_tables ON v3q_config.query_patterns USING GIN(tables_used);
CREATE INDEX IF NOT EXISTS idx_table_registry_connection ON v3q_config.table_registry(connection_id);

-- ============================================================
-- Notification System Tables
-- ============================================================

-- Which columns to monitor per table during sync
CREATE TABLE IF NOT EXISTS v3q_config.watch_columns (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    table_id        UUID NOT NULL REFERENCES v3q_config.table_registry(id) ON DELETE CASCADE,
    column_name     TEXT NOT NULL,
    is_status_column BOOLEAN NOT NULL DEFAULT false,
    is_key_column   BOOLEAN NOT NULL DEFAULT false,
    is_doc_number_column BOOLEAN NOT NULL DEFAULT false,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
    UNIQUE(table_id, column_name)
);

-- Migration: add is_doc_number_column if missing (idempotent)
ALTER TABLE v3q_config.watch_columns ADD COLUMN IF NOT EXISTS is_doc_number_column BOOLEAN NOT NULL DEFAULT false;

-- Configurable status flows per document type
CREATE TABLE IF NOT EXISTS v3q_config.status_stage_definitions (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    doc_type        TEXT NOT NULL,
    source_system   TEXT NOT NULL,
    stage_order     INTEGER NOT NULL,
    stage_key       TEXT NOT NULL,
    stage_label     TEXT NOT NULL,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
    UNIQUE(doc_type, source_system, stage_order)
);

-- Notification subscriptions — who gets notified about what
CREATE TABLE IF NOT EXISTS v3q_config.subscriptions (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    email           TEXT NOT NULL,
    doc_number      TEXT NOT NULL,
    chain_id        UUID,
    source_system   TEXT,
    doc_type        TEXT,
    notify_on_stages TEXT[],
    is_active       BOOLEAN NOT NULL DEFAULT true,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at      TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX IF NOT EXISTS idx_subscriptions_doc ON v3q_config.subscriptions(doc_number);
CREATE INDEX IF NOT EXISTS idx_subscriptions_email ON v3q_config.subscriptions(email);

-- Every detected change from sync
CREATE TABLE IF NOT EXISTS v3q_data.sync_events (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    table_id        UUID NOT NULL,
    local_table     TEXT NOT NULL,
    row_key         JSONB NOT NULL,
    column_name     TEXT NOT NULL,
    old_value       TEXT,
    new_value       TEXT,
    change_type     TEXT NOT NULL CHECK (change_type IN ('changed', 'added', 'removed')),
    chain_id        UUID,
    doc_number      TEXT,
    processed       BOOLEAN NOT NULL DEFAULT false,
    detected_at     TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX IF NOT EXISTS idx_sync_events_unprocessed ON v3q_data.sync_events(processed) WHERE NOT processed;
CREATE INDEX IF NOT EXISTS idx_sync_events_doc ON v3q_data.sync_events(doc_number);
CREATE INDEX IF NOT EXISTS idx_sync_events_table ON v3q_data.sync_events(table_id, detected_at);

-- Audit trail for sent notifications
CREATE TABLE IF NOT EXISTS v3q_config.notification_log (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    subscription_id UUID REFERENCES v3q_config.subscriptions(id) ON DELETE SET NULL,
    event_id        UUID,
    email           TEXT NOT NULL,
    doc_number      TEXT NOT NULL,
    subject         TEXT NOT NULL,
    status          TEXT NOT NULL DEFAULT 'pending' CHECK (status IN ('pending', 'sent', 'failed')),
    error_message   TEXT,
    retry_count     INTEGER NOT NULL DEFAULT 0,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
    sent_at         TIMESTAMPTZ
);

CREATE INDEX IF NOT EXISTS idx_notification_log_doc ON v3q_config.notification_log(doc_number);
CREATE INDEX IF NOT EXISTS idx_notification_log_status ON v3q_config.notification_log(status);

-- SMTP and notification settings (key-value like app_settings)
CREATE TABLE IF NOT EXISTS v3q_config.notification_settings (
    key             TEXT PRIMARY KEY,
    value           JSONB NOT NULL,
    updated_at      TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- Seed default notification settings
INSERT INTO v3q_config.notification_settings (key, value) VALUES
('smtp', '{"host": "", "port": 587, "username": "", "password": "", "use_tls": true, "from_email": "", "from_name": "V3Q.AI Notifications"}'::jsonb),
('global', '{"enabled": false, "max_retries": 3, "batch_size": 50}'::jsonb)
ON CONFLICT (key) DO NOTHING;

-- Sync job tracking — persistent state for long-running sync operations
CREATE TABLE IF NOT EXISTS v3q_config.sync_jobs (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    connection_id   UUID REFERENCES v3q_config.database_connections(id) ON DELETE SET NULL,
    connection_name TEXT NOT NULL,
    db_type         TEXT NOT NULL,
    source_schema   TEXT NOT NULL,
    source_table    TEXT NOT NULL,
    local_table     TEXT,
    status          TEXT NOT NULL DEFAULT 'pending'
                    CHECK (status IN ('pending', 'running', 'completed', 'failed', 'cancelled')),
    phase           TEXT,                   -- current sync phase: 'schema_read', 'staging', 'fetching', 'inserting', 'registering', 'enriching'
    phase_detail    TEXT,                   -- human-readable progress message
    rows_fetched    BIGINT NOT NULL DEFAULT 0,
    rows_inserted   BIGINT NOT NULL DEFAULT 0,
    total_columns   INTEGER,
    batches_done    INTEGER NOT NULL DEFAULT 0,
    fetch_rate      REAL,                   -- rows/sec from remote
    insert_rate     REAL,                   -- rows/sec into local PG
    error_message   TEXT,
    started_at      TIMESTAMPTZ,
    finished_at     TIMESTAMPTZ,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at      TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX IF NOT EXISTS idx_sync_jobs_status ON v3q_config.sync_jobs(status);
CREATE INDEX IF NOT EXISTS idx_sync_jobs_created ON v3q_config.sync_jobs(created_at DESC);

-- ============================================================
-- Migration: expand db_type CHECK constraint for existing DBs
-- ============================================================
DO $$
BEGIN
    IF EXISTS (
        SELECT 1 FROM information_schema.table_constraints
        WHERE table_schema = 'v3q_config'
          AND table_name = 'database_connections'
          AND constraint_type = 'CHECK'
          AND constraint_name = 'database_connections_db_type_check'
    ) THEN
        ALTER TABLE v3q_config.database_connections
            DROP CONSTRAINT database_connections_db_type_check;
    END IF;

    ALTER TABLE v3q_config.database_connections
        ADD CONSTRAINT database_connections_db_type_check
        CHECK (db_type IN ('sqlserver', 'sybase', 'hana', 'postgresql', 'mysql', 'oracle', 'db2'));
EXCEPTION
    WHEN duplicate_object THEN NULL;
END $$;
