-- ============================================================================
-- cache_schema.sql — Schema Tabella Cache per Supabase A
-- 
-- Eseguire su Supabase A (nodo analytics/cache) per creare la tabella cache
-- ============================================================================

-- Tabella Cache Entries
CREATE TABLE IF NOT EXISTS cache_entries (
    id BIGSERIAL PRIMARY KEY,
    cache_key VARCHAR(32) NOT NULL UNIQUE,
    strategy VARCHAR(50) NOT NULL,
    identifier TEXT NOT NULL,
    value TEXT NOT NULL,
    ttl_seconds INTEGER DEFAULT 3600,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    expires_at TIMESTAMP WITH TIME ZONE NOT NULL,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    
    -- Indici per performance
    CONSTRAINT cache_key_idx UNIQUE (cache_key),
    CONSTRAINT strategy_idx ON strategy,
    CONSTRAINT expires_at_idx ON expires_at
);

-- Indice su expires_at per pulizia automatica
CREATE INDEX IF NOT EXISTS idx_cache_expires_at ON cache_entries(expires_at);

-- Indice su strategy per query veloci per strategia
CREATE INDEX IF NOT EXISTS idx_cache_strategy ON cache_entries(strategy);

-- Indice composito per query veloci (strategy + identifier)
CREATE INDEX IF NOT EXISTS idx_cache_strategy_identifier ON cache_entries(strategy, identifier);

-- Tabella Statistiche Cache
CREATE TABLE IF NOT EXISTS cache_stats (
    id BIGSERIAL PRIMARY KEY,
    hits BIGINT DEFAULT 0,
    misses BIGINT DEFAULT 0,
    sets BIGINT DEFAULT 0,
    deletes BIGINT DEFAULT 0,
    evictions BIGINT DEFAULT 0,
    recorded_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    
    CONSTRAINT cache_stats_pkey PRIMARY KEY (id)
);

-- Tabella Audit Cache (opzionale)
CREATE TABLE IF NOT EXISTS cache_audit (
    id BIGSERIAL PRIMARY KEY,
    action VARCHAR(50) NOT NULL,  -- 'GET', 'SET', 'DELETE', 'EVICT'
    strategy VARCHAR(50) NOT NULL,
    identifier TEXT NOT NULL,
    cache_key VARCHAR(32),
    status VARCHAR(20) NOT NULL,  -- 'HIT', 'MISS', 'SUCCESS', 'FAILURE'
    duration_ms INTEGER,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Indice su created_at per query audit veloci
CREATE INDEX IF NOT EXISTS idx_cache_audit_created_at ON cache_audit(created_at DESC);

-- Indice su action per filtrare per tipo operazione
CREATE INDEX IF NOT EXISTS idx_cache_audit_action ON cache_audit(action);

-- ============================================================================
-- Funzione: Pulizia automatica entry scaduti
-- ============================================================================
CREATE OR REPLACE FUNCTION cleanup_expired_cache()
RETURNS TABLE(deleted_count INTEGER) AS $$
BEGIN
    DELETE FROM cache_entries WHERE expires_at < NOW();
    RETURN QUERY SELECT COUNT(*)::INTEGER FROM cache_entries WHERE expires_at < NOW();
END;
$$ LANGUAGE plpgsql;

-- ============================================================================
-- Trigger: Aggiorna updated_at su UPDATE
-- ============================================================================
CREATE OR REPLACE FUNCTION update_cache_updated_at()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER cache_entries_updated_at_trigger
BEFORE UPDATE ON cache_entries
FOR EACH ROW
EXECUTE FUNCTION update_cache_updated_at();

-- ============================================================================
-- View: Statistiche Cache Attuali
-- ============================================================================
CREATE OR REPLACE VIEW cache_stats_view AS
SELECT
    COUNT(*) as total_entries,
    COUNT(CASE WHEN expires_at > NOW() THEN 1 END) as valid_entries,
    COUNT(CASE WHEN expires_at <= NOW() THEN 1 END) as expired_entries,
    COUNT(DISTINCT strategy) as unique_strategies,
    MIN(created_at) as oldest_entry,
    MAX(created_at) as newest_entry,
    AVG(ttl_seconds) as avg_ttl_seconds
FROM cache_entries;

-- ============================================================================
-- View: Hit Rate (ultimi 24 ore)
-- ============================================================================
CREATE OR REPLACE VIEW cache_hit_rate_24h AS
SELECT
    COUNT(CASE WHEN status = 'HIT' THEN 1 END) as hits,
    COUNT(CASE WHEN status = 'MISS' THEN 1 END) as misses,
    COUNT(*) as total_requests,
    ROUND(
        COUNT(CASE WHEN status = 'HIT' THEN 1 END)::NUMERIC / 
        NULLIF(COUNT(*), 0) * 100,
        2
    ) as hit_rate_percent
FROM cache_audit
WHERE created_at > NOW() - INTERVAL '24 hours';

-- ============================================================================
-- Policy RLS (Row Level Security) — Opzionale
-- ============================================================================
-- Abilita RLS sulla tabella cache_entries
ALTER TABLE cache_entries ENABLE ROW LEVEL SECURITY;

-- Policy: Tutti possono leggere cache (read-only per A)
CREATE POLICY "Allow read access to cache" ON cache_entries
    FOR SELECT USING (true);

-- Policy: Solo service role può scrivere/modificare cache
CREATE POLICY "Allow write access to cache (service role only)" ON cache_entries
    FOR INSERT WITH CHECK (current_user = 'postgres');

CREATE POLICY "Allow update access to cache (service role only)" ON cache_entries
    FOR UPDATE USING (current_user = 'postgres');

CREATE POLICY "Allow delete access to cache (service role only)" ON cache_entries
    FOR DELETE USING (current_user = 'postgres');

-- ============================================================================
-- Commenti Documentazione
-- ============================================================================
COMMENT ON TABLE cache_entries IS 'Archivio cache distribuito su Supabase A (read-heavy, non-critical)';
COMMENT ON COLUMN cache_entries.cache_key IS 'Chiave cache univoca (SHA256 hash di strategy:identifier)';
COMMENT ON COLUMN cache_entries.strategy IS 'Strategia cache: query, memory, embedding, conversation, analytics';
COMMENT ON COLUMN cache_entries.identifier IS 'Identificatore univoco per il valore (es. session_id, query_hash)';
COMMENT ON COLUMN cache_entries.value IS 'Valore memorizzato in cache (JSON serializzato)';
COMMENT ON COLUMN cache_entries.ttl_seconds IS 'Time-to-live in secondi (tempo di scadenza)';
COMMENT ON COLUMN cache_entries.expires_at IS 'Timestamp di scadenza (created_at + ttl_seconds)';

COMMENT ON TABLE cache_stats IS 'Statistiche aggregate del cache layer';
COMMENT ON TABLE cache_audit IS 'Audit log di tutte le operazioni cache (GET, SET, DELETE, EVICT)';

COMMENT ON FUNCTION cleanup_expired_cache() IS 'Rimuove entry scaduti dalla cache';
COMMENT ON VIEW cache_stats_view IS 'Statistiche attuali della cache (totale, valide, scadute, ecc.)';
COMMENT ON VIEW cache_hit_rate_24h IS 'Hit rate del cache nelle ultime 24 ore';