const fs = require('fs');
const path = require('path');
const mysql = require('mysql2/promise');
const bcrypt = require('bcryptjs');
const crypto = require('crypto');
const config = require('./config');

async function main() {
  const conn = await mysql.createConnection({
    host: config.db.host,
    port: config.db.port,
    user: config.db.user,
    password: config.db.password,
    multipleStatements: true,
  });

  const schema = fs.readFileSync(path.join(__dirname, '../sql/schema.sql'), 'utf8');
  await conn.query(schema);

  const [cols] = await conn.query(
    `SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS
     WHERE TABLE_SCHEMA = ? AND TABLE_NAME = 'users' AND COLUMN_NAME = 'mobile_verified_at'`,
    [config.db.database],
  );
  if (!cols[0]) {
    await conn.query('ALTER TABLE users ADD COLUMN mobile_verified_at DATETIME NULL AFTER password_hash');
  }

  const hash = await bcrypt.hash('demo1234', 10);
  await conn.query(
    `INSERT INTO users (name, mobile, password_hash, mobile_verified_at) VALUES (?,?,?,NOW())
     ON DUPLICATE KEY UPDATE password_hash = VALUES(password_hash), name = VALUES(name)`,
    ['Demo Owner', '9999999999', hash],
  );
  await conn.query(
    `INSERT INTO users (name, mobile, password_hash, mobile_verified_at) VALUES (?,?,?,NOW())
     ON DUPLICATE KEY UPDATE name = VALUES(name)`,
    ['Demo Viewer', '8888888888', hash],
  );
  await conn.query(
    `UPDATE users u
     JOIN sessions s ON s.user_id = u.id
     SET u.mobile_verified_at = NOW()
     WHERE u.mobile_verified_at IS NULL`,
  );

  const secret1 = crypto.randomBytes(32).toString('hex');
  const secret2 = crypto.randomBytes(32).toString('hex');

  await conn.query(
    `INSERT INTO devices (public_id, name, imei, iccid, ingest_secret, status, last_seen, battery_percent)
     VALUES ('DEV001','Family Car','359000000000001','89910000000000000001',?,'online',NOW(),86)
     ON DUPLICATE KEY UPDATE name = VALUES(name)`,
    [secret1],
  );
  await conn.query(
    `INSERT INTO devices (public_id, name, imei, iccid, ingest_secret, status, last_seen, battery_percent)
     VALUES ('DEV002','Unassigned Tracker','359000000000002','89910000000000000002',?,'offline',NULL,100)
     ON DUPLICATE KEY UPDATE name = VALUES(name)`,
    [secret2],
  );

  const [[d1]] = await conn.query('SELECT id FROM devices WHERE public_id = ?', ['DEV001']);
  const [[d2]] = await conn.query('SELECT id FROM devices WHERE public_id = ?', ['DEV002']);
  const [[owner]] = await conn.query('SELECT id FROM users WHERE mobile = ?', ['9999999999']);

  await conn.query(
    `INSERT INTO device_activation (device_id, token, status, activated_at)
     VALUES (?, 'DEMO-ACTIVATE-TOKEN', 'USED', NOW())
     ON DUPLICATE KEY UPDATE status = 'USED'`,
    [d1.id],
  );
  await conn.query(
    `INSERT INTO device_activation (device_id, token, status)
     VALUES (?, 'NEW-DEVICE-TOKEN', 'NEW')
     ON DUPLICATE KEY UPDATE token = VALUES(token)`,
    [d2.id],
  );

  await conn.query('DELETE FROM device_ownership WHERE device_id = ?', [d1.id]);
  await conn.query('INSERT INTO device_ownership (device_id, owner_id) VALUES (?,?)', [
    d1.id,
    owner.id,
  ]);

  await conn.query('DELETE FROM subscriptions WHERE user_id = ?', [owner.id]);
  await conn.query(
    `INSERT INTO subscriptions (user_id, plan, starts_at, ends_at)
     VALUES (?, 'annual', CURDATE(), DATE_ADD(CURDATE(), INTERVAL 1 YEAR))`,
    [owner.id],
  );

  await conn.query('DELETE FROM locations WHERE device_id = ?', [d1.id]);
  let lat = 28.6139;
  let lng = 77.209;
  for (let i = 0; i < 24; i++) {
    lat += 0.0012;
    lng += 0.0008;
    await conn.query(
      `INSERT INTO locations (device_id, recorded_at, lat, lng, speed, direction, accuracy)
       VALUES (?, DATE_SUB(NOW(), INTERVAL ? MINUTE), ?, ?, ?, ?, 8)`,
      [d1.id, 24 - i, lat, lng, 18 + (i % 10), (i * 15) % 360],
    );
  }

  console.log('Database ready.');
  console.log('Demo login: 9999999999 / demo1234');
  console.log('Unused activation token: NEW-DEVICE-TOKEN');
  await conn.end();
}

main().catch((err) => {
  console.error(err);
  process.exit(1);
});
