PRAGMA busy_timeout = 10000;

BEGIN IMMEDIATE;

WITH RECURSIVE seq(n) AS (
    SELECT 1
    UNION ALL
    SELECT n + 1 FROM seq WHERE n < 1200
)
INSERT INTO users (
    id, email, password_hash, role, status, created_at, updated_at, deleted_at
)
SELECT
    printf('scale-user-%04d', n),
    printf('scale.user.%04d@example.test', n),
    'disabled-scale-account',
    'user',
    CASE
        WHEN n % 29 = 0 THEN 'banned'
        WHEN n % 17 = 0 THEN 'suspended'
        ELSE 'active'
    END,
    datetime('now', printf('-%d hours', n % 2160)),
    datetime('now', printf('-%d hours', n % 72)),
    NULL
FROM seq
WHERE NOT EXISTS (
    SELECT 1 FROM users WHERE id = printf('scale-user-%04d', n)
);

WITH RECURSIVE seq(n) AS (
    SELECT 1
    UNION ALL
    SELECT n + 1 FROM seq WHERE n < 1200
)
INSERT INTO user_profiles (
    user_id, display_name, avatar_url, bio, created_at, updated_at
)
SELECT
    printf('scale-user-%04d', n),
    printf('Scale User %04d', n),
    NULL,
    'Governance scale test account',
    datetime('now', printf('-%d hours', n % 2160)),
    datetime('now')
FROM seq
WHERE NOT EXISTS (
    SELECT 1
    FROM user_profiles
    WHERE user_id = printf('scale-user-%04d', n)
);

WITH RECURSIVE seq(n) AS (
    SELECT 1
    UNION ALL
    SELECT n + 1 FROM seq WHERE n < 1200
)
INSERT INTO credit_accounts (
    user_id, balance, version, total_granted, total_consumed, updated_at
)
SELECT
    printf('scale-user-%04d', n),
    (n * 7) % 120,
    1,
    30 + (n % 40),
    n % 25,
    datetime('now', printf('-%d minutes', n % 1440))
FROM seq
WHERE NOT EXISTS (
    SELECT 1
    FROM credit_accounts
    WHERE user_id = printf('scale-user-%04d', n)
);

WITH RECURSIVE seq(n) AS (
    SELECT 1
    UNION ALL
    SELECT n + 1 FROM seq WHERE n < 1200
)
INSERT INTO mailboxes (
    user_id, address, local_part, domain, access_token_hash, note, status,
    max_quota_bytes, used_bytes, expires_at, created_at, updated_at
)
SELECT
    printf('scale-user-%04d', n),
    printf('scale-box-%04d@mail.shenjianl.cn', n),
    printf('scale-box-%04d', n),
    'mail.shenjianl.cn',
    printf('disabled-scale-token-%04d', n),
    CASE n % 4
        WHEN 0 THEN 'Automation'
        WHEN 1 THEN 'Registration'
        WHEN 2 THEN 'Monitoring'
        ELSE 'QA inbox'
    END,
    CASE
        WHEN n % 19 = 0 THEN 'revoked'
        WHEN n % 13 = 0 THEN 'expired'
        ELSE 'active'
    END,
    10485760,
    0,
    datetime(
        'now',
        CASE
            WHEN n % 13 = 0 THEN printf('-%d hours', (n % 48) + 1)
            ELSE printf('+%d hours', (n % 168) + 1)
        END
    ),
    datetime('now', printf('-%d hours', n % 720)),
    datetime('now', printf('-%d minutes', n % 1440))
FROM seq
WHERE NOT EXISTS (
    SELECT 1
    FROM mailboxes
    WHERE address = printf('scale-box-%04d@mail.shenjianl.cn', n)
);

