-- Audit scheduling: one-time, weekly, or monthly scheduled audits with assignment and reminder emails
CREATE TABLE IF NOT EXISTS audit_schedules (
    id INT AUTO_INCREMENT PRIMARY KEY,
    audit_name VARCHAR(255) NOT NULL,
    assigned_to_user_id INT NOT NULL,
    scheduled_date DATE NOT NULL,
    recurrence_type ENUM('once', 'weekly', 'monthly') NOT NULL DEFAULT 'once',
    recurrence_day_of_week TINYINT NULL COMMENT '0=Sun, 1=Mon, ... 6=Sat for weekly',
    recurrence_day_of_month TINYINT NULL COMMENT '1-31 for monthly',
    reminder_days_before INT NOT NULL DEFAULT 1,
    assignment_mail_sent_at DATETIME NULL,
    created_by INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (assigned_to_user_id) REFERENCES users(user_id),
    FOREIGN KEY (created_by) REFERENCES users(user_id)
);
