import { nowIso } from './utils.js';

function tableColumns(db, table) {
  try { return db.prepare(`PRAGMA table_info(${table})`).all(); }
  catch { return []; }
}

function ensureColumn(db, table, column, definition) {
  if (!tableColumns(db, table).some((item) => item.name === column)) {
    db.exec(`ALTER TABLE ${table} ADD COLUMN ${column} ${definition}`);
  }
}

function settingDefault(db, key, value) {
  db.prepare('INSERT OR IGNORE INTO settings(key,value,updated_at) VALUES(?,?,?)')
    .run(key, JSON.stringify(value), nowIso());
}

function migration001FinalPlatform(db) {
  ensureColumn(db, 'users', 'email', 'TEXT');
  ensureColumn(db, 'users', 'email_verified_at', 'TEXT');
  ensureColumn(db, 'users', 'auth_primary', "TEXT NOT NULL DEFAULT 'telegram'");
  ensureColumn(db, 'users', 'terms_version', "TEXT NOT NULL DEFAULT ''");
  ensureColumn(db, 'users', 'terms_accepted_at', 'TEXT');

  ensureColumn(db, 'ledger_accounts', 'balance_text', "TEXT NOT NULL DEFAULT '0'");
  ensureColumn(db, 'ledger_entries', 'amount_text', "TEXT NOT NULL DEFAULT '0'");
  db.exec(`
    UPDATE ledger_accounts SET balance_text=CAST(balance AS TEXT)
    WHERE balance_text IS NULL OR balance_text='' OR (balance_text='0' AND balance<>0);
    UPDATE ledger_entries SET amount_text=CAST(amount AS TEXT)
    WHERE amount_text IS NULL OR amount_text='' OR amount_text='0';
  `);

  ensureColumn(db, 'presale_purchases', 'pricing_policy', "TEXT NOT NULL DEFAULT 'launch_price'");
  ensureColumn(db, 'presale_purchases', 'price_snapshot_usdt_micros', 'INTEGER NOT NULL DEFAULT 0');
  ensureColumn(db, 'presale_purchases', 'estimated_token_base', "TEXT NOT NULL DEFAULT '0'");
  ensureColumn(db, 'presale_purchases', 'weighted_usdt_micros', 'INTEGER NOT NULL DEFAULT 0');
  ensureColumn(db, 'presale_purchases', 'final_token_base', "TEXT NOT NULL DEFAULT '0'");
  ensureColumn(db, 'presale_purchases', 'allocation_version', 'INTEGER');
  ensureColumn(db, 'presale_purchases', 'finalized_at', 'TEXT');
  ensureColumn(db, 'presale_purchases', 'terms_version', "TEXT NOT NULL DEFAULT '1.0'");
  ensureColumn(db, 'presale_purchases', 'refund_status', "TEXT NOT NULL DEFAULT 'none'");

  db.exec(`
    CREATE UNIQUE INDEX IF NOT EXISTS idx_users_email_unique ON users(lower(email)) WHERE email IS NOT NULL AND email<>'';

    CREATE TABLE IF NOT EXISTS schema_migrations (
      version INTEGER PRIMARY KEY,
      name TEXT NOT NULL,
      applied_at TEXT NOT NULL
    );

    CREATE TABLE IF NOT EXISTS secure_settings (
      key TEXT PRIMARY KEY,
      encrypted_value TEXT NOT NULL,
      key_version INTEGER NOT NULL DEFAULT 1,
      updated_at TEXT NOT NULL
    );

    CREATE TABLE IF NOT EXISTS user_credentials (
      user_id TEXT PRIMARY KEY REFERENCES users(id) ON DELETE CASCADE,
      password_hash TEXT NOT NULL,
      password_salt TEXT NOT NULL,
      password_params_json TEXT NOT NULL,
      failed_attempts INTEGER NOT NULL DEFAULT 0,
      locked_until INTEGER,
      password_changed_at TEXT NOT NULL,
      created_at TEXT NOT NULL,
      updated_at TEXT NOT NULL
    );

    CREATE TABLE IF NOT EXISTS email_verification_tokens (
      id TEXT PRIMARY KEY,
      user_id TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
      code_hash TEXT NOT NULL,
      attempts INTEGER NOT NULL DEFAULT 0,
      expires_at INTEGER NOT NULL,
      consumed_at TEXT,
      created_at TEXT NOT NULL
    );
    CREATE INDEX IF NOT EXISTS idx_email_verification_user ON email_verification_tokens(user_id,created_at DESC);

    CREATE TABLE IF NOT EXISTS password_reset_tokens (
      id TEXT PRIMARY KEY,
      user_id TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
      token_hash TEXT NOT NULL UNIQUE,
      attempts INTEGER NOT NULL DEFAULT 0,
      expires_at INTEGER NOT NULL,
      consumed_at TEXT,
      created_at TEXT NOT NULL
    );

    CREATE TABLE IF NOT EXISTS oauth_providers (
      id TEXT PRIMARY KEY,
      name TEXT NOT NULL,
      enabled INTEGER NOT NULL DEFAULT 0 CHECK(enabled IN (0,1)),
      client_id TEXT NOT NULL DEFAULT '',
      secrets_enc TEXT NOT NULL DEFAULT '',
      authorize_url TEXT NOT NULL DEFAULT '',
      token_url TEXT NOT NULL DEFAULT '',
      profile_url TEXT NOT NULL DEFAULT '',
      jwks_url TEXT NOT NULL DEFAULT '',
      issuer TEXT NOT NULL DEFAULT '',
      scopes TEXT NOT NULL DEFAULT '',
      config_json TEXT NOT NULL DEFAULT '{}',
      sort_order INTEGER NOT NULL DEFAULT 0,
      updated_at TEXT NOT NULL
    );

    CREATE TABLE IF NOT EXISTS user_oauth_identities (
      id TEXT PRIMARY KEY,
      user_id TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
      provider TEXT NOT NULL REFERENCES oauth_providers(id),
      provider_user_id TEXT NOT NULL,
      email TEXT NOT NULL DEFAULT '',
      email_verified INTEGER NOT NULL DEFAULT 0 CHECK(email_verified IN (0,1)),
      profile_json TEXT NOT NULL DEFAULT '{}',
      created_at TEXT NOT NULL,
      updated_at TEXT NOT NULL,
      UNIQUE(provider,provider_user_id)
    );

    CREATE TABLE IF NOT EXISTS oauth_states (
      id TEXT PRIMARY KEY,
      provider TEXT NOT NULL,
      state_hash TEXT NOT NULL UNIQUE,
      code_verifier TEXT NOT NULL DEFAULT '',
      nonce TEXT NOT NULL DEFAULT '',
      redirect_after TEXT NOT NULL DEFAULT '/game/',
      referral_code TEXT NOT NULL DEFAULT '',
      expires_at INTEGER NOT NULL,
      consumed_at TEXT,
      created_at TEXT NOT NULL
    );

    CREATE TABLE IF NOT EXISTS login_exchange_codes (
      id TEXT PRIMARY KEY,
      user_id TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
      code_hash TEXT NOT NULL UNIQUE,
      expires_at INTEGER NOT NULL,
      consumed_at TEXT,
      created_at TEXT NOT NULL
    );

    CREATE TABLE IF NOT EXISTS email_outbox (
      id TEXT PRIMARY KEY,
      to_email TEXT NOT NULL,
      subject TEXT NOT NULL,
      text_body TEXT NOT NULL,
      html_body TEXT NOT NULL DEFAULT '',
      template TEXT NOT NULL DEFAULT 'generic',
      status TEXT NOT NULL DEFAULT 'queued' CHECK(status IN ('queued','sending','sent','failed','cancelled')),
      attempts INTEGER NOT NULL DEFAULT 0,
      last_error TEXT NOT NULL DEFAULT '',
      metadata_json TEXT NOT NULL DEFAULT '{}',
      sent_at TEXT,
      created_at TEXT NOT NULL,
      updated_at TEXT NOT NULL
    );
    CREATE INDEX IF NOT EXISTS idx_email_outbox_status ON email_outbox(status,created_at);

    CREATE TABLE IF NOT EXISTS user_wallets (
      id TEXT PRIMARY KEY,
      user_id TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
      wallet_type TEXT NOT NULL CHECK(wallet_type IN ('generated_testnet','external_mainnet')),
      network TEXT NOT NULL,
      chain_id INTEGER NOT NULL DEFAULT 0,
      address TEXT NOT NULL,
      label TEXT NOT NULL DEFAULT '',
      encrypted_key_json TEXT NOT NULL DEFAULT '',
      password_verifier TEXT NOT NULL DEFAULT '',
      verification_status TEXT NOT NULL DEFAULT 'unverified' CHECK(verification_status IN ('unverified','email_pending','email_confirmed','signature_verified','admin_verified')),
      status TEXT NOT NULL DEFAULT 'active' CHECK(status IN ('active','cooldown','disabled')),
      password_set_at TEXT,
      email_notified_at TEXT,
      available_at INTEGER NOT NULL DEFAULT 0,
      created_at TEXT NOT NULL,
      updated_at TEXT NOT NULL,
      UNIQUE(user_id,wallet_type,network,address)
    );
    CREATE INDEX IF NOT EXISTS idx_user_wallets_user ON user_wallets(user_id,created_at DESC);

    CREATE TABLE IF NOT EXISTS wallet_recovery_exports (
      id TEXT PRIMARY KEY,
      wallet_id TEXT NOT NULL REFERENCES user_wallets(id) ON DELETE CASCADE,
      token_hash TEXT NOT NULL UNIQUE,
      expires_at INTEGER NOT NULL,
      downloaded_at TEXT,
      created_at TEXT NOT NULL
    );

    CREATE TABLE IF NOT EXISTS wallet_address_confirmations (
      id TEXT PRIMARY KEY,
      wallet_id TEXT NOT NULL REFERENCES user_wallets(id) ON DELETE CASCADE,
      user_id TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
      code_hash TEXT NOT NULL,
      attempts INTEGER NOT NULL DEFAULT 0,
      expires_at INTEGER NOT NULL,
      consumed_at TEXT,
      created_at TEXT NOT NULL
    );

    CREATE TABLE IF NOT EXISTS presale_allocation_snapshots (
      id TEXT PRIMARY KEY,
      version INTEGER NOT NULL UNIQUE,
      policy TEXT NOT NULL,
      launch_price_usdt_micros INTEGER NOT NULL,
      pool_token_base TEXT NOT NULL,
      total_paid_usdt_micros INTEGER NOT NULL,
      total_weighted_usdt_micros INTEGER NOT NULL,
      total_token_base TEXT NOT NULL,
      purchase_count INTEGER NOT NULL,
      config_json TEXT NOT NULL DEFAULT '{}',
      status TEXT NOT NULL CHECK(status IN ('preview','pending_approval','finalized','cancelled')),
      created_by TEXT NOT NULL,
      approved_by TEXT,
      approved_at TEXT,
      finalized_at TEXT,
      created_at TEXT NOT NULL
    );

    CREATE TABLE IF NOT EXISTS presale_finalization_approvals (
      id TEXT PRIMARY KEY,
      snapshot_id TEXT NOT NULL REFERENCES presale_allocation_snapshots(id) ON DELETE CASCADE,
      actor_id TEXT NOT NULL,
      decision TEXT NOT NULL CHECK(decision IN ('approve','reject')),
      note TEXT NOT NULL DEFAULT '',
      created_at TEXT NOT NULL,
      UNIQUE(snapshot_id,actor_id)
    );

    CREATE TABLE IF NOT EXISTS distribution_campaigns (
      id TEXT PRIMARY KEY,
      name TEXT NOT NULL,
      mode TEXT NOT NULL CHECK(mode IN ('auto_contract','manual_withdrawal')),
      network TEXT NOT NULL,
      chain_id INTEGER NOT NULL,
      token_contract TEXT NOT NULL DEFAULT '',
      signer_mode TEXT NOT NULL DEFAULT 'webhook',
      snapshot_id TEXT REFERENCES presale_allocation_snapshots(id),
      status TEXT NOT NULL CHECK(status IN ('draft','ready','running','paused','manual_open','completed','cancelled','failed')),
      total_recipients INTEGER NOT NULL DEFAULT 0,
      total_token_base TEXT NOT NULL DEFAULT '0',
      processed_recipients INTEGER NOT NULL DEFAULT 0,
      created_by TEXT NOT NULL,
      started_at TEXT,
      completed_at TEXT,
      created_at TEXT NOT NULL,
      updated_at TEXT NOT NULL
    );

    CREATE TABLE IF NOT EXISTS distribution_items (
      id TEXT PRIMARY KEY,
      campaign_id TEXT NOT NULL REFERENCES distribution_campaigns(id) ON DELETE CASCADE,
      user_id TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
      wallet_id TEXT REFERENCES user_wallets(id),
      address TEXT NOT NULL DEFAULT '',
      amount_token_base TEXT NOT NULL,
      status TEXT NOT NULL CHECK(status IN ('waiting_wallet','ready','broadcast','confirmed','manual','failed','cancelled')),
      tx_hash TEXT NOT NULL DEFAULT '',
      block_number TEXT NOT NULL DEFAULT '',
      confirmations INTEGER NOT NULL DEFAULT 0,
      error_text TEXT NOT NULL DEFAULT '',
      created_at TEXT NOT NULL,
      updated_at TEXT NOT NULL,
      UNIQUE(campaign_id,user_id)
    );
    CREATE INDEX IF NOT EXISTS idx_distribution_items_status ON distribution_items(campaign_id,status);
    CREATE UNIQUE INDEX IF NOT EXISTS idx_distribution_campaign_snapshot ON distribution_campaigns(snapshot_id) WHERE snapshot_id IS NOT NULL AND status<>'cancelled';

    CREATE TABLE IF NOT EXISTS distribution_receipts (
      id TEXT PRIMARY KEY,
      item_id TEXT NOT NULL REFERENCES distribution_items(id) ON DELETE CASCADE,
      chain_id INTEGER NOT NULL,
      contract_address TEXT NOT NULL,
      recipient_address TEXT NOT NULL,
      amount_token_base TEXT NOT NULL,
      tx_hash TEXT NOT NULL,
      receipt_json TEXT NOT NULL DEFAULT '{}',
      verified_at TEXT NOT NULL,
      UNIQUE(tx_hash,item_id)
    );

    CREATE TABLE IF NOT EXISTS admin_approvals (
      id TEXT PRIMARY KEY,
      operation_type TEXT NOT NULL,
      operation_id TEXT NOT NULL,
      actor_id TEXT NOT NULL,
      decision TEXT NOT NULL CHECK(decision IN ('approve','reject')),
      note TEXT NOT NULL DEFAULT '',
      created_at TEXT NOT NULL,
      UNIQUE(operation_type,operation_id,actor_id)
    );
  `);

  const providers = [
    ['google','Google',0,'https://accounts.google.com/o/oauth2/v2/auth','https://oauth2.googleapis.com/token','https://openidconnect.googleapis.com/v1/userinfo','https://www.googleapis.com/oauth2/v3/certs','https://accounts.google.com','openid email profile',10],
    ['apple','Apple',0,'https://appleid.apple.com/auth/authorize','https://appleid.apple.com/auth/token','','https://appleid.apple.com/auth/keys','https://appleid.apple.com','openid email name',20],
    ['facebook','Facebook',0,'https://www.facebook.com/v20.0/dialog/oauth','https://graph.facebook.com/v20.0/oauth/access_token','https://graph.facebook.com/me','','https://www.facebook.com','email public_profile',30],
    ['x','X',0,'https://twitter.com/i/oauth2/authorize','https://api.twitter.com/2/oauth2/token','https://api.twitter.com/2/users/me','','https://twitter.com','tweet.read users.read offline.access',40],
  ];
  const insert = db.prepare(`INSERT OR IGNORE INTO oauth_providers(id,name,enabled,authorize_url,token_url,profile_url,jwks_url,issuer,scopes,sort_order,updated_at) VALUES(?,?,?,?,?,?,?,?,?,?,?)`);
  for (const row of providers) insert.run(...row, nowIso());

  const defaults = {
    account_registration_enabled: true,
    email_registration_enabled: true,
    account_require_email_verification: false,
    email_verification_required: false,
    account_password_min_length: 12,
    email_verification_ttl_minutes: 15,
    email_verification_max_attempts: 5,
    password_reset_ttl_minutes: 30,
    oauth_enabled: true,
    social_login_enabled: true,
    wallet_testnet_enabled: true,
    wallet_testnet_network_name: 'BSC Testnet',
    wallet_testnet_chain_id: 97,
    wallet_testnet_explorer_url: 'https://testnet.bscscan.com',
    wallet_password_min_length: 12,
    wallet_recovery_link_ttl_minutes: 30,
    wallet_email_confirmation_required: true,
    wallet_mainnet_enabled: true,
    wallet_mainnet_network_name: 'BSC Mainnet',
    wallet_mainnet_chain_id: 56,
    wallet_address_cooldown_hours: 24,
    token_total_supply_base_units: '10000000000000000000000000000',
    presale_pool_token_base_units: '2000000000000000000000000000',
    presale_allocation_policy: 'launch_price',
    presale_launch_price_usdt_micros: 30000,
    presale_reference_price_usdt_micros: 12500,
    presale_terms_version: '1.0',
    presale_finalization_requires_two_approvals: false,
    presale_auto_finalize_at_launch: false,
    presale_refund_policy: 'manual_review',
    distribution_enabled: true,
    distribution_auto_start: false,
    distribution_mode: 'auto_contract',
    distribution_signer_mode: 'webhook',
    distribution_signer_url: '',
    distribution_contract_address: '',
    distribution_batch_size: 100,
    distribution_confirmations_required: 12,
    distribution_requires_confirmed_wallet: true,
    login_cookie_enabled: true
  };
  for (const [key, value] of Object.entries(defaults)) settingDefault(db, key, value);
}



