-- ============================================================================
-- TOPIC INTEL: Source-Agnostic News Intelligence Platform
-- D1 Database Schema
-- ============================================================================
-- Version: 1.1.0
-- Target: Cloudflare D1 (SQLite-based)
-- ============================================================================

-- ============================================================================
-- MIGRATION 001: Core Domain Tables
-- ============================================================================

-- ----------------------------------------------------------------------------
-- SOURCE_ADAPTERS: Discovery/fetch adapters (Google News, Twitter, Reddit, etc.)
-- ----------------------------------------------------------------------------
-- The crawler is source-agnostic. Each adapter knows how to discover and fetch
-- content from a specific platform. Google News is just the first adapter.
-- ----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS source_adapters (
    id TEXT PRIMARY KEY,                -- 'google_news', 'twitter', 'reddit', 'direct_rss'
    name TEXT NOT NULL,                 -- "Google News", "Twitter/X", "Reddit"
    type TEXT NOT NULL CHECK (type IN ('news_aggregator', 'social', 'wire', 'govt', 'research', 'direct', 'podcast', 'video')),
    is_enabled INTEGER DEFAULT 1,
    priority INTEGER DEFAULT 100,       -- Lower = higher priority for keyword routing
    rate_limit_rpm INTEGER DEFAULT 60,  -- Requests per minute
    rate_limit_concurrent INTEGER DEFAULT 5,
    config TEXT,                        -- JSON: adapter-specific configuration
    created_at TEXT DEFAULT (datetime('now')),
    updated_at TEXT DEFAULT (datetime('now'))
);

-- Seed the initial adapters
INSERT OR IGNORE INTO source_adapters (id, name, type, priority, config) VALUES
    ('google_news', 'Google News', 'news_aggregator', 10, '{"rss_base": "https://news.google.com/rss", "fallbacks": ["zenrows", "rapidapi"]}'),
    ('direct_rss', 'Direct RSS Feeds', 'direct', 20, '{}'),
    ('twitter', 'Twitter/X', 'social', 50, '{"api_version": "v2"}'),
    ('reddit', 'Reddit', 'social', 50, '{}'),
    ('hackernews', 'Hacker News', 'social', 50, '{"api": "firebase"}'),
    ('pr_newswire', 'PR Newswire', 'wire', 30, '{}'),
    ('business_wire', 'Business Wire', 'wire', 30, '{}'),
    ('sec_edgar', 'SEC EDGAR', 'govt', 40, '{}');

-- ----------------------------------------------------------------------------
-- SOURCES: News publishers and content sources (the actual publishers)
-- ----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS sources (
    id TEXT PRIMARY KEY DEFAULT (lower(hex(randomblob(16)))),
    name TEXT NOT NULL,
    domain TEXT NOT NULL UNIQUE,
    type TEXT NOT NULL CHECK (type IN ('news_org', 'blog', 'press_release', 'govt', 'research', 'aggregator', 'social', 'podcast', 'video')),
    authority_score REAL DEFAULT 0.5 CHECK (authority_score >= 0 AND authority_score <= 1),
    political_lean REAL CHECK (political_lean >= -1 AND political_lean <= 1),
    region TEXT, -- JSON array of regions
    topics TEXT, -- JSON array of topics
    fetch_config TEXT, -- JSON: rate limits, selectors, anti-bot handling
    last_crawled_at TEXT,
    is_active INTEGER DEFAULT 1,
    created_at TEXT DEFAULT (datetime('now')),
    updated_at TEXT DEFAULT (datetime('now'))
);

CREATE INDEX idx_sources_domain ON sources(domain);
CREATE INDEX idx_sources_type ON sources(type);
CREATE INDEX idx_sources_authority ON sources(authority_score DESC);
CREATE INDEX idx_sources_active ON sources(is_active) WHERE is_active = 1;

-- ----------------------------------------------------------------------------
-- URLS: Deduplication table for canonical URLs
-- ----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS urls (
    id TEXT PRIMARY KEY DEFAULT (lower(hex(randomblob(16)))),
    url_hash TEXT NOT NULL UNIQUE, -- SHA-256 of normalized URL
    canonical_url TEXT NOT NULL,
    first_seen_at TEXT DEFAULT (datetime('now')),
    last_seen_at TEXT DEFAULT (datetime('now')),
    hit_count INTEGER DEFAULT 1,
    created_at TEXT DEFAULT (datetime('now'))
);

CREATE INDEX idx_urls_hash ON urls(url_hash);

-- ----------------------------------------------------------------------------
-- AUTHORS: Normalized author tracking
-- ----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS authors (
    id TEXT PRIMARY KEY DEFAULT (lower(hex(randomblob(16)))),
    name TEXT NOT NULL,
    aliases TEXT, -- JSON array
    source_ids TEXT, -- JSON array of source UUIDs
    trust_score REAL DEFAULT 0.5 CHECK (trust_score >= 0 AND trust_score <= 1),
    article_count INTEGER DEFAULT 0,
    avg_social_engagement REAL DEFAULT 0,
    political_lean REAL CHECK (political_lean >= -1 AND political_lean <= 1),
    topics TEXT, -- JSON array
    first_seen_at TEXT DEFAULT (datetime('now')),
    last_seen_at TEXT DEFAULT (datetime('now')),
    embedding_id TEXT,
    created_at TEXT DEFAULT (datetime('now')),
    updated_at TEXT DEFAULT (datetime('now'))
);

CREATE INDEX idx_authors_name ON authors(name);
CREATE INDEX idx_authors_trust ON authors(trust_score DESC);

-- ----------------------------------------------------------------------------
-- LOCATIONS: Geographic reference (225K+ entries)
-- ----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS locations (
    id TEXT PRIMARY KEY DEFAULT (lower(hex(randomblob(16)))),
    google_place_id TEXT,
    name TEXT NOT NULL,
    name_variations TEXT, -- JSON array: ["SF", "San Fran", "Bay Area"]
    type TEXT NOT NULL CHECK (type IN ('city', 'state', 'country', 'region', 'landmark', 'address')),
    parent_id TEXT REFERENCES locations(id),
    country_code TEXT, -- ISO 3166-1 alpha-2
    lat REAL,
    lng REAL,
    population INTEGER,
    timezone TEXT,
    embedding_id TEXT,
    created_at TEXT DEFAULT (datetime('now'))
);

CREATE INDEX idx_locations_name ON locations(name);
CREATE INDEX idx_locations_type ON locations(type);
CREATE INDEX idx_locations_country ON locations(country_code);
CREATE INDEX idx_locations_parent ON locations(parent_id);

-- ----------------------------------------------------------------------------
-- ARTICLES: Core content entity
-- ----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS articles (
    id TEXT PRIMARY KEY DEFAULT (lower(hex(randomblob(16)))),
    url_id TEXT REFERENCES urls(id),
    source_id TEXT NOT NULL REFERENCES sources(id),
    canonical_url TEXT NOT NULL UNIQUE,
    url_hash TEXT NOT NULL UNIQUE, -- SHA-256 for fast lookup
    google_news_url TEXT,
    headline TEXT NOT NULL,
    subheadline TEXT,
    body_text TEXT,
    body_html TEXT,
    summary TEXT, -- AI-generated
    author_ids TEXT, -- JSON array of author UUIDs
    published_at TEXT,
    updated_at TEXT,
    captured_at TEXT DEFAULT (datetime('now')),
    language TEXT DEFAULT 'en', -- ISO 639-1
    detected_language TEXT,
    word_count INTEGER DEFAULT 0,
    reading_time_mins INTEGER DEFAULT 0,
    media_refs TEXT, -- JSON array: [{type, url, caption, alt}]
    raw_snapshot_r2_key TEXT,
    embedding_id TEXT,
    processing_status TEXT DEFAULT 'pending' CHECK (processing_status IN ('pending', 'processing', 'complete', 'failed')),
    geo_locations TEXT, -- JSON array of location UUIDs
    primary_location_id TEXT REFERENCES locations(id),
    created_at TEXT DEFAULT (datetime('now'))
);

CREATE INDEX idx_articles_source ON articles(source_id);
CREATE INDEX idx_articles_url_hash ON articles(url_hash);
CREATE INDEX idx_articles_published ON articles(published_at DESC);
CREATE INDEX idx_articles_captured ON articles(captured_at DESC);
CREATE INDEX idx_articles_status ON articles(processing_status);
CREATE INDEX idx_articles_language ON articles(language);
CREATE INDEX idx_articles_embedding ON articles(embedding_id) WHERE embedding_id IS NOT NULL;

-- ----------------------------------------------------------------------------
-- ARTICLE_LOCATIONS: Junction table for geographic extraction
-- ----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS article_locations (
    article_id TEXT NOT NULL REFERENCES articles(id) ON DELETE CASCADE,
    location_id TEXT NOT NULL REFERENCES locations(id),
    mention_count INTEGER DEFAULT 1,
    is_primary INTEGER DEFAULT 0,
    context_type TEXT CHECK (context_type IN ('mentioned', 'about', 'happened_in', 'authored_from')),
    confidence REAL DEFAULT 0.5,
    created_at TEXT DEFAULT (datetime('now')),
    PRIMARY KEY (article_id, location_id)
);

CREATE INDEX idx_article_locations_article ON article_locations(article_id);
CREATE INDEX idx_article_locations_location ON article_locations(location_id);

-- ----------------------------------------------------------------------------
-- STORIES: Emergent narrative clusters
-- ----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS stories (
    id TEXT PRIMARY KEY DEFAULT (lower(hex(randomblob(16)))),
    slug TEXT UNIQUE NOT NULL,
    title TEXT NOT NULL,
    summary TEXT, -- AI-generated, evolving
    status TEXT DEFAULT 'developing' CHECK (status IN ('breaking', 'developing', 'mature', 'archived')),
    importance REAL DEFAULT 0.5 CHECK (importance >= 0 AND importance <= 1),
    first_seen_at TEXT DEFAULT (datetime('now')),
    last_activity_at TEXT DEFAULT (datetime('now')),
    article_count INTEGER DEFAULT 0,
    source_count INTEGER DEFAULT 0,
    primary_entities TEXT, -- JSON array of top entities
    timeline_summary TEXT, -- AI-generated narrative arc
    embedding_id TEXT,
    created_at TEXT DEFAULT (datetime('now')),
    updated_at TEXT DEFAULT (datetime('now'))
);

CREATE INDEX idx_stories_slug ON stories(slug);
CREATE INDEX idx_stories_status ON stories(status);
CREATE INDEX idx_stories_importance ON stories(importance DESC);
CREATE INDEX idx_stories_last_activity ON stories(last_activity_at DESC);

-- ----------------------------------------------------------------------------
-- ARTICLE_STORIES: N:M junction between articles and stories
-- ----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS article_stories (
    article_id TEXT NOT NULL REFERENCES articles(id) ON DELETE CASCADE,
    story_id TEXT NOT NULL REFERENCES stories(id) ON DELETE CASCADE,
    confidence REAL DEFAULT 0.5 CHECK (confidence >= 0 AND confidence <= 1),
    role TEXT DEFAULT 'primary' CHECK (role IN ('primary', 'mention', 'recap', 'analysis')),
    added_by TEXT DEFAULT 'auto_cluster' CHECK (added_by IN ('auto_cluster', 'manual', 'llm')),
    created_at TEXT DEFAULT (datetime('now')),
    PRIMARY KEY (article_id, story_id)
);

CREATE INDEX idx_article_stories_article ON article_stories(article_id);
CREATE INDEX idx_article_stories_story ON article_stories(story_id);
CREATE INDEX idx_article_stories_confidence ON article_stories(confidence DESC);

-- ----------------------------------------------------------------------------
-- ENTITIES: Named entities extracted via NER
-- ----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS entities (
    id TEXT PRIMARY KEY DEFAULT (lower(hex(randomblob(16)))),
    canonical_name TEXT NOT NULL,
    type TEXT NOT NULL CHECK (type IN ('person', 'org', 'location', 'product', 'event', 'concept')),
    aliases TEXT, -- JSON array
    metadata TEXT, -- JSON: type-specific data
    embedding_id TEXT,
    created_at TEXT DEFAULT (datetime('now')),
    updated_at TEXT DEFAULT (datetime('now'))
);

CREATE INDEX idx_entities_name ON entities(canonical_name);
CREATE INDEX idx_entities_type ON entities(type);

-- ----------------------------------------------------------------------------
-- ENTITY_MENTIONS: Junction with context
-- ----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS entity_mentions (
    entity_id TEXT NOT NULL REFERENCES entities(id) ON DELETE CASCADE,
    article_id TEXT NOT NULL REFERENCES articles(id) ON DELETE CASCADE,
    mention_count INTEGER DEFAULT 1,
    sentiment REAL CHECK (sentiment >= -1 AND sentiment <= 1),
    is_primary_subject INTEGER DEFAULT 0,
    context_snippets TEXT, -- JSON array of surrounding text
    created_at TEXT DEFAULT (datetime('now')),
    PRIMARY KEY (entity_id, article_id)
);

CREATE INDEX idx_entity_mentions_entity ON entity_mentions(entity_id);
CREATE INDEX idx_entity_mentions_article ON entity_mentions(article_id);
CREATE INDEX idx_entity_mentions_primary ON entity_mentions(is_primary_subject) WHERE is_primary_subject = 1;

-- ----------------------------------------------------------------------------
-- STORY_TIMELINE: Story development events
-- ----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS story_timeline (
    id TEXT PRIMARY KEY DEFAULT (lower(hex(randomblob(16)))),
    story_id TEXT NOT NULL REFERENCES stories(id) ON DELETE CASCADE,
    timestamp TEXT NOT NULL,
    event_type TEXT CHECK (event_type IN ('development', 'reaction', 'official_statement', 'escalation', 'resolution')),
    summary TEXT NOT NULL,
    article_ids TEXT, -- JSON array of contributing articles
    importance REAL DEFAULT 0.5,
    created_at TEXT DEFAULT (datetime('now'))
);

CREATE INDEX idx_story_timeline_story ON story_timeline(story_id);
CREATE INDEX idx_story_timeline_timestamp ON story_timeline(timestamp DESC);

-- ============================================================================
-- MIGRATION 002: Customer Domain Tables
-- ============================================================================

-- ----------------------------------------------------------------------------
-- SUBSCRIPTION_TIERS: Admin-configurable subscription limits
-- ----------------------------------------------------------------------------
-- Tiers are NOT hardcoded. Admin console can modify limits at any time.
-- ----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS subscription_tiers (
    id TEXT PRIMARY KEY,                -- 'free', 'starter', 'pro', 'business', 'enterprise', 'unlimited'
    name TEXT NOT NULL,                 -- Display name
    description TEXT,

    -- Keyword limits
    max_keywords INTEGER NOT NULL,      -- Max keywords customer can subscribe to
    max_topics INTEGER NOT NULL,        -- Max topics (keyword bundles)
    max_custom_taxonomies INTEGER DEFAULT 0, -- Custom taxonomy labels

    -- API limits
    api_calls_per_day INTEGER NOT NULL,
    api_calls_per_minute INTEGER DEFAULT 60,

    -- Data retention
    article_retention_days INTEGER NOT NULL,
    export_enabled INTEGER DEFAULT 0,
    webhook_enabled INTEGER DEFAULT 0,

    -- Features
    search_enabled INTEGER DEFAULT 1,
    briefings_enabled INTEGER DEFAULT 0,
    qa_enabled INTEGER DEFAULT 0,       -- RAG Q&A

    -- Pricing (for display, actual billing handled externally)
    price_monthly_cents INTEGER DEFAULT 0,
    price_yearly_cents INTEGER DEFAULT 0,

    -- Tier ordering for upgrades
    sort_order INTEGER DEFAULT 0,
    is_public INTEGER DEFAULT 1,        -- Show on pricing page
    is_active INTEGER DEFAULT 1,

    created_at TEXT DEFAULT (datetime('now')),
    updated_at TEXT DEFAULT (datetime('now'))
);

