//! OTA release management: releases, artifacts, and app slug assignment.

use sqlx::PgPool;

use super::models::{DbOtaArtifact, DbOtaRelease, DbSyncApp};
use super::{OtaArtifactId, OtaReleaseId, SyncAppId};
use crate::error::Result;

// ── App slug ──

/// Set the URL-friendly slug for a sync app.
#[tracing::instrument(skip_all)]
pub(crate) async fn set_app_slug(pool: &PgPool, app_id: SyncAppId, slug: &str) -> Result<()> {
    sqlx::query("UPDATE sync_apps SET slug = $2 WHERE id = $1")
        .bind(app_id)
        .bind(slug)
        .execute(pool)
        .await?;

    Ok(())
}

/// Look up a sync app by its slug.
#[tracing::instrument(skip_all)]
pub(crate) async fn get_app_by_slug(pool: &PgPool, slug: &str) -> Result<Option<DbSyncApp>> {
    let app = sqlx::query_as::<_, DbSyncApp>(
        "SELECT * FROM sync_apps WHERE slug = $1 AND is_active = true",
    )
    .bind(slug)
    .fetch_optional(pool)
    .await?;

    Ok(app)
}

// ── Releases ──

/// Create a new OTA release for an app.
///
/// The signature is per-artifact (Tauri signs each file independently), so it is
/// supplied at artifact-register time via [`create_artifact`], not here. The
/// legacy `ota_releases.signature` column is left defaulted and unused.
///
/// Returns `Conflict` if a release with the same version already exists for
/// this app (enforced by the UNIQUE(app_id, version) constraint in migration
/// 033).
#[tracing::instrument(skip_all)]
pub(crate) async fn create_release(
    pool: &PgPool,
    app_id: SyncAppId,
    version: &str,
    notes: &str,
) -> Result<DbOtaRelease> {
    let release = sqlx::query_as::<_, DbOtaRelease>(
        r"
        INSERT INTO ota_releases (app_id, version, notes)
        VALUES ($1, $2, $3)
        ON CONFLICT (app_id, version) DO NOTHING
        RETURNING *
        ",
    )
    .bind(app_id)
    .bind(version)
    .bind(notes)
    .fetch_optional(pool)
    .await?;

    release.ok_or_else(|| {
        crate::error::AppError::Conflict(format!(
            "OTA release version {version} already exists for this app"
        ))
    })
}

/// List all releases for an app, newest first.
#[tracing::instrument(skip_all)]
pub(crate) async fn list_releases(pool: &PgPool, app_id: SyncAppId) -> Result<Vec<DbOtaRelease>> {
    let releases = sqlx::query_as::<_, DbOtaRelease>(
        "SELECT * FROM ota_releases WHERE app_id = $1 ORDER BY pub_date DESC LIMIT 100",
    )
    .bind(app_id)
    .fetch_all(pool)
    .await?;

    Ok(releases)
}

/// Get the latest release for an app by semantic version (highest version wins).
///
/// Falls back to pub_date ordering if version parts aren't numeric.
#[tracing::instrument(skip_all)]
pub(crate) async fn get_latest_release(
    pool: &PgPool,
    app_id: SyncAppId,
) -> Result<Option<DbOtaRelease>> {
    let release = sqlx::query_as::<_, DbOtaRelease>(
        r"
        SELECT * FROM ota_releases WHERE app_id = $1
        ORDER BY
            CASE WHEN split_part(version, '-', 1) ~ '^\d+(\.\d+)*$'
                 THEN (string_to_array(split_part(version, '-', 1), '.'))::int[]
                 ELSE ARRAY[0]
            END DESC,
            pub_date DESC
        LIMIT 1
        ",
    )
    .bind(app_id)
    .fetch_optional(pool)
    .await?;

    Ok(release)
}

/// Fetch a single release scoped to its app, a direct indexed `(id, app_id)`
/// lookup instead of listing the app's whole release set and scanning it in Rust
/// (ultra-fuzz Run 11 Perf SER-1). Returns None if the release doesn't exist or
/// doesn't belong to the app.
#[tracing::instrument(skip_all)]
pub(crate) async fn get_release(
    pool: &PgPool,
    app_id: SyncAppId,
    release_id: OtaReleaseId,
) -> Result<Option<DbOtaRelease>> {
    let release = sqlx::query_as::<_, DbOtaRelease>(
        "SELECT * FROM ota_releases WHERE id = $1 AND app_id = $2",
    )
    .bind(release_id)
    .bind(app_id)
    .fetch_optional(pool)
    .await?;

    Ok(release)
}

