<?php

function ensureStoreExpensesTable(mysqli $conn): void
{
    $sql = "
        CREATE TABLE IF NOT EXISTS store_expenses (
            id INT(11) NOT NULL AUTO_INCREMENT,
            expense_date DATE NOT NULL,
            supplier_name VARCHAR(255) NOT NULL,
            qbo_account VARCHAR(255) NOT NULL,
            line_description VARCHAR(255) NOT NULL,
            amount DECIMAL(10,2) NOT NULL DEFAULT 0.00,
            tax_code VARCHAR(50) NOT NULL DEFAULT 'NON',
            notes TEXT DEFAULT NULL,
            created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            KEY idx_store_expenses_date (expense_date),
            KEY idx_store_expenses_account (qbo_account)
        ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
    ";

    if (!$conn->query($sql)) {
        throw new RuntimeException('Could not create the store expenses table: ' . $conn->error);
    }
}

function normalizeExpenseMonth(string $month): string
{
    $month = trim($month);
    if (!preg_match('/^\d{4}-\d{2}$/', $month)) {
        return date('Y-m');
    }

    return $month;
}

function expenseMonthRange(string $month): array
{
    $month = normalizeExpenseMonth($month);
    $start = $month . '-01';
    $end = date('Y-m-t', strtotime($start));

    return [$start, $end];
}

function fetchStoreExpensesByMonth(mysqli $conn, string $month): array
{
    ensureStoreExpensesTable($conn);
    [$startDate, $endDate] = expenseMonthRange($month);

    $stmt = $conn->prepare("
        SELECT id, expense_date, supplier_name, qbo_account, line_description, amount, tax_code, notes, created_at
        FROM store_expenses
        WHERE expense_date BETWEEN ? AND ?
        ORDER BY expense_date DESC, id DESC
    ");
    $stmt->bind_param('ss', $startDate, $endDate);
    $stmt->execute();
    $result = $stmt->get_result();
    $rows = $result ? $result->fetch_all(MYSQLI_ASSOC) : [];
    $stmt->close();

    return $rows;
}

function addStoreExpense(
    mysqli $conn,
    string $expenseDate,
    string $supplierName,
    string $qboAccount,
    string $lineDescription,
    string $amount,
    string $taxCode,
    string $notes
): void {
    ensureStoreExpensesTable($conn);

    $expenseDate = trim($expenseDate);
    $supplierName = trim($supplierName);
    $qboAccount = trim($qboAccount);
    $lineDescription = trim($lineDescription);
    $amount = trim($amount);
    $taxCode = trim($taxCode);
    $notes = trim($notes);

    if (!preg_match('/^\d{4}-\d{2}-\d{2}$/', $expenseDate)) {
        throw new RuntimeException('Please enter a valid expense date.');
    }

    if ($supplierName === '') {
        throw new RuntimeException('Supplier or vendor is required.');
    }

    if ($qboAccount === '') {
        throw new RuntimeException('QBO account/category is required.');
    }

    if ($lineDescription === '') {
        throw new RuntimeException('Description is required.');
    }

    if ($amount === '' || !is_numeric($amount) || (float) $amount <= 0) {
        throw new RuntimeException('Please enter a valid amount greater than zero.');
    }

    if ($taxCode === '') {
        $taxCode = 'NON';
    }

    $amountValue = number_format((float) $amount, 2, '.', '');

    $stmt = $conn->prepare("
        INSERT INTO store_expenses (
            expense_date,
            supplier_name,
            qbo_account,
            line_description,
            amount,
            tax_code,
            notes
        ) VALUES (?, ?, ?, ?, ?, ?, ?)
    ");
    $stmt->bind_param(
        'sssssss',
        $expenseDate,
        $supplierName,
        $qboAccount,
        $lineDescription,
        $amountValue,
        $taxCode,
        $notes
    );
    $stmt->execute();
    $stmt->close();
}

function deleteStoreExpense(mysqli $conn, int $expenseId): void
{
    ensureStoreExpensesTable($conn);

    if ($expenseId <= 0) {
        throw new RuntimeException('Invalid expense selected.');
    }

    $stmt = $conn->prepare("DELETE FROM store_expenses WHERE id = ? LIMIT 1");
    $stmt->bind_param('i', $expenseId);
    $stmt->execute();
    $stmt->close();
}

function storeExpenseMonthlyTotal(array $expenses): float
{
    $total = 0.0;
    foreach ($expenses as $expense) {
        $total += (float) ($expense['amount'] ?? 0);
    }

    return $total;
}

function storeExpenseTotalsByAccount(array $expenses): array
{
    $totals = [];
    foreach ($expenses as $expense) {
        $account = trim((string) ($expense['qbo_account'] ?? 'Uncategorized'));
        if ($account === '') {
            $account = 'Uncategorized';
        }

        if (!isset($totals[$account])) {
            $totals[$account] = 0.0;
        }

        $totals[$account] += (float) ($expense['amount'] ?? 0);
    }

    ksort($totals, SORT_NATURAL | SORT_FLAG_CASE);
    return $totals;
}

function storeExpenseExportFilename(string $storeName, string $month): string
{
    $safeStore = preg_replace('/[^A-Za-z0-9_-]+/', '_', trim($storeName));
    $safeStore = trim((string) $safeStore, '_');
    if ($safeStore === '') {
        $safeStore = 'HouseTabPro';
    }

    return $safeStore . '_store_expenses_' . str_replace('-', '_', normalizeExpenseMonth($month)) . '_qbo_bills.csv';
}

function exportStoreExpensesQboBillsCsv(array $expenses): string
{
    $handle = fopen('php://temp', 'r+');
    if ($handle === false) {
        throw new RuntimeException('Could not build the export file.');
    }

    fputcsv($handle, [
        'Bill no.',
        'Supplier',
        'Bill Date',
        'Due Date',
        'Account',
        'Line Description',
        'Line Amount',
        'Line Tax Code',
        'Memo',
    ]);

    foreach ($expenses as $expense) {
        $expenseDate = (string) ($expense['expense_date'] ?? '');
        fputcsv($handle, [
            'HTP-' . (int) ($expense['id'] ?? 0),
            (string) ($expense['supplier_name'] ?? ''),
            $expenseDate,
            $expenseDate,
            (string) ($expense['qbo_account'] ?? ''),
            (string) ($expense['line_description'] ?? ''),
            number_format((float) ($expense['amount'] ?? 0), 2, '.', ''),
            (string) ($expense['tax_code'] ?? 'NON'),
            (string) ($expense['notes'] ?? ''),
        ]);
    }

    rewind($handle);
    $csv = stream_get_contents($handle);
    fclose($handle);

    return $csv === false ? '' : $csv;
}