use rusqlite::{params, Connection};

pub fn init_db(conn: &Connection) {
    conn.execute(
        "CREATE TABLE IF NOT EXISTS users (
            username TEXT PRIMARY KEY,
            hashed_password TEXT NOT NULL,
            account_id INTEGER UNIQUE,
            balance INTEGER DEFAULT 1000
        )",
        [],
    )
    .unwrap();
    conn.execute(
        "CREATE TABLE IF NOT EXISTS plays (
            session_id TEXT PRIMARY KEY,
            username TEXT NOT NULL,
            game_type TEXT NOT NULL,
            bet_amount INTEGER NOT NULL,
            winnings INTEGER NOT NULL,
            balance_after INTEGER NOT NULL,
            details TEXT NOT NULL DEFAULT '{}',
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP
        )",
        [],
    )
    .unwrap();
    // A multi-step game's reserved bet is written here in the same transaction
    // as the debit, so a crash between reserve and settle is recoverable.
    conn.execute(
        "CREATE TABLE IF NOT EXISTS pending_plays (
            session_id TEXT PRIMARY KEY,
            username TEXT NOT NULL,
            game_type TEXT NOT NULL,
            bet_amount INTEGER NOT NULL,
            details TEXT NOT NULL DEFAULT '{}',
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP
        )",
        [],
    )
    .unwrap();
}

pub fn add_user(
    conn: &Connection,
    username: &str,
    hashed_password: &str,
) -> Result<(), Box<dyn std::error::Error>> {
    if get_hashed_password(conn, username).is_some() {
        return Err("user already exists".into());
    }

    for _ in 0..10 {
        let account_id: u32 = rand::random::<u32>();
        match conn.execute(
            "INSERT INTO users (username, hashed_password, account_id, balance)
             VALUES (?1, ?2, ?3, ?4)",
            params![username, hashed_password, account_id as i64, 1000i64],
        ) {
            Ok(_) => return Ok(()),
            Err(rusqlite::Error::SqliteFailure(error, _))
                if error.code == rusqlite::ErrorCode::ConstraintViolation =>
            {
                continue;
            }
            Err(error) => return Err(Box::new(error)),
        }
    }

    Err("could not allocate a unique account id".into())
}

pub fn get_hashed_password(conn: &Connection, username: &str) -> Option<String> {
    let mut stmt = conn
        .prepare("SELECT hashed_password FROM users WHERE username = ?1")
        .unwrap();
    let mut rows = stmt.query([username]).unwrap();
    if let Ok(Some(row)) = rows.next() {
        row.get(0).ok()
    } else {
        None
    }
}

pub fn set_hashed_password(
    conn: &Connection,
    username: &str,
    hashed_password: &str,
) -> rusqlite::Result<()> {
    conn.execute(
        "UPDATE users SET hashed_password = ?1 WHERE username = ?2",
        params![hashed_password, username],
    )?;
    Ok(())
}

pub fn get_account_id(conn: &Connection, username: &str) -> Option<i64> {
    let mut stmt = conn
        .prepare("SELECT account_id FROM users WHERE username = ?1")
        .unwrap();
    let mut rows = stmt.query([username]).unwrap();
    if let Ok(Some(row)) = rows.next() {
        Some(row.get::<_, i64>(0).ok()?)
    } else {
        None
    }
}

pub fn get_balance(conn: &Connection, username: &str) -> Option<i64> {
    let mut stmt = conn
        .prepare("SELECT balance FROM users WHERE username = ?1")
        .unwrap();
    let mut rows = stmt.query([username]).unwrap();
    if let Ok(Some(row)) = rows.next() {
        Some(row.get::<_, i64>(0).ok()?)
    } else {
        None
    }
}

