import { pool } from "../config/db.js";

async function ensureCashTables() {
  await pool.query(`
    CREATE TABLE IF NOT EXISTS cash_register_sessions (
      id INT AUTO_INCREMENT PRIMARY KEY,
      business_date DATE NOT NULL,
      opening_amount DECIMAL(10,2) NOT NULL DEFAULT 0,
      closing_amount DECIMAL(10,2) NULL,
      expected_cash DECIMAL(10,2) NULL,
      difference_amount DECIMAL(10,2) NULL,
      status ENUM('open','closed') NOT NULL DEFAULT 'open',
      opened_by INT NULL,
      closed_by INT NULL,
      notes TEXT NULL,
      close_breakdown LONGTEXT NULL,
      opened_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
      closed_at DATETIME NULL,
      INDEX idx_cash_sessions_date (business_date),
      INDEX idx_cash_sessions_status (status)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
  `);

  const [uniqueDateIndexes] = await pool.query(`
    SELECT INDEX_NAME
    FROM INFORMATION_SCHEMA.STATISTICS
    WHERE TABLE_SCHEMA = DATABASE()
      AND TABLE_NAME = 'cash_register_sessions'
      AND COLUMN_NAME = 'business_date'
      AND NON_UNIQUE = 0
      AND INDEX_NAME <> 'PRIMARY'
  `);

  for (const row of uniqueDateIndexes) {
    const indexName = String(row.INDEX_NAME || "").replace(/`/g, "");
    if (indexName) {
      await pool.query(`ALTER TABLE cash_register_sessions DROP INDEX \`${indexName}\``);
    }
  }

  const [breakdownColumns] = await pool.query(`
    SELECT COLUMN_NAME
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE()
      AND TABLE_NAME = 'cash_register_sessions'
      AND COLUMN_NAME = 'close_breakdown'
  `);

  if (breakdownColumns.length === 0) {
    await pool.query("ALTER TABLE cash_register_sessions ADD COLUMN close_breakdown LONGTEXT NULL AFTER notes");
  }

  await pool.query(`
    CREATE TABLE IF NOT EXISTS cash_register_movements (
      id INT AUTO_INCREMENT PRIMARY KEY,
      session_id INT NOT NULL,
      type ENUM('in','out') NOT NULL,
      amount DECIMAL(10,2) NOT NULL,
      concept VARCHAR(180) NOT NULL,
      notes TEXT NULL,
      created_by INT NULL,
      created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
      INDEX idx_cash_movements_session (session_id),
      CONSTRAINT fk_cash_movements_session
        FOREIGN KEY (session_id) REFERENCES cash_register_sessions(id)
        ON DELETE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
  `);

  await pool.query(`
    CREATE TABLE IF NOT EXISTS cash_register_shifts (
      id INT AUTO_INCREMENT PRIMARY KEY,
      session_id INT NOT NULL,
      shift_number INT NOT NULL DEFAULT 1,
      opening_amount DECIMAL(10,2) NOT NULL DEFAULT 0,
      closing_amount DECIMAL(10,2) NULL,
      expected_cash DECIMAL(10,2) NULL,
      difference_amount DECIMAL(10,2) NULL,
      status ENUM('open','closed') NOT NULL DEFAULT 'open',
      opened_by INT NULL,
      closed_by INT NULL,
      notes TEXT NULL,
      opened_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
      closed_at DATETIME NULL,
      INDEX idx_cash_shifts_session (session_id),
      INDEX idx_cash_shifts_status (status),
      CONSTRAINT fk_cash_shifts_session
        FOREIGN KEY (session_id) REFERENCES cash_register_sessions(id)
        ON DELETE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
  `);
}

function number(value) {
  return Number(value || 0);
}

function mapSession(row) {
  if (!row) return null;
  let closeBreakdown = null;
  if (row.close_breakdown) {
    try {
      closeBreakdown = JSON.parse(row.close_breakdown);
    } catch {
      closeBreakdown = null;
    }
  }

  return {
    id: row.id,
    businessDate: row.business_date,
    openingAmount: number(row.opening_amount),
    closingAmount: row.closing_amount === null ? null : number(row.closing_amount),
    expectedCash: row.expected_cash === null ? null : number(row.expected_cash),
    differenceAmount: row.difference_amount === null ? null : number(row.difference_amount),
    status: row.status,
    openedBy: row.opened_by,
    closedBy: row.closed_by,
    openedByName: row.opened_by_name || row.opened_by_username || "",
    closedByName: row.closed_by_name || row.closed_by_username || "",
    notes: row.notes || "",
    closeBreakdown,
    openedAt: row.opened_at,
    closedAt: row.closed_at
  };
}

