//! Transactional backup restore.
//!
//! A single all-or-nothing restore over one SQLite transaction: either the entire
//! backup is merged into the database, or nothing is. This replaces the previous
//! per-repository orchestration in `goingson_core::backup_restore`, which ran each
//! entity through a separate pooled connection (so a mid-restore failure left a
//! permanent half-state) and rebuilt task sub-collections through the normal create
//! helpers, losing subtask completion state and ordering, and annotation
//! timestamps (ultra-fuzz Run #26 SERIOUS finding).
//!
//! Restore is a *merge*: every row is inserted verbatim with `INSERT OR IGNORE`,
//! preserving its original id and every field, and existing rows are left untouched.
//! Because ids are preserved, restoring the same backup twice is idempotent. The sync
//! changelog triggers fire as normal, so restored rows propagate to other devices.

use goingson_core::backup_restore::{RestoreInput, RestoreResult};
use goingson_core::models::SortDirection;
use goingson_core::{CoreError, DbValue, Result, UserId};
use sqlx::{Sqlite, SqlitePool, Transaction};

use crate::utils::{format_datetime, format_datetime_opt};

/// Every table a full backup must capture and restore, the single source of
/// truth for backup completeness. `collect_full_export` gathers each of these and
/// `restore_all` writes each back; the `backup_tables_match_schema` test asserts
/// this list equals the live schema minus [`EXCLUDED_TABLES`], so adding a table to
/// the schema without registering it here (or excluding it) is a test failure. This
/// is the structural fix for the recurring "backup silently omitted table X" finding
/// (ultra-fuzz CHRONIC-D), mirroring the trigger/synced-column round-trip test.
pub const BACKUP_TABLES: &[&str] = &[
    "projects",
    "tasks",
    "annotations",
    "subtasks",
    "task_status_tokens",
    "events",
    "emails",
    "contacts",
    "contact_emails",
    "contact_phones",
    "contact_social_handles",
    "contact_custom_fields",
    "milestones",
    "time_sessions",
    "daily_notes",
    "attachments",
    "sync_accounts",
    "saved_views",
    "weekly_reviews",
    "monthly_goals",
    "monthly_reflections",
];

/// Tables deliberately excluded from backups, each for a documented reason. A table
/// must appear in either [`BACKUP_TABLES`] or here; the coverage test fails otherwise.
pub const EXCLUDED_TABLES: &[&str] = &[
    "users",                  // single fixed desktop user, recreated on init
    "email_accounts", // credentials/config; secrets live in the OS keychain, re-auth on restore
    "backup_settings", // local backup cadence/retention config
    "backup_settings_new", // transient table-rename scaffold (absent post-migration)
    "imap_folder_sync_state", // sync-transient IMAP state, re-derived on next sync
    "sync_changelog", // sync internals, regenerated
    "sync_state",     // sync cursors/flags, internal
    "hlc_state",      // hybrid logical clock, device-local sync internal
    "sync_committed_hlc", // per-row committed HLC clock store, device-local sync internal
    "user_config", // app settings; synced keys recover via cloud sync, device-local keys re-derive per device
];

