//! Read paths for contacts.
//!
//! Every function takes an already-checked-out connection, so the parent trait
//! impl owns the pool checkout. `promote_contact` writes a single flag but lives
//! here: it is the terminal step of implicit-contact resolution and shares its
//! query shape with `find_by_email`.

use goingson_core::{Contact, ContactEmailEntry, ContactId, Result, UserId};
use std::collections::HashSet;

use rusqlite::{Connection, params, params_from_iter};

use crate::utils::{
    bind_placeholders, escape_like, execute, format_datetime_now, query_all, query_opt,
};

use super::hydrate;
use super::row::ContactRow;

pub(super) fn list_all(conn: &Connection, user_id: UserId) -> Result<Vec<Contact>> {
    let rows = query_all(
        conn,
        r"
            SELECT id, display_name, nickname, company, title, notes, tags, birthday, timezone, external_source, external_id, is_implicit, created_at, updated_at
            FROM contacts
            WHERE user_id = ? AND is_implicit = 0
            ORDER BY display_name ASC
            ",
        params![user_id.to_string()],
        ContactRow::from_row,
    )?;

    hydrate::hydrate_contacts(conn, rows)
}

pub(super) fn list_all_for_backup(conn: &Connection, user_id: UserId) -> Result<Vec<Contact>> {
    let rows = query_all(
        conn,
        r"
            SELECT id, display_name, nickname, company, title, notes, tags, birthday, timezone, external_source, external_id, is_implicit, created_at, updated_at
            FROM contacts
            WHERE user_id = ?
            ORDER BY display_name ASC
            ",
        params![user_id.to_string()],
        ContactRow::from_row,
    )?;

    hydrate::hydrate_contacts(conn, rows)
}

pub(super) fn get_by_id(
    conn: &Connection,
    id: ContactId,
    user_id: UserId,
) -> Result<Option<Contact>> {
    let row = query_opt(
        conn,
        r"
            SELECT id, display_name, nickname, company, title, notes, tags, birthday, timezone, external_source, external_id, is_implicit, created_at, updated_at
            FROM contacts
            WHERE id = ? AND user_id = ?
            ",
        params![id.to_string(), user_id.to_string()],
        ContactRow::from_row,
    )?;

    match row {
        Some(r) => {
            let contacts = hydrate::hydrate_contacts(conn, vec![r])?;
            Ok(contacts.into_iter().next())
        }
        None => Ok(None),
    }
}

pub(super) fn list_by_tag(conn: &Connection, user_id: UserId, tag: &str) -> Result<Vec<Contact>> {
    // Tags stored as JSON array, use LIKE for matching
    let pattern = format!("%\"{}\"%", escape_like(tag));
    let rows = query_all(
        conn,
        r"
            SELECT id, display_name, nickname, company, title, notes, tags, birthday, timezone, external_source, external_id, is_implicit, created_at, updated_at
            FROM contacts
            WHERE user_id = ? AND tags LIKE ? ESCAPE '\'
            ORDER BY display_name ASC
            ",
        params![user_id.to_string(), &pattern],
        ContactRow::from_row,
    )?;

    hydrate::hydrate_contacts(conn, rows)
}

pub(super) fn list_filtered(
    conn: &Connection,
    user_id: UserId,
    search: Option<&str>,
    tag: Option<&str>,
    include_implicit: bool,
) -> Result<Vec<Contact>> {
    let has_search = search.is_some_and(|s| !s.is_empty());
    let has_tag = tag.is_some_and(|t| !t.is_empty());

    if !has_search && !has_tag && !include_implicit {
        return list_all(conn, user_id);
    }

    let mut conditions = vec!["c.user_id = ?".to_string()];
    if !include_implicit {
        conditions.push("c.is_implicit = 0".to_string());
    }
    let mut binds: Vec<String> = vec![user_id.to_string()];

    if let Some(t) = tag.filter(|t| !t.is_empty()) {
        conditions.push("c.tags LIKE ? ESCAPE '\\'".to_string());
        binds.push(format!("%\"{}\"%", escape_like(t)));
    }

    if let Some(s) = search.filter(|s| !s.is_empty()) {
        let search_pattern = format!("%{}%", escape_like(&s.to_lowercase()));
        // Search across contact fields and email addresses using a subquery
        conditions.push(
            "(LOWER(c.display_name) LIKE ? ESCAPE '\\' OR LOWER(COALESCE(c.nickname, '')) LIKE ? ESCAPE '\\' OR LOWER(COALESCE(c.company, '')) LIKE ? ESCAPE '\\' OR LOWER(COALESCE(c.title, '')) LIKE ? ESCAPE '\\' OR LOWER(c.notes) LIKE ? ESCAPE '\\' OR EXISTS (SELECT 1 FROM contact_emails ce WHERE ce.contact_id = c.id AND LOWER(ce.address) LIKE ? ESCAPE '\\'))".to_string()
        );
        // 6 binds for the search pattern
        for _ in 0..6 {
            binds.push(search_pattern.clone());
        }
    }

    let sql = format!(
        "SELECT c.id, c.display_name, c.nickname, c.company, c.title, c.notes, c.tags, c.birthday, c.timezone, c.external_source, c.external_id, c.is_implicit, c.created_at, c.updated_at FROM contacts c WHERE {} ORDER BY c.display_name ASC",
        conditions.join(" AND ")
    );

    let rows = query_all(
        conn,
        &sql,
        params_from_iter(binds.iter()),
        ContactRow::from_row,
    )?;
    hydrate::hydrate_contacts(conn, rows)
}

