-- Migration 012: Appointment routing & optimization
-- Adds geocoding, tech availability, conflict detection, route optimization

-- 1. Add lat/lng to customers (geocoded service address)
-- Use dynamic SQL to avoid errors if columns already exist
SET @dbname = DATABASE();

SET @sql = (SELECT IF(
    COUNT(*) = 0,
    'ALTER TABLE customers ADD COLUMN service_lat DECIMAL(10,7) NULL, ADD COLUMN service_lng DECIMAL(10,7) NULL, ADD COLUMN geo_updated_at TIMESTAMP NULL',
    'SELECT 1'
) FROM information_schema.columns
WHERE table_schema = @dbname AND table_name = 'customers' AND column_name = 'service_lat');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- 1b. Add lat/lng to service_addresses
SET @sql = (SELECT IF(
    COUNT(*) = 0,
    'ALTER TABLE service_addresses ADD COLUMN lat DECIMAL(10,7) NULL, ADD COLUMN lng DECIMAL(10,7) NULL, ADD COLUMN geo_updated_at TIMESTAMP NULL',
    'SELECT 1'
) FROM information_schema.columns
WHERE table_schema = @dbname AND table_name = 'service_addresses' AND column_name = 'lat');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- 2. Tech availability - work schedule and blocked dates
CREATE TABLE IF NOT EXISTS tech_availability (
    id              INT AUTO_INCREMENT PRIMARY KEY,
    user_id         INT NOT NULL,
    day_of_week     TINYINT NOT NULL COMMENT '0=Sun,1=Mon,...,6=Sat',
    start_time      TIME NOT NULL DEFAULT '09:00:00',
    end_time        TIME NOT NULL DEFAULT '17:00:00',
    max_appts       INT NOT NULL DEFAULT 8 COMMENT 'max appointments per day',
    UNIQUE KEY uk_tech_day (user_id, day_of_week),
    KEY idx_tech (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Seed default Mon-Fri 9-5, 8 appts/day for all existing field techs
INSERT IGNORE INTO tech_availability (user_id, day_of_week, start_time, end_time, max_appts)
SELECT u.user_id, d.day, '09:00:00', '17:00:00', 8
FROM users u
CROSS JOIN (SELECT 1 AS day UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) d
WHERE u.role = 'technician' AND u.is_field_tech = 1;

-- 3. Blocked dates (PTO, holidays, training)
CREATE TABLE IF NOT EXISTS tech_blocked_dates (
    id          INT AUTO_INCREMENT PRIMARY KEY,
    user_id     INT NOT NULL,
    block_date  DATE NOT NULL,
    reason      VARCHAR(100) NULL COMMENT 'pto, holiday, training, etc.',
    UNIQUE KEY uk_tech_date (user_id, block_date),
    KEY idx_tech (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 4. Appointment travel time cache (optional, for route optimization)
CREATE TABLE IF NOT EXISTS appointment_travel (
    id                  INT AUTO_INCREMENT PRIMARY KEY,
    appointment_id      INT NOT NULL,
    prev_appointment_id INT NULL COMMENT 'null = first stop of the day',
    travel_minutes      INT NULL COMMENT 'estimated drive time',
    distance_miles      DECIMAL(6,2) NULL,
    stop_order          INT NOT NULL DEFAULT 0,
    UNIQUE KEY uk_appt_prev (appointment_id, prev_appointment_id),
    KEY idx_appt (appointment_id),
    KEY idx_date_tech (appointment_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 5. Geocode existing customers that have addresses
-- (This is a one-time seed - in production, run via admin action)
-- Note: Actual geocoding requires an API call; this sets NULL for now.
-- The application will geocode on address save/update.