/// Restore a backup into the database as a single transaction.
///
/// On any error the transaction is dropped without committing, so the database is
/// left exactly as it was before the call.
pub async fn restore_all(
    pool: &SqlitePool,
    user_id: UserId,
    input: &RestoreInput,
) -> Result<RestoreResult> {
    let mut tx = pool.begin().await.map_err(CoreError::database)?;
    let mut result = RestoreResult::default();

    // Defer foreign-key enforcement to COMMIT. Without this, a self-referential FK
    // (tasks/events `recurrence_parent_id`) raises immediately when a child instance
    // is inserted before its parent root, and because the backup is emitted in
    // urgency/created order, the child routinely precedes the parent. An immediate
    // FK violation (code 787) aborts the whole transaction, losing the entire
    // restore (ultra-fuzz Run #27 C1). Deferring checks them once, at commit, by
    // which point every referent in the backup has been inserted; only a genuinely
    // dangling reference still fails. `INSERT OR IGNORE` does NOT suppress FK
    // violations, it only suppresses UNIQUE/NOT NULL/CHECK conflicts.
    sqlx::query("PRAGMA defer_foreign_keys = ON")
        .execute(&mut *tx)
        .await
        .map_err(CoreError::database)?;

    // The insert order below is no longer load-bearing for FK correctness (deferred
    // checks make any order valid as long as every referent is present), but is kept
    // roots-first for readability. Every table in `BACKUP_TABLES` must be restored
    // here; the round-trip test enforces it.
    restore_projects(&mut tx, user_id, input, &mut result).await?;
    restore_contacts_with_children(&mut tx, user_id, input, &mut result).await?;
    restore_milestones(&mut tx, user_id, input, &mut result).await?;
    restore_emails(&mut tx, user_id, input, &mut result).await?;
    restore_tasks_with_children(&mut tx, user_id, input, &mut result).await?;
    restore_time_sessions(&mut tx, user_id, input, &mut result).await?;
    restore_events(&mut tx, user_id, input, &mut result).await?;
    restore_attachments(&mut tx, user_id, input, &mut result).await?;
    restore_daily_notes(&mut tx, user_id, input, &mut result).await?;
    restore_sync_accounts(&mut tx, user_id, input, &mut result).await?;
    restore_saved_views(&mut tx, user_id, input, &mut result).await?;
    restore_weekly_reviews(&mut tx, user_id, input, &mut result).await?;
    restore_monthly_goals(&mut tx, user_id, input, &mut result).await?;
    restore_monthly_reflections(&mut tx, user_id, input, &mut result).await?;

    tx.commit().await.map_err(CoreError::database)?;
    Ok(result)
}

async fn restore_projects(
    tx: &mut Transaction<'_, Sqlite>,
    user_id: UserId,
    input: &RestoreInput,
    result: &mut RestoreResult,
) -> Result<()> {
    for project in &input.projects {
        let affected = sqlx::query(
            "INSERT OR IGNORE INTO projects (id, user_id, name, description, project_type, status, created_at) \
             VALUES (?, ?, ?, ?, ?, ?, ?)",
        )
        .bind(project.id.to_string())
        .bind(user_id.to_string())
        .bind(&project.name)
        .bind(&project.description)
        .bind(project.project_type.db_value())
        .bind(project.status.db_value())
        .bind(format_datetime(&project.created_at))
        .execute(&mut **tx)
        .await
        .map_err(CoreError::database)?
        .rows_affected();
        if affected > 0 {
            result.projects_restored += 1;
        }
    }
    Ok(())
}