-- Seed default tiers (admin can modify via console)
INSERT OR IGNORE INTO subscription_tiers (id, name, sort_order, max_keywords, max_topics, max_custom_taxonomies, api_calls_per_day, article_retention_days, export_enabled, webhook_enabled, briefings_enabled, qa_enabled, price_monthly_cents) VALUES
    ('free', 'Free', 0, 5, 1, 0, 100, 7, 0, 0, 0, 0, 0),
    ('starter', 'Starter', 1, 25, 5, 0, 1000, 14, 0, 1, 0, 0, 2900),
    ('pro', 'Pro', 2, 100, 20, 5, 10000, 30, 1, 1, 1, 0, 9900),
    ('business', 'Business', 3, 500, 50, 20, 50000, 60, 1, 1, 1, 1, 29900),
    ('enterprise', 'Enterprise', 4, 1000, 100, 100, 200000, 90, 1, 1, 1, 1, 99900),
    ('unlimited', 'Unlimited', 5, -1, -1, -1, -1, 365, 1, 1, 1, 1, 0);  -- -1 = unlimited, price negotiated

-- ----------------------------------------------------------------------------
-- CUSTOMERS: End users of the platform
-- ----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS customers (
    id TEXT PRIMARY KEY DEFAULT (lower(hex(randomblob(16)))),
    email TEXT NOT NULL UNIQUE,
    name TEXT NOT NULL,
    tier_id TEXT DEFAULT 'free' REFERENCES subscription_tiers(id),

    -- Override limits (NULL = use tier defaults, set value = override)
    override_max_keywords INTEGER,
    override_max_topics INTEGER,
    override_api_calls_per_day INTEGER,
    override_article_retention_days INTEGER,

    settings TEXT DEFAULT '{}', -- JSON
    is_active INTEGER DEFAULT 1,
    created_at TEXT DEFAULT (datetime('now')),
    updated_at TEXT DEFAULT (datetime('now'))
);

CREATE INDEX idx_customers_email ON customers(email);
CREATE INDEX idx_customers_tier ON customers(tier_id);
CREATE INDEX idx_customers_active ON customers(is_active) WHERE is_active = 1;

-- ----------------------------------------------------------------------------
-- KEYWORDS: Global keyword pool (shared across all customers)
-- ----------------------------------------------------------------------------
-- KEY INSIGHT: 1000 customers wanting "bitcoin" = 1 crawl, not 1000 crawls
-- We dedupe at keyword level and fan out at match level
-- ----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS keywords (
    id TEXT PRIMARY KEY DEFAULT (lower(hex(randomblob(16)))),
    keyword TEXT NOT NULL,
    keyword_hash TEXT NOT NULL UNIQUE, -- SHA-256 for fast lookup
    keyword_normalized TEXT NOT NULL, -- lowercase, trimmed, normalized
    language TEXT DEFAULT 'en', -- ISO 639-1
    region TEXT DEFAULT 'US', -- Default region

    -- Source adapter routing (which adapters should crawl this keyword)
    source_adapters TEXT DEFAULT '["google_news"]', -- JSON array of adapter IDs
    primary_adapter TEXT DEFAULT 'google_news' REFERENCES source_adapters(id),

    -- Crawl scheduling (dynamic based on rate of change)
    crawl_tier TEXT DEFAULT 'normal' CHECK (crawl_tier IN ('hot', 'warm', 'normal', 'cold', 'frozen')),
    crawl_interval_mins INTEGER DEFAULT 60, -- Current crawl interval
    min_crawl_interval_mins INTEGER DEFAULT 15, -- Floor (hot keywords)
    max_crawl_interval_mins INTEGER DEFAULT 1440, -- Ceiling (24h for frozen)

    -- Rate of change metrics (determines crawl frequency)
    articles_last_24h INTEGER DEFAULT 0,
    articles_last_7d INTEGER DEFAULT 0,
    rate_of_change REAL DEFAULT 0, -- velocity: positive = accelerating, negative = decelerating
    velocity_trend TEXT DEFAULT 'stable' CHECK (velocity_trend IN ('accelerating', 'stable', 'decelerating')),

    -- External data enrichment
    search_volume_monthly INTEGER, -- From DataForSEO
    backlink_velocity REAL, -- Rate of new backlinks
    social_velocity REAL, -- Rate of social mentions

    -- Subscription tracking
    subscriber_count INTEGER DEFAULT 0, -- Denormalized: customers subscribed
    is_active INTEGER DEFAULT 1, -- Only crawl if subscriber_count > 0

    -- Timestamps
    last_crawled_at TEXT,
    next_crawl_at TEXT,
    last_article_found_at TEXT,
    created_at TEXT DEFAULT (datetime('now')),
    updated_at TEXT DEFAULT (datetime('now'))
);

CREATE INDEX idx_keywords_hash ON keywords(keyword_hash);
CREATE INDEX idx_keywords_normalized ON keywords(keyword_normalized);
CREATE INDEX idx_keywords_active ON keywords(is_active) WHERE is_active = 1 AND subscriber_count > 0;
CREATE INDEX idx_keywords_next_crawl ON keywords(next_crawl_at) WHERE is_active = 1 AND subscriber_count > 0;
CREATE INDEX idx_keywords_tier ON keywords(crawl_tier);
CREATE INDEX idx_keywords_subscribers ON keywords(subscriber_count DESC);

-- ----------------------------------------------------------------------------
-- CUSTOMER_KEYWORD_SUBSCRIPTIONS: Links customers to global keywords
-- ----------------------------------------------------------------------------
-- This is the fan-out table: when an article matches a keyword,
-- we notify all customers subscribed to that keyword
-- ----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS customer_keyword_subscriptions (
    id TEXT PRIMARY KEY DEFAULT (lower(hex(randomblob(16)))),
    customer_id TEXT NOT NULL REFERENCES customers(id) ON DELETE CASCADE,
    keyword_id TEXT NOT NULL REFERENCES keywords(id) ON DELETE CASCADE,

    -- Customer-specific settings for this keyword
    notify_enabled INTEGER DEFAULT 1,
    notify_threshold REAL DEFAULT 0.5, -- Minimum relevance score to notify
    priority TEXT DEFAULT 'normal' CHECK (priority IN ('critical', 'high', 'normal', 'low')),

    -- Optional: link to a topic (keyword can be subscribed directly or via topic)
    topic_id TEXT REFERENCES topics(id) ON DELETE SET NULL,

    -- Stats
    articles_matched INTEGER DEFAULT 0,
    last_matched_at TEXT,

    created_at TEXT DEFAULT (datetime('now')),
    updated_at TEXT DEFAULT (datetime('now')),
    UNIQUE(customer_id, keyword_id)
);

CREATE INDEX idx_cks_customer ON customer_keyword_subscriptions(customer_id);
CREATE INDEX idx_cks_keyword ON customer_keyword_subscriptions(keyword_id);
CREATE INDEX idx_cks_topic ON customer_keyword_subscriptions(topic_id) WHERE topic_id IS NOT NULL;
CREATE INDEX idx_cks_notify ON customer_keyword_subscriptions(notify_enabled) WHERE notify_enabled = 1;

-- ----------------------------------------------------------------------------
-- TOPICS: Keyword bundles (e.g., "Crypto" = bitcoin + ethereum + ...)
-- ----------------------------------------------------------------------------
-- Topics provide a way for customers to subscribe to themed groups of keywords
-- rather than managing individual keywords
-- ----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS topics (
    id TEXT PRIMARY KEY DEFAULT (lower(hex(randomblob(16)))),
    customer_id TEXT REFERENCES customers(id) ON DELETE CASCADE, -- NULL = system topic
    name TEXT NOT NULL,
    description TEXT,
    slug TEXT NOT NULL,

    -- Topic type
    type TEXT DEFAULT 'custom' CHECK (type IN ('system', 'industry', 'custom')),

    -- Category reference (links to DataForSEO 3000 categories)
    category_id TEXT, -- External category ID from DataForSEO
    category_path TEXT, -- Hierarchical path: "Technology > Software > AI"

    -- Stats
    keyword_count INTEGER DEFAULT 0,
    subscriber_count INTEGER DEFAULT 0,
    articles_last_24h INTEGER DEFAULT 0,

    is_active INTEGER DEFAULT 1,
    created_at TEXT DEFAULT (datetime('now')),
    updated_at TEXT DEFAULT (datetime('now')),
    UNIQUE(customer_id, slug)
);

CREATE INDEX idx_topics_customer ON topics(customer_id);
CREATE INDEX idx_topics_slug ON topics(slug);
CREATE INDEX idx_topics_type ON topics(type);
CREATE INDEX idx_topics_category ON topics(category_id) WHERE category_id IS NOT NULL;

-- ----------------------------------------------------------------------------
-- TOPIC_KEYWORDS: Junction between topics and keywords
-- ----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS topic_keywords (
    topic_id TEXT NOT NULL REFERENCES topics(id) ON DELETE CASCADE,
    keyword_id TEXT NOT NULL REFERENCES keywords(id) ON DELETE CASCADE,
    added_by TEXT DEFAULT 'manual' CHECK (added_by IN ('manual', 'auto', 'ai_suggested')),
    created_at TEXT DEFAULT (datetime('now')),
    PRIMARY KEY (topic_id, keyword_id)
);

CREATE INDEX idx_topic_keywords_topic ON topic_keywords(topic_id);
CREATE INDEX idx_topic_keywords_keyword ON topic_keywords(keyword_id);

-- ----------------------------------------------------------------------------
-- CUSTOMER_TOPIC_SUBSCRIPTIONS: Customers subscribed to entire topics
-- ----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS customer_topic_subscriptions (
    customer_id TEXT NOT NULL REFERENCES customers(id) ON DELETE CASCADE,
    topic_id TEXT NOT NULL REFERENCES topics(id) ON DELETE CASCADE,
    notify_enabled INTEGER DEFAULT 1,
    notify_threshold REAL DEFAULT 0.5,
    created_at TEXT DEFAULT (datetime('now')),
    PRIMARY KEY (customer_id, topic_id)
);

CREATE INDEX idx_cts_customer ON customer_topic_subscriptions(customer_id);
CREATE INDEX idx_cts_topic ON customer_topic_subscriptions(topic_id);

-- ----------------------------------------------------------------------------
-- KEYWORD_ARTICLES: Junction tracking which articles matched which keywords
-- ----------------------------------------------------------------------------
-- This enables efficient fan-out: find article, look up keywords, notify subscribers
-- ----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS keyword_articles (
    keyword_id TEXT NOT NULL REFERENCES keywords(id) ON DELETE CASCADE,
    article_id TEXT NOT NULL REFERENCES articles(id) ON DELETE CASCADE,
    match_type TEXT DEFAULT 'headline' CHECK (match_type IN ('headline', 'body', 'both')),
    match_count INTEGER DEFAULT 1, -- How many times keyword appears
    relevance_score REAL DEFAULT 0.5,
    created_at TEXT DEFAULT (datetime('now')),
    PRIMARY KEY (keyword_id, article_id)
);

CREATE INDEX idx_ka_keyword ON keyword_articles(keyword_id);
CREATE INDEX idx_ka_article ON keyword_articles(article_id);
CREATE INDEX idx_ka_created ON keyword_articles(created_at DESC);

-- ----------------------------------------------------------------------------
-- KEYWORD_CRAWL_HISTORY: Time-series for rate of change calculation
-- ----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS keyword_crawl_history (
    keyword_id TEXT NOT NULL REFERENCES keywords(id) ON DELETE CASCADE,
    crawled_at TEXT NOT NULL,
    source_adapter TEXT NOT NULL DEFAULT 'google_news' REFERENCES source_adapters(id),
    articles_found INTEGER DEFAULT 0,
    new_articles INTEGER DEFAULT 0,
    crawl_duration_ms INTEGER,
    fetch_method TEXT CHECK (fetch_method IN ('direct', 'zenrows', 'rapidapi', 'api', 'failed')),
    error_message TEXT,
    created_at TEXT DEFAULT (datetime('now')),
    PRIMARY KEY (keyword_id, crawled_at, source_adapter)
);

CREATE INDEX idx_kch_keyword ON keyword_crawl_history(keyword_id);
CREATE INDEX idx_kch_crawled ON keyword_crawl_history(crawled_at DESC);
CREATE INDEX idx_kch_method ON keyword_crawl_history(fetch_method);

-- ----------------------------------------------------------------------------
-- DATAFORSEO_CATEGORIES: Hierarchical category reference (3000 categories)
-- ----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS dataforseo_categories (
    id TEXT PRIMARY KEY, -- DataForSEO category ID
    name TEXT NOT NULL,
    parent_id TEXT REFERENCES dataforseo_categories(id),
    path TEXT NOT NULL, -- Full hierarchical path
    depth INTEGER DEFAULT 0,
    keyword_patterns TEXT, -- JSON array of typical keywords
    created_at TEXT DEFAULT (datetime('now'))
);

CREATE INDEX idx_dfs_cat_parent ON dataforseo_categories(parent_id);
CREATE INDEX idx_dfs_cat_path ON dataforseo_categories(path);
CREATE INDEX idx_dfs_cat_depth ON dataforseo_categories(depth);

-- ----------------------------------------------------------------------------
-- LEGACY: KEYWORD_SETS (deprecated, kept for migration)
-- ----------------------------------------------------------------------------
-- NOTE: This table is deprecated. Use KEYWORDS + CUSTOMER_KEYWORD_SUBSCRIPTIONS
-- Keeping for backward compatibility during migration
-- ----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS keyword_sets (
    id TEXT PRIMARY KEY DEFAULT (lower(hex(randomblob(16)))),
    customer_id TEXT NOT NULL REFERENCES customers(id) ON DELETE CASCADE,
    name TEXT NOT NULL,
    keywords TEXT NOT NULL, -- JSON array
    boolean_query TEXT,
    language TEXT DEFAULT 'en',
    region TEXT DEFAULT 'US',
    google_news_ceid TEXT DEFAULT 'US:en',
    crawl_frequency TEXT DEFAULT 'hourly' CHECK (crawl_frequency IN ('every_15min', 'hourly', 'daily')),
    is_active INTEGER DEFAULT 1,
    last_crawled_at TEXT,
    article_count INTEGER DEFAULT 0,
    migrated_to_keywords INTEGER DEFAULT 0, -- Flag: migrated to new model
    created_at TEXT DEFAULT (datetime('now')),
    updated_at TEXT DEFAULT (datetime('now'))
);

CREATE INDEX idx_keyword_sets_customer ON keyword_sets(customer_id);
CREATE INDEX idx_keyword_sets_active ON keyword_sets(is_active) WHERE is_active = 1;
CREATE INDEX idx_keyword_sets_migrated ON keyword_sets(migrated_to_keywords);

