<?php
// config/location_validator.php

class LocationValidator {
    private $conn;
    
    public function __construct($db) {
        $this->conn = $db;
    }

    private function ensureServiceAreasTable() {
        $this->conn->exec("CREATE TABLE IF NOT EXISTS service_areas (
            id INT AUTO_INCREMENT PRIMARY KEY,
            zip_code VARCHAR(10) NOT NULL,
            city VARCHAR(100) NOT NULL,
            state VARCHAR(50) NOT NULL,
            region VARCHAR(100) NOT NULL,
            is_active TINYINT(1) NOT NULL DEFAULT 1,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            UNIQUE KEY unique_zip_city_state_region (zip_code, city, state, region),
            INDEX idx_service_zip (zip_code),
            INDEX idx_service_city (city),
            INDEX idx_service_state (state),
            INDEX idx_service_region (region),
            INDEX idx_service_active (is_active)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
    }

    private function stateAbbrev($state) {
        $state = trim((string)$state);
        $map = [
            'ALABAMA'=>'AL','AL'=>'AL','ARKANSAS'=>'AR','AR'=>'AR','FLORIDA'=>'FL','FL'=>'FL','GEORGIA'=>'GA','GA'=>'GA',
            'ILLINOIS'=>'IL','IL'=>'IL','INDIANA'=>'IN','IN'=>'IN','KENTUCKY'=>'KY','KY'=>'KY','LOUISIANA'=>'LA','LA'=>'LA',
            'MISSISSIPPI'=>'MS','MS'=>'MS','MISSOURI'=>'MO','MO'=>'MO','NORTH CAROLINA'=>'NC','NC'=>'NC','OKLAHOMA'=>'OK','OK'=>'OK',
            'SOUTH CAROLINA'=>'SC','SC'=>'SC','TENNESSEE'=>'TN','TN'=>'TN','TEXAS'=>'TX','TX'=>'TX','VIRGINIA'=>'VA','VA'=>'VA'
        ];
        return $map[strtoupper($state)] ?? strtoupper($state);
    }

    private function ensureCityRecord($city, $state, $region) {
        $city = trim((string)$city);
        $state = trim((string)$state);
        $region = trim((string)$region);
        if ($city === '') { return null; }

        try {
            $q = $this->conn->prepare("SELECT id FROM cities WHERE LOWER(city_name)=LOWER(:city) AND (LOWER(state)=LOWER(:state) OR UPPER(state)=UPPER(:abbr)) LIMIT 1");
            $abbr = $this->stateAbbrev($state);
            $q->execute([':city'=>$city, ':state'=>$state, ':abbr'=>$abbr]);
            $id = $q->fetchColumn();
            if ($id) { return (int)$id; }

            $ins = $this->conn->prepare("INSERT INTO cities (city_name, region, state, is_active) VALUES (:city, :region, :state, 1)");
            $ins->execute([':city'=>$city, ':region'=>$region, ':state'=>$state]);
            return (int)$this->conn->lastInsertId();
        } catch (Exception $e) {
            return null;
        }
    }

    /** Get all active cities from the service_areas master coverage table. */
    public function getActiveCities() {
        $this->ensureServiceAreasTable();
        $query = "SELECT 
                    MIN(id) AS id,
                    city AS city_name,
                    state,
                    region,
                    COUNT(DISTINCT zip_code) AS zip_count
                  FROM service_areas
                  WHERE is_active = 1
                  GROUP BY city, state, region
                  ORDER BY region, state, city";
        $stmt = $this->conn->prepare($query);
        $stmt->execute();
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }

    /** Compatibility: region/city group has no separate locations in the new master coverage table. */
    public function getLocationsByCity($city_id) {
        $this->ensureServiceAreasTable();
        $query = "SELECT id, city AS location_name FROM service_areas WHERE id = :id AND is_active = 1 LIMIT 1";
        $stmt = $this->conn->prepare($query);
        $stmt->execute([':id'=>$city_id]);
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }

    /** Compatibility: get ZIPs for a service_areas row/city group. */
    public function getZipCodesByLocation($location_id) {
        $this->ensureServiceAreasTable();
        $row = $this->conn->prepare("SELECT city, state, region FROM service_areas WHERE id = :id LIMIT 1");
        $row->execute([':id'=>$location_id]);
        $meta = $row->fetch(PDO::FETCH_ASSOC);
        if (!$meta) { return []; }
        $query = "SELECT zip_code FROM service_areas WHERE city=:city AND state=:state AND region=:region AND is_active=1 ORDER BY zip_code";
        $stmt = $this->conn->prepare($query);
        $stmt->execute([':city'=>$meta['city'], ':state'=>$meta['state'], ':region'=>$meta['region']]);
        return $stmt->fetchAll(PDO::FETCH_COLUMN);
    }

    /** Validate if zip code is serviceable and return full details from service_areas first. */
    public function validateZipCode($zip_code) {
        $this->ensureServiceAreasTable();
        $zip_code = trim((string)$zip_code);
        if ($zip_code === '') { return false; }

        $query = "SELECT id, zip_code, city, state, region
                  FROM service_areas
                  WHERE zip_code = :zip_code AND is_active = 1
                  ORDER BY region, city
                  LIMIT 1";
        $stmt = $this->conn->prepare($query);
        $stmt->execute([':zip_code'=>$zip_code]);
        $row = $stmt->fetch(PDO::FETCH_ASSOC);

        if ($row) {
            $cityId = $this->ensureCityRecord($row['city'], $row['state'], $row['region']);
            return [
                'city_id' => $cityId ?: (int)$row['id'],
                'city_name' => $row['city'],
                'state' => $row['state'],
                'state_abbr' => $this->stateAbbrev($row['state']),
                'region' => $row['region'],
                'location_id' => null,
                'location_name' => $row['city'],
                'service_area_id' => (int)$row['id'],
                'service_region_name' => $row['region'],
                'zip_code' => $row['zip_code']
            ];
        }

        // Legacy fallback for old data if service_areas is empty or ZIP is only in old tables.
        try {
            $query = "SELECT 
                        c.id as city_id,
                        c.city_name as service_region_name,
                        c.state as service_region_state,
                        c.region as city_region,
                        l.id as location_id,
                        l.location_name,
                        l.region as location_region,
                        z.zip_code,
                        z.region as zip_region,
                        z.state as zip_state
                      FROM zip_codes z
                      JOIN locations l ON z.location_id = l.id
                      JOIN cities c ON c.id = COALESCE(z.city_id, l.city_id)
                      WHERE z.zip_code = :zip_code 
                        AND COALESCE(z.is_active,1) = 1 
                        AND COALESCE(l.is_active,1) = 1 
                        AND COALESCE(c.is_active,1) = 1
                      LIMIT 1";
            $stmt = $this->conn->prepare($query);
            $stmt->execute([':zip_code'=>$zip_code]);
            $row = $stmt->fetch(PDO::FETCH_ASSOC);
            if (!$row) { return false; }
            $actualCity = trim($row['location_name'] ?? '');
            $region = trim($row['zip_region'] ?: ($row['location_region'] ?: ($row['city_region'] ?: $row['service_region_name'])));
            $state = trim($row['zip_state'] ?: $row['service_region_state']);
            return [
                'city_id' => (int)$row['city_id'],
                'city_name' => $actualCity ?: $row['service_region_name'],
                'state' => $state,
                'state_abbr' => $this->stateAbbrev($state),
                'region' => $region ?: $row['service_region_name'],
                'location_id' => (int)$row['location_id'],
                'location_name' => $actualCity ?: $row['service_region_name'],
                'service_region_name' => $row['service_region_name'],
                'zip_code' => $row['zip_code']
            ];
        } catch (Exception $e) {
            return false;
        }
    }

    public function getCityIdByZip($zip_code) {
        $result = $this->validateZipCode($zip_code);
        return $result ? $result['city_id'] : null;
    }

    public function getLocationIdByZip($zip_code) {
        $result = $this->validateZipCode($zip_code);
        return $result ? ($result['location_id'] ?? null) : null;
    }

    public function getAllServiceableAreasGrouped() {
        $this->ensureServiceAreasTable();
        $query = "SELECT 
                    region AS city_name,
                    state,
                    GROUP_CONCAT(DISTINCT zip_code ORDER BY zip_code SEPARATOR ', ') as zip_codes,
                    COUNT(DISTINCT zip_code) as zip_count,
                    COUNT(DISTINCT city) as city_count
                  FROM service_areas
                  WHERE is_active = 1
                  GROUP BY region, state
                  ORDER BY region, state";
        $stmt = $this->conn->prepare($query);
        $stmt->execute();
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }

    public function isServiceable($zip_code) {
        return !empty($this->validateZipCode($zip_code));
    }

    public function getServiceableMessage($zip_code) {
        $location = $this->validateZipCode($zip_code);
        if($location) {
            return [
                'serviceable' => true,
                'message' => "Perfect! This ZIP code is within our coverage area. We provide service in {$location['city_name']}, {$location['state']} ({$location['region']} region).",
                'city' => $location['city_name'],
                'city_name' => $location['city_name'],
                'state' => $location['state'],
                'state_abbr' => $location['state_abbr'] ?? $this->stateAbbrev($location['state']),
                'location' => $location['location_name'],
                'location_name' => $location['location_name'],
                'region' => $location['region'],
                'service_region_name' => $location['service_region_name'] ?? $location['region'],
                'city_id' => $location['city_id'],
                'location_id' => $location['location_id'] ?? null,
                'service_area_id' => $location['service_area_id'] ?? null,
                'zip_code' => $location['zip_code']
            ];
        }
        return [
            'serviceable' => false,
            'message' => "Uh oh! This ZIP code is outside of our coverage area. Please submit your request, and our reps will call you shortly regarding service!",
            'city' => null,
            'location' => null
        ];
    }
}
?>