//! Person/cast metadata cache commands.
//!
//! TRACES: UR-035, UR-036 | IR-023 | DR-040, DR-041

use serde::{Deserialize, Serialize};
use std::sync::Arc;
use tauri::State;

use super::DatabaseWrapper;
use crate::storage::db_service::{DatabaseService, Query, QueryParam};

/// Cached person info returned to frontend
#[derive(specta::Type, Debug, Clone, Serialize, Deserialize)]
#[serde(rename_all = "camelCase")]
pub struct CachedPerson {
    pub id: String,
    pub server_id: String,
    pub name: String,
    pub overview: Option<String>,
    pub primary_image_tag: Option<String>,
    pub premiere_date: Option<String>,
    pub end_date: Option<String>,
}

/// Item-person association for caching
#[derive(specta::Type, Debug, Clone, Serialize, Deserialize)]
#[serde(rename_all = "camelCase")]
pub struct CachedItemPerson {
    pub item_id: String,
    pub person_id: String,
    pub server_id: String,
    pub person_type: String,
    pub role: Option<String>,
    pub sort_order: i32,
}

/// Save a person to the cache
#[tauri::command]
#[specta::specta]
pub async fn storage_save_person(
    db: State<'_, DatabaseWrapper>,
    person: CachedPerson,
) -> Result<(), String> {
    let db_service = {
        let database = db.0.lock().map_err(|e| e.to_string())?;
        Arc::new(database.service())
    };

    let query = Query::with_params(
        // A real UPSERT, not INSERT OR REPLACE — `people` is now backed by the
        // `people_fts` index (migration 022), and REPLACE would orphan an index
        // entry on every re-cache: it fires no AFTER DELETE trigger without
        // `recursive_triggers`, and reassigns the rowid that `content_rowid`
        // refers to. Same defect as DR-110 fixed for `items`.
        //
        // TRACES: UR-065 | DR-110, DR-111
        "INSERT INTO people (
            id, server_id, name, overview, primary_image_tag,
            premiere_date, end_date, synced_at
        ) VALUES (?, ?, ?, ?, ?, ?, ?, CURRENT_TIMESTAMP)
        ON CONFLICT(id) DO UPDATE SET
            server_id = excluded.server_id,
            name = excluded.name,
            overview = excluded.overview,
            primary_image_tag = excluded.primary_image_tag,
            premiere_date = excluded.premiere_date,
            end_date = excluded.end_date,
            synced_at = CURRENT_TIMESTAMP",
        vec![
            QueryParam::String(person.id),
            QueryParam::String(person.server_id),
            QueryParam::String(person.name),
            person
                .overview
                .map(QueryParam::String)
                .unwrap_or(QueryParam::Null),
            person
                .primary_image_tag
                .map(QueryParam::String)
                .unwrap_or(QueryParam::Null),
            person
                .premiere_date
                .map(QueryParam::String)
                .unwrap_or(QueryParam::Null),
            person
                .end_date
                .map(QueryParam::String)
                .unwrap_or(QueryParam::Null),
        ],
    );

    db_service.execute(query).await.map_err(|e| e.to_string())?;
    Ok(())
}

/// Get a cached person by ID
#[tauri::command]
#[specta::specta]
pub async fn storage_get_person(
    db: State<'_, DatabaseWrapper>,
    person_id: String,
) -> Result<Option<CachedPerson>, String> {
    let db_service = {
        let database = db.0.lock().map_err(|e| e.to_string())?;
        Arc::new(database.service())
    };

    let query = Query::with_params(
        "SELECT id, server_id, name, overview, primary_image_tag, premiere_date, end_date
         FROM people WHERE id = ?",
        vec![QueryParam::String(person_id)],
    );

    let result = db_service
        .query_optional(query, |row| {
            Ok(CachedPerson {
                id: row.get(0)?,
                server_id: row.get(1)?,
                name: row.get(2)?,
                overview: row.get(3)?,
                primary_image_tag: row.get(4)?,
                premiere_date: row.get(5)?,
                end_date: row.get(6)?,
            })
        })
        .await
        .map_err(|e| e.to_string())?;

    Ok(result)
}

/// Save item-person associations (batch)
#[tauri::command]
#[specta::specta]
pub async fn storage_save_item_people(
    db: State<'_, DatabaseWrapper>,
    associations: Vec<CachedItemPerson>,
) -> Result<(), String> {
    let db_service = {
        let database = db.0.lock().map_err(|e| e.to_string())?;
        Arc::new(database.service())
    };

    // Clone associations for the closure
    let associations_clone = associations.clone();

    // Use transaction for batch insert
    db_service
        .transaction(move |tx| {
            for assoc in &associations_clone {
                let query = Query::with_params(
                    "INSERT OR REPLACE INTO item_people (
                    item_id, person_id, server_id, person_type, role, sort_order, synced_at
                ) VALUES (?, ?, ?, ?, ?, ?, CURRENT_TIMESTAMP)",
                    vec![
                        QueryParam::String(assoc.item_id.clone()),
                        QueryParam::String(assoc.person_id.clone()),
                        QueryParam::String(assoc.server_id.clone()),
                        QueryParam::String(assoc.person_type.clone()),
                        assoc
                            .role
                            .clone()
                            .map(QueryParam::String)
                            .unwrap_or(QueryParam::Null),
                        QueryParam::Int(assoc.sort_order),
                    ],
                );
                tx.execute(query)?;
            }
            Ok(())
        })
        .await
        .map_err(|e| e.to_string())?;

    Ok(())
}

/// Get people for an item (with person details joined)
#[tauri::command]
#[specta::specta]
pub async fn storage_get_item_people(
    db: State<'_, DatabaseWrapper>,
    item_id: String,
) -> Result<Vec<CachedItemPerson>, String> {
    let db_service = {
        let database = db.0.lock().map_err(|e| e.to_string())?;
        Arc::new(database.service())
    };

    let query = Query::with_params(
        "SELECT ip.item_id, ip.person_id, ip.server_id, ip.person_type, ip.role, ip.sort_order
         FROM item_people ip
         WHERE ip.item_id = ?
         ORDER BY ip.sort_order ASC",
        vec![QueryParam::String(item_id)],
    );

    let people = db_service
        .query_many(query, |row| {
            Ok(CachedItemPerson {
                item_id: row.get(0)?,
                person_id: row.get(1)?,
                server_id: row.get(2)?,
                person_type: row.get(3)?,
                role: row.get(4)?,
                sort_order: row.get(5)?,
            })
        })
        .await
        .map_err(|e| e.to_string())?;

    Ok(people)
}