-- ----------------------------------------------------------------------------
-- API_KEYS: Customer API authentication
-- ----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS api_keys (
    id TEXT PRIMARY KEY DEFAULT (lower(hex(randomblob(16)))),
    customer_id TEXT NOT NULL REFERENCES customers(id) ON DELETE CASCADE,
    key_hash TEXT NOT NULL UNIQUE, -- SHA-256 of key (never store plaintext)
    key_prefix TEXT NOT NULL, -- First 8 chars for ID: "nz_live_"
    name TEXT NOT NULL,
    scopes TEXT DEFAULT '["read:feed"]', -- JSON array
    rate_limit_tier TEXT DEFAULT 'standard' CHECK (rate_limit_tier IN ('standard', 'elevated', 'unlimited')),
    last_used_at TEXT,
    expires_at TEXT,
    is_active INTEGER DEFAULT 1,
    created_at TEXT DEFAULT (datetime('now')),
    revoked_at TEXT
);

CREATE INDEX idx_api_keys_customer ON api_keys(customer_id);
CREATE INDEX idx_api_keys_hash ON api_keys(key_hash);
CREATE INDEX idx_api_keys_prefix ON api_keys(key_prefix);
CREATE INDEX idx_api_keys_active ON api_keys(is_active) WHERE is_active = 1;

-- ----------------------------------------------------------------------------
-- ADMIN_TOKENS: Admin JWT/session management
-- ----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS admin_tokens (
    id TEXT PRIMARY KEY DEFAULT (lower(hex(randomblob(16)))),
    user_id TEXT NOT NULL,
    token_hash TEXT NOT NULL UNIQUE,
    token_type TEXT DEFAULT 'access' CHECK (token_type IN ('access', 'refresh')),
    permissions TEXT DEFAULT '["admin:read"]', -- JSON array
    ip_allowlist TEXT, -- JSON array
    last_used_at TEXT,
    expires_at TEXT NOT NULL,
    created_at TEXT DEFAULT (datetime('now')),
    revoked_at TEXT
);

CREATE INDEX idx_admin_tokens_user ON admin_tokens(user_id);
CREATE INDEX idx_admin_tokens_hash ON admin_tokens(token_hash);
CREATE INDEX idx_admin_tokens_expires ON admin_tokens(expires_at);

-- ----------------------------------------------------------------------------
-- CUSTOMER_EVENTS: Behavioral analytics
-- ----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS customer_events (
    id TEXT PRIMARY KEY DEFAULT (lower(hex(randomblob(16)))),
    customer_id TEXT NOT NULL REFERENCES customers(id) ON DELETE CASCADE,
    event_type TEXT NOT NULL CHECK (event_type IN ('view', 'click', 'save', 'dismiss', 'share', 'dwell', 'search', 'export')),
    article_id TEXT REFERENCES articles(id) ON DELETE SET NULL,
    story_id TEXT REFERENCES stories(id) ON DELETE SET NULL,
    profile_id TEXT,
    source TEXT DEFAULT 'api' CHECK (source IN ('web', 'mobile', 'api', 'email')),
    metadata TEXT, -- JSON: {dwell_time_ms, scroll_depth, etc}
    session_id TEXT,
    created_at TEXT DEFAULT (datetime('now'))
);

CREATE INDEX idx_customer_events_customer ON customer_events(customer_id);
CREATE INDEX idx_customer_events_type ON customer_events(event_type);
CREATE INDEX idx_customer_events_article ON customer_events(article_id) WHERE article_id IS NOT NULL;
CREATE INDEX idx_customer_events_created ON customer_events(created_at DESC);

-- ----------------------------------------------------------------------------
-- CUSTOMER_PROFILES: Monitoring profiles (saved searches)
-- ----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS customer_profiles (
    id TEXT PRIMARY KEY DEFAULT (lower(hex(randomblob(16)))),
    customer_id TEXT NOT NULL REFERENCES customers(id) ON DELETE CASCADE,
    name TEXT NOT NULL,
    keywords TEXT, -- JSON array
    entities_following TEXT, -- JSON array of entity UUIDs
    sources_preferred TEXT, -- JSON array of source UUIDs
    sources_excluded TEXT, -- JSON array of source UUIDs
    topics TEXT, -- JSON array
    regions TEXT, -- JSON array
    languages TEXT DEFAULT '["en"]', -- JSON array
    min_authority_score REAL DEFAULT 0,
    political_lean_range TEXT, -- JSON array: [-0.3, 0.3]
    notify_threshold REAL DEFAULT 0.7,
    notify_channels TEXT DEFAULT '{"email": true}', -- JSON
    profile_embedding_id TEXT,
    is_active INTEGER DEFAULT 1,
    created_at TEXT DEFAULT (datetime('now')),
    updated_at TEXT DEFAULT (datetime('now'))
);

CREATE INDEX idx_customer_profiles_customer ON customer_profiles(customer_id);
CREATE INDEX idx_customer_profiles_active ON customer_profiles(is_active) WHERE is_active = 1;

-- ----------------------------------------------------------------------------
-- CUSTOMER_ARTICLE_SCORES: Relevance scoring per profile
-- ----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS customer_article_scores (
    customer_id TEXT NOT NULL REFERENCES customers(id) ON DELETE CASCADE,
    article_id TEXT NOT NULL REFERENCES articles(id) ON DELETE CASCADE,
    profile_id TEXT NOT NULL REFERENCES customer_profiles(id) ON DELETE CASCADE,
    relevance_score REAL NOT NULL CHECK (relevance_score >= 0 AND relevance_score <= 1),
    score_components TEXT, -- JSON: {semantic: 0.8, keyword: 0.6, entity: 0.9}
    notified INTEGER DEFAULT 0,
    notified_at TEXT,
    feedback TEXT CHECK (feedback IN ('relevant', 'not_relevant', NULL)),
    created_at TEXT DEFAULT (datetime('now')),
    PRIMARY KEY (customer_id, article_id, profile_id)
);

CREATE INDEX idx_cas_customer ON customer_article_scores(customer_id);
CREATE INDEX idx_cas_article ON customer_article_scores(article_id);
CREATE INDEX idx_cas_profile ON customer_article_scores(profile_id);
CREATE INDEX idx_cas_score ON customer_article_scores(relevance_score DESC);
CREATE INDEX idx_cas_notified ON customer_article_scores(notified) WHERE notified = 0;

-- ----------------------------------------------------------------------------
-- CUSTOMER_STORY_SUBSCRIPTIONS: Story follow state
-- ----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS customer_story_subscriptions (
    customer_id TEXT NOT NULL REFERENCES customers(id) ON DELETE CASCADE,
    story_id TEXT NOT NULL REFERENCES stories(id) ON DELETE CASCADE,
    subscribed_at TEXT DEFAULT (datetime('now')),
    notify_on_update INTEGER DEFAULT 1,
    last_notified_at TEXT,
    PRIMARY KEY (customer_id, story_id)
);

CREATE INDEX idx_css_customer ON customer_story_subscriptions(customer_id);
CREATE INDEX idx_css_story ON customer_story_subscriptions(story_id);

-- ============================================================================
-- MIGRATION 003: Classification & Learning Tables
-- ============================================================================

-- ----------------------------------------------------------------------------
-- ARTICLE_CLASSIFICATIONS: Classification results per article
-- ----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS article_classifications (
    article_id TEXT PRIMARY KEY REFERENCES articles(id) ON DELETE CASCADE,
    topics TEXT, -- JSON array: [{id, label, confidence}]
    industries TEXT, -- JSON array
    tactics TEXT, -- JSON array: PR tactic detection
    sentiment REAL CHECK (sentiment >= -1 AND sentiment <= 1),
    political_lean REAL CHECK (political_lean >= -1 AND political_lean <= 1),
    pov_frames TEXT, -- JSON array
    claims TEXT, -- JSON array of extracted claims
    risk_signals TEXT, -- JSON array
    classification_version TEXT DEFAULT '1.0',
    classified_by TEXT DEFAULT 'rules' CHECK (classified_by IN ('rules', 'vector', 'llm', 'human')),
    confidence REAL DEFAULT 0.5,
    needs_review INTEGER DEFAULT 0,
    created_at TEXT DEFAULT (datetime('now')),
    updated_at TEXT DEFAULT (datetime('now'))
);

CREATE INDEX idx_article_class_confidence ON article_classifications(confidence);
CREATE INDEX idx_article_class_review ON article_classifications(needs_review) WHERE needs_review = 1;
CREATE INDEX idx_article_class_method ON article_classifications(classified_by);

-- ----------------------------------------------------------------------------
-- TAXONOMIES: Multiple taxonomy support (system, industry, customer)
-- ----------------------------------------------------------------------------
-- System taxonomies: DataForSEO categories, platform-defined labels
-- Customer taxonomies: Custom labels defined by individual customers
-- ----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS taxonomies (
    id TEXT PRIMARY KEY DEFAULT (lower(hex(randomblob(16)))),
    customer_id TEXT REFERENCES customers(id) ON DELETE CASCADE, -- NULL = system taxonomy
    name TEXT NOT NULL,                 -- "DataForSEO Categories", "PR Tactics", "My Custom Labels"
    slug TEXT NOT NULL,                 -- "dataforseo", "pr-tactics", "acme-corp-labels"
    type TEXT NOT NULL CHECK (type IN ('system', 'industry', 'customer')),
    category TEXT NOT NULL CHECK (category IN ('topic', 'industry', 'tactic', 'risk', 'sentiment', 'custom')),
    description TEXT,
    is_hierarchical INTEGER DEFAULT 1,  -- Supports parent/child relationships
    is_active INTEGER DEFAULT 1,
    created_at TEXT DEFAULT (datetime('now')),
    updated_at TEXT DEFAULT (datetime('now')),
    UNIQUE(customer_id, slug)           -- Each customer can have unique slugs
);

CREATE INDEX idx_taxonomies_customer ON taxonomies(customer_id);
CREATE INDEX idx_taxonomies_type ON taxonomies(type);
CREATE INDEX idx_taxonomies_category ON taxonomies(category);
CREATE INDEX idx_taxonomies_active ON taxonomies(is_active) WHERE is_active = 1;

-- Seed system taxonomies
INSERT OR IGNORE INTO taxonomies (id, customer_id, name, slug, type, category, description) VALUES
    ('tax_dataforseo', NULL, 'DataForSEO Categories', 'dataforseo', 'system', 'topic', '3,000 hierarchical categories from DataForSEO'),
    ('tax_industries', NULL, 'Industries', 'industries', 'system', 'industry', 'Standard industry classifications'),
    ('tax_pr_tactics', NULL, 'PR Tactics', 'pr-tactics', 'system', 'tactic', 'Press release and PR content patterns'),
    ('tax_risk_signals', NULL, 'Risk Signals', 'risk-signals', 'system', 'risk', 'Content risk indicators'),
    ('tax_sentiment', NULL, 'Sentiment', 'sentiment', 'system', 'sentiment', 'Sentiment classifications');

-- ----------------------------------------------------------------------------
-- TAXONOMY_LABELS: Hierarchical classification labels within taxonomies
-- ----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS taxonomy_labels (
    id TEXT PRIMARY KEY DEFAULT (lower(hex(randomblob(16)))),
    taxonomy_id TEXT NOT NULL REFERENCES taxonomies(id) ON DELETE CASCADE,
    label TEXT NOT NULL,
    parent_id TEXT REFERENCES taxonomy_labels(id),
    external_id TEXT,                   -- DataForSEO category ID, etc.
    description TEXT,
    exemplar_embedding_id TEXT,         -- Vector centroid for this label
    keyword_patterns TEXT,              -- JSON array of regex patterns
    sort_order INTEGER DEFAULT 0,
    is_active INTEGER DEFAULT 1,
    created_at TEXT DEFAULT (datetime('now')),
    updated_at TEXT DEFAULT (datetime('now')),
    UNIQUE(taxonomy_id, label, parent_id)
);

CREATE INDEX idx_taxonomy_labels_taxonomy ON taxonomy_labels(taxonomy_id);
CREATE INDEX idx_taxonomy_labels_parent ON taxonomy_labels(parent_id);
CREATE INDEX idx_taxonomy_labels_external ON taxonomy_labels(external_id) WHERE external_id IS NOT NULL;
CREATE INDEX idx_taxonomy_labels_active ON taxonomy_labels(is_active) WHERE is_active = 1;

-- ============================================================================
-- MIGRATION 003b: Pluggable Extraction Framework
-- ============================================================================
-- Extensible system for adding new extraction types over time.
-- Examples: events, products, prices, quotes, job postings, etc.
-- Add new extractors without schema changes - just add rows to extractors table.
-- ============================================================================

-- ----------------------------------------------------------------------------
-- EXTRACTORS: Registry of available extraction add-ons
-- ----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS extractors (
    id TEXT PRIMARY KEY,                -- 'entities', 'events', 'products', 'quotes', 'jobs'
    name TEXT NOT NULL,                 -- "Named Entities", "Events", "Product Mentions"
    description TEXT,

    -- Extractor configuration
    type TEXT NOT NULL CHECK (type IN ('builtin', 'ai', 'rules', 'hybrid')),
    model TEXT,                         -- AI model if applicable: 'gpt-4o-mini', 'workers-ai-ner'
    config TEXT,                        -- JSON: extractor-specific settings

    -- Schema definition for extracted data (JSON Schema)
    output_schema TEXT NOT NULL,        -- JSON Schema defining the structure of extracted data

    -- Execution settings
    run_on_ingest INTEGER DEFAULT 1,    -- Run automatically on new articles
    priority INTEGER DEFAULT 100,       -- Lower = runs first
    timeout_ms INTEGER DEFAULT 30000,

    -- Cost tracking
    avg_cost_micros INTEGER DEFAULT 0,  -- Average cost per extraction

    -- Status
    is_enabled INTEGER DEFAULT 1,
    created_at TEXT DEFAULT (datetime('now')),
    updated_at TEXT DEFAULT (datetime('now'))
);

-- Seed built-in extractors
INSERT OR IGNORE INTO extractors (id, name, type, output_schema, priority, description) VALUES
    ('entities', 'Named Entities', 'ai', '{
        "type": "array",
        "items": {
            "type": "object",
            "properties": {
                "name": {"type": "string"},
                "type": {"type": "string", "enum": ["person", "org", "location", "product", "event", "concept"]},
                "mentions": {"type": "integer"},
                "sentiment": {"type": "number"},
                "is_primary": {"type": "boolean"}
            }
        }
    }', 10, 'Extract people, organizations, locations, products'),

    ('locations', 'Geographic Locations', 'hybrid', '{
        "type": "array",
        "items": {
            "type": "object",
            "properties": {
                "name": {"type": "string"},
                "type": {"type": "string", "enum": ["city", "state", "country", "region", "landmark"]},
                "lat": {"type": "number"},
                "lng": {"type": "number"},
                "is_primary": {"type": "boolean"}
            }
        }
    }', 20, 'Extract and geocode locations mentioned'),

    ('events', 'Events & Dates', 'ai', '{
        "type": "array",
        "items": {
            "type": "object",
            "properties": {
                "name": {"type": "string"},
                "type": {"type": "string", "enum": ["conference", "earnings", "product_launch", "ipo", "merger", "lawsuit", "election", "other"]},
                "start_date": {"type": "string", "format": "date-time"},
                "end_date": {"type": "string", "format": "date-time"},
                "location": {"type": "string"},
                "url": {"type": "string"},
                "price": {"type": "string"},
                "organizer": {"type": "string"}
            }
        }
    }', 50, 'Extract structured event data'),

    ('quotes', 'Quotes & Attributions', 'ai', '{
        "type": "array",
        "items": {
            "type": "object",
            "properties": {
                "text": {"type": "string"},
                "speaker": {"type": "string"},
                "speaker_title": {"type": "string"},
                "context": {"type": "string"}
            }
        }
    }', 60, 'Extract quotes with attribution'),

    ('products', 'Product Mentions', 'ai', '{
        "type": "array",
        "items": {
            "type": "object",
            "properties": {
                "name": {"type": "string"},
                "brand": {"type": "string"},
                "category": {"type": "string"},
                "price": {"type": "string"},
                "sentiment": {"type": "number"}
            }
        }
    }', 70, 'Extract product mentions and details'),

    ('jobs', 'Job Postings', 'ai', '{
        "type": "array",
        "items": {
            "type": "object",
            "properties": {
                "title": {"type": "string"},
                "company": {"type": "string"},
                "location": {"type": "string"},
                "salary_range": {"type": "string"},
                "type": {"type": "string", "enum": ["full_time", "part_time", "contract", "remote"]}
            }
        }
    }', 80, 'Extract job posting details');

