<?php
declare(strict_types=1);

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

function ensure_project_inquiries_table(PDO $pdo): void
{
    $pdo->exec(
        'CREATE TABLE IF NOT EXISTS project_inquiries (
            id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
            reference_code VARCHAR(32) NOT NULL UNIQUE,
            full_name VARCHAR(120) NOT NULL,
            company_name VARCHAR(150) NOT NULL,
            email VARCHAR(190) NOT NULL,
            project_type VARCHAR(80) NOT NULL,
            budget_range VARCHAR(80) NOT NULL,
            launch_window VARCHAR(80) NOT NULL,
            message TEXT NOT NULL,
            status VARCHAR(30) NOT NULL DEFAULT "new",
            created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci'
    );
}

function generate_inquiry_reference(): string
{
    return 'NSL-' . gmdate('ymd') . '-' . strtoupper(bin2hex(random_bytes(2)));
}

function create_project_inquiry(array $payload): string
{
    $pdo = db();
    ensure_project_inquiries_table($pdo);

    $reference = generate_inquiry_reference();
    $statement = $pdo->prepare(
        'INSERT INTO project_inquiries (
            reference_code,
            full_name,
            company_name,
            email,
            project_type,
            budget_range,
            launch_window,
            message
        ) VALUES (
            :reference_code,
            :full_name,
            :company_name,
            :email,
            :project_type,
            :budget_range,
            :launch_window,
            :message
        )'
    );

    $statement->bindValue(':reference_code', $reference, PDO::PARAM_STR);
    $statement->bindValue(':full_name', $payload['full_name'], PDO::PARAM_STR);
    $statement->bindValue(':company_name', $payload['company_name'], PDO::PARAM_STR);
    $statement->bindValue(':email', $payload['email'], PDO::PARAM_STR);
    $statement->bindValue(':project_type', $payload['project_type'], PDO::PARAM_STR);
    $statement->bindValue(':budget_range', $payload['budget_range'], PDO::PARAM_STR);
    $statement->bindValue(':launch_window', $payload['launch_window'], PDO::PARAM_STR);
    $statement->bindValue(':message', $payload['message'], PDO::PARAM_STR);
    $statement->execute();

    return $reference;
}

function find_project_inquiry(string $reference): ?array
{
    $pdo = db();
    ensure_project_inquiries_table($pdo);

    $statement = $pdo->prepare(
        'SELECT reference_code, full_name, company_name, email, project_type, budget_range, launch_window, message, status, created_at
         FROM project_inquiries
         WHERE reference_code = :reference_code
         LIMIT 1'
    );
    $statement->bindValue(':reference_code', $reference, PDO::PARAM_STR);
    $statement->execute();

    $result = $statement->fetch();
    return is_array($result) ? $result : null;
}