//! License key management: CRUD, activation tracking, and revocation.

use sqlx::PgPool;

use super::models::{DbLicenseActivation, DbLicenseKey};
use super::validated_types::KeyCode;
use super::{ItemId, LicenseActivationId, LicenseKeyId, TransactionId, UserId};
use crate::error::Result;

/// Create a new license key for an item.
///
/// Retries once on a 23505 unique-violation with a freshly-generated code.
/// A real collision out of the wordlist generator is vanishingly rare (the
/// six-word space gives ~6B coin-flip headroom), but the alternative is
/// surfacing a 500 to whatever flow is creating the key.
#[tracing::instrument(skip_all)]
pub async fn create_license_key(
    pool: &PgPool,
    item_id: ItemId,
    owner_id: UserId,
    transaction_id: Option<TransactionId>,
    key_code: &KeyCode,
    max_activations: Option<i32>,
) -> Result<DbLicenseKey> {
    let first = sqlx::query_as!(
        DbLicenseKey,
        r#"
        INSERT INTO license_keys (item_id, owner_id, transaction_id, key_code, max_activations)
        VALUES ($1, $2, $3, $4, $5)
        RETURNING id AS "id: LicenseKeyId", item_id AS "item_id: ItemId",
                  owner_id AS "owner_id: UserId", transaction_id AS "transaction_id: TransactionId",
                  key_code AS "key_code: KeyCode", max_activations, activation_count,
                  revoked_at AS "revoked_at: chrono::DateTime<chrono::Utc>",
                  created_at AS "created_at: chrono::DateTime<chrono::Utc>"
        "#,
        item_id as ItemId,
        owner_id as UserId,
        transaction_id as Option<TransactionId>,
        key_code as &KeyCode,
        max_activations,
    )
    .fetch_one(pool)
    .await;

    match first {
        Ok(key) => Ok(key),
        Err(sqlx::Error::Database(e)) if e.code().as_deref() == Some("23505") => {
            // Distinguish which unique index fired. A `transaction_id` collision
            // (mig 151's partial unique index) means this purchase already minted
            // its one key, a duplicate finalize from crash-recovery redelivery.
            // Return the existing key as idempotent success; retrying with a fresh
            // code would only collide on the same index again (Pay-M1). A `key_code`
            // collision is a random clash, regenerate and retry once.
            let constraint = e.constraint().map(str::to_string);
            if let (Some("license_keys_transaction_id_key"), Some(tx_id)) =
                (constraint.as_deref(), transaction_id)
            {
                return get_license_key_by_transaction_id(pool, tx_id)
                    .await?
                    .ok_or_else(|| {
                        crate::error::AppError::Internal(anyhow::anyhow!(
                            "license_keys transaction_id unique violation but no existing \
                             row found for {tx_id:?}"
                        ))
                    });
            }
            let retry_code = crate::helpers::generate_key_code();
            tracing::warn!(item_id = %item_id, "license key key_code 23505 collision; retrying once");
            let key = sqlx::query_as!(
                DbLicenseKey,
                r#"
                INSERT INTO license_keys (item_id, owner_id, transaction_id, key_code, max_activations)
                VALUES ($1, $2, $3, $4, $5)
                RETURNING id AS "id: LicenseKeyId", item_id AS "item_id: ItemId",
                          owner_id AS "owner_id: UserId", transaction_id AS "transaction_id: TransactionId",
                          key_code AS "key_code: KeyCode", max_activations, activation_count,
                          revoked_at AS "revoked_at: chrono::DateTime<chrono::Utc>",
                          created_at AS "created_at: chrono::DateTime<chrono::Utc>"
                "#,
                item_id as ItemId,
                owner_id as UserId,
                transaction_id as Option<TransactionId>,
                &retry_code as &KeyCode,
                max_activations,
            )
            .fetch_one(pool)
            .await?;
            Ok(key)
        }
        Err(e) => Err(e.into()),
    }
}

/// Look up a license key by its code.
#[tracing::instrument(skip_all)]
pub async fn get_license_key_by_code(
    pool: &PgPool,
    key_code: &KeyCode,
) -> Result<Option<DbLicenseKey>> {
    let key = sqlx::query_as!(
        DbLicenseKey,
        r#"
        SELECT id AS "id: LicenseKeyId", item_id AS "item_id: ItemId",
               owner_id AS "owner_id: UserId", transaction_id AS "transaction_id: TransactionId",
               key_code AS "key_code: KeyCode", max_activations, activation_count,
               revoked_at AS "revoked_at: chrono::DateTime<chrono::Utc>",
               created_at AS "created_at: chrono::DateTime<chrono::Utc>"
        FROM license_keys WHERE key_code = $1
        "#,
        key_code as &KeyCode,
    )
    .fetch_optional(pool)
    .await?;

    Ok(key)
}