function mapShift(row) {
  if (!row) return null;
  return {
    id: row.id,
    sessionId: row.session_id,
    shiftNumber: row.shift_number,
    openingAmount: number(row.opening_amount),
    closingAmount: row.closing_amount === null ? null : number(row.closing_amount),
    expectedCash: row.expected_cash === null ? null : number(row.expected_cash),
    differenceAmount: row.difference_amount === null ? null : number(row.difference_amount),
    status: row.status,
    openedBy: row.opened_by,
    closedBy: row.closed_by,
    notes: row.notes || "",
    openedAt: row.opened_at,
    closedAt: row.closed_at
  };
}

async function getSessionByDate(date = null) {
  const [rows] = await pool.query(
    `
    SELECT *
    FROM cash_register_sessions
    WHERE business_date = COALESCE(:date, CURDATE())
    ORDER BY FIELD(status, 'open', 'closed'), opened_at DESC, id DESC
    LIMIT 1
    `,
    { date }
  );
  return rows[0] || null;
}

async function buildSummary(session) {
  const targetDate = session?.business_date || new Date();
  const paymentParams = session
    ? {
        startAt: session.opened_at,
        endAt: session.closed_at || new Date()
      }
    : { businessDate: targetDate };
  const paymentWhere = session
    ? "p.paid_at BETWEEN :startAt AND :endAt"
    : "DATE(p.paid_at) = :businessDate";

  const [payments] = await pool.query(
    `
    SELECT p.method, COALESCE(pm.name, p.method) AS method_name, COUNT(*) AS count, COALESCE(SUM(p.amount), 0) AS amount
    FROM payments p
    LEFT JOIN payment_methods pm ON pm.code = p.method
    WHERE ${paymentWhere}
    GROUP BY p.method, pm.name
    ORDER BY amount DESC
    `,
    paymentParams
  );

  const [movements] = session
    ? await pool.query("SELECT * FROM cash_register_movements WHERE session_id = ? ORDER BY created_at DESC, id DESC", [session.id])
    : [[]];

  const cashPayments = payments
    .filter(row => row.method === "cash" || String(row.method_name || "").toLowerCase().includes("efectivo"))
    .reduce((sum, row) => sum + number(row.amount), 0);
  const totalPayments = payments.reduce((sum, row) => sum + number(row.amount), 0);
  const movementIn = movements.filter(row => row.type === "in").reduce((sum, row) => sum + number(row.amount), 0);
  const movementOut = movements.filter(row => row.type === "out").reduce((sum, row) => sum + number(row.amount), 0);
  const expectedCash = number(session?.opening_amount) + cashPayments + movementIn - movementOut;

  return {
    session: mapSession(session),
    totals: {
      totalPayments,
      cashPayments,
      nonCashPayments: totalPayments - cashPayments,
      movementIn,
      movementOut,
      expectedCash
    },
    paymentsByMethod: payments.map(row => ({
      method: row.method,
      methodName: row.method_name || row.method,
      count: Number(row.count || 0),
      amount: number(row.amount)
    })),
    movements: movements.map(row => ({
      id: row.id,
      type: row.type,
      amount: number(row.amount),
      concept: row.concept,
      notes: row.notes || "",
      createdBy: row.created_by,
      createdAt: row.created_at
    }))
  };
}

async function getActiveShift(sessionId) {
  const [rows] = await pool.query(
    "SELECT * FROM cash_register_shifts WHERE session_id = ? AND status = 'open' ORDER BY id DESC LIMIT 1",
    [sessionId]
  );
  return rows[0] || null;
}

