import 'dotenv/config';
import mysql from 'mysql2/promise';

/**
 * Manual migration: bikin tabel langsung dari skema, tanpa drizzle-kit.
 * Ini disengaja agar deploy di cPanel bisa lewat 1 perintah `npm run db:migrate`
 * tanpa perlu drizzle-kit di production.
 */
async function migrate() {
  const conn = await mysql.createConnection({
    host: process.env.DB_HOST,
    port: Number(process.env.DB_PORT) || 3306,
    user: process.env.DB_USER,
    password: process.env.DB_PASSWORD,
    database: process.env.DB_NAME,
    multipleStatements: true,
  });

  console.log('🔧 Menjalankan migrasi...');

  const sql = `
  CREATE TABLE IF NOT EXISTS users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    nama VARCHAR(150) NOT NULL,
    email VARCHAR(191) NOT NULL,
    password_hash VARCHAR(255),
    google_id VARCHAR(191),
    role VARCHAR(20) NOT NULL DEFAULT 'wali_kelas',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL,
    UNIQUE KEY users_email_idx (email),
    KEY users_google_idx (google_id)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

  CREATE TABLE IF NOT EXISTS angkatan (
    id INT AUTO_INCREMENT PRIMARY KEY,
    nomor INT NOT NULL,
    tahun INT NOT NULL,
    status VARCHAR(20) NOT NULL DEFAULT 'aktif',
    background_url VARCHAR(500),
    backsound_url VARCHAR(500),
    nama_label_position JSON,
    wa_template TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL,
    UNIQUE KEY angkatan_nomor_idx (nomor)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

  CREATE TABLE IF NOT EXISTS kelas (
    id INT AUTO_INCREMENT PRIMARY KEY,
    angkatan_id INT NOT NULL,
    nama VARCHAR(100) NOT NULL,
    jenjang VARCHAR(10) NOT NULL,
    wali_kelas_user_id INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL,
    KEY kelas_angkatan_idx (angkatan_id),
    KEY kelas_wali_idx (wali_kelas_user_id),
    CONSTRAINT fk_kelas_angkatan FOREIGN KEY (angkatan_id) REFERENCES angkatan(id) ON DELETE CASCADE,
    CONSTRAINT fk_kelas_wali FOREIGN KEY (wali_kelas_user_id) REFERENCES users(id) ON DELETE SET NULL
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

  CREATE TABLE IF NOT EXISTS santri (
    id INT AUTO_INCREMENT PRIMARY KEY,
    kelas_id INT NOT NULL,
    angkatan_id INT NOT NULL,
    nis VARCHAR(50) NOT NULL,
    nama VARCHAR(200) NOT NULL,
    slug VARCHAR(220) NOT NULL,
    nomor_wa_enc TEXT NOT NULL,
    status_terkirim BOOLEAN NOT NULL DEFAULT FALSE,
    terkirim_at TIMESTAMP NULL,
    terkirim_by_user_id INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL,
    UNIQUE KEY santri_angkatan_slug_idx (angkatan_id, slug),
    UNIQUE KEY santri_angkatan_nis_idx (angkatan_id, nis),
    KEY santri_kelas_idx (kelas_id),
    CONSTRAINT fk_santri_kelas FOREIGN KEY (kelas_id) REFERENCES kelas(id) ON DELETE CASCADE,
    CONSTRAINT fk_santri_angkatan FOREIGN KEY (angkatan_id) REFERENCES angkatan(id) ON DELETE CASCADE,
    CONSTRAINT fk_santri_terkirim_by FOREIGN KEY (terkirim_by_user_id) REFERENCES users(id) ON DELETE SET NULL
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

  CREATE TABLE IF NOT EXISTS audit_log (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT,
    action VARCHAR(100) NOT NULL,
    entity_type VARCHAR(50),
    entity_id INT,
    metadata JSON,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
    KEY audit_user_idx (user_id),
    KEY audit_entity_idx (entity_type, entity_id)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

  CREATE TABLE IF NOT EXISTS sessions (
    session_id VARCHAR(128) NOT NULL PRIMARY KEY,
    expires INT UNSIGNED NOT NULL,
    data MEDIUMTEXT
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
  `;

  await conn.query(sql);
  console.log('✅ Migrasi selesai.');
  await conn.end();
}

migrate().catch((err) => {
  console.error('❌ Migrasi gagal:', err);
  process.exit(1);
});
