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 trySql(conn: mysql.Connection, label: string, sql: string) {
  try {
    await conn.query(sql);
    console.log(label + "=OK");
    return true;
  } catch (e) {
    console.log(label + "=FAIL " + (e instanceof Error ? e.message : e));
    return false;
  }
}

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,
  });

  // Variant A: CASE instead of IF
  await trySql(
    conn,
    "A_create",
    `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) AS (CASE WHEN type = 'BOOKING_PAYMENT' THEN booking_id ELSE NULL END) STORED,
  withdrawal_key VARCHAR(36) AS (CASE WHEN type = 'WITHDRAWAL' THEN withdrawal_id ELSE NULL END) 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`,
  );

  // Variant B: no generated, with FKs
  if (!(await conn.query("SHOW TABLES LIKE 'ledger_entries'").then(([r]) => (r as unknown[]).length))) {
    await trySql(
      conn,
      "B_create_no_gen",
      `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),
  PRIMARY KEY (id),
  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`,
    );
  }

  await conn.end();
}

main();