//! SQLite implementation of the ContactRepository.
//!
//! Manages contacts with sub-collections (emails, phones, social handles).
//! Sub-collections are stored in separate tables and batch-loaded for list operations.

use async_trait::async_trait;
use sqlx::SqlitePool;
use std::collections::{HashMap, HashSet};
use goingson_core::{
    Contact, ContactCustomField, ContactEmail, ContactEmailId, ContactId, ContactPhone,
    ContactPhoneId, ContactRepository, CoreError, CustomFieldId, NewContact,
    NewContactCustomField, NewContactEmail, NewContactPhone, NewSocialHandle, Result,
    SocialHandle, SocialHandleId, UpdateContact, UserId,
};

use crate::utils::{escape_like, format_datetime_now, parse_datetime, parse_tags, parse_uuid};

// ============ Row Structs ============

#[derive(Debug, Clone, sqlx::FromRow)]
struct ContactRow {
    pub id: String,
    pub display_name: String,
    pub nickname: Option<String>,
    pub company: Option<String>,
    pub title: Option<String>,
    pub notes: String,
    pub tags: String,
    pub birthday: Option<String>,
    pub timezone: Option<String>,
    pub external_source: Option<String>,
    pub external_id: Option<String>,
    pub is_implicit: i32,
    pub created_at: String,
    pub updated_at: String,
}

#[derive(Debug, Clone, sqlx::FromRow)]
struct ContactEmailRow {
    pub id: String,
    pub contact_id: String,
    pub address: String,
    pub label: String,
    pub is_primary: i32,
}

#[derive(Debug, Clone, sqlx::FromRow)]
struct ContactPhoneRow {
    pub id: String,
    pub contact_id: String,
    pub number: String,
    pub label: String,
    pub is_primary: i32,
}

#[derive(Debug, Clone, sqlx::FromRow)]
struct SocialHandleRow {
    pub id: String,
    pub contact_id: String,
    pub platform: String,
    pub handle: String,
    pub url: Option<String>,
}

#[derive(Debug, Clone, sqlx::FromRow)]
struct CustomFieldRow {
    pub id: String,
    pub contact_id: String,
    pub label: String,
    pub value: String,
    pub url: Option<String>,
}

// ============ Row Conversions ============

fn contact_email_from_row(row: ContactEmailRow) -> Result<ContactEmail> {
    Ok(ContactEmail {
        id: parse_uuid(&row.id)?.into(),
        contact_id: parse_uuid(&row.contact_id)?.into(),
        address: row.address,
        label: row.label,
        is_primary: row.is_primary != 0,
    })
}

fn contact_phone_from_row(row: ContactPhoneRow) -> Result<ContactPhone> {
    Ok(ContactPhone {
        id: parse_uuid(&row.id)?.into(),
        contact_id: parse_uuid(&row.contact_id)?.into(),
        number: row.number,
        label: row.label,
        is_primary: row.is_primary != 0,
    })
}

fn social_handle_from_row(row: SocialHandleRow) -> Result<SocialHandle> {
    Ok(SocialHandle {
        id: parse_uuid(&row.id)?.into(),
        contact_id: parse_uuid(&row.contact_id)?.into(),
        platform: row.platform,
        handle: row.handle,
        url: row.url,
    })
}

fn custom_field_from_row(row: CustomFieldRow) -> Result<ContactCustomField> {
    Ok(ContactCustomField {
        id: parse_uuid(&row.id)?.into(),
        contact_id: parse_uuid(&row.contact_id)?.into(),
        label: row.label,
        value: row.value,
        url: row.url,
    })
}