/// Look up the auto-minted license key for a purchase transaction, if any.
///
/// Used by the finalize pre-check so a crash-recovery redelivery does not mint
/// a second key (the `license_keys_transaction_id_key` partial unique index is
/// the structural backstop). At most one such key exists per transaction.
#[tracing::instrument(skip_all)]
pub async fn get_license_key_by_transaction_id(
    pool: &PgPool,
    transaction_id: TransactionId,
) -> Result<Option<DbLicenseKey>> {
    let key = sqlx::query_as!(
        DbLicenseKey,
        r#"
        SELECT id AS "id: LicenseKeyId", item_id AS "item_id: ItemId",
               owner_id AS "owner_id: UserId", transaction_id AS "transaction_id: TransactionId",
               key_code AS "key_code: KeyCode", max_activations, activation_count,
               revoked_at AS "revoked_at: chrono::DateTime<chrono::Utc>",
               created_at AS "created_at: chrono::DateTime<chrono::Utc>"
        FROM license_keys WHERE transaction_id = $1
        "#,
        transaction_id as TransactionId,
    )
    .fetch_optional(pool)
    .await?;

    Ok(key)
}

/// Get a license key by ID. UNSCOPED: returns any user's key, so the caller MUST
/// authorize against the returned `owner_id` / `item_id` before acting on it
/// (the `_unchecked` suffix makes that contract legible at the call site, Sec-M2).
#[tracing::instrument(skip_all)]
pub async fn get_license_key_by_id_unchecked(
    pool: &PgPool,
    id: LicenseKeyId,
) -> Result<Option<DbLicenseKey>> {
    let key = sqlx::query_as!(
        DbLicenseKey,
        r#"
        SELECT id AS "id: LicenseKeyId", item_id AS "item_id: ItemId",
               owner_id AS "owner_id: UserId", transaction_id AS "transaction_id: TransactionId",
               key_code AS "key_code: KeyCode", max_activations, activation_count,
               revoked_at AS "revoked_at: chrono::DateTime<chrono::Utc>",
               created_at AS "created_at: chrono::DateTime<chrono::Utc>"
        FROM license_keys WHERE id = $1
        "#,
        id as LicenseKeyId,
    )
    .fetch_optional(pool)
    .await?;

    Ok(key)
}

/// Count license keys for an item.
#[tracing::instrument(skip_all)]
pub async fn count_keys_by_item(pool: &PgPool, item_id: ItemId) -> Result<i64> {
    let count = sqlx::query_scalar!(
        r#"SELECT COUNT(*) AS "count!" FROM license_keys WHERE item_id = $1"#,
        item_id as ItemId,
    )
    .fetch_one(pool)
    .await?;

    Ok(count)
}

/// List all license keys for an item, newest first.
///
/// Hard-caps at 500 rows to bound memory and response size for the creator
/// dashboard list view. Items with more than 500 keys are uncommon;
/// future work could add cursor-based pagination if needed.
#[tracing::instrument(skip_all)]
pub async fn get_license_keys_by_item(pool: &PgPool, item_id: ItemId) -> Result<Vec<DbLicenseKey>> {
    let keys = sqlx::query_as!(
        DbLicenseKey,
        r#"
        SELECT id AS "id: LicenseKeyId", item_id AS "item_id: ItemId",
               owner_id AS "owner_id: UserId", transaction_id AS "transaction_id: TransactionId",
               key_code AS "key_code: KeyCode", max_activations, activation_count,
               revoked_at AS "revoked_at: chrono::DateTime<chrono::Utc>",
               created_at AS "created_at: chrono::DateTime<chrono::Utc>"
        FROM license_keys WHERE item_id = $1 ORDER BY created_at DESC LIMIT 500
        "#,
        item_id as ItemId,
    )
    .fetch_all(pool)
    .await?;

    Ok(keys)
}

