//! Backup round-trip fidelity: export then restore must lose nothing.
//!
//! `backup_roundtrip_preserves_every_column` is the structural guard for the
//! "restore silently omitted column X" finding class. The sibling table-level
//! guard (`schema_tables_are_all_classified` in `backup_completeness_tests.rs`)
//! fixed the *table* case and has held since, but it was then treated as closing
//! the whole class, and it does not look at columns. Migrations 059 (`group_id`),
//! 062 (`tasks.title`) and 063 (the event timezone model) each shipped without
//! touching `restore.rs` and nothing failed, producing four live data losses
//! through the one path a user reaches when they have already lost data.
//!
//! The guard here is deliberately not a second registry. A `RESTORE_COLUMNS`
//! list naming what each INSERT writes would be a second copy of the truth, free
//! to drift from the statements it claims to describe. Instead this seeds one
//! fully-populated row per table in `BACKUP_TABLES` (every column set to a
//! distinctive non-default value), runs the real export path, restores into a
//! fresh database, and compares every column of every row. A migration that adds
//! a column to a backed-up table fails here until `restore_all` carries it,
//! because the seeded value has nowhere to survive. That also catches the
//! neighbouring classes a registry cannot see: datetime formatting, JSON
//! encoding, and type coercion drift.

mod common;

use goingson_core::ContextRepository as _;
use goingson_core::backup_restore::RestoreInput;
use goingson_core::{
    AttachmentRepository, ContactRepository, DailyNoteRepository, EmailRepository, EventRepository,
    MilestoneRepository, MonthlyReviewRepository, ProblemFilter, ProblemRepository,
    ProjectRepository, SavedViewRepository, SyncAccountRepository, TaskCrud, TaskDependencies,
    TaskTimeTracking, UserId, WeeklyReviewRepository,
};
use goingson_db_sqlite::Db;
use goingson_db_sqlite::{
    BACKUP_TABLES, EXCLUDED_BACKUP_COLUMNS, SqliteAttachmentRepository, SqliteContactRepository,
    SqliteContextRepository, SqliteDailyNoteRepository, SqliteEmailRepository,
    SqliteEventRepository, SqliteMilestoneRepository, SqliteMonthlyReviewRepository,
    SqliteProblemRepository, SqliteProjectRepository, SqliteSavedViewRepository,
    SqliteSyncAccountRepository, SqliteTaskRepository, SqliteWeeklyReviewRepository, restore_all,
};
use rusqlite::params;

/// Fixed ids so the seed can wire real foreign keys without a lookup pass.
const PROJECT_ID: &str = "11111111-1111-4111-8111-111111111111";
const TASK_ID: &str = "22222222-2222-4222-8222-222222222222";
const EVENT_ID: &str = "33333333-3333-4333-8333-333333333333";
const EMAIL_ID: &str = "44444444-4444-4444-8444-444444444444";
const CONTACT_ID: &str = "55555555-5555-4555-8555-555555555555";
const MILESTONE_ID: &str = "66666666-6666-4666-8666-666666666666";
const GROUP_ID: &str = "77777777-7777-4777-8777-777777777777";
/// The second endpoint of the seeded dependency edge. `task_dependencies` has a
/// CHECK forbidding a self edge, so this table is the one that cannot be seeded
/// from the single `TASK_ID` every other task child hangs off.
const BLOCKER_TASK_ID: &str = "22222222-2222-4222-8222-222222222223";