-- Enable only core extractors by default
UPDATE extractors SET is_enabled = 0 WHERE id IN ('events', 'quotes', 'products', 'jobs');
UPDATE extractors SET is_enabled = 1 WHERE id IN ('entities', 'locations');

-- ----------------------------------------------------------------------------
-- ARTICLE_EXTRACTIONS: Stores extracted data for each article/extractor combo
-- ----------------------------------------------------------------------------
-- Generic storage for any extractor output. Schema validated against extractor's output_schema.
-- ----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS article_extractions (
    id TEXT PRIMARY KEY DEFAULT (lower(hex(randomblob(16)))),
    article_id TEXT NOT NULL REFERENCES articles(id) ON DELETE CASCADE,
    extractor_id TEXT NOT NULL REFERENCES extractors(id),

    -- Extracted data (JSON, validated against extractor's output_schema)
    data TEXT NOT NULL,                 -- JSON array/object of extracted items

    -- Extraction metadata
    item_count INTEGER DEFAULT 0,       -- Number of items extracted
    confidence REAL,                    -- Overall confidence score
    model_version TEXT,                 -- Model/version used for extraction

    -- Cost tracking
    cost_micros INTEGER DEFAULT 0,
    latency_ms INTEGER,

    -- Status
    status TEXT DEFAULT 'complete' CHECK (status IN ('pending', 'processing', 'complete', 'failed', 'skipped')),
    error_message TEXT,

    created_at TEXT DEFAULT (datetime('now')),
    updated_at TEXT DEFAULT (datetime('now')),

    UNIQUE(article_id, extractor_id)
);

CREATE INDEX idx_extractions_article ON article_extractions(article_id);
CREATE INDEX idx_extractions_extractor ON article_extractions(extractor_id);
CREATE INDEX idx_extractions_status ON article_extractions(status);

-- ----------------------------------------------------------------------------
-- EXTRACTION_ITEMS: Denormalized searchable items (optional, for performance)
-- ----------------------------------------------------------------------------
-- For extractors that produce searchable items (entities, events, products),
-- we denormalize into a searchable table. Not all extractors need this.
-- ----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS extraction_items (
    id TEXT PRIMARY KEY DEFAULT (lower(hex(randomblob(16)))),
    extraction_id TEXT NOT NULL REFERENCES article_extractions(id) ON DELETE CASCADE,
    article_id TEXT NOT NULL REFERENCES articles(id) ON DELETE CASCADE,
    extractor_id TEXT NOT NULL REFERENCES extractors(id),

    -- Common searchable fields
    item_type TEXT NOT NULL,            -- 'person', 'event', 'product', etc.
    name TEXT NOT NULL,                 -- Primary name/title
    normalized_name TEXT,               -- Lowercase, for deduplication

    -- Optional common fields
    start_date TEXT,                    -- For events
    end_date TEXT,
    location TEXT,
    url TEXT,

    -- Full item data
    data TEXT NOT NULL,                 -- Full JSON object for this item

    created_at TEXT DEFAULT (datetime('now'))
);

CREATE INDEX idx_extraction_items_article ON extraction_items(article_id);
CREATE INDEX idx_extraction_items_extractor ON extraction_items(extractor_id);
CREATE INDEX idx_extraction_items_type ON extraction_items(item_type);
CREATE INDEX idx_extraction_items_name ON extraction_items(normalized_name);
CREATE INDEX idx_extraction_items_date ON extraction_items(start_date) WHERE start_date IS NOT NULL;

-- ----------------------------------------------------------------------------
-- CUSTOMER_EXTRACTORS: Per-customer extractor preferences
-- ----------------------------------------------------------------------------
-- Customers can enable/disable extractors and configure them
-- ----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS customer_extractors (
    customer_id TEXT NOT NULL REFERENCES customers(id) ON DELETE CASCADE,
    extractor_id TEXT NOT NULL REFERENCES extractors(id),

    is_enabled INTEGER DEFAULT 1,
    config TEXT,                        -- Customer-specific config overrides (JSON)

    created_at TEXT DEFAULT (datetime('now')),
    PRIMARY KEY (customer_id, extractor_id)
);

CREATE INDEX idx_cust_extractors_customer ON customer_extractors(customer_id);

-- ----------------------------------------------------------------------------

-- ----------------------------------------------------------------------------
-- CLASSIFICATION_FEEDBACK: Human review feedback
-- ----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS classification_feedback (
    id TEXT PRIMARY KEY DEFAULT (lower(hex(randomblob(16)))),
    article_id TEXT NOT NULL REFERENCES articles(id) ON DELETE CASCADE,
    original_labels TEXT NOT NULL, -- JSON
    corrected_labels TEXT NOT NULL, -- JSON
    reviewer TEXT DEFAULT 'human' CHECK (reviewer IN ('human', 'auto_heuristic')),
    reviewer_id TEXT,
    notes TEXT,
    incorporated INTEGER DEFAULT 0,
    created_at TEXT DEFAULT (datetime('now'))
);

CREATE INDEX idx_class_feedback_article ON classification_feedback(article_id);
CREATE INDEX idx_class_feedback_incorporated ON classification_feedback(incorporated) WHERE incorporated = 0;

-- ============================================================================
-- MIGRATION 004: Enrichment Data Tables
-- ============================================================================

-- ----------------------------------------------------------------------------
-- ARTICLE_SOCIAL_METRICS: Social engagement data
-- ----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS article_social_metrics (
    article_id TEXT PRIMARY KEY REFERENCES articles(id) ON DELETE CASCADE,
    facebook_shares INTEGER DEFAULT 0,
    facebook_reactions INTEGER DEFAULT 0,
    twitter_shares INTEGER DEFAULT 0,
    pinterest_shares INTEGER DEFAULT 0,
    linkedin_shares INTEGER DEFAULT 0,
    total_engagement INTEGER DEFAULT 0,
    fetched_at TEXT DEFAULT (datetime('now')),
    created_at TEXT DEFAULT (datetime('now'))
);

CREATE INDEX idx_social_engagement ON article_social_metrics(total_engagement DESC);

-- ----------------------------------------------------------------------------
-- ARTICLE_BACKLINK_METRICS: SEO and authority data
-- ----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS article_backlink_metrics (
    article_id TEXT PRIMARY KEY REFERENCES articles(id) ON DELETE CASCADE,
    referring_domains INTEGER DEFAULT 0,
    backlinks_total INTEGER DEFAULT 0,
    domain_authority REAL,
    url_authority REAL,
    fetched_at TEXT DEFAULT (datetime('now')),
    created_at TEXT DEFAULT (datetime('now'))
);

CREATE INDEX idx_backlinks_authority ON article_backlink_metrics(domain_authority DESC);

-- ----------------------------------------------------------------------------
-- SOURCE_AUTHORITY_HISTORY: Time-series authority tracking
-- ----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS source_authority_history (
    source_id TEXT NOT NULL REFERENCES sources(id) ON DELETE CASCADE,
    date TEXT NOT NULL,
    authority_score REAL,
    backlink_count INTEGER,
    domain_authority REAL,
    avg_social_engagement REAL,
    created_at TEXT DEFAULT (datetime('now')),
    PRIMARY KEY (source_id, date)
);

CREATE INDEX idx_source_auth_date ON source_authority_history(date DESC);

-- ----------------------------------------------------------------------------
-- TREND_SIGNALS: Detected trends
-- ----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS trend_signals (
    id TEXT PRIMARY KEY DEFAULT (lower(hex(randomblob(16)))),
    signal_type TEXT NOT NULL CHECK (signal_type IN ('keyword_spike', 'entity_surge', 'topic_emergence', 'sentiment_shift')),
    target_type TEXT NOT NULL CHECK (target_type IN ('keyword', 'entity', 'topic', 'location')),
    target_id TEXT,
    target_label TEXT NOT NULL,
    baseline_value REAL,
    current_value REAL,
    delta_percent REAL,
    velocity REAL,
    window_start TEXT,
    window_end TEXT,
    article_ids TEXT, -- JSON array
    confidence REAL DEFAULT 0.5,
    is_confirmed_trend INTEGER DEFAULT 0,
    created_at TEXT DEFAULT (datetime('now'))
);

CREATE INDEX idx_trend_type ON trend_signals(signal_type);
CREATE INDEX idx_trend_target ON trend_signals(target_type, target_id);
CREATE INDEX idx_trend_confirmed ON trend_signals(is_confirmed_trend) WHERE is_confirmed_trend = 1;
CREATE INDEX idx_trend_window ON trend_signals(window_end DESC);

-- ----------------------------------------------------------------------------
-- KEYWORD_VELOCITY: Time-series for trend detection
-- ----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS keyword_velocity (
    keyword TEXT NOT NULL,
    keyword_hash TEXT NOT NULL,
    hour TEXT NOT NULL, -- Truncated to hour
    article_count INTEGER DEFAULT 0,
    source_count INTEGER DEFAULT 0,
    avg_sentiment REAL,
    total_engagement INTEGER DEFAULT 0,
    created_at TEXT DEFAULT (datetime('now')),
    PRIMARY KEY (keyword_hash, hour)
);

CREATE INDEX idx_kv_keyword ON keyword_velocity(keyword);
CREATE INDEX idx_kv_hour ON keyword_velocity(hour DESC);

-- ============================================================================
-- MIGRATION 005: Cost Tracking Tables
-- ============================================================================

-- ----------------------------------------------------------------------------
-- COST_EVENTS: Per-operation cost tracking
-- ----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS cost_events (
    id TEXT PRIMARY KEY DEFAULT (lower(hex(randomblob(16)))),
    timestamp TEXT DEFAULT (datetime('now')),
    service TEXT NOT NULL CHECK (service IN ('zenrows', 'rapidapi', 'data4seo', 'sharedcount', 'workers_ai', 'vectorize', 'openai', 'anthropic')),
    operation TEXT NOT NULL, -- embed, classify_ner, classify_sentiment, fetch, etc.
    article_id TEXT REFERENCES articles(id) ON DELETE SET NULL,
    customer_id TEXT REFERENCES customers(id) ON DELETE SET NULL,
    workflow_id TEXT,
    input_units INTEGER DEFAULT 0, -- tokens, requests, bytes
    output_units INTEGER DEFAULT 0,
    cost_micros INTEGER NOT NULL, -- Cost in microdollars (1/1,000,000 USD)
    latency_ms INTEGER,
    success INTEGER DEFAULT 1,
    error_code TEXT,
    metadata TEXT, -- JSON: service-specific details
    created_at TEXT DEFAULT (datetime('now'))
);

CREATE INDEX idx_cost_timestamp ON cost_events(timestamp DESC);
CREATE INDEX idx_cost_service ON cost_events(service);
CREATE INDEX idx_cost_article ON cost_events(article_id) WHERE article_id IS NOT NULL;
CREATE INDEX idx_cost_customer ON cost_events(customer_id) WHERE customer_id IS NOT NULL;
CREATE INDEX idx_cost_workflow ON cost_events(workflow_id) WHERE workflow_id IS NOT NULL;

-- ----------------------------------------------------------------------------
-- COST_BUDGETS: Budget limits and alerts
-- ----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS cost_budgets (
    id TEXT PRIMARY KEY DEFAULT (lower(hex(randomblob(16)))),
    scope TEXT NOT NULL CHECK (scope IN ('global', 'customer', 'service', 'operation')),
    scope_id TEXT, -- customer_id or service name if applicable
    period TEXT NOT NULL CHECK (period IN ('daily', 'weekly', 'monthly')),
    budget_micros INTEGER NOT NULL, -- Budget in microdollars
    alert_threshold_pct INTEGER DEFAULT 80,
    hard_limit INTEGER DEFAULT 0, -- If 1, stop operations when exceeded
    current_spend_micros INTEGER DEFAULT 0,
    period_start TEXT,
    is_active INTEGER DEFAULT 1,
    created_at TEXT DEFAULT (datetime('now')),
    updated_at TEXT DEFAULT (datetime('now'))
);

CREATE INDEX idx_budget_scope ON cost_budgets(scope, scope_id);
CREATE INDEX idx_budget_active ON cost_budgets(is_active) WHERE is_active = 1;

-- ----------------------------------------------------------------------------
-- COST_ROLLUPS: Materialized daily aggregates
-- ----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS cost_rollups (
    date TEXT NOT NULL,
    service TEXT NOT NULL,
    operation TEXT,
    total_micros INTEGER DEFAULT 0,
    operation_count INTEGER DEFAULT 0,
    avg_latency_ms INTEGER,
    success_count INTEGER DEFAULT 0,
    error_count INTEGER DEFAULT 0,
    created_at TEXT DEFAULT (datetime('now')),
    PRIMARY KEY (date, service, operation)
);

CREATE INDEX idx_rollups_date ON cost_rollups(date DESC);
CREATE INDEX idx_rollups_service ON cost_rollups(service);

-- ============================================================================
-- MIGRATION 006: Pipeline & Operations Tables
-- ============================================================================

-- ----------------------------------------------------------------------------
-- PIPELINE_RUNS: Workflow execution tracking
-- ----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS pipeline_runs (
    id TEXT PRIMARY KEY DEFAULT (lower(hex(randomblob(16)))),
    workflow_id TEXT, -- Cloudflare workflow instance ID
    workflow_type TEXT NOT NULL CHECK (workflow_type IN ('scheduled_crawl', 'story_recluster', 'retention_cleanup', 'enrichment_backfill', 'profile_rebuild')),
    status TEXT DEFAULT 'running' CHECK (status IN ('running', 'completed', 'failed', 'cancelled')),
    started_at TEXT DEFAULT (datetime('now')),
    completed_at TEXT,
    trigger TEXT DEFAULT 'cron' CHECK (trigger IN ('cron', 'manual', 'api', 'webhook')),
    triggered_by TEXT,
    metrics TEXT, -- JSON: {articles_found, articles_new, errors, duration_ms}
    error_message TEXT,
    created_at TEXT DEFAULT (datetime('now'))
);

CREATE INDEX idx_pipeline_workflow ON pipeline_runs(workflow_id);
CREATE INDEX idx_pipeline_type ON pipeline_runs(workflow_type);
CREATE INDEX idx_pipeline_status ON pipeline_runs(status);
CREATE INDEX idx_pipeline_started ON pipeline_runs(started_at DESC);

-- ----------------------------------------------------------------------------
-- PIPELINE_ERRORS: Detailed error logging
-- ----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS pipeline_errors (
    id TEXT PRIMARY KEY DEFAULT (lower(hex(randomblob(16)))),
    pipeline_run_id TEXT REFERENCES pipeline_runs(id) ON DELETE SET NULL,
    stage TEXT NOT NULL, -- crawl, extract, enrich, classify, cluster, match
    article_id TEXT REFERENCES articles(id) ON DELETE SET NULL,
    error_type TEXT NOT NULL, -- fetch_failed, parse_error, ai_error, timeout
    error_message TEXT NOT NULL,
    stack_trace TEXT,
    retryable INTEGER DEFAULT 1,
    retry_count INTEGER DEFAULT 0,
    resolved INTEGER DEFAULT 0,
    created_at TEXT DEFAULT (datetime('now'))
);

CREATE INDEX idx_pipeline_errors_run ON pipeline_errors(pipeline_run_id);
CREATE INDEX idx_pipeline_errors_stage ON pipeline_errors(stage);
CREATE INDEX idx_pipeline_errors_article ON pipeline_errors(article_id) WHERE article_id IS NOT NULL;
CREATE INDEX idx_pipeline_errors_retryable ON pipeline_errors(retryable) WHERE retryable = 1 AND resolved = 0;

-- ----------------------------------------------------------------------------
-- SCHEDULED_JOBS: Cron job configurations
-- ----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS scheduled_jobs (
    id TEXT PRIMARY KEY DEFAULT (lower(hex(randomblob(16)))),
    job_type TEXT NOT NULL CHECK (job_type IN ('crawl', 'recluster', 'retention', 'enrichment_backfill', 'cost_rollup', 'authority_recalc')),
    cron_expression TEXT NOT NULL, -- "*/15 * * * *"
    is_enabled INTEGER DEFAULT 1,
    last_run_at TEXT,
    next_run_at TEXT,
    config TEXT, -- JSON: job-specific configuration
    created_at TEXT DEFAULT (datetime('now')),
    updated_at TEXT DEFAULT (datetime('now'))
);

CREATE INDEX idx_jobs_type ON scheduled_jobs(job_type);
CREATE INDEX idx_jobs_enabled ON scheduled_jobs(is_enabled) WHERE is_enabled = 1;
CREATE INDEX idx_jobs_next ON scheduled_jobs(next_run_at);

-- ----------------------------------------------------------------------------
-- NOTIFICATION_LOG: Outbound notification tracking
-- ----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS notification_log (
    id TEXT PRIMARY KEY DEFAULT (lower(hex(randomblob(16)))),
    customer_id TEXT NOT NULL REFERENCES customers(id) ON DELETE CASCADE,
    profile_id TEXT REFERENCES customer_profiles(id) ON DELETE SET NULL,
    article_id TEXT REFERENCES articles(id) ON DELETE SET NULL,
    story_id TEXT REFERENCES stories(id) ON DELETE SET NULL,
    channel TEXT NOT NULL CHECK (channel IN ('email', 'slack', 'webhook', 'push')),
    status TEXT DEFAULT 'pending' CHECK (status IN ('pending', 'sent', 'failed', 'bounced')),
    sent_at TEXT,
    error_message TEXT,
    retry_count INTEGER DEFAULT 0,
    payload_hash TEXT, -- For deduplication
    created_at TEXT DEFAULT (datetime('now'))
);

CREATE INDEX idx_notif_customer ON notification_log(customer_id);
CREATE INDEX idx_notif_status ON notification_log(status);
CREATE INDEX idx_notif_created ON notification_log(created_at DESC);
CREATE INDEX idx_notif_hash ON notification_log(payload_hash);

-- ----------------------------------------------------------------------------
-- EXPORTS: Customer data export jobs
-- ----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS exports (
    id TEXT PRIMARY KEY DEFAULT (lower(hex(randomblob(16)))),
    customer_id TEXT NOT NULL REFERENCES customers(id) ON DELETE CASCADE,
    export_type TEXT NOT NULL CHECK (export_type IN ('articles', 'feed', 'analytics', 'full_backup')),
    format TEXT NOT NULL CHECK (format IN ('json', 'csv', 'xlsx')),
    filters TEXT, -- JSON: same as search filters
    status TEXT DEFAULT 'pending' CHECK (status IN ('pending', 'processing', 'completed', 'failed')),
    r2_key TEXT,
    download_url TEXT, -- Pre-signed URL (expires)
    expires_at TEXT,
    file_size_bytes INTEGER,
    row_count INTEGER,
    created_at TEXT DEFAULT (datetime('now')),
    completed_at TEXT
);

CREATE INDEX idx_exports_customer ON exports(customer_id);
CREATE INDEX idx_exports_status ON exports(status);
CREATE INDEX idx_exports_expires ON exports(expires_at);

-- ============================================================================
-- MIGRATION 007: Source Management Tables
-- ============================================================================

-- ----------------------------------------------------------------------------
-- SOURCE_CANDIDATES: Auto-discovered sources pending review
-- ----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS source_candidates (
    id TEXT PRIMARY KEY DEFAULT (lower(hex(randomblob(16)))),
    domain TEXT NOT NULL UNIQUE,
    first_seen_at TEXT DEFAULT (datetime('now')),
    article_count INTEGER DEFAULT 1,
    sample_urls TEXT, -- JSON array
    auto_detected_type TEXT CHECK (auto_detected_type IN ('news', 'blog', 'spam', 'unknown')),
    auto_detected_quality REAL, -- 0-1 heuristic score
    status TEXT DEFAULT 'pending_review' CHECK (status IN ('pending_review', 'approved', 'rejected', 'blocked')),
    reviewed_by TEXT,
    reviewed_at TEXT,
    notes TEXT,
    created_at TEXT DEFAULT (datetime('now'))
);

CREATE INDEX idx_candidates_domain ON source_candidates(domain);
CREATE INDEX idx_candidates_status ON source_candidates(status);
CREATE INDEX idx_candidates_quality ON source_candidates(auto_detected_quality DESC);

-- ----------------------------------------------------------------------------
-- SOURCE_RELIABILITY: Bias and credibility scores
-- ----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS source_reliability (
    source_id TEXT PRIMARY KEY REFERENCES sources(id) ON DELETE CASCADE,
    factual_accuracy REAL CHECK (factual_accuracy >= 0 AND factual_accuracy <= 1),
    editorial_bias REAL CHECK (editorial_bias >= -1 AND editorial_bias <= 1),
    sensationalism_score REAL CHECK (sensationalism_score >= 0 AND sensationalism_score <= 1),
    transparency_score REAL CHECK (transparency_score >= 0 AND transparency_score <= 1),
    mbfc_rating TEXT, -- Media Bias Fact Check
    newsguard_score INTEGER CHECK (newsguard_score >= 0 AND newsguard_score <= 100),
    last_evaluated_at TEXT,
    created_at TEXT DEFAULT (datetime('now')),
    updated_at TEXT DEFAULT (datetime('now'))
);

-- ============================================================================
-- MIGRATION 008: Content Quality Tables
-- ============================================================================

-- ----------------------------------------------------------------------------
-- ARTICLE_DUPLICATES: Near-duplicate detection
-- ----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS article_duplicates (
    article_id TEXT NOT NULL REFERENCES articles(id) ON DELETE CASCADE,
    original_article_id TEXT NOT NULL REFERENCES articles(id) ON DELETE CASCADE,
    similarity_score REAL NOT NULL CHECK (similarity_score >= 0 AND similarity_score <= 1),
    duplicate_type TEXT NOT NULL CHECK (duplicate_type IN ('exact', 'near_duplicate', 'syndicated', 'rewrite')),
    detected_at TEXT DEFAULT (datetime('now')),
    created_at TEXT DEFAULT (datetime('now')),
    PRIMARY KEY (article_id, original_article_id)
);

CREATE INDEX idx_dupes_original ON article_duplicates(original_article_id);
CREATE INDEX idx_dupes_type ON article_duplicates(duplicate_type);

-- ----------------------------------------------------------------------------
-- ARTICLE_REVISIONS: Track article updates
-- ----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS article_revisions (
    id TEXT PRIMARY KEY DEFAULT (lower(hex(randomblob(16)))),
    article_id TEXT NOT NULL REFERENCES articles(id) ON DELETE CASCADE,
    revision_number INTEGER NOT NULL,
    detected_at TEXT DEFAULT (datetime('now')),
    previous_headline TEXT,
    previous_body_hash TEXT,
    changes TEXT, -- JSON: {headline: true, body: true, author: false}
    significance TEXT DEFAULT 'minor' CHECK (significance IN ('minor', 'correction', 'major_update')),
    raw_snapshot_r2_key TEXT,
    created_at TEXT DEFAULT (datetime('now'))
);

CREATE INDEX idx_revisions_article ON article_revisions(article_id);
CREATE INDEX idx_revisions_detected ON article_revisions(detected_at DESC);

-- ----------------------------------------------------------------------------
-- ARTICLE_MODERATION: Content safety flags
-- ----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS article_moderation (
    article_id TEXT PRIMARY KEY REFERENCES articles(id) ON DELETE CASCADE,
    flagged INTEGER DEFAULT 0,
    flags TEXT, -- JSON array: ["hate_speech", "violence", "nsfw", "spam"]
    confidence REAL,
    reviewed INTEGER DEFAULT 0,
    reviewer_decision TEXT CHECK (reviewer_decision IN ('approved', 'hidden', 'deleted')),
    reviewed_by TEXT,
    reviewed_at TEXT,
    created_at TEXT DEFAULT (datetime('now')),
    updated_at TEXT DEFAULT (datetime('now'))
);

CREATE INDEX idx_moderation_flagged ON article_moderation(flagged) WHERE flagged = 1;
CREATE INDEX idx_moderation_pending ON article_moderation(reviewed) WHERE reviewed = 0 AND flagged = 1;

-- ============================================================================
-- MIGRATION 009: Webhook Configuration
-- ============================================================================

-- ----------------------------------------------------------------------------
-- WEBHOOK_ENDPOINTS: Customer webhook configurations
-- ----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS webhook_endpoints (
    id TEXT PRIMARY KEY DEFAULT (lower(hex(randomblob(16)))),
    customer_id TEXT NOT NULL REFERENCES customers(id) ON DELETE CASCADE,
    url TEXT NOT NULL,
    secret TEXT NOT NULL, -- HMAC signing secret (encrypted)
    events TEXT NOT NULL DEFAULT '["article.matched"]', -- JSON array of event types
    is_active INTEGER DEFAULT 1,
    failure_count INTEGER DEFAULT 0,
    last_success_at TEXT,
    last_failure_at TEXT,
    created_at TEXT DEFAULT (datetime('now')),
    updated_at TEXT DEFAULT (datetime('now'))
);

CREATE INDEX idx_webhooks_customer ON webhook_endpoints(customer_id);
CREATE INDEX idx_webhooks_active ON webhook_endpoints(is_active) WHERE is_active = 1;

-- ----------------------------------------------------------------------------
-- WEBHOOK_DELIVERIES: Webhook delivery attempts
-- ----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS webhook_deliveries (
    id TEXT PRIMARY KEY DEFAULT (lower(hex(randomblob(16)))),
    endpoint_id TEXT NOT NULL REFERENCES webhook_endpoints(id) ON DELETE CASCADE,
    event_type TEXT NOT NULL,
    payload TEXT NOT NULL, -- JSON payload
    status TEXT DEFAULT 'pending' CHECK (status IN ('pending', 'success', 'failed')),
    response_code INTEGER,
    response_body TEXT,
    attempt_count INTEGER DEFAULT 0,
    next_retry_at TEXT,
    delivered_at TEXT,
    created_at TEXT DEFAULT (datetime('now'))
);

CREATE INDEX idx_deliveries_endpoint ON webhook_deliveries(endpoint_id);
CREATE INDEX idx_deliveries_status ON webhook_deliveries(status);
CREATE INDEX idx_deliveries_retry ON webhook_deliveries(next_retry_at) WHERE status = 'pending';

-- ============================================================================
-- MIGRATION 010: Admin Users (for multi-admin support)
-- ============================================================================

-- ----------------------------------------------------------------------------
-- ADMIN_USERS: Platform administrators
-- ----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS admin_users (
    id TEXT PRIMARY KEY DEFAULT (lower(hex(randomblob(16)))),
    email TEXT NOT NULL UNIQUE,
    password_hash TEXT NOT NULL, -- Argon2id
    name TEXT NOT NULL,
    role TEXT DEFAULT 'admin' CHECK (role IN ('super_admin', 'admin', 'viewer')),
    mfa_secret TEXT, -- TOTP secret (encrypted)
    mfa_enabled INTEGER DEFAULT 0,
    last_login_at TEXT,
    failed_login_attempts INTEGER DEFAULT 0,
    locked_until TEXT,
    is_active INTEGER DEFAULT 1,
    created_at TEXT DEFAULT (datetime('now')),
    updated_at TEXT DEFAULT (datetime('now'))
);

CREATE INDEX idx_admin_email ON admin_users(email);
CREATE INDEX idx_admin_active ON admin_users(is_active) WHERE is_active = 1;

-- ----------------------------------------------------------------------------
-- ADMIN_AUDIT_LOG: Audit trail for admin actions
-- ----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS admin_audit_log (
    id TEXT PRIMARY KEY DEFAULT (lower(hex(randomblob(16)))),
    admin_user_id TEXT REFERENCES admin_users(id) ON DELETE SET NULL,
    action TEXT NOT NULL, -- create, update, delete, login, export, etc.
    resource_type TEXT NOT NULL, -- customer, source, article, taxonomy, etc.
    resource_id TEXT,
    changes TEXT, -- JSON: {before: {}, after: {}}
    ip_address TEXT,
    user_agent TEXT,
    created_at TEXT DEFAULT (datetime('now'))
);

CREATE INDEX idx_audit_admin ON admin_audit_log(admin_user_id);
CREATE INDEX idx_audit_action ON admin_audit_log(action);
CREATE INDEX idx_audit_resource ON admin_audit_log(resource_type, resource_id);
CREATE INDEX idx_audit_created ON admin_audit_log(created_at DESC);

-- ============================================================================
-- VIEWS: Common query patterns
-- ============================================================================

-- Active articles with source info
CREATE VIEW IF NOT EXISTS v_articles_with_source AS
SELECT
    a.*,
    s.name as source_name,
    s.domain as source_domain,
    s.authority_score as source_authority,
    s.type as source_type
FROM articles a
JOIN sources s ON a.source_id = s.id
WHERE a.processing_status = 'complete';

-- Story feed with article counts
CREATE VIEW IF NOT EXISTS v_story_feed AS
SELECT
    s.*,
    COUNT(DISTINCT as2.article_id) as total_articles,
    MAX(a.published_at) as latest_article_at
FROM stories s
LEFT JOIN article_stories as2 ON s.id = as2.story_id
LEFT JOIN articles a ON as2.article_id = a.id
GROUP BY s.id
ORDER BY s.last_activity_at DESC;

-- Cost summary by day
CREATE VIEW IF NOT EXISTS v_daily_costs AS
SELECT
    date(timestamp) as date,
    service,
    SUM(cost_micros) as total_micros,
    SUM(cost_micros) / 1000000.0 as total_usd,
    COUNT(*) as operation_count,
    SUM(CASE WHEN success = 1 THEN 1 ELSE 0 END) as success_count,
    SUM(CASE WHEN success = 0 THEN 1 ELSE 0 END) as error_count
FROM cost_events
GROUP BY date(timestamp), service
ORDER BY date DESC, total_micros DESC;

-- Pipeline health snapshot
CREATE VIEW IF NOT EXISTS v_pipeline_health AS
SELECT
    workflow_type,
    status,
    COUNT(*) as run_count,
    AVG(CAST(json_extract(metrics, '$.duration_ms') AS INTEGER)) as avg_duration_ms,
    MAX(started_at) as last_run
FROM pipeline_runs
WHERE started_at > datetime('now', '-7 days')
GROUP BY workflow_type, status;

-- Keywords ready to crawl (sorted by priority)
CREATE VIEW IF NOT EXISTS v_keywords_to_crawl AS
SELECT
    k.*,
    CASE
        WHEN k.crawl_tier = 'hot' THEN 1
        WHEN k.crawl_tier = 'warm' THEN 2
        WHEN k.crawl_tier = 'normal' THEN 3
        WHEN k.crawl_tier = 'cold' THEN 4
        ELSE 5
    END as priority_order
FROM keywords k
WHERE k.is_active = 1
  AND k.subscriber_count > 0
  AND (k.next_crawl_at IS NULL OR k.next_crawl_at <= datetime('now'))
ORDER BY priority_order, k.subscriber_count DESC;

-- Crawl health dashboard (admin view)
CREATE VIEW IF NOT EXISTS v_crawl_health AS
SELECT
    date(crawled_at) as date,
    fetch_method,
    COUNT(*) as crawl_count,
    SUM(new_articles) as total_new_articles,
    AVG(crawl_duration_ms) as avg_duration_ms,
    SUM(CASE WHEN error_message IS NOT NULL THEN 1 ELSE 0 END) as error_count,
    ROUND(100.0 * SUM(CASE WHEN error_message IS NULL THEN 1 ELSE 0 END) / COUNT(*), 2) as success_rate
FROM keyword_crawl_history
WHERE crawled_at > datetime('now', '-7 days')
GROUP BY date(crawled_at), fetch_method
ORDER BY date DESC, fetch_method;

-- Fetch method fallback rates (for admin dashboard)
CREATE VIEW IF NOT EXISTS v_fetch_fallback_rates AS
SELECT
    date(crawled_at) as date,
    COUNT(*) as total_crawls,
    SUM(CASE WHEN fetch_method = 'direct' THEN 1 ELSE 0 END) as direct_count,
    SUM(CASE WHEN fetch_method = 'zenrows' THEN 1 ELSE 0 END) as zenrows_count,
    SUM(CASE WHEN fetch_method = 'rapidapi' THEN 1 ELSE 0 END) as rapidapi_count,
    SUM(CASE WHEN fetch_method = 'failed' THEN 1 ELSE 0 END) as failed_count,
    ROUND(100.0 * SUM(CASE WHEN fetch_method = 'direct' THEN 1 ELSE 0 END) / COUNT(*), 2) as direct_pct,
    ROUND(100.0 * SUM(CASE WHEN fetch_method = 'zenrows' THEN 1 ELSE 0 END) / COUNT(*), 2) as zenrows_pct,
    ROUND(100.0 * SUM(CASE WHEN fetch_method = 'rapidapi' THEN 1 ELSE 0 END) / COUNT(*), 2) as rapidapi_pct,
    ROUND(100.0 * SUM(CASE WHEN fetch_method = 'failed' THEN 1 ELSE 0 END) / COUNT(*), 2) as failed_pct
FROM keyword_crawl_history
WHERE crawled_at > datetime('now', '-30 days')
GROUP BY date(crawled_at)
ORDER BY date DESC;

-- Keyword subscriber summary
CREATE VIEW IF NOT EXISTS v_keyword_subscribers AS
SELECT
    k.id,
    k.keyword,
    k.keyword_normalized,
    k.subscriber_count,
    k.crawl_tier,
    k.articles_last_24h,
    k.rate_of_change,
    k.last_crawled_at,
    GROUP_CONCAT(DISTINCT c.email) as subscriber_emails
FROM keywords k
LEFT JOIN customer_keyword_subscriptions cks ON k.id = cks.keyword_id
LEFT JOIN customers c ON cks.customer_id = c.id
WHERE k.subscriber_count > 0
GROUP BY k.id
ORDER BY k.subscriber_count DESC;

-- Topic overview with keyword counts
CREATE VIEW IF NOT EXISTS v_topic_overview AS
SELECT
    t.*,
    COUNT(DISTINCT tk.keyword_id) as actual_keyword_count,
    COUNT(DISTINCT cts.customer_id) as actual_subscriber_count,
    SUM(k.articles_last_24h) as topic_articles_24h
FROM topics t
LEFT JOIN topic_keywords tk ON t.id = tk.topic_id
LEFT JOIN keywords k ON tk.keyword_id = k.id
LEFT JOIN customer_topic_subscriptions cts ON t.id = cts.topic_id
WHERE t.is_active = 1
GROUP BY t.id
ORDER BY actual_subscriber_count DESC;

-- ============================================================================
-- TRIGGERS: Automatic denormalization
-- ============================================================================

-- Update article count on stories when article_stories changes
CREATE TRIGGER IF NOT EXISTS trg_article_stories_insert
AFTER INSERT ON article_stories
BEGIN
    UPDATE stories
    SET article_count = (
        SELECT COUNT(*) FROM article_stories WHERE story_id = NEW.story_id
    ),
    source_count = (
        SELECT COUNT(DISTINCT a.source_id)
        FROM article_stories as2
        JOIN articles a ON as2.article_id = a.id
        WHERE as2.story_id = NEW.story_id
    ),
    last_activity_at = datetime('now'),
    updated_at = datetime('now')
    WHERE id = NEW.story_id;
END;

CREATE TRIGGER IF NOT EXISTS trg_article_stories_delete
AFTER DELETE ON article_stories
BEGIN
    UPDATE stories
    SET article_count = (
        SELECT COUNT(*) FROM article_stories WHERE story_id = OLD.story_id
    ),
    source_count = (
        SELECT COUNT(DISTINCT a.source_id)
        FROM article_stories as2
        JOIN articles a ON as2.article_id = a.id
        WHERE as2.story_id = OLD.story_id
    ),
    updated_at = datetime('now')
    WHERE id = OLD.story_id;
END;

-- Update author article count
CREATE TRIGGER IF NOT EXISTS trg_articles_insert_author
AFTER INSERT ON articles
WHEN NEW.author_ids IS NOT NULL
BEGIN
    UPDATE authors
    SET article_count = article_count + 1,
        last_seen_at = datetime('now'),
        updated_at = datetime('now')
    WHERE id IN (SELECT value FROM json_each(NEW.author_ids));
END;

-- Update keyword subscriber count when subscriptions change
CREATE TRIGGER IF NOT EXISTS trg_keyword_sub_insert
AFTER INSERT ON customer_keyword_subscriptions
BEGIN
    UPDATE keywords
    SET subscriber_count = (
        SELECT COUNT(*) FROM customer_keyword_subscriptions WHERE keyword_id = NEW.keyword_id
    ),
    updated_at = datetime('now')
    WHERE id = NEW.keyword_id;
END;

CREATE TRIGGER IF NOT EXISTS trg_keyword_sub_delete
AFTER DELETE ON customer_keyword_subscriptions
BEGIN
    UPDATE keywords
    SET subscriber_count = (
        SELECT COUNT(*) FROM customer_keyword_subscriptions WHERE keyword_id = OLD.keyword_id
    ),
    updated_at = datetime('now')
    WHERE id = OLD.keyword_id;
END;

-- Update topic keyword count
CREATE TRIGGER IF NOT EXISTS trg_topic_keyword_insert
AFTER INSERT ON topic_keywords
BEGIN
    UPDATE topics
    SET keyword_count = (
        SELECT COUNT(*) FROM topic_keywords WHERE topic_id = NEW.topic_id
    ),
    updated_at = datetime('now')
    WHERE id = NEW.topic_id;
END;

CREATE TRIGGER IF NOT EXISTS trg_topic_keyword_delete
AFTER DELETE ON topic_keywords
BEGIN
    UPDATE topics
    SET keyword_count = (
        SELECT COUNT(*) FROM topic_keywords WHERE topic_id = OLD.topic_id
    ),
    updated_at = datetime('now')
    WHERE id = OLD.topic_id;
END;

-- Update keyword article stats when matches are found
CREATE TRIGGER IF NOT EXISTS trg_keyword_article_insert
AFTER INSERT ON keyword_articles
BEGIN
    UPDATE keywords
    SET last_article_found_at = datetime('now'),
        updated_at = datetime('now')
    WHERE id = NEW.keyword_id;
END;

-- LEGACY: Update keyword_set article count (deprecated)
CREATE TRIGGER IF NOT EXISTS trg_keyword_set_crawl
AFTER UPDATE OF last_crawled_at ON keyword_sets
BEGIN
    UPDATE keyword_sets
    SET updated_at = datetime('now')
    WHERE id = NEW.id;
END;

-- Update URL hit count on re-discovery
CREATE TRIGGER IF NOT EXISTS trg_urls_hit
AFTER UPDATE OF last_seen_at ON urls
BEGIN
    UPDATE urls
    SET hit_count = hit_count + 1
    WHERE id = NEW.id;
END;

-- ============================================================================
-- MIGRATION 011: Projects & Focus-Based Extraction
-- ============================================================================

-- ----------------------------------------------------------------------------
-- PROJECTS: Customer campaigns with specific focus
-- ----------------------------------------------------------------------------
-- Projects allow customers to create focused monitoring campaigns that inform
-- extraction. For example, a "Funding Tracker" project would prioritize the
-- funding_rounds extractor for articles matching project keywords.
-- ----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS projects (
    id TEXT PRIMARY KEY DEFAULT (lower(hex(randomblob(16)))),
    customer_id TEXT NOT NULL REFERENCES customers(id) ON DELETE CASCADE,
    name TEXT NOT NULL,                     -- "Funding Tracker", "Earnings Watch"
    description TEXT,

    -- Project focus (informs extraction)
    focus_type TEXT NOT NULL,               -- 'funding_rounds', 'product_launches', 'earnings', 'acquisitions', 'custom'
    focus_description TEXT,                 -- LLM-readable description of what to extract

    -- Stats
    article_count INTEGER DEFAULT 0,
    extraction_count INTEGER DEFAULT 0,

    -- Status
    is_active INTEGER DEFAULT 1,
    created_at TEXT DEFAULT (datetime('now')),
    updated_at TEXT DEFAULT (datetime('now'))
);

CREATE INDEX idx_projects_customer ON projects(customer_id);
CREATE INDEX idx_projects_focus ON projects(focus_type);
CREATE INDEX idx_projects_active ON projects(is_active) WHERE is_active = 1;

-- ----------------------------------------------------------------------------
-- PROJECT_KEYWORDS: Keywords associated with a project
-- ----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS project_keywords (
    project_id TEXT NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
    keyword_id TEXT NOT NULL REFERENCES keywords(id) ON DELETE CASCADE,
    created_at TEXT DEFAULT (datetime('now')),
    PRIMARY KEY (project_id, keyword_id)
);

CREATE INDEX idx_project_keywords_project ON project_keywords(project_id);
CREATE INDEX idx_project_keywords_keyword ON project_keywords(keyword_id);

-- ----------------------------------------------------------------------------
-- PROJECT_EXTRACTORS: Which extractors run for a project, with config
-- ----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS project_extractors (
    project_id TEXT NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
    extractor_id TEXT NOT NULL REFERENCES extractors(id) ON DELETE CASCADE,
    is_enabled INTEGER DEFAULT 1,
    config TEXT,                            -- Project-specific config overrides (JSON)
    priority_override INTEGER,              -- Override default priority for this project
    created_at TEXT DEFAULT (datetime('now')),
    PRIMARY KEY (project_id, extractor_id)
);

CREATE INDEX idx_project_extractors_project ON project_extractors(project_id);
CREATE INDEX idx_project_extractors_extractor ON project_extractors(extractor_id);

-- Trigger: Update project stats when articles match
CREATE TRIGGER IF NOT EXISTS trg_project_article_count
AFTER INSERT ON keyword_articles
BEGIN
    UPDATE projects
    SET article_count = article_count + 1,
        updated_at = datetime('now')
    WHERE id IN (
        SELECT pk.project_id
        FROM project_keywords pk
        WHERE pk.keyword_id = NEW.keyword_id
    );
END;

-- ============================================================================
-- MIGRATION 012: Enhanced Extractors
-- ============================================================================

-- Add funding_rounds extractor
INSERT OR IGNORE INTO extractors (id, name, type, output_schema, priority, description) VALUES
    ('funding_rounds', 'Funding Rounds', 'ai', '{
        "type": "array",
        "items": {
            "type": "object",
            "properties": {
                "company_name": {"type": "string"},
                "company_url": {"type": "string", "format": "uri"},
                "round_type": {"type": "string", "enum": ["pre_seed", "seed", "series_a", "series_b", "series_c", "series_d", "series_e", "growth", "debt", "bridge", "ipo", "spac", "other"]},
                "amount_usd": {"type": "number"},
                "amount_raw": {"type": "string"},
                "currency": {"type": "string"},
                "valuation_usd": {"type": "number"},
                "valuation_raw": {"type": "string"},
                "lead_investors": {"type": "array", "items": {"type": "string"}},
                "other_investors": {"type": "array", "items": {"type": "string"}},
                "announced_date": {"type": "string", "format": "date"},
                "use_of_funds": {"type": "string"},
                "sector": {"type": "string"},
                "stage": {"type": "string"},
                "confidence": {"type": "number"}
            },
            "required": ["company_name", "round_type"]
        }
    }', 45, 'Extract funding round details from news articles');

-- Update events extractor with enhanced date handling
UPDATE extractors SET output_schema = '{
    "type": "array",
    "items": {
        "type": "object",
        "properties": {
            "name": {"type": "string"},
            "type": {"type": "string", "enum": ["conference", "earnings", "product_launch", "regulatory", "election", "ipo", "acquisition", "layoff", "other"]},
            "start_date": {"type": "string", "format": "date"},
            "start_time": {"type": "string", "format": "time"},
            "end_date": {"type": "string", "format": "date"},
            "end_time": {"type": "string", "format": "time"},
            "is_multi_day": {"type": "boolean"},
            "timezone": {"type": "string"},
            "location": {"type": "string"},
            "organizer": {"type": "string"},
            "description": {"type": "string"},
            "url": {"type": "string", "format": "uri"},
            "recurrence": {"type": "string"},
            "confidence": {"type": "number"}
        },
        "required": ["name", "start_date"]
    }
}' WHERE id = 'events';