/// Batch-load license keys for multiple items, grouped by item_id.
#[tracing::instrument(skip_all)]
pub async fn get_license_keys_by_items(
    pool: &PgPool,
    item_ids: &[ItemId],
) -> Result<std::collections::HashMap<ItemId, Vec<DbLicenseKey>>> {
    let keys = sqlx::query_as!(
        DbLicenseKey,
        r#"
        SELECT id AS "id: LicenseKeyId", item_id AS "item_id: ItemId",
               owner_id AS "owner_id: UserId", transaction_id AS "transaction_id: TransactionId",
               key_code AS "key_code: KeyCode", max_activations, activation_count,
               revoked_at AS "revoked_at: chrono::DateTime<chrono::Utc>",
               created_at AS "created_at: chrono::DateTime<chrono::Utc>"
        FROM license_keys WHERE item_id = ANY($1) ORDER BY item_id, created_at DESC
        "#,
        item_ids as &[ItemId],
    )
    .fetch_all(pool)
    .await?;

    let mut map: std::collections::HashMap<ItemId, Vec<DbLicenseKey>> =
        std::collections::HashMap::new();
    for k in keys {
        map.entry(k.item_id).or_default().push(k);
    }
    Ok(map)
}

/// Find an existing activation for a key + machine combo.
#[tracing::instrument(skip_all)]
pub async fn get_activation(
    pool: &PgPool,
    license_key_id: LicenseKeyId,
    machine_id: &str,
) -> Result<Option<DbLicenseActivation>> {
    let activation = sqlx::query_as!(
        DbLicenseActivation,
        r#"
        SELECT id AS "id: LicenseActivationId", license_key_id AS "license_key_id: LicenseKeyId",
               machine_id, label,
               activated_at AS "activated_at: chrono::DateTime<chrono::Utc>",
               last_validated_at AS "last_validated_at: chrono::DateTime<chrono::Utc>", is_active
        FROM license_activations WHERE license_key_id = $1 AND machine_id = $2
        "#,
        license_key_id as LicenseKeyId,
        machine_id,
    )
    .fetch_optional(pool)
    .await?;

    Ok(activation)
}

/// Update the last_validated_at timestamp for an existing activation.
#[tracing::instrument(skip_all)]
pub async fn touch_activation(pool: &PgPool, activation_id: LicenseActivationId) -> Result<()> {
    sqlx::query!(
        "UPDATE license_activations SET last_validated_at = NOW() WHERE id = $1",
        activation_id as LicenseActivationId,
    )
    .execute(pool)
    .await?;

    Ok(())
}

/// Read the denormalized active-activation count for a key.
///
/// `try_create_activation` keeps `license_keys.activation_count` authoritative
/// (it recomputes it under the row lock), so this is the value to report back to
/// the client rather than a pre-lock read or a manual `+ 1` guess that drifts
/// under concurrent activations (ultra-fuzz Run #1 Payments MINOR).
#[tracing::instrument(skip_all)]
pub async fn get_activation_count(pool: &PgPool, license_key_id: LicenseKeyId) -> Result<i32> {
    let count = sqlx::query_scalar!(
        "SELECT activation_count FROM license_keys WHERE id = $1",
        license_key_id as LicenseKeyId,
    )
    .fetch_one(pool)
    .await?;
    Ok(count)
}

