<?php
// CSV import (Section 23 Future Enhancement, added post-Phase-11 at the
// user's request). Parses the user's personal two-block ledger format —
// side-by-side Expenses | Income columns, date only filled on the first
// transaction of each day (blank rows below it belong to the same date).
// This is deliberately NOT the same shape as export.php's output; it
// matches the CSV the user already has for every past month.

require_once __DIR__ . '/helpers.php';

/**
 * Parses one uploaded ledger CSV. Returns:
 *   ['error' => string] on a fatal parse failure, or
 *   ['expenses' => [...], 'incomes' => [...], 'warnings' => [...]] on success.
 * Each expense/income row is ['date' => 'Y-m-d', 'amount' => '123.45',
 * 'description' => ?string, 'category_name' => string].
 */
function parse_ledger_csv(string $filePath): array
{
    $handle = fopen($filePath, 'r');
    if (!$handle) {
        return ['error' => 'Could not read the uploaded file.'];
    }

    $rows = [];
    while (($row = fgetcsv($handle)) !== false) {
        $rows[] = $row;
    }
    fclose($handle);

    // Find the header row (the one with "Date" in the expense-date column)
    // rather than assuming a fixed row number, so minor formatting
    // differences between months' files don't break parsing.
    $headerIndex = null;
    foreach ($rows as $i => $row) {
        if (isset($row[1]) && strtolower(trim($row[1])) === 'date') {
            $headerIndex = $i;
            break;
        }
    }
    if ($headerIndex === null) {
        return ['error' => 'Could not find the header row (expected a "Date" column).'];
    }

    $expenses = [];
    $incomes = [];
    $warnings = [];
    $lastExpenseDate = null;
    $lastIncomeDate = null;

    for ($i = $headerIndex + 1; $i < count($rows); $i++) {
        $row = $rows[$i];
        $rowNum = $i + 1;

        // ---- Expense block: columns 1-4 (Date, Amount, Description, Category) ----
        $expDateRaw = trim((string) ($row[1] ?? ''));
        $expAmountRaw = trim((string) ($row[2] ?? ''));
        $expDesc = trim((string) ($row[3] ?? ''));
        $expCategory = trim((string) ($row[4] ?? ''));

        if ($expDateRaw !== '') {
            $parsed = parse_flexible_date($expDateRaw);
            if ($parsed !== null) {
                $lastExpenseDate = $parsed;
            } else {
                $warnings[] = "Row $rowNum: couldn't read expense date \"$expDateRaw\" — used the previous row's date.";
            }
        }

        if ($expAmountRaw !== '') {
            $amount = parse_ledger_amount($expAmountRaw);
            if ($amount === null) {
                $warnings[] = "Row $rowNum: couldn't read expense amount \"$expAmountRaw\" — row skipped.";
            } elseif ($lastExpenseDate === null) {
                $warnings[] = "Row $rowNum: expense has no date yet — row skipped.";
            } else {
                $expenses[] = [
                    'date' => $lastExpenseDate,
                    'amount' => $amount,
                    'description' => $expDesc !== '' ? $expDesc : null,
                    'category_name' => $expCategory !== '' ? $expCategory : 'Others',
                ];
            }
        }

        // ---- Income block: columns 6-9 ----
        $incDateRaw = trim((string) ($row[6] ?? ''));
        $incAmountRaw = trim((string) ($row[7] ?? ''));
        $incDesc = trim((string) ($row[8] ?? ''));
        $incCategory = trim((string) ($row[9] ?? ''));

        if ($incDateRaw !== '') {
            $parsed = parse_flexible_date($incDateRaw);
            if ($parsed !== null) {
                $lastIncomeDate = $parsed;
            } else {
                $warnings[] = "Row $rowNum: couldn't read income date \"$incDateRaw\" — used the previous row's date.";
            }
        }

        if ($incAmountRaw !== '') {
            $amount = parse_ledger_amount($incAmountRaw);
            if ($amount === null) {
                $warnings[] = "Row $rowNum: couldn't read income amount \"$incAmountRaw\" — row skipped.";
            } elseif ($lastIncomeDate === null) {
                $warnings[] = "Row $rowNum: income has no date yet — row skipped.";
            } else {
                $incomes[] = [
                    'date' => $lastIncomeDate,
                    'amount' => $amount,
                    'description' => $incDesc !== '' ? $incDesc : null,
                    'category_name' => $incCategory !== '' ? $incCategory : 'Other',
                ];
            }
        }
    }

    return ['expenses' => $expenses, 'incomes' => $incomes, 'warnings' => $warnings];
}

/** Accepts M/D/YYYY (the user's format), plus a couple of sensible fallbacks. */
function parse_flexible_date(string $raw): ?string
{
    foreach (['n/j/Y', 'm/d/Y', 'Y-m-d'] as $format) {
        $dt = DateTime::createFromFormat('!' . $format, $raw);
        if ($dt !== false) {
            return $dt->format('Y-m-d');
        }
    }

    return null;
}

/** Strips currency symbols (¥/￥/$), thousands separators, and whitespace. */
function parse_ledger_amount(string $raw): ?string
{
    $clean = preg_replace('/[^\d.]/u', '', $raw);
    if ($clean === '' || $clean === null || !is_numeric($clean)) {
        return null;
    }

    return number_format((float) $clean, 2, '.', '');
}

/**
 * Finds a category by name (case-insensitive, via the categories table's
 * own ci collation) or creates it if nothing matches — so an import never
 * silently drops data into the wrong bucket over a naming mismatch.
 */
function resolve_or_create_category(PDO $db, string $name, string $type, array &$cache): int
{
    $key = $type . ':' . mb_strtolower($name);
    if (isset($cache[$key])) {
        return $cache[$key];
    }

    $stmt = $db->prepare('SELECT id FROM categories WHERE type = :type AND name = :name');
    $stmt->execute(['type' => $type, 'name' => $name]);
    $id = $stmt->fetchColumn();

    if ($id === false) {
        $palette = ['#5a5bd8', '#22a06b', '#e8843c', '#3d8be0', '#d96bb1', '#33a9c7', '#b0813f', '#9a77cf'];
        $color = $palette[array_rand($palette)];

        $orderStmt = $db->prepare('SELECT COALESCE(MAX(sort_order), 0) + 1 FROM categories WHERE type = :type');
        $orderStmt->execute(['type' => $type]);
        $nextOrder = (int) $orderStmt->fetchColumn();

        $db->prepare('INSERT INTO categories (name, type, color, sort_order, is_active) VALUES (:name, :type, :color, :sort_order, 1)')
            ->execute(['name' => $name, 'type' => $type, 'color' => $color, 'sort_order' => $nextOrder]);
        $id = (int) $db->lastInsertId();
    }

    $cache[$key] = (int) $id;

    return (int) $id;
}
