use serde::Deserialize;
use worker::{wasm_bindgen::JsValue, D1Database};
use crate::error::MigrationError;

pub const TABLE: &str = "_d1_migrations";

pub async fn ensure_table(db: &D1Database) -> Result<(), MigrationError> {
    let sql = format!(
        "CREATE TABLE IF NOT EXISTS {} (id TEXT PRIMARY KEY, name TEXT NOT NULL, applied_at TEXT NOT NULL)",
        TABLE
    );
    db.prepare(&sql).run().await.map_err(|e| MigrationError::Sql(e.to_string()))?;
    Ok(())
}

pub async fn is_applied(db: &D1Database, id: &str) -> Result<bool, MigrationError> {
    #[derive(Deserialize)]
    struct Row {}
    let sql = format!("SELECT id FROM {} WHERE id = ?1", TABLE);
    let val = JsValue::from_str(id);
    let row = db.prepare(&sql)
        .bind(&[val]).map_err(|e| MigrationError::Sql(e.to_string()))?
        .first::<Row>(None).await.map_err(|e| MigrationError::Sql(e.to_string()))?;
    Ok(row.is_some())
}

pub async fn record(db: &D1Database, id: &str, name: &str) -> Result<(), MigrationError> {
    let sql = format!(
        "INSERT INTO {} (id, name, applied_at) VALUES (?1, ?2, datetime('now'))",
        TABLE
    );
    let id_val = JsValue::from_str(id);
    let name_val = JsValue::from_str(name);
    db.prepare(&sql)
        .bind(&[id_val, name_val]).map_err(|e| MigrationError::Sql(e.to_string()))?
        .run().await.map_err(|e| MigrationError::Sql(e.to_string()))?;
    Ok(())
}

pub async fn remove(db: &D1Database, id: &str) -> Result<(), MigrationError> {
    let sql = format!("DELETE FROM {} WHERE id = ?1", TABLE);
    let val = JsValue::from_str(id);
    db.prepare(&sql)
        .bind(&[val]).map_err(|e| MigrationError::Sql(e.to_string()))?
        .run().await.map_err(|e| MigrationError::Sql(e.to_string()))?;
    Ok(())
}

pub async fn applied_entries(db: &D1Database) -> Result<Vec<(String, String)>, MigrationError> {
    #[derive(Deserialize)]
    struct Row { id: String, applied_at: String }
    let sql = format!("SELECT id, applied_at FROM {} ORDER BY applied_at ASC", TABLE);
    let result = db.prepare(&sql).all().await.map_err(|e| MigrationError::Sql(e.to_string()))?;
    let rows = result.results::<Row>().map_err(|e| MigrationError::Sql(e.to_string()))?;
    Ok(rows.into_iter().map(|r| (r.id, r.applied_at)).collect())
}
