<?php

require_once __DIR__ . '/db.php';
require_once __DIR__ . '/pricing.php';

function sparrow_get_orders(): array
{
    $pdo = sparrow_db();

    $stmt = $pdo->query("
        SELECT
            o.*,
            COALESCE(SUM(oi.quantity), o.quantity) AS total_quantity,
            COUNT(oi.id) AS item_count,
            GROUP_CONCAT(
                CONCAT(
                    oi.quantity,
                    ' ',
                    COALESCE(oi.shirt_color, ''),
                    ' ',
                    COALESCE(oi.shirt_size, ''),
                    ' ',
                    COALESCE(oi.print_location, '')
                )
                SEPARATOR ' | '
            ) AS item_summary
        FROM orders o
        LEFT JOIN order_items oi
            ON oi.order_id = o.id
		WHERE COALESCE(o.trashed, 0) = 0
        GROUP BY o.id
        ORDER BY o.order_date DESC, o.id DESC
    ");

    return $stmt->fetchAll();
}

function sparrow_get_dashboard_totals(): array
{
    $orders = sparrow_get_orders();

    $totals = [
        'order_count' => 0,
        'item_count' => 0,
        'total_quantity' => 0,
        'total_revenue' => 0,
        'total_collected' => 0,
        'total_blessings' => 0,
        'total_discounts' => 0,
        'total_internal_costs' => 0,
        'outstanding_balance' => 0,
        'estimated_profit' => 0,
		'total_blank_costs' => 0,
		'unpaid_count' => 0,
'partial_count' => 0,
'paid_count' => 0,
'ready_count' => 0,
'production_count' => 0,
'delivered_count' => 0,
'open_count' => 0,
    ];

    foreach ($orders as $order) {
        $total_amount = (float) ($order['total_amount'] ?? 0);

if ($total_amount <= 0) {
    if (!function_exists('sparrow_calculate_order_pricing_from_lookup')) {
        require_once __DIR__ . '/pricing.php';
    }

    $pricing_lookup = sparrow_calculate_order_pricing_from_lookup((int) $order['id']);
    $total_amount = (float) ($pricing_lookup['retail_total'] ?? 0);
}
        $paid_amount = (float) ($order['paid_amount'] ?? 0);
$blessing_amount = (float) ($order['blessing_amount'] ?? 0);
$order_discount_amount = (float) ($order['order_discount_amount'] ?? 0);

$supplier_item_cost = (float) ($order['supplier_item_cost'] ?? 0);
$shipping_cost = (float) ($order['shipping_cost'] ?? 0);
$tax_cost = (float) ($order['tax_cost'] ?? 0);
$other_cost = (float) ($order['other_cost'] ?? 0);
$discount_cost = (float) ($order['discount_cost'] ?? 0);
		
		$blank_cost_total = 0.00;

if (!function_exists('sparrow_calculate_order_pricing_from_lookup')) {
    require_once __DIR__ . '/pricing.php';
}

$pricing_lookup = sparrow_calculate_order_pricing_from_lookup((int) $order['id']);
$blank_cost_estimate = (float) ($pricing_lookup['blank_cost_total'] ?? 0);

/*
 * Use the actual Supplier Splitter item cost when available.
 * Otherwise, fall back to the estimated blank cost.
 */
$blank_cost_total = $supplier_item_cost > 0
    ? 0.00
    : $blank_cost_estimate;

$production_cost = $supplier_item_cost > 0
    ? $supplier_item_cost
    : $blank_cost_total;

$internal_costs =
    $production_cost
    + $shipping_cost
    + $tax_cost
    + $other_cost
    - $discount_cost;
        $order_discount_amount = (float) ($order['order_discount_amount'] ?? 0);
$customer_total_after_discount = max(0, $total_amount - $order_discount_amount);
$balance = max(0, $customer_total_after_discount - $paid_amount);

        $totals['order_count']++;
        $totals['item_count'] += (int) ($order['item_count'] ?? 0);
        $totals['total_quantity'] += (int) ($order['total_quantity'] ?? $order['quantity'] ?? 0);

        $totals['total_revenue'] += $total_amount;
        $totals['total_collected'] += $paid_amount;
        $totals['total_blessings'] += $blessing_amount;
        $totals['total_discounts'] += $order_discount_amount;
        $totals['total_internal_costs'] += $internal_costs;
		$totals['total_blank_costs'] += $blank_cost_total;

        if ($balance > 0) {
            $totals['outstanding_balance'] += $balance;
        }

        $totals['estimated_profit'] += ($customer_total_after_discount - $internal_costs);
		
		$status = trim((string) ($order['status'] ?? ''));

if ($customer_total_after_discount <= 0 && $paid_amount <= 0) {
    $totals['unpaid_count']++;
} elseif ($paid_amount <= 0) {
    $totals['unpaid_count']++;
} elseif ($paid_amount < $customer_total_after_discount) {
    $totals['partial_count']++;
} else {
    $totals['paid_count']++;
}

if (in_array($status, ['Products Ordered', 'Pressing', 'Packaging'], true)) {
    $totals['production_count']++;
}

if ($status === 'Ready for Pickup') {
    $totals['ready_count']++;
}

if ($status === 'Delivered') {
    $totals['delivered_count']++;
}

if (!in_array($status, ['Delivered', 'Completed', 'Archived', 'Merged'], true)) {
    $totals['open_count']++;
}
    }

    return $totals;
}

function sparrow_get_next_order_number(): string
{
    $pdo = sparrow_db();

    $stmt = $pdo->query("
        SELECT MAX(CAST(SUBSTRING(order_number, 5) AS UNSIGNED)) AS max_number
        FROM orders
        WHERE order_number REGEXP '^SSC-[0-9]+$'
    ");

    $max_number = (int) $stmt->fetchColumn();

    $next_number = $max_number + 1;

    while (true) {
        $order_number = 'SSC-' . str_pad((string) $next_number, 3, '0', STR_PAD_LEFT);

        $check_stmt = $pdo->prepare("
            SELECT id
            FROM orders
            WHERE order_number = :order_number
            LIMIT 1
        ");

        $check_stmt->execute([
            ':order_number' => $order_number,
        ]);

        if (!$check_stmt->fetch()) {
            return $order_number;
        }

        $next_number++;
    }
}

function sparrow_build_order_data_from_post(array $post, bool $is_admin = false): array
{
    $product_type = trim((string) ($post['product_type'] ?? ''));

    $customer_name = trim((string) ($post['customer_name'] ?? ''));
    $customer_email = trim((string) ($post['customer_email'] ?? ''));
    $customer_phone = trim((string) ($post['customer_phone'] ?? ''));

    if ($customer_name === '') {
        throw new RuntimeException('Customer name is required.');
    }

    if (!$is_admin && $customer_email === '') {
    throw new RuntimeException(
        'Customer email is required.'
    );
}

    if ($product_type === '') {
        throw new RuntimeException('Product type is required.');
    }

    if (empty($post['agreement_checked']) && !$is_admin) {
        throw new RuntimeException('Agreement is required.');
    }

    $status = trim((string) ($post['order_status'] ?? 'Order Received'));

    if ($status === '') {
        $status = 'Order Received';
    }

    $order_number = '';

    if ($is_admin) {
        $order_number = trim((string) ($post['order_number'] ?? ''));
    }
	
	$order_date = date('Y-m-d');

if ($is_admin) {
    $requested_order_date = trim(
        (string) ($post['order_date'] ?? '')
    );

    if ($requested_order_date !== '') {
        $date_check = DateTime::createFromFormat(
            'Y-m-d',
            $requested_order_date
        );

        if (
            !$date_check ||
            $date_check->format('Y-m-d')
                !== $requested_order_date
        ) {
            throw new RuntimeException(
                'Please enter a valid order date.'
            );
        }

        $order_date = $requested_order_date;
    }
}

    if ($order_number === '') {
        $order_number = sparrow_get_next_order_number();
    }

    $notes_parts = [];

    $group_name = trim((string) ($post['group_name'] ?? ''));
    $order_notes = trim((string) ($post['order_notes'] ?? ''));

    if ($group_name !== '') {
        $notes_parts[] = 'Group Name: ' . $group_name;
    }

    if ($order_notes !== '') {
        $notes_parts[] = $order_notes;
    }

    $items = [];

    if ($product_type === 'Shirt') {
        $sizes = $post['shirt_size'] ?? [];
        $quantities = $post['shirt_quantity'] ?? [];
        $specialties = $post['shirt_specialty'] ?? [];
        $colors = $post['shirt_color'] ?? [];
$customer_supplied =
    $post['shirt_customer_supplied'] ?? [];
$placements = $post['shirt_placement'] ?? [];
$notes = $post['shirt_notes'] ?? [];

        foreach ($quantities as $index => $quantity) {
            $quantity = (int) $quantity;

            if ($quantity < 1) {
                continue;
            }
			
			$is_customer_supplied =
    !empty($customer_supplied[$index]);

            $items[] = [
                'item_type' => 'Shirt',
                'quantity' => $quantity,
                'shirt_size' => trim((string) ($sizes[$index] ?? '')),
                'shirt_color' => $is_customer_supplied
    ? ''
    : trim((string) ($colors[$index] ?? '')),

'customer_supplied' => $is_customer_supplied ? 1 : 0,
                'specialty' => trim((string) ($specialties[$index] ?? 'None')),
                'print_location' => trim((string) ($placements[$index] ?? '')),
                'item_notes' => trim((string) ($notes[$index] ?? '')),
            ];
        }
    }

    if ($product_type === 'Koozie') {
        $quantities = $post['koozie_quantity'] ?? [];
$colors = $post['koozie_color'] ?? [];
$placements = $post['koozie_placement'] ?? [];
$notes = $post['koozie_notes'] ?? [];

        foreach ($quantities as $index => $quantity) {
            $quantity = (int) $quantity;

            if ($quantity < 1) {
                continue;
            }

            $items[] = [
                'item_type' => 'Koozie',
                'quantity' => $quantity,
                'shirt_size' => null,
                'shirt_color' => trim((string) ($colors[$index] ?? '')),
				'customer_supplied' => 0,
                'specialty' => 'None',
                'print_location' => trim(
    (string) ($placements[$index] ?? 'Front')
),
                'item_notes' => trim((string) ($notes[$index] ?? '')),
            ];
        }
    }

    if (!$items) {
        throw new RuntimeException('At least one item is required.');
    }

    return [
        'order_number' => $order_number,
        'customer_name' => $customer_name,
        'customer_email' => $customer_email,
        'customer_phone' => $customer_phone,
        'order_date' => $order_date,
        'status' => $status,
        'notes' => implode("\n\n", $notes_parts),
        'items' => $items,
    ];
}

function sparrow_create_order(array $data): int
{
    $pdo = sparrow_db();

    $pdo->beginTransaction();

    try {
        $items = $data['items'] ?? [];

        if (!$items) {
            throw new RuntimeException('At least one order item is required.');
        }

        $first_item = $items[0];

        $total_quantity = 0;

        foreach ($items as $item) {
            $total_quantity += max(1, (int) ($item['quantity'] ?? 1));
        }

        $stmt = $pdo->prepare("
            INSERT INTO orders
            (
                order_number,
                original_forminator_entry_id,
                customer_name,
                customer_email,
                customer_phone,
                order_date,
                status,
                quantity,
                shirt_size,
                shirt_color,
                specialty,
                print_location,
                notes
            )
            VALUES
            (
                :order_number,
                NULL,
                :customer_name,
                :customer_email,
                :customer_phone,
                :order_date,
                :status,
                :quantity,
                :shirt_size,
                :shirt_color,
                :specialty,
                :print_location,
                :notes
            )
        ");

        $stmt->execute([
            ':order_number' => $data['order_number'],
            ':customer_name' => $data['customer_name'],
            ':customer_email' => $data['customer_email'] ?: null,
            ':customer_phone' => $data['customer_phone'] ?: null,
            ':order_date' => $data['order_date'],
            ':status' => $data['status'],
            ':quantity' => $total_quantity,
            ':shirt_size' => $first_item['shirt_size'] ?? null,
            ':shirt_color' => $first_item['shirt_color'] ?? null,
            ':specialty' => $first_item['specialty'] ?? null,
            ':print_location' => $first_item['print_location'] ?? null,
            ':notes' => $data['notes'] ?: null,
        ]);

        $order_id = (int) $pdo->lastInsertId();

        $item_stmt = $pdo->prepare("
            INSERT INTO order_items
            (
                order_id,
                item_type,
                quantity,
                shirt_size,
                shirt_color,
				customer_supplied,
				is_gift,
                specialty,
                print_location,
                item_notes
            )
            VALUES
            (
                :order_id,
                :item_type,
                :quantity,
                :shirt_size,
                :shirt_color,
				:customer_supplied,
				:is_gift,
                :specialty,
                :print_location,
                :item_notes
            )
        ");

        foreach ($items as $item) {
                $item_stmt->execute([
    ':order_id' => $order_id,
    ':item_type' => $item['item_type'] ?: 'Shirt',
    ':quantity' => max(1, (int) $item['quantity']),
    ':shirt_size' => $item['shirt_size'] ?: null,
    ':shirt_color' => $item['shirt_color'] ?: null,
    ':customer_supplied' =>
        !empty($item['customer_supplied']) ? 1 : 0,
	':is_gift' =>
		!empty($item['is_gift']) ? 1 : 0,
    ':specialty' => $item['specialty'] ?: null,
    ':print_location' => $item['print_location'] ?: null,
    ':item_notes' => $item['item_notes'] ?: null,
]);
        }

        $pdo->commit();

        return $order_id;
    } catch (Throwable $e) {
        $pdo->rollBack();

        throw $e;
    }
}

function sparrow_get_order(int $order_id): ?array
{
    $pdo = sparrow_db();

    $stmt = $pdo->prepare("
        SELECT *
        FROM orders
        WHERE id = :id
        LIMIT 1
    ");

    $stmt->execute([
        ':id' => $order_id,
    ]);

    $order = $stmt->fetch();

    return $order ?: null;
}

function sparrow_get_order_items(int $order_id): array
{
    $pdo = sparrow_db();

    $stmt = $pdo->prepare("
        SELECT *
        FROM order_items
        WHERE order_id = :order_id
        ORDER BY id ASC
    ");

    $stmt->execute([
        ':order_id' => $order_id,
    ]);

    return $stmt->fetchAll();
}

function sparrow_update_order(int $order_id, array $data): void
{
    $pdo = sparrow_db();

    $pdo->beginTransaction();

    try {
        $items = $data['items'] ?? [];

        if (!$items) {
            throw new RuntimeException('At least one order item is required.');
        }

        $first_item = $items[0];

        $total_quantity = 0;

        foreach ($items as $item) {
            $total_quantity += max(1, (int) ($item['quantity'] ?? 1));
        }

        $stmt = $pdo->prepare("
            UPDATE orders
            SET
				customer_name = :customer_name,
				order_date = COALESCE(:order_date, order_date),
				customer_email = :customer_email,
                customer_phone = :customer_phone,
                status = :status,
                quantity = :quantity,
                shirt_size = :shirt_size,
                shirt_color = :shirt_color,
                specialty = :specialty,
                print_location = :print_location,
                notes = :notes
            WHERE id = :id
            LIMIT 1
        ");

        $stmt->execute([
            ':id' => $order_id,
            ':customer_name' => $data['customer_name'],
			':order_date' => !empty($data['order_date'])
				? $data['order_date']
				: null,
            ':customer_email' => $data['customer_email'] ?: null,
            ':customer_phone' => $data['customer_phone'] ?: null,
            ':status' => $data['status'],
            ':quantity' => $total_quantity,
            ':shirt_size' => $first_item['shirt_size'] ?? null,
            ':shirt_color' => $first_item['shirt_color'] ?? null,
            ':specialty' => $first_item['specialty'] ?? null,
            ':print_location' => $first_item['print_location'] ?? null,
            ':notes' => $data['notes'] ?: null,
        ]);

$existing_stmt = $pdo->prepare("
    SELECT id
    FROM order_items
    WHERE order_id = :order_id
");

$existing_stmt->execute([
    ':order_id' => $order_id,
]);

$existing_item_ids = array_fill_keys(
    array_map('intval', $existing_stmt->fetchAll(PDO::FETCH_COLUMN)),
    true
);

$update_item_stmt = $pdo->prepare("
    UPDATE order_items
    SET
        item_type = :item_type,
        quantity = :quantity,
        shirt_size = :shirt_size,
        shirt_color = :shirt_color,
		customer_supplied = :customer_supplied,
		is_gift = :is_gift,
        specialty = :specialty,
        print_location = :print_location,
        item_notes = :item_notes
    WHERE id = :id
      AND order_id = :order_id
    LIMIT 1
");

$insert_item_stmt = $pdo->prepare("
    INSERT INTO order_items
    (
        order_id,
        item_type,
        quantity,
        shirt_size,
        shirt_color,
        customer_supplied,
        is_gift,
        specialty,
        print_location,
        item_notes
    )
    VALUES
    (
        :order_id,
        :item_type,
        :quantity,
        :shirt_size,
        :shirt_color,
        :customer_supplied,
        :is_gift,
        :specialty,
        :print_location,
        :item_notes
    )
");

$kept_item_ids = [];

foreach ($items as $item) {
    $item_id = (int) ($item['id'] ?? 0);

    $item_data = [
        ':order_id' => $order_id,
        ':item_type' => $item['item_type'] ?: 'Shirt',
        ':quantity' => max(1, (int) $item['quantity']),
        ':shirt_size' => $item['shirt_size'] ?: null,
        ':shirt_color' => $item['shirt_color'] ?: null,
		':customer_supplied' =>
    !empty($item['customer_supplied']) ? 1 : 0,
		':is_gift' =>
    !empty($item['is_gift']) ? 1 : 0,
        ':specialty' => $item['specialty'] ?: null,
        ':print_location' => $item['print_location'] ?: null,
        ':item_notes' => $item['item_notes'] ?: null,
    ];

    if ($item_id > 0) {
        if (!isset($existing_item_ids[$item_id])) {
            throw new RuntimeException(
    'One of the order items could not be verified.'
);
        }

        $update_item_stmt->execute(
            [':id' => $item_id] + $item_data
        );

        $kept_item_ids[] = $item_id;
    } else {
        $insert_item_stmt->execute($item_data);

        $kept_item_ids[] = (int) $pdo->lastInsertId();
    }
}

$placeholders = implode(
    ',',
    array_fill(0, count($kept_item_ids), '?')
);

$delete_removed_stmt = $pdo->prepare("
    DELETE FROM order_items
    WHERE order_id = ?
      AND id NOT IN ($placeholders)
");

$delete_removed_stmt->execute(
    array_merge([$order_id], $kept_item_ids)
);

        $pdo->commit();
    } catch (Throwable $e) {
        $pdo->rollBack();

        throw $e;
    }
}

function sparrow_update_order_payment(int $order_id, array $data): void
{
    $pdo = sparrow_db();

    $stmt = $pdo->prepare("
        UPDATE orders
        SET
            total_amount = :total_amount,
            paid_amount = :paid_amount,
            payment_method = :payment_method,
            blessing_amount = :blessing_amount,
            order_discount_amount = :order_discount_amount,
			order_shipping_amount = :order_shipping_amount
        WHERE id = :id
        LIMIT 1
    ");

    $stmt->execute([
        ':id' => $order_id,
        ':total_amount' => $data['total_amount'] ?? 0,
        ':paid_amount' => $data['paid_amount'] ?? 0,
        ':payment_method' => trim((string) ($data['payment_method'] ?? '')) ?: null,
        ':blessing_amount' => $data['blessing_amount'] ?? 0,
        ':order_discount_amount' => $data['order_discount_amount'] ?? 0,
		':order_shipping_amount' => $data['order_shipping_amount'] ?? 0,
    ]);
}

function sparrow_update_order_gift(int $order_id, bool $is_gift): void
{
    $pdo = sparrow_db();

    $stmt = $pdo->prepare("
        UPDATE orders
        SET is_gift = :is_gift
        WHERE id = :id
        LIMIT 1
    ");

    $stmt->execute([
        ':is_gift' => $is_gift ? 1 : 0,
        ':id' => $order_id,
    ]);
}

function sparrow_order_number_exists(string $order_number, ?int $exclude_order_id = null): bool
{
    $pdo = sparrow_db();

    if ($exclude_order_id) {
        $stmt = $pdo->prepare("
            SELECT id
            FROM orders
            WHERE order_number = :order_number
            AND COALESCE(trashed, 0) = 0
            AND id != :exclude_order_id
            LIMIT 1
        ");

        $stmt->execute([
            ':order_number' => $order_number,
            ':exclude_order_id' => $exclude_order_id,
        ]);
    } else {
        $stmt = $pdo->prepare("
            SELECT id
            FROM orders
            WHERE order_number = :order_number
            AND COALESCE(trashed, 0) = 0
            LIMIT 1
        ");

        $stmt->execute([
            ':order_number' => $order_number,
        ]);
    }

    return (bool) $stmt->fetch();
}

function sparrow_update_order_number(int $order_id, string $order_number): void
{
    $pdo = sparrow_db();

    $order_number = strtoupper(trim($order_number));

    if ($order_number === '') {
        throw new RuntimeException('Order number is required.');
    }

    if (!preg_match('/^SSC-\d+$/', $order_number)) {
        throw new RuntimeException('Order number must look like SSC-001.');
    }

    if (sparrow_order_number_exists($order_number, $order_id)) {
        throw new RuntimeException('That order number is already being used.');
    }

    $stmt = $pdo->prepare("
        UPDATE orders
        SET order_number = :order_number
        WHERE id = :id
        LIMIT 1
    ");

    $stmt->execute([
        ':id' => $order_id,
        ':order_number' => $order_number,
    ]);
}

function sparrow_update_order_customer(int $order_id, array $data): void
{
    $pdo = sparrow_db();

    $stmt = $pdo->prepare("
        UPDATE orders
        SET
            customer_name = :customer_name,
            customer_email = :customer_email,
            customer_phone = :customer_phone,
			order_date = :order_date,
            status = :status,
            notes = :notes
        WHERE id = :id
        LIMIT 1
    ");

    $stmt->execute([
        ':id' => $order_id,
        ':customer_name' => $data['customer_name'],
        ':customer_email' => $data['customer_email'] ?: null,
        ':customer_phone' => $data['customer_phone'] ?: null,
		':order_date' => $data['order_date'],
        ':status' => $data['status'],
        ':notes' => $data['notes'] ?: null,
    ]);
}

function sparrow_update_order_delivery(int $order_id, array $data): void
{
    $pdo = sparrow_db();

    $stmt = $pdo->prepare("
        UPDATE orders
        SET
            delivery_method = :delivery_method,
			order_shipping_amount = :order_shipping_amount,
            delivery_notes = :delivery_notes
        WHERE id = :id
        LIMIT 1
    ");

    $stmt->execute([
        ':id' => $order_id,
        ':delivery_method' => $data['delivery_method'] ?? 'Pickup',
		':order_shipping_amount' => $data['order_shipping_amount'] ?? 0,
        ':delivery_notes' => $data['delivery_notes'] ?: null,
    ]);
}

function sparrow_supplier_safe_cost($value): float
{
    $amount = round((float) ($value ?? 0), 2);

    return max(0, $amount);
}

function sparrow_update_order_internal_costs(int $order_id, array $data): void
{
    $pdo = sparrow_db();

    $stmt = $pdo->prepare("
        UPDATE orders
        SET
            supplier_item_cost = COALESCE(
                :supplier_item_cost,
                supplier_item_cost
            ),
            shipping_cost = :shipping_cost,
            discount_cost = :discount_cost,
            tax_cost = :tax_cost,
            other_cost = :other_cost,
            internal_cost_notes = :internal_cost_notes
        WHERE id = :id
        LIMIT 1
    ");

    $stmt->execute([
        ':supplier_item_cost' => array_key_exists(
            'supplier_item_cost',
            $data
        )
            ? sparrow_supplier_safe_cost(
                $data['supplier_item_cost']
            )
            : null,

        ':shipping_cost' => sparrow_supplier_safe_cost(
            $data['shipping_cost'] ?? 0
        ),

        ':discount_cost' => sparrow_supplier_safe_cost(
            $data['discount_cost'] ?? 0
        ),

        ':tax_cost' => sparrow_supplier_safe_cost(
            $data['tax_cost'] ?? 0
        ),

        ':other_cost' => sparrow_supplier_safe_cost(
            $data['other_cost'] ?? 0
        ),

        ':internal_cost_notes' => trim(
            (string) ($data['internal_cost_notes'] ?? '')
        ),

        ':id' => $order_id,
    ]);
}

function sparrow_update_order_admin_notes(int $order_id, string $admin_notes): void
{
    $pdo = sparrow_db();

    $stmt = $pdo->prepare("
        UPDATE orders
        SET admin_notes = :admin_notes
        WHERE id = :id
    ");

    $stmt->execute([
        ':admin_notes' => $admin_notes,
        ':id' => $order_id,
    ]);
}

function sparrow_update_order_approval(int $order_id, ?string $image_path, string $status = 'waiting', string $notes = ''): void
{
    $pdo = sparrow_db();

    $stmt = $pdo->prepare("
        UPDATE orders
        SET
            approval_image = :approval_image,
            approval_status = :approval_status,
            approval_notes = :approval_notes
        WHERE id = :id
    ");

    $stmt->execute([
        ':approval_image' => $image_path,
        ':approval_status' => $status,
        ':approval_notes' => $notes,
        ':id' => $order_id,
    ]);
}

function sparrow_get_order_reference_images(
    int $order_id
): array {
    $pdo = sparrow_db();

    $stmt = $pdo->prepare("
        SELECT
            id,
            order_id,
            product_type,
            item_number,
            image_position,
            original_name,
            image_path,
            created_at
        FROM order_reference_images
        WHERE order_id = :order_id
        ORDER BY
            CASE product_type
                WHEN 'Shirt' THEN 1
                WHEN 'Koozie' THEN 2
                ELSE 3
            END,
            item_number,
            FIELD(
                image_position,
                'front',
                'back',
                'chest',
                'sleeve',
                'other'
            ),
            id
    ");

    $stmt->execute([
        ':order_id' => $order_id,
    ]);

    return $stmt->fetchAll(PDO::FETCH_ASSOC) ?: [];
}

function sparrow_save_order_reference_image(
    int $order_id,
    string $product_type,
    int $item_number,
    string $image_position,
    ?string $original_name,
    string $image_path
): void {
    $allowed_positions = [
        'Shirt' => [
            'front',
            'back',
            'chest',
            'sleeve',
            'other',
        ],
        'Koozie' => [
            'front',
            'back',
        ],
    ];

    $product_type = trim($product_type);
    $image_position = strtolower(trim($image_position));
    $image_path = trim($image_path);
    $item_number = max(1, $item_number);

    if (!isset($allowed_positions[$product_type])) {
        throw new InvalidArgumentException(
            'Invalid reference image product type.'
        );
    }

    if (
        !in_array(
            $image_position,
            $allowed_positions[$product_type],
            true
        )
    ) {
        throw new InvalidArgumentException(
            'Invalid reference image position.'
        );
    }

    if ($image_path === '') {
        throw new InvalidArgumentException(
            'Reference image path cannot be empty.'
        );
    }

    $original_name = trim((string) $original_name);

    $pdo = sparrow_db();

    $stmt = $pdo->prepare("
        INSERT INTO order_reference_images (
            order_id,
            product_type,
            item_number,
            image_position,
            original_name,
            image_path
        )
        VALUES (
            :order_id,
            :product_type,
            :item_number,
            :image_position,
            :original_name,
            :image_path
        )
    ");

    $stmt->execute([
        ':order_id' => $order_id,
        ':product_type' => $product_type,
        ':item_number' => $item_number,
        ':image_position' => $image_position,
        ':original_name' => (
            $original_name !== ''
                ? basename($original_name)
                : null
        ),
        ':image_path' => $image_path,
    ]);
}

function sparrow_get_approval_image_positions(): array
{
    return [
        'front' => 'Front',
        'back' => 'Back',
        'chest' => 'Chest',
        'sleeve' => 'Sleeve',
        'other' => 'Other',
    ];
}

function sparrow_get_order_approval_images(
    int $order_id
): array {
    $pdo = sparrow_db();

    $stmt = $pdo->prepare("
        SELECT
            id,
            order_id,
            image_position,
            image_path,
            created_at,
            updated_at
        FROM order_approval_images
        WHERE order_id = :order_id
        ORDER BY FIELD(
    image_position,
    'front',
    'back',
    'chest',
    'sleeve',
    'other'
)
    ");

    $stmt->execute([
        ':order_id' => $order_id,
    ]);

    $images = [];

    foreach ($stmt->fetchAll(PDO::FETCH_ASSOC) as $image) {
        $position = (string) $image['image_position'];
        $images[$position] = $image;
    }

    return $images;
}

function sparrow_get_order_item_approval_images(
    int $order_item_id
): array {
    $pdo = sparrow_db();

    $stmt = $pdo->prepare("
        SELECT
            id,
            order_item_id,
            image_position,
            image_path,
            created_at,
            updated_at
        FROM order_item_approval_images
        WHERE order_item_id = :order_item_id
        ORDER BY FIELD(
            image_position,
            'front',
            'back',
            'chest',
            'sleeve',
            'other'
        )
    ");

    $stmt->execute([
        ':order_item_id' => $order_item_id,
    ]);

    $images = [];

    foreach ($stmt->fetchAll(PDO::FETCH_ASSOC) as $image) {
        $position = (string) $image['image_position'];
        $images[$position] = $image;
    }

    return $images;
}

function sparrow_save_order_item_approval_image(
    int $order_item_id,
    string $image_position,
    string $image_path
): void {
    $positions = sparrow_get_approval_image_positions();

    if (!isset($positions[$image_position])) {
        throw new InvalidArgumentException(
            'Invalid approval image position.'
        );
    }

    $image_path = trim($image_path);

    if ($image_path === '') {
        throw new InvalidArgumentException(
            'Approval image path cannot be empty.'
        );
    }

    $pdo = sparrow_db();
    $pdo->beginTransaction();

    try {
        $item_stmt = $pdo->prepare("
            SELECT id
            FROM order_items
            WHERE id = :id
            LIMIT 1
        ");

        $item_stmt->execute([
            ':id' => $order_item_id,
        ]);

        if (!$item_stmt->fetchColumn()) {
            throw new RuntimeException(
                'The selected order item could not be found.'
            );
        }

        $image_stmt = $pdo->prepare("
            INSERT INTO order_item_approval_images (
                order_item_id,
                image_position,
                image_path
            )
            VALUES (
                :order_item_id,
                :image_position,
                :image_path
            )
            ON DUPLICATE KEY UPDATE
                image_path = VALUES(image_path),
                updated_at = CURRENT_TIMESTAMP
        ");

        $image_stmt->execute([
            ':order_item_id' => $order_item_id,
            ':image_position' => $image_position,
            ':image_path' => $image_path,
        ]);

        $status_stmt = $pdo->prepare("
            UPDATE order_items
            SET
                approval_status = 'waiting',
                customer_approval_notes = NULL,
                approval_responded_at = NULL
            WHERE id = :id
            LIMIT 1
        ");

        $status_stmt->execute([
            ':id' => $order_item_id,
        ]);

        $pdo->commit();
    } catch (Throwable $exception) {
        if ($pdo->inTransaction()) {
            $pdo->rollBack();
        }

        throw $exception;
    }
}

function sparrow_delete_order_item_approval_image(
    int $order_item_id,
    string $image_position
): ?string {
    $positions = sparrow_get_approval_image_positions();

    if (!isset($positions[$image_position])) {
        throw new InvalidArgumentException(
            'Invalid approval image position.'
        );
    }

    $pdo = sparrow_db();
    $pdo->beginTransaction();

    try {
    $item_stmt = $pdo->prepare("
        SELECT id
        FROM order_items
        WHERE id = :order_item_id
        LIMIT 1
    ");

    $item_stmt->execute([
        ':order_item_id' => $order_item_id,
    ]);

    if (!$item_stmt->fetchColumn()) {
        throw new RuntimeException(
            'The selected order item could not be found.'
        );
    }

    $image_stmt = $pdo->prepare("
            SELECT image_path
            FROM order_item_approval_images
            WHERE order_item_id = :order_item_id
              AND image_position = :image_position
            LIMIT 1
        ");

        $image_stmt->execute([
            ':order_item_id' => $order_item_id,
            ':image_position' => $image_position,
        ]);

        $image_path = $image_stmt->fetchColumn();

        $delete_stmt = $pdo->prepare("
            DELETE FROM order_item_approval_images
            WHERE order_item_id = :order_item_id
              AND image_position = :image_position
            LIMIT 1
        ");

        $delete_stmt->execute([
            ':order_item_id' => $order_item_id,
            ':image_position' => $image_position,
        ]);

        $status_stmt = $pdo->prepare("
            UPDATE order_items
            SET
                approval_status = 'waiting',
                customer_approval_notes = NULL,
                approval_responded_at = NULL
            WHERE id = :order_item_id
            LIMIT 1
        ");

        $status_stmt->execute([
            ':order_item_id' => $order_item_id,
        ]);

        $pdo->commit();

        return is_string($image_path)
            ? $image_path
            : null;
    } catch (Throwable $exception) {
        if ($pdo->inTransaction()) {
            $pdo->rollBack();
        }

        throw $exception;
    }
}

function sparrow_save_order_approval_image(
    int $order_id,
    string $image_position,
    string $image_path
): void {
    $positions = sparrow_get_approval_image_positions();

    if (!isset($positions[$image_position])) {
        throw new InvalidArgumentException(
            'Invalid approval image position.'
        );
    }

    $image_path = trim($image_path);

    if ($image_path === '') {
        throw new InvalidArgumentException(
            'Approval image path cannot be empty.'
        );
    }

    $pdo = sparrow_db();

    $pdo->beginTransaction();

    try {
        $stmt = $pdo->prepare("
            INSERT INTO order_approval_images (
                order_id,
                image_position,
                image_path
            )
            VALUES (
                :order_id,
                :image_position,
                :image_path
            )
            ON DUPLICATE KEY UPDATE
                image_path = VALUES(image_path),
                updated_at = CURRENT_TIMESTAMP
        ");

        $stmt->execute([
            ':order_id' => $order_id,
            ':image_position' => $image_position,
            ':image_path' => $image_path,
        ]);

        $status_stmt = $pdo->prepare("
            UPDATE orders
            SET approval_status = 'waiting'
            WHERE id = :order_id
        ");

        $status_stmt->execute([
            ':order_id' => $order_id,
        ]);

        $pdo->commit();
    } catch (Throwable $exception) {
        if ($pdo->inTransaction()) {
            $pdo->rollBack();
        }

        throw $exception;
    }
}

function sparrow_get_trashed_orders(): array
{
    $pdo = sparrow_db();

    $stmt = $pdo->query("
        SELECT
            o.*,
            COALESCE(SUM(oi.quantity), o.quantity) AS total_quantity,
            COUNT(oi.id) AS item_count,
            GROUP_CONCAT(
                CONCAT(
                    oi.quantity,
                    ' ',
                    COALESCE(oi.shirt_color, ''),
                    ' ',
                    COALESCE(oi.shirt_size, ''),
                    ' ',
                    COALESCE(oi.print_location, '')
                )
                SEPARATOR ' | '
            ) AS item_summary
        FROM orders o
        LEFT JOIN order_items oi
            ON oi.order_id = o.id
        WHERE COALESCE(o.trashed, 0) = 1
        GROUP BY o.id
        ORDER BY o.order_date DESC, o.id DESC
    ");

    return $stmt->fetchAll();
}

function sparrow_trash_order(int $order_id): void
{
    $pdo = sparrow_db();

    $pdo->beginTransaction();

    try {
        $stmt = $pdo->prepare("
            UPDATE orders
            SET trashed = 1
            WHERE id = :id
            LIMIT 1
        ");

        $stmt->execute([
            ':id' => $order_id,
        ]);

        $stmt = $pdo->prepare("
            UPDATE order_items
            SET trashed = 1
            WHERE order_id = :order_id
        ");

        $stmt->execute([
            ':order_id' => $order_id,
        ]);

        $pdo->commit();
    } catch (Throwable $e) {
        $pdo->rollBack();
        throw $e;
    }
}

function sparrow_restore_order(int $order_id): void
{
    $pdo = sparrow_db();

    $pdo->beginTransaction();

    try {
        $stmt = $pdo->prepare("
            UPDATE orders
            SET trashed = 0
            WHERE id = :id
            LIMIT 1
        ");

        $stmt->execute([
            ':id' => $order_id,
        ]);

        $stmt = $pdo->prepare("
            UPDATE order_items
            SET trashed = 0
            WHERE order_id = :order_id
        ");

        $stmt->execute([
            ':order_id' => $order_id,
        ]);

        $pdo->commit();
    } catch (Throwable $e) {
        $pdo->rollBack();
        throw $e;
    }
}

function sparrow_delete_order_permanent(int $order_id): void
{
    $pdo = sparrow_db();

    $pdo->beginTransaction();

    try {
        $stmt = $pdo->prepare("
            DELETE FROM order_items
            WHERE order_id = :order_id
        ");

        $stmt->execute([
            ':order_id' => $order_id,
        ]);

        $stmt = $pdo->prepare("
            DELETE FROM orders
            WHERE id = :id
            AND COALESCE(trashed, 0) = 1
            LIMIT 1
        ");

        $stmt->execute([
            ':id' => $order_id,
        ]);

        $pdo->commit();
    } catch (Throwable $e) {
        $pdo->rollBack();
        throw $e;
    }
}

function sparrow_normalize_import_value($value): string
{
    $value = strtolower(trim((string) $value));
    $value = preg_replace('/\s+/', ' ', $value);

    return $value;
}

function sparrow_make_order_import_key(array $order, string $order_date): string
{
    $item_parts = [];

    foreach (($order['order_items'] ?? []) as $item) {
        $item_parts[] = implode('|', [
            sparrow_normalize_import_value($item['item_type'] ?? ''),
            (int) ($item['quantity'] ?? 0),
            sparrow_normalize_import_value($item['shirt_color'] ?? ''),
            sparrow_normalize_import_value($item['shirt_size'] ?? ''),
            sparrow_normalize_import_value($item['specialty'] ?? ''),
            sparrow_normalize_import_value($item['print_location'] ?? ''),
            sparrow_normalize_import_value($item['item_notes'] ?? ''),
        ]);
    }

    sort($item_parts);

    $key_source = implode('||', [
        sparrow_normalize_import_value($order['customer_name'] ?? ''),
        sparrow_normalize_import_value($order['customer_email'] ?? ''),
        sparrow_normalize_import_value($order['customer_phone'] ?? ''),
        sparrow_normalize_import_value($order_date),
        sparrow_normalize_import_value($order['status'] ?? ''),
        sparrow_normalize_import_value($order['order_notes'] ?? ''),
        implode('~~', $item_parts),
    ]);

    return hash('sha256', $key_source);
}

function sparrow_import_key_exists(string $import_key): bool
{
    $pdo = sparrow_db();

    $stmt = $pdo->prepare("
        SELECT id
        FROM orders
        WHERE import_key = :import_key
        LIMIT 1
    ");

    $stmt->execute([
        ':import_key' => $import_key,
    ]);

    return (bool) $stmt->fetch();
}

function sparrow_set_order_import_key(int $order_id, string $import_key): void
{
    $pdo = sparrow_db();

    $stmt = $pdo->prepare("
        UPDATE orders
        SET import_key = :import_key
        WHERE id = :id
        LIMIT 1
    ");

    $stmt->execute([
        ':id' => $order_id,
        ':import_key' => $import_key,
    ]);
}

function sparrow_format_dashboard_size_label(string $size): string
{
    $size = strtoupper(trim($size));

    $map = [
        'XS' => 'X-Small',
        'X-SMALL' => 'X-Small',
        'EXTRA SMALL' => 'X-Small',
        'EXTRA-SMALL' => 'X-Small',

        'S' => 'Small',
        'SMALL' => 'Small',

        'M' => 'Medium',
        'MEDIUM' => 'Medium',

        'L' => 'Large',
        'LARGE' => 'Large',

        'XL' => 'X-Large',
        'X-LARGE' => 'X-Large',
        'X LARGE' => 'X-Large',
        'EXTRA LARGE' => 'X-Large',
        'EXTRA-LARGE' => 'X-Large',

        '2XL' => '2X-Large',
        '2X' => '2X-Large',
        'XXL' => '2X-Large',
        '2X-LARGE' => '2X-Large',
        '2X LARGE' => '2X-Large',

        '3XL' => '3X-Large',
        '3X' => '3X-Large',
        '3X-LARGE' => '3X-Large',
        '3X LARGE' => '3X-Large',

        '4XL' => '4X-Large',
        '4X' => '4X-Large',
        '4X-LARGE' => '4X-Large',
        '4X LARGE' => '4X-Large',

        '5XL' => '5X-Large',
        '5X' => '5X-Large',
        '5X-LARGE' => '5X-Large',
        '5X LARGE' => '5X-Large',
    ];

    return $map[$size] ?? trim($size);
}

function sparrow_get_backyard_item_summary_label(int $order_id): string
{
    $items = sparrow_get_order_items($order_id);

    if (!$items) {
        return '0 Items';
    }

    $shirt_lines = [];
    $shirt_summary = [];
    $shirt_total = 0;

    $counts = [
        'koozies' => 0,
        'tumblers' => 0,
        'pens' => 0,
        'other' => 0,
    ];

    foreach ($items as $item) {
        $qty = (int) ($item['quantity'] ?? 0);

        if ($qty <= 0) {
            continue;
        }

        $item_type = strtolower(trim((string) ($item['item_type'] ?? '')));
        $color = trim((string) ($item['shirt_color'] ?? ''));
$size = trim((string) ($item['shirt_size'] ?? ''));
$notes = strtolower(trim((string) ($item['item_notes'] ?? '')));

$customer_supplied = !empty(
    $item['customer_supplied']
);

if (strtolower($color) === 'shirt') {
    $color = '';
}

$size = sparrow_format_dashboard_size_label($size);

        $is_koozie = (
            strpos($item_type, 'koozie') !== false ||
            strpos($notes, 'koozie') !== false ||
            strpos($notes, 'coozie') !== false ||
            strpos($notes, 'can cooler') !== false ||
            strpos($notes, 'can holder') !== false
        );

        $is_tumbler = (
            strpos($item_type, 'tumbler') !== false ||
            strpos($notes, 'tumbler') !== false ||
            strpos($notes, 'cup') !== false
        );

        $is_pen = (
            strpos($item_type, 'pen') !== false ||
            strpos($notes, 'pen') !== false
        );

        $is_shirt = (
            strpos($item_type, 'shirt') !== false ||
            strpos($item_type, 'tee') !== false ||
            strpos($item_type, 't-shirt') !== false ||
            strpos($item_type, 'tshirt') !== false
        );

        if ($is_koozie) {
            $counts['koozies'] += $qty;
            continue;
        }

        if ($is_tumbler) {
            $counts['tumblers'] += $qty;
            continue;
        }

        if ($is_pen) {
            $counts['pens'] += $qty;
            continue;
        }

        if ($is_shirt) {
            $shirt_total += $qty;

            if ($color === '') {
    $color = $customer_supplied
        ? 'Customer Supplied'
        : 'Unknown Color';
}

            if ($size === '') {
                $size = 'Size not set';
            }

            $shirt_lines[] = [
                'qty' => $qty,
                'color' => $color,
                'size' => $size,
            ];

            if (!isset($shirt_summary[$color])) {
                $shirt_summary[$color] = [];
            }

            if (!isset($shirt_summary[$color][$size])) {
                $shirt_summary[$color][$size] = 0;
            }

            $shirt_summary[$color][$size] += $qty;

            continue;
        }

        $counts['other'] += $qty;
    }

    $parts = [];

    if ($shirt_total > 0 && count($shirt_lines) <= 4 && $shirt_total <= 6) {
        foreach ($shirt_lines as $line) {
            $parts[] =
                (int) $line['qty'] . ' ' .
                $line['color'] . ' Shirt, ' .
                $line['size'];
        }
    }

    if ($shirt_total > 0 && (count($shirt_lines) > 4 || $shirt_total > 6)) {
        $parts[] = 'Shirts: ' . $shirt_total . ' total';

        foreach ($shirt_summary as $color => $sizes) {
            $size_parts = [];

            foreach ($sizes as $size => $qty) {
                $size_parts[] = (int) $qty . ' ' . $size;
            }

            $parts[] = $color . ': ' . implode(', ', $size_parts);
        }
    }

    if ($counts['koozies'] > 0) {
        $parts[] = $counts['koozies'] . ' ' . ($counts['koozies'] === 1 ? 'Koozie' : 'Koozies');
    }

    if ($counts['tumblers'] > 0) {
        $parts[] = $counts['tumblers'] . ' ' . ($counts['tumblers'] === 1 ? 'Tumbler' : 'Tumblers');
    }

    if ($counts['pens'] > 0) {
        $parts[] = $counts['pens'] . ' ' . ($counts['pens'] === 1 ? 'Pen' : 'Pens');
    }

    if ($counts['other'] > 0) {
        $parts[] = $counts['other'] . ' Other';
    }

    if (!$parts) {
        return '0 Items';
    }

    if ($shirt_total > 6 || count($shirt_lines) > 4) {
        return implode('<br>', array_map('sparrow_escape', $parts));
    }

    return sparrow_escape(implode(' • ', $parts));
}