<?php

require_once __DIR__ . '/db.php';

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

    $stmt = $pdo->query("
        SELECT *
        FROM pricing_items
        ORDER BY item_type ASC, color_style ASC, id ASC
    ");

    return $stmt->fetchAll();
}

function sparrow_add_pricing_row(array $row): void
{
    $pdo = sparrow_db();

    $stmt = $pdo->prepare("
        INSERT INTO pricing_items
        (
            item_type,
            size_range,
            color_style,
            unit_cost,
            retail_price,
            notes
        )
        VALUES
        (
            :item_type,
            :size_range,
            :color_style,
            :unit_cost,
            :retail_price,
            :notes
        )
    ");

    $stmt->execute([
        ':item_type' => trim((string) ($row['item_type'] ?? '')),
        ':size_range' => trim((string) ($row['size_range'] ?? '')),
        ':color_style' => trim((string) ($row['color_style'] ?? '')),
        ':unit_cost' => (float) ($row['unit_cost'] ?? 0),
        ':retail_price' => ($row['retail_price'] ?? '') === '' ? null : (float) $row['retail_price'],
        ':notes' => trim((string) ($row['notes'] ?? '')),
    ]);
}

function sparrow_delete_pricing_row(int $id): void
{
    $pdo = sparrow_db();

    $stmt = $pdo->prepare("
        DELETE FROM pricing_items
        WHERE id = :id
        LIMIT 1
    ");

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

function sparrow_save_pricing_rows_from_post(array $post): void
{
    $pdo = sparrow_db();

    $ids = $post['pricing_id'] ?? [];
    $item_types = $post['pricing_item_type'] ?? [];
    $size_ranges = $post['pricing_size_range'] ?? [];
    $color_styles = $post['pricing_color_style'] ?? [];
    $unit_costs = $post['pricing_unit_cost'] ?? [];
    $retail_prices = $post['pricing_retail_price'] ?? [];
    $notes = $post['pricing_notes'] ?? [];

    $pdo->beginTransaction();

    try {
        $update_stmt = $pdo->prepare("
            UPDATE pricing_items
            SET
                item_type = :item_type,
                size_range = :size_range,
                color_style = :color_style,
                unit_cost = :unit_cost,
                retail_price = :retail_price,
                notes = :notes
            WHERE id = :id
            LIMIT 1
        ");

        $insert_stmt = $pdo->prepare("
            INSERT INTO pricing_items
            (
                item_type,
                size_range,
                color_style,
                unit_cost,
                retail_price,
                notes
            )
            VALUES
            (
                :item_type,
                :size_range,
                :color_style,
                :unit_cost,
                :retail_price,
                :notes
            )
        ");

        foreach ($item_types as $i => $item_type) {
            $id = (int) ($ids[$i] ?? 0);

            $item_type = trim((string) $item_type);
            $size_range = trim((string) ($size_ranges[$i] ?? ''));
            $color_style = trim((string) ($color_styles[$i] ?? ''));
            $unit_cost = (float) ($unit_costs[$i] ?? 0);
            $retail_price_raw = trim((string) ($retail_prices[$i] ?? ''));
            $note = trim((string) ($notes[$i] ?? ''));

            if (
                $item_type === '' &&
                $size_range === '' &&
                $color_style === '' &&
                $unit_cost <= 0 &&
                $retail_price_raw === '' &&
                $note === ''
            ) {
                continue;
            }

            $data = [
                ':item_type' => $item_type,
                ':size_range' => $size_range,
                ':color_style' => $color_style,
                ':unit_cost' => $unit_cost,
                ':retail_price' => $retail_price_raw === '' ? null : (float) $retail_price_raw,
                ':notes' => $note,
            ];

            if ($id > 0) {
                $data[':id'] = $id;
                $update_stmt->execute($data);
            } else {
                $insert_stmt->execute($data);
            }
        }

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

function sparrow_get_retail_price_for_size_label(string $size, float $standard_retail = 15.00, float $plus_retail = 18.00): float
{
    $size_text = strtolower(trim($size));

    if (preg_match('/\b2xl\b|\b2x\b|\bxxl\b|\b3xl\b|\b3x\b|\b4xl\b|\b4x\b|\b5xl\b|\b5x\b/', $size_text)) {
        return $plus_retail;
    }

    return $standard_retail;
}

function sparrow_parse_pricing_csv_line(string $line): array
{
    if (strpos($line, "\t") !== false) {
        return str_getcsv($line, "\t");
    }

    return str_getcsv($line);
}

function sparrow_import_pricing_matrix_from_text(
    string $matrix_text,
    string $item_type = 'Gildan Light Cotton Shirt',
    float $standard_retail = 15.00,
    float $plus_retail = 18.00,
    string $notes = 'Imported from pricing spreadsheet',
    bool $replace_existing = false
): int {
    $pdo = sparrow_db();

    $lines = preg_split('/\r\n|\r|\n/', trim($matrix_text));
    $lines = array_values(array_filter($lines, fn($line) => trim($line) !== ''));

    if (count($lines) < 2) {
        throw new RuntimeException('The import needs a header row and at least one color row.');
    }

    $header = sparrow_parse_pricing_csv_line(array_shift($lines));

    if (count($header) < 2) {
        throw new RuntimeException('The header row needs Color in column A and sizes across the next columns.');
    }

    $sizes = [];

    foreach ($header as $index => $heading) {
        if ($index === 0) {
            continue;
        }

        $heading = trim((string) $heading);

        if ($heading !== '') {
            $sizes[$index] = $heading;
        }
    }

    if (!$sizes) {
        throw new RuntimeException('No size columns were found in the header row.');
    }

    $rows = [];

    foreach ($lines as $line) {
        $columns = sparrow_parse_pricing_csv_line($line);

        if (!$columns) {
            continue;
        }

        $color_style = trim((string) ($columns[0] ?? ''));

        if ($color_style === '') {
            continue;
        }

        foreach ($sizes as $index => $size) {
            $unit_cost_raw = trim((string) ($columns[$index] ?? ''));

            if ($unit_cost_raw === '') {
                continue;
            }

            $unit_cost_clean = preg_replace('/[^0-9.\-]/', '', $unit_cost_raw);

            if ($unit_cost_clean === '' || !is_numeric($unit_cost_clean)) {
                continue;
            }

            $rows[] = [
                'item_type' => $item_type,
                'size_range' => $size,
                'color_style' => $color_style,
                'unit_cost' => (float) $unit_cost_clean,
                'retail_price' => sparrow_get_retail_price_for_size_label($size, $standard_retail, $plus_retail),
                'notes' => $notes,
            ];
        }
    }

    if (!$rows) {
        throw new RuntimeException('No pricing rows were imported. Check that your cost cells contain numbers.');
    }

    $pdo->beginTransaction();

    try {
        if ($replace_existing) {
            $pdo->exec("DELETE FROM pricing_items");
        }

        $stmt = $pdo->prepare("
            INSERT INTO pricing_items
            (
                item_type,
                size_range,
                color_style,
                unit_cost,
                retail_price,
                notes
            )
            VALUES
            (
                :item_type,
                :size_range,
                :color_style,
                :unit_cost,
                :retail_price,
                :notes
            )
        ");

        foreach ($rows as $row) {
            $stmt->execute([
                ':item_type' => $row['item_type'],
                ':size_range' => $row['size_range'],
                ':color_style' => $row['color_style'],
                ':unit_cost' => $row['unit_cost'],
                ':retail_price' => $row['retail_price'],
                ':notes' => $row['notes'],
            ]);
        }

        $pdo->commit();

        return count($rows);
    } catch (Throwable $e) {
        $pdo->rollBack();
        throw $e;
    }
}

function sparrow_pricing_normalize_size_label(string $size): string
{
    $size = strtoupper(trim($size));
    $size = str_replace(['(', ')'], '', $size);
    $size = preg_replace('/\s+/', ' ', $size);

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

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

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

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

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

        '2X' => '2XL',
        '2XL' => '2XL',
        'XXL' => '2XL',
        '2X LARGE' => '2XL',
        '2X-LARGE' => '2XL',
        'XX-LARGE' => '2XL',
        '2XLARGE' => '2XL',

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

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

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

    return $map[$size] ?? $size;
}

function sparrow_pricing_size_order_value(string $size): int
{
    $size = sparrow_pricing_normalize_size_label($size);

    $order = [
        'XS' => 1,
        'S' => 2,
        'M' => 3,
        'L' => 4,
        'XL' => 5,
        '2XL' => 6,
        '3XL' => 7,
        '4XL' => 8,
        '5XL' => 9,
    ];

    return $order[$size] ?? 999;
}

function sparrow_pricing_format_size_range(array $sizes): string
{
    $sizes = array_values(array_unique(array_filter(array_map(function ($size) {
        return sparrow_pricing_normalize_size_label((string) $size);
    }, $sizes))));

    usort($sizes, function ($a, $b) {
        return sparrow_pricing_size_order_value($a) <=> sparrow_pricing_size_order_value($b);
    });

    $standard_sizes = ['XS', 'S', 'M', 'L', 'XL'];

    if ($sizes === $standard_sizes) {
        return 'XS-XL';
    }

    if (count($sizes) === 1) {
        return $sizes[0];
    }

    return reset($sizes) . '-' . end($sizes);
}

function sparrow_bundle_pricing_rows_in_database(): int
{
    $pdo = sparrow_db();

    $rows = sparrow_get_pricing_rows();

    if (!$rows) {
        return 0;
    }

    $groups = [];

    foreach ($rows as $row) {
        $item_type = trim((string) ($row['item_type'] ?? ''));
        $size_range = trim((string) ($row['size_range'] ?? ''));
        $color_style = trim((string) ($row['color_style'] ?? ''));
        $unit_cost = number_format((float) ($row['unit_cost'] ?? 0), 2, '.', '');

        $retail_raw = $row['retail_price'] ?? null;
        $retail_price = ($retail_raw === null || $retail_raw === '')
            ? ''
            : number_format((float) $retail_raw, 2, '.', '');

        $notes = trim((string) ($row['notes'] ?? ''));

        if ($item_type === '' && $size_range === '' && $color_style === '') {
            continue;
        }

        $group_key = md5($item_type . '|' . $color_style . '|' . $unit_cost . '|' . $retail_price . '|' . $notes);

        if (!isset($groups[$group_key])) {
            $groups[$group_key] = [
                'item_type' => $item_type,
                'sizes' => [],
                'color_style' => $color_style,
                'unit_cost' => $unit_cost,
                'retail_price' => $retail_price,
                'notes' => $notes,
            ];
        }

        $groups[$group_key]['sizes'][] = $size_range;
    }

    $pdo->beginTransaction();

    try {
        $pdo->exec("DELETE FROM pricing_items");

        $stmt = $pdo->prepare("
            INSERT INTO pricing_items
            (
                item_type,
                size_range,
                color_style,
                unit_cost,
                retail_price,
                notes
            )
            VALUES
            (
                :item_type,
                :size_range,
                :color_style,
                :unit_cost,
                :retail_price,
                :notes
            )
        ");

        foreach ($groups as $group) {
            $stmt->execute([
                ':item_type' => $group['item_type'],
                ':size_range' => sparrow_pricing_format_size_range($group['sizes']),
                ':color_style' => $group['color_style'],
                ':unit_cost' => $group['unit_cost'],
                ':retail_price' => $group['retail_price'] === '' ? null : $group['retail_price'],
                ':notes' => $group['notes'],
            ]);
        }

        $pdo->commit();

        return count($groups);
    } catch (Throwable $e) {
        $pdo->rollBack();
        throw $e;
    }
}

function sparrow_pricing_size_matches_range(string $size, string $range): bool
{
    $size = sparrow_pricing_normalize_size_label($size);
    $range = strtoupper(trim($range));

    if ($size === $range) {
        return true;
    }

    if (strpos($range, '-') !== false) {
        $parts = explode('-', $range);

        $start = sparrow_pricing_normalize_size_label($parts[0] ?? '');
        $end = sparrow_pricing_normalize_size_label($parts[1] ?? '');

        $size_value = sparrow_pricing_size_order_value($size);
        $start_value = sparrow_pricing_size_order_value($start);
        $end_value = sparrow_pricing_size_order_value($end);

        return $size_value >= $start_value && $size_value <= $end_value;
    }

    if ($range === '2XL+' || $range === '2X+') {
        return sparrow_pricing_size_order_value($size) >= sparrow_pricing_size_order_value('2XL');
    }

    if ($range === 'ANY' || $range === 'ONE SIZE' || $range === 'ONESIZE') {
        return true;
    }

    return false;
}

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

    return $value;
}

function sparrow_find_pricing_row_for_order_item(array $item): ?array
{
    $pricing_rows = sparrow_get_pricing_rows();

$item_type = sparrow_pricing_normalize_text(
    (string) ($item['item_type'] ?? '')
);

$item_size = (string) ($item['shirt_size'] ?? '');

$item_color = sparrow_pricing_normalize_text(
    (string) ($item['shirt_color'] ?? '')
);

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

    $item_size = (string) ($item['shirt_size'] ?? '');
    $item_color = sparrow_pricing_normalize_text((string) ($item['shirt_color'] ?? ''));

    foreach ($pricing_rows as $row) {
        $row_item_type = sparrow_pricing_normalize_text((string) ($row['item_type'] ?? ''));
        $row_size_range = (string) ($row['size_range'] ?? '');
        $row_color = sparrow_pricing_normalize_text((string) ($row['color_style'] ?? ''));

        if ($row_item_type === '') {
            continue;
        }

        /*
         * For now, this lookup is for shirt blanks.
         * It will match Gildan Light Cotton Shirt rows to order items marked Shirt.
         */
        if ($item_type === 'shirt') {
            if (
                strpos($row_item_type, 'shirt') === false &&
                strpos($row_item_type, 'gildan') === false &&
                strpos($row_item_type, 'cotton') === false
            ) {
                continue;
            }
        }

        if ($item_size !== '' && !sparrow_pricing_size_matches_range($item_size, $row_size_range)) {
            continue;
        }

        /*
 * Customer-supplied garments do not need a color match.
 * We still use the size to locate a normal retail price.
 */
$color_matches = $customer_supplied;

if (!$color_matches) {
    if ($row_color === '' || $row_color === 'any') {
        $color_matches = true;
    }

    if (
        !$color_matches &&
        $item_color !== '' &&
        $item_color === $row_color
    ) {
        $color_matches = true;
    }

    if (
        !$color_matches &&
        $item_color !== '' &&
        $row_color !== '' &&
        strpos($item_color, $row_color) !== false
    ) {
        $color_matches = true;
    }

    if (
        !$color_matches &&
        $item_color !== '' &&
        $row_color !== '' &&
        strpos($row_color, $item_color) !== false
    ) {
        $color_matches = true;
    }
}

if (!$color_matches) {
    continue;
}

        return $row;
    }

    return null;
}

function sparrow_calculate_order_pricing_from_lookup(int $order_id): array
{
    require_once __DIR__ . '/orders.php';

    $items = sparrow_get_order_items($order_id);

    $summary = [
        'retail_total' => 0.00,
        'blank_cost_total' => 0.00,
        'estimated_profit' => 0.00,
        'unknown_count' => 0,
        'lines' => [],
    ];

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

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

$pricing_row =
    sparrow_find_pricing_row_for_order_item($item);

$unit_cost = null;
$retail_price = null;

        if ($pricing_row) {
    $unit_cost = $customer_supplied
        ? 0.00
        : (float) ($pricing_row['unit_cost'] ?? 0);

    $retail_price =
        $pricing_row['retail_price'] === null ||
        $pricing_row['retail_price'] === ''
            ? null
            : (float) $pricing_row['retail_price'];
}

        if ($retail_price === null) {
            $summary['unknown_count']++;
            $retail_price = 0.00;
        }

        if ($unit_cost === null) {
            $unit_cost = 0.00;
        }

        $addons = sparrow_calculate_order_item_addons($item);

$retail_price_with_addons = $retail_price + (float) $addons['total'];

$line_retail_total = $quantity * $retail_price_with_addons;
$line_blank_cost_total = $quantity * $unit_cost;
$line_profit = $line_retail_total - $line_blank_cost_total;

        $summary['retail_total'] += $line_retail_total;
        $summary['blank_cost_total'] += $line_blank_cost_total;
        $summary['estimated_profit'] += $line_profit;

        $summary['lines'][] = [
            'item' => $item,
            'pricing_row' => $pricing_row,
            'quantity' => $quantity,
            'unit_cost' => $unit_cost,
            'retail_price' => $retail_price,
			'addons' => $addons,
			'retail_price_with_addons' => $retail_price_with_addons,
            'line_retail_total' => $line_retail_total,
            'line_blank_cost_total' => $line_blank_cost_total,
            'line_profit' => $line_profit,
        ];
    }

    return $summary;
}

function sparrow_calculate_order_item_addons(array $item): array
{
    $placement = strtolower(trim((string) ($item['print_location'] ?? '')));
    $specialty = strtolower(trim((string) ($item['specialty'] ?? '')));

    $addons = [
        'front_back' => 0.00,
        'sleeve' => 0.00,
        'specialty' => 0.00,
        'total' => 0.00,
        'labels' => [],
    ];

    if (
        strpos($placement, 'front & back') !== false ||
        strpos($placement, 'front and back') !== false ||
        strpos($placement, 'double sided') !== false ||
        (strpos($placement, 'front') !== false && strpos($placement, 'back') !== false)
    ) {
        $addons['front_back'] = 5.00;
        $addons['labels'][] = 'Front & Back +$5';
    }

    if (strpos($placement, 'sleeve') !== false) {
        $addons['sleeve'] = 3.00;
        $addons['labels'][] = 'Sleeve +$3';
    }

    if ($specialty !== '' && $specialty !== 'none') {
        $addons['specialty'] = 3.00;
        $addons['labels'][] = 'Specialty +$3';
    }

    $addons['total'] = $addons['front_back'] + $addons['sleeve'] + $addons['specialty'];

    return $addons;
}