<?php

require_once __DIR__ . '/donation_helpers.php';

function ensureGiftCardTables($conn)
{
    $cardsSql = "
        CREATE TABLE IF NOT EXISTS gift_cards (
            id INT(11) NOT NULL AUTO_INCREMENT,
            card_label VARCHAR(255) NOT NULL,
            card_number VARCHAR(255) NOT NULL DEFAULT '',
            initial_amount DECIMAL(10,2) NOT NULL DEFAULT 0.00,
            notes TEXT DEFAULT NULL,
            issued_date DATE NOT NULL,
            created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            KEY idx_gift_cards_label (card_label),
            KEY idx_gift_cards_issued_date (issued_date)
        ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
    ";

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

    upgradeGiftCardsTable($conn);

    $transactionsSql = "
        CREATE TABLE IF NOT EXISTS gift_card_transactions (
            id INT(11) NOT NULL AUTO_INCREMENT,
            gift_card_id INT(11) NOT NULL,
            transaction_date DATE 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,
            taxable TINYINT(1) NOT NULL DEFAULT 0,
            notes TEXT DEFAULT NULL,
            created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            KEY idx_gift_card_transactions_card (gift_card_id),
            KEY idx_gift_card_transactions_date (transaction_date)
        ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
    ";

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

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

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

    return $exists;
}

function upgradeGiftCardsTable($conn)
{
    if (!giftCardColumnExists($conn, 'expiration_days')) {
        if (!$conn->query("ALTER TABLE gift_cards ADD COLUMN expiration_days INT(11) NOT NULL DEFAULT 180 AFTER issued_date")) {
            throw new RuntimeException('Could not add expiration days to gift cards: ' . $conn->error);
        }
    }

    if (!giftCardColumnExists($conn, 'expiration_date')) {
        if (!$conn->query("ALTER TABLE gift_cards ADD COLUMN expiration_date DATE DEFAULT NULL AFTER expiration_days")) {
            throw new RuntimeException('Could not add expiration date to gift cards: ' . $conn->error);
        }
    }

    if (!giftCardColumnExists($conn, 'is_donation')) {
        if (!$conn->query("ALTER TABLE gift_cards ADD COLUMN is_donation TINYINT(1) NOT NULL DEFAULT 0 AFTER initial_amount")) {
            throw new RuntimeException('Could not add donation flag to gift cards: ' . $conn->error);
        }
    }

    if (!giftCardColumnExists($conn, 'donation_recipient')) {
        if (!$conn->query("ALTER TABLE gift_cards ADD COLUMN donation_recipient VARCHAR(255) NOT NULL DEFAULT '' AFTER is_donation")) {
            throw new RuntimeException('Could not add donation recipient to gift cards: ' . $conn->error);
        }
    }

    if (!giftCardColumnExists($conn, 'donation_qbo_account')) {
        if (!$conn->query("ALTER TABLE gift_cards ADD COLUMN donation_qbo_account VARCHAR(255) NOT NULL DEFAULT '' AFTER donation_recipient")) {
            throw new RuntimeException('Could not add donation QBO account to gift cards: ' . $conn->error);
        }
    }

    if (!giftCardColumnExists($conn, 'donation_entry_id')) {
        if (!$conn->query("ALTER TABLE gift_cards ADD COLUMN donation_entry_id INT(11) DEFAULT NULL AFTER donation_qbo_account")) {
            throw new RuntimeException('Could not add linked donation entry id to gift cards: ' . $conn->error);
        }
    }

    if (!giftCardColumnExists($conn, 'is_archived')) {
        if (!$conn->query("ALTER TABLE gift_cards ADD COLUMN is_archived TINYINT(1) NOT NULL DEFAULT 0 AFTER donation_entry_id")) {
            throw new RuntimeException('Could not add archived flag to gift cards: ' . $conn->error);
        }
    }

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

    if (!$conn->query("UPDATE gift_cards SET expiration_date = DATE_ADD(issued_date, INTERVAL expiration_days DAY) WHERE expiration_date IS NULL")) {
        throw new RuntimeException('Could not backfill gift card expiration dates: ' . $conn->error);
    }
}

function giftCardExpirationOptions()
{
    return array(90, 180, 360);
}

function normalizeGiftCardExpirationDays($days)
{
    $days = (int) $days;
    if (!in_array($days, giftCardExpirationOptions(), true)) {
        return 180;
    }

    return $days;
}

function buildGiftCardSelectSql($expired)
{
    $where = $expired ? 'expiration_date <= CURDATE()' : 'expiration_date > CURDATE()';

    return "
        SELECT id, card_label, card_number, initial_amount, issued_date, expiration_days, expiration_date, is_donation, donation_recipient, donation_qbo_account, donation_entry_id, notes, created_at
        FROM gift_cards
        WHERE {$where}
          AND is_archived = 0
        ORDER BY expiration_date ASC, issued_date DESC, id DESC
    ";
}

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

    return $scope;
}

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

    return $month;
}

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

    return array($start, $end);
}

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

    return $date;
}

