<?php
// pages/submit_custom_plan.php — apply/authorize custom installment plans (FIXED + STABLE)
declare(strict_types=1);

@date_default_timezone_set('Asia/Colombo');
header('Content-Type: application/json; charset=utf-8');

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
if (session_status() !== PHP_SESSION_ACTIVE) session_start();

/* ===================== DB bootstrap ===================== */
$mysqli = null;

if (is_file(__DIR__ . '/../includes/auth.php')) {
  require_once __DIR__ . '/../includes/auth.php';
  if (function_exists('ec_db')) {
    $db = ec_db();
    if ($db instanceof mysqli) $mysqli = $db;
  }
}

if (!$mysqli) {
  $mysqli = new mysqli('localhost', 'root', '', 'edencamp_edencampus');
}
$mysqli->set_charset('utf8mb4');

/* ===================== response helpers ===================== */
$ok = function(array $data = [], int $code = 200) {
  http_response_code($code);
  if (!array_key_exists('ok', $data)) $data['ok'] = ($code >= 200 && $code < 300);
  echo json_encode($data, JSON_UNESCAPED_UNICODE);
  exit;
};
$err = function(string $msg, int $code = 400) use ($ok) {
  $ok(['ok'=>false, 'message'=>$msg], $code);
};

function now(): string { return date('Y-m-d H:i:s'); }
function arr($x): array { return is_array($x) ? $x : []; }

function toMoney($v): float {
  if ($v === null || $v === '') return 0.0;
  if (is_numeric($v)) return (float)$v;
  $v = preg_replace('/[^\d.\-]/', '', (string)$v);
  return (float)$v;
}

function tableExists(mysqli $db, string $t): bool {
  $st = $db->prepare("SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME=?");
  $st->bind_param('s', $t);
  $st->execute();
  $st->store_result();
  $ok = $st->num_rows > 0;
  $st->free_result();
  $st->close();
  return $ok;
}

function colExists(mysqli $db, string $t, string $c): bool {
  $st = $db->prepare("SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME=? AND COLUMN_NAME=?");
  $st->bind_param('ss', $t, $c);
  $st->execute();
  $st->store_result();
  $ok = $st->num_rows > 0;
  $st->free_result();
  $st->close();
  return $ok;
}

/**
 * Prefer payment_schedule, else installments. If neither exist, create payment_schedule.
 */