-- ============================================================================
-- MIGRATION 013: Audit Logging
-- ============================================================================

-- ----------------------------------------------------------------------------
-- AUDIT_LOG: Comprehensive audit trail for all mutations
-- ----------------------------------------------------------------------------
-- Tracks all data changes for compliance, debugging, and security analysis.
-- This complements admin_audit_log by tracking ALL system changes, not just admin actions.
-- ----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS audit_log (
    id TEXT PRIMARY KEY DEFAULT (lower(hex(randomblob(16)))),

    -- Who performed the action
    actor_type TEXT NOT NULL CHECK (actor_type IN ('system', 'admin', 'customer', 'api', 'workflow', 'cron')),
    actor_id TEXT,                          -- admin_user_id, customer_id, or workflow name

    -- What action was performed
    action TEXT NOT NULL CHECK (action IN ('create', 'update', 'delete', 'archive', 'restore', 'export', 'import', 'process', 'notify')),

    -- On what resource
    resource_type TEXT NOT NULL,            -- 'article', 'customer', 'keyword', 'project', etc.
    resource_id TEXT,                       -- Primary key of affected resource

    -- Change details
    changes TEXT,                           -- JSON: {"field": {"old": "...", "new": "..."}}
    metadata TEXT,                          -- JSON: Additional context

    -- Request context
    request_id TEXT,                        -- Correlation ID for tracing
    ip_address TEXT,
    user_agent TEXT,

    -- Timestamp
    created_at TEXT DEFAULT (datetime('now'))
);