/// Settle one play and record its audit row in a single transaction.
///
/// `details` is a JSON object with game-specific fields (reels, cards,
/// winning number, crash point, ...). All SQL is parameterized: usernames are
/// data, never part of the statement text.
///
/// Returns the balance after the play.
#[allow(clippy::too_many_arguments)]
pub fn apply_play(
    conn: &mut Connection,
    session_id: &str,
    username: &str,
    game_type: &str,
    bet_amount: i64,
    winnings: i64,
    details: &str,
) -> rusqlite::Result<i64> {
    let tx = conn.transaction()?;

    let deducted = tx.execute(
        "UPDATE users SET balance = balance - ?1
         WHERE username = ?2 AND balance >= ?1",
        params![bet_amount, username],
    )?;
    if deducted != 1 {
        return Err(rusqlite::Error::QueryReturnedNoRows);
    }

    tx.execute(
        "UPDATE users SET balance = balance + ?1 WHERE username = ?2",
        params![winnings, username],
    )?;

    let balance_after: i64 = tx.query_row(
        "SELECT balance FROM users WHERE username = ?1",
        [username],
        |row| row.get(0),
    )?;

    tx.execute(
        "INSERT INTO plays
            (session_id, username, game_type, bet_amount, winnings,
             balance_after, details)
         VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7)",
        params![
            session_id,
            username,
            game_type,
            bet_amount,
            winnings,
            balance_after,
            details
        ],
    )?;

    tx.commit()?;
    Ok(balance_after)
}

pub fn count_plays(conn: &Connection, username: &str) -> i64 {
    conn.query_row(
        "SELECT COUNT(*) FROM plays WHERE username = ?1",
        [username],
        |row| row.get(0),
    )
    .unwrap_or(0)
}

/// Reserve a bet before a multi-step hand starts.
///
/// The debit and the `pending_plays` row are one transaction. If the server
/// dies before the hand settles, the row is what lets the next startup record
/// the lost bet instead of leaving an orphaned deduction.
#[allow(clippy::too_many_arguments)]
pub fn reserve_play(
    conn: &mut Connection,
    session_id: &str,
    username: &str,
    game_type: &str,
    bet_amount: i64,
    details: &str,
) -> rusqlite::Result<i64> {
    let tx = conn.transaction()?;
    let updated = tx.execute(
        "UPDATE users SET balance = balance - ?1
         WHERE username = ?2 AND balance >= ?1",
        params![bet_amount, username],
    )?;
    if updated != 1 {
        return Err(rusqlite::Error::QueryReturnedNoRows);
    }
    tx.execute(
        "INSERT INTO pending_plays (session_id, username, game_type, bet_amount, details)
         VALUES (?1, ?2, ?3, ?4, ?5)",
        params![session_id, username, game_type, bet_amount, details],
    )?;
    let balance = tx.query_row(
        "SELECT balance FROM users WHERE username = ?1",
        [username],
        |row| row.get(0),
    )?;
    tx.commit()?;
    Ok(balance)
}

/// Add to an existing reservation (blackjack double), keeping the pending row
/// and the debit in step.
pub fn extend_reservation(
    conn: &mut Connection,
    session_id: &str,
    amount: i64,
) -> rusqlite::Result<i64> {
    let tx = conn.transaction()?;
    let updated = tx.execute(
        "UPDATE users SET balance = balance - ?1
         WHERE balance >= ?1
           AND username = (SELECT username FROM pending_plays WHERE session_id = ?2)",
        params![amount, session_id],
    )?;
    if updated != 1 {
        return Err(rusqlite::Error::QueryReturnedNoRows);
    }
    tx.execute(
        "UPDATE pending_plays SET bet_amount = bet_amount + ?1 WHERE session_id = ?2",
        params![amount, session_id],
    )?;
    let balance = tx.query_row(
        "SELECT balance FROM users
         WHERE username = (SELECT username FROM pending_plays WHERE session_id = ?1)",
        [session_id],
        |row| row.get(0),
    )?;
    tx.commit()?;
    Ok(balance)
}

/// Undo part of a reservation when the action that needed it fails.
pub fn refund_reservation(
    conn: &mut Connection,
    session_id: &str,
    amount: i64,
) -> rusqlite::Result<i64> {
    let tx = conn.transaction()?;
    tx.execute(
        "UPDATE users SET balance = balance + ?1
         WHERE username = (SELECT username FROM pending_plays WHERE session_id = ?2)",
        params![amount, session_id],
    )?;
    tx.execute(
        "UPDATE pending_plays SET bet_amount = MAX(bet_amount - ?1, 0) WHERE session_id = ?2",
        params![amount, session_id],
    )?;
    let balance = tx.query_row(
        "SELECT balance FROM users
         WHERE username = (SELECT username FROM pending_plays WHERE session_id = ?1)",
        [session_id],
        |row| row.get(0),
    )?;
    tx.commit()?;
    Ok(balance)
}