function scheduleTable(mysqli $db): string {
  if (tableExists($db,'payment_schedule')) return 'payment_schedule';
  if (tableExists($db,'installments'))     return 'installments';

  $db->query("CREATE TABLE IF NOT EXISTS payment_schedule (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `student_id` int(10) UNSIGNED NOT NULL,
    `plan_id` int(10) UNSIGNED DEFAULT NULL,
    `course_id` int(10) UNSIGNED DEFAULT NULL,
    `installment_no` int(10) UNSIGNED NOT NULL,
    `due_date` date DEFAULT NULL,
    `course_fee_lkr` decimal(12,2) NOT NULL DEFAULT 0.00,
    `registration_fee_lkr` decimal(12,2) NOT NULL DEFAULT 0.00,
    `certificate_fee_usd` decimal(10,2) NOT NULL DEFAULT 0.00,
    `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
    PRIMARY KEY (`id`),
    KEY `student_id` (`student_id`),
    KEY `plan_id` (`plan_id`),
    KEY `course_id` (`course_id`),
    KEY `installment_no` (`installment_no`)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");

  return 'payment_schedule';
}

/* ===================== auth / role ===================== */
$role = '';
if (function_exists('ec_role')) $role = strtolower((string)ec_role());
elseif (!empty($_SESSION['role'])) $role = strtolower((string)$_SESSION['role']);

$userId = (int)($_SESSION['user']['id'] ?? $_SESSION['id'] ?? 0);
if ($userId <= 0) $err('Not logged in.', 401);

$canDirectApply = in_array($role, ['super_admin','admin','branch_admin'], true);

/* ===================== inputs ===================== */
$student_id = (int)($_POST['student_id'] ?? 0);
$course_id  = (int)($_POST['course_id']  ?? 0);  // IMPORTANT for bundle schedules
if ($student_id <= 0) $err('Missing student_id.', 400);

$mode       = strtolower(trim((string)($_POST['mode'] ?? 'authorize'))); // authorize | apply
$plan_label = trim((string)($_POST['plan_label'] ?? 'Custom'));
$notes      = trim((string)($_POST['reason'] ?? $_POST['notes'] ?? '')); // FIX: accept "reason"
if ($notes === '') $err('Reason is required.', 400);

// optional: UI can send exact sfp id (recommended)
$student_fee_plan_id = (int)($_POST['student_fee_plan_id'] ?? 0);

/* ===================== parse rows ===================== */
// rows: accept rows_json OR array fields (no[]/course_lkr[]/reg_lkr[]/cert_usd[]/due_date[])
$rows = [];

if (is_string($_POST['rows_json'] ?? null) && (string)$_POST['rows_json'] !== '') {
  $tmp = json_decode((string)$_POST['rows_json'], true);
  if (is_array($tmp)) {
    foreach ($tmp as $r) {
      $n = (int)($r['n'] ?? $r['no'] ?? 0);
      $c = toMoney($r['course_fee'] ?? $r['course'] ?? $r['course_lkr'] ?? 0);
      $g = toMoney($r['reg_fee']    ?? $r['reg']    ?? $r['reg_lkr']    ?? 0);
      $t = toMoney($r['cert_fee']   ?? $r['cert']   ?? $r['cert_usd']   ?? 0);
      $d = trim((string)($r['due_date'] ?? $r['due'] ?? ''));

      if ($n <= 0) $n = count($rows) + 1;
      $rows[] = [$n,$c,$g,$t,$d];
    }
  }
} else {
  $n = arr($_POST['no']         ?? $_POST['n']        ?? $_POST['num'] ?? []);
  $c = arr($_POST['course_lkr'] ?? $_POST['course']   ?? $_POST['course_fee'] ?? []);
  $g = arr($_POST['reg_lkr']    ?? $_POST['reg']      ?? $_POST['reg_fee']    ?? []);
  $t = arr($_POST['cert_usd']   ?? $_POST['cert']     ?? $_POST['cert_fee']   ?? []);
  $d = arr($_POST['due_date']   ?? $_POST['due']      ?? []);

  $m = max(count($n), count($c), count($g), count($t), count($d));
  for ($i=0; $i<$m; $i++) {
    $ni = isset($n[$i]) ? (int)$n[$i] : ($i+1);
    if ($ni <= 0) $ni = $i + 1;

    $ci = isset($c[$i]) ? toMoney($c[$i]) : 0.0;
    $gi = isset($g[$i]) ? toMoney($g[$i]) : 0.0;
    $ti = isset($t[$i]) ? toMoney($t[$i]) : 0.0;
    $di = isset($d[$i]) ? trim((string)$d[$i]) : '';

    $rows[] = [$ni,$ci,$gi,$ti,$di];
  }
}

// keep only rows with any positive amount
$rows = array_values(array_filter($rows, fn($r) => ((float)$r[1] + (float)$r[2] + (float)$r[3]) > 0.0));
if (!$rows) $err('No installment rows.', 400);

// normalize: sort by installment_no and make them unique if user repeats numbers
usort($rows, function($a,$b){ return (int)$a[0] <=> (int)$b[0]; });
$seenNo = [];
foreach ($rows as $i => $r) {
  $no = (int)$r[0];
  if ($no <= 0 || isset($seenNo[$no])) $no = $i + 1; // reindex safely
  $seenNo[$no] = true;
  $rows[$i][0] = $no;

  // due date normalize to YYYY-MM-DD or empty
  $dd = trim((string)$rows[$i][4]);
  if ($dd !== '' && !preg_match('/^\d{4}-\d{2}-\d{2}$/', $dd)) {
    // try strtotime fallback
    $ts = strtotime($dd);
    $rows[$i][4] = $ts ? date('Y-m-d', $ts) : '';
  }
}

/* ===================== helper: latest student_fee_plans.id ===================== */
function latestSfpId(mysqli $db, int $student_id, ?int $course_id): ?int {
  if (!tableExists($db,'student_fee_plans')) return null;

  $hasCourse = colExists($db,'student_fee_plans','course_id');
  if ($hasCourse && $course_id && $course_id > 0) {
    $st = $db->prepare("SELECT id FROM student_fee_plans WHERE student_id=? AND course_id=? ORDER BY id DESC LIMIT 1");
    $st->bind_param('ii', $student_id, $course_id);
  } else {
    $st = $db->prepare("SELECT id FROM student_fee_plans WHERE student_id=? ORDER BY id DESC LIMIT 1");
    $st->bind_param('i', $student_id);
  }

  $st->execute();
  $res = method_exists($st,'get_result') ? $st->get_result() : null;
  $row = $res ? $res->fetch_assoc() : null;
  $st->close();

  return $row ? (int)$row['id'] : null;
}

/* ===================== helper: pending approval id ===================== */
function findPendingApprovalRequestId(mysqli $db, int $student_id): int {
  if (!tableExists($db,'approval_requests')) return 0;

  $st = $db->prepare("SELECT id FROM approval_requests
                      WHERE student_id=? AND type='custom_plan' AND status='pending'
                      ORDER BY id DESC LIMIT 1");
  $st->bind_param('i', $student_id);
  $st->execute();
  $res = method_exists($st,'get_result') ? $st->get_result() : null;
  $row = $res ? $res->fetch_assoc() : null;
  $st->close();

  return (int)($row['id'] ?? 0);
}

function findPendingApprovalsId(mysqli $db, int $student_id): int {
  if (!tableExists($db,'approvals')) return 0;

  $st = $db->prepare("SELECT id FROM approvals
                      WHERE student_id=? AND type='custom_plan' AND status='PENDING'
                      ORDER BY id DESC LIMIT 1");
  $st->bind_param('i', $student_id);
  $st->execute();
  $res = method_exists($st,'get_result') ? $st->get_result() : null;
  $row = $res ? $res->fetch_assoc() : null;
  $st->close();

  return (int)($row['id'] ?? 0);
}

/* ===================== APPLY (shared) ===================== */
$applyNow = function() use (
  $mysqli, $student_id, $course_id, $student_fee_plan_id, $rows, $ok, $err
) {
  $tbl = scheduleTable($mysqli);

  $has_plan    = colExists($mysqli, $tbl, 'plan_id');
  $has_course  = colExists($mysqli, $tbl, 'course_id');
  $has_c       = colExists($mysqli, $tbl, 'course_fee_lkr');
  $has_r       = colExists($mysqli, $tbl, 'registration_fee_lkr');
  $has_u       = colExists($mysqli, $tbl, 'certificate_fee_usd');
  $has_due     = colExists($mysqli, $tbl, 'due_date');
  $has_created = colExists($mysqli, $tbl, 'created_at');

  // IMPORTANT: if schedule table uses course_id, enforce course_id on request
  if ($has_course && $course_id <= 0) {
    $err('Missing course_id for this schedule.', 400);
  }

  // Preferred plan_id: explicit sfp id from UI, else latest student_fee_plans.id
  $planIdForSchedule = null;
  if ($has_plan) {
    if ($student_fee_plan_id > 0) $planIdForSchedule = $student_fee_plan_id;
    else $planIdForSchedule = latestSfpId($mysqli, $student_id, $has_course ? $course_id : null);

    // fallback: reuse previous schedule plan_id
    if ($planIdForSchedule === null) {
      $sel = $mysqli->prepare("SELECT plan_id FROM {$tbl} WHERE student_id=? ORDER BY id DESC LIMIT 1");
      $sel->bind_param('i', $student_id);
      $sel->execute();
      $res = method_exists($sel,'get_result') ? $sel->get_result() : null;
      $r   = $res ? $res->fetch_assoc() : null;
      $sel->close();
      if ($r && $r['plan_id'] !== null) $planIdForSchedule = (int)$r['plan_id'];
    }
  }

  $mysqli->begin_transaction();
  try {
    // 1) delete existing schedule rows (scoped by course_id if column exists)
    $delSql = "DELETE FROM {$tbl} WHERE student_id=?";
    $types  = 'i';
    $args   = [$student_id];

    if ($has_course && $course_id > 0) {
      $delSql .= " AND course_id=?";
      $types  .= 'i';
      $args[]  = $course_id;
    }

    $del = $mysqli->prepare($delSql);
    $del->bind_param($types, ...$args);
    $del->execute();
    $del->close();

    // 2) build insert statement
    $cols = ['student_id'];
    if ($has_plan)   $cols[] = 'plan_id';
    if ($has_course) $cols[] = 'course_id';
    $cols[] = 'installment_no';
    if ($has_due) $cols[] = 'due_date';
    if ($has_c)   $cols[] = 'course_fee_lkr';
    if ($has_r)   $cols[] = 'registration_fee_lkr';
    if ($has_u)   $cols[] = 'certificate_fee_usd';
    if ($has_created) $cols[] = 'created_at';

    $ph = [];
    foreach ($cols as $cname) {
      if ($cname === 'created_at') $ph[] = 'NOW()';
      else $ph[] = '?';
    }

    $sql = "INSERT INTO {$tbl} (" . implode(',', $cols) . ") VALUES (" . implode(',', $ph) . ")";
    $ins = $mysqli->prepare($sql);

    /* ===== FIX: bind_param must use references (bind once, execute many) ===== */
    $SID  = $student_id;
    $PID  = $planIdForSchedule;  // may be null but still ok if column exists
    $CID  = $course_id;          // required if $has_course

    $no   = 0;
    $due  = null;                // string|null
    $cFee = 0.0;
    $rFee = 0.0;
    $uFee = 0.0;

    $bindTypes = '';
    $bindRefs  = [];

    $push = function(string $t, &$v) use (&$bindTypes, &$bindRefs) {
      $bindTypes .= $t;
      $bindRefs[] = &$v;
    };

    foreach ($cols as $cname) {
      switch ($cname) {
        case 'student_id':           $push('i', $SID); break;
        case 'plan_id':              $push('i', $PID); break;          // ok even if null
        case 'course_id':            $push('i', $CID); break;
        case 'installment_no':       $push('i', $no); break;
        case 'due_date':             $push('s', $due); break;          // null ok
        case 'course_fee_lkr':       $push('d', $cFee); break;
        case 'registration_fee_lkr': $push('d', $rFee); break;
        case 'certificate_fee_usd':  $push('d', $uFee); break;
        case 'created_at':           /* NOW() */ break;
      }
    }

    if ($bindTypes !== '') {
      $ins->bind_param($bindTypes, ...$bindRefs);
    }

    foreach ($rows as [$n,$cVal,$rVal,$uVal,$dVal]) {
      $no   = (int)$n;
      $cFee = (float)$cVal;
      $rFee = (float)$rVal;
      $uFee = (float)$uVal;

      $dVal = trim((string)$dVal);
      $due  = ($dVal !== '') ? $dVal : null;

      $ins->execute();
    }

    $ins->close();

    // 3) mark any pending approvals as applied (optional cleanup)
    if (tableExists($mysqli,'approval_requests')) {
      $st = $mysqli->prepare("UPDATE approval_requests
                              SET status='applied', decided_at=NOW()
                              WHERE student_id=? AND type='custom_plan' AND status='pending'");
      $st->bind_param('i', $student_id);
      $st->execute();
      $st->close();
    }

    if (tableExists($mysqli,'approvals')) {
      $st = $mysqli->prepare("UPDATE approvals
                              SET status='APPROVED', decided_at=NOW()
                              WHERE student_id=? AND type='custom_plan' AND status='PENDING'");
      $st->bind_param('i', $student_id);
      $st->execute();
      $st->close();
    }

    $mysqli->commit();

    $ok([
      'message'      => 'Customize Installment Plan added',
      'message_code' => 'plan_applied',
      'action'       => 'applied',
      'applied'      => true,
      'tab'          => 'payments'
    ]);
  } catch (\Throwable $e) {
    try { $mysqli->rollback(); } catch (\Throwable $e2) {}
    $err('Apply failed: ' . $e->getMessage(), 500);
  }
};

/* ===================== dispatch ===================== */
if ($mode === 'apply') {
  $applyNow();
}

// authorize
if ($mode === 'authorize') {
  if ($canDirectApply) {
    // admins apply immediately
    $applyNow();
  }

  // staff: create/refresh a pending approval request
  $payload = json_encode([
    'student_id'   => $student_id,
    'course_id'    => $course_id ?: null,
    'student_fee_plan_id' => $student_fee_plan_id ?: null,
    'plan_label'   => $plan_label,
    'notes'        => $notes,
    'rows'         => array_map(fn($r) => [
      'n'        => (int)$r[0],
      'course_fee' => (float)$r[1],
      'reg_fee'    => (float)$r[2],
      'cert_fee'   => (float)$r[3],
      'due_date'   => (string)$r[4],
    ], $rows),
    'requested_by' => $userId,
    'requested_at' => now(),
    'type'         => 'custom_installment_plan',
  ], JSON_UNESCAPED_UNICODE);

  // Preferred: approval_requests table
  if (tableExists($mysqli,'approval_requests')) {
    $pendingId = findPendingApprovalRequestId($mysqli, $student_id);

    if ($pendingId > 0) {
      $st = $mysqli->prepare("UPDATE approval_requests
                              SET payload=?, requested_by=?, requested_at=NOW()
                              WHERE id=?");
      $st->bind_param('sii', $payload, $userId, $pendingId);
      $st->execute();
      $st->close();

      $ok([
        'message'      => 'Approval sent to admin',
        'message_code' => 'approval_sent',
        'action'       => 'requested',
        'pending'      => true,
        'mode'         => 'authorize',
        'request_id'   => $pendingId,
        'tab'          => 'payments'
      ]);
    }

    $st = $mysqli->prepare("INSERT INTO approval_requests (type,status,student_id,payload,requested_by,requested_at)
                            VALUES ('custom_plan','pending',?,?,?,NOW())");
    $st->bind_param('isi', $student_id, $payload, $userId);
    $st->execute();
    $rid = (int)$st->insert_id;
    $st->close();

    $ok([
      'message'      => 'Approval sent to admin',
      'message_code' => 'approval_sent',
      'action'       => 'requested',
      'pending'      => true,
      'mode'         => 'authorize',
      'request_id'   => $rid,
      'tab'          => 'payments'
    ]);
  }

  // Fallback: approvals table (create if missing)
  if (!tableExists($mysqli,'approvals')) {
    $mysqli->query("CREATE TABLE IF NOT EXISTS approvals (
      id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
      type VARCHAR(64) NOT NULL,
      subtype VARCHAR(64) NULL,
      student_id INT UNSIGNED NULL,
      requested_by INT UNSIGNED NULL,
      payload_json JSON NOT NULL,
      status ENUM('PENDING','APPROVED','REJECTED') NOT NULL DEFAULT 'PENDING',
      decision_note TEXT NULL,
      decided_by INT UNSIGNED NULL,
      decided_at DATETIME NULL,
      created_at DATETIME NOT NULL,
      INDEX(type), INDEX(student_id), INDEX(requested_by), INDEX(status)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
  }

  $pendingId = findPendingApprovalsId($mysqli, $student_id);

  if ($pendingId > 0) {
    $created = now();
    $st = $mysqli->prepare("UPDATE approvals
                            SET payload_json=?, requested_by=?, created_at=?
                            WHERE id=?");
    $st->bind_param('sisi', $payload, $userId, $created, $pendingId);
    $st->execute();
    $st->close();

    $ok([
      'message'      => 'Approval sent to admin',
      'message_code' => 'approval_sent',
      'action'       => 'requested',
      'pending'      => true,
      'mode'         => 'authorize',
      'request_id'   => $pendingId,
      'tab'          => 'payments'
    ]);
  }

  $sub = $plan_label !== '' ? $plan_label : null;
  $created = now();
  $st = $mysqli->prepare("INSERT INTO approvals (type,subtype,student_id,requested_by,payload_json,status,created_at)
                          VALUES ('custom_plan', ?, ?, ?, ?, 'PENDING', ?)");
  $st->bind_param('siiss', $sub, $student_id, $userId, $payload, $created);
  $st->execute();
  $rid = (int)$st->insert_id;
  $st->close();

  $ok([
    'message'      => 'Approval sent to admin',
    'message_code' => 'approval_sent',
    'action'       => 'requested',
    'pending'      => true,
    'mode'         => 'authorize',
    'request_id'   => $rid,
    'tab'          => 'payments'
  ]);
}

// fallback
$err('Unknown mode', 400);
