-- sort local SQLite schema v2
-- 다운로드 시작 경로는 다르더라도 완료 후 MEDIA_SAVED 처리는 하나로 통합한다.

PRAGMA foreign_keys = ON;

CREATE TABLE IF NOT EXISTS download_batches (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    batch_key TEXT NOT NULL,
    source_mode TEXT NOT NULL,
    account_id INTEGER,
    sort_by TEXT NOT NULL DEFAULT '',
    filter_days INTEGER,
    requested_count INTEGER NOT NULL DEFAULT 0,
    batch_status TEXT NOT NULL DEFAULT 'PENDING',
    metadata_json TEXT NOT NULL DEFAULT '{}',
    started_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
    completed_at TEXT,
    created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(batch_key),
    FOREIGN KEY(account_id) REFERENCES social_accounts(id) ON DELETE SET NULL
);
CREATE INDEX IF NOT EXISTS idx_download_batches_mode_status
    ON download_batches(source_mode, batch_status, id DESC);

CREATE TABLE IF NOT EXISTS download_jobs (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    batch_id INTEGER,
    post_id INTEGER NOT NULL,
    source_mode TEXT NOT NULL,
    asset_kind TEXT NOT NULL,
    media_index INTEGER NOT NULL DEFAULT 0,
    rank_no INTEGER,
    source_url TEXT NOT NULL DEFAULT '',
    expected_file_name TEXT NOT NULL DEFAULT '',
    job_status TEXT NOT NULL DEFAULT 'PENDING',
    requested_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
    completed_at TEXT,
    last_error TEXT NOT NULL DEFAULT '',
    created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(batch_id, post_id, asset_kind, media_index),
    FOREIGN KEY(batch_id) REFERENCES download_batches(id) ON DELETE SET NULL,
    FOREIGN KEY(post_id) REFERENCES posts(id) ON DELETE CASCADE
);
CREATE INDEX IF NOT EXISTS idx_download_jobs_status
    ON download_jobs(job_status, source_mode, id);
CREATE INDEX IF NOT EXISTS idx_download_jobs_post
    ON download_jobs(post_id, asset_kind, media_index, id DESC);

CREATE TABLE IF NOT EXISTS media_saved_events (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    event_uuid TEXT NOT NULL,
    download_job_id INTEGER,
    post_id INTEGER NOT NULL,
    asset_id INTEGER NOT NULL,
    account_id INTEGER,
    shortcode TEXT NOT NULL,
    source_mode TEXT NOT NULL,
    asset_kind TEXT NOT NULL,
    media_index INTEGER NOT NULL DEFAULT 0,
    rank_no INTEGER,
    relative_path TEXT NOT NULL DEFAULT '',
    file_name TEXT NOT NULL,
    file_size INTEGER NOT NULL DEFAULT 0,
    md5 TEXT NOT NULL DEFAULT '',
    sha256 TEXT NOT NULL DEFAULT '',
    completed_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
    payload_json TEXT NOT NULL DEFAULT '{}',
    created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(event_uuid),
    FOREIGN KEY(download_job_id) REFERENCES download_jobs(id) ON DELETE SET NULL,
    FOREIGN KEY(post_id) REFERENCES posts(id) ON DELETE CASCADE,
    FOREIGN KEY(asset_id) REFERENCES media_assets(id) ON DELETE CASCADE,
    FOREIGN KEY(account_id) REFERENCES social_accounts(id) ON DELETE SET NULL
);
CREATE INDEX IF NOT EXISTS idx_media_saved_events_post_time
    ON media_saved_events(post_id, completed_at DESC, id DESC);
CREATE INDEX IF NOT EXISTS idx_media_saved_events_mode
    ON media_saved_events(source_mode, completed_at DESC, id DESC);

PRAGMA user_version = 2;