/// Activate a license key on a machine, atomically enforcing max_activations.
///
/// Uses a transaction with `FOR UPDATE` to serialize concurrent activations
/// for the same key. Re-activations (same machine_id) always succeed via
/// upsert. New activations are rejected if the active count would exceed
/// `max_activations`.
///
/// Returns `None` if the activation limit has been reached.
///
/// After the upsert, the denormalized `activation_count` on `license_keys`
/// is refreshed with a full COUNT rather than an increment; this avoids
/// drift if a crash leaves the count out of sync.
#[tracing::instrument(skip_all)]
pub async fn try_create_activation(
    pool: &PgPool,
    license_key_id: LicenseKeyId,
    machine_id: &str,
    label: Option<&str>,
) -> Result<Option<DbLicenseActivation>> {
    let mut tx = pool.begin().await?;

    // Lock the license key row to serialize concurrent activations, re-check
    // revocation, AND read `max_activations` from the locked row, not from a
    // caller-supplied argument (Pay-M2). The caller's value was read before the
    // lock; if an admin lowered the cap in between, enforcing the stale arg would
    // let an extra machine activate. Reading the column here makes the limit the
    // authoritative one. No eligible (non-revoked) row => no activation.
    let locked: Option<Option<i32>> = sqlx::query_scalar!(
        r#"SELECT max_activations FROM license_keys WHERE id = $1 AND revoked_at IS NULL FOR UPDATE"#,
        license_key_id as LicenseKeyId,
    )
    .fetch_optional(&mut *tx)
    .await?;
    let Some(max_activations) = locked else {
        tx.rollback().await?;
        return Ok(None);
    };

    // Check if this machine already has an activation (re-activation is always OK)
    let existing: Option<DbLicenseActivation> = sqlx::query_as!(
        DbLicenseActivation,
        r#"
        SELECT id AS "id: LicenseActivationId", license_key_id AS "license_key_id: LicenseKeyId",
               machine_id, label,
               activated_at AS "activated_at: chrono::DateTime<chrono::Utc>",
               last_validated_at AS "last_validated_at: chrono::DateTime<chrono::Utc>", is_active
        FROM license_activations WHERE license_key_id = $1 AND machine_id = $2
        "#,
        license_key_id as LicenseKeyId,
        machine_id,
    )
    .fetch_optional(&mut *tx)
    .await?;

    // For truly new activations, enforce the limit
    if existing.is_none()
        && let Some(max) = max_activations
    {
        let count = sqlx::query_scalar!(
            r#"SELECT COUNT(*) AS "count!" FROM license_activations WHERE license_key_id = $1 AND is_active = true"#,
            license_key_id as LicenseKeyId,
        )
        .fetch_one(&mut *tx)
        .await?;

        if count >= max as i64 {
            tx.rollback().await?;
            return Ok(None);
        }
    }

    // Upsert: if same machine_id re-activates, reactivate it
    let activation = sqlx::query_as!(
        DbLicenseActivation,
        r#"
        INSERT INTO license_activations (license_key_id, machine_id, label)
        VALUES ($1, $2, $3)
        ON CONFLICT (license_key_id, machine_id)
        DO UPDATE SET is_active = true, last_validated_at = NOW(),
                      label = COALESCE(EXCLUDED.label, license_activations.label)
        RETURNING id AS "id: LicenseActivationId", license_key_id AS "license_key_id: LicenseKeyId",
                  machine_id, label,
                  activated_at AS "activated_at: chrono::DateTime<chrono::Utc>",
                  last_validated_at AS "last_validated_at: chrono::DateTime<chrono::Utc>", is_active
        "#,
        license_key_id as LicenseKeyId,
        machine_id,
        label,
    )
    .fetch_one(&mut *tx)
    .await?;

    // Recount active activations to keep denormalized count accurate
    sqlx::query!(
        r#"
        UPDATE license_keys
        SET activation_count = (
            SELECT COUNT(*) FROM license_activations
            WHERE license_key_id = $1 AND is_active = true
        )
        WHERE id = $1
        "#,
        license_key_id as LicenseKeyId,
    )
    .execute(&mut *tx)
    .await?;

    tx.commit().await?;
    Ok(Some(activation))
}

/// Deactivate a machine and update the key's activation_count.
///
/// Only recounts if a row was actually deactivated (`rows_affected > 0`),
/// avoiding a wasted query when the machine wasn't active. Uses the same
/// full-recount strategy as [`try_create_activation`] for consistency.
#[tracing::instrument(skip_all)]
pub async fn deactivate_machine(
    pool: &PgPool,
    license_key_id: LicenseKeyId,
    machine_id: &str,
) -> Result<bool> {
    let mut tx = pool.begin().await?;

    let result = sqlx::query!(
        r#"
        UPDATE license_activations
        SET is_active = false
        WHERE license_key_id = $1 AND machine_id = $2 AND is_active = true
        "#,
        license_key_id as LicenseKeyId,
        machine_id,
    )
    .execute(&mut *tx)
    .await?;

    if result.rows_affected() > 0 {
        // Recount active activations
        sqlx::query!(
            r#"
            UPDATE license_keys
            SET activation_count = (
                SELECT COUNT(*) FROM license_activations
                WHERE license_key_id = $1 AND is_active = true
            )
            WHERE id = $1
            "#,
            license_key_id as LicenseKeyId,
        )
        .execute(&mut *tx)
        .await?;

        tx.commit().await?;
        Ok(true)
    } else {
        tx.commit().await?;
        Ok(false)
    }
}

