import fs from "fs";
import path from "path";
import mysql from "mysql2/promise";

function loadEnv() {
  const t = fs.readFileSync(path.join(process.cwd(), ".env"), "utf8");
  for (const line of t.split(/\r?\n/)) {
    const s = line.trim();
    if (!s || s.startsWith("#")) continue;
    const eq = s.indexOf("=");
    if (eq <= 0) continue;
    const k = s.slice(0, eq).trim();
    let v = s.slice(eq + 1).trim();
    if (
      (v.startsWith('"') && v.endsWith('"')) ||
      (v.startsWith("'") && v.endsWith("'"))
    ) {
      v = v.slice(1, -1);
    }
    process.env[k] = v;
  }
}

async function main() {
  loadEnv();
  const conn = await mysql.createConnection({
    host: process.env.MYSQL_HOST,
    port: Number(process.env.MYSQL_PORT),
    user: process.env.MYSQL_USER,
    password: process.env.MYSQL_PASSWORD,
    database: process.env.MYSQL_DATABASE,
  });
  const [tables] = await conn.query("SHOW TABLES");
  console.log(
    "tables=" +
      (tables as Array<Record<string, string>>).map((t) => Object.values(t)[0]).join(","),
  );

  const sql = `CREATE TABLE ledger_entries (
  id VARCHAR(36) NOT NULL,
  type VARCHAR(64) NOT NULL,
  direction VARCHAR(16) NOT NULL,
  booking_id VARCHAR(36) NULL,
  refund_id VARCHAR(36) NULL,
  withdrawal_id VARCHAR(36) NULL,
  amount_lyd DECIMAL(14,4) NOT NULL,
  amount_tnd DECIMAL(14,4) NOT NULL,
  party_user_id VARCHAR(36) NULL,
  party_role VARCHAR(32) NOT NULL,
  status VARCHAR(32) NOT NULL DEFAULT 'POSTED',
  meta JSON NULL,
  created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  updated_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
  booking_payment_key VARCHAR(36) GENERATED ALWAYS AS (IF(type = 'BOOKING_PAYMENT', booking_id, NULL)) STORED,
  withdrawal_key VARCHAR(36) GENERATED ALWAYS AS (IF(type = 'WITHDRAWAL', withdrawal_id, NULL)) STORED,
  PRIMARY KEY (id),
  UNIQUE KEY uk_ledger_booking_payment (booking_payment_key),
  UNIQUE KEY uk_ledger_withdrawal (withdrawal_key),
  KEY idx_ledger_type (type),
  KEY idx_ledger_booking (booking_id),
  KEY idx_ledger_party_user (party_user_id),
  KEY idx_ledger_created_at (created_at),
  KEY idx_ledger_type_created (type, created_at),
  CONSTRAINT fk_ledger_booking FOREIGN KEY (booking_id) REFERENCES bookings (id) ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT fk_ledger_refund FOREIGN KEY (refund_id) REFERENCES refunds (id) ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT fk_ledger_withdrawal FOREIGN KEY (withdrawal_id) REFERENCES withdrawal_requests (id) ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT fk_ledger_party_user FOREIGN KEY (party_user_id) REFERENCES users (id) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci`;

  try {
    await conn.query(sql);
    console.log("ledger_ok");
  } catch (e) {
    const err = e as { message?: string; code?: string; errno?: number; sqlState?: string };
    console.log("err=" + err.message);
    console.log(`code=${err.code} errno=${err.errno} sqlState=${err.sqlState}`);
    try {
      const [st] = await conn.query("SHOW ENGINE INNODB STATUS");
      const status = (st as Array<{ Status: string }>)[0].Status;
      const idx = status.indexOf("LATEST FOREIGN KEY ERROR");
      console.log(status.slice(Math.max(0, idx), idx + 1500));
    } catch (e2) {
      console.log("status_fail=" + (e2 instanceof Error ? e2.message : e2));
    }
  }
  await conn.end();
}

main();