//! Table column whitelists and row-level apply logic (upsert, delete).
//!
//! # SQL Safety: `format!()` for table and column names
//!
//! Several functions in this module use `format!()` to interpolate table and column
//! names into SQL strings (e.g., `apply_upsert`, `apply_delete`).
//! This is safe because:
//!
//! 1. **Table names come from hardcoded constants** (`UPSERT_ORDER`, `DELETE_ORDER`) -- never
//!    from user input or remote data.
//! 2. **Column names come from `table_columns()`**, a compile-time whitelist of `&'static str`
//!    literals -- also never from user input.
//! 3. **All user-supplied values** (row IDs, field data) are passed through `sqlx::query().bind()`,
//!    which parameterizes them safely.
//! 4. **Unknown table names are rejected** before any SQL is constructed: both `apply_upsert` and
//!    `apply_delete` return an error if `table_columns()` returns `None`.

use goingson_core::CoreError;
use sqlx::SqliteConnection;

use super::EMAIL_ACCOUNT_SYNC_COLS;

/// Return the syncable column whitelist for a given table, or `None` if unknown.
pub(crate) fn table_columns(table: &str) -> Option<&'static [&'static str]> {
    match table {
        "projects" => Some(&[
            "id", "name", "description", "project_type", "status", "created_at", "user_id",
        ]),
        "tasks" => Some(&[
            "id", "project_id", "description", "status", "priority", "due", "tags", "urgency",
            "recurrence", "created_at", "user_id", "recurrence_parent_id", "source_email_id",
            "snoozed_until", "waiting_for_response", "waiting_since", "expected_response_date",
            "scheduled_start", "scheduled_duration", "is_focus", "focus_set_at", "contact_id",
            "milestone_id", "completed_at", "estimated_minutes", "actual_minutes",
        ]),
        "events" => Some(&[
            "id", "project_id", "title", "description", "start_time", "end_time", "location",
            "user_id", "linked_task_id", "recurrence", "recurrence_parent_id", "contact_id",
            "block_type", "external_source", "external_id", "is_read_only", "snoozed_until",
            "reminder_offsets_seconds",
        ]),
        "contacts" => Some(&[
            "id", "user_id", "display_name", "nickname", "company", "title", "notes", "tags",
            "birthday", "timezone", "external_source", "external_id", "created_at", "updated_at",
        ]),
        "contact_emails" => Some(&["id", "contact_id", "address", "label", "is_primary"]),
        "contact_phones" => Some(&["id", "contact_id", "number", "label", "is_primary"]),
        "contact_social_handles" => Some(&["id", "contact_id", "platform", "handle", "url"]),
        "contact_custom_fields" => Some(&["id", "contact_id", "label", "value", "url"]),
        "annotations" => Some(&["id", "task_id", "timestamp", "note"]),
        "subtasks" => Some(&[
            "id", "task_id", "text", "is_completed", "position", "created_at", "linked_task_id",
        ]),
        "milestones" => Some(&[
            "id", "user_id", "project_id", "name", "description", "position", "target_date",
            "status", "created_at",
        ]),
        "time_sessions" => Some(&[
            "id", "task_id", "user_id", "started_at", "ended_at", "duration_minutes",
            "created_at",
        ]),
        "attachments" => Some(&[
            "id", "user_id", "task_id", "project_id", "filename",
            "file_size", "mime_type", "blob_hash", "source_email_id", "created_at",
        ]),
        "sync_accounts" => Some(&[
            "id", "user_id", "provider", "account_name", "email",
            "sync_calendars", "sync_contacts", "calendar_ids",
            "sync_interval_minutes", "enabled", "created_at",
        ]),
        "email_accounts" => Some(EMAIL_ACCOUNT_SYNC_COLS),
        "daily_notes" => Some(&[
            "id", "user_id", "note_date", "went_well", "could_improve",
            "is_reviewed", "reviewed_at", "created_at", "updated_at",
        ]),
        _ => None,
    }
}

