-- ===========================================
-- L'dicO - Schema PostgreSQL
-- Base de données Search Intelligence
-- ===========================================

-- Extensions
CREATE EXTENSION IF NOT EXISTS vector;
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

-- ============================================
-- TABLE: search_query
-- Stocke chaque recherche (anonymisée)
-- ============================================
CREATE TABLE search_query (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),

    -- Classification IA (Mistral)
    detected_theme VARCHAR(50),
    detected_intent VARCHAR(30),
    has_local_intent BOOLEAN DEFAULT FALSE,
    detected_location VARCHAR(100),
    classification_confidence REAL,

    -- Contexte anonymisé (pas de user_id !)
    keywords JSONB,
    user_plan VARCHAR(20),
    mode_slug VARCHAR(50),
    language VARCHAR(5) DEFAULT 'fr',

    -- Métriques
    query_token_count INTEGER,
    results_count INTEGER DEFAULT 0,
    results_used_by_ai INTEGER DEFAULT 0,
    search_duration_ms INTEGER,

    -- Métadonnées techniques
    searxng_engines_used JSONB,
    api_version VARCHAR(10) DEFAULT '1.0'
);

-- ============================================
-- TABLE: search_result
-- Stocke chaque résultat retourné par SearXNG
-- ============================================
CREATE TABLE search_result (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    query_id UUID NOT NULL REFERENCES search_query(id) ON DELETE CASCADE,
    created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),

    -- Source
    domain VARCHAR(255) NOT NULL,
    url_hash VARCHAR(64) NOT NULL,
    source_engine VARCHAR(50),

    -- Ranking
    rank_position INTEGER NOT NULL,
    was_used_by_ai BOOLEAN DEFAULT FALSE,

    -- Contenu
    title TEXT,
    snippet TEXT,
    snippet_token_count INTEGER,

    -- Phase 2: Embedding vectoriel (préparé)
    -- snippet_embedding VECTOR(1536),

    -- Métadonnées
    content_type VARCHAR(50),
    result_score REAL
);

-- ============================================
-- TABLE: domain_stats
-- Agrégations par domaine/thème (job CRON)
-- ============================================
CREATE TABLE domain_stats (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    domain VARCHAR(255) NOT NULL,
    theme VARCHAR(50) NOT NULL,

    -- Statistiques
    total_appearances INTEGER DEFAULT 0,
    avg_rank REAL,
    times_used_by_ai INTEGER DEFAULT 0,
    ai_usage_rate REAL,

    -- Score calculé
    quality_score REAL,

    -- Période
    period_start DATE NOT NULL,
    period_end DATE NOT NULL,
    updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),

    UNIQUE(domain, theme, period_start)
);

-- ============================================
-- INDEX pour performances
-- ============================================

-- search_query
CREATE INDEX idx_query_created_at ON search_query(created_at DESC);
CREATE INDEX idx_query_theme ON search_query(detected_theme);
CREATE INDEX idx_query_intent ON search_query(detected_intent);
CREATE INDEX idx_query_theme_intent ON search_query(detected_theme, detected_intent);
CREATE INDEX idx_query_plan ON search_query(user_plan);
CREATE INDEX idx_query_location ON search_query(detected_location) WHERE detected_location IS NOT NULL;

-- search_result
CREATE INDEX idx_result_query ON search_result(query_id);
CREATE INDEX idx_result_domain ON search_result(domain);
CREATE INDEX idx_result_used ON search_result(was_used_by_ai) WHERE was_used_by_ai = TRUE;
CREATE INDEX idx_result_domain_used ON search_result(domain, was_used_by_ai);

-- domain_stats
CREATE INDEX idx_stats_domain ON domain_stats(domain);
CREATE INDEX idx_stats_theme ON domain_stats(theme);
CREATE INDEX idx_stats_quality ON domain_stats(quality_score DESC);
CREATE INDEX idx_stats_period ON domain_stats(period_start, period_end);
