<?php

function ensureStoreExpensesTable($conn)
{
    $sql = "
        CREATE TABLE IF NOT EXISTS store_expenses (
            id INT(11) NOT NULL AUTO_INCREMENT,
            expense_date DATE NOT NULL,
            entry_type VARCHAR(100) NOT NULL DEFAULT 'Store Use',
            qbo_account VARCHAR(255) NOT NULL,
            item_description VARCHAR(255) NOT NULL,
            quantity DECIMAL(10,2) NOT NULL DEFAULT 1.00,
            unit_price DECIMAL(10,2) NOT NULL DEFAULT 0.00,
            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);
    }

    upgradeStoreExpensesTable($conn);
}

function storeExpensesColumnExists($conn, $columnName)
{
    $safeColumn = $conn->real_escape_string($columnName);
    $result = $conn->query("SHOW COLUMNS FROM store_expenses LIKE '" . $safeColumn . "'");
    if (!$result) {
        throw new RuntimeException('Could not inspect the store expenses table: ' . $conn->error);
    }

    $exists = $result->num_rows > 0;
    $result->close();

    return $exists;
}

function upgradeStoreExpensesTable($conn)
{
    if (!storeExpensesColumnExists($conn, 'entry_type')) {
        if (!$conn->query("ALTER TABLE store_expenses ADD COLUMN entry_type VARCHAR(100) NOT NULL DEFAULT 'Store Use' AFTER expense_date")) {
            throw new RuntimeException('Could not add entry type to store expenses: ' . $conn->error);
        }
    }

    if (!storeExpensesColumnExists($conn, 'item_description')) {
        if (!$conn->query("ALTER TABLE store_expenses ADD COLUMN item_description VARCHAR(255) NOT NULL DEFAULT '' AFTER qbo_account")) {
            throw new RuntimeException('Could not add item description to store expenses: ' . $conn->error);
        }

        if (storeExpensesColumnExists($conn, 'line_description')) {
            if (!$conn->query("UPDATE store_expenses SET item_description = line_description WHERE item_description = ''")) {
                throw new RuntimeException('Could not migrate prior expense descriptions: ' . $conn->error);
            }
        }
    }

    if (!storeExpensesColumnExists($conn, 'quantity')) {
        if (!$conn->query("ALTER TABLE store_expenses ADD COLUMN quantity DECIMAL(10,2) NOT NULL DEFAULT 1.00 AFTER item_description")) {
            throw new RuntimeException('Could not add quantity to store expenses: ' . $conn->error);
        }
    }

    if (!storeExpensesColumnExists($conn, 'unit_price')) {
        if (!$conn->query("ALTER TABLE store_expenses ADD COLUMN unit_price DECIMAL(10,2) NOT NULL DEFAULT 0.00 AFTER quantity")) {
            throw new RuntimeException('Could not add unit price to store expenses: ' . $conn->error);
        }

        if (storeExpensesColumnExists($conn, 'amount')) {
            if (!$conn->query("UPDATE store_expenses SET unit_price = amount WHERE unit_price = 0.00")) {
                throw new RuntimeException('Could not migrate prior expense amounts: ' . $conn->error);
            }
        }
    }

    if (!storeExpensesColumnExists($conn, 'notes')) {
        if (!$conn->query("ALTER TABLE store_expenses ADD COLUMN notes TEXT DEFAULT NULL AFTER unit_price")) {
            throw new RuntimeException('Could not add notes to store expenses: ' . $conn->error);
        }
    }

    if (storeExpensesColumnExists($conn, 'supplier_name')) {
        if (!$conn->query("ALTER TABLE store_expenses MODIFY supplier_name VARCHAR(255) NOT NULL DEFAULT ''")) {
            throw new RuntimeException('Could not relax legacy supplier field on store expenses: ' . $conn->error);
        }
    }

    if (storeExpensesColumnExists($conn, 'tax_code')) {
        if (!$conn->query("ALTER TABLE store_expenses MODIFY tax_code VARCHAR(100) NOT NULL DEFAULT ''")) {
            throw new RuntimeException('Could not relax legacy tax code field on store expenses: ' . $conn->error);
        }
    }

    if (storeExpensesColumnExists($conn, 'amount')) {
        if (!$conn->query("ALTER TABLE store_expenses MODIFY amount DECIMAL(10,2) NOT NULL DEFAULT 0.00")) {
            throw new RuntimeException('Could not relax legacy amount field on store expenses: ' . $conn->error);
        }
    }

    if (storeExpensesColumnExists($conn, 'line_description')) {
        if (!$conn->query("ALTER TABLE store_expenses MODIFY line_description VARCHAR(255) NOT NULL DEFAULT ''")) {
            throw new RuntimeException('Could not relax legacy line description field on store expenses: ' . $conn->error);
        }
    }
}

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

    return $month;
}

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

    return [$start, $end];
}

function storeExpenseLineTotal($expense)
{
    $quantity = isset($expense['quantity']) ? (float) $expense['quantity'] : 0;
    $unitPrice = isset($expense['unit_price']) ? (float) $expense['unit_price'] : 0;

    return $quantity * $unitPrice;
}

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

    $stmt = $conn->prepare("
        SELECT id, expense_date, entry_type, qbo_account, item_description, quantity, unit_price, 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();

    foreach ($rows as &$row) {
        $row['line_total'] = storeExpenseLineTotal($row);
    }
    unset($row);

    return $rows;
}

