use std::path::PathBuf;

use sqlx::{Connection as _, query};
use tracing::{debug, info, warn};

use crate::MbtType::{Flat, FlatWithHash, Normalized};
use crate::queries::detach_db;
use crate::{
    AGG_TILES_HASH, AGG_TILES_HASH_AFTER_APPLY, AGG_TILES_HASH_BEFORE_APPLY, MbtError, MbtResult,
    MbtType, Mbtiles,
};

#[hotpath::measure]
pub async fn apply_patch(base_file: PathBuf, patch_file: PathBuf, force: bool) -> MbtResult<()> {
    let base_mbt = Mbtiles::new(base_file)?;
    let patch_mbt = Mbtiles::new(patch_file)?;

    let mut conn = patch_mbt.open_readonly().await?;
    let patch_info = patch_mbt.examine_diff(&mut conn).await?;
    if patch_info.patch_type.is_some() {
        return Err(MbtError::UnsupportedPatchType);
    }
    patch_mbt.validate_diff_info(&patch_info, force)?;
    let patch_type = patch_info.mbt_type;
    conn.close().await?;

    let mut conn = base_mbt.open().await?;
    let base_info = base_mbt.examine_diff(&mut conn).await?;
    let base_hash = base_mbt.get_agg_tiles_hash(&mut conn).await?;
    base_mbt.assert_hashes(&base_info, force)?;

    match (force, base_hash, patch_info.agg_tiles_hash_before_apply) {
        (false, Some(base_hash), Some(expected_hash)) if base_hash != expected_hash => {
            return Err(MbtError::AggHashMismatchWithDiff(
                patch_mbt.filepath().to_string(),
                expected_hash,
                base_mbt.filepath().to_string(),
                base_hash,
            ));
        }
        (true, Some(base_hash), Some(expected_hash)) if base_hash != expected_hash => {
            warn!(
                "Aggregate tiles hash mismatch: Patch file expected {expected_hash} but found {base_hash} in {base_mbt} (force mode)"
            );
        }
        _ => {}
    }

    info!(
        "Applying patch file {patch_mbt} ({patch_type}) to {base_mbt} ({base_type})",
        base_type = base_info.mbt_type
    );

    patch_mbt.attach_to(&mut conn, "patchDb").await?;
    let select_from = get_select_from(base_info.mbt_type, patch_type);
    let (main_table, insert1, insert2) = get_insert_sql(base_info.mbt_type, &select_from);

    let sql = format!("{insert1} WHERE tile_data NOTNULL");
    query(&sql).execute(&mut conn).await?;

    if let Some(insert2) = insert2 {
        let sql = format!("{insert2} WHERE tile_data NOTNULL");
        query(&sql).execute(&mut conn).await?;
    }

    let sql = format!(
        "
    DELETE FROM {main_table}
    WHERE (zoom_level, tile_column, tile_row) IN (
        SELECT zoom_level, tile_column, tile_row FROM ({select_from} WHERE tile_data ISNULL)
    )"
    );
    query(&sql).execute(&mut conn).await?;

    if let Some(schema) = base_info.mbt_type.normalized_schema() {
        debug!("Removing unused tiles from the images table (normalized schema)");
        let (map, img, id) = (
            schema.map_table(),
            schema.content_table(),
            schema.tile_id_column(),
        );
        let sql = format!(
            "DELETE FROM {img} WHERE NOT EXISTS (SELECT 1 FROM {map} WHERE {map}.{id} = {img}.{id})"
        );
        query(&sql).execute(&mut conn).await?;
    }

    // Copy metadata from patchDb to the destination file, replacing existing values
    // Convert 'agg_tiles_hash_in_patch' into 'agg_tiles_hash'
    // Delete metadata entries if the value is NULL in patchDb
    let sql = format!(
        "
    INSERT OR REPLACE INTO metadata (name, value)
    SELECT IIF(name = '{AGG_TILES_HASH_AFTER_APPLY}', '{AGG_TILES_HASH}', name) as name,
           value
    FROM patchDb.metadata
    WHERE name NOTNULL AND name NOT IN ('{AGG_TILES_HASH}', '{AGG_TILES_HASH_BEFORE_APPLY}');"
    );
    query(&sql).execute(&mut conn).await?;

    let sql = "
    DELETE FROM metadata
    WHERE name IN (SELECT name FROM patchDb.metadata WHERE value ISNULL);";
    query(sql).execute(&mut conn).await?;

    detach_db(&mut conn, "patchDb").await
}