/// Apply an INSERT OR REPLACE for a remote change.
pub(crate) async fn apply_upsert(
    conn: &mut SqliteConnection,
    table: &str,
    _row_id: &str,
    data: &serde_json::Value,
) -> Result<(), CoreError> {
    // Email accounts use ON CONFLICT to preserve local credentials
    if table == "email_accounts" {
        return apply_email_account_upsert(conn, data).await;
    }

    let columns = table_columns(table)
        .ok_or_else(|| CoreError::bad_request(format!("unknown syncable table: {}", table)))?;

    let col_list = columns.join(", ");
    let placeholders = columns.iter().map(|_| "?").collect::<Vec<_>>().join(", ");
    let sql = format!(
        "INSERT OR REPLACE INTO {} ({}) VALUES ({})",
        table, col_list, placeholders
    );

    let mut query = sqlx::query(&sql);

    for col in columns {
        query = bind_json_value(query, &data[*col]);
    }

    query
        .execute(&mut *conn)
        .await
        .map_err(CoreError::database)?;

    Ok(())
}

/// Apply an upsert for email_accounts that preserves local credentials.
///
/// Uses INSERT ... ON CONFLICT(id) DO UPDATE to only touch the 16 config columns,
/// leaving `password`, `oauth2_access_token`, `oauth2_refresh_token`, and
/// `oauth2_token_expires_at` untouched on existing rows. New rows get `password = ''`
/// to satisfy the NOT NULL constraint.
async fn apply_email_account_upsert(
    conn: &mut SqliteConnection,
    data: &serde_json::Value,
) -> Result<(), CoreError> {
    let cols = EMAIL_ACCOUNT_SYNC_COLS;

    // INSERT columns: 16 sync cols + password (hardcoded to '')
    let mut insert_cols: Vec<&str> = cols.to_vec();
    insert_cols.push("password");

    let col_list = insert_cols.join(", ");
    let placeholders = insert_cols.iter().map(|_| "?").collect::<Vec<_>>().join(", ");

    // ON CONFLICT: only update the 16 sync columns
    let update_set = cols
        .iter()
        .filter(|c| **c != "id")
        .map(|c| format!("{} = excluded.{}", c, c))
        .collect::<Vec<_>>()
        .join(", ");

    let sql = format!(
        "INSERT INTO email_accounts ({}) VALUES ({}) ON CONFLICT(id) DO UPDATE SET {}",
        col_list, placeholders, update_set
    );

    let mut query = sqlx::query(&sql);

    // Bind the 16 sync columns from data
    for col in cols {
        query = bind_json_value(query, &data[*col]);
    }

    // Bind password = '' for the INSERT
    query = query.bind("");

    query
        .execute(&mut *conn)
        .await
        .map_err(CoreError::database)?;

    Ok(())
}

/// Bind a JSON value to a sqlx query.
pub(crate) fn bind_json_value<'q>(
    query: sqlx::query::Query<'q, sqlx::Sqlite, sqlx::sqlite::SqliteArguments<'q>>,
    val: &'q serde_json::Value,
) -> sqlx::query::Query<'q, sqlx::Sqlite, sqlx::sqlite::SqliteArguments<'q>> {
    match val {
        serde_json::Value::String(s) => query.bind(s.as_str()),
        serde_json::Value::Number(n) => {
            if let Some(i) = n.as_i64() {
                query.bind(i)
            } else if let Some(f) = n.as_f64() {
                query.bind(f)
            } else {
                query.bind(None::<String>)
            }
        }
        serde_json::Value::Bool(b) => query.bind(if *b { 1i32 } else { 0i32 }),
        serde_json::Value::Null => query.bind(None::<String>),
        _ => {
            // Arrays/objects: serialize as JSON string -- need owned String
            query.bind(val.to_string())
        }
    }
}

/// Apply a DELETE for a remote change.
pub(crate) async fn apply_delete(conn: &mut SqliteConnection, table: &str, row_id: &str) -> Result<(), CoreError> {
    // Validate table name is in our whitelist
    if table_columns(table).is_none() {
        return Err(CoreError::bad_request(format!("unknown syncable table: {}", table)));
    }

    let sql = format!("DELETE FROM {} WHERE id = ?", table);
    sqlx::query(&sql)
        .bind(row_id)
        .execute(&mut *conn)
        .await
        .map_err(CoreError::database)?;

    Ok(())
}