import mysql, { Pool, RowDataPacket, ResultSetHeader } from "mysql2/promise";
import bcrypt from "bcryptjs";

let pool: Pool;
let initialized = false;

function getPool(): Pool {
  if (!pool) {
    pool = mysql.createPool({
      host: process.env.DB_HOST || "localhost",
      user: process.env.DB_USER || "marjztrr_crmuser",
      password: process.env.DB_PASS || "Arnavutluk-2222",
      database: process.env.DB_NAME || "marjztrr_callcenter",
      charset: "utf8mb4",
      waitForConnections: true,
      connectionLimit: 10,
      queueLimit: 0,
    });
  }
  return pool;
}

async function initializeSchema() {
  const p = getPool();

  await p.query(`
    CREATE TABLE IF NOT EXISTS users (
      id INT PRIMARY KEY AUTO_INCREMENT,
      username VARCHAR(100) UNIQUE NOT NULL,
      password_hash VARCHAR(255) NOT NULL,
      full_name VARCHAR(200) NOT NULL,
      role ENUM('admin', 'manager', 'sale') NOT NULL,
      is_active TINYINT NOT NULL DEFAULT 1,
      created_by INT,
      created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
      FOREIGN KEY (created_by) REFERENCES users(id)
    )
  `);

  await p.query(`
    CREATE TABLE IF NOT EXISTS leads (
      id INT PRIMARY KEY AUTO_INCREMENT,
      first_name VARCHAR(200) NOT NULL,
      last_name VARCHAR(200) NOT NULL,
      phone VARCHAR(50) UNIQUE NOT NULL,
      email VARCHAR(255),
      reference_source VARCHAR(255),
      status ENUM('yeni_atama','cevapsiz','ilgili','ilgisiz','yanlis_numara','tekrar_ara','kara_liste','yatirimci','ulasilamiyor','aranmak_istemiyor','yakin_takip','uzak_takip','takip') NOT NULL DEFAULT 'yeni_atama',
      assigned_to INT,
      previous_assigned_to INT,
      uploaded_by INT,
      created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
      updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
      FOREIGN KEY (assigned_to) REFERENCES users(id),
      FOREIGN KEY (previous_assigned_to) REFERENCES users(id),
      FOREIGN KEY (uploaded_by) REFERENCES users(id)
    )
  `);

  // Migration for new statuses
  try {
    await p.query(`
      ALTER TABLE leads MODIFY COLUMN status ENUM(
        'yeni_atama','cevapsiz','ilgili','ilgisiz','yanlis_numara','tekrar_ara','kara_liste','yatirimci',
        'ulasilamiyor','aranmak_istemiyor','yakin_takip','uzak_takip','takip'
      ) NOT NULL DEFAULT 'yeni_atama'
    `);
  } catch (err) {
    // Ignore if already changed or error
  }

  await p.query(`
    CREATE TABLE IF NOT EXISTS notes (
      id INT PRIMARY KEY AUTO_INCREMENT,
      lead_id INT NOT NULL,
      user_id INT NOT NULL,
      content TEXT NOT NULL,
      created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
      FOREIGN KEY (lead_id) REFERENCES leads(id) ON DELETE CASCADE,
      FOREIGN KEY (user_id) REFERENCES users(id)
    )
  `);

  await p.query(`
    CREATE TABLE IF NOT EXISTS excel_uploads (
      id INT PRIMARY KEY AUTO_INCREMENT,
      uploaded_by INT NOT NULL,
      filename VARCHAR(500) NOT NULL,
      total_rows INT NOT NULL DEFAULT 0,
      imported_rows INT NOT NULL DEFAULT 0,
      skipped_rows INT NOT NULL DEFAULT 0,
      reference_source VARCHAR(255),
      created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
      FOREIGN KEY (uploaded_by) REFERENCES users(id)
    )
  `);

  // Indexes (ignore if already exist)
  try { await p.query("CREATE INDEX idx_leads_assigned ON leads(assigned_to)"); } catch {}
  try { await p.query("CREATE INDEX idx_leads_status ON leads(status)"); } catch {}
  try { await p.query("CREATE INDEX idx_leads_created ON leads(created_at)"); } catch {}
  try { await p.query("CREATE INDEX idx_notes_lead ON notes(lead_id)"); } catch {}

  // Seed admin user if not exists
  const [adminRows] = await p.query<RowDataPacket[]>(
    "SELECT id FROM users WHERE username = 'admin'"
  );
  if (adminRows.length === 0) {
    const hash = bcrypt.hashSync("admin123", 10);
    await p.query(
      "INSERT INTO users (username, password_hash, full_name, role) VALUES ('admin', ?, 'Sistem Admini', 'admin')",
      [hash]
    );
  }
}

/** SELECT — returns row array */
export async function queryRows(sql: string, params: any[] = []) {
  const p = getPool();
  if (!initialized) { await initializeSchema(); initialized = true; }
  const [rows] = await p.query<RowDataPacket[]>(sql, params);
  return rows;
}

/** SELECT — returns first row or null */
export async function queryOne(sql: string, params: any[] = []) {
  const rows = await queryRows(sql, params);
  return rows[0] || null;
}

/** INSERT / UPDATE / DELETE — returns result header */
export async function execute(sql: string, params: any[] = []) {
  const p = getPool();
  if (!initialized) { await initializeSchema(); initialized = true; }
  const [result] = await p.query<ResultSetHeader>(sql, params);
  return result;
}

/** Get raw pool for transactions */
export async function getConnection() {
  const p = getPool();
  if (!initialized) { await initializeSchema(); initialized = true; }
  return p.getConnection();
}