/// Settle a hand whose bet was already reserved: credit winnings, write the
/// audit row, clear the reservation, and return the new balance.
#[allow(clippy::too_many_arguments)]
pub fn settle_play(
    conn: &mut Connection,
    session_id: &str,
    username: &str,
    game_type: &str,
    bet_amount: i64,
    winnings: i64,
    details: &str,
) -> rusqlite::Result<i64> {
    let tx = conn.transaction()?;

    tx.execute(
        "UPDATE users SET balance = balance + ?1 WHERE username = ?2",
        params![winnings, username],
    )?;

    let balance_after: i64 = tx.query_row(
        "SELECT balance FROM users WHERE username = ?1",
        [username],
        |row| row.get(0),
    )?;

    tx.execute(
        "INSERT INTO plays
            (session_id, username, game_type, bet_amount, winnings,
             balance_after, details)
         VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7)",
        params![
            session_id,
            username,
            game_type,
            bet_amount,
            winnings,
            balance_after,
            details
        ],
    )?;

    tx.execute(
        "DELETE FROM pending_plays WHERE session_id = ?1",
        [session_id],
    )?;

    tx.commit()?;
    Ok(balance_after)
}

/// Settle reservations left behind by a server restart.
///
/// A round cannot outlive the process that held its in-memory state, so the
/// reserved bet is forfeited and recorded, never refunded. Call this once at
/// startup, before serving players: sweeping while rounds are live would settle
/// someone else's hand. Returns how many orphaned plays were settled.
pub fn abandon_pending(conn: &mut Connection) -> rusqlite::Result<usize> {
    let tx = conn.transaction()?;
    let pending: Vec<(String, String, String, i64, String)> = {
        let mut statement = tx.prepare(
            "SELECT session_id, username, game_type, bet_amount, details
             FROM pending_plays",
        )?;
        let rows = statement.query_map([], |row| {
            Ok((
                row.get(0)?,
                row.get(1)?,
                row.get(2)?,
                row.get(3)?,
                row.get(4)?,
            ))
        })?;
        rows.collect::<rusqlite::Result<_>>()?
    };

    for (session_id, username, game_type, bet_amount, details) in &pending {
        let balance_after: i64 = tx
            .query_row(
                "SELECT balance FROM users WHERE username = ?1",
                [username],
                |row| row.get(0),
            )
            .unwrap_or(0);
        let details = abandoned_details(details);
        tx.execute(
            "INSERT OR IGNORE INTO plays
                (session_id, username, game_type, bet_amount, winnings,
                 balance_after, details)
             VALUES (?1, ?2, ?3, ?4, 0, ?5, ?6)",
            params![
                session_id,
                username,
                game_type,
                bet_amount,
                balance_after,
                details
            ],
        )?;
        tx.execute(
            "DELETE FROM pending_plays WHERE session_id = ?1",
            [session_id],
        )?;
    }

    tx.commit()?;
    Ok(pending.len())
}

/// Mark a game's own detail blob as abandoned without dropping its payload.
fn abandoned_details(raw: &str) -> String {
    let mut value: serde_json::Value = serde_json::from_str(raw)
        .unwrap_or_else(|_| serde_json::Value::Object(serde_json::Map::new()));
    if let Some(object) = value.as_object_mut() {
        object.insert("result".to_string(), serde_json::json!("abandoned"));
        object.insert("phase".to_string(), serde_json::json!("abandoned"));
        object.insert("reason".to_string(), serde_json::json!("server_restart"));
    }
    value.to_string()
}

/// Most recent settled plays for a user, newest first.
pub fn recent_plays(
    conn: &Connection,
    username: &str,
    limit: u32,
) -> rusqlite::Result<Vec<crate::history::PlayRecord>> {
    let mut statement = conn.prepare(
        "SELECT game_type, bet_amount, winnings, balance_after, details, created_at
         FROM plays
         WHERE username = ?1
         ORDER BY created_at DESC, rowid DESC
         LIMIT ?2",
    )?;
    let rows = statement.query_map(params![username, limit], |row| {
        let details: String = row.get(4)?;
        Ok(crate::history::PlayRecord {
            game: row.get(0)?,
            bet: row.get(1)?,
            winnings: row.get(2)?,
            balance_after: row.get(3)?,
            at: row.get(5)?,
            details: serde_json::from_str(&details).unwrap_or(serde_json::Value::Null),
        })
    })?;
    rows.collect()
}