<?php
require_once __DIR__ . '/../../includes/auth.php';
requireLogin();

require_once __DIR__ . '/../../config/database.php';
require_once __DIR__ . '/SettlementEngine.php';

$companyId = $_SESSION['company_id'];
/* ===============================
   GET SETTLEMENT PERIOD
================================= */

$period = $_POST['period'] ?? '';

if (!$period) {
    die("Please select settlement period.");
}

$periodStart = date('Y-m-01', strtotime($period));
$periodEnd   = date('Y-m-t', strtotime($period));
/* ===============================
   BLOCK DUPLICATE MONTH
================================= */

$stmt = $pdo->prepare("
    SELECT id 
    FROM settlement_runs
    WHERE company_id = ?
    AND period_start = ?
    LIMIT 1
");

$stmt->execute([$companyId, $periodStart]);

if ($stmt->fetch()) {
    die("⚠️ Settlement for this month already exists.");
}

$engine = new SettlementEngine($pdo, $companyId);

if (!isset($_FILES['settlement_file'])) {
    die("No file uploaded.");
}

$fileName = $_FILES['settlement_file']['name'];
$fileTmp  = $_FILES['settlement_file']['tmp_name'];

$extension = strtolower(pathinfo($fileName, PATHINFO_EXTENSION));
if ($extension !== 'csv') {
    die("Please upload CSV file only.");
}

/* ===============================
   READ FILE + AUTO DETECT DELIMITER
================================= */
$handle = fopen($fileTmp, 'r');
if (!$handle) {
    die("Unable to read file.");
}

$firstLine = fgets($handle);
rewind($handle);

$delimiter = (substr_count($firstLine, ';') > substr_count($firstLine, ',')) ? ';' : ',';

$rows = [];
while (($data = fgetcsv($handle, 0, $delimiter)) !== false) {
    $rows[] = $data;
}
fclose($handle);

if (count($rows) < 2) {
    die("CSV has no data.");
}

/* ===============================
   NORMALIZE HEADER
================================= */
$header = array_map(function($h) {
    return strtolower(trim($h));
}, $rows[0]);

$required = [
    'department',
    'bank account number',
    'bank branch code',
    'bank account type',
    'landlord amount'
];

foreach ($required as $col) {
    if (!in_array($col, $header)) {
        die("Missing column: " . ucfirst($col));
    }
}

$departmentIndex = array_search('department', $header);
$bankIndex       = array_search('bank account number', $header);
$branchIndex     = array_search('bank branch code', $header);
$typeIndex       = array_search('bank account type', $header);
$amountIndex     = array_search('landlord amount', $header);

/* ===============================
   HELPERS
================================= */
function parse_amount($value): float {
    $value = (string)$value;
    $value = str_replace([" ", "R"], "", $value);

    if (substr_count($value, ",") === 1 && substr_count($value, ".") === 0) {
        $value = str_replace(",", ".", $value);
    } else {
        $value = str_replace(",", "", $value);
    }
    return (float)$value;
}

function normalize_type($type): string {
    $t = strtolower(trim((string)$type));
    if ($t === '1' || $t === '2') return $t;
    if (strpos($t, 'cheq') !== false || strpos($t, 'current') !== false) return '1';
    if (strpos($t, 'sav') !== false) return '2';
    return '';
}

function valid_account_number($acc): bool {
    $acc = trim((string)$acc);
    return preg_match('/^[0-9]{6,12}$/', $acc) === 1;
}

function normalize_branch_code($branch): string {
    $branch = trim((string)$branch);
    $branch = preg_replace('/[^0-9]/', '', $branch);
    if ($branch === '') return '';
    return str_pad($branch, 6, '0', STR_PAD_LEFT);
}

function valid_branch_code($branch): bool {
    return preg_match('/^[0-9]{6}$/', $branch) === 1;
}

function valid_account_type($type): bool {
    return in_array(trim((string)$type), ['1','2'], true);
}

/* ===============================
   GROUP DATA
================================= */
$grouped = [];

for ($i = 1; $i < count($rows); $i++) {

    $row = $rows[$i];
    if (!isset($row[$departmentIndex])) continue;

    $departmentRaw = trim((string)$row[$departmentIndex]);
    // REMOVE MULTIPLE SPACES
$departmentRaw = preg_replace('/\s+/', ' ', $departmentRaw);

// FORCE UPPERCASE
$departmentRaw = strtoupper($departmentRaw);
    if ($departmentRaw === '') continue;

    $departmentFull = $departmentRaw;
    $parts = preg_split('/\s+/', $departmentRaw, 2);
    $departmentCode = strtoupper(trim($departmentRaw));
    $departmentName = $parts[1] ?? '';

    $bank   = trim((string)($row[$bankIndex] ?? ''));
    $branch = normalize_branch_code($row[$branchIndex] ?? '');
    $type   = normalize_type($row[$typeIndex] ?? '');
    $amount = parse_amount($row[$amountIndex] ?? 0);

    $rowValid = valid_account_number($bank) 
        && valid_branch_code($branch) 
        && valid_account_type($type);

    if (!isset($grouped[$departmentCode])) {
        $grouped[$departmentCode] = [
            'department_code' => $departmentCode,
            'department_name' => $departmentName,
            'department_full' => $departmentFull,
            'bank' => $bank,
            'branch' => $branch,
            'type' => $type,
            'month_total' => 0,
            'valid' => $rowValid
        ];
    }

    if (!$rowValid) {
        $grouped[$departmentCode]['valid'] = false;
    }

    $grouped[$departmentCode]['month_total'] += $amount;
}

/* ===============================
   PROCESS ENGINE + BANK MATCH
================================= */
$results = [];

foreach ($grouped as $dept => $data) {

    if (!$data['valid']) {
        $results[] = [
            'department_code' => $dept,
            'department_name' => $data['department_name'],
            'department_full' => $data['department_full'],
            'bank' => $data['bank'],
            'branch' => $data['branch'],
            'type' => $data['type'],
            'month_total' => $data['month_total'],
            'carry_in' => 0.00,
            'payable' => 0.00,
            'status' => 'HOLD',
            'new_balance' => $data['month_total'],
            'db_bank' => null,
            'db_branch' => null
        ];
        continue;
    }

    // Fetch verified banking from DB
   $stmt = $pdo->prepare("
    SELECT bank_account, branch_code
    FROM department_banking_details
    WHERE company_id = ?
    AND TRIM(LOWER(department_code)) = TRIM(LOWER(?))
    LIMIT 1
");
    $stmt->execute([$companyId, $dept]);
    $dbBankRow = $stmt->fetch(PDO::FETCH_ASSOC);

    $dbBank   = $dbBankRow['bank_account'] ?? null;
    $dbBranch = $dbBankRow['branch_code'] ?? null;

    $engineResult = $engine->process($dept, $data['month_total'], false);

    $finalStatus = $engineResult['status'];

    /* ===============================
   NORMALIZE BANK DATA
================================= */

// clean account numbers (digits only)
$csvBankClean = preg_replace('/\D/', '', $data['bank']);
$dbBankClean  = preg_replace('/\D/', '', (string)$dbBank);

// normalize branch codes (pad to 6 digits)
$csvBranchClean = str_pad(preg_replace('/\D/', '', $data['branch']), 6, '0', STR_PAD_LEFT);
$dbBranchClean  = str_pad(preg_replace('/\D/', '', (string)$dbBranch), 6, '0', STR_PAD_LEFT);

/* ===============================
   COMPARE NORMALIZED VALUES
================================= */

if ($dbBank === null || $dbBranch === null) {
    $finalStatus = 'BANK_MISMATCH';
} else {
    if ($csvBankClean !== $dbBankClean || $csvBranchClean !== $dbBranchClean) {
        $finalStatus = 'BANK_MISMATCH';
    }
}

    $results[] = [
        'department_code' => $dept,
        'department_name' => $data['department_name'],
        'department_full' => $data['department_full'],
        'bank' => $data['bank'],
        'branch' => $data['branch'],
        'type' => $data['type'],
        'month_total' => $engineResult['month_total'],
        'carry_in' => $engineResult['carry_in'],
        'payable' => $engineResult['payable'],
        'status' => $finalStatus,
        'new_balance' => $engineResult['new_balance'],
        'db_bank' => $dbBank,
        'db_branch' => $dbBranch
    ];
}

$_SESSION['settlement_preview'] = $results;
$_SESSION['settlement_filename'] = $fileName;
$_SESSION['settlement_period_start'] = $periodStart;
$_SESSION['settlement_period_end']   = $periodEnd;

header("Location: view.php");
exit;