//! Mutations over the four contact sub-collection tables.
//!
//! Twelve functions that are three shapes (add, remove, update) replicated over
//! emails, phones, social handles and custom fields. They stay in one module so
//! the repetition stays visible rather than spread over four files that each say
//! the same thing.

use goingson_core::{
    ContactCustomField, ContactEmail, ContactEmailId, ContactId, ContactPhone, ContactPhoneId,
    CoreError, CustomFieldId, NewContactCustomField, NewContactEmail, NewContactPhone,
    NewSocialHandle, Result, SocialHandle, SocialHandleId, UserId,
};

use rusqlite::{Connection, params};

use crate::utils::execute;

pub(super) fn add_email(
    conn: &Connection,
    contact_id: ContactId,
    user_id: UserId,
    email: NewContactEmail,
) -> Result<ContactEmail> {
    // Verify contact ownership
    let exists: i64 = conn
        .query_row(
            "SELECT COUNT(*) FROM contacts WHERE id = ? AND user_id = ?",
            params![contact_id.to_string(), user_id.to_string()],
            |row| row.get(0),
        )
        .map_err(CoreError::database)?;

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

    let id = ContactEmailId::new();
    execute(
        conn,
        "INSERT INTO contact_emails (id, contact_id, address, label, is_primary) VALUES (?, ?, ?, ?, ?)",
        params![
            id.to_string(),
            contact_id.to_string(),
            &email.address,
            &email.label,
            email.is_primary as i32
        ],
    )?;

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

pub(super) fn remove_email(
    conn: &Connection,
    email_id: ContactEmailId,
    user_id: UserId,
) -> Result<bool> {
    // Verify ownership via JOIN
    let result = execute(
        conn,
        r"
            DELETE FROM contact_emails
            WHERE id = ? AND contact_id IN (SELECT id FROM contacts WHERE user_id = ?)
            ",
        params![email_id.to_string(), user_id.to_string()],
    )?;

    Ok(result > 0)
}

pub(super) fn update_email(
    conn: &Connection,
    email_id: ContactEmailId,
    user_id: UserId,
    email: &NewContactEmail,
) -> Result<Option<ContactEmail>> {
    let result = execute(
        conn,
        r"
            UPDATE contact_emails
            SET address = ?, label = ?, is_primary = ?
            WHERE id = ? AND contact_id IN (SELECT id FROM contacts WHERE user_id = ?)
            ",
        params![
            &email.address,
            &email.label,
            email.is_primary as i32,
            email_id.to_string(),
            user_id.to_string()
        ],
    )?;

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

    let row: (String, String, String, i32) = conn
        .query_row(
            "SELECT contact_id, address, label, is_primary FROM contact_emails WHERE id = ?",
            params![email_id.to_string()],
            |row| Ok((row.get(0)?, row.get(1)?, row.get(2)?, row.get(3)?)),
        )
        .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,
    }))
}

pub(super) fn add_phone(
    conn: &Connection,
    contact_id: ContactId,
    user_id: UserId,
    phone: NewContactPhone,
) -> Result<ContactPhone> {
    // Verify contact ownership
    let exists: i64 = conn
        .query_row(
            "SELECT COUNT(*) FROM contacts WHERE id = ? AND user_id = ?",
            params![contact_id.to_string(), user_id.to_string()],
            |row| row.get(0),
        )
        .map_err(CoreError::database)?;

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

    let id = ContactPhoneId::new();
    execute(
        conn,
        "INSERT INTO contact_phones (id, contact_id, number, label, is_primary) VALUES (?, ?, ?, ?, ?)",
        params![
            id.to_string(),
            contact_id.to_string(),
            &phone.number,
            &phone.label,
            phone.is_primary as i32
        ],
    )?;

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

pub(super) fn remove_phone(
    conn: &Connection,
    phone_id: ContactPhoneId,
    user_id: UserId,
) -> Result<bool> {
    let result = execute(
        conn,
        r"
            DELETE FROM contact_phones
            WHERE id = ? AND contact_id IN (SELECT id FROM contacts WHERE user_id = ?)
            ",
        params![phone_id.to_string(), user_id.to_string()],
    )?;

    Ok(result > 0)
}

pub(super) fn update_phone(
    conn: &Connection,
    phone_id: ContactPhoneId,
    user_id: UserId,
    phone: &NewContactPhone,
) -> Result<Option<ContactPhone>> {
    let result = execute(
        conn,
        r"
            UPDATE contact_phones
            SET number = ?, label = ?, is_primary = ?
            WHERE id = ? AND contact_id IN (SELECT id FROM contacts WHERE user_id = ?)
            ",
        params![
            &phone.number,
            &phone.label,
            phone.is_primary as i32,
            phone_id.to_string(),
            user_id.to_string()
        ],
    )?;

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

    let row: (String, String, String, i32) = conn
        .query_row(
            "SELECT contact_id, number, label, is_primary FROM contact_phones WHERE id = ?",
            params![phone_id.to_string()],
            |row| Ok((row.get(0)?, row.get(1)?, row.get(2)?, row.get(3)?)),
        )
        .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,
    }))
}

