Database Schema

The application creates multiple tables for comprehensive event and task management:

Events Table

CREATE TABLE events (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    description TEXT,
    start_date DATE NOT NULL,
    end_date DATE NOT NULL,
    start_time TIME,
    end_time TIME,
    is_all_day BOOLEAN DEFAULT FALSE,
    is_recurring BOOLEAN DEFAULT FALSE,
    recurrence_type ENUM('none', 'daily', 'weekly', 'monthly', 'monthly_weekday', 'yearly', 'custom') DEFAULT 'none',
    recurrence_interval INT DEFAULT 1,
    recurrence_end_date DATE NULL,
    recurrence_count INT NULL,
    recurrence_weekday INT NULL,
    recurrence_week_of_month VARCHAR(10) NULL,
    colour VARCHAR(7) DEFAULT '#3B82F6',
    notification_enabled BOOLEAN DEFAULT FALSE,
    notification_minutes INT DEFAULT 30,
    notification_type ENUM('minutes_before', 'specific_datetime') DEFAULT 'minutes_before',
    notification_datetime DATETIME NULL,
    notification_sent BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

Task Categories Table

CREATE TABLE task_categories (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    colour VARCHAR(7) DEFAULT '#3B82F6',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Tasks Table

CREATE TABLE tasks (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    description TEXT,
    due_date DATE,
    is_completed BOOLEAN DEFAULT FALSE,
    category_id INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (category_id) REFERENCES task_categories(id) ON DELETE SET NULL
);

Notification History Table

CREATE TABLE notification_history (
    id INT AUTO_INCREMENT PRIMARY KEY,
    event_id INT NOT NULL,
    occurrence_date DATE NOT NULL,
    notification_sent BOOLEAN DEFAULT FALSE,
    sent_at TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (event_id) REFERENCES events(id) ON DELETE CASCADE,
    UNIQUE KEY unique_event_occurrence (event_id, occurrence_date)
);