CREATE TABLE IF NOT EXISTS driver_off_duty (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    driver_id BIGINT UNSIGNED NOT NULL,
    off_date DATE NOT NULL,
    month_year VARCHAR(7) NOT NULL COMMENT 'YYYY-MM for grouping monthly 5-day limit',
    reason VARCHAR(255) NOT NULL DEFAULT '',
    notes TEXT NULL,
    created_by INT NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL DEFAULT NULL,
    deleted_at DATETIME NULL DEFAULT NULL,
    PRIMARY KEY (id),
    KEY idx_dod_driver (driver_id),
    KEY idx_dod_month (month_year),
    KEY idx_dod_date (off_date),
    CONSTRAINT fk_dod_driver FOREIGN KEY (driver_id) REFERENCES drivers(id) ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT IGNORE INTO permissions (code, description) VALUES
('driver.off_duty.view', 'View driver off-duty records'),
('driver.off_duty.create', 'Create driver off-duty records'),
('driver.off_duty.edit', 'Edit driver off-duty records'),
('driver.off_duty.delete', 'Delete driver off-duty records');