async function buildShiftSummary(shift) {
  if (!shift) return null;
  const endAt = shift.closed_at || new Date();
  const [payments] = await pool.query(
    `
    SELECT p.method, COALESCE(pm.name, p.method) AS method_name, COUNT(*) AS count, COALESCE(SUM(p.amount), 0) AS amount
    FROM payments p
    LEFT JOIN payment_methods pm ON pm.code = p.method
    WHERE p.paid_at BETWEEN :startAt AND :endAt
    GROUP BY p.method, pm.name
    ORDER BY amount DESC
    `,
    { startAt: shift.opened_at, endAt }
  );
  const [movements] = await pool.query(
    `
    SELECT *
    FROM cash_register_movements
    WHERE session_id = :sessionId AND created_at BETWEEN :startAt AND :endAt
    ORDER BY created_at DESC, id DESC
    `,
    { sessionId: shift.session_id, startAt: shift.opened_at, endAt }
  );
  const cashPayments = payments
    .filter(row => row.method === "cash" || String(row.method_name || "").toLowerCase().includes("efectivo"))
    .reduce((sum, row) => sum + number(row.amount), 0);
  const totalPayments = payments.reduce((sum, row) => sum + number(row.amount), 0);
  const movementIn = movements.filter(row => row.type === "in").reduce((sum, row) => sum + number(row.amount), 0);
  const movementOut = movements.filter(row => row.type === "out").reduce((sum, row) => sum + number(row.amount), 0);
  const expectedCash = number(shift.opening_amount) + cashPayments + movementIn - movementOut;

  return {
    shift: mapShift(shift),
    totals: {
      totalPayments,
      cashPayments,
      nonCashPayments: totalPayments - cashPayments,
      movementIn,
      movementOut,
      expectedCash
    },
    paymentsByMethod: payments.map(row => ({
      method: row.method,
      methodName: row.method_name || row.method,
      count: Number(row.count || 0),
      amount: number(row.amount)
    })),
    movements: movements.map(row => ({
      id: row.id,
      type: row.type,
      amount: number(row.amount),
      concept: row.concept,
      notes: row.notes || "",
      createdAt: row.created_at
    }))
  };
}

export async function getTodayCashRegister(req, res, next) {
  try {
    await ensureCashTables();
    const session = await getSessionByDate(req.query.date || null);
    const summary = await buildSummary(session);
    const [history] = req.user?.role === "admin"
      ? await pool.query(`
          SELECT s.*,
                 COALESCE(opened_user.full_name, opened_user.username) AS opened_by_name,
                 opened_user.username AS opened_by_username,
                 COALESCE(closed_user.full_name, closed_user.username) AS closed_by_name,
                 closed_user.username AS closed_by_username
          FROM cash_register_sessions s
          LEFT JOIN users opened_user ON opened_user.id = s.opened_by
          LEFT JOIN users closed_user ON closed_user.id = s.closed_by
          ORDER BY s.business_date DESC, s.opened_at DESC, s.id DESC
          LIMIT 10
        `)
      : [[]];
    const [shifts] = session
      ? await pool.query("SELECT * FROM cash_register_shifts WHERE session_id = ? ORDER BY opened_at DESC, id DESC", [session.id])
      : [[]];
    const activeShift = session ? await getActiveShift(session.id) : null;

    res.json({
      success: true,
      data: {
        ...summary,
        activeShift: mapShift(activeShift),
        activeShiftSummary: await buildShiftSummary(activeShift),
        shifts: shifts.map(mapShift),
        history: history.map(mapSession)
      }
    });
  } catch (err) {
    next(err);
  }
}

export async function openCashRegister(req, res, next) {
  try {
    await ensureCashTables();
    if (req.body.openingAmount === undefined || req.body.openingAmount === null || req.body.openingAmount === "") {
      return res.status(400).json({ success: false, message: "Debes ingresar el petty cash inicial para abrir caja" });
    }
    const openingAmount = Number(req.body.openingAmount || 0);
    if (openingAmount < 0) {
      return res.status(400).json({ success: false, message: "El monto inicial no puede ser negativo" });
    }

    const existing = await getSessionByDate();
    if (existing && existing.status === "open") {
      return res.status(400).json({ success: false, message: "La caja de hoy ya esta abierta" });
    }
    const [result] = await pool.query(
      `
      INSERT INTO cash_register_sessions (business_date, opening_amount, opened_by, notes, opened_at)
      VALUES (CURDATE(), :openingAmount, :openedBy, :notes, NOW())
      `,
      {
        openingAmount,
        openedBy: req.user?.id || null,
        notes: req.body.notes || null
      }
    );

    const [[session]] = await pool.query("SELECT * FROM cash_register_sessions WHERE id = ?", [result.insertId]);
    res.status(201).json({ success: true, message: "Caja abierta correctamente", data: await buildSummary(session) });
  } catch (err) {
    next(err);
  }
}

