use sqlx::PgPool;

use crate::db::models::*;
use crate::db::{ItemId, ProjectId, SyncAppId, UserId};
use crate::error::Result;

// ── Sync Apps ──

/// Compute the SHA-256 hash of an API key (hex-encoded).
pub fn hash_api_key(api_key: &str) -> String {
    use sha2::Digest;
    let hash = sha2::Sha256::digest(api_key.as_bytes());
    hex::encode(hash)
}

/// Create a new sync app. Stores the hashed API key and prefix.
#[tracing::instrument(skip_all)]
pub async fn create_sync_app(
    pool: &PgPool,
    creator_id: UserId,
    name: &str,
    api_key: &str,
    project_id: Option<ProjectId>,
    item_id: Option<ItemId>,
) -> Result<DbSyncApp> {
    let key_hash = hash_api_key(api_key);
    let key_prefix = &api_key[..8.min(api_key.len())];
    let app = sqlx::query_as::<_, DbSyncApp>(
        r#"
        INSERT INTO sync_apps (creator_id, name, api_key_hash, api_key_prefix, project_id, item_id)
        VALUES ($1, $2, $3, $4, $5, $6)
        RETURNING *
        "#,
    )
    .bind(creator_id)
    .bind(name)
    .bind(&key_hash)
    .bind(key_prefix)
    .bind(project_id)
    .bind(item_id)
    .fetch_one(pool)
    .await?;

    Ok(app)
}

/// Update the project/item link for a sync app.
#[tracing::instrument(skip_all)]
pub async fn update_sync_app_link(
    pool: &PgPool,
    app_id: SyncAppId,
    project_id: Option<ProjectId>,
    item_id: Option<ItemId>,
) -> Result<DbSyncApp> {
    let app = sqlx::query_as::<_, DbSyncApp>(
        r#"
        UPDATE sync_apps SET project_id = $2, item_id = $3
        WHERE id = $1
        RETURNING *
        "#,
    )
    .bind(app_id)
    .bind(project_id)
    .bind(item_id)
    .fetch_one(pool)
    .await?;

    Ok(app)
}

/// Get a sync app by API key (only if active). Hashes the input before lookup.
#[tracing::instrument(skip_all)]
pub async fn get_sync_app_by_api_key(
    pool: &PgPool,
    api_key: &str,
) -> Result<Option<DbSyncApp>> {
    let key_hash = hash_api_key(api_key);
    let app = sqlx::query_as::<_, DbSyncApp>(
        "SELECT * FROM sync_apps WHERE api_key_hash = $1 AND is_active = true",
    )
    .bind(&key_hash)
    .fetch_optional(pool)
    .await?;

    Ok(app)
}

/// Get a sync app by ID.
#[tracing::instrument(skip_all)]
pub async fn get_sync_app_by_id(pool: &PgPool, id: SyncAppId) -> Result<Option<DbSyncApp>> {
    let app = sqlx::query_as::<_, DbSyncApp>(
        "SELECT * FROM sync_apps WHERE id = $1",
    )
    .bind(id)
    .fetch_optional(pool)
    .await?;

    Ok(app)
}

/// List all sync apps for a creator.
#[tracing::instrument(skip_all)]
pub async fn get_sync_apps_by_creator(
    pool: &PgPool,
    creator_id: UserId,
) -> Result<Vec<DbSyncApp>> {
    let apps = sqlx::query_as::<_, DbSyncApp>(
        "SELECT * FROM sync_apps WHERE creator_id = $1 ORDER BY created_at DESC LIMIT 100",
    )
    .bind(creator_id)
    .fetch_all(pool)
    .await?;

    Ok(apps)
}

/// Get all sync apps linked to a specific project.
pub async fn get_sync_apps_by_project(
    pool: &PgPool,
    project_id: ProjectId,
) -> Result<Vec<DbSyncApp>> {
    let apps = sqlx::query_as::<_, DbSyncApp>(
        "SELECT * FROM sync_apps WHERE project_id = $1 ORDER BY created_at DESC LIMIT 100",
    )
    .bind(project_id)
    .fetch_all(pool)
    .await?;

    Ok(apps)
}

/// Regenerate an API key for a sync app. Stores the hashed key and prefix.
#[tracing::instrument(skip_all)]
pub async fn regenerate_sync_app_key(
    pool: &PgPool,
    app_id: SyncAppId,
    new_api_key: &str,
) -> Result<DbSyncApp> {
    let key_hash = hash_api_key(new_api_key);
    let key_prefix = &new_api_key[..8.min(new_api_key.len())];
    let app = sqlx::query_as::<_, DbSyncApp>(
        r#"
        UPDATE sync_apps SET api_key_hash = $2, api_key_prefix = $3
        WHERE id = $1
        RETURNING *
        "#,
    )
    .bind(app_id)
    .bind(&key_hash)
    .bind(key_prefix)
    .fetch_one(pool)
    .await?;

    Ok(app)
}

/// Delete a sync app (cascades to devices and log entries).
#[tracing::instrument(skip_all)]
pub async fn delete_sync_app(pool: &PgPool, app_id: SyncAppId) -> Result<()> {
    sqlx::query("DELETE FROM sync_apps WHERE id = $1")
        .bind(app_id)
        .execute(pool)
        .await?;

    Ok(())
}