function migration002DistributionSources(db) {
  ensureColumn(db, 'distribution_items', 'presale_token_base', "TEXT NOT NULL DEFAULT '0'");
  ensureColumn(db, 'distribution_items', 'game_token_whole', 'INTEGER NOT NULL DEFAULT 0');
  db.exec(`
    UPDATE distribution_items
    SET presale_token_base=amount_token_base
    WHERE (presale_token_base IS NULL OR presale_token_base='0')
      AND COALESCE(game_token_whole,0)=0;
  `);
}

function migration003ProductionInstaller(db) {
  db.exec(`
    CREATE TABLE IF NOT EXISTS admin_users (
      id TEXT PRIMARY KEY,
      email TEXT NOT NULL COLLATE NOCASE UNIQUE,
      name TEXT NOT NULL,
      password_hash TEXT NOT NULL,
      password_salt TEXT NOT NULL,
      password_params_json TEXT NOT NULL,
      role TEXT NOT NULL DEFAULT 'owner' CHECK(role IN ('owner','administrator','finance','support','editor','auditor')),
      status TEXT NOT NULL DEFAULT 'active' CHECK(status IN ('active','suspended','disabled')),
      failed_attempts INTEGER NOT NULL DEFAULT 0,
      locked_until INTEGER,
      last_login_at TEXT,
      created_at TEXT NOT NULL,
      updated_at TEXT NOT NULL
    );
    CREATE INDEX IF NOT EXISTS idx_admin_users_status ON admin_users(status,email);

    CREATE TABLE IF NOT EXISTS installation_events (
      id TEXT PRIMARY KEY,
      installation_id TEXT NOT NULL,
      event_type TEXT NOT NULL,
      metadata_json TEXT NOT NULL DEFAULT '{}',
      created_at TEXT NOT NULL
    );

    DELETE FROM oauth_providers WHERE id='demo';
    DELETE FROM settings WHERE key IN (
      'demo_finance_mode',
      'oauth_demo_enabled',
      'distribution_allow_demo_signer',
      'payment_test_mode'
    );
    UPDATE payment_providers SET test_mode=0, status=CASE WHEN enabled=1 THEN status ELSE 'not_configured' END;
    UPDATE fx_rates SET source='manual', is_demo=0 WHERE is_demo<>0 OR source='demo';
    UPDATE crypto_assets SET price_source='manual' WHERE price_source='demo';
    DELETE FROM bank_accounts WHERE COALESCE(iban,'')='' AND COALESCE(account_number,'')='';
  `);

  const productionDefaults = {
    account_registration_enabled: true,
    email_registration_enabled: true,
    account_require_email_verification: true,
    email_verification_required: true,
    oauth_enabled: true,
    social_login_enabled: true,
    initial_points: 0,
    purchases_enabled: false,
    presale_enabled: false,
    deposit_enabled: false,
    withdrawal_enabled: false,
    crypto_funding_enabled: false,
    crypto_payment_for_goods_enabled: false,
    bank_transfer_enabled: false,
    fiat_payments_enabled: false,
    distribution_enabled: false,
    distribution_auto_start: false,
    distribution_signer_mode: 'webhook',
    smtp_enabled: false,
    login_cookie_enabled: true
  };
  for (const [key, value] of Object.entries(productionDefaults)) settingDefault(db, key, value);
}

const MIGRATIONS = [
  [1, 'final-platform-schema', migration001FinalPlatform],
  [2, 'distribution-presale-and-game-sources', migration002DistributionSources],
  [3, 'production-installer-and-admin-accounts', migration003ProductionInstaller]
];

export function applyMigrations(db) {
  db.exec(`CREATE TABLE IF NOT EXISTS schema_migrations(version INTEGER PRIMARY KEY,name TEXT NOT NULL,applied_at TEXT NOT NULL)`);
  const has = db.prepare('SELECT 1 FROM schema_migrations WHERE version=?');
  const add = db.prepare('INSERT INTO schema_migrations(version,name,applied_at) VALUES(?,?,?)');
  for (const [version, name, migration] of MIGRATIONS) {
    if (has.get(version)) continue;
    db.exec('BEGIN IMMEDIATE');
    try {
      migration(db);
      add.run(version, name, nowIso());
      db.exec('COMMIT');
    } catch (error) {
      try { db.exec('ROLLBACK'); } catch {}
      throw error;
    }
  }
  // Idempotent repair for databases created by intermediate development builds.
  migration001FinalPlatform(db);
  migration002DistributionSources(db);
  migration003ProductionInstaller(db);
}