CREATE INDEX idx_audit_actor ON audit_log(actor_type, actor_id);
CREATE INDEX idx_audit_action ON audit_log(action);
CREATE INDEX idx_audit_resource ON audit_log(resource_type, resource_id);
CREATE INDEX idx_audit_created ON audit_log(created_at DESC);
CREATE INDEX idx_audit_request ON audit_log(request_id) WHERE request_id IS NOT NULL;

-- Partition-friendly index for date-based queries
CREATE INDEX idx_audit_date ON audit_log(date(created_at));

-- ============================================================================
-- SEED DATA: Initial configuration
-- ============================================================================

-- Default scheduled jobs
INSERT OR IGNORE INTO scheduled_jobs (id, job_type, cron_expression, is_enabled, config) VALUES
    ('job_crawl_15min', 'crawl', '*/15 * * * *', 1, '{"priority": "high", "frequency": "every_15min"}'),
    ('job_crawl_hourly', 'crawl', '0 * * * *', 1, '{"priority": "normal", "frequency": "hourly"}'),
    ('job_crawl_daily', 'crawl', '0 6 * * *', 1, '{"priority": "low", "frequency": "daily"}'),
    ('job_recluster', 'recluster', '0 */4 * * *', 1, '{"window_hours": 24}'),
    ('job_retention', 'retention', '0 2 * * *', 1, '{"article_days": 90, "story_days": null}'),
    ('job_cost_rollup', 'cost_rollup', '5 0 * * *', 1, '{}'),
    ('job_authority', 'authority_recalc', '0 3 * * 0', 1, '{}');