fn contact_from_row(
    row: ContactRow,
    emails: Vec<ContactEmail>,
    phones: Vec<ContactPhone>,
    social_handles: Vec<SocialHandle>,
    custom_fields: Vec<ContactCustomField>,
) -> Result<Contact> {
    let birthday = row
        .birthday
        .as_deref()
        .map(|s| {
            chrono::NaiveDate::parse_from_str(s, "%Y-%m-%d")
                .map_err(|e| CoreError::database_msg(format!("Invalid birthday: {}", e)))
        })
        .transpose()?;

    Ok(Contact {
        id: parse_uuid(&row.id)?.into(),
        display_name: row.display_name,
        nickname: row.nickname,
        company: row.company,
        title: row.title,
        notes: row.notes,
        tags: parse_tags(&row.tags),
        birthday,
        timezone: row.timezone,
        external_source: row.external_source,
        external_id: row.external_id,
        is_implicit: row.is_implicit != 0,
        emails,
        phones,
        social_handles,
        custom_fields,
        created_at: parse_datetime(&row.created_at)?,
        updated_at: parse_datetime(&row.updated_at)?,
    })
}

// ============ Repository ============

/// SQLite-backed implementation of [`ContactRepository`].
pub struct SqliteContactRepository {
    pool: SqlitePool,
}

impl SqliteContactRepository {
    /// Creates a new repository instance with the given connection pool.
    #[tracing::instrument(skip_all)]
    pub fn new(pool: SqlitePool) -> Self {
        Self { pool }
    }

    /// Batch-load emails for a set of contact IDs.
    async fn load_emails_for_contacts(
        &self,
        ids: &[String],
    ) -> Result<HashMap<ContactId, Vec<ContactEmail>>> {
        if ids.is_empty() {
            return Ok(HashMap::new());
        }

        let placeholders = ids.iter().map(|_| "?").collect::<Vec<_>>().join(",");
        let sql = format!(
            "SELECT id, contact_id, address, label, is_primary FROM contact_emails WHERE contact_id IN ({}) ORDER BY is_primary DESC, rowid ASC",
            placeholders
        );

        let mut query = sqlx::query_as::<_, ContactEmailRow>(&sql);
        for id in ids {
            query = query.bind(id);
        }

        let rows = query.fetch_all(&self.pool).await.map_err(CoreError::database)?;
        let mut map: HashMap<ContactId, Vec<ContactEmail>> = HashMap::new();
        for row in rows {
            let contact_id: ContactId = parse_uuid(&row.contact_id)?.into();
            let email = contact_email_from_row(row)?;
            map.entry(contact_id).or_default().push(email);
        }
        Ok(map)
    }

    /// Batch-load phones for a set of contact IDs.
    async fn load_phones_for_contacts(
        &self,
        ids: &[String],
    ) -> Result<HashMap<ContactId, Vec<ContactPhone>>> {
        if ids.is_empty() {
            return Ok(HashMap::new());
        }

        let placeholders = ids.iter().map(|_| "?").collect::<Vec<_>>().join(",");
        let sql = format!(
            "SELECT id, contact_id, number, label, is_primary FROM contact_phones WHERE contact_id IN ({}) ORDER BY is_primary DESC, rowid ASC",
            placeholders
        );

        let mut query = sqlx::query_as::<_, ContactPhoneRow>(&sql);
        for id in ids {
            query = query.bind(id);
        }

        let rows = query.fetch_all(&self.pool).await.map_err(CoreError::database)?;
        let mut map: HashMap<ContactId, Vec<ContactPhone>> = HashMap::new();
        for row in rows {
            let contact_id: ContactId = parse_uuid(&row.contact_id)?.into();
            let phone = contact_phone_from_row(row)?;
            map.entry(contact_id).or_default().push(phone);
        }
        Ok(map)
    }

    /// Batch-load social handles for a set of contact IDs.
    async fn load_social_handles_for_contacts(
        &self,
        ids: &[String],
    ) -> Result<HashMap<ContactId, Vec<SocialHandle>>> {
        if ids.is_empty() {
            return Ok(HashMap::new());
        }

        let placeholders = ids.iter().map(|_| "?").collect::<Vec<_>>().join(",");
        let sql = format!(
            "SELECT id, contact_id, platform, handle, url FROM contact_social_handles WHERE contact_id IN ({}) ORDER BY rowid ASC",
            placeholders
        );

        let mut query = sqlx::query_as::<_, SocialHandleRow>(&sql);
        for id in ids {
            query = query.bind(id);
        }

        let rows = query.fetch_all(&self.pool).await.map_err(CoreError::database)?;
        let mut map: HashMap<ContactId, Vec<SocialHandle>> = HashMap::new();
        for row in rows {
            let contact_id: ContactId = parse_uuid(&row.contact_id)?.into();
            let handle = social_handle_from_row(row)?;
            map.entry(contact_id).or_default().push(handle);
        }
        Ok(map)
    }

