use sqlx::{FromRow, PgPool};
use time::OffsetDateTime;

#[derive(FromRow)]
#[allow(dead_code)]
pub struct Bot {
    pub id: String,
    pub user_id: String,
    pub name: String,
    pub nick: String,
    pub network_addr: String,
    pub sasl_username: Option<String>,
    pub sasl_password: Option<String>,
    pub channels: Vec<String>,
    pub enabled: bool,
    pub status: String,
    pub status_message: Option<String>,
    pub created_at: OffsetDateTime,
    pub updated_at: OffsetDateTime,
}

#[derive(FromRow)]
#[allow(dead_code)]
pub struct BotScript {
    pub id: String,
    pub bot_id: String,
    pub name: String,
    pub source: String,
    pub enabled: bool,
    pub created_at: OffsetDateTime,
    pub updated_at: OffsetDateTime,
}

#[derive(FromRow)]
pub struct BotLog {
    pub id: i64,
    pub bot_id: String,
    pub level: String,
    pub message: String,
    pub created_at: OffsetDateTime,
}

#[derive(FromRow)]
pub struct BotKv {
    pub bot_id: String,
    pub key: String,
    pub value: String,
    pub updated_at: OffsetDateTime,
}

pub async fn get_bot(db: &PgPool, id: &str) -> Result<Option<Bot>, sqlx::Error> {
    sqlx::query_as("SELECT * FROM bots WHERE id = $1")
        .bind(id)
        .fetch_optional(db)
        .await
}

pub async fn get_bot_owned(db: &PgPool, id: &str, user_id: &str) -> Result<Option<Bot>, sqlx::Error> {
    sqlx::query_as("SELECT * FROM bots WHERE id = $1 AND user_id = $2")
        .bind(id)
        .bind(user_id)
        .fetch_optional(db)
        .await
}

pub async fn list_bots(db: &PgPool, user_id: &str) -> Result<Vec<Bot>, sqlx::Error> {
    sqlx::query_as("SELECT * FROM bots WHERE user_id = $1 ORDER BY created_at DESC")
        .bind(user_id)
        .fetch_all(db)
        .await
}

pub async fn count_bots(db: &PgPool, user_id: &str) -> Result<i64, sqlx::Error> {
    let (count,): (i64,) = sqlx::query_as("SELECT count(*) FROM bots WHERE user_id = $1")
        .bind(user_id)
        .fetch_one(db)
        .await?;
    Ok(count)
}

pub async fn insert_bot(
    db: &PgPool,
    id: &str,
    user_id: &str,
    name: &str,
    nick: &str,
    network_addr: &str,
    sasl_username: Option<&str>,
    sasl_password: Option<&str>,
    channels: &[String],
) -> Result<(), sqlx::Error> {
    sqlx::query(
        "INSERT INTO bots (id, user_id, name, nick, network_addr, sasl_username, sasl_password, channels)
         VALUES ($1, $2, $3, $4, $5, $6, $7, $8)",
    )
    .bind(id)
    .bind(user_id)
    .bind(name)
    .bind(nick)
    .bind(network_addr)
    .bind(sasl_username)
    .bind(sasl_password)
    .bind(channels)
    .execute(db)
    .await?;
    Ok(())
}

pub async fn update_bot(
    db: &PgPool,
    id: &str,
    user_id: &str,
    name: &str,
    nick: &str,
    network_addr: &str,
    sasl_username: Option<&str>,
    sasl_password: Option<&str>,
    channels: &[String],
) -> Result<bool, sqlx::Error> {
    let result = sqlx::query(
        "UPDATE bots SET name = $3, nick = $4, network_addr = $5, sasl_username = $6,
         sasl_password = $7, channels = $8, updated_at = now()
         WHERE id = $1 AND user_id = $2",
    )
    .bind(id)
    .bind(user_id)
    .bind(name)
    .bind(nick)
    .bind(network_addr)
    .bind(sasl_username)
    .bind(sasl_password)
    .bind(channels)
    .execute(db)
    .await?;
    Ok(result.rows_affected() > 0)
}

pub async fn delete_bot(db: &PgPool, id: &str, user_id: &str) -> Result<bool, sqlx::Error> {
    let result = sqlx::query("DELETE FROM bots WHERE id = $1 AND user_id = $2")
        .bind(id)
        .bind(user_id)
        .execute(db)
        .await?;
    Ok(result.rows_affected() > 0)
}

pub async fn set_bot_status(
    db: &PgPool,
    id: &str,
    status: &str,
    message: Option<&str>,
) -> Result<(), sqlx::Error> {
    sqlx::query(
        "UPDATE bots SET status = $2, status_message = $3, updated_at = now() WHERE id = $1",
    )
    .bind(id)
    .bind(status)
    .bind(message)
    .execute(db)
    .await?;
    Ok(())
}

pub async fn list_enabled_bots(db: &PgPool) -> Result<Vec<Bot>, sqlx::Error> {
    sqlx::query_as("SELECT * FROM bots WHERE enabled = true")
        .fetch_all(db)
        .await
}