pub(super) fn add_social_handle(
    conn: &Connection,
    contact_id: ContactId,
    user_id: UserId,
    handle: NewSocialHandle,
) -> Result<SocialHandle> {
    // Verify contact ownership
    let exists: i64 = conn
        .query_row(
            "SELECT COUNT(*) FROM contacts WHERE id = ? AND user_id = ?",
            params![contact_id.to_string(), user_id.to_string()],
            |row| row.get(0),
        )
        .map_err(CoreError::database)?;

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

    let id = SocialHandleId::new();
    execute(
        conn,
        "INSERT INTO contact_social_handles (id, contact_id, platform, handle, url) VALUES (?, ?, ?, ?, ?)",
        params![
            id.to_string(),
            contact_id.to_string(),
            &handle.platform,
            &handle.handle,
            &handle.url
        ],
    )?;

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

pub(super) fn remove_social_handle(
    conn: &Connection,
    handle_id: SocialHandleId,
    user_id: UserId,
) -> Result<bool> {
    let result = execute(
        conn,
        r"
            DELETE FROM contact_social_handles
            WHERE id = ? AND contact_id IN (SELECT id FROM contacts WHERE user_id = ?)
            ",
        params![handle_id.to_string(), user_id.to_string()],
    )?;

    Ok(result > 0)
}

pub(super) fn update_social_handle(
    conn: &Connection,
    handle_id: SocialHandleId,
    user_id: UserId,
    handle: &NewSocialHandle,
) -> Result<Option<SocialHandle>> {
    let result = execute(
        conn,
        r"
            UPDATE contact_social_handles
            SET platform = ?, handle = ?, url = ?
            WHERE id = ? AND contact_id IN (SELECT id FROM contacts WHERE user_id = ?)
            ",
        params![
            &handle.platform,
            &handle.handle,
            &handle.url,
            handle_id.to_string(),
            user_id.to_string()
        ],
    )?;

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

    let row: (String, String, String, Option<String>) = conn
        .query_row(
            "SELECT contact_id, platform, handle, url FROM contact_social_handles WHERE id = ?",
            params![handle_id.to_string()],
            |row| Ok((row.get(0)?, row.get(1)?, row.get(2)?, row.get(3)?)),
        )
        .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,
    }))
}

pub(super) fn add_custom_field(
    conn: &Connection,
    contact_id: ContactId,
    user_id: UserId,
    field: NewContactCustomField,
) -> Result<ContactCustomField> {
    // Verify contact ownership
    let exists: i64 = conn
        .query_row(
            "SELECT COUNT(*) FROM contacts WHERE id = ? AND user_id = ?",
            params![contact_id.to_string(), user_id.to_string()],
            |row| row.get(0),
        )
        .map_err(CoreError::database)?;

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

    let id = CustomFieldId::new();
    execute(
        conn,
        "INSERT INTO contact_custom_fields (id, contact_id, label, value, url) VALUES (?, ?, ?, ?, ?)",
        params![
            id.to_string(),
            contact_id.to_string(),
            &field.label,
            &field.value,
            &field.url
        ],
    )?;

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

pub(super) fn remove_custom_field(
    conn: &Connection,
    field_id: CustomFieldId,
    user_id: UserId,
) -> Result<bool> {
    let result = execute(
        conn,
        r"
            DELETE FROM contact_custom_fields
            WHERE id = ? AND contact_id IN (SELECT id FROM contacts WHERE user_id = ?)
            ",
        params![field_id.to_string(), user_id.to_string()],
    )?;

    Ok(result > 0)
}

pub(super) fn update_custom_field(
    conn: &Connection,
    field_id: CustomFieldId,
    user_id: UserId,
    field: &NewContactCustomField,
) -> Result<Option<ContactCustomField>> {
    let result = execute(
        conn,
        r"
            UPDATE contact_custom_fields
            SET label = ?, value = ?, url = ?
            WHERE id = ? AND contact_id IN (SELECT id FROM contacts WHERE user_id = ?)
            ",
        params![
            &field.label,
            &field.value,
            &field.url,
            field_id.to_string(),
            user_id.to_string()
        ],
    )?;

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

    let row: (String, String, String, Option<String>) = conn
        .query_row(
            "SELECT contact_id, label, value, url FROM contact_custom_fields WHERE id = ?",
            params![field_id.to_string()],
            |row| Ok((row.get(0)?, row.get(1)?, row.get(2)?, row.get(3)?)),
        )
        .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,
    }))
}