pub(super) fn list_email_directory(
    conn: &Connection,
    user_id: UserId,
    include_implicit: bool,
) -> Result<Vec<ContactEmailEntry>> {
    // One JOIN, one row per email address, no per-contact sub-collection
    // hydration (the compose autocomplete only needs name + address).
    let implicit_filter = if include_implicit {
        ""
    } else {
        "AND c.is_implicit = 0"
    };
    let sql = format!(
        "SELECT c.display_name, ce.address, c.is_implicit \
         FROM contacts c JOIN contact_emails ce ON ce.contact_id = c.id \
         WHERE c.user_id = ? {implicit_filter} \
         ORDER BY c.display_name ASC, ce.is_primary DESC"
    );
    let rows: Vec<(String, String, i64)> =
        query_all(conn, &sql, params![user_id.to_string()], |row| {
            Ok((row.get(0)?, row.get(1)?, row.get(2)?))
        })?;
    Ok(rows
        .into_iter()
        .map(|(name, email, is_implicit)| ContactEmailEntry {
            name,
            email,
            is_implicit: is_implicit != 0,
        })
        .collect())
}

pub(super) fn find_by_email(
    conn: &Connection,
    user_id: UserId,
    email: &str,
) -> Result<Option<Contact>> {
    let row = query_opt(
        conn,
        r"
            SELECT c.id, c.display_name, c.nickname, c.company, c.title, c.notes, c.tags, c.birthday, c.timezone, c.external_source, c.external_id, c.is_implicit, c.created_at, c.updated_at
            FROM contacts c
            JOIN contact_emails ce ON ce.contact_id = c.id
            WHERE c.user_id = ? AND LOWER(ce.address) = LOWER(?)
            LIMIT 1
            ",
        params![user_id.to_string(), email],
        ContactRow::from_row,
    )?;

    match row {
        Some(r) => {
            let contacts = hydrate::hydrate_contacts(conn, vec![r])?;
            Ok(contacts.into_iter().next())
        }
        None => Ok(None),
    }
}

pub(super) fn find_emails_in_contacts(
    conn: &Connection,
    user_id: UserId,
    addresses: &[&str],
) -> Result<HashSet<String>> {
    if addresses.is_empty() {
        return Ok(HashSet::new());
    }

    let placeholders = bind_placeholders(addresses.len());
    let query = format!(
        "SELECT DISTINCT LOWER(ce.address) FROM contact_emails ce JOIN contacts c ON ce.contact_id = c.id WHERE c.user_id = ? AND LOWER(ce.address) IN ({placeholders})"
    );

    let mut binds: Vec<String> = Vec::with_capacity(addresses.len() + 1);
    binds.push(user_id.to_string());
    binds.extend(addresses.iter().map(|a| a.to_lowercase()));

    let rows: Vec<String> = query_all(conn, &query, params_from_iter(binds), |row| row.get(0))?;

    Ok(rows.into_iter().collect())
}

pub(super) fn promote_contact(
    conn: &Connection,
    id: ContactId,
    user_id: UserId,
) -> Result<Option<Contact>> {
    let now = format_datetime_now();
    let result = execute(
        conn,
        "UPDATE contacts SET is_implicit = 0, updated_at = ? WHERE id = ? AND user_id = ?",
        params![&now, id.to_string(), user_id.to_string()],
    )?;

    if result > 0 {
        get_by_id(conn, id, user_id)
    } else {
        Ok(None)
    }
}

pub(super) fn find_by_external_id(
    conn: &Connection,
    source: &str,
    ext_id: &str,
    user_id: UserId,
) -> Result<Option<Contact>> {
    let row = query_opt(
        conn,
        r"
            SELECT id, display_name, nickname, company, title, notes, tags, birthday, timezone, external_source, external_id, is_implicit, created_at, updated_at
            FROM contacts
            WHERE user_id = ? AND external_source = ? AND external_id = ?
            LIMIT 1
            ",
        params![user_id.to_string(), source, ext_id],
        ContactRow::from_row,
    )?;

    match row {
        Some(r) => {
            let contacts = hydrate::hydrate_contacts(conn, vec![r])?;
            Ok(contacts.into_iter().next())
        }
        None => Ok(None),
    }
}