fn get_select_from(src_type: MbtType, patch_type: MbtType) -> String {
    if src_type == Flat {
        "SELECT zoom_level, tile_column, tile_row, tile_data FROM patchDb.tiles".to_string()
    } else {
        match patch_type {
            Flat => "
        SELECT zoom_level, tile_column, tile_row, tile_data, md5_hex(tile_data) as hash
        FROM patchDb.tiles"
                .to_string(),
            FlatWithHash
            | Normalized {
                schema: _,
                hash_view: true,
            } => "
        SELECT zoom_level, tile_column, tile_row, tile_data, tile_hash AS hash
        FROM patchDb.tiles_with_hash"
                .to_string(),
            Normalized {
                schema,
                hash_view: false,
            } => schema.select_tiles_sql("patchDb", "hash", "LEFT JOIN"),
        }
    }
}

fn get_insert_sql(src_type: MbtType, select_from: &str) -> (String, String, Option<String>) {
    match src_type {
        Flat => (
            "tiles".to_string(),
            format!(
                "
    INSERT OR REPLACE INTO tiles (zoom_level, tile_column, tile_row, tile_data)
    {select_from}"
            ),
            None,
        ),
        FlatWithHash => (
            "tiles_with_hash".to_string(),
            format!(
                "
    INSERT OR REPLACE INTO tiles_with_hash (zoom_level, tile_column, tile_row, tile_data, tile_hash)
    {select_from}"
            ),
            None,
        ),
        Normalized { schema, .. } => {
            let (map, img, id) = (
                schema.map_table(),
                schema.content_table(),
                schema.tile_id_column(),
            );
            (
                map.to_string(),
                format!(
                    "
    INSERT OR REPLACE INTO {map} (zoom_level, tile_column, tile_row, {id})
    SELECT zoom_level, tile_column, tile_row, hash as {id}
    FROM ({select_from})"
                ),
                Some(format!(
                    "
    INSERT OR REPLACE INTO {img} ({id}, tile_data)
    SELECT hash as {id}, tile_data
    FROM ({select_from})"
                )),
            )
        }
    }
}

#[cfg(test)]
mod tests {
    use sqlx::Executor as _;

    use super::*;
    use crate::MbtilesCopier;
    use crate::metadata::temp_named_mbtiles;

    #[actix_rt::test]
    async fn apply_flat_patch_file() {
        // Copy the src file to an in-memory DB
        let script = include_str!("../../tests/fixtures/mbtiles/world_cities.sql");
        let (_mbt, _conn, src_file) = temp_named_mbtiles("flat_src_file_mem", script).await;

        let dst_file = PathBuf::from("file:apply_flat_patch_file?mode=memory&cache=shared");

        let mut src_conn = MbtilesCopier {
            src_file: src_file.clone(),
            dst_file: dst_file.clone(),
            ..Default::default()
        }
        .run()
        .await
        .unwrap();

        // Apply patch to the src data in in-memory DB
        let script = include_str!("../../tests/fixtures/mbtiles/world_cities_diff.sql");
        let (_mbt, _conn, patch_file) = temp_named_mbtiles("flat_patch_file_mem", script).await;
        apply_patch(dst_file, patch_file, true).await.unwrap();

        // Verify the data is the same as the file the patch was generated from
        let script = include_str!("../../tests/fixtures/mbtiles/world_cities_modified.sql");
        let (mbt, _conn, _) = temp_named_mbtiles("flat_attached_mem_db", script).await;
        mbt.attach_to(&mut src_conn, "testOtherDb").await.unwrap();

        assert!(
            src_conn
                .fetch_optional("SELECT * FROM tiles EXCEPT SELECT * FROM testOtherDb.tiles;")
                .await
                .unwrap()
                .is_none()
        );
    }

    #[actix_rt::test]
    async fn apply_normalized_patch_file() {
        // Copy the src file to an in-memory DB
        let script = include_str!("../../tests/fixtures/mbtiles/geography-class-jpg.sql");
        let (_mbt, _conn, src_file) = temp_named_mbtiles("normalized_src_file_mem", script).await;

        let dst_file =
            PathBuf::from("file:apply_normalized_diff_file_mem_db?mode=memory&cache=shared");

        let mut src_conn = MbtilesCopier {
            src_file: src_file.clone(),
            dst_file: dst_file.clone(),
            ..Default::default()
        }
        .run()
        .await
        .unwrap();

        // Apply patch to the src data in in-memory DB
        let script = include_str!("../../tests/fixtures/mbtiles/geography-class-jpg-diff.sql");
        let (_mbt, _conn, patch_file) =
            temp_named_mbtiles("normalized_patch_file_mem", script).await;
        apply_patch(dst_file, patch_file, true).await.unwrap();

        // Verify the data is the same as the file the patch was generated from
        let script = include_str!("../../tests/fixtures/mbtiles/geography-class-jpg-modified.sql");
        let (mbt, _conn, _) = temp_named_mbtiles("normalized_attached_mem_db", script).await;
        mbt.attach_to(&mut src_conn, "testOtherDb").await.unwrap();

        assert!(
            src_conn
                .fetch_optional("SELECT * FROM tiles EXCEPT SELECT * FROM testOtherDb.tiles;")
                .await
                .unwrap()
                .is_none()
        );
    }
}