export async function addCashMovement(req, res, next) {
  try {
    await ensureCashTables();
    const session = await getSessionByDate();
    if (!session || session.status !== "open") {
      return res.status(400).json({ success: false, message: "Debes abrir la caja antes de registrar movimientos" });
    }

    const { type, concept, notes } = req.body;
    const amount = Number(req.body.amount || 0);
    if (!["in", "out"].includes(type)) {
      return res.status(400).json({ success: false, message: "Tipo de movimiento invalido" });
    }
    if (amount <= 0 || !concept?.trim()) {
      return res.status(400).json({ success: false, message: "Monto y concepto son requeridos" });
    }

    await pool.query(
      `
      INSERT INTO cash_register_movements (session_id, type, amount, concept, notes, created_by, created_at)
      VALUES (:sessionId, :type, :amount, :concept, :notes, :createdBy, NOW())
      `,
      {
        sessionId: session.id,
        type,
        amount,
        concept: concept.trim(),
        notes: notes || null,
        createdBy: req.user?.id || null
      }
    );

    res.status(201).json({ success: true, message: "Movimiento registrado", data: await buildSummary(session) });
  } catch (err) {
    next(err);
  }
}

export async function updateCashMovement(req, res, next) {
  try {
    await ensureCashTables();
    const { id } = req.params;
    const { type, concept, notes } = req.body;
    const amount = Number(req.body.amount || 0);

    if (!["in", "out"].includes(type)) {
      return res.status(400).json({ success: false, message: "Tipo de movimiento invalido" });
    }
    if (amount <= 0 || !concept?.trim()) {
      return res.status(400).json({ success: false, message: "Monto y concepto son requeridos" });
    }

    const [rows] = await pool.query(
      `
      SELECT m.*, s.status
      FROM cash_register_movements m
      JOIN cash_register_sessions s ON s.id = m.session_id
      WHERE m.id = ?
      `,
      [id]
    );
    if (!rows.length) {
      return res.status(404).json({ success: false, message: "Movimiento no encontrado" });
    }
    if (rows[0].status !== "open") {
      return res.status(400).json({ success: false, message: "No puedes editar movimientos de una caja cerrada" });
    }

    await pool.query(
      `
      UPDATE cash_register_movements
      SET type = :type,
          amount = :amount,
          concept = :concept,
          notes = :notes
      WHERE id = :id
      `,
      {
        id,
        type,
        amount,
        concept: concept.trim(),
        notes: notes || null
      }
    );

    const [[session]] = await pool.query("SELECT * FROM cash_register_sessions WHERE id = ?", [rows[0].session_id]);
    res.json({ success: true, message: "Movimiento actualizado", data: await buildSummary(session) });
  } catch (err) {
    next(err);
  }
}

export async function deleteCashMovement(req, res, next) {
  try {
    await ensureCashTables();
    const { id } = req.params;

    const [rows] = await pool.query(
      `
      SELECT m.*, s.status
      FROM cash_register_movements m
      JOIN cash_register_sessions s ON s.id = m.session_id
      WHERE m.id = ?
      `,
      [id]
    );
    if (!rows.length) {
      return res.status(404).json({ success: false, message: "Movimiento no encontrado" });
    }
    if (rows[0].status !== "open") {
      return res.status(400).json({ success: false, message: "No puedes eliminar movimientos de una caja cerrada" });
    }

    await pool.query("DELETE FROM cash_register_movements WHERE id = ?", [id]);

    const [[session]] = await pool.query("SELECT * FROM cash_register_sessions WHERE id = ?", [rows[0].session_id]);
    res.json({ success: true, message: "Movimiento eliminado", data: await buildSummary(session) });
  } catch (err) {
    next(err);
  }
}

