use sea_orm::{ConnectionTrait, DatabaseConnection};
use crate::error::AppError;

/// Adds three tables supporting two features:
///
/// - `id_cache_snapshot` / `ratings_cache_snapshot`: persist the in-memory
///   id/ratings moka caches to Postgres on every fresh compute (cache miss),
///   so they can be reloaded on startup instead of starting cold after a
///   restart. Each row stores its own `expires_at` (epoch seconds) so a
///   reload can skip/purge entries whose original TTL has already lapsed.
///
/// - `cleanup_state`: tracks when the 7-day image_meta/available_ratings
///   cleanup job last ran. Single-row table (boolean PK) so an in-process
///   scheduler can check "is a run overdue" on startup/periodically and
///   catch up after any missed window.
pub async fn run(
    db: &DatabaseConnection,
    _cache_dir: &str,
    _external_cache_only: bool,
) -> Result<(), AppError> {
    db.execute_unprepared(
        "CREATE TABLE IF NOT EXISTS id_cache_snapshot (
            key         TEXT PRIMARY KEY,
            value       TEXT NOT NULL,
            expires_at  BIGINT NOT NULL
        )",
    )
    .await?;
    db.execute_unprepared(
        "CREATE TABLE IF NOT EXISTS ratings_cache_snapshot (
            key         TEXT PRIMARY KEY,
            value       TEXT NOT NULL,
            expires_at  BIGINT NOT NULL
        )",
    )
    .await?;
    db.execute_unprepared(
        "CREATE TABLE IF NOT EXISTS cleanup_state (
            id        BOOLEAN PRIMARY KEY DEFAULT true,
            last_run  BIGINT,
            CHECK (id)
        )",
    )
    .await?;
    tracing::info!("cache persistence + cleanup_state tables created");
    Ok(())
}