SET NAMES utf8mb4;

CREATE TABLE IF NOT EXISTS coverage_areas (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    area_name VARCHAR(150) NOT NULL,
    postal_code VARCHAR(10) NULL,
    city VARCHAR(100) NULL,
    latitude DECIMAL(10,7) NULL,
    longitude DECIMAL(10,7) NULL,
    radius_meters INT UNSIGNED NULL DEFAULT 1500,
    olt_id INT UNSIGNED NULL,
    odp_code VARCHAR(60) NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_postal (postal_code),
    INDEX idx_city (city),
    CONSTRAINT fk_coverage_olt FOREIGN KEY (olt_id) REFERENCES olts(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS coverage_check_logs (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    query_text VARCHAR(255) NOT NULL,
    latitude DECIMAL(10,7) NULL,
    longitude DECIMAL(10,7) NULL,
    result ENUM('available','not_available') NOT NULL,
    matched_area_id INT UNSIGNED NULL,
    ip_address VARCHAR(45) NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;