function giftCardFilterClause($scope, $month, $startDate, $endDate, &$params, &$types)
{
    $scope = normalizeGiftCardExportScope($scope);
    $params = array();
    $types = '';

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

    if ($scope === 'range') {
        $startDate = normalizeGiftCardDate($startDate);
        $endDate = normalizeGiftCardDate($endDate);
        if ($startDate === '' || $endDate === '') {
            return '';
        }

        $params[] = $startDate;
        $params[] = $endDate;
        $types .= 'ss';
        return ' AND issued_date BETWEEN ? AND ?';
    }

    list($monthStart, $monthEnd) = giftCardMonthRange($month);
    $params[] = $monthStart;
    $params[] = $monthEnd;
    $types .= 'ss';
    return ' AND issued_date BETWEEN ? AND ?';
}

function fetchGiftCards($conn, $taxRate, $scope, $month, $startDate, $endDate)
{
    ensureGiftCardTables($conn);
    return fetchGiftCardsByVisibilityAndFilter($conn, $taxRate, false, false, $scope, $month, $startDate, $endDate);
}

function fetchExpiredGiftCards($conn, $taxRate)
{
    ensureGiftCardTables($conn);
    return fetchGiftCardsByVisibilityAndFilter($conn, $taxRate, true, false, 'all', '', '', '');
}

function fetchArchivedGiftCards($conn, $taxRate)
{
    ensureGiftCardTables($conn);
    return fetchGiftCardsByVisibilityAndFilter($conn, $taxRate, false, true, 'all', '', '', '');
}