async fn restore_tasks_with_children(
    tx: &mut Transaction<'_, Sqlite>,
    user_id: UserId,
    input: &RestoreInput,
    result: &mut RestoreResult,
) -> Result<()> {
    // First pass: task row + annotations + text-only subtasks. Children are restored
    // only when the parent task is newly inserted (a re-restore skips them).
    for task in &input.tasks {
        let tags_json = serde_json::to_string(&task.tags).unwrap_or_else(|_| "[]".to_string());
        let recurrence_rule_json = task
            .recurrence_rule
            .as_ref()
            .map(|r| serde_json::to_string(r).unwrap_or_default());

        let affected = sqlx::query(
            "INSERT OR IGNORE INTO tasks (\
                id, user_id, project_id, contact_id, milestone_id, description, status, \
                priority, due, tags, urgency, recurrence, recurrence_rule, recurrence_parent_id, \
                source_email_id, snoozed_until, waiting_for_response, waiting_since, expected_response_date, \
                scheduled_start, scheduled_duration, estimated_minutes, actual_minutes, \
                created_at, completed_at, is_focus, focus_set_at\
            ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)",
        )
        .bind(task.id.to_string())
        .bind(user_id.to_string())
        .bind(task.project_id.map(|p| p.to_string()))
        .bind(task.contact_id.map(|c| c.to_string()))
        .bind(task.milestone_id.map(|m| m.to_string()))
        .bind(&task.description)
        .bind(task.status.db_value())
        .bind(task.priority.db_value())
        .bind(format_datetime_opt(task.due))
        .bind(&tags_json)
        .bind(task.urgency)
        .bind(task.recurrence.db_value())
        .bind(&recurrence_rule_json)
        .bind(task.recurrence_parent_id.map(|p| p.to_string()))
        .bind(task.source_email_id.map(|e| e.to_string()))
        .bind(format_datetime_opt(task.snoozed_until))
        .bind(i32::from(task.waiting_for_response))
        .bind(format_datetime_opt(task.waiting_since))
        .bind(format_datetime_opt(task.expected_response_date))
        .bind(format_datetime_opt(task.scheduled_start))
        .bind(task.scheduled_duration)
        .bind(task.estimated_minutes)
        .bind(task.actual_minutes)
        .bind(format_datetime(&task.created_at))
        .bind(task.completed_at.map(|d| format_datetime(&d)))
        .bind(i32::from(task.is_focus))
        .bind(task.focus_set_at.map(|d| format_datetime(&d)))
        .execute(&mut **tx)
        .await
        .map_err(CoreError::database)?
        .rows_affected();

        if affected == 0 {
            continue;
        }
        result.tasks_restored += 1;

        // Annotations, preserve the original timestamp verbatim.
        for annotation in &task.annotations {
            let a = sqlx::query(
                "INSERT OR IGNORE INTO annotations (id, task_id, timestamp, note) VALUES (?, ?, ?, ?)",
            )
            .bind(annotation.id.to_string())
            .bind(task.id.to_string())
            .bind(format_datetime(&annotation.timestamp))
            .bind(&annotation.note)
            .execute(&mut **tx)
            .await
            .map_err(CoreError::database)?
            .rows_affected();
            if a > 0 {
                result.annotations_restored += 1;
            }
        }

        // Text-only subtasks, preserve is_completed and position verbatim.
        // Linked subtasks are deferred to the second pass (their linked_task_id FK
        // requires every task row to exist first).
        for subtask in &task.subtasks {
            if subtask.linked_task_id.is_some() {
                continue;
            }
            let s = sqlx::query(
                "INSERT OR IGNORE INTO subtasks (id, task_id, text, is_completed, position) \
                 VALUES (?, ?, ?, ?, ?)",
            )
            .bind(subtask.id.to_string())
            .bind(task.id.to_string())
            .bind(&subtask.text)
            .bind(i32::from(subtask.is_completed))
            .bind(subtask.position)
            .execute(&mut **tx)
            .await
            .map_err(CoreError::database)?
            .rows_affected();
            if s > 0 {
                result.subtasks_restored += 1;
            }
        }

        // Status tokens, preserve kind, state, is_primary, position, and the
        // deterministic id verbatim.
        for token in &task.status_tokens {
            let c = sqlx::query(
                "INSERT OR IGNORE INTO task_status_tokens (id, task_id, kind, reference, state, is_primary, position) \
                 VALUES (?, ?, ?, ?, ?, ?, ?)",
            )
            .bind(token.id.to_string())
            .bind(task.id.to_string())
            .bind(&token.kind)
            .bind(&token.reference)
            .bind(token.state.db_value())
            .bind(i32::from(token.is_primary))
            .bind(token.position)
            .execute(&mut **tx)
            .await
            .map_err(CoreError::database)?
            .rows_affected();
            if c > 0 {
                result.status_tokens_restored += 1;
            }
        }
    }

    // Second pass: linked subtasks, now that all task rows exist. Verbatim and
    // idempotent (INSERT OR IGNORE on the preserved id), so it is safe to run for
    // every task regardless of whether the parent was newly inserted.
    for task in &input.tasks {
        for subtask in &task.subtasks {
            let Some(linked_id) = subtask.linked_task_id else {
                continue;
            };
            let s = sqlx::query(
                "INSERT OR IGNORE INTO subtasks (id, task_id, text, is_completed, position, linked_task_id) \
                 VALUES (?, ?, ?, ?, ?, ?)",
            )
            .bind(subtask.id.to_string())
            .bind(task.id.to_string())
            .bind(&subtask.text)
            .bind(i32::from(subtask.is_completed))
            .bind(subtask.position)
            .bind(linked_id.to_string())
            .execute(&mut **tx)
            .await
            .map_err(CoreError::database)?
            .rows_affected();
            if s > 0 {
                result.subtasks_restored += 1;
            }
        }
    }

    Ok(())
}