export async function closeCashRegister(req, res, next) {
  try {
    await ensureCashTables();
    const session = await getSessionByDate();
    if (!session || session.status !== "open") {
      return res.status(400).json({ success: false, message: "No hay una caja abierta para cerrar" });
    }

    const countedCash = Number(req.body.countedCash || 0);
    if (countedCash < 0) {
      return res.status(400).json({ success: false, message: "El efectivo contado no puede ser negativo" });
    }

    const summary = await buildSummary(session);
    const expectedCash = summary.totals.expectedCash;
    const difference = countedCash - expectedCash;

    await pool.query(
      `
      UPDATE cash_register_sessions
      SET status = 'closed',
          closing_amount = :countedCash,
          expected_cash = :expectedCash,
          difference_amount = :difference,
          closed_by = :closedBy,
          notes = :notes,
          close_breakdown = :closeBreakdown,
          closed_at = NOW()
      WHERE id = :id
      `,
      {
        id: session.id,
        countedCash,
        expectedCash,
        difference,
        closedBy: req.user?.id || null,
        notes: req.body.notes || session.notes || null,
        closeBreakdown: req.body.breakdown ? JSON.stringify(req.body.breakdown) : null
      }
    );

    const [[closed]] = await pool.query("SELECT * FROM cash_register_sessions WHERE id = ?", [session.id]);
    res.json({ success: true, message: "Caja cerrada correctamente", data: await buildSummary(closed) });
  } catch (err) {
    next(err);
  }
}

export async function openCashShift(req, res, next) {
  try {
    await ensureCashTables();
    const session = await getSessionByDate();
    if (!session || session.status !== "open") {
      return res.status(400).json({ success: false, message: "Debes abrir la caja diaria antes de abrir un turno" });
    }
    const activeShift = await getActiveShift(session.id);
    if (activeShift) {
      return res.status(400).json({ success: false, message: "Ya hay un turno abierto" });
    }

    const summary = await buildSummary(session);
    const openingAmount = req.body.openingAmount === undefined || req.body.openingAmount === null || req.body.openingAmount === ""
      ? summary.totals.expectedCash
      : Number(req.body.openingAmount || 0);
    if (openingAmount < 0) {
      return res.status(400).json({ success: false, message: "El monto inicial del turno no puede ser negativo" });
    }

    const [[counter]] = await pool.query("SELECT COUNT(*) AS total FROM cash_register_shifts WHERE session_id = ?", [session.id]);
    const [result] = await pool.query(
      `
      INSERT INTO cash_register_shifts (session_id, shift_number, opening_amount, opened_by, notes, opened_at)
      VALUES (:sessionId, :shiftNumber, :openingAmount, :openedBy, :notes, NOW())
      `,
      {
        sessionId: session.id,
        shiftNumber: Number(counter.total || 0) + 1,
        openingAmount,
        openedBy: req.user?.id || null,
        notes: req.body.notes || null
      }
    );

    const [[shift]] = await pool.query("SELECT * FROM cash_register_shifts WHERE id = ?", [result.insertId]);
    res.status(201).json({ success: true, message: "Turno abierto correctamente", data: await buildShiftSummary(shift) });
  } catch (err) {
    next(err);
  }
}

export async function closeCashShift(req, res, next) {
  try {
    await ensureCashTables();
    const session = await getSessionByDate();
    if (!session || session.status !== "open") {
      return res.status(400).json({ success: false, message: "No hay caja diaria abierta" });
    }
    const shift = await getActiveShift(session.id);
    if (!shift) {
      return res.status(400).json({ success: false, message: "No hay un turno abierto para cerrar" });
    }

    const countedCash = Number(req.body.countedCash || 0);
    if (countedCash < 0) {
      return res.status(400).json({ success: false, message: "El efectivo contado no puede ser negativo" });
    }

    const summary = await buildShiftSummary(shift);
    const expectedCash = summary.totals.expectedCash;
    const difference = countedCash - expectedCash;
    await pool.query(
      `
      UPDATE cash_register_shifts
      SET status = 'closed',
          closing_amount = :countedCash,
          expected_cash = :expectedCash,
          difference_amount = :difference,
          closed_by = :closedBy,
          notes = :notes,
          closed_at = NOW()
      WHERE id = :id
      `,
      {
        id: shift.id,
        countedCash,
        expectedCash,
        difference,
        closedBy: req.user?.id || null,
        notes: req.body.notes || shift.notes || null
      }
    );

    const [[closed]] = await pool.query("SELECT * FROM cash_register_shifts WHERE id = ?", [shift.id]);
    res.json({ success: true, message: "Turno cerrado correctamente", data: await buildShiftSummary(closed) });
  } catch (err) {
    next(err);
  }
}
