-- Create scheduled_maintenance table
-- This table stores information about scheduled maintenance periods

CREATE TABLE IF NOT EXISTS scheduled_maintenance (
    id INT PRIMARY KEY AUTO_INCREMENT,
    scheduled_datetime DATETIME NOT NULL,
    estimated_duration VARCHAR(100),
    message TEXT NOT NULL,
    status ENUM('scheduled', 'completed', 'cancelled') DEFAULT 'scheduled',
    created_by INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    cancelled_by INT NULL,
    cancelled_at TIMESTAMP NULL,
    completed_at TIMESTAMP NULL,
    
    FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (cancelled_by) REFERENCES users(id) ON DELETE SET NULL,
    
    INDEX idx_scheduled_datetime (scheduled_datetime),
    INDEX idx_status (status),
    INDEX idx_created_by (created_by)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Add comment to table
ALTER TABLE scheduled_maintenance COMMENT = 'Stores scheduled maintenance periods and their details';