-- sort local SQLite schema v3
-- Analyze8780 계정·게시물 조회, 10분 통계, soft-delete tombstone 구조.

PRAGMA foreign_keys = ON;

CREATE TABLE IF NOT EXISTS post_stat_10m (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    post_id INTEGER NOT NULL,
    bucket_at TEXT NOT NULL,
    observed_at TEXT NOT NULL,
    views INTEGER NOT NULL DEFAULT 0,
    likes INTEGER NOT NULL DEFAULT 0,
    comments INTEGER NOT NULL DEFAULT 0,
    reposts INTEGER NOT NULL DEFAULT 0,
    sample_count INTEGER NOT NULL DEFAULT 1,
    created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(post_id, bucket_at),
    FOREIGN KEY(post_id) REFERENCES posts(id) ON DELETE CASCADE
);
CREATE INDEX IF NOT EXISTS idx_post_stat_10m_post_bucket
    ON post_stat_10m(post_id, bucket_at DESC);
CREATE INDEX IF NOT EXISTS idx_post_stat_10m_bucket
    ON post_stat_10m(bucket_at DESC, post_id);

CREATE TABLE IF NOT EXISTS deletion_events (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    entity_type TEXT NOT NULL,
    entity_local_id INTEGER NOT NULL,
    entity_key TEXT NOT NULL DEFAULT '',
    reason TEXT NOT NULL DEFAULT '',
    created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
    synced_at TEXT,
    UNIQUE(entity_type, entity_local_id)
);
CREATE INDEX IF NOT EXISTS idx_deletion_events_pending
    ON deletion_events(entity_type, synced_at, id DESC);

PRAGMA user_version = 3;
