<?php
include("connect.php");
require 'vendor/autoload.php';

use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Shared\Date;

$message = "";

/* ✅ Excel date convert function */
function excelDateToYmd($excelDate)
{
    if (empty($excelDate)) {
        return null;
    }

    // 1️⃣ Excel serial number (most common)
    if (is_numeric($excelDate)) {
        return Date::excelToDateTimeObject($excelDate)->format('Y-m-d');
    }

    // 2️⃣ Clean spaces (30 -09 -2025 → 30-09-2025)
    $excelDate = preg_replace('/\s+/', '', $excelDate);

    // 3️⃣ Try exact formats
    $formats = [
        'd-m-Y',
        'd/m/Y',
        'Y-m-d',
        'Y/m/d'
    ];

    foreach ($formats as $format) {
        $dt = DateTime::createFromFormat($format, $excelDate);
        if ($dt !== false) {
            return $dt->format('Y-m-d');
        }
    }

    // 4️⃣ Last fallback
    $timestamp = strtotime($excelDate);
    if ($timestamp !== false) {
        return date('Y-m-d', $timestamp);
    }

    return null; // invalid date
}



if (isset($_POST['upload'])) {

    if (!empty($_FILES['excel_file']['name'])) {

        $fileName = $_FILES['excel_file']['tmp_name'];

        $spreadsheet = IOFactory::load($fileName);
        $sheet = $spreadsheet->getActiveSheet();
        $rows = $sheet->toArray();

        // Skip header row
        unset($rows[0]);

        foreach ($rows as $row) {

            // Skip empty rows
            if (empty($row[0]) && empty($row[3])) {
                continue;
            }

            $transaction_date = excelDateToYmd($row[0]);
            $value_date       = excelDateToYmd($row[1]);
            $instrument_no    = trim($row[2]);
            $narration        = trim($row[3]);

            $debit_amount  = is_numeric($row[4]) ? $row[4] : 0;
            $credit_amount = is_numeric($row[5]) ? $row[5] : 0;
            $balance = trim($row[6]);

            $stmt = mysqli_prepare(
                $conn,
                "INSERT INTO fdr_statement 
                (transaction_date, value_date, instrument_no, narration, debit_amount, credit_amount, balance)
                VALUES (?,?,?,?,?,?,?)"
            );

            mysqli_stmt_bind_param(
                $stmt,
                "ssssdds",
                $transaction_date,
                $value_date,
                $instrument_no,
                $narration,
                $debit_amount,
                $credit_amount,
                $balance
            );

            mysqli_stmt_execute($stmt);
        }

        $message = "FDR Excel uploaded & data imported successfully ✅";
    } else {
        $message = "Please select an Excel file ❌";
    }
}
?>

<!DOCTYPE html>
<html>

<head>
    <meta charset="UTF-8">
    <title>Upload FDR</title>

    <style>
        body {
            margin: 0;
            font-family: Arial, sans-serif;
            background: #f4f6f9;
        }

        .content {
            margin-left: 250px;
            padding: 30px;
            padding-top: 90px;
        }

        .card {
            width: 450px;
            margin: 80px auto;
            background: #fff;
            padding: 30px;
            border-radius: 10px;
            box-shadow: 0 0 10px rgba(0, 0, 0, .1);
        }

        button {
            width: 100%;
            padding: 12px;
            background: #28a745;
            color: #fff;
            border: none;
            border-radius: 5px;
            font-size: 16px;
        }

        .msg {
            text-align: center;
            margin-bottom: 10px;
            color: green;
        }

        .header {
            height: 60px;
            background: #ffffff;
            border-bottom: 1px solid #ddd;
            position: fixed;
            top: 0;
            left: 250px;
            /* sidebar width */
            right: 0;
            display: flex;
            align-items: center;
            justify-content: space-between;
            padding: 0 20px;
            box-shadow: 0 2px 5px rgba(0, 0, 0, 0.05);
            z-index: 1000;
        }

        .header-left {
            font-size: 16px;
            font-weight: bold;
        }

        .logout-btn {
            background: #dc3545;
            color: #fff;
            padding: 8px 14px;
            text-decoration: none;
            border-radius: 5px;
            font-size: 14px;
        }

        .logout-btn:hover {
            background: #b52a37;
        }
    </style>
</head>

<body>

    <?php include("sidebar.php"); ?>
    <?php include("header.php"); ?>

    <div class="content">
        <div class="card">
            <h2>Upload FDR Statement</h2>

            <?php if ($message != "") { ?>
                <p class="msg"><?php echo $message; ?></p>
            <?php } ?>

            <form method="post" enctype="multipart/form-data">
                <input type="file" name="excel_file" accept=".xlsx,.xls" required>
                <br><br>
                <button type="submit" name="upload">Upload & Import</button>
            </form>
        </div>
    </div>

</body>

</html>