/// Create a manually-generated key for an item, atomically enforcing a per-item
/// cap. Locks the item row `FOR UPDATE` so concurrent manual issuance for the
/// same item serializes, the prior count-then-insert let N concurrent generates
/// each read `count = cap-1` and all insert, exceeding the cap (fuzz 2026-07-06
/// C6-1). Returns `None` if the cap is already reached (caller maps to a 400).
#[tracing::instrument(skip_all)]
pub async fn create_manual_key_capped(
    pool: &PgPool,
    item_id: ItemId,
    owner_id: UserId,
    key_code: &KeyCode,
    max_activations: Option<i32>,
    cap: i64,
) -> Result<Option<DbLicenseKey>> {
    let mut tx = pool.begin().await?;
    // Serialize concurrent manual issuance for this item so the count below is
    // stable through the insert (mirrors lock_project_for_splits' cap pattern).
    sqlx::query!(
        r#"SELECT id FROM items WHERE id = $1 FOR UPDATE"#,
        item_id as ItemId
    )
    .fetch_one(&mut *tx)
    .await?;
    let count = sqlx::query_scalar!(
        r#"SELECT COUNT(*) AS "count!" FROM license_keys WHERE item_id = $1"#,
        item_id as ItemId,
    )
    .fetch_one(&mut *tx)
    .await?;
    if count >= cap {
        tx.rollback().await?;
        return Ok(None);
    }
    let key = sqlx::query_as!(
        DbLicenseKey,
        r#"
        INSERT INTO license_keys (item_id, owner_id, transaction_id, key_code, max_activations)
        VALUES ($1, $2, NULL, $3, $4)
        RETURNING id AS "id: LicenseKeyId", item_id AS "item_id: ItemId",
                  owner_id AS "owner_id: UserId", transaction_id AS "transaction_id: TransactionId",
                  key_code AS "key_code: KeyCode", max_activations, activation_count,
                  revoked_at AS "revoked_at: chrono::DateTime<chrono::Utc>",
                  created_at AS "created_at: chrono::DateTime<chrono::Utc>"
        "#,
        item_id as ItemId,
        owner_id as UserId,
        key_code as &KeyCode,
        max_activations,
    )
    .fetch_one(&mut *tx)
    .await?;
    tx.commit().await?;
    Ok(Some(key))
}

/// Revoke a license key and deactivate all its activations.
///
/// Wrapped in a transaction so the key revocation and activation
/// deactivation are atomic; a crash between the two statements
/// cannot leave the key revoked with activations still active.
#[tracing::instrument(skip_all)]
pub async fn revoke_license_key(pool: &PgPool, key_id: LicenseKeyId) -> Result<()> {
    let mut tx = pool.begin().await?;

    sqlx::query!(
        r#"
        UPDATE license_keys
        SET revoked_at = NOW()
        WHERE id = $1
        "#,
        key_id as LicenseKeyId,
    )
    .execute(&mut *tx)
    .await?;

    sqlx::query!(
        "UPDATE license_activations SET is_active = false WHERE license_key_id = $1",
        key_id as LicenseKeyId,
    )
    .execute(&mut *tx)
    .await?;

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

/// Revoke all license keys for a given transaction and deactivate all activations.
/// Called from the Stripe `charge.refunded` webhook handler.
///
/// Two-step approach: bulk-revoke keys, then bulk-deactivate activations.
/// Separate queries because `license_activations` is keyed by `license_key_id`,
/// not `transaction_id`.
#[tracing::instrument(skip_all)]
pub async fn revoke_keys_by_transaction(
    conn: &mut sqlx::PgConnection,
    transaction_id: TransactionId,
) -> Result<u64> {
    // Get all key IDs for this transaction
    let key_ids: Vec<LicenseKeyId> = sqlx::query_scalar!(
        r#"SELECT id AS "id: LicenseKeyId" FROM license_keys WHERE transaction_id = $1 AND revoked_at IS NULL"#,
        transaction_id as TransactionId,
    )
    .fetch_all(&mut *conn)
    .await?;

    if key_ids.is_empty() {
        return Ok(0);
    }

    // Revoke the keys
    let result = sqlx::query!(
        r#"
        UPDATE license_keys
        SET revoked_at = NOW()
        WHERE transaction_id = $1 AND revoked_at IS NULL
        "#,
        transaction_id as TransactionId,
    )
    .execute(&mut *conn)
    .await?;

    // Deactivate all activations for those keys in a single query
    if !key_ids.is_empty() {
        sqlx::query!(
            "UPDATE license_activations SET is_active = false WHERE license_key_id = ANY($1)",
            &key_ids as &[LicenseKeyId],
        )
        .execute(&mut *conn)
        .await?;
    }

    Ok(result.rows_affected())
}