async fn restore_events(
    tx: &mut Transaction<'_, Sqlite>,
    user_id: UserId,
    input: &RestoreInput,
    result: &mut RestoreResult,
) -> Result<()> {
    for event in &input.events {
        let recurrence_rule_json = event
            .recurrence_rule
            .as_ref()
            .map(|r| serde_json::to_string(r).unwrap_or_default());
        let reminder_offsets_json = if event.reminder_offsets_seconds.is_empty() {
            None
        } else {
            Some(serde_json::to_string(&event.reminder_offsets_seconds).unwrap_or_default())
        };

        let affected = sqlx::query(
            "INSERT OR IGNORE INTO events (\
                id, user_id, project_id, title, description, start_time, end_time, location, \
                linked_task_id, recurrence, recurrence_rule, recurrence_parent_id, contact_id, \
                block_type, external_source, external_id, is_read_only, snoozed_until, reminder_offsets_seconds\
            ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)",
        )
        .bind(event.id.to_string())
        .bind(user_id.to_string())
        .bind(event.project_id.map(|p| p.to_string()))
        .bind(&event.title)
        .bind(&event.description)
        .bind(format_datetime(&event.start_time))
        .bind(format_datetime_opt(event.end_time))
        .bind(&event.location)
        .bind(event.linked_task_id.map(|t| t.to_string()))
        .bind(event.recurrence.db_value())
        .bind(&recurrence_rule_json)
        .bind(event.recurrence_parent_id.map(|p| p.to_string()))
        .bind(event.contact_id.map(|c| c.to_string()))
        .bind(event.block_type.as_ref().map(goingson_core::DbValue::db_value))
        .bind(&event.external_source)
        .bind(&event.external_id)
        .bind(i32::from(event.is_read_only))
        .bind(format_datetime_opt(event.snoozed_until))
        .bind(&reminder_offsets_json)
        .execute(&mut **tx)
        .await
        .map_err(CoreError::database)?
        .rows_affected();
        if affected > 0 {
            result.events_restored += 1;
        }
    }
    Ok(())
}

async fn restore_emails(
    tx: &mut Transaction<'_, Sqlite>,
    user_id: UserId,
    input: &RestoreInput,
    result: &mut RestoreResult,
) -> Result<()> {
    for email in &input.emails {
        // Dedupe by message_id when present (the same message can have a different
        // row id across devices); otherwise the preserved id + INSERT OR IGNORE
        // makes the restore idempotent.
        if let Some(msg_id) = &email.message_id {
            let existing: (i64,) =
                sqlx::query_as("SELECT COUNT(*) FROM emails WHERE user_id = ? AND message_id = ?")
                    .bind(user_id.to_string())
                    .bind(msg_id)
                    .fetch_one(&mut **tx)
                    .await
                    .map_err(CoreError::database)?;
            if existing.0 > 0 {
                continue;
            }
        }

        let labels_json = serde_json::to_string(&email.labels).unwrap_or_else(|_| "[]".to_string());
        let affected = sqlx::query(
            "INSERT OR IGNORE INTO emails (\
                id, user_id, project_id, from_address, to_address, subject, body, html_body, \
                is_read, is_archived, received_at, message_id, in_reply_to, thread_id, is_outgoing, \
                labels, is_draft, cc_address, bcc_address, snoozed_until, waiting_for_response, \
                waiting_since, expected_response_date\
            ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)",
        )
        .bind(email.id.to_string())
        .bind(user_id.to_string())
        .bind(email.project_id.map(|p| p.to_string()))
        .bind(&email.from)
        .bind(&email.to)
        .bind(&email.subject)
        .bind(&email.body)
        .bind(&email.html_body)
        .bind(i32::from(email.is_read))
        .bind(i32::from(email.is_archived))
        .bind(format_datetime(&email.received_at))
        .bind(&email.message_id)
        .bind(&email.in_reply_to)
        .bind(&email.thread_id)
        .bind(i32::from(email.is_outgoing))
        .bind(&labels_json)
        .bind(i32::from(email.is_draft))
        .bind(&email.cc_address)
        .bind(&email.bcc_address)
        .bind(format_datetime_opt(email.snoozed_until))
        .bind(i32::from(email.waiting_for_response))
        .bind(format_datetime_opt(email.waiting_since))
        .bind(format_datetime_opt(email.expected_response_date))
        .execute(&mut **tx)
        .await
        .map_err(CoreError::database)?
        .rows_affected();
        if affected > 0 {
            result.emails_restored += 1;
        }
    }
    Ok(())
}