function fetchGiftCardsByVisibilityAndFilter($conn, $taxRate, $expired, $archived, $scope, $month, $startDate, $endDate)
{
    ensureGiftCardTables($conn);

    $whereParts = array();
    if ($archived) {
        $whereParts[] = 'is_archived = 1';
    } else {
        $whereParts[] = 'is_archived = 0';
        $whereParts[] = $expired ? 'expiration_date <= CURDATE()' : 'expiration_date > CURDATE()';
    }

    $params = array();
    $types = '';
    $dateClause = giftCardFilterClause($scope, $month, $startDate, $endDate, $params, $types);
    if ($dateClause !== '') {
        $whereParts[] = substr($dateClause, 5);
    }

    $orderBy = $archived
        ? 'archived_at DESC, expiration_date DESC, id DESC'
        : ($expired ? 'expiration_date ASC, issued_date DESC, id DESC' : 'issued_date DESC, id DESC');

    $sql = "
        SELECT id, card_label, card_number, initial_amount, issued_date, expiration_days, expiration_date, is_donation, donation_recipient, donation_qbo_account, donation_entry_id, notes, archived_at, created_at
        FROM gift_cards
        WHERE " . implode(' AND ', $whereParts) . "
        ORDER BY {$orderBy}
    ";

    if ($types === '') {
        $result = $conn->query($sql);
        if (!$result) {
            throw new RuntimeException('Could not load gift cards: ' . $conn->error);
        }
        $rows = $result->fetch_all(MYSQLI_ASSOC);
    } else {
        $stmt = $conn->prepare($sql);
        if (!$stmt) {
            throw new RuntimeException('Could not prepare the gift card 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['spent_total'] = giftCardSpentTotal($conn, (int) $row['id'], $taxRate);
        $row['remaining_balance'] = giftCardRemainingBalance($row, $row['spent_total']);
        $row['amount_owed'] = giftCardAmountOwed($row['remaining_balance']);
    }
    unset($row);

    return $rows;
}

function addGiftCard($conn, $cardLabel, $cardNumber, $initialAmount, $issuedDate, $expirationDays, $notes, $isDonation, $donationRecipient, $donationQboAccount)
{
    ensureGiftCardTables($conn);

    $cardLabel = trim((string) $cardLabel);
    $cardNumber = trim((string) $cardNumber);
    $initialAmount = trim((string) $initialAmount);
    $issuedDate = trim((string) $issuedDate);
    $expirationDays = normalizeGiftCardExpirationDays($expirationDays);
    $notes = trim((string) $notes);
    $isDonation = (int) $isDonation === 1 ? 1 : 0;
    $donationRecipient = trim((string) $donationRecipient);
    $donationQboAccount = trim((string) $donationQboAccount);

    if ($cardLabel === '') {
        throw new RuntimeException('Gift card label is required.');
    }

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

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

    if ($isDonation === 1) {
        if ($donationRecipient === '') {
            throw new RuntimeException('Donation recipient / organization is required when this gift card was donated.');
        }

        if ($donationQboAccount === '') {
            throw new RuntimeException('Donation QBO account/category is required when this gift card was donated.');
        }
    }

    $initialAmountValue = number_format((float) $initialAmount, 2, '.', '');
    $expirationDate = date('Y-m-d', strtotime($issuedDate . ' + ' . $expirationDays . ' days'));
    $donationEntryId = null;

    if ($isDonation === 1) {
        $donationNotes = 'Gift card donation';
        if ($cardNumber !== '') {
            $donationNotes .= ' - Card #' . $cardNumber;
        }
        if ($notes !== '') {
            $donationNotes .= ' - ' . $notes;
        }

        addDonation(
            $conn,
            $issuedDate,
            $donationRecipient,
            $donationQboAccount,
            'Gift Card - ' . $cardLabel,
            '1.00',
            $initialAmountValue,
            $donationNotes
        );
        $donationEntryId = (int) $conn->insert_id;
    }

    $stmt = $conn->prepare("
        INSERT INTO gift_cards (
            card_label,
            card_number,
            initial_amount,
            issued_date,
            expiration_days,
            expiration_date,
            is_donation,
            donation_recipient,
            donation_qbo_account,
            donation_entry_id,
            notes
        ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
    ");
    $stmt->bind_param('ssssisisiss', $cardLabel, $cardNumber, $initialAmountValue, $issuedDate, $expirationDays, $expirationDate, $isDonation, $donationRecipient, $donationQboAccount, $donationEntryId, $notes);
    $stmt->execute();
    $stmt->close();
}

function fetchGiftCard($conn, $giftCardId, $taxRate)
{
    ensureGiftCardTables($conn);

    $stmt = $conn->prepare("
        SELECT id, card_label, card_number, initial_amount, issued_date, expiration_days, expiration_date, is_donation, donation_recipient, donation_qbo_account, donation_entry_id, notes, created_at
        FROM gift_cards
        WHERE id = ?
          AND is_archived = 0
        LIMIT 1
    ");
    $stmt->bind_param('i', $giftCardId);
    $stmt->execute();
    $result = $stmt->get_result();
    $card = $result ? $result->fetch_assoc() : null;
    $stmt->close();

    if (!$card) {
        throw new RuntimeException('Gift card not found.');
    }

    $transactions = fetchGiftCardTransactions($conn, $giftCardId, $taxRate);
    $card['transactions'] = $transactions;
    $card['spent_total'] = giftCardTransactionsTotal($transactions);
    $card['remaining_balance'] = giftCardRemainingBalance($card, $card['spent_total']);
    $card['amount_owed'] = giftCardAmountOwed($card['remaining_balance']);

    return $card;
}

function restoreExpiredGiftCard($conn, $giftCardId, $expirationDays)
{
    ensureGiftCardTables($conn);

    if ((int) $giftCardId <= 0) {
        throw new RuntimeException('Invalid gift card selected.');
    }

    $expirationDays = normalizeGiftCardExpirationDays($expirationDays);
    $today = date('Y-m-d');
    $expirationDate = date('Y-m-d', strtotime($today . ' + ' . $expirationDays . ' days'));

    $stmt = $conn->prepare("
        UPDATE gift_cards
        SET expiration_days = ?, expiration_date = ?
        WHERE id = ?
        LIMIT 1
    ");
    $stmt->bind_param('isi', $expirationDays, $expirationDate, $giftCardId);
    $stmt->execute();
    $stmt->close();
}

function restoreGiftCardOnly($conn, $giftCardId)
{
    ensureGiftCardTables($conn);

    if ((int) $giftCardId <= 0) {
        throw new RuntimeException('Invalid gift card selected.');
    }

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

function archiveGiftCardOnly($conn, $giftCardId)
{
    ensureGiftCardTables($conn);

    if ((int) $giftCardId <= 0) {
        throw new RuntimeException('Invalid gift card selected.');
    }

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

function archiveGiftCardAndLinkedDonation($conn, $giftCardId)
{
    ensureGiftCardTables($conn);

    $stmt = $conn->prepare("SELECT donation_entry_id FROM gift_cards WHERE id = ? LIMIT 1");
    $stmt->bind_param('i', $giftCardId);
    $stmt->execute();
    $result = $stmt->get_result();
    $row = $result ? $result->fetch_assoc() : null;
    $stmt->close();

    if ($row && !empty($row['donation_entry_id'])) {
        archiveDonation($conn, (int) $row['donation_entry_id']);
    }

    archiveGiftCardOnly($conn, $giftCardId);
}

function restoreGiftCardAndLinkedDonation($conn, $giftCardId)
{
    ensureGiftCardTables($conn);

    $stmt = $conn->prepare("SELECT donation_entry_id FROM gift_cards WHERE id = ? LIMIT 1");
    $stmt->bind_param('i', $giftCardId);
    $stmt->execute();
    $result = $stmt->get_result();
    $row = $result ? $result->fetch_assoc() : null;
    $stmt->close();

    if ($row && !empty($row['donation_entry_id'])) {
        restoreDonation($conn, (int) $row['donation_entry_id']);
    }

    restoreGiftCardOnly($conn, $giftCardId);
}

function deleteGiftCardOnly($conn, $giftCardId)
{
    ensureGiftCardTables($conn);

    if ((int) $giftCardId <= 0) {
        throw new RuntimeException('Invalid gift card selected.');
    }

    $stmt = $conn->prepare("DELETE FROM gift_card_transactions WHERE gift_card_id = ?");
    $stmt->bind_param('i', $giftCardId);
    $stmt->execute();
    $stmt->close();

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

function deleteGiftCardAndLinkedDonation($conn, $giftCardId)
{
    ensureGiftCardTables($conn);

    $stmt = $conn->prepare("SELECT donation_entry_id FROM gift_cards WHERE id = ? LIMIT 1");
    $stmt->bind_param('i', $giftCardId);
    $stmt->execute();
    $result = $stmt->get_result();
    $row = $result ? $result->fetch_assoc() : null;
    $stmt->close();

    $donationEntryId = $row && !empty($row['donation_entry_id']) ? (int) $row['donation_entry_id'] : 0;

    deleteGiftCardOnly($conn, $giftCardId);

    if ($donationEntryId > 0) {
        deleteDonation($conn, $donationEntryId);
    }
}

function fetchGiftCardTransactions($conn, $giftCardId, $taxRate)
{
    ensureGiftCardTables($conn);

    $stmt = $conn->prepare("
        SELECT id, gift_card_id, transaction_date, item_description, quantity, unit_price, taxable, notes, created_at
        FROM gift_card_transactions
        WHERE gift_card_id = ?
        ORDER BY transaction_date DESC, id DESC
    ");
    $stmt->bind_param('i', $giftCardId);
    $stmt->execute();
    $result = $stmt->get_result();
    $rows = $result ? $result->fetch_all(MYSQLI_ASSOC) : array();
    $stmt->close();

    foreach ($rows as &$row) {
        $row['subtotal'] = giftCardTransactionSubtotal($row);
        $row['tax_amount'] = giftCardTransactionTax($row, $taxRate);
        $row['line_total'] = $row['subtotal'] + $row['tax_amount'];
    }
    unset($row);

    return $rows;
}

function addGiftCardTransaction($conn, $giftCardId, $transactionDate, $itemDescription, $quantity, $unitPrice, $taxable, $notes)
{
    ensureGiftCardTables($conn);

    if ((int) $giftCardId <= 0) {
        throw new RuntimeException('Invalid gift card selected.');
    }

    $transactionDate = trim((string) $transactionDate);
    $itemDescription = trim((string) $itemDescription);
    $quantity = trim((string) $quantity);
    $unitPrice = trim((string) $unitPrice);
    $notes = trim((string) $notes);
    $taxable = (int) $taxable === 1 ? 1 : 0;

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

    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 gift_card_transactions (
            gift_card_id,
            transaction_date,
            item_description,
            quantity,
            unit_price,
            taxable,
            notes
        ) VALUES (?, ?, ?, ?, ?, ?, ?)
    ");
    $stmt->bind_param('issssis', $giftCardId, $transactionDate, $itemDescription, $quantityValue, $unitPriceValue, $taxable, $notes);
    $stmt->execute();
    $stmt->close();
}

function updateGiftCardInfo($conn, $giftCardId, $cardLabel, $cardNumber, $notes)
{
    ensureGiftCardTables($conn);

    if ((int) $giftCardId <= 0) {
        throw new RuntimeException('Invalid gift card selected.');
    }

    $cardLabel = trim((string) $cardLabel);
    $cardNumber = trim((string) $cardNumber);
    $notes = trim((string) $notes);

    if ($cardLabel === '') {
        throw new RuntimeException('Gift card label is required.');
    }

    $stmt = $conn->prepare("
        UPDATE gift_cards
        SET card_label = ?, card_number = ?, notes = ?
        WHERE id = ?
        LIMIT 1
    ");
    $stmt->bind_param('sssi', $cardLabel, $cardNumber, $notes, $giftCardId);
    $stmt->execute();
    $stmt->close();
}

function updateGiftCardTransaction($conn, $transactionId, $quantity, $unitPrice, $taxable, $notes)
{
    ensureGiftCardTables($conn);

    if ((int) $transactionId <= 0) {
        throw new RuntimeException('Invalid gift card transaction selected.');
    }

    $quantity = trim((string) $quantity);
    $unitPrice = trim((string) $unitPrice);
    $notes = trim((string) $notes);
    $taxable = (int) $taxable === 1 ? 1 : 0;

    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 gift_card_transactions
        SET quantity = ?, unit_price = ?, taxable = ?, notes = ?
        WHERE id = ?
        LIMIT 1
    ");
    $stmt->bind_param('ssisi', $quantityValue, $unitPriceValue, $taxable, $notes, $transactionId);
    $stmt->execute();
    $stmt->close();
}

function deleteGiftCardTransaction($conn, $transactionId)
{
    ensureGiftCardTables($conn);

    if ((int) $transactionId <= 0) {
        throw new RuntimeException('Invalid gift card transaction selected.');
    }

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

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

    return $quantity * $unitPrice;
}

function giftCardTransactionTax($row, $taxRate)
{
    if (empty($row['taxable'])) {
        return 0.0;
    }

    return giftCardTransactionSubtotal($row) * (float) $taxRate;
}

function giftCardTransactionsTotal($rows)
{
    $total = 0.0;
    foreach ($rows as $row) {
        $total += isset($row['line_total']) ? (float) $row['line_total'] : 0.0;
    }

    return $total;
}

function giftCardSpentTotal($conn, $giftCardId, $taxRate)
{
    $transactions = fetchGiftCardTransactions($conn, $giftCardId, $taxRate);
    return giftCardTransactionsTotal($transactions);
}

function giftCardRemainingBalance($card, $spentTotal)
{
    $initialAmount = isset($card['initial_amount']) ? (float) $card['initial_amount'] : 0.0;
    return $initialAmount - (float) $spentTotal;
}

function giftCardAmountOwed($remainingBalance)
{
    $remainingBalance = (float) $remainingBalance;
    if ($remainingBalance >= 0) {
        return 0.0;
    }

    return abs($remainingBalance);
}

function giftCardCounts($conn)
{
    ensureGiftCardTables($conn);

    $counts = array(
        'active' => 0,
        'expired' => 0,
        'archived' => 0,
    );

    $queries = array(
        'active' => "SELECT COUNT(*) AS total FROM gift_cards WHERE is_archived = 0 AND expiration_date > CURDATE()",
        'expired' => "SELECT COUNT(*) AS total FROM gift_cards WHERE is_archived = 0 AND expiration_date <= CURDATE()",
        'archived' => "SELECT COUNT(*) AS total FROM gift_cards WHERE is_archived = 1",
    );

    foreach ($queries as $key => $sql) {
        $result = $conn->query($sql);
        if (!$result) {
            throw new RuntimeException('Could not load gift card counts: ' . $conn->error);
        }
        $row = $result->fetch_assoc();
        $counts[$key] = $row ? (int) $row['total'] : 0;
    }

    return $counts;
}

function quickFindGiftCard($conn, $lookup, $taxRate)
{
    ensureGiftCardTables($conn);

    $lookup = trim((string) $lookup);
    if ($lookup === '') {
        return null;
    }

    $like = '%' . $lookup . '%';
    $stmt = $conn->prepare("
        SELECT id
        FROM gift_cards
        WHERE is_archived = 0
          AND (card_label LIKE ? OR card_number LIKE ?)
        ORDER BY expiration_date ASC, id DESC
        LIMIT 1
    ");
    if (!$stmt) {
        throw new RuntimeException('Could not search gift cards: ' . $conn->error);
    }
    $stmt->bind_param('ss', $like, $like);
    $stmt->execute();
    $result = $stmt->get_result();
    $row = $result ? $result->fetch_assoc() : null;
    $stmt->close();

    if (!$row) {
        return null;
    }

    return fetchGiftCard($conn, (int) $row['id'], $taxRate);
}

function soonExpiringGiftCards($conn, $taxRate, $daysAhead)
{
    ensureGiftCardTables($conn);

    $daysAhead = (int) $daysAhead;
    if ($daysAhead <= 0) {
        $daysAhead = 14;
    }

    $stmt = $conn->prepare("
        SELECT id, card_label, card_number, initial_amount, issued_date, expiration_days, expiration_date, is_donation, donation_recipient, donation_qbo_account, donation_entry_id, notes, archived_at, created_at
        FROM gift_cards
        WHERE is_archived = 0
          AND expiration_date > CURDATE()
          AND expiration_date <= DATE_ADD(CURDATE(), INTERVAL ? DAY)
        ORDER BY expiration_date ASC, id ASC
    ");
    if (!$stmt) {
        throw new RuntimeException('Could not load soon-to-expire gift cards: ' . $conn->error);
    }
    $stmt->bind_param('i', $daysAhead);
    $stmt->execute();
    $result = $stmt->get_result();
    $rows = $result ? $result->fetch_all(MYSQLI_ASSOC) : array();
    $stmt->close();

    foreach ($rows as &$row) {
        $row['spent_total'] = giftCardSpentTotal($conn, (int) $row['id'], $taxRate);
        $row['remaining_balance'] = giftCardRemainingBalance($row, $row['spent_total']);
        $row['amount_owed'] = giftCardAmountOwed($row['remaining_balance']);
        $row['days_until_expiration'] = (int) floor((strtotime($row['expiration_date']) - strtotime(date('Y-m-d'))) / 86400);
    }
    unset($row);

    return $rows;
}

function transferableGiftCards($conn, $taxRate, $excludeGiftCardId)
{
    $rows = fetchGiftCards($conn, $taxRate, 'all', '', '', '');
    $excludeGiftCardId = (int) $excludeGiftCardId;

    return array_values(array_filter($rows, function ($row) use ($excludeGiftCardId) {
        return (int) $row['id'] !== $excludeGiftCardId;
    }));
}

function transferGiftCardBalance($conn, $sourceGiftCardId, $targetGiftCardId)
{
    ensureGiftCardTables($conn);

    $sourceGiftCardId = (int) $sourceGiftCardId;
    $targetGiftCardId = (int) $targetGiftCardId;

    if ($sourceGiftCardId <= 0 || $targetGiftCardId <= 0 || $sourceGiftCardId === $targetGiftCardId) {
        throw new RuntimeException('Choose two different gift cards for the transfer.');
    }

    $stmt = $conn->prepare("
        SELECT id, card_label, initial_amount, is_archived
        FROM gift_cards
        WHERE id IN (?, ?)
    ");
    if (!$stmt) {
        throw new RuntimeException('Could not load gift cards for transfer: ' . $conn->error);
    }
    $stmt->bind_param('ii', $sourceGiftCardId, $targetGiftCardId);
    $stmt->execute();
    $result = $stmt->get_result();
    $cards = array();
    while ($row = $result->fetch_assoc()) {
        $cards[(int) $row['id']] = $row;
    }
    $stmt->close();

    if (!isset($cards[$sourceGiftCardId]) || !isset($cards[$targetGiftCardId])) {
        throw new RuntimeException('One of the selected gift cards could not be found.');
    }

    if (!empty($cards[$sourceGiftCardId]['is_archived']) || !empty($cards[$targetGiftCardId]['is_archived'])) {
        throw new RuntimeException('Archived gift cards cannot be used for a transfer.');
    }

    $taxRate = 0.0;
    $sourceCard = fetchGiftCard($conn, $sourceGiftCardId, $taxRate);
    $targetCard = fetchGiftCard($conn, $targetGiftCardId, $taxRate);

    $remainingBalance = (float) $sourceCard['remaining_balance'];
    if ($remainingBalance <= 0) {
        throw new RuntimeException('The source gift card does not have a remaining balance to transfer.');
    }

    $newTargetAmount = (float) $targetCard['initial_amount'] + $remainingBalance;

    $conn->begin_transaction();
    try {
        $newTargetAmountValue = number_format($newTargetAmount, 2, '.', '');
        $targetNote = trim((string) $targetCard['notes']);
        $transferNote = 'Received balance transfer of $' . number_format($remainingBalance, 2) . ' from ' . $sourceCard['card_label'];
        $targetNote = $targetNote === '' ? $transferNote : ($targetNote . ' | ' . $transferNote);

        $stmt = $conn->prepare("
            UPDATE gift_cards
            SET initial_amount = ?, notes = ?
            WHERE id = ?
            LIMIT 1
        ");
        $stmt->bind_param('ssi', $newTargetAmountValue, $targetNote, $targetGiftCardId);
        $stmt->execute();
        $stmt->close();

        $sourceNote = trim((string) $sourceCard['notes']);
        $sourceTransferNote = 'Transferred remaining balance of $' . number_format($remainingBalance, 2) . ' to ' . $targetCard['card_label'];
        $sourceNote = $sourceNote === '' ? $sourceTransferNote : ($sourceNote . ' | ' . $sourceTransferNote);

        $stmt = $conn->prepare("
            UPDATE gift_cards
            SET initial_amount = '0.00', notes = ?
            WHERE id = ?
            LIMIT 1
        ");
        $stmt->bind_param('si', $sourceNote, $sourceGiftCardId);
        $stmt->execute();
        $stmt->close();

        $conn->commit();
    } catch (Exception $e) {
        $conn->rollback();
        throw $e;
    }

    return $remainingBalance;
}

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

    fputcsv($handle, array(
        'Card Label',
        'Card Number',
        'Issued Date',
        'Expiration Date',
        'Initial Amount',
        'Spent',
        'Remaining Balance',
        'Amount Owed',
        'Donation Card',
        'Donation Recipient',
        'Notes',
    ));

    foreach ($rows as $row) {
        fputcsv($handle, array(
            isset($row['card_label']) ? $row['card_label'] : '',
            isset($row['card_number']) ? $row['card_number'] : '',
            isset($row['issued_date']) ? $row['issued_date'] : '',
            isset($row['expiration_date']) ? $row['expiration_date'] : '',
            number_format((float) (isset($row['initial_amount']) ? $row['initial_amount'] : 0), 2, '.', ''),
            number_format((float) (isset($row['spent_total']) ? $row['spent_total'] : 0), 2, '.', ''),
            number_format((float) (isset($row['remaining_balance']) ? $row['remaining_balance'] : 0), 2, '.', ''),
            number_format((float) (isset($row['amount_owed']) ? $row['amount_owed'] : 0), 2, '.', ''),
            !empty($row['is_donation']) ? 'Yes' : 'No',
            isset($row['donation_recipient']) ? $row['donation_recipient'] : '',
            isset($row['notes']) ? $row['notes'] : '',
        ));
    }

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

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

function giftCardExportFilename($scope, $month, $startDate, $endDate)
{
    $scope = normalizeGiftCardExportScope($scope);
    if ($scope === 'range') {
        $startDate = normalizeGiftCardDate($startDate);
        $endDate = normalizeGiftCardDate($endDate);
        if ($startDate !== '' && $endDate !== '') {
            return 'gift_cards_' . str_replace('-', '_', $startDate) . '_to_' . str_replace('-', '_', $endDate) . '.csv';
        }
    }

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

    return 'gift_cards_' . str_replace('-', '_', normalizeGiftCardMonth($month)) . '.csv';
}