/// One row per backed-up table, every column carrying a distinctive non-default
/// value. Raw SQL rather than the repository builders on purpose: the builders
/// only reach the columns they happen to expose, and a column no API sets is
/// exactly the kind this guard exists to catch.
fn seed_every_column(db: &Db, user_id: UserId) {
    let u = user_id.to_string();

    // Parents first — the seed runs with foreign keys on.
    db.conn().unwrap().execute(// status_changed_at is deliberately later than created_at: it is the
        // shelving instant a dormant project's problems anchor to, so a restore
        // that collapsed it back onto created_at would silently rescore them.
        "INSERT INTO projects (id, name, description, project_type, status, created_at, status_changed_at, user_id, group_id)
         VALUES (?, 'Seeded project', 'project description', 'SideProject', 'OnHold', '2026-01-02 03:04:05', '2026-03-04 05:06:07', ?, ?)", params![PROJECT_ID, &u, GROUP_ID]).expect("seed projects");

    db.conn().unwrap().execute("INSERT INTO contacts (id, user_id, display_name, nickname, company, title, notes, tags,
             birthday, timezone, created_at, updated_at, external_source, external_id, is_implicit)
         VALUES (?, ?, 'Seeded Contact', 'Seed', 'Acme', 'Principal', 'contact notes', '[\"vip\"]',
             '1990-04-05', 'Europe/Berlin', '2026-01-02 03:04:05', '2026-01-03 03:04:05',
             'carddav', 'ext-contact-1', 1)", params![CONTACT_ID, &u])
    .expect("seed contacts");

    db.conn().unwrap().execute("INSERT INTO milestones (id, user_id, project_id, name, description, position, target_date,
             status, created_at, group_id)
         VALUES (?, ?, ?, 'Seeded milestone', 'milestone description', 7, '2026-06-01',
             'open', '2026-01-02 03:04:05', ?)", params![MILESTONE_ID, &u, PROJECT_ID, GROUP_ID])
    .expect("seed milestones");

    db.conn()
        .unwrap()
        .execute(
            "INSERT INTO emails (id, project_id, from_address, to_address, subject, body, is_read,
             received_at, user_id, message_id, email_account_id, is_outgoing, is_archived, imap_uid,
             source_folder, snoozed_until, waiting_for_response, waiting_since,
             expected_response_date, in_reply_to, thread_id, html_body, attachment_meta, is_draft,
             cc_address, bcc_address, draft_account_id, labels, jmap_id, body_truncated)
         VALUES (?, ?, 'from@example.com', 'to@example.com', 'Seeded subject', 'plain body', 1,
             '2026-01-02 03:04:05', ?, '<msg-1@example.com>', NULL, 1, 1, 4242,
             'INBOX/Seeded', '2026-02-01 00:00:00', 1, '2026-01-04 00:00:00',
             '2026-01-09 00:00:00', '<parent@example.com>', 'thread-1', '<p>html body</p>',
             '[{\"filename\":\"a.pdf\",\"blob_hash\":\"abc123\"}]', 0,
             'cc@example.com', 'bcc@example.com', NULL, '[\"important\"]', 'jmap-1', 1)",
            params![EMAIL_ID, PROJECT_ID, &u],
        )
        .expect("seed emails");

    db.conn().unwrap().execute("INSERT INTO tasks (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, recurrence_rule, group_id, title)
         VALUES (?, ?, 'task description', 'Pending', 'High', '2026-05-05 12:00:00', '[\"seed\"]',
             3.5, 'Monthly', '2026-01-02 03:04:05', ?, NULL, ?, '2026-03-01 00:00:00',
             1, '2026-01-05 00:00:00', '2026-01-10 00:00:00', '2026-04-01 09:00:00',
             90, 1, '2026-01-06 00:00:00', ?, ?, NULL,
             120, 45, '{\"pattern\":\"Monthly\",\"interval\":2,\"weekdays\":[],\"monthlySpec\":null}', ?, 'Seeded task title')", params![TASK_ID, PROJECT_ID, &u, EMAIL_ID, CONTACT_ID, MILESTONE_ID, GROUP_ID]).expect("seed tasks");

    db.conn()
        .unwrap()
        .execute(
            "INSERT INTO tasks (id, description, title, status, priority, tags, urgency, recurrence,
                 created_at, user_id, actual_minutes)
             VALUES (?, 'blocker description', 'Seeded blocker task', 'Pending', 'Medium', '[]',
                 1.0, 'None', '2026-01-02 03:04:05', ?, 0)",
            params![BLOCKER_TASK_ID, &u],
        )
        .expect("seed blocker task");

    db.conn()
        .unwrap()
        .execute(
            "INSERT INTO task_dependencies (id, blocked_id, blocker_id, created_at, group_id)
             VALUES ('88888888-8888-4888-8888-888888888884', ?, ?, '2026-01-02 03:04:05', ?)",
            params![TASK_ID, BLOCKER_TASK_ID, GROUP_ID],
        )
        .expect("seed task_dependencies");

    db.conn().unwrap().execute("INSERT INTO events (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, recurrence_rule, snoozed_until,
             reminder_offsets_seconds, group_id, tz_kind, timezone, start_local, end_local)
         VALUES (?, ?, 'Seeded event', 'event description', '2026-07-01 06:00:00',
             '2026-07-01 07:00:00', 'Studio', ?, ?, 'Weekly', NULL, ?, 'focus',
             'caldav', 'ext-event-1', 1, '{\"pattern\":\"Weekly\",\"interval\":3,\"weekdays\":[0,2],\"monthlySpec\":null}',
             '2026-06-01 00:00:00', '[600,1800]', ?, 'relative', 'Europe/Berlin',
             '2026-07-01 08:00:00', '2026-07-01 09:00:00')", params![EVENT_ID, PROJECT_ID, &u, TASK_ID, CONTACT_ID, GROUP_ID]).expect("seed events");

    // Task children.
    db.conn().unwrap().execute("INSERT INTO subtasks (id, task_id, text, is_completed, position, created_at, linked_task_id, group_id)
         VALUES ('88888888-8888-4888-8888-888888888881', ?, 'seeded subtask', 1, 3,
             '2026-01-02 03:04:05', NULL, ?)", params![TASK_ID, GROUP_ID]).expect("seed subtasks");

    db.conn().unwrap().execute("INSERT INTO annotations (id, task_id, timestamp, note, group_id)
         VALUES ('88888888-8888-4888-8888-888888888882', ?, '2026-01-02 03:04:05', 'seeded note', ?)", params![TASK_ID, GROUP_ID]).expect("seed annotations");

    db.conn().unwrap().execute("INSERT INTO task_status_tokens (id, task_id, kind, reference, state, is_primary, position, created_at, group_id)
         VALUES ('88888888-8888-4888-8888-888888888883', ?, 'commit', 'go@abc1234', 'Pending', 1, 2,
             '2026-01-02 03:04:05', ?)", params![TASK_ID, GROUP_ID]).expect("seed task_status_tokens");

    db.conn().unwrap().execute("INSERT INTO time_sessions (id, task_id, user_id, started_at, ended_at, duration_minutes, created_at, group_id)
         VALUES ('88888888-8888-4888-8888-888888888884', ?, ?, '2026-01-02 03:04:05',
             '2026-01-02 04:04:05', 60, '2026-01-02 03:04:05', ?)", params![TASK_ID, &u, GROUP_ID]).expect("seed time_sessions");

    db.conn().unwrap().execute("INSERT INTO attachments (id, user_id, task_id, project_id, filename, file_size, mime_type,
             blob_hash, source_email_id, created_at, group_id)
         VALUES ('88888888-8888-4888-8888-888888888885', ?, ?, ?, 'seeded.pdf', 12345,
             'application/pdf', 'a1b2c3d4e5f60718293a4b5c6d7e8f90a1b2c3d4e5f60718293a4b5c6d7e8f90',
             ?, '2026-01-02 03:04:05', ?)", params![&u, TASK_ID, PROJECT_ID, EMAIL_ID, GROUP_ID])
    .expect("seed attachments");

    // Contact children.
    db.conn()
        .unwrap()
        .execute(
            "INSERT INTO contact_emails (id, contact_id, address, label, is_primary)
         VALUES ('99999999-9999-4999-8999-999999999991', ?, 'primary@example.com', 'work', 1)",
            params![CONTACT_ID],
        )
        .expect("seed contact_emails");

    db.conn()
        .unwrap()
        .execute(
            "INSERT INTO contact_phones (id, contact_id, number, label, is_primary)
         VALUES ('99999999-9999-4999-8999-999999999992', ?, '+15550001111', 'mobile', 1)",
            params![CONTACT_ID],
        )
        .expect("seed contact_phones");

    db.conn().unwrap().execute("INSERT INTO contact_social_handles (id, contact_id, platform, handle, url)
         VALUES ('99999999-9999-4999-8999-999999999993', ?, 'mastodon', '@seed', 'https://example.com/@seed')", params![CONTACT_ID]).expect("seed contact_social_handles");

    db.conn().unwrap().execute("INSERT INTO contact_custom_fields (id, contact_id, label, value, url)
         VALUES ('99999999-9999-4999-8999-999999999994', ?, 'Website', 'example.com', 'https://example.com')", params![CONTACT_ID]).expect("seed contact_custom_fields");

    // Standalone user-scoped tables.
    db.conn()
        .unwrap()
        .execute(
            "INSERT INTO contexts (id, user_id, label, kind, starts_on, ends_on,
             created_at, updated_at)
         VALUES ('aaaaaaaa-aaaa-4aaa-8aaa-aaaaaaaaaaa5', ?, 'Seeded leave', 'Vacation',
             '2026-01-05', '2026-01-09', '2026-01-02 03:04:05', '2026-01-02 03:04:05')",
            params![&u],
        )
        .expect("seed contexts");

    db.conn().unwrap().execute("INSERT INTO daily_notes (id, user_id, note_date, went_well, could_improve, is_reviewed,
             reviewed_at, created_at, updated_at)
         VALUES ('aaaaaaaa-aaaa-4aaa-8aaa-aaaaaaaaaaa1', ?, '2026-01-02', 'went well text',
             'could improve text', 1, '2026-01-03 00:00:00', '2026-01-02 03:04:05', '2026-01-03 03:04:05')", params![&u]).expect("seed daily_notes");

    db.conn()
        .unwrap()
        .execute(
            "INSERT INTO saved_views (id, user_id, name, view_type, filters, sort_by, sort_order,
             is_pinned, position, created_at, updated_at)
         VALUES ('aaaaaaaa-aaaa-4aaa-8aaa-aaaaaaaaaaa2', ?, 'Seeded view', 'tasks',
             '{\"status\":[\"Pending\"]}', 'due', 'asc', 1, 4,
             '2026-01-02 03:04:05', '2026-01-03 03:04:05')",
            params![&u],
        )
        .expect("seed saved_views");

    db.conn().unwrap().execute("INSERT INTO weekly_reviews (id, user_id, week_start_date, completed_at, notes, vacation_days)
         VALUES ('aaaaaaaa-aaaa-4aaa-8aaa-aaaaaaaaaaa3', ?, '2026-01-05', '2026-01-11 18:00:00',
             'weekly notes', 2)", params![&u]).expect("seed weekly_reviews");

    db.conn().unwrap().execute("INSERT INTO monthly_goals (id, user_id, month, text, status, position, created_at, updated_at)
         VALUES ('aaaaaaaa-aaaa-4aaa-8aaa-aaaaaaaaaaa4', ?, '2026-01', 'goal text', 'active', 5,
             '2026-01-02 03:04:05', '2026-01-03 03:04:05')", params![&u]).expect("seed monthly_goals");

    db.conn().unwrap().execute("INSERT INTO monthly_reflections (id, user_id, month, highlight_text, change_text, completed_at)
         VALUES ('aaaaaaaa-aaaa-4aaa-8aaa-aaaaaaaaaaa5', ?, '2026-01', 'highlight text',
             'change text', '2026-02-01 00:00:00')", params![&u]).expect("seed monthly_reflections");

    db.conn().unwrap().execute("INSERT INTO problems (id, user_id, source, source_ref, title, body, pain, scale, status,
             project_id, tags, promoted_task_id, created_at, updated_at, settled_at, last_seen_at)
         VALUES ('aaaaaaaa-aaaa-4aaa-8aaa-aaaaaaaaaaa6', ?, 'audit', 'go:seed:ref', 'Seeded problem',
             'problem body', 4, 3, 'Open', ?, '[\"seed\"]', NULL,
             '2026-01-02 03:04:05', '2026-01-03 03:04:05', NULL, '2026-01-04 03:04:05')", params![&u, PROJECT_ID]).expect("seed problems");

    db.conn()
        .unwrap()
        .execute(
            "INSERT INTO sync_accounts (id, user_id, provider, account_name, email, sync_calendars,
             sync_contacts, calendar_ids, last_calendar_sync, last_contact_sync,
             sync_interval_minutes, enabled, created_at)
         VALUES ('aaaaaaaa-aaaa-4aaa-8aaa-aaaaaaaaaaa7', ?, 'google', 'Seeded account',
             'sync@example.com', 1, 1, '[\"cal-1\"]', '2026-01-03 00:00:00', '2026-01-04 00:00:00',
             30, 1, '2026-01-02 03:04:05')",
            params![&u],
        )
        .expect("seed sync_accounts");
}

/// The production export path, assembled exactly as `collect_full_export` does in
/// `src-tauri/src/backup_scheduler.rs`. Kept in the same shape so an export-side
/// filter (the kind that hid draft emails) shows up here as a missing row.
fn export_all(db: &Db, user_id: UserId) -> RestoreInput {
    let tasks_repo = SqliteTaskRepository::new(db.clone());
    let monthly = SqliteMonthlyReviewRepository::new(db.clone());
    RestoreInput {
        projects: SqliteProjectRepository::new(db.clone())
            .list_all(user_id)
            .unwrap(),
        tasks: tasks_repo.list_all_for_backup(user_id).unwrap(),
        events: SqliteEventRepository::new(db.clone())
            .list_all(user_id)
            .unwrap(),
        emails: SqliteEmailRepository::new(db.clone())
            .list_all_for_backup(user_id)
            .unwrap(),
        contacts: SqliteContactRepository::new(db.clone())
            .list_all_for_backup(user_id)
            .unwrap(),
        time_sessions: tasks_repo.list_all_time_sessions(user_id).unwrap(),
        milestones: SqliteMilestoneRepository::new(db.clone())
            .list_all(user_id)
            .unwrap(),
        contexts: SqliteContextRepository::new(db.clone())
            .list_all(user_id)
            .unwrap(),
        daily_notes: SqliteDailyNoteRepository::new(db.clone())
            .list_all(user_id)
            .unwrap(),
        attachments: SqliteAttachmentRepository::new(db.clone())
            .list_all(user_id)
            .unwrap(),
        sync_accounts: SqliteSyncAccountRepository::new(db.clone())
            .list_all(user_id)
            .unwrap(),
        saved_views: SqliteSavedViewRepository::new(db.clone())
            .list_all(user_id)
            .unwrap(),
        weekly_reviews: SqliteWeeklyReviewRepository::new(db.clone())
            .list_all(user_id)
            .unwrap(),
        monthly_goals: monthly.list_all_goals(user_id).unwrap(),
        monthly_reflections: monthly.list_all_reflections(user_id).unwrap(),
        problems: SqliteProblemRepository::new(db.clone())
            .list(user_id, &ProblemFilter::default())
            .unwrap(),
        dependencies: tasks_repo.list_all_dependencies(user_id).unwrap(),
    }
}

/// Every column of `table`, read as TEXT so one comparator covers every affinity.
fn columns_of(db: &Db, table: &str) -> Vec<String> {
    let conn = db.conn().unwrap();
    let mut stmt = conn
        .prepare(&format!("SELECT name FROM pragma_table_info('{table}')"))
        .expect("pragma table_info");
    let names = stmt
        .query_map([], |row| row.get::<_, String>("name"))
        .expect("pragma table_info");
    names.map(Result::unwrap).collect()
}

/// All rows of `table` as (id, column, value) triples, ordered by id.
fn rows_of(db: &Db, table: &str, cols: &[String]) -> Vec<(String, Vec<(String, Option<String>)>)> {
    let select = cols
        .iter()
        .map(|c| format!("CAST(\"{c}\" AS TEXT) AS \"{c}\""))
        .collect::<Vec<_>>()
        .join(", ");
    let conn = db.conn().unwrap();
    let mut stmt = conn
        .prepare(&format!("SELECT {select} FROM \"{table}\" ORDER BY id"))
        .unwrap_or_else(|e| panic!("select from {table}: {e}"));
    let rows = stmt
        .query_map([], |r| {
            let id: Option<String> = r.get("id")?;
            let values = cols
                .iter()
                .map(|c| Ok((c.clone(), r.get::<_, Option<String>>(c.as_str())?)))
                .collect::<rusqlite::Result<Vec<_>>>()?;
            Ok((id.unwrap_or_default(), values))
        })
        .unwrap_or_else(|e| panic!("select from {table}: {e}"));
    rows.map(Result::unwrap).collect()
}

#[test]
fn backup_roundtrip_preserves_every_column() {
    let src = common::setup_test_db();
    let user_id = common::create_test_user(&src);
    seed_every_column(&src, user_id);

    let input = export_all(&src, user_id);

    // Restore into a fresh database carrying only the same user row, so every
    // other value present afterwards arrived through the backup.
    let dst = common::setup_test_db();
    dst.conn()
        .unwrap()
        .execute(
            "INSERT INTO users (id, email, password_hash, display_name, created_at)
             VALUES (?, ?, 'x', 'Restored User', '2026-01-01 00:00:00')",
            params![user_id.to_string(), format!("u-{user_id}@example.com")],
        )
        .expect("recreate user in destination");

    restore_all(&mut dst.conn().unwrap(), user_id, &input).expect("restore must succeed");

    let mut failures: Vec<String> = Vec::new();
    for table in BACKUP_TABLES {
        let cols = columns_of(&src, table);
        let before = rows_of(&src, table, &cols);
        let after = rows_of(&dst, table, &cols);

        if before.len() != after.len() {
            failures.push(format!(
                "{table}: {} row(s) exported, {} restored — the export query drops rows the \
                 backup is supposed to carry",
                before.len(),
                after.len()
            ));
            continue;
        }

        for ((id, src_row), (_, dst_row)) in before.iter().zip(after.iter()) {
            for ((col, src_val), (_, dst_val)) in src_row.iter().zip(dst_row.iter()) {
                let excluded = EXCLUDED_BACKUP_COLUMNS
                    .iter()
                    .any(|(t, c, _)| t == table && c == col);
                if !excluded && src_val != dst_val {
                    failures.push(format!(
                        "{table}.{col} (row {id}): {src_val:?} before restore, {dst_val:?} after"
                    ));
                }
            }
        }
    }

    assert!(
        failures.is_empty(),
        "backup round-trip lost data. Each line is a column the export carries and \
         `restore_all` discards, or a row the export never collected:\n  {}",
        failures.join("\n  ")
    );
}

/// A typo in the exclusion register would silently excuse a column that does not
/// exist while the real one goes unguarded, so pin the entries to the schema.
#[test]
fn excluded_backup_columns_are_real_columns() {
    let db = common::setup_test_db();
    for (table, column, reason) in EXCLUDED_BACKUP_COLUMNS {
        assert!(
            BACKUP_TABLES.contains(table),
            "EXCLUDED_BACKUP_COLUMNS names table `{table}`, which is not in BACKUP_TABLES"
        );
        let cols = columns_of(&db, table);
        assert!(
            cols.iter().any(|c| c == column),
            "EXCLUDED_BACKUP_COLUMNS excuses `{table}.{column}`, which is not a real column"
        );
        assert!(
            !reason.trim().is_empty(),
            "`{table}.{column}` is excluded from backups with no stated reason"
        );
    }
}