async fn restore_contacts_with_children(
    tx: &mut Transaction<'_, Sqlite>,
    user_id: UserId,
    input: &RestoreInput,
    result: &mut RestoreResult,
) -> Result<()> {
    for contact in &input.contacts {
        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 affected = sqlx::query(
            "INSERT OR IGNORE INTO contacts (\
                id, user_id, display_name, nickname, company, title, notes, tags, birthday, \
                timezone, external_source, external_id, is_implicit, created_at, updated_at\
            ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)",
        )
        .bind(contact.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(&contact.external_source)
        .bind(&contact.external_id)
        .bind(i32::from(contact.is_implicit))
        .bind(format_datetime(&contact.created_at))
        .bind(format_datetime(&contact.updated_at))
        .execute(&mut **tx)
        .await
        .map_err(CoreError::database)?
        .rows_affected();

        if affected == 0 {
            continue;
        }
        result.contacts_restored += 1;

        // Sub-collections, verbatim with their preserved ids.
        for email in &contact.emails {
            sqlx::query(
                "INSERT OR IGNORE INTO contact_emails (id, contact_id, address, label, is_primary) \
                 VALUES (?, ?, ?, ?, ?)",
            )
            .bind(email.id.to_string())
            .bind(contact.id.to_string())
            .bind(&email.address)
            .bind(&email.label)
            .bind(i32::from(email.is_primary))
            .execute(&mut **tx)
            .await
            .map_err(CoreError::database)?;
        }
        for phone in &contact.phones {
            sqlx::query(
                "INSERT OR IGNORE INTO contact_phones (id, contact_id, number, label, is_primary) \
                 VALUES (?, ?, ?, ?, ?)",
            )
            .bind(phone.id.to_string())
            .bind(contact.id.to_string())
            .bind(&phone.number)
            .bind(&phone.label)
            .bind(i32::from(phone.is_primary))
            .execute(&mut **tx)
            .await
            .map_err(CoreError::database)?;
        }
        for handle in &contact.social_handles {
            sqlx::query(
                "INSERT OR IGNORE INTO contact_social_handles (id, contact_id, platform, handle, url) \
                 VALUES (?, ?, ?, ?, ?)",
            )
            .bind(handle.id.to_string())
            .bind(contact.id.to_string())
            .bind(&handle.platform)
            .bind(&handle.handle)
            .bind(&handle.url)
            .execute(&mut **tx)
            .await
            .map_err(CoreError::database)?;
        }
        for field in &contact.custom_fields {
            sqlx::query(
                "INSERT OR IGNORE INTO contact_custom_fields (id, contact_id, label, value, url) \
                 VALUES (?, ?, ?, ?, ?)",
            )
            .bind(field.id.to_string())
            .bind(contact.id.to_string())
            .bind(&field.label)
            .bind(&field.value)
            .bind(&field.url)
            .execute(&mut **tx)
            .await
            .map_err(CoreError::database)?;
        }
    }
    Ok(())
}

async fn restore_milestones(
    tx: &mut Transaction<'_, Sqlite>,
    user_id: UserId,
    input: &RestoreInput,
    result: &mut RestoreResult,
) -> Result<()> {
    for milestone in &input.milestones {
        let target_date = milestone
            .target_date
            .map(|d| d.format("%Y-%m-%d").to_string());
        let affected = sqlx::query(
            "INSERT OR IGNORE INTO milestones (\
                id, user_id, project_id, name, description, position, target_date, status, created_at\
            ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)",
        )
        .bind(milestone.id.to_string())
        .bind(user_id.to_string())
        .bind(milestone.project_id.to_string())
        .bind(&milestone.name)
        .bind(&milestone.description)
        .bind(milestone.position)
        .bind(&target_date)
        .bind(milestone.status.db_value())
        .bind(format_datetime(&milestone.created_at))
        .execute(&mut **tx)
        .await
        .map_err(CoreError::database)?
        .rows_affected();
        if affected > 0 {
            result.milestones_restored += 1;
        }
    }
    Ok(())
}

async fn restore_time_sessions(
    tx: &mut Transaction<'_, Sqlite>,
    user_id: UserId,
    input: &RestoreInput,
    result: &mut RestoreResult,
) -> Result<()> {
    for session in &input.time_sessions {
        let affected = sqlx::query(
            "INSERT OR IGNORE INTO time_sessions (\
                id, task_id, user_id, started_at, ended_at, duration_minutes, created_at\
            ) VALUES (?, ?, ?, ?, ?, ?, ?)",
        )
        .bind(session.id.to_string())
        .bind(session.task_id.to_string())
        .bind(user_id.to_string())
        .bind(format_datetime(&session.started_at))
        .bind(format_datetime_opt(session.ended_at))
        .bind(session.duration_minutes)
        .bind(format_datetime(&session.created_at))
        .execute(&mut **tx)
        .await
        .map_err(CoreError::database)?
        .rows_affected();
        if affected > 0 {
            result.time_sessions_restored += 1;
        }
    }
    Ok(())
}

async fn restore_attachments(
    tx: &mut Transaction<'_, Sqlite>,
    user_id: UserId,
    input: &RestoreInput,
    result: &mut RestoreResult,
) -> Result<()> {
    // Row metadata only; the content-addressed blob files are a separate store and
    // are re-fetched by blob sync, not embedded in the backup.
    for attachment in &input.attachments {
        let affected = sqlx::query(
            "INSERT OR IGNORE INTO attachments (\
                id, user_id, task_id, project_id, filename, file_size, mime_type, blob_hash, \
                source_email_id, created_at\
            ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)",
        )
        .bind(attachment.id.to_string())
        .bind(user_id.to_string())
        .bind(attachment.task_id.map(|t| t.to_string()))
        .bind(attachment.project_id.map(|p| p.to_string()))
        .bind(&attachment.filename)
        .bind(attachment.file_size)
        .bind(&attachment.mime_type)
        .bind(&attachment.blob_hash)
        .bind(attachment.source_email_id.map(|e| e.to_string()))
        .bind(format_datetime(&attachment.created_at))
        .execute(&mut **tx)
        .await
        .map_err(CoreError::database)?
        .rows_affected();
        if affected > 0 {
            result.attachments_restored += 1;
        }
    }
    Ok(())
}

async fn restore_daily_notes(
    tx: &mut Transaction<'_, Sqlite>,
    user_id: UserId,
    input: &RestoreInput,
    result: &mut RestoreResult,
) -> Result<()> {
    for note in &input.daily_notes {
        let affected = sqlx::query(
            "INSERT OR IGNORE INTO daily_notes (\
                id, user_id, note_date, went_well, could_improve, is_reviewed, reviewed_at, \
                created_at, updated_at\
            ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)",
        )
        .bind(note.id.to_string())
        .bind(user_id.to_string())
        .bind(note.note_date.format("%Y-%m-%d").to_string())
        .bind(&note.went_well)
        .bind(&note.could_improve)
        .bind(i32::from(note.is_reviewed))
        .bind(format_datetime_opt(note.reviewed_at))
        .bind(format_datetime(&note.created_at))
        .bind(format_datetime(&note.updated_at))
        .execute(&mut **tx)
        .await
        .map_err(CoreError::database)?
        .rows_affected();
        if affected > 0 {
            result.daily_notes_restored += 1;
        }
    }
    Ok(())
}

async fn restore_sync_accounts(
    tx: &mut Transaction<'_, Sqlite>,
    user_id: UserId,
    input: &RestoreInput,
    result: &mut RestoreResult,
) -> Result<()> {
    // The sync-transient last_*_sync timestamps are intentionally omitted (they are
    // re-derived on the next sync), matching the synced-column set.
    for account in &input.sync_accounts {
        let calendar_ids =
            serde_json::to_string(&account.calendar_ids).unwrap_or_else(|_| "[]".to_string());
        let affected = sqlx::query(
            "INSERT OR IGNORE INTO sync_accounts (\
                id, user_id, provider, account_name, email, sync_calendars, sync_contacts, \
                calendar_ids, sync_interval_minutes, enabled, created_at\
            ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)",
        )
        .bind(account.id.to_string())
        .bind(user_id.to_string())
        .bind(&account.provider)
        .bind(&account.account_name)
        .bind(&account.email)
        .bind(i32::from(account.sync_calendars))
        .bind(i32::from(account.sync_contacts))
        .bind(&calendar_ids)
        .bind(account.sync_interval_minutes)
        .bind(i32::from(account.enabled))
        .bind(format_datetime(&account.created_at))
        .execute(&mut **tx)
        .await
        .map_err(CoreError::database)?
        .rows_affected();
        if affected > 0 {
            result.sync_accounts_restored += 1;
        }
    }
    Ok(())
}

async fn restore_saved_views(
    tx: &mut Transaction<'_, Sqlite>,
    user_id: UserId,
    input: &RestoreInput,
    result: &mut RestoreResult,
) -> Result<()> {
    for view in &input.saved_views {
        let filters_json =
            serde_json::to_string(&view.filters).unwrap_or_else(|_| "{}".to_string());
        let sort_by = view.sort_by.and_then(|sf| {
            serde_json::to_value(sf)
                .ok()
                .and_then(|v| v.as_str().map(String::from))
        });
        let sort_order = match view.sort_order {
            SortDirection::Desc => "desc",
            SortDirection::Asc => "asc",
        };
        let affected = sqlx::query(
            "INSERT OR IGNORE INTO saved_views (\
                id, user_id, name, view_type, filters, sort_by, sort_order, is_pinned, position, \
                created_at, updated_at\
            ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)",
        )
        .bind(view.id.to_string())
        .bind(user_id.to_string())
        .bind(&view.name)
        .bind(view.view_type.db_value())
        .bind(&filters_json)
        .bind(&sort_by)
        .bind(sort_order)
        .bind(i32::from(view.is_pinned))
        .bind(view.position)
        .bind(format_datetime(&view.created_at))
        .bind(format_datetime(&view.updated_at))
        .execute(&mut **tx)
        .await
        .map_err(CoreError::database)?
        .rows_affected();
        if affected > 0 {
            result.saved_views_restored += 1;
        }
    }
    Ok(())
}

async fn restore_weekly_reviews(
    tx: &mut Transaction<'_, Sqlite>,
    user_id: UserId,
    input: &RestoreInput,
    result: &mut RestoreResult,
) -> Result<()> {
    for review in &input.weekly_reviews {
        // Restore vacation_days verbatim. Earlier code silently dropped any value > 6,
        // which violates this module's "every row inserted exactly as backed up" contract
        // (ultra-fuzz Run #28 MINOR): a backup is a faithful snapshot, not a place to
        // re-validate. The 0-6 weekday domain is enforced on the write path, not here.
        let vacation_days = review
            .vacation_days
            .iter()
            .map(std::string::ToString::to_string)
            .collect::<Vec<_>>()
            .join(",");
        let affected = sqlx::query(
            "INSERT OR IGNORE INTO weekly_reviews (\
                id, user_id, week_start_date, completed_at, notes, vacation_days\
            ) VALUES (?, ?, ?, ?, ?, ?)",
        )
        .bind(review.id.to_string())
        .bind(user_id.to_string())
        .bind(review.week_start_date.format("%Y-%m-%d").to_string())
        .bind(format_datetime(&review.completed_at))
        .bind(&review.notes)
        .bind(&vacation_days)
        .execute(&mut **tx)
        .await
        .map_err(CoreError::database)?
        .rows_affected();
        if affected > 0 {
            result.weekly_reviews_restored += 1;
        }
    }
    Ok(())
}

async fn restore_monthly_goals(
    tx: &mut Transaction<'_, Sqlite>,
    user_id: UserId,
    input: &RestoreInput,
    result: &mut RestoreResult,
) -> Result<()> {
    for goal in &input.monthly_goals {
        let affected = sqlx::query(
            "INSERT OR IGNORE INTO monthly_goals (\
                id, user_id, month, text, status, position, created_at, updated_at\
            ) VALUES (?, ?, ?, ?, ?, ?, ?, ?)",
        )
        .bind(goal.id.to_string())
        .bind(user_id.to_string())
        .bind(&goal.month)
        .bind(&goal.text)
        .bind(goal.status.as_str())
        .bind(goal.position)
        .bind(format_datetime(&goal.created_at))
        .bind(format_datetime(&goal.updated_at))
        .execute(&mut **tx)
        .await
        .map_err(CoreError::database)?
        .rows_affected();
        if affected > 0 {
            result.monthly_goals_restored += 1;
        }
    }
    Ok(())
}

async fn restore_monthly_reflections(
    tx: &mut Transaction<'_, Sqlite>,
    user_id: UserId,
    input: &RestoreInput,
    result: &mut RestoreResult,
) -> Result<()> {
    for reflection in &input.monthly_reflections {
        let affected = sqlx::query(
            "INSERT OR IGNORE INTO monthly_reflections (\
                id, user_id, month, highlight_text, change_text, completed_at\
            ) VALUES (?, ?, ?, ?, ?, ?)",
        )
        .bind(reflection.id.to_string())
        .bind(user_id.to_string())
        .bind(&reflection.month)
        .bind(&reflection.highlight_text)
        .bind(&reflection.change_text)
        .bind(format_datetime(&reflection.completed_at))
        .execute(&mut **tx)
        .await
        .map_err(CoreError::database)?
        .rows_affected();
        if affected > 0 {
            result.monthly_reflections_restored += 1;
        }
    }
    Ok(())
}