-- Notifications System SQL Schema

CREATE TABLE IF NOT EXISTS notifications (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_type ENUM('staff', 'client') NOT NULL,
    user_id INT NOT NULL,
    title VARCHAR(255) NOT NULL,
    message TEXT NOT NULL,
    type VARCHAR(50) DEFAULT 'general',
    is_read BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_user (user_type, user_id),
    INDEX idx_created_at (created_at),
    INDEX idx_is_read (is_read)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Add sample notifications
INSERT INTO notifications (user_type, user_id, title, message, type) VALUES
('staff', 1, 'Welcome to OctaDigital!', 'Your account has been created successfully. Complete your profile to get started.', 'info'),
('staff', 1, 'Time Tracking Reminder', 'Don\'t forget to punch out at the end of your shift.', 'reminder');
