<?php

function ensureDonationsTable($conn)
{
    $sql = "
        CREATE TABLE IF NOT EXISTS donations (
            id INT(11) NOT NULL AUTO_INCREMENT,
            donation_date DATE NOT NULL,
            recipient_name VARCHAR(255) NOT NULL,
            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_donations_date (donation_date),
            KEY idx_donations_recipient (recipient_name)
        ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
    ";

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

    upgradeDonationsTable($conn);
}

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

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

    return $exists;
}

function upgradeDonationsTable($conn)
{
    if (!donationColumnExists($conn, 'is_archived')) {
        if (!$conn->query("ALTER TABLE donations ADD COLUMN is_archived TINYINT(1) NOT NULL DEFAULT 0 AFTER notes")) {
            throw new RuntimeException('Could not add archived flag to donations: ' . $conn->error);
        }
    }

    if (!donationColumnExists($conn, 'archived_at')) {
        if (!$conn->query("ALTER TABLE donations ADD COLUMN archived_at DATETIME DEFAULT NULL AFTER is_archived")) {
            throw new RuntimeException('Could not add archived timestamp to donations: ' . $conn->error);
        }
    }
}

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

    return $month;
}

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

    return array($start, $end);
}

function normalizeDonationExportScope($scope)
{
    $scope = trim((string) $scope);
    if (!in_array($scope, array('month', 'range', 'all'), true)) {
        return 'month';
    }

    return $scope;
}

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

    return $date;
}

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

    return $quantity * $unitPrice;
}