/// Delete a release (cascades to artifacts).
#[tracing::instrument(skip_all)]
pub(crate) async fn delete_release(pool: &PgPool, release_id: OtaReleaseId) -> Result<bool> {
    let result = sqlx::query("DELETE FROM ota_releases WHERE id = $1")
        .bind(release_id)
        .execute(pool)
        .await?;

    Ok(result.rows_affected() > 0)
}

/// Get artifact S3 keys for a release, verifying it belongs to the given app.
/// Returns None if the release doesn't exist or doesn't belong to the app.
#[tracing::instrument(skip_all)]
pub(crate) async fn get_release_artifact_keys(
    pool: &PgPool,
    app_id: SyncAppId,
    release_id: OtaReleaseId,
) -> Result<Option<Vec<String>>> {
    // Verify release belongs to app
    let exists: bool = sqlx::query_scalar(
        "SELECT EXISTS(SELECT 1 FROM ota_releases WHERE id = $1 AND app_id = $2)",
    )
    .bind(release_id)
    .bind(app_id)
    .fetch_one(pool)
    .await?;

    if !exists {
        return Ok(None);
    }

    let keys: Vec<String> =
        sqlx::query_scalar("SELECT s3_key FROM ota_artifacts WHERE release_id = $1")
            .bind(release_id)
            .fetch_all(pool)
            .await?;

    Ok(Some(keys))
}

// ── Artifacts ──

/// Create or replace an artifact record for a release.
///
/// The S3 key is deterministic (`ota/{app}/{version}/{target}/{arch}/artifact`) and
/// the object overwrites in place, so re-uploading the same artifact (fixing a bad
/// binary, retrying a half-finished upload) must succeed. Upsert on the
/// `(release_id, target, arch)` unique key rather than raising 23505 -> 500 on the
/// second upload (ultra-fuzz Run 12 Storage F1).
///
/// `signature` is the artifact's own minisign signature and must be non-empty: an
/// unsigned artifact can never be installed (the Tauri updater silently refuses
/// it), so storing "" just advertises a dead download. Reject it at the write
/// boundary. Re-upload preserves an existing signature when a new one isn't
/// supplied, so a bytes-only retry doesn't blank it.
#[tracing::instrument(skip_all)]
pub(crate) async fn create_artifact(
    pool: &PgPool,
    release_id: OtaReleaseId,
    target: &str,
    arch: &str,
    s3_key: &str,
    file_size: i64,
    signature: &str,
) -> Result<DbOtaArtifact> {
    if signature.trim().is_empty() {
        return Err(crate::error::AppError::BadRequest(
            "OTA artifact signature is required".to_string(),
        ));
    }
    let artifact = sqlx::query_as::<_, DbOtaArtifact>(
        r"
        INSERT INTO ota_artifacts (release_id, target, arch, s3_key, file_size, signature)
        VALUES ($1, $2, $3, $4, $5, $6)
        ON CONFLICT (release_id, target, arch)
        DO UPDATE SET s3_key = EXCLUDED.s3_key, file_size = EXCLUDED.file_size,
                      signature = EXCLUDED.signature, scan_status = 'pending'
        RETURNING *
        ",
    )
    .bind(release_id)
    .bind(target)
    .bind(arch)
    .bind(s3_key)
    .bind(file_size)
    .bind(signature)
    .fetch_one(pool)
    .await?;

    Ok(artifact)
}

/// Update an OTA artifact's malware-scan status (called by the scan worker).
#[tracing::instrument(skip_all)]
pub(crate) async fn update_artifact_scan_status(
    pool: &PgPool,
    artifact_id: OtaArtifactId,
    status: crate::db::FileScanStatus,
) -> std::result::Result<(), sqlx::Error> {
    sqlx::query("UPDATE ota_artifacts SET scan_status = $1 WHERE id = $2")
        .bind(status)
        .bind(artifact_id)
        .execute(pool)
        .await?;
    Ok(())
}

/// Get an artifact by release, target, and arch.
#[tracing::instrument(skip_all)]
pub(crate) async fn get_artifact(
    pool: &PgPool,
    release_id: OtaReleaseId,
    target: &str,
    arch: &str,
) -> Result<Option<DbOtaArtifact>> {
    let artifact = sqlx::query_as::<_, DbOtaArtifact>(
        "SELECT * FROM ota_artifacts WHERE release_id = $1 AND target = $2 AND arch = $3",
    )
    .bind(release_id)
    .bind(target)
    .bind(arch)
    .fetch_optional(pool)
    .await?;

    Ok(artifact)
}