function fetchStoreExpenseById($conn, $expenseId)
{
    ensureStoreExpensesTable($conn);

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

    $stmt = $conn->prepare("
        SELECT id, expense_date, entry_type, qbo_account, item_description, quantity, unit_price, notes, created_at
        FROM store_expenses
        WHERE id = ?
        LIMIT 1
    ");
    $stmt->bind_param('i', $expenseId);
    $stmt->execute();
    $result = $stmt->get_result();
    $expense = $result ? $result->fetch_assoc() : null;
    $stmt->close();

    if (!$expense) {
        throw new RuntimeException('Store expense not found.');
    }

    $expense['line_total'] = storeExpenseLineTotal($expense);

    return $expense;
}

function addStoreExpense(
    $conn,
    $expenseDate,
    $entryType,
    $qboAccount,
    $itemDescription,
    $quantity,
    $unitPrice,
    $notes
) {
    ensureStoreExpensesTable($conn);

    $expenseDate = trim($expenseDate);
    $entryType = normalizeStoreExpenseEntryType($entryType);
    $qboAccount = trim($qboAccount);
    $itemDescription = trim($itemDescription);
    $quantity = trim($quantity);
    $unitPrice = trim($unitPrice);
    $notes = trim($notes);

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

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

    if ($itemDescription === '') {
        throw new RuntimeException('Item description is required.');
    }

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

    if ($unitPrice === '' || !is_numeric($unitPrice) || (float) $unitPrice < 0) {
        throw new RuntimeException('Please enter a valid price.');
    }

    $quantityValue = number_format((float) $quantity, 2, '.', '');
    $unitPriceValue = number_format((float) $unitPrice, 2, '.', '');

    $stmt = $conn->prepare("
        INSERT INTO store_expenses (
            expense_date,
            entry_type,
            qbo_account,
            item_description,
            quantity,
            unit_price,
            notes
        ) VALUES (?, ?, ?, ?, ?, ?, ?)
    ");
    $stmt->bind_param(
        'sssssss',
        $expenseDate,
        $entryType,
        $qboAccount,
        $itemDescription,
        $quantityValue,
        $unitPriceValue,
        $notes
    );
    $stmt->execute();
    $stmt->close();
}

function updateStoreExpense($conn, $expenseId, $expenseDate, $entryType, $qboAccount, $itemDescription, $quantity, $unitPrice, $notes)
{
    ensureStoreExpensesTable($conn);

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

    $expenseDate = trim($expenseDate);
    $entryType = normalizeStoreExpenseEntryType($entryType);
    $qboAccount = trim($qboAccount);
    $itemDescription = trim($itemDescription);
    $quantity = trim($quantity);
    $unitPrice = trim($unitPrice);
    $notes = trim($notes);

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

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

    if ($itemDescription === '') {
        throw new RuntimeException('Item description is required.');
    }

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

    if ($unitPrice === '' || !is_numeric($unitPrice) || (float) $unitPrice < 0) {
        throw new RuntimeException('Please enter a valid price.');
    }

    $quantityValue = number_format((float) $quantity, 2, '.', '');
    $unitPriceValue = number_format((float) $unitPrice, 2, '.', '');

    $stmt = $conn->prepare("
        UPDATE store_expenses
        SET expense_date = ?, entry_type = ?, qbo_account = ?, item_description = ?, quantity = ?, unit_price = ?, notes = ?
        WHERE id = ?
        LIMIT 1
    ");
    $stmt->bind_param('sssssssi', $expenseDate, $entryType, $qboAccount, $itemDescription, $quantityValue, $unitPriceValue, $notes, $expenseId);
    $stmt->execute();
    $stmt->close();
}

function deleteStoreExpense($conn, $expenseId)
{
    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 storeExpenseEntryTypeOptions()
{
    return array(
        'Store Use',
        'Damaged / Broken',
        'Spoiled / Expired',
    );
}

function normalizeStoreExpenseEntryType($entryType)
{
    $entryType = trim((string) $entryType);
    $allowed = storeExpenseEntryTypeOptions();

    if (!in_array($entryType, $allowed, true)) {
        return 'Store Use';
    }

    return $entryType;
}

function storeExpenseMonthlyTotal($expenses)
{
    $total = 0.0;
    foreach ($expenses as $expense) {
        $total += storeExpenseLineTotal($expense);
    }

    return $total;
}

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

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

        $totals[$account] += storeExpenseLineTotal($expense);
    }

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

function storeExpenseExportFilename($storeName, $month)
{
    $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.csv';
}

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

    fputcsv($handle, [
        'Expense Date',
        'Entry Type',
        'Account',
        'Description',
        'Qty',
        'Price',
        'Amount',
        'Memo',
    ]);

    foreach ($expenses as $expense) {
        $expenseDate = isset($expense['expense_date']) ? (string) $expense['expense_date'] : '';
        $lineTotal = storeExpenseLineTotal($expense);

        fputcsv($handle, [
            $expenseDate,
            isset($expense['entry_type']) ? (string) $expense['entry_type'] : 'Store Use',
            isset($expense['qbo_account']) ? (string) $expense['qbo_account'] : '',
            isset($expense['item_description']) ? (string) $expense['item_description'] : '',
            number_format((float) (isset($expense['quantity']) ? $expense['quantity'] : 0), 2, '.', ''),
            number_format((float) (isset($expense['unit_price']) ? $expense['unit_price'] : 0), 2, '.', ''),
            number_format($lineTotal, 2, '.', ''),
            buildStoreExpenseMemo($expense),
        ]);
    }

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

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

function buildStoreExpenseMemo($expense)
{
    $parts = array();
    $entryType = isset($expense['entry_type']) ? trim((string) $expense['entry_type']) : '';
    $notes = isset($expense['notes']) ? trim((string) $expense['notes']) : '';

    if ($entryType !== '') {
        $parts[] = $entryType;
    }

    if ($notes !== '') {
        $parts[] = $notes;
    }

    return implode(' - ', $parts);
}