<?php
declare(strict_types=1);

require_once __DIR__ . '/_auth.php';
require_once __DIR__ . '/_tax.php';

financial_require_access();

$pdo      = financial_db();
$branchId = financial_branch_id();
$userId   = financial_user_id();

function redirect_expense_import(string $type, string $message): void
{
    header('Location: expenses.php?' . $type . '=' . urlencode($message));
    exit;
}

function normalize_import_date(string $date): string
{
    $date = trim(str_replace(['"', "'"], '', $date));

    if ($date === '') return '';
    if (preg_match('/^\d{4}-\d{2}-\d{2}$/', $date)) return $date;
    if (preg_match('/^\d{4}\/\d{2}\/\d{2}$/', $date)) return str_replace('/', '-', $date);

    if (preg_match('/^\d{2}\/\d{2}\/\d{4}$/', $date)) {
        [$d, $m, $y] = explode('/', $date);
        return $y . '-' . $m . '-' . $d;
    }

    if (preg_match('/^\d{2}-\d{2}-\d{4}$/', $date)) {
        [$d, $m, $y] = explode('-', $date);
        return $y . '-' . $m . '-' . $d;
    }

    return '';
}

function normalize_import_amount(string $amount): float
{
    $amount = trim($amount);
    $amount = str_replace(['د.إ', 'AED', 'aed', ',', ' '], '', $amount);
    return is_numeric($amount) ? (float)$amount : 0.0;
}

function load_csv_rows(string $path): array
{
    $content = file_get_contents($path);
    if ($content === false) {
        throw new RuntimeException('تعذر قراءة ملف CSV');
    }

    if (substr($content, 0, 3) === "\xEF\xBB\xBF") {
        $content = substr($content, 3);
    }

    $firstLine = strtok($content, "\r\n") ?: '';
    $delimiter = substr_count($firstLine, ';') > substr_count($firstLine, ',') ? ';' : ',';

    $tmp = fopen('php://temp', 'r+');
    fwrite($tmp, $content);
    rewind($tmp);

    $rows = [];
    while (($data = fgetcsv($tmp, 10000, $delimiter)) !== false) {
        $rows[] = $data;
    }

    fclose($tmp);
    return $rows;
}

function load_excel_rows(string $path): array
{
    $autoload = __DIR__ . '/vendor/autoload.php';
    if (!is_file($autoload)) {
        throw new RuntimeException('ملفات Excel تحتاج مكتبة PhpSpreadsheet داخل financial/vendor. استخدم CSV أو ارفع vendor كامل.');
    }

    require_once $autoload;

    if (!class_exists('\PhpOffice\PhpSpreadsheet\IOFactory')) {
        throw new RuntimeException('مكتبة PhpSpreadsheet غير مكتملة داخل financial/vendor');
    }

    $spreadsheet = \PhpOffice\PhpSpreadsheet\IOFactory::load($path);
    return $spreadsheet->getActiveSheet()->toArray(null, true, true, false);
}

function load_import_rows(string $path, string $ext): array
{
    return $ext === 'csv' ? load_csv_rows($path) : load_excel_rows($path);
}

if ($_SERVER['REQUEST_METHOD'] !== 'POST') {
    redirect_expense_import('err', 'طلب غير صحيح');
}

if (empty($_FILES['csv_file']['tmp_name']) || !is_uploaded_file((string)$_FILES['csv_file']['tmp_name'])) {
    redirect_expense_import('err', 'لم يتم اختيار ملف');
}

$fileName = (string)$_FILES['csv_file']['name'];
$tmpName  = (string)$_FILES['csv_file']['tmp_name'];
$fileSize = (int)($_FILES['csv_file']['size'] ?? 0);
$ext      = strtolower(pathinfo($fileName, PATHINFO_EXTENSION));

if (!in_array($ext, ['csv', 'xlsx', 'xls'], true)) {
    redirect_expense_import('err', 'صيغة الملف غير مدعومة. الصيغ المسموحة: CSV / XLSX / XLS');
}

if ($fileSize <= 0) {
    redirect_expense_import('err', 'الملف فارغ');
}

if ($fileSize > 10 * 1024 * 1024) {
    redirect_expense_import('err', 'حجم الملف أكبر من 10MB');
}

$uploadDir = __DIR__ . '/uploads/imports/';
if (!is_dir($uploadDir) && !mkdir($uploadDir, 0755, true)) {
    redirect_expense_import('err', 'تعذر إنشاء مجلد الاستيراد');
}