    /// Batch-load custom fields for a set of contact IDs.
    async fn load_custom_fields_for_contacts(
        &self,
        ids: &[String],
    ) -> Result<HashMap<ContactId, Vec<ContactCustomField>>> {
        if ids.is_empty() {
            return Ok(HashMap::new());
        }

        let placeholders = ids.iter().map(|_| "?").collect::<Vec<_>>().join(",");
        let sql = format!(
            "SELECT id, contact_id, label, value, url FROM contact_custom_fields WHERE contact_id IN ({}) ORDER BY rowid ASC",
            placeholders
        );

        let mut query = sqlx::query_as::<_, CustomFieldRow>(&sql);
        for id in ids {
            query = query.bind(id);
        }

        let rows = query.fetch_all(&self.pool).await.map_err(CoreError::database)?;
        let mut map: HashMap<ContactId, Vec<ContactCustomField>> = HashMap::new();
        for row in rows {
            let contact_id: ContactId = parse_uuid(&row.contact_id)?.into();
            let field = custom_field_from_row(row)?;
            map.entry(contact_id).or_default().push(field);
        }
        Ok(map)
    }

    /// Hydrate a list of contact rows with their sub-collections.
    async fn hydrate_contacts(&self, rows: Vec<ContactRow>) -> Result<Vec<Contact>> {
        if rows.is_empty() {
            return Ok(vec![]);
        }

        let ids: Vec<String> = rows.iter().map(|r| r.id.clone()).collect();
        let mut emails_map = self.load_emails_for_contacts(&ids).await?;
        let mut phones_map = self.load_phones_for_contacts(&ids).await?;
        let mut social_map = self.load_social_handles_for_contacts(&ids).await?;
        let mut custom_map = self.load_custom_fields_for_contacts(&ids).await?;

        let mut contacts = Vec::with_capacity(rows.len());
        for row in rows {
            let id: ContactId = parse_uuid(&row.id)?.into();
            let emails = emails_map.remove(&id).unwrap_or_default();
            let phones = phones_map.remove(&id).unwrap_or_default();
            let social_handles = social_map.remove(&id).unwrap_or_default();
            let custom_fields = custom_map.remove(&id).unwrap_or_default();
            contacts.push(contact_from_row(row, emails, phones, social_handles, custom_fields)?);
        }
        Ok(contacts)
    }
}

#[async_trait]
impl ContactRepository for SqliteContactRepository {
    #[tracing::instrument(skip_all)]
    async fn list_all(&self, user_id: UserId) -> Result<Vec<Contact>> {
        let rows = sqlx::query_as::<_, ContactRow>(
            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
            "#,
        )
        .bind(user_id.to_string())
        .fetch_all(&self.pool)
        .await
        .map_err(CoreError::database)?;

        self.hydrate_contacts(rows).await
    }

