<?php
declare(strict_types=1);

require __DIR__ . '/db.php';

$pdo = db();
$method = $_SERVER['REQUEST_METHOD'];

if ($method === 'GET') {
    $rows = $pdo->query('SELECT * FROM pesticide_records ORDER BY customer_name ASC, application_date DESC, updated_at DESC')->fetchAll();
    $records = array_map(function (array $row): array {
        return [
            'id' => $row['id'],
            'recordName' => $row['record_name'],
            'applicationDate' => $row['application_date'],
            'method' => $row['method'],
            'siteType' => $row['site_type'],
            'areaTreated' => $row['area_treated'],
            'permitNumber' => $row['permit_number'],
            'location' => $row['location'],
            'customerName' => $row['customer_name'],
            'customerFirm' => $row['customer_firm'],
            'customerStreet' => $row['customer_street'],
            'customerCity' => $row['customer_city'],
            'customerState' => $row['customer_state'],
            'customerZip' => $row['customer_zip'],
            'applicatorName' => $row['applicator_name'],
            'applicatorLicense' => $row['applicator_license'],
            'applicatorFirm' => $row['applicator_firm'],
            'applicatorPhone' => $row['applicator_phone'],
            'applicatorStreet' => $row['applicator_street'],
            'applicatorCityStateZip' => $row['applicator_city_state_zip'],
            'startTime' => $row['start_time'],
            'endTime' => $row['end_time'],
            'temperature' => $row['temperature'],
            'windDirection' => $row['wind_direction'],
            'windVelocity' => $row['wind_velocity'],
            'equipment' => $row['equipment'],
            'weatherLookupEnabled' => (bool) $row['weather_lookup_enabled'],
            'weatherZip' => $row['weather_zip'],
            'weatherLat' => $row['weather_lat'],
            'weatherLon' => $row['weather_lon'],
            'notes' => $row['notes'],
            'pesticides' => json_decode($row['pesticides'] ?: '[]', true) ?: [],
            'createdAt' => $row['created_at'],
            'updatedAt' => $row['updated_at'],
        ];
    }, $rows);

    json_response($records);
}

if ($method === 'POST') {
    $data = input_json();
    $id = text_value($data, 'id');
    if ($id === '') {
        json_response(['error' => 'Record id is required.'], 422);
    }

    $now = gmdate('c');
    $sql = 'INSERT INTO pesticide_records (
        id, record_name, application_date, method, site_type, area_treated, permit_number, location,
        customer_name, customer_firm, customer_street, customer_city, customer_state, customer_zip,
        applicator_name, applicator_license, applicator_firm, applicator_phone, applicator_street,
        applicator_city_state_zip, start_time, end_time, temperature, wind_direction, wind_velocity,
        equipment, weather_lookup_enabled, weather_zip, weather_lat, weather_lon, notes, pesticides,
        created_at, updated_at
      ) VALUES (
        :id, :record_name, :application_date, :method, :site_type, :area_treated, :permit_number, :location,
        :customer_name, :customer_firm, :customer_street, :customer_city, :customer_state, :customer_zip,
        :applicator_name, :applicator_license, :applicator_firm, :applicator_phone, :applicator_street,
        :applicator_city_state_zip, :start_time, :end_time, :temperature, :wind_direction, :wind_velocity,
        :equipment, :weather_lookup_enabled, :weather_zip, :weather_lat, :weather_lon, :notes, :pesticides,
        :created_at, :updated_at
      ) ON DUPLICATE KEY UPDATE
        record_name = VALUES(record_name),
        application_date = VALUES(application_date),
        method = VALUES(method),
        site_type = VALUES(site_type),
        area_treated = VALUES(area_treated),
        permit_number = VALUES(permit_number),
        location = VALUES(location),
        customer_name = VALUES(customer_name),
        customer_firm = VALUES(customer_firm),
        customer_street = VALUES(customer_street),
        customer_city = VALUES(customer_city),
        customer_state = VALUES(customer_state),
        customer_zip = VALUES(customer_zip),
        applicator_name = VALUES(applicator_name),
        applicator_license = VALUES(applicator_license),
        applicator_firm = VALUES(applicator_firm),
        applicator_phone = VALUES(applicator_phone),
        applicator_street = VALUES(applicator_street),
        applicator_city_state_zip = VALUES(applicator_city_state_zip),
        start_time = VALUES(start_time),
        end_time = VALUES(end_time),
        temperature = VALUES(temperature),
        wind_direction = VALUES(wind_direction),
        wind_velocity = VALUES(wind_velocity),
        equipment = VALUES(equipment),
        weather_lookup_enabled = VALUES(weather_lookup_enabled),
        weather_zip = VALUES(weather_zip),
        weather_lat = VALUES(weather_lat),
        weather_lon = VALUES(weather_lon),
        notes = VALUES(notes),
        pesticides = VALUES(pesticides),
        updated_at = VALUES(updated_at)';

    $statement = $pdo->prepare($sql);
    $statement->execute([
        ':id' => $id,
        ':record_name' => text_value($data, 'recordName'),
        ':application_date' => text_value($data, 'applicationDate') ?: null,
        ':method' => text_value($data, 'method'),
        ':site_type' => text_value($data, 'siteType'),
        ':area_treated' => text_value($data, 'areaTreated'),
        ':permit_number' => text_value($data, 'permitNumber'),
        ':location' => text_value($data, 'location'),
        ':customer_name' => text_value($data, 'customerName'),
        ':customer_firm' => text_value($data, 'customerFirm'),
        ':customer_street' => text_value($data, 'customerStreet'),
        ':customer_city' => text_value($data, 'customerCity'),
        ':customer_state' => text_value($data, 'customerState'),
        ':customer_zip' => text_value($data, 'customerZip'),
        ':applicator_name' => text_value($data, 'applicatorName'),
        ':applicator_license' => text_value($data, 'applicatorLicense'),
        ':applicator_firm' => text_value($data, 'applicatorFirm'),
        ':applicator_phone' => text_value($data, 'applicatorPhone'),
        ':applicator_street' => text_value($data, 'applicatorStreet'),
        ':applicator_city_state_zip' => text_value($data, 'applicatorCityStateZip'),
        ':start_time' => text_value($data, 'startTime'),
        ':end_time' => text_value($data, 'endTime'),
        ':temperature' => text_value($data, 'temperature'),
        ':wind_direction' => text_value($data, 'windDirection'),
        ':wind_velocity' => text_value($data, 'windVelocity'),
        ':equipment' => text_value($data, 'equipment'),
        ':weather_lookup_enabled' => !empty($data['weatherLookupEnabled']) ? 1 : 0,
        ':weather_zip' => text_value($data, 'weatherZip'),
        ':weather_lat' => text_value($data, 'weatherLat'),
        ':weather_lon' => text_value($data, 'weatherLon'),
        ':notes' => text_value($data, 'notes'),
        ':pesticides' => json_value($data, 'pesticides'),
        ':created_at' => text_value($data, 'createdAt') ?: $now,
        ':updated_at' => text_value($data, 'updatedAt') ?: $now,
    ]);

    json_response($data);
}

if ($method === 'DELETE') {
    $id = isset($_GET['id']) ? trim((string) $_GET['id']) : '';
    if ($id === '') {
        json_response(['error' => 'Record id is required.'], 422);
    }

    $statement = $pdo->prepare('DELETE FROM pesticide_records WHERE id = :id');
    $statement->execute([':id' => $id]);
    json_response(['ok' => true]);
}

json_response(['error' => 'Method not allowed.'], 405);