$storedName = 'expense_import_' . date('YmdHis') . '_' . bin2hex(random_bytes(5)) . '.' . $ext;
$filePath   = 'uploads/imports/' . $storedName;
$savedPath  = $uploadDir . $storedName;

if (!move_uploaded_file($tmpName, $savedPath)) {
    redirect_expense_import('err', 'فشل حفظ ملف الاستيراد');
}

try {
    $rows = load_import_rows($savedPath, $ext);
    $expenseCols = financial_table_columns($pdo, 'financial_expenses');

    $hasImportFileId   = in_array('import_file_id', $expenseCols, true);
    $hasVatAmount      = in_array('vat_amount', $expenseCols, true);
    $hasNetAmount      = in_array('net_amount', $expenseCols, true);
    $hasVatRecoverable = in_array('is_vat_recoverable', $expenseCols, true);

    $hasImportFilesTable = financial_has_table($pdo, 'financial_import_files');

    $pdo->beginTransaction();

    $importFileId = null;
    if ($hasImportFilesTable) {
        $stFile = $pdo->prepare("\n            INSERT INTO financial_import_files\n            (branch_id, import_type, original_name, stored_name, file_path, file_ext, file_size, imported_rows, skipped_rows, status, created_by)\n            VALUES (?, 'expense', ?, ?, ?, ?, ?, 0, 0, 'pending', ?)\n        ");
        $stFile->execute([$branchId, $fileName, $storedName, $filePath, $ext, $fileSize, $userId > 0 ? $userId : null]);
        $importFileId = (int)$pdo->lastInsertId();
    }

    $inserted = 0;
    $skipped  = 0;
    $yearsTouched = [];

    foreach ($rows as $index => $data) {
        if ($index === 0) continue;

        $expenseDate = normalize_import_date((string)($data[0] ?? ''));
        $title       = trim((string)($data[1] ?? ''));
        $category    = trim((string)($data[2] ?? ''));
        $amount      = normalize_import_amount((string)($data[3] ?? ''));
        $notes       = trim((string)($data[4] ?? ''));

        if ($expenseDate === '' || $title === '' || $category === '' || $amount <= 0) {
            $skipped++;
            continue;
        }

        [$vatAmount, $netAmount] = financial_tax_from_gross($amount);

        $fields = ['branch_id', 'title', 'category', 'amount', 'expense_date', 'notes'];
        $values = ['?', '?', '?', '?', '?', '?'];
        $params = [$branchId, $title, $category, $amount, $expenseDate, $notes !== '' ? $notes : null];

        if ($hasVatAmount) { $fields[] = 'vat_amount'; $values[] = '?'; $params[] = $vatAmount; }
        if ($hasNetAmount) { $fields[] = 'net_amount'; $values[] = '?'; $params[] = $netAmount; }
        if ($hasVatRecoverable) { $fields[] = 'is_vat_recoverable'; $values[] = '?'; $params[] = 1; }
        if ($hasImportFileId && $importFileId !== null) { $fields[] = 'import_file_id'; $values[] = '?'; $params[] = $importFileId; }

        $sql = 'INSERT INTO financial_expenses (' . implode(', ', $fields) . ') VALUES (' . implode(', ', $values) . ')';
        $st = $pdo->prepare($sql);
        $st->execute($params);

        $yearsTouched[(int)substr($expenseDate, 0, 4)] = true;
        $inserted++;
    }

    if ($hasImportFilesTable && $importFileId !== null) {
        $status = $inserted > 0 ? 'completed' : 'failed';
        $errorMessage = $inserted > 0 ? null : 'لم يتم استيراد أي صف صالح من الملف';
        $stUpdate = $pdo->prepare("\n            UPDATE financial_import_files\n            SET imported_rows = ?, skipped_rows = ?, status = ?, error_message = ?\n            WHERE id = ?\n        ");
        $stUpdate->execute([$inserted, $skipped, $status, $errorMessage, $importFileId]);
    }

    foreach (array_keys($yearsTouched) as $year) {
        financial_recalculate_tax_summary($pdo, $branchId, (int)$year);
    }

    $pdo->commit();

    redirect_expense_import('msg', 'تم استيراد ' . $inserted . ' مصروف واحتساب VAT تلقائياً، وتم تجاهل ' . $skipped . ' صف');

} catch (Throwable $e) {
    if ($pdo->inTransaction()) $pdo->rollBack();

    redirect_expense_import('err', 'تعذر استيراد الملف: ' . $e->getMessage());
}
