CREATE TABLE IF NOT EXISTS work_sessions (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    user_id BIGINT UNSIGNED NOT NULL,
    worksite_id BIGINT UNSIGNED NOT NULL,
    session_date DATE NOT NULL,
    clock_in_time DATETIME NOT NULL,
    clock_in_latitude DECIMAL(10,8) DEFAULT NULL,
    clock_in_longitude DECIMAL(11,8) DEFAULT NULL,
    clock_in_accuracy_meters DECIMAL(8,2) DEFAULT NULL,
    clock_in_distance_meters DECIMAL(10,2) DEFAULT NULL,
    clock_in_ip_address VARCHAR(45) DEFAULT NULL,
    clock_in_device_info VARCHAR(255) DEFAULT NULL,
    clock_in_status ENUM('ON_SITE','OUTSIDE_RADIUS','MANUAL','REJECTED') DEFAULT NULL,
    clock_out_time DATETIME DEFAULT NULL,
    clock_out_latitude DECIMAL(10,8) DEFAULT NULL,
    clock_out_longitude DECIMAL(11,8) DEFAULT NULL,
    clock_out_accuracy_meters DECIMAL(8,2) DEFAULT NULL,
    clock_out_distance_meters DECIMAL(10,2) DEFAULT NULL,
    clock_out_ip_address VARCHAR(45) DEFAULT NULL,
    clock_out_device_info VARCHAR(255) DEFAULT NULL,
    clock_out_status ENUM('ON_SITE','OUTSIDE_RADIUS','MANUAL','REJECTED') DEFAULT NULL,
    session_status ENUM('OPEN','CLOSED','REJECTED') NOT NULL DEFAULT 'OPEN',
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_work_sessions_user_id (user_id),
    KEY idx_work_sessions_worksite_id (worksite_id),
    KEY idx_work_sessions_session_date (session_date),
    KEY idx_work_sessions_open (user_id, session_status, clock_out_time),
    CONSTRAINT fk_work_sessions_user
        FOREIGN KEY (user_id) REFERENCES users(id)
        ON UPDATE CASCADE
        ON DELETE CASCADE,
    CONSTRAINT fk_work_sessions_worksite
        FOREIGN KEY (worksite_id) REFERENCES worksites(id)
        ON UPDATE CASCADE
        ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;