pub async fn list_scripts(db: &PgPool, bot_id: &str) -> Result<Vec<BotScript>, sqlx::Error> {
    sqlx::query_as("SELECT * FROM bot_scripts WHERE bot_id = $1 ORDER BY name")
        .bind(bot_id)
        .fetch_all(db)
        .await
}

pub async fn list_enabled_scripts(db: &PgPool, bot_id: &str) -> Result<Vec<BotScript>, sqlx::Error> {
    sqlx::query_as("SELECT * FROM bot_scripts WHERE bot_id = $1 AND enabled = true ORDER BY name")
        .bind(bot_id)
        .fetch_all(db)
        .await
}

pub async fn get_script(db: &PgPool, id: &str) -> Result<Option<BotScript>, sqlx::Error> {
    sqlx::query_as("SELECT * FROM bot_scripts WHERE id = $1")
        .bind(id)
        .fetch_optional(db)
        .await
}

pub async fn insert_script(
    db: &PgPool,
    id: &str,
    bot_id: &str,
    name: &str,
    source: &str,
) -> Result<(), sqlx::Error> {
    sqlx::query(
        "INSERT INTO bot_scripts (id, bot_id, name, source) VALUES ($1, $2, $3, $4)",
    )
    .bind(id)
    .bind(bot_id)
    .bind(name)
    .bind(source)
    .execute(db)
    .await?;
    Ok(())
}

pub async fn update_script(
    db: &PgPool,
    id: &str,
    name: &str,
    source: &str,
    enabled: bool,
) -> Result<bool, sqlx::Error> {
    let result = sqlx::query(
        "UPDATE bot_scripts SET name = $2, source = $3, enabled = $4, updated_at = now() WHERE id = $1",
    )
    .bind(id)
    .bind(name)
    .bind(source)
    .bind(enabled)
    .execute(db)
    .await?;
    Ok(result.rows_affected() > 0)
}

pub async fn delete_script(db: &PgPool, id: &str) -> Result<bool, sqlx::Error> {
    let result = sqlx::query("DELETE FROM bot_scripts WHERE id = $1")
        .bind(id)
        .execute(db)
        .await?;
    Ok(result.rows_affected() > 0)
}

pub async fn insert_log(
    db: &PgPool,
    bot_id: &str,
    level: &str,
    message: &str,
) -> Result<(), sqlx::Error> {
    sqlx::query("INSERT INTO bot_logs (bot_id, level, message) VALUES ($1, $2, $3)")
        .bind(bot_id)
        .bind(level)
        .bind(message)
        .execute(db)
        .await?;
    Ok(())
}

pub async fn list_logs(
    db: &PgPool,
    bot_id: &str,
    limit: i64,
) -> Result<Vec<BotLog>, sqlx::Error> {
    sqlx::query_as(
        "SELECT * FROM bot_logs WHERE bot_id = $1 ORDER BY created_at DESC LIMIT $2",
    )
    .bind(bot_id)
    .bind(limit)
    .fetch_all(db)
    .await
}

pub async fn cleanup_old_logs(db: &PgPool, days: i64) -> Result<u64, sqlx::Error> {
    let result = sqlx::query(
        "DELETE FROM bot_logs WHERE created_at < now() - make_interval(days => $1)",
    )
    .bind(days)
    .execute(db)
    .await?;
    Ok(result.rows_affected())
}

pub async fn kv_get(db: &PgPool, bot_id: &str, key: &str) -> Result<Option<BotKv>, sqlx::Error> {
    sqlx::query_as("SELECT * FROM bot_kv WHERE bot_id = $1 AND key = $2")
        .bind(bot_id)
        .bind(key)
        .fetch_optional(db)
        .await
}

pub async fn kv_set(
    db: &PgPool,
    bot_id: &str,
    key: &str,
    value: &str,
) -> Result<(), sqlx::Error> {
    sqlx::query(
        "INSERT INTO bot_kv (bot_id, key, value) VALUES ($1, $2, $3)
         ON CONFLICT (bot_id, key) DO UPDATE SET value = $3, updated_at = now()",
    )
    .bind(bot_id)
    .bind(key)
    .bind(value)
    .execute(db)
    .await?;
    Ok(())
}

pub async fn kv_delete(db: &PgPool, bot_id: &str, key: &str) -> Result<bool, sqlx::Error> {
    let result = sqlx::query("DELETE FROM bot_kv WHERE bot_id = $1 AND key = $2")
        .bind(bot_id)
        .bind(key)
        .execute(db)
        .await?;
    Ok(result.rows_affected() > 0)
}

pub async fn kv_list(db: &PgPool, bot_id: &str) -> Result<Vec<BotKv>, sqlx::Error> {
    sqlx::query_as("SELECT * FROM bot_kv WHERE bot_id = $1 ORDER BY key")
        .bind(bot_id)
        .fetch_all(db)
        .await
}