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

use sqlx::PgPool;

use super::models::*;
use super::{OtaReleaseId, SyncAppId};
use crate::error::Result;

// ── App slug ──

/// Set the URL-friendly slug for a sync app.
#[tracing::instrument(skip_all)]
pub 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 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.
///
/// 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 async fn create_release(
    pool: &PgPool,
    app_id: SyncAppId,
    version: &str,
    notes: &str,
    signature: &str,
) -> Result<DbOtaRelease> {
    let release = sqlx::query_as::<_, DbOtaRelease>(
        r#"
        INSERT INTO ota_releases (app_id, version, notes, signature)
        VALUES ($1, $2, $3, $4)
        ON CONFLICT (app_id, version) DO NOTHING
        RETURNING *
        "#,
    )
    .bind(app_id)
    .bind(version)
    .bind(notes)
    .bind(signature)
    .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 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 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)
}

/// Delete a release (cascades to artifacts).
#[tracing::instrument(skip_all)]
pub 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 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 an artifact record for a release.
#[tracing::instrument(skip_all)]
pub async fn create_artifact(
    pool: &PgPool,
    release_id: OtaReleaseId,
    target: &str,
    arch: &str,
    s3_key: &str,
    file_size: i64,
) -> Result<DbOtaArtifact> {
    let artifact = sqlx::query_as::<_, DbOtaArtifact>(
        r#"
        INSERT INTO ota_artifacts (release_id, target, arch, s3_key, file_size)
        VALUES ($1, $2, $3, $4, $5)
        RETURNING *
        "#,
    )
    .bind(release_id)
    .bind(target)
    .bind(arch)
    .bind(s3_key)
    .bind(file_size)
    .fetch_one(pool)
    .await?;

    Ok(artifact)
}

/// Get an artifact by release, target, and arch.
#[tracing::instrument(skip_all)]
pub 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)
}