WITH RECURSIVE seq(n) AS (
    SELECT 1
    UNION ALL
    SELECT n + 1 FROM seq WHERE n < 3000
)
INSERT INTO emails (
    mailbox_id, user_id, recipient, mail_from, rcpt_to, sender_name, subject,
    text_body, html_body, source_ip, helo, ptr_result, spf_result, dkim_result,
    dmarc_result, abuse_score, status, size_bytes, message_id, received_at,
    expires_at
)
SELECT
    mailbox.id,
    mailbox.user_id,
    mailbox.address,
    printf('sender%03d@scale-domain-%02d.test', n % 240, n % 36),
    mailbox.address,
    printf('Scale Sender %03d', n % 240),
    CASE n % 6
        WHEN 0 THEN 'Account verification'
        WHEN 1 THEN 'Build pipeline result'
        WHEN 2 THEN 'Security notification'
        WHEN 3 THEN 'Weekly account digest'
        WHEN 4 THEN 'Service status update'
        ELSE 'One-time access code'
    END,
    printf('Scale test email %04d for pagination and governance.', n),
    printf(
        '<p>Scale test email <strong>%04d</strong>.</p><p>Safe local fixture.</p>',
        n
    ),
    printf('198.51.%d.%d', (n / 250) % 250, (n % 250) + 1),
    printf('mx%02d.scale-sender.test', n % 24),
    CASE WHEN n % 11 = 0 THEN 'missing' ELSE 'pass' END,
    CASE WHEN n % 9 = 0 THEN 'fail' ELSE 'pass' END,
    CASE WHEN n % 8 = 0 THEN 'none' ELSE 'pass' END,
    CASE WHEN n % 10 = 0 THEN 'fail' ELSE 'pass' END,
    (n * 13) % 101,
    CASE WHEN n % 7 = 0 THEN 'quarantined' ELSE 'received' END,
    8192 + ((n * 791) % 65536),
    printf('<scale-%05d@seed.local>', n),
    datetime('now', printf('-%d hours', n % 720)),
    datetime('now', printf('-%d hours', n % 720), '+7 days')
FROM seq
JOIN mailboxes AS mailbox
  ON mailbox.address = printf(
      'scale-box-%04d@mail.shenjianl.cn',
      ((n - 1) % 1200) + 1
  )
WHERE NOT EXISTS (
    SELECT 1
    FROM emails
    WHERE message_id = printf('<scale-%05d@seed.local>', n)
);

INSERT INTO email_attachments (
    email_id, filename, content_type, size_bytes, content, created_at
)
SELECT
    email.id,
    printf('scale-report-%05d.txt', email.id),
    'text/plain',
    2048,
    CAST('Scale fixture' AS BLOB),
    email.received_at
FROM emails AS email
WHERE email.message_id LIKE '<scale-%@seed.local>'
  AND email.id % 10 = 0
  AND NOT EXISTS (
      SELECT 1
      FROM email_attachments AS attachment
      WHERE attachment.email_id = email.id
        AND attachment.filename = printf('scale-report-%05d.txt', email.id)
  );

UPDATE mailboxes
SET used_bytes = COALESCE(
    (
        SELECT SUM(email.size_bytes)
        FROM emails AS email
        WHERE email.mailbox_id = mailboxes.id
    ),
    0
)
WHERE address LIKE 'scale-box-%@mail.shenjianl.cn';

WITH RECURSIVE seq(n) AS (
    SELECT 1
    UNION ALL
    SELECT n + 1 FROM seq WHERE n < 2000
)
INSERT INTO credit_transactions (
    user_id, delta, balance_after, reason, related_mailbox_id,
    related_email_id, description, operator_id, created_at
)
SELECT
    printf('scale-user-%04d', ((n - 1) % 1200) + 1),
    CASE WHEN n % 5 = 0 THEN 3 ELSE -1 END,
    (n * 7) % 120,
    CASE
        WHEN n % 5 = 0 THEN 'daily_check_in'
        WHEN n % 3 = 0 THEN 'create_mailbox'
        ELSE 'receive_email'
    END,
    NULL,
    NULL,
    printf('Scale transaction %05d', n),
    NULL,
    datetime('now', printf('-%d hours', n % 720))
FROM seq
WHERE NOT EXISTS (
    SELECT 1
    FROM credit_transactions
    WHERE description = printf('Scale transaction %05d', n)
);

