<?php
declare(strict_types=1);

require_once __DIR__ . '/_auth.php';
require_once __DIR__ . '/vendor/autoload.php';

use PhpOffice\PhpSpreadsheet\IOFactory;

financial_require_access();

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

const UAE_VAT_RATE = 0.05;

function redirect_import(string $type, string $message): void
{
    header('Location: import_files.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 financial_current_db(PDO $pdo): string
{
    return (string)$pdo->query('SELECT DATABASE()')->fetchColumn();
}

function financial_table_exists_safe(PDO $pdo, string $table): bool
{
    $db = financial_current_db($pdo);

    $st = $pdo->prepare("
        SELECT COUNT(*)
        FROM information_schema.TABLES
        WHERE TABLE_SCHEMA = :db
          AND TABLE_NAME = :table
    ");

    $st->execute([
        ':db' => $db,
        ':table' => $table,
    ]);

    return (int)$st->fetchColumn() > 0;
}

function financial_table_columns_safe(PDO $pdo, string $table): array
{
    $db = financial_current_db($pdo);

    $st = $pdo->prepare("
        SELECT COLUMN_NAME
        FROM information_schema.COLUMNS
        WHERE TABLE_SCHEMA = :db
          AND TABLE_NAME = :table
        ORDER BY ORDINAL_POSITION
    ");

    $st->execute([
        ':db' => $db,
        ':table' => $table,
    ]);

    return array_map('strval', $st->fetchAll(PDO::FETCH_COLUMN));
}

function add_import_field(array $cols, array &$fields, array &$values, array &$params, string $field, mixed $value): void
{
    if (in_array($field, $cols, true)) {
        $fields[] = $field;
        $values[] = '?';
        $params[] = $value;
    }
}

function load_import_rows(string $path, string $ext): array
{
    if ($ext === 'csv') {
        $rows = [];
        $handle = fopen($path, 'r');

        if (!$handle) {
            throw new RuntimeException('تعذر قراءة ملف CSV');
        }

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

        fclose($handle);
        return $rows;
    }

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

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

$importType = trim((string)($_POST['import_type'] ?? 'income'));

if (!in_array($importType, ['income', 'expense'], true)) {
    $importType = 'income';
}

if (empty($_FILES['csv_file']['tmp_name']) || !is_uploaded_file($_FILES['csv_file']['tmp_name'])) {
    redirect_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_import('err', 'صيغة الملف غير مدعومة. الصيغ المسموحة: CSV / XLSX / XLS');
}

$uploadDir = __DIR__ . '/uploads/imports/';

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

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

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

try {
    $table = $importType === 'expense'
        ? 'financial_expenses'
        : 'financial_income';

    if (!financial_table_exists_safe($pdo, $table)) {
        throw new RuntimeException('الجدول غير موجود: ' . $table);
    }

    $cols = financial_table_columns_safe($pdo, $table);

    $hasImportFilesTable = financial_table_exists_safe($pdo, 'financial_import_files');
    $rows = load_import_rows($savedPath, $ext);

    $pdo->beginTransaction();

    $importFileId = null;

    if ($hasImportFilesTable) {
        $stFile = $pdo->prepare("
            INSERT INTO financial_import_files
            (
                branch_id,
                import_type,
                original_name,
                stored_name,
                file_path,
                file_ext,
                file_size,
                imported_rows,
                skipped_rows,
                status,
                created_by
            )
            VALUES (?, ?, ?, ?, ?, ?, ?, 0, 0, 'pending', ?)
        ");

        $stFile->execute([
            $branchId,
            $importType,
            $fileName,
            $storedName,
            $filePath,
            $ext,
            $fileSize,
            $userId > 0 ? $userId : null,
        ]);

        $importFileId = (int)$pdo->lastInsertId();
    }

    $inserted = 0;
    $skipped  = 0;

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

        $date          = normalize_import_date((string)($data[0] ?? ''));
        $name          = trim((string)($data[1] ?? ''));
        $category      = trim((string)($data[2] ?? ''));
        $amount        = normalize_import_amount((string)($data[3] ?? ''));
        $paymentMethod = trim((string)($data[4] ?? ''));
        $notes         = trim((string)($data[5] ?? ''));

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

        if ($importType === 'income' && $paymentMethod === '') {
            $skipped++;
            continue;
        }

        $vatAmount = round($amount - ($amount / (1 + UAE_VAT_RATE)), 2);
        $netAmount = round($amount - $vatAmount, 2);

        $fields = [];
        $values = [];
        $params = [];

        if ($importType === 'income') {
            add_import_field($cols, $fields, $values, $params, 'branch_id', $branchId);
            add_import_field($cols, $fields, $values, $params, 'client_name', $name);
            add_import_field($cols, $fields, $values, $params, 'service_type', $category);
            add_import_field($cols, $fields, $values, $params, 'amount', $amount);
            add_import_field($cols, $fields, $values, $params, 'payment_method', $paymentMethod);
            add_import_field($cols, $fields, $values, $params, 'income_date', $date);
            add_import_field($cols, $fields, $values, $params, 'notes', $notes !== '' ? $notes : null);
            add_import_field($cols, $fields, $values, $params, 'vat_amount', $vatAmount);
            add_import_field($cols, $fields, $values, $params, 'net_amount', $netAmount);
            add_import_field($cols, $fields, $values, $params, 'is_vat_applicable', 1);
        } else {
            add_import_field($cols, $fields, $values, $params, 'branch_id', $branchId);
            add_import_field($cols, $fields, $values, $params, 'title', $name);
            add_import_field($cols, $fields, $values, $params, 'category', $category);
            add_import_field($cols, $fields, $values, $params, 'amount', $amount);
            add_import_field($cols, $fields, $values, $params, 'expense_date', $date);
            add_import_field($cols, $fields, $values, $params, 'paid_by', $userId > 0 ? $userId : 1);
            add_import_field($cols, $fields, $values, $params, 'notes', $notes !== '' ? $notes : null);
            add_import_field($cols, $fields, $values, $params, 'vat_amount', $vatAmount);
            add_import_field($cols, $fields, $values, $params, 'net_amount', $netAmount);
            add_import_field($cols, $fields, $values, $params, 'is_vat_recoverable', 1);
        }

        if ($importFileId !== null) {
            add_import_field($cols, $fields, $values, $params, 'import_file_id', $importFileId);
        }

        if (empty($fields)) {
            $skipped++;
            continue;
        }

        $sql = "
            INSERT INTO {$table}
            (" . implode(', ', $fields) . ")
            VALUES (" . implode(', ', $values) . ")
        ";

        $st = $pdo->prepare($sql);
        $st->execute($params);

        $inserted++;
    }

    if ($hasImportFilesTable && $importFileId !== null) {
        $status = $inserted > 0 ? 'completed' : 'failed';
        $errorMessage = $inserted > 0 ? null : 'لم يتم استيراد أي صف صالح من الملف';

        $stUpdate = $pdo->prepare("
            UPDATE financial_import_files
            SET imported_rows = ?, skipped_rows = ?, status = ?, error_message = ?
            WHERE id = ?
        ");

        $stUpdate->execute([
            $inserted,
            $skipped,
            $status,
            $errorMessage,
            $importFileId,
        ]);
    }

    $pdo->commit();

    $label = $importType === 'expense' ? 'مصروف' : 'مدخول';

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

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

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