    #[tracing::instrument(skip_all)]
    async fn get_by_id(&self, id: ContactId, user_id: UserId) -> Result<Option<Contact>> {
        let row = sqlx::query_as::<_, ContactRow>(
            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 = ?
            "#,
        )
        .bind(id.to_string())
        .bind(user_id.to_string())
        .fetch_optional(&self.pool)
        .await
        .map_err(CoreError::database)?;

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

    #[tracing::instrument(skip_all)]
    async fn create(&self, user_id: UserId, contact: NewContact) -> Result<Contact> {
        let id = ContactId::new();
        let now = format_datetime_now();
        let tags_json = serde_json::to_string(&contact.tags).unwrap_or_else(|_| "[]".to_string());
        let birthday_str = contact.birthday.map(|d| d.format("%Y-%m-%d").to_string());

        sqlx::query(
            r#"
            INSERT INTO contacts (id, user_id, display_name, nickname, company, title, notes, tags, birthday, timezone, is_implicit, created_at, updated_at)
            VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
            "#,
        )
        .bind(id.to_string())
        .bind(user_id.to_string())
        .bind(&contact.display_name)
        .bind(&contact.nickname)
        .bind(&contact.company)
        .bind(&contact.title)
        .bind(&contact.notes)
        .bind(&tags_json)
        .bind(&birthday_str)
        .bind(&contact.timezone)
        .bind(if contact.is_implicit { 1 } else { 0 })
        .bind(&now)
        .bind(&now)
        .execute(&self.pool)
        .await
        .map_err(CoreError::database)?;

        self.get_by_id(id, user_id)
            .await?
            .ok_or_else(|| CoreError::internal("Failed to retrieve created contact"))
    }

    #[tracing::instrument(skip_all)]
    async fn update(&self, id: ContactId, user_id: UserId, contact: UpdateContact) -> Result<Option<Contact>> {
        let now = format_datetime_now();
        let tags_json = serde_json::to_string(&contact.tags).unwrap_or_else(|_| "[]".to_string());
        let birthday_str = contact.birthday.map(|d| d.format("%Y-%m-%d").to_string());

        let result = sqlx::query(
            r#"
            UPDATE contacts
            SET display_name = ?, nickname = ?, company = ?, title = ?, notes = ?, tags = ?, birthday = ?, timezone = ?, updated_at = ?
            WHERE id = ? AND user_id = ?
            "#,
        )
        .bind(&contact.display_name)
        .bind(&contact.nickname)
        .bind(&contact.company)
        .bind(&contact.title)
        .bind(&contact.notes)
        .bind(&tags_json)
        .bind(&birthday_str)
        .bind(&contact.timezone)
        .bind(&now)
        .bind(id.to_string())
        .bind(user_id.to_string())
        .execute(&self.pool)
        .await
        .map_err(CoreError::database)?;

        if result.rows_affected() > 0 {
            self.get_by_id(id, user_id).await
        } else {
            Ok(None)
        }
    }

    #[tracing::instrument(skip_all)]
    async fn delete(&self, id: ContactId, user_id: UserId) -> Result<bool> {
        let result = sqlx::query("DELETE FROM contacts WHERE id = ? AND user_id = ?")
            .bind(id.to_string())
            .bind(user_id.to_string())
            .execute(&self.pool)
            .await
            .map_err(CoreError::database)?;

        Ok(result.rows_affected() > 0)
    }

    #[tracing::instrument(skip_all)]
    async fn delete_many(&self, ids: &[ContactId], user_id: UserId) -> Result<u64> {
        if ids.is_empty() {
            return Ok(0);
        }
        let user_id_str = user_id.to_string();
        let placeholders = vec!["?"; ids.len()].join(",");
        let sql = format!("DELETE FROM contacts WHERE user_id = ? AND id IN ({placeholders})");
        let mut query = sqlx::query(&sql).bind(&user_id_str);
        for id in ids {
            query = query.bind(id.to_string());
        }
        let result = query.execute(&self.pool).await.map_err(CoreError::database)?;
        Ok(result.rows_affected())
    }

    #[tracing::instrument(skip_all)]
    async fn tag_many(&self, ids: &[ContactId], user_id: UserId, tag: &str) -> Result<u64> {
        if ids.is_empty() || tag.is_empty() {
            return Ok(0);
        }
        let user_id_str = user_id.to_string();
        let like_pattern = format!("%\"{}\"%" , escape_like(tag));
        let placeholders = vec!["?"; ids.len()].join(",");
        // Append tag to JSON array where not already present.
        let sql = format!(
            r#"UPDATE contacts
               SET tags = CASE
                   WHEN tags IS NULL OR tags = '' OR tags = '[]'
                       THEN json_array(?)
                   ELSE json_insert(tags, '$[#]', ?)
               END,
               updated_at = datetime('now')
               WHERE user_id = ? AND id IN ({placeholders})
                 AND (tags IS NULL OR tags NOT LIKE ? ESCAPE '\')"#,
        );
        let mut query = sqlx::query(&sql)
            .bind(tag)
            .bind(tag)
            .bind(&user_id_str);
        for id in ids {
            query = query.bind(id.to_string());
        }
        query = query.bind(&like_pattern);
        let result = query.execute(&self.pool).await.map_err(CoreError::database)?;
        Ok(result.rows_affected())
    }

    #[tracing::instrument(skip_all)]
    async fn list_by_tag(&self, 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 = sqlx::query_as::<_, ContactRow>(
            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
            "#,
        )
        .bind(user_id.to_string())
        .bind(&pattern)
        .fetch_all(&self.pool)
        .await
        .map_err(CoreError::database)?;

        self.hydrate_contacts(rows).await
    }

    #[tracing::instrument(skip_all)]
    async fn list_filtered(&self, 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 self.list_all(user_id).await;
        }

        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 mut query = sqlx::query_as::<_, ContactRow>(&sql);
        for bind in &binds {
            query = query.bind(bind);
        }

        let rows = query.fetch_all(&self.pool).await.map_err(CoreError::database)?;
        self.hydrate_contacts(rows).await
    }

    #[tracing::instrument(skip_all)]
    async fn find_by_email(&self, user_id: UserId, email: &str) -> Result<Option<Contact>> {
        let row = sqlx::query_as::<_, ContactRow>(
            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
            "#,
        )
        .bind(user_id.to_string())
        .bind(email)
        .fetch_optional(&self.pool)
        .await
        .map_err(CoreError::database)?;

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

    #[tracing::instrument(skip_all)]
    async fn find_emails_in_contacts(&self, user_id: UserId, addresses: &[&str]) -> Result<HashSet<String>> {
        if addresses.is_empty() {
            return Ok(HashSet::new());
        }

        let placeholders = addresses.iter().map(|_| "?").collect::<Vec<_>>().join(",");
        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 q = sqlx::query_as::<_, (String,)>(&query).bind(user_id.to_string());
        for addr in addresses {
            q = q.bind(addr.to_lowercase());
        }

        let rows = q.fetch_all(&self.pool).await
            .map_err(CoreError::database)?;

        Ok(rows.into_iter().map(|(a,)| a).collect())
    }

    #[tracing::instrument(skip_all)]
    async fn promote_contact(&self, id: ContactId, user_id: UserId) -> Result<Option<Contact>> {
        let now = format_datetime_now();
        let result = sqlx::query(
            "UPDATE contacts SET is_implicit = 0, updated_at = ? WHERE id = ? AND user_id = ?"
        )
        .bind(&now)
        .bind(id.to_string())
        .bind(user_id.to_string())
        .execute(&self.pool)
        .await
        .map_err(CoreError::database)?;

        if result.rows_affected() > 0 {
            self.get_by_id(id, user_id).await
        } else {
            Ok(None)
        }
    }

    #[tracing::instrument(skip_all)]
    async fn find_by_external_id(&self, source: &str, ext_id: &str, user_id: UserId) -> Result<Option<Contact>> {
        let row = sqlx::query_as::<_, ContactRow>(
            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
            "#,
        )
        .bind(user_id.to_string())
        .bind(source)
        .bind(ext_id)
        .fetch_optional(&self.pool)
        .await
        .map_err(CoreError::database)?;

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

    #[tracing::instrument(skip_all)]
    async fn add_email(&self, contact_id: ContactId, user_id: UserId, email: NewContactEmail) -> Result<ContactEmail> {
        // Verify contact ownership
        let exists = sqlx::query_scalar::<_, i32>(
            "SELECT COUNT(*) FROM contacts WHERE id = ? AND user_id = ?"
        )
        .bind(contact_id.to_string())
        .bind(user_id.to_string())
        .fetch_one(&self.pool)
        .await
        .map_err(CoreError::database)?;

        if exists == 0 {
            return Err(CoreError::not_found("contact", contact_id));
        }

        let id = ContactEmailId::new();
        sqlx::query(
            "INSERT INTO contact_emails (id, contact_id, address, label, is_primary) VALUES (?, ?, ?, ?, ?)"
        )
        .bind(id.to_string())
        .bind(contact_id.to_string())
        .bind(&email.address)
        .bind(&email.label)
        .bind(email.is_primary as i32)
        .execute(&self.pool)
        .await
        .map_err(CoreError::database)?;

        Ok(ContactEmail {
            id,
            contact_id,
            address: email.address,
            label: email.label,
            is_primary: email.is_primary,
        })
    }

    #[tracing::instrument(skip_all)]
    async fn remove_email(&self, email_id: ContactEmailId, user_id: UserId) -> Result<bool> {
        // Verify ownership via JOIN
        let result = sqlx::query(
            r#"
            DELETE FROM contact_emails
            WHERE id = ? AND contact_id IN (SELECT id FROM contacts WHERE user_id = ?)
            "#
        )
        .bind(email_id.to_string())
        .bind(user_id.to_string())
        .execute(&self.pool)
        .await
        .map_err(CoreError::database)?;

        Ok(result.rows_affected() > 0)
    }

    #[tracing::instrument(skip_all)]
    async fn add_phone(&self, contact_id: ContactId, user_id: UserId, phone: NewContactPhone) -> Result<ContactPhone> {
        // Verify contact ownership
        let exists = sqlx::query_scalar::<_, i32>(
            "SELECT COUNT(*) FROM contacts WHERE id = ? AND user_id = ?"
        )
        .bind(contact_id.to_string())
        .bind(user_id.to_string())
        .fetch_one(&self.pool)
        .await
        .map_err(CoreError::database)?;

        if exists == 0 {
            return Err(CoreError::not_found("contact", contact_id));
        }

        let id = ContactPhoneId::new();
        sqlx::query(
            "INSERT INTO contact_phones (id, contact_id, number, label, is_primary) VALUES (?, ?, ?, ?, ?)"
        )
        .bind(id.to_string())
        .bind(contact_id.to_string())
        .bind(&phone.number)
        .bind(&phone.label)
        .bind(phone.is_primary as i32)
        .execute(&self.pool)
        .await
        .map_err(CoreError::database)?;

        Ok(ContactPhone {
            id,
            contact_id,
            number: phone.number,
            label: phone.label,
            is_primary: phone.is_primary,
        })
    }

    #[tracing::instrument(skip_all)]
    async fn remove_phone(&self, phone_id: ContactPhoneId, user_id: UserId) -> Result<bool> {
        let result = sqlx::query(
            r#"
            DELETE FROM contact_phones
            WHERE id = ? AND contact_id IN (SELECT id FROM contacts WHERE user_id = ?)
            "#
        )
        .bind(phone_id.to_string())
        .bind(user_id.to_string())
        .execute(&self.pool)
        .await
        .map_err(CoreError::database)?;

        Ok(result.rows_affected() > 0)
    }

    #[tracing::instrument(skip_all)]
    async fn add_social_handle(&self, contact_id: ContactId, user_id: UserId, handle: NewSocialHandle) -> Result<SocialHandle> {
        // Verify contact ownership
        let exists = sqlx::query_scalar::<_, i32>(
            "SELECT COUNT(*) FROM contacts WHERE id = ? AND user_id = ?"
        )
        .bind(contact_id.to_string())
        .bind(user_id.to_string())
        .fetch_one(&self.pool)
        .await
        .map_err(CoreError::database)?;

        if exists == 0 {
            return Err(CoreError::not_found("contact", contact_id));
        }

        let id = SocialHandleId::new();
        sqlx::query(
            "INSERT INTO contact_social_handles (id, contact_id, platform, handle, url) VALUES (?, ?, ?, ?, ?)"
        )
        .bind(id.to_string())
        .bind(contact_id.to_string())
        .bind(&handle.platform)
        .bind(&handle.handle)
        .bind(&handle.url)
        .execute(&self.pool)
        .await
        .map_err(CoreError::database)?;

        Ok(SocialHandle {
            id,
            contact_id,
            platform: handle.platform,
            handle: handle.handle,
            url: handle.url,
        })
    }

    #[tracing::instrument(skip_all)]
    async fn remove_social_handle(&self, handle_id: SocialHandleId, user_id: UserId) -> Result<bool> {
        let result = sqlx::query(
            r#"
            DELETE FROM contact_social_handles
            WHERE id = ? AND contact_id IN (SELECT id FROM contacts WHERE user_id = ?)
            "#
        )
        .bind(handle_id.to_string())
        .bind(user_id.to_string())
        .execute(&self.pool)
        .await
        .map_err(CoreError::database)?;

        Ok(result.rows_affected() > 0)
    }

    #[tracing::instrument(skip_all)]
    async fn add_custom_field(&self, contact_id: ContactId, user_id: UserId, field: NewContactCustomField) -> Result<ContactCustomField> {
        // Verify contact ownership
        let exists = sqlx::query_scalar::<_, i32>(
            "SELECT COUNT(*) FROM contacts WHERE id = ? AND user_id = ?"
        )
        .bind(contact_id.to_string())
        .bind(user_id.to_string())
        .fetch_one(&self.pool)
        .await
        .map_err(CoreError::database)?;

        if exists == 0 {
            return Err(CoreError::not_found("contact", contact_id));
        }

        let id = CustomFieldId::new();
        sqlx::query(
            "INSERT INTO contact_custom_fields (id, contact_id, label, value, url) VALUES (?, ?, ?, ?, ?)"
        )
        .bind(id.to_string())
        .bind(contact_id.to_string())
        .bind(&field.label)
        .bind(&field.value)
        .bind(&field.url)
        .execute(&self.pool)
        .await
        .map_err(CoreError::database)?;

        Ok(ContactCustomField {
            id,
            contact_id,
            label: field.label,
            value: field.value,
            url: field.url,
        })
    }

    #[tracing::instrument(skip_all)]
    async fn remove_custom_field(&self, field_id: CustomFieldId, user_id: UserId) -> Result<bool> {
        let result = sqlx::query(
            r#"
            DELETE FROM contact_custom_fields
            WHERE id = ? AND contact_id IN (SELECT id FROM contacts WHERE user_id = ?)
            "#
        )
        .bind(field_id.to_string())
        .bind(user_id.to_string())
        .execute(&self.pool)
        .await
        .map_err(CoreError::database)?;

        Ok(result.rows_affected() > 0)
    }

    #[tracing::instrument(skip_all)]
    async fn update_email(&self, email_id: ContactEmailId, user_id: UserId, email: NewContactEmail) -> Result<Option<ContactEmail>> {
        let result = sqlx::query(
            r#"
            UPDATE contact_emails
            SET address = ?, label = ?, is_primary = ?
            WHERE id = ? AND contact_id IN (SELECT id FROM contacts WHERE user_id = ?)
            "#
        )
        .bind(&email.address)
        .bind(&email.label)
        .bind(email.is_primary as i32)
        .bind(email_id.to_string())
        .bind(user_id.to_string())
        .execute(&self.pool)
        .await
        .map_err(CoreError::database)?;

        if result.rows_affected() == 0 {
            return Ok(None);
        }

        let row = sqlx::query_as::<_, (String, String, String, i32)>(
            "SELECT contact_id, address, label, is_primary FROM contact_emails WHERE id = ?"
        )
        .bind(email_id.to_string())
        .fetch_one(&self.pool)
        .await
        .map_err(CoreError::database)?;

        Ok(Some(ContactEmail {
            id: email_id,
            contact_id: crate::utils::parse_uuid(&row.0)?.into(),
            address: row.1,
            label: row.2,
            is_primary: row.3 != 0,
        }))
    }

    #[tracing::instrument(skip_all)]
    async fn update_phone(&self, phone_id: ContactPhoneId, user_id: UserId, phone: NewContactPhone) -> Result<Option<ContactPhone>> {
        let result = sqlx::query(
            r#"
            UPDATE contact_phones
            SET number = ?, label = ?, is_primary = ?
            WHERE id = ? AND contact_id IN (SELECT id FROM contacts WHERE user_id = ?)
            "#
        )
        .bind(&phone.number)
        .bind(&phone.label)
        .bind(phone.is_primary as i32)
        .bind(phone_id.to_string())
        .bind(user_id.to_string())
        .execute(&self.pool)
        .await
        .map_err(CoreError::database)?;

        if result.rows_affected() == 0 {
            return Ok(None);
        }

        let row = sqlx::query_as::<_, (String, String, String, i32)>(
            "SELECT contact_id, number, label, is_primary FROM contact_phones WHERE id = ?"
        )
        .bind(phone_id.to_string())
        .fetch_one(&self.pool)
        .await
        .map_err(CoreError::database)?;

        Ok(Some(ContactPhone {
            id: phone_id,
            contact_id: crate::utils::parse_uuid(&row.0)?.into(),
            number: row.1,
            label: row.2,
            is_primary: row.3 != 0,
        }))
    }

    #[tracing::instrument(skip_all)]
    async fn update_social_handle(&self, handle_id: SocialHandleId, user_id: UserId, handle: NewSocialHandle) -> Result<Option<SocialHandle>> {
        let result = sqlx::query(
            r#"
            UPDATE contact_social_handles
            SET platform = ?, handle = ?, url = ?
            WHERE id = ? AND contact_id IN (SELECT id FROM contacts WHERE user_id = ?)
            "#
        )
        .bind(&handle.platform)
        .bind(&handle.handle)
        .bind(&handle.url)
        .bind(handle_id.to_string())
        .bind(user_id.to_string())
        .execute(&self.pool)
        .await
        .map_err(CoreError::database)?;

        if result.rows_affected() == 0 {
            return Ok(None);
        }

        let row = sqlx::query_as::<_, (String, String, String, Option<String>)>(
            "SELECT contact_id, platform, handle, url FROM contact_social_handles WHERE id = ?"
        )
        .bind(handle_id.to_string())
        .fetch_one(&self.pool)
        .await
        .map_err(CoreError::database)?;

        Ok(Some(SocialHandle {
            id: handle_id,
            contact_id: crate::utils::parse_uuid(&row.0)?.into(),
            platform: row.1,
            handle: row.2,
            url: row.3,
        }))
    }

    #[tracing::instrument(skip_all)]
    async fn update_custom_field(&self, field_id: CustomFieldId, user_id: UserId, field: NewContactCustomField) -> Result<Option<ContactCustomField>> {
        let result = sqlx::query(
            r#"
            UPDATE contact_custom_fields
            SET label = ?, value = ?, url = ?
            WHERE id = ? AND contact_id IN (SELECT id FROM contacts WHERE user_id = ?)
            "#
        )
        .bind(&field.label)
        .bind(&field.value)
        .bind(&field.url)
        .bind(field_id.to_string())
        .bind(user_id.to_string())
        .execute(&self.pool)
        .await
        .map_err(CoreError::database)?;

        if result.rows_affected() == 0 {
            return Ok(None);
        }

        let row = sqlx::query_as::<_, (String, String, String, Option<String>)>(
            "SELECT contact_id, label, value, url FROM contact_custom_fields WHERE id = ?"
        )
        .bind(field_id.to_string())
        .fetch_one(&self.pool)
        .await
        .map_err(CoreError::database)?;

        Ok(Some(ContactCustomField {
            id: field_id,
            contact_id: crate::utils::parse_uuid(&row.0)?.into(),
            label: row.1,
            value: row.2,
            url: row.3,
        }))
    }
}