WITH RECURSIVE seq(n) AS (
    SELECT 1
    UNION ALL
    SELECT n + 1 FROM seq WHERE n < 1500
)
INSERT INTO audit_logs (
    event_type, source_ip, helo, mail_from, rcpt_to, action, reason,
    operator_id, created_at, target_type, target_id, metadata_json
)
SELECT
    CASE n % 4
        WHEN 0 THEN 'smtp'
        WHEN 1 THEN 'email_governance'
        WHEN 2 THEN 'blacklist'
        ELSE 'admin'
    END,
    printf('203.0.%d.%d', (n / 250) % 250, (n % 250) + 1),
    printf('scale-mx-%02d.test', n % 24),
    printf('audit%04d@scale.test', n),
    printf('scale-box-%04d@mail.shenjianl.cn', ((n - 1) % 1200) + 1),
    CASE n % 5
        WHEN 0 THEN 'accepted'
        WHEN 1 THEN 'rejected'
        WHEN 2 THEN 'quarantined'
        WHEN 3 THEN 'viewed'
        ELSE 'released'
    END,
    'Scale governance fixture',
    CASE WHEN n % 4 = 3 THEN 'demo-ops-admin' ELSE NULL END,
    datetime('now', printf('-%d minutes', n % 43200)),
    'email',
    printf('scale-audit-%05d', n),
    printf('{"demo":true,"scale_id":%d}', n)
FROM seq
WHERE NOT EXISTS (
    SELECT 1
    FROM audit_logs
    WHERE target_id = printf('scale-audit-%05d', n)
);

WITH RECURSIVE seq(n) AS (
    SELECT 1
    UNION ALL
    SELECT n + 1 FROM seq WHERE n < 1600
)
INSERT INTO email_logs (
    user_id, recipient, kind, status, error, created_at
)
SELECT
    printf('scale-user-%04d', ((n - 1) % 1200) + 1),
    printf('scale.out.%04d@example.test', n),
    CASE n % 3
        WHEN 0 THEN 'verification'
        WHEN 1 THEN 'notification'
        ELSE 'security'
    END,
    CASE WHEN n % 9 = 0 THEN 'failed' ELSE 'sent' END,
    CASE WHEN n % 9 = 0 THEN 'Scale provider timeout' ELSE NULL END,
    datetime('now', printf('-%d hours', n % 720))
FROM seq
WHERE NOT EXISTS (
    SELECT 1
    FROM email_logs
    WHERE recipient = printf('scale.out.%04d@example.test', n)
);

WITH RECURSIVE seq(n) AS (
    SELECT 1
    UNION ALL
    SELECT n + 1 FROM seq WHERE n < 600
)
INSERT INTO blocked_senders (
    kind, value, reason, source, expires_at, created_at
)
SELECT
    CASE WHEN n % 3 = 0 THEN 'domain' ELSE 'email' END,
    CASE
        WHEN n % 3 = 0 THEN printf('scale-block-%04d.test', n)
        ELSE printf('blocked%04d@scale.test', n)
    END,
    'Scale governance fixture',
    'manual',
    CASE WHEN n % 8 = 0 THEN datetime('now', '-1 hour') ELSE NULL END,
    datetime('now', printf('-%d hours', n % 720))
FROM seq
WHERE NOT EXISTS (
    SELECT 1
    FROM blocked_senders
    WHERE value = CASE
        WHEN n % 3 = 0 THEN printf('scale-block-%04d.test', n)
        ELSE printf('blocked%04d@scale.test', n)
    END
);

WITH RECURSIVE seq(n) AS (
    SELECT 1
    UNION ALL
    SELECT n + 1 FROM seq WHERE n < 600
)
INSERT INTO blocked_ips (
    ip, reason, source, expires_at, created_at
)
SELECT
    printf('198.18.%d.%d', n / 256, n % 256),
    'Scale governance fixture',
    'manual',
    CASE WHEN n % 8 = 0 THEN datetime('now', '-1 hour') ELSE NULL END,
    datetime('now', printf('-%d hours', n % 720))
FROM seq
WHERE NOT EXISTS (
    SELECT 1
    FROM blocked_ips
    WHERE ip = printf('198.18.%d.%d', n / 256, n % 256)
);

COMMIT;