function fetchDonationsByMonth($conn, $month)
{
    ensureDonationsTable($conn);
    list($startDate, $endDate) = donationMonthRange($month);

    $stmt = $conn->prepare("
        SELECT id, donation_date, recipient_name, qbo_account, item_description, quantity, unit_price, notes, created_at
        FROM donations
        WHERE donation_date BETWEEN ? AND ?
          AND is_archived = 0
        ORDER BY donation_date DESC, id DESC
    ");
    $stmt->bind_param('ss', $startDate, $endDate);
    $stmt->execute();
    $result = $stmt->get_result();
    $rows = $result ? $result->fetch_all(MYSQLI_ASSOC) : array();
    $stmt->close();

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

    return $rows;
}

function fetchDonationsForExport($conn, $scope, $month, $startDate, $endDate)
{
    ensureDonationsTable($conn);

    $scope = normalizeDonationExportScope($scope);
    $sql = "
        SELECT id, donation_date, recipient_name, qbo_account, item_description, quantity, unit_price, notes, created_at
        FROM donations
        WHERE is_archived = 0
    ";
    $params = array();
    $types = '';

    if ($scope === 'range') {
        $startDate = normalizeDonationDate($startDate);
        $endDate = normalizeDonationDate($endDate);
        if ($startDate !== '' && $endDate !== '') {
            $sql .= " AND donation_date BETWEEN ? AND ?";
            $params[] = $startDate;
            $params[] = $endDate;
            $types .= 'ss';
        }
    } elseif ($scope === 'month') {
        list($monthStart, $monthEnd) = donationMonthRange($month);
        $sql .= " AND donation_date BETWEEN ? AND ?";
        $params[] = $monthStart;
        $params[] = $monthEnd;
        $types .= 'ss';
    }

    $sql .= " ORDER BY donation_date DESC, id DESC";

    if ($types === '') {
        $result = $conn->query($sql);
        if (!$result) {
            throw new RuntimeException('Could not load donations for export: ' . $conn->error);
        }
        $rows = $result->fetch_all(MYSQLI_ASSOC);
    } else {
        $stmt = $conn->prepare($sql);
        if (!$stmt) {
            throw new RuntimeException('Could not prepare the donations export query: ' . $conn->error);
        }
        $bind = array($types);
        foreach ($params as $index => $param) {
            $bind[] = &$params[$index];
        }
        call_user_func_array(array($stmt, 'bind_param'), $bind);
        $stmt->execute();
        $result = $stmt->get_result();
        $rows = $result ? $result->fetch_all(MYSQLI_ASSOC) : array();
        $stmt->close();
    }

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

    return $rows;
}

function fetchArchivedDonations($conn)
{
    ensureDonationsTable($conn);

    $result = $conn->query("
        SELECT id, donation_date, recipient_name, qbo_account, item_description, quantity, unit_price, notes, archived_at, created_at
        FROM donations
        WHERE is_archived = 1
        ORDER BY archived_at DESC, donation_date DESC, id DESC
    ");

    if (!$result) {
        throw new RuntimeException('Could not load archived donations: ' . $conn->error);
    }

    $rows = $result->fetch_all(MYSQLI_ASSOC);
    foreach ($rows as &$row) {
        $row['line_total'] = donationLineTotal($row);
    }
    unset($row);

    return $rows;
}

function addDonation($conn, $donationDate, $recipientName, $qboAccount, $itemDescription, $quantity, $unitPrice, $notes)
{
    ensureDonationsTable($conn);

    $donationDate = trim((string) $donationDate);
    $recipientName = trim((string) $recipientName);
    $qboAccount = trim((string) $qboAccount);
    $itemDescription = trim((string) $itemDescription);
    $quantity = trim((string) $quantity);
    $unitPrice = trim((string) $unitPrice);
    $notes = trim((string) $notes);

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

    if ($recipientName === '') {
        throw new RuntimeException('Recipient / organization is required.');
    }

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

    if ($itemDescription === '') {
        throw new RuntimeException('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 donations (
            donation_date,
            recipient_name,
            qbo_account,
            item_description,
            quantity,
            unit_price,
            notes
        ) VALUES (?, ?, ?, ?, ?, ?, ?)
    ");
    $stmt->bind_param('sssssss', $donationDate, $recipientName, $qboAccount, $itemDescription, $quantityValue, $unitPriceValue, $notes);
    $stmt->execute();
    $stmt->close();
}

function updateDonation($conn, $donationId, $quantity, $unitPrice)
{
    ensureDonationsTable($conn);

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

    $quantity = trim((string) $quantity);
    $unitPrice = trim((string) $unitPrice);

    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 donations
        SET quantity = ?, unit_price = ?
        WHERE id = ?
        LIMIT 1
    ");
    $stmt->bind_param('ssi', $quantityValue, $unitPriceValue, $donationId);
    $stmt->execute();
    $stmt->close();
}

function donationLinkedGiftCardId($conn, $donationId)
{
    $stmt = $conn->prepare("SELECT id FROM gift_cards WHERE donation_entry_id = ? LIMIT 1");
    if (!$stmt) {
        throw new RuntimeException('Could not inspect linked gift card: ' . $conn->error);
    }
    $stmt->bind_param('i', $donationId);
    $stmt->execute();
    $result = $stmt->get_result();
    $row = $result ? $result->fetch_assoc() : null;
    $stmt->close();

    return $row ? (int) $row['id'] : 0;
}

function archiveDonation($conn, $donationId)
{
    ensureDonationsTable($conn);

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

    $archivedAt = date('Y-m-d H:i:s');
    $stmt = $conn->prepare("UPDATE donations SET is_archived = 1, archived_at = ? WHERE id = ? LIMIT 1");
    $stmt->bind_param('si', $archivedAt, $donationId);
    $stmt->execute();
    $stmt->close();
}

function restoreDonation($conn, $donationId)
{
    ensureDonationsTable($conn);

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

    $stmt = $conn->prepare("UPDATE donations SET is_archived = 0, archived_at = NULL WHERE id = ? LIMIT 1");
    $stmt->bind_param('i', $donationId);
    $stmt->execute();
    $stmt->close();
}

function deleteDonation($conn, $donationId)
{
    ensureDonationsTable($conn);

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

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

function deleteDonationAndLinkedGiftCard($conn, $donationId)
{
    $giftCardId = donationLinkedGiftCardId($conn, $donationId);
    if ($giftCardId > 0 && function_exists('deleteGiftCardOnly')) {
        deleteGiftCardOnly($conn, $giftCardId);
    }

    deleteDonation($conn, $donationId);
}

function archiveDonationAndLinkedGiftCard($conn, $donationId)
{
    $giftCardId = donationLinkedGiftCardId($conn, $donationId);
    if ($giftCardId > 0 && function_exists('archiveGiftCardOnly')) {
        archiveGiftCardOnly($conn, $giftCardId);
    }

    archiveDonation($conn, $donationId);
}

function restoreDonationAndLinkedGiftCard($conn, $donationId)
{
    $giftCardId = donationLinkedGiftCardId($conn, $donationId);
    if ($giftCardId > 0 && function_exists('restoreGiftCardOnly')) {
        restoreGiftCardOnly($conn, $giftCardId);
    }

    restoreDonation($conn, $donationId);
}

function donationMonthlyTotal($rows)
{
    $total = 0.0;
    foreach ($rows as $row) {
        $total += donationLineTotal($row);
    }

    return $total;
}

function donationTotalsByAccount($rows)
{
    $totals = array();

    foreach ($rows as $row) {
        $account = isset($row['qbo_account']) ? trim((string) $row['qbo_account']) : 'Uncategorized';
        if ($account === '') {
            $account = 'Uncategorized';
        }

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

        $totals[$account] += donationLineTotal($row);
    }

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

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

    fputcsv($handle, array(
        'Donation Date',
        'Recipient',
        'QBO Account',
        'Description',
        'Qty',
        'Price',
        'Total',
        'Notes',
    ));

    foreach ($rows as $row) {
        fputcsv($handle, array(
            isset($row['donation_date']) ? $row['donation_date'] : '',
            isset($row['recipient_name']) ? $row['recipient_name'] : '',
            isset($row['qbo_account']) ? $row['qbo_account'] : '',
            isset($row['item_description']) ? $row['item_description'] : '',
            number_format((float) (isset($row['quantity']) ? $row['quantity'] : 0), 2, '.', ''),
            number_format((float) (isset($row['unit_price']) ? $row['unit_price'] : 0), 2, '.', ''),
            number_format((float) (isset($row['line_total']) ? $row['line_total'] : 0), 2, '.', ''),
            isset($row['notes']) ? $row['notes'] : '',
        ));
    }

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

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

function donationExportFilename($scope, $month, $startDate, $endDate)
{
    $scope = normalizeDonationExportScope($scope);
    if ($scope === 'range') {
        $startDate = normalizeDonationDate($startDate);
        $endDate = normalizeDonationDate($endDate);
        if ($startDate !== '' && $endDate !== '') {
            return 'donations_' . str_replace('-', '_', $startDate) . '_to_' . str_replace('-', '_', $endDate) . '.csv';
        }
    }

    if ($scope === 'all') {
        return 'donations_all.csv';
    }

    return 'donations_' . str_replace('-', '_', normalizeDonationMonth($month)) . '.csv';
}