-- Create reading statistics table
CREATE TABLE IF NOT EXISTS reading_stats
(
    id              INTEGER PRIMARY KEY AUTOINCREMENT,
    user_id         INTEGER            DEFAULT 1,   -- For future multi-user support
    article_id      INTEGER   NOT NULL REFERENCES articles (id) ON DELETE CASCADE,
    read_at         TIMESTAMP NOT NULL,
    reading_time    INTEGER,                        -- in seconds
    completion_rate REAL               DEFAULT 1.0, -- 0.0-1.0, how much of the article was read
    created_at      TIMESTAMP NOT NULL DEFAULT (datetime('now'))
);

-- Create filter presets table
CREATE TABLE IF NOT EXISTS filter_presets
(
    id              INTEGER PRIMARY KEY AUTOINCREMENT,
    name            TEXT      NOT NULL,
    filter_criteria TEXT      NOT NULL,           -- JSON string of filter parameters
    user_id         INTEGER            DEFAULT 1, -- For future multi-user support
    created_at      TIMESTAMP NOT NULL DEFAULT (datetime('now'))
);

-- Create share templates table
CREATE TABLE IF NOT EXISTS share_templates
(
    id               INTEGER PRIMARY KEY AUTOINCREMENT,
    name             TEXT      NOT NULL,
    format           TEXT      NOT NULL, -- 'text', 'markdown', 'html', 'json'
    template_content TEXT      NOT NULL,
    is_default       BOOLEAN   NOT NULL DEFAULT 0,
    created_at       TIMESTAMP NOT NULL DEFAULT (datetime('now'))
);

-- Create legacy migration tracking table
CREATE TABLE IF NOT EXISTS legacy_migration
(
    id              INTEGER PRIMARY KEY AUTOINCREMENT,
    old_filter_type TEXT      NOT NULL, -- 'country', 'category', etc.
    old_value       TEXT      NOT NULL,
    new_tag_ids     TEXT,               -- JSON array of tag IDs
    migrated_at     TIMESTAMP NOT NULL DEFAULT (datetime('now'))
);

-- Create indexes
CREATE INDEX IF NOT EXISTS idx_reading_stats_user_id ON reading_stats (user_id);
CREATE INDEX IF NOT EXISTS idx_reading_stats_article_id ON reading_stats (article_id);
CREATE INDEX IF NOT EXISTS idx_reading_stats_read_at ON reading_stats (read_at);
CREATE INDEX IF NOT EXISTS idx_filter_presets_user_id ON filter_presets (user_id);
CREATE INDEX IF NOT EXISTS idx_share_templates_format ON share_templates (format);
CREATE INDEX IF NOT EXISTS idx_legacy_migration_old_filter_type ON legacy_migration (old_filter_type);