-- Word tracking & sentiment analytics for NAS documents

CREATE TABLE IF NOT EXISTS document_analytics (
    id SERIAL PRIMARY KEY,
    storage_path TEXT UNIQUE NOT NULL,
    filename VARCHAR(512),
    doc_type VARCHAR(64) DEFAULT 'general',
    language VARCHAR(16),
    word_count INT DEFAULT 0,
    unique_lemmas INT DEFAULT 0,
    sentence_count INT DEFAULT 0,
    sentiment_compound FLOAT,
    sentiment_positive FLOAT,
    sentiment_negative FLOAT,
    sentiment_neutral FLOAT,
    sentiment_subjectivity FLOAT,
    sentiment_label VARCHAR(32),
    extraction_method VARCHAR(64) DEFAULT 'standard',
    file_sig VARCHAR(64),
    analyzed_at TIMESTAMPTZ DEFAULT NOW(),
    metadata JSONB DEFAULT '{}'
);

CREATE TABLE IF NOT EXISTS document_word_counts (
    id SERIAL PRIMARY KEY,
    storage_path TEXT NOT NULL,
    lemma VARCHAR(128) NOT NULL,
    token VARCHAR(128),
    pos_tag VARCHAR(16),
    count INT DEFAULT 1,
    is_stopword BOOLEAN DEFAULT FALSE,
    language VARCHAR(16),
    updated_at TIMESTAMPTZ DEFAULT NOW(),
    UNIQUE (storage_path, lemma)
);

CREATE INDEX IF NOT EXISTS idx_doc_word_lemma ON document_word_counts (lemma);
CREATE INDEX IF NOT EXISTS idx_doc_word_path ON document_word_counts (storage_path);
CREATE INDEX IF NOT EXISTS idx_doc_word_stop ON document_word_counts (is_stopword);
CREATE INDEX IF NOT EXISTS idx_doc_analytics_sentiment ON document_analytics (sentiment_label);
CREATE INDEX IF NOT EXISTS idx_doc_analytics_analyzed ON document_analytics (analyzed_at DESC);

CREATE OR REPLACE VIEW global_word_frequency AS
SELECT
    lemma,
    MAX(token) AS sample_token,
    SUM(count) AS total_count,
    COUNT(DISTINCT storage_path) AS document_count
FROM document_word_counts
GROUP BY lemma;