-- Default global budget (alert at $100/day)
INSERT OR IGNORE INTO cost_budgets (id, scope, period, budget_micros, alert_threshold_pct, hard_limit) VALUES
    ('budget_global_daily', 'global', 'daily', 100000000, 80, 0),
    ('budget_global_monthly', 'global', 'monthly', 2000000000, 90, 0);

-- ============================================================================
-- MIGRATION 014: Analytics Mode Configuration (D1/ClickHouse Toggle)
-- ============================================================================
--
-- DEV MODE (default): All analytics data stays in D1
-- PROD MODE: High-volume analytics streams to ClickHouse
--
-- This allows development without ClickHouse, then flip a switch for production.
-- When ANALYTICS_MODE = 'clickhouse':
--   - cost_events → streams to ClickHouse, 7-day retention in D1
--   - customer_events → streams to ClickHouse, NOT stored in D1
--   - pipeline_metrics → streams to ClickHouse only
--   - article_metrics → streams to ClickHouse only
--   - keyword_velocity → streams to ClickHouse, 30-day retention in D1
--   - extraction_items → streams to ClickHouse, 30-day retention in D1
-- ============================================================================

-- ----------------------------------------------------------------------------
-- SYSTEM_CONFIG: Global configuration key-value store
-- ----------------------------------------------------------------------------
-- Use this for feature flags, mode switches, and system-wide settings.
-- Accessed via KV cache for performance, D1 is source of truth.
-- ----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS system_config (
    key TEXT PRIMARY KEY,
    value TEXT NOT NULL,
    type TEXT DEFAULT 'string' CHECK (type IN ('string', 'number', 'boolean', 'json')),
    description TEXT,
    is_secret INTEGER DEFAULT 0,        -- If 1, value is encrypted reference to secret
    updated_by TEXT,                    -- admin_user_id or 'system'
    created_at TEXT DEFAULT (datetime('now')),
    updated_at TEXT DEFAULT (datetime('now'))
);

-- Seed default configuration
INSERT OR IGNORE INTO system_config (key, value, type, description) VALUES
    -- Analytics Mode Toggle (THE BIG SWITCH)
    ('analytics.mode', 'd1', 'string', 'Analytics storage mode: "d1" (dev) or "clickhouse" (prod)'),

    -- ClickHouse Configuration (only used when analytics.mode = clickhouse)
    ('clickhouse.enabled', 'false', 'boolean', 'Master switch for ClickHouse integration'),
    ('clickhouse.host', '', 'string', 'ClickHouse host (from secrets)'),
    ('clickhouse.database', 'storyintel', 'string', 'ClickHouse database name'),
    ('clickhouse.batch_size', '100', 'number', 'Records per batch insert'),
    ('clickhouse.flush_interval_ms', '5000', 'number', 'Max time between flushes'),

    -- D1 Retention (when ClickHouse is enabled, how long to keep in D1)
    ('retention.cost_events_days', '7', 'number', 'Days to retain cost_events in D1 when ClickHouse enabled'),
    ('retention.customer_events_days', '0', 'number', 'Days to retain customer_events in D1 (0 = ClickHouse only)'),
    ('retention.keyword_velocity_days', '30', 'number', 'Days to retain keyword_velocity in D1'),
    ('retention.extraction_items_days', '30', 'number', 'Days to retain extraction_items in D1'),

    -- Circuit Breakers
    ('circuit.queue_max_backlog', '5000', 'number', 'Pause crawl if any queue exceeds this'),
    ('circuit.queue_alert_threshold', '2500', 'number', 'Alert when queue exceeds this'),
    ('circuit.cost_daily_hard_limit_usd', '500', 'number', 'Hard stop if daily costs exceed this'),
    ('circuit.cost_hourly_alert_usd', '50', 'number', 'Alert if hourly costs exceed this'),

    -- Feature Flags
    ('feature.near_duplicate_detection', 'false', 'boolean', 'Enable MinHash near-duplicate detection'),
    ('feature.paywall_detection', 'false', 'boolean', 'Enable paywall content detection'),
    ('feature.transaction_batching', 'true', 'boolean', 'Enable transaction batching for bulk inserts'),
    ('feature.kv_feed_cache', 'true', 'boolean', 'Cache feed results in KV'),
    ('feature.kv_feed_cache_ttl_seconds', '300', 'number', 'Feed cache TTL (5 min default)');

-- Index for faster lookups by prefix
CREATE INDEX idx_system_config_prefix ON system_config(key);

-- ----------------------------------------------------------------------------
-- PIPELINE_METRICS: Technical metrics for observability (D1 in dev, CH in prod)
-- ----------------------------------------------------------------------------
-- In dev mode: stored in D1, queried directly
-- In prod mode: streamed to ClickHouse, short D1 retention for recent queries
-- ----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS pipeline_metrics (
    id TEXT PRIMARY KEY DEFAULT (lower(hex(randomblob(16)))),
    timestamp TEXT DEFAULT (datetime('now')),

    -- What stage/operation
    stage TEXT NOT NULL,                -- 'crawl', 'extract', 'classify', 'cluster', 'match', 'notify'
    operation TEXT,                     -- More specific: 'fetch_direct', 'fetch_zenrows', 'embed_article'

    -- Performance
    duration_ms INTEGER NOT NULL,
    success INTEGER DEFAULT 1,
    error_type TEXT,
    error_message TEXT,

    -- Context
    article_id TEXT,
    keyword_id TEXT,
    workflow_id TEXT,

    -- Cost (if applicable)
    cost_micros INTEGER DEFAULT 0,

    -- Dimensions for aggregation
    source_adapter TEXT,                -- 'google_news', 'twitter', etc.
    fetch_method TEXT,                  -- 'direct', 'zenrows', 'rapidapi'
    extractor_id TEXT,

    created_at TEXT DEFAULT (datetime('now'))
);

CREATE INDEX idx_pipeline_metrics_timestamp ON pipeline_metrics(timestamp DESC);
CREATE INDEX idx_pipeline_metrics_stage ON pipeline_metrics(stage);
CREATE INDEX idx_pipeline_metrics_success ON pipeline_metrics(success) WHERE success = 0;
CREATE INDEX idx_pipeline_metrics_article ON pipeline_metrics(article_id) WHERE article_id IS NOT NULL;

-- ----------------------------------------------------------------------------
-- ARTICLE_METRICS: Engagement metrics (D1 in dev, CH in prod)
-- ----------------------------------------------------------------------------
-- Tracks article-level engagement over time for trend detection
-- ----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS article_metrics (
    id TEXT PRIMARY KEY DEFAULT (lower(hex(randomblob(16)))),
    timestamp TEXT DEFAULT (datetime('now')),

    article_id TEXT NOT NULL REFERENCES articles(id) ON DELETE CASCADE,
    source_id TEXT,

    -- Engagement snapshot
    view_count INTEGER DEFAULT 0,
    click_count INTEGER DEFAULT 0,
    save_count INTEGER DEFAULT 0,
    share_count INTEGER DEFAULT 0,
    dismiss_count INTEGER DEFAULT 0,

    -- Social metrics snapshot (from SharedCount)
    social_engagement INTEGER DEFAULT 0,

    -- Backlink metrics snapshot (from DataForSEO)
    backlink_count INTEGER DEFAULT 0,
    referring_domains INTEGER DEFAULT 0,

    created_at TEXT DEFAULT (datetime('now'))
);

CREATE INDEX idx_article_metrics_timestamp ON article_metrics(timestamp DESC);
CREATE INDEX idx_article_metrics_article ON article_metrics(article_id);

-- ----------------------------------------------------------------------------
-- SOURCE_HEALTH: Source crawl health (D1 in dev, CH in prod)
-- ----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS source_health (
    id TEXT PRIMARY KEY DEFAULT (lower(hex(randomblob(16)))),
    timestamp TEXT DEFAULT (datetime('now')),

    source_id TEXT NOT NULL REFERENCES sources(id) ON DELETE CASCADE,

    -- Crawl stats
    crawl_success_count INTEGER DEFAULT 0,
    crawl_failure_count INTEGER DEFAULT 0,
    avg_response_time_ms INTEGER,

    -- Content quality
    articles_found INTEGER DEFAULT 0,
    articles_deduplicated INTEGER DEFAULT 0,
    articles_paywall_blocked INTEGER DEFAULT 0,

    -- Rate limiting
    rate_limit_hits INTEGER DEFAULT 0,

    created_at TEXT DEFAULT (datetime('now'))
);

CREATE INDEX idx_source_health_timestamp ON source_health(timestamp DESC);
CREATE INDEX idx_source_health_source ON source_health(source_id);

-- ----------------------------------------------------------------------------
-- ENTITY_VELOCITY: Entity trending (D1 in dev, CH in prod)
-- ----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS entity_velocity (
    id TEXT PRIMARY KEY DEFAULT (lower(hex(randomblob(16)))),
    hour TEXT NOT NULL,                 -- Truncated to hour: '2024-12-19T14:00:00'

    entity_id TEXT NOT NULL REFERENCES entities(id) ON DELETE CASCADE,
    entity_name TEXT NOT NULL,
    entity_type TEXT NOT NULL,

    -- Velocity metrics
    article_count INTEGER DEFAULT 0,
    source_count INTEGER DEFAULT 0,
    mention_count INTEGER DEFAULT 0,
    avg_sentiment REAL,

    created_at TEXT DEFAULT (datetime('now')),

    UNIQUE(entity_id, hour)
);

CREATE INDEX idx_entity_velocity_hour ON entity_velocity(hour DESC);
CREATE INDEX idx_entity_velocity_entity ON entity_velocity(entity_id);
CREATE INDEX idx_entity_velocity_type ON entity_velocity(entity_type);

-- ============================================================================
-- MIGRATION 015: Critical Performance Indexes
-- ============================================================================
-- These indexes address the bottlenecks identified in the architecture audit.
-- ============================================================================

-- Compound index for keyword crawl scheduling (10x faster)
CREATE INDEX IF NOT EXISTS idx_keywords_active_next_crawl
    ON keywords(is_active, next_crawl_at)
    WHERE is_active = 1 AND subscriber_count > 0;

-- Compound index for feed queries (10x faster)
CREATE INDEX IF NOT EXISTS idx_cas_customer_score
    ON customer_article_scores(customer_id, relevance_score DESC)
    WHERE relevance_score > 0.5;

-- Index for story clustering queries
CREATE INDEX IF NOT EXISTS idx_article_stories_story_confidence
    ON article_stories(story_id, confidence DESC);

-- Index for extraction item lookups
CREATE INDEX IF NOT EXISTS idx_extraction_items_article_extractor
    ON extraction_items(article_id, extractor_id);

-- Index for cost analytics (when querying D1 in dev mode)
CREATE INDEX IF NOT EXISTS idx_cost_events_timestamp_service
    ON cost_events(timestamp DESC, service);

-- Index for recent customer events (feed activity)
CREATE INDEX IF NOT EXISTS idx_customer_events_customer_recent
    ON customer_events(customer_id, created_at DESC);

-- Index for pipeline metrics aggregation
CREATE INDEX IF NOT EXISTS idx_pipeline_metrics_stage_time
    ON pipeline_metrics(stage, timestamp DESC);

-- ============================================================================
-- VIEWS: Analytics Views (work in both D1 and ClickHouse modes)
-- ============================================================================

-- Hourly cost summary (for dashboard)
CREATE VIEW IF NOT EXISTS v_hourly_costs AS
SELECT
    strftime('%Y-%m-%dT%H:00:00', timestamp) as hour,
    service,
    SUM(cost_micros) as total_micros,
    SUM(cost_micros) / 1000000.0 as total_usd,
    COUNT(*) as operation_count,
    AVG(latency_ms) as avg_latency_ms,
    SUM(CASE WHEN success = 1 THEN 1 ELSE 0 END) as success_count,
    SUM(CASE WHEN success = 0 THEN 1 ELSE 0 END) as error_count
FROM cost_events
WHERE timestamp > datetime('now', '-24 hours')
GROUP BY strftime('%Y-%m-%dT%H:00:00', timestamp), service
ORDER BY hour DESC;

-- Pipeline health by stage (last 24h)
CREATE VIEW IF NOT EXISTS v_pipeline_stage_health AS
SELECT
    stage,
    COUNT(*) as total_operations,
    SUM(CASE WHEN success = 1 THEN 1 ELSE 0 END) as success_count,
    SUM(CASE WHEN success = 0 THEN 1 ELSE 0 END) as error_count,
    ROUND(100.0 * SUM(CASE WHEN success = 1 THEN 1 ELSE 0 END) / COUNT(*), 2) as success_rate,
    AVG(duration_ms) as avg_duration_ms,
    MAX(duration_ms) as max_duration_ms,
    SUM(cost_micros) / 1000000.0 as total_cost_usd
FROM pipeline_metrics
WHERE timestamp > datetime('now', '-24 hours')
GROUP BY stage
ORDER BY total_operations DESC;

