<?php

declare(strict_types=1);

require_once __DIR__ . '/db.php';

function sparrow_get_supplies(
    bool $include_archived = false
): array {
    $pdo = sparrow_db();

    $where = $include_archived
        ? ''
        : 'WHERE is_active = 1';

    $stmt = $pdo->query("
        SELECT *
        FROM supplies
        {$where}
        ORDER BY category ASC, name ASC, id ASC
    ");

    return $stmt->fetchAll();
}

function sparrow_get_supply(int $supply_id): ?array
{
    $stmt = sparrow_db()->prepare("
        SELECT *
        FROM supplies
        WHERE id = :id
        LIMIT 1
    ");

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

    $supply = $stmt->fetch();

    return $supply ?: null;
}

function sparrow_get_low_supplies(): array
{
    $stmt = sparrow_db()->query("
        SELECT *
        FROM supplies
        WHERE is_active = 1
          AND reorder_level > 0
          AND quantity <= reorder_level
        ORDER BY quantity ASC, name ASC
    ");

    return $stmt->fetchAll();
}

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

    $supply_id = (int) (
        $data['supply_id'] ?? 0
    );

    $name = trim((string) (
        $data['name'] ?? ''
    ));

    $category = trim((string) (
        $data['category'] ?? ''
    ));

    $quantity = max(
        0,
        (float) ($data['quantity'] ?? 0)
    );

    $reorder_level = max(
        0,
        (float) ($data['reorder_level'] ?? 0)
    );

    $unit = trim((string) (
        $data['unit'] ?? ''
    ));

    $supplier = trim((string) (
        $data['supplier'] ?? ''
    ));

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

    $unit_cost_raw = trim((string) (
        $data['unit_cost'] ?? ''
    ));

    $unit_cost = $unit_cost_raw === ''
        ? null
        : max(0, (float) $unit_cost_raw);

    if ($name === '') {
        throw new InvalidArgumentException(
            'Supply name is required.'
        );
    }

    $pdo->beginTransaction();

    try {
        if ($supply_id > 0) {
            $stmt = $pdo->prepare("
                SELECT quantity
                FROM supplies
                WHERE id = :id
                  AND is_active = 1
                FOR UPDATE
            ");

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

            $before_quantity = $stmt->fetchColumn();

            if ($before_quantity === false) {
                throw new RuntimeException(
                    'Supply item could not be found.'
                );
            }

            $before_quantity = (float) $before_quantity;

            $stmt = $pdo->prepare("
                UPDATE supplies
                SET
                    name = :name,
                    category = :category,
                    quantity = :quantity,
                    reorder_level = :reorder_level,
                    unit = :unit,
                    supplier = :supplier,
                    unit_cost = :unit_cost,
                    notes = :notes
                WHERE id = :id
                  AND is_active = 1
            ");

            $stmt->execute([
                ':name' => $name,
                ':category' => $category,
                ':quantity' => $quantity,
                ':reorder_level' => $reorder_level,
                ':unit' => $unit,
                ':supplier' => $supplier,
                ':unit_cost' => $unit_cost,
                ':notes' => $notes,
                ':id' => $supply_id,
            ]);

            if (
                abs(
                    $quantity - $before_quantity
                ) > 0.00001
            ) {
                sparrow_add_supply_adjustment_record(
                    $pdo,
                    $supply_id,
                    $before_quantity,
                    $quantity,
                    'Correction',
                    'Quantity changed while editing the supply item.'
                );
            }
        } else {
            $stmt = $pdo->prepare("
                INSERT INTO supplies (
                    name,
                    category,
                    quantity,
                    reorder_level,
                    unit,
                    supplier,
                    unit_cost,
                    notes
                ) VALUES (
                    :name,
                    :category,
                    :quantity,
                    :reorder_level,
                    :unit,
                    :supplier,
                    :unit_cost,
                    :notes
                )
            ");

            $stmt->execute([
                ':name' => $name,
                ':category' => $category,
                ':quantity' => $quantity,
                ':reorder_level' => $reorder_level,
                ':unit' => $unit,
                ':supplier' => $supplier,
                ':unit_cost' => $unit_cost,
                ':notes' => $notes,
            ]);

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

            if ($quantity > 0) {
                sparrow_add_supply_adjustment_record(
                    $pdo,
                    $supply_id,
                    0,
                    $quantity,
                    'Starting Quantity',
                    'Quantity entered when the supply item was created.'
                );
            }
        }

        $pdo->commit();

        return $supply_id;
    } catch (Throwable $e) {
        if ($pdo->inTransaction()) {
            $pdo->rollBack();
        }

        throw $e;
    }
}

function sparrow_adjust_supply(
    int $supply_id,
    float $amount,
    string $adjustment_type = 'Adjustment',
    string $note = ''
): float {
    $pdo = sparrow_db();

    if ($supply_id <= 0) {
        throw new InvalidArgumentException(
            'Choose a valid supply item.'
        );
    }

    if (abs($amount) < 0.00001) {
        throw new InvalidArgumentException(
            'Adjustment amount cannot be zero.'
        );
    }

    $allowed_types = [
        'Restock',
        'Used',
        'Waste',
        'Correction',
        'Other',
    ];

    if (
        !in_array(
            $adjustment_type,
            $allowed_types,
            true
        )
    ) {
        throw new InvalidArgumentException(
            'Choose a valid adjustment reason.'
        );
    }

    $pdo->beginTransaction();

    try {
        $stmt = $pdo->prepare("
            SELECT quantity
            FROM supplies
            WHERE id = :id
              AND is_active = 1
            FOR UPDATE
        ");

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

        $before_quantity = $stmt->fetchColumn();

        if ($before_quantity === false) {
            throw new RuntimeException(
                'Supply item could not be found.'
            );
        }

        $before_quantity = (float) $before_quantity;

        $after_quantity = max(
            0,
            $before_quantity + $amount
        );

        if (
            abs(
                $after_quantity - $before_quantity
            ) < 0.00001
        ) {
            throw new RuntimeException(
                'This adjustment would not change the quantity.'
            );
        }

        $stmt = $pdo->prepare("
            UPDATE supplies
            SET quantity = :quantity
            WHERE id = :id
              AND is_active = 1
        ");

        $stmt->execute([
            ':quantity' => $after_quantity,
            ':id' => $supply_id,
        ]);

        sparrow_add_supply_adjustment_record(
            $pdo,
            $supply_id,
            $before_quantity,
            $after_quantity,
            $adjustment_type,
            trim($note)
        );

        $pdo->commit();

        return $after_quantity;
    } catch (Throwable $e) {
        if ($pdo->inTransaction()) {
            $pdo->rollBack();
        }

        throw $e;
    }
}

function sparrow_archive_supply(
    int $supply_id
): void {
    $stmt = sparrow_db()->prepare("
        UPDATE supplies
        SET is_active = 0
        WHERE id = :id
        LIMIT 1
    ");

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

function sparrow_get_supply_adjustments(
    ?int $supply_id = null,
    int $limit = 50
): array {
    $limit = max(
        1,
        min(250, $limit)
    );

    $pdo = sparrow_db();

    $sql = "
        SELECT
            a.*,
            s.name AS supply_name,
            s.unit AS supply_unit
        FROM supply_adjustments a
        INNER JOIN supplies s
            ON s.id = a.supply_id
    ";

    $params = [];

    if (
        $supply_id !== null
        && $supply_id > 0
    ) {
        $sql .= "
            WHERE a.supply_id = :supply_id
        ";

        $params[':supply_id'] = $supply_id;
    }

    $sql .= "
        ORDER BY
            a.created_at DESC,
            a.id DESC
        LIMIT {$limit}
    ";

    $stmt = $pdo->prepare($sql);
    $stmt->execute($params);

    return $stmt->fetchAll();
}

function sparrow_delete_supply(
    int $supply_id
): void {
    if ($supply_id <= 0) {
        throw new InvalidArgumentException(
            'Choose a valid supply item.'
        );
    }

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

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

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

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

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

        if ($stmt->rowCount() === 0) {
            throw new RuntimeException(
                'Supply item could not be found.'
            );
        }

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

        throw $e;
    }
}

function sparrow_add_supply_adjustment_record(
    PDO $pdo,
    int $supply_id,
    float $before_quantity,
    float $after_quantity,
    string $adjustment_type,
    string $note
): void {
    $stmt = $pdo->prepare("
        INSERT INTO supply_adjustments (
            supply_id,
            change_amount,
            quantity_before,
            quantity_after,
            adjustment_type,
            note
        ) VALUES (
            :supply_id,
            :change_amount,
            :quantity_before,
            :quantity_after,
            :adjustment_type,
            :note
        )
    ");

    $stmt->execute([
        ':supply_id' => $supply_id,
        ':change_amount' =>
            $after_quantity - $before_quantity,
        ':quantity_before' => $before_quantity,
        ':quantity_after' => $after_quantity,
        ':adjustment_type' => $adjustment_type,
        ':note' => $note,
    ]);
}