use sqlx::PgPool;
use uuid::Uuid;
use anyhow::Result;
use chrono::{DateTime, Utc};
use serde::Serialize;

/// User record from the database
#[derive(Debug, Clone, sqlx::FromRow, Serialize)]
pub struct UserRow {
    pub id: Uuid,
    pub email: String,
    pub username: String,
    #[serde(skip_serializing)]
    pub password_hash: String,
    #[serde(skip_serializing)]
    pub totp_secret: Option<String>,
    pub totp_enabled: bool,
    #[serde(skip_serializing)]
    pub backup_codes: Option<Vec<String>>,
    pub email_verified: bool,
    pub is_super_admin: bool,
    pub created_at: DateTime<Utc>,
    pub last_login_at: Option<DateTime<Utc>>,
}

/// Create a new user record. Returns the new user's UUID.
pub async fn create_user(
    pool: &PgPool,
    email: &str,
    username: &str,
    password_hash: &str,
) -> Result<Uuid> {
    let row: (Uuid,) = sqlx::query_as(
        "INSERT INTO users (email, username, password_hash) VALUES ($1, $2, $3) RETURNING id",
    )
    .bind(email)
    .bind(username)
    .bind(password_hash)
    .fetch_one(pool)
    .await?;

    Ok(row.0)
}

/// Fetch a user by their primary key.
pub async fn get_user_by_id(pool: &PgPool, user_id: Uuid) -> Result<Option<UserRow>> {
    let user = sqlx::query_as::<_, UserRow>(
        "SELECT id, email, username, password_hash, totp_secret, totp_enabled, \
         backup_codes, email_verified, is_super_admin, created_at, last_login_at \
         FROM users WHERE id = $1",
    )
    .bind(user_id)
    .fetch_optional(pool)
    .await?;

    Ok(user)
}

/// Fetch a user by email address (for login lookups).
pub async fn get_user_by_email(pool: &PgPool, email: &str) -> Result<Option<UserRow>> {
    let user = sqlx::query_as::<_, UserRow>(
        "SELECT id, email, username, password_hash, totp_secret, totp_enabled, \
         backup_codes, email_verified, is_super_admin, created_at, last_login_at \
         FROM users WHERE email = $1",
    )
    .bind(email)
    .fetch_optional(pool)
    .await?;

    Ok(user)
}

/// Update mutable user profile fields.
pub async fn update_user(
    pool: &PgPool,
    user_id: Uuid,
    display_name: Option<&str>,
    _avatar_url: Option<&str>,
) -> Result<()> {
    if let Some(name) = display_name {
        sqlx::query("UPDATE users SET username = $1 WHERE id = $2")
            .bind(name)
            .bind(user_id)
            .execute(pool)
            .await?;
    }
    Ok(())
}

/// Bump the last_login_at timestamp for a user.
pub async fn update_last_login(pool: &PgPool, user_id: Uuid) -> Result<()> {
    sqlx::query("UPDATE users SET last_login_at = NOW() WHERE id = $1")
        .bind(user_id)
        .execute(pool)
        .await?;
    Ok(())
}