-- Entity trending (last 24h)
CREATE VIEW IF NOT EXISTS v_trending_entities AS
SELECT
    e.id,
    e.canonical_name,
    e.type,
    SUM(ev.article_count) as total_articles,
    SUM(ev.mention_count) as total_mentions,
    AVG(ev.avg_sentiment) as avg_sentiment,
    COUNT(DISTINCT ev.hour) as hours_active
FROM entities e
JOIN entity_velocity ev ON e.id = ev.entity_id
WHERE ev.hour > datetime('now', '-24 hours')
GROUP BY e.id
HAVING total_articles > 5
ORDER BY total_articles DESC
LIMIT 50;

-- Queue health estimate (based on recent pipeline metrics)
CREATE VIEW IF NOT EXISTS v_queue_health_estimate AS
SELECT
    stage as queue_name,
    COUNT(*) as recent_processed,
    AVG(duration_ms) as avg_processing_ms,
    SUM(CASE WHEN success = 0 THEN 1 ELSE 0 END) as recent_errors,
    MAX(timestamp) as last_activity
FROM pipeline_metrics
WHERE timestamp > datetime('now', '-1 hour')
GROUP BY stage;

-- ============================================================================
-- MIGRATION 016: Multi-Taxonomy Classification (Schema Upgrade)
-- ============================================================================
-- Replaces flat article_classifications with proper junction tables.
-- Supports multiple taxonomies per article, customer-specific taxonomies,
-- and easy taxonomy switching without data migration.
--
-- NOTE: The old article_classifications table is DEPRECATED but kept for
-- backward compatibility during migration. New code should use these tables.
-- ============================================================================

-- ----------------------------------------------------------------------------
-- ARTICLE_LABEL_SCORES: Per-label classification scores (Core Junction)
-- ----------------------------------------------------------------------------
-- This is the core junction table. Each row = one article + one taxonomy label.
-- An article can have scores for labels across multiple taxonomies.
-- ----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS article_label_scores (
    id TEXT PRIMARY KEY DEFAULT (lower(hex(randomblob(16)))),
    article_id TEXT NOT NULL REFERENCES articles(id) ON DELETE CASCADE,
    taxonomy_id TEXT NOT NULL REFERENCES taxonomies(id) ON DELETE CASCADE,
    label_id TEXT NOT NULL REFERENCES taxonomy_labels(id) ON DELETE CASCADE,

    -- Classification score (0-1, higher = more confident match)
    score REAL NOT NULL CHECK (score >= 0 AND score <= 1),

    -- How was this classification made?
    method TEXT NOT NULL CHECK (method IN ('rules', 'vector', 'llm', 'human', 'inherited')),

    -- Confidence in this specific classification
    confidence REAL DEFAULT 1.0 CHECK (confidence >= 0 AND confidence <= 1),

    -- Is this a primary classification or secondary?
    is_primary INTEGER DEFAULT 0,

    -- Hierarchy: was this inferred from a child label?
    inferred_from_label_id TEXT REFERENCES taxonomy_labels(id),

    -- Cost tracking for LLM classifications
    cost_micros INTEGER DEFAULT 0,

    -- Timestamps
    created_at TEXT DEFAULT (datetime('now')),
    updated_at TEXT DEFAULT (datetime('now')),

    -- Unique constraint: one score per article per label
    UNIQUE(article_id, label_id)
);

-- Performance indexes for article_label_scores
CREATE INDEX idx_als_article ON article_label_scores(article_id);
CREATE INDEX idx_als_taxonomy ON article_label_scores(taxonomy_id);
CREATE INDEX idx_als_label ON article_label_scores(label_id);
CREATE INDEX idx_als_score ON article_label_scores(score DESC);
CREATE INDEX idx_als_primary ON article_label_scores(is_primary) WHERE is_primary = 1;
CREATE INDEX idx_als_method ON article_label_scores(method);

-- Composite index for common query: "get all labels for article in taxonomy"
CREATE INDEX idx_als_article_taxonomy ON article_label_scores(article_id, taxonomy_id);

-- Composite index for "articles with this label"
CREATE INDEX idx_als_label_score ON article_label_scores(label_id, score DESC);

-- ----------------------------------------------------------------------------
-- ARTICLE_CLASSIFICATION_SUMMARY: Denormalized summary per article
-- ----------------------------------------------------------------------------
-- Provides quick access to classification metadata without querying
-- the junction table. Updated via triggers.
-- ----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS article_classification_summary (
    article_id TEXT PRIMARY KEY REFERENCES articles(id) ON DELETE CASCADE,

    -- Overall classification status
    status TEXT DEFAULT 'pending' CHECK (status IN ('pending', 'partial', 'complete', 'failed', 'needs_review')),

    -- Scalar values (not taxonomy-based, stored directly)
    sentiment REAL CHECK (sentiment >= -1 AND sentiment <= 1),
    sentiment_confidence REAL,
    sentiment_method TEXT,
    political_lean REAL CHECK (political_lean >= -1 AND political_lean <= 1),
    political_lean_confidence REAL,

    -- Counts for quick stats
    total_labels INTEGER DEFAULT 0,
    primary_labels INTEGER DEFAULT 0,
    taxonomies_classified INTEGER DEFAULT 0,

    -- Quality metrics
    avg_confidence REAL,
    min_confidence REAL,
    needs_review INTEGER DEFAULT 0,

    -- Processing metadata
    last_classified_at TEXT,
    last_method TEXT,
    total_cost_micros INTEGER DEFAULT 0,

    -- Version tracking (for re-classification)
    classification_version TEXT DEFAULT '2.0',

    created_at TEXT DEFAULT (datetime('now')),
    updated_at TEXT DEFAULT (datetime('now'))
);

CREATE INDEX idx_acs_status ON article_classification_summary(status);
CREATE INDEX idx_acs_sentiment ON article_classification_summary(sentiment);
CREATE INDEX idx_acs_needs_review ON article_classification_summary(needs_review) WHERE needs_review = 1;
CREATE INDEX idx_acs_last_classified ON article_classification_summary(last_classified_at DESC);

-- ----------------------------------------------------------------------------
-- TAXONOMY_PRIORITY: Configure which taxonomy is primary for each dimension
-- ----------------------------------------------------------------------------
-- Allows switching primary taxonomies (e.g., from DataForSEO to custom)
-- without schema changes or data migration.
-- ----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS taxonomy_priority (
    id TEXT PRIMARY KEY DEFAULT (lower(hex(randomblob(16)))),

    -- Scope: global or per-customer
    customer_id TEXT REFERENCES customers(id) ON DELETE CASCADE,  -- NULL = global default

    -- Dimension this taxonomy is primary for
    dimension TEXT NOT NULL CHECK (dimension IN ('topic', 'industry', 'tactic', 'risk', 'sentiment', 'custom')),

    -- The taxonomy to use
    taxonomy_id TEXT NOT NULL REFERENCES taxonomies(id) ON DELETE CASCADE,

    -- Priority order (lower = higher priority, for fallbacks)
    priority INTEGER DEFAULT 0,

    -- Status
    is_active INTEGER DEFAULT 1,

    created_at TEXT DEFAULT (datetime('now')),
    updated_at TEXT DEFAULT (datetime('now')),

    -- One primary taxonomy per dimension per scope
    UNIQUE(customer_id, dimension, taxonomy_id)
);

CREATE INDEX idx_tp_customer ON taxonomy_priority(customer_id);
CREATE INDEX idx_tp_dimension ON taxonomy_priority(dimension);
CREATE INDEX idx_tp_active ON taxonomy_priority(is_active) WHERE is_active = 1;

-- Seed default taxonomy priorities (DataForSEO as primary for topics)
INSERT OR IGNORE INTO taxonomy_priority (id, customer_id, dimension, taxonomy_id, priority) VALUES
    ('tp_default_topic', NULL, 'topic', 'tax_dataforseo', 0),
    ('tp_default_industry', NULL, 'industry', 'tax_industries', 0),
    ('tp_default_tactic', NULL, 'tactic', 'tax_pr_tactics', 0),
    ('tp_default_risk', NULL, 'risk', 'tax_risk_signals', 0),
    ('tp_default_sentiment', NULL, 'sentiment', 'tax_sentiment', 0);

-- ----------------------------------------------------------------------------
-- CUSTOMER_TAXONOMY_LABELS: Customer-specific label preferences
-- ----------------------------------------------------------------------------
-- Customers can customize which labels they care about, set thresholds,
-- or add custom names/aliases for labels.
-- ----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS customer_taxonomy_labels (
    customer_id TEXT NOT NULL REFERENCES customers(id) ON DELETE CASCADE,
    label_id TEXT NOT NULL REFERENCES taxonomy_labels(id) ON DELETE CASCADE,

    -- Customer-specific settings
    is_enabled INTEGER DEFAULT 1,           -- Include in their feed/alerts
    min_score_threshold REAL DEFAULT 0.5,   -- Only show if score above this
    custom_name TEXT,                       -- Customer's preferred name for label
    notify_on_match INTEGER DEFAULT 0,      -- Trigger notification when matched

    -- Customer's feedback on this label
    relevance_feedback TEXT CHECK (relevance_feedback IN ('relevant', 'not_relevant', NULL)),
    feedback_count INTEGER DEFAULT 0,

    created_at TEXT DEFAULT (datetime('now')),
    updated_at TEXT DEFAULT (datetime('now')),
    PRIMARY KEY (customer_id, label_id)
);

CREATE INDEX idx_ctl_customer ON customer_taxonomy_labels(customer_id);
CREATE INDEX idx_ctl_label ON customer_taxonomy_labels(label_id);
CREATE INDEX idx_ctl_notify ON customer_taxonomy_labels(notify_on_match) WHERE notify_on_match = 1;
CREATE INDEX idx_ctl_enabled ON customer_taxonomy_labels(customer_id, is_enabled) WHERE is_enabled = 1;

-- ----------------------------------------------------------------------------
-- TRIGGERS: Keep article_classification_summary in sync
-- ----------------------------------------------------------------------------

-- Update summary when labels are added
CREATE TRIGGER IF NOT EXISTS trg_als_insert_summary
AFTER INSERT ON article_label_scores
BEGIN
    INSERT INTO article_classification_summary (
        article_id, status, total_labels, primary_labels, taxonomies_classified,
        last_classified_at, last_method, updated_at
    )
    VALUES (
        NEW.article_id, 'partial', 1,
        CASE WHEN NEW.is_primary = 1 THEN 1 ELSE 0 END,
        1, datetime('now'), NEW.method, datetime('now')
    )
    ON CONFLICT(article_id) DO UPDATE SET
        total_labels = total_labels + 1,
        primary_labels = primary_labels + CASE WHEN NEW.is_primary = 1 THEN 1 ELSE 0 END,
        taxonomies_classified = (
            SELECT COUNT(DISTINCT taxonomy_id)
            FROM article_label_scores
            WHERE article_id = NEW.article_id
        ),
        total_cost_micros = total_cost_micros + NEW.cost_micros,
        last_classified_at = datetime('now'),
        last_method = NEW.method,
        updated_at = datetime('now');
END;

-- Update summary when labels are removed
CREATE TRIGGER IF NOT EXISTS trg_als_delete_summary
AFTER DELETE ON article_label_scores
BEGIN
    UPDATE article_classification_summary
    SET total_labels = total_labels - 1,
        primary_labels = primary_labels - CASE WHEN OLD.is_primary = 1 THEN 1 ELSE 0 END,
        taxonomies_classified = (
            SELECT COUNT(DISTINCT taxonomy_id)
            FROM article_label_scores
            WHERE article_id = OLD.article_id
        ),
        updated_at = datetime('now')
    WHERE article_id = OLD.article_id;
END;

-- Update summary when label scores change
CREATE TRIGGER IF NOT EXISTS trg_als_update_summary
AFTER UPDATE ON article_label_scores
BEGIN
    UPDATE article_classification_summary
    SET avg_confidence = (
            SELECT AVG(confidence)
            FROM article_label_scores
            WHERE article_id = NEW.article_id
        ),
        min_confidence = (
            SELECT MIN(confidence)
            FROM article_label_scores
            WHERE article_id = NEW.article_id
        ),
        needs_review = CASE
            WHEN (SELECT MIN(confidence) FROM article_label_scores WHERE article_id = NEW.article_id) < 0.5
            THEN 1 ELSE 0
        END,
        updated_at = datetime('now')
    WHERE article_id = NEW.article_id;
END;

-- ----------------------------------------------------------------------------
-- VIEWS: Multi-Taxonomy Classification Views
-- ----------------------------------------------------------------------------

-- Get all classifications for an article with taxonomy info
CREATE VIEW IF NOT EXISTS v_article_classifications_full AS
SELECT
    als.article_id,
    t.id as taxonomy_id,
    t.name as taxonomy_name,
    t.category as taxonomy_category,
    tl.id as label_id,
    tl.label,
    tl.parent_id as parent_label_id,
    pl.label as parent_label,
    als.score,
    als.method,
    als.confidence,
    als.is_primary,
    als.created_at
FROM article_label_scores als
JOIN taxonomy_labels tl ON als.label_id = tl.id
JOIN taxonomies t ON als.taxonomy_id = t.id
LEFT JOIN taxonomy_labels pl ON tl.parent_id = pl.id
ORDER BY als.article_id, t.category, als.score DESC;

-- Get primary taxonomy for each dimension
CREATE VIEW IF NOT EXISTS v_primary_taxonomies AS
SELECT
    tp.dimension,
    tp.customer_id,
    t.id as taxonomy_id,
    t.name as taxonomy_name,
    t.slug as taxonomy_slug,
    tp.priority
FROM taxonomy_priority tp
JOIN taxonomies t ON tp.taxonomy_id = t.id
WHERE tp.is_active = 1
ORDER BY tp.customer_id NULLS FIRST, tp.dimension, tp.priority ASC;

-- Classification stats by taxonomy
CREATE VIEW IF NOT EXISTS v_taxonomy_classification_stats AS
SELECT
    t.id as taxonomy_id,
    t.name as taxonomy_name,
    t.category,
    COUNT(DISTINCT als.article_id) as articles_classified,
    COUNT(*) as total_labels_assigned,
    AVG(als.score) as avg_score,
    AVG(als.confidence) as avg_confidence,
    SUM(CASE WHEN als.method = 'rules' THEN 1 ELSE 0 END) as rules_count,
    SUM(CASE WHEN als.method = 'vector' THEN 1 ELSE 0 END) as vector_count,
    SUM(CASE WHEN als.method = 'llm' THEN 1 ELSE 0 END) as llm_count,
    SUM(CASE WHEN als.method = 'human' THEN 1 ELSE 0 END) as human_count,
    SUM(als.cost_micros) / 1000000.0 as total_cost_usd
FROM taxonomies t
LEFT JOIN article_label_scores als ON t.id = als.taxonomy_id
GROUP BY t.id
ORDER BY articles_classified DESC;

-- Low confidence classifications needing review
CREATE VIEW IF NOT EXISTS v_classifications_needing_review AS
SELECT
    als.article_id,
    a.headline,
    t.name as taxonomy_name,
    tl.label,
    als.score,
    als.confidence,
    als.method,
    als.created_at
FROM article_label_scores als
JOIN articles a ON als.article_id = a.id
JOIN taxonomy_labels tl ON als.label_id = tl.id
JOIN taxonomies t ON als.taxonomy_id = t.id
WHERE als.confidence < 0.5
ORDER BY als.confidence ASC, als.created_at DESC
LIMIT 100;

-- ============================================================================
-- END OF SCHEMA
-- ============================================================================
