CREATE TABLE IF NOT EXISTS location_logs (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    user_id BIGINT UNSIGNED NOT NULL,
    work_session_id BIGINT UNSIGNED NOT NULL,
    logged_at DATETIME NOT NULL,
    latitude DECIMAL(10,8) NOT NULL,
    longitude DECIMAL(11,8) NOT NULL,
    accuracy_meters DECIMAL(8,2) DEFAULT NULL,
    speed_kmh DECIMAL(8,2) DEFAULT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_location_logs_user_time (user_id, logged_at),
    KEY idx_location_logs_session_time (work_session_id, logged_at),
    CONSTRAINT fk_location_logs_user
        FOREIGN KEY (user_id) REFERENCES users(id)
        ON UPDATE CASCADE
        ON DELETE CASCADE,
    CONSTRAINT fk_location_logs_session
        FOREIGN KEY (work_session_id) REFERENCES work_sessions(id)
        ON UPDATE CASCADE
        ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS location_stops (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    user_id BIGINT UNSIGNED NOT NULL,
    work_session_id BIGINT UNSIGNED NOT NULL,
    stop_start_time DATETIME NOT NULL,
    stop_end_time DATETIME NOT NULL,
    duration_minutes INT UNSIGNED NOT NULL,
    latitude DECIMAL(10,8) NOT NULL,
    longitude DECIMAL(11,8) NOT NULL,
    notes VARCHAR(255) DEFAULT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_location_stops_user_time (user_id, stop_start_time),
    KEY idx_location_stops_session_time (work_session_id, stop_start_time),
    CONSTRAINT fk_location_stops_user
        FOREIGN KEY (user_id) REFERENCES users(id)
        ON UPDATE CASCADE
        ON DELETE CASCADE,
    CONSTRAINT fk_location_stops_session
        FOREIGN KEY (work_session_id) REFERENCES work_sessions(id)
        ON UPDATE CASCADE
        ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;