smart_task.sql

-- SmartTask Database Schema

-- Users table
CREATE TABLE IF NOT EXISTS users (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(100) NOT NULL UNIQUE,
    email VARCHAR(255) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_username (username),
    INDEX idx_email (email)
);

-- Refresh tokens table
CREATE TABLE IF NOT EXISTS refresh_tokens (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT NOT NULL,
    token_hash VARCHAR(255) NOT NULL UNIQUE,
    issued_at TIMESTAMP NOT NULL,
    expires_at TIMESTAMP NOT NULL,
    revoked_at TIMESTAMP NULL,
    replaced_by_token_hash VARCHAR(255) NULL,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_user_id (user_id),
    INDEX idx_token_hash (token_hash),
    INDEX idx_expires_at (expires_at)
);

-- Tasks table
CREATE TABLE IF NOT EXISTS tasks (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT NOT NULL,
    title VARCHAR(200) NOT NULL,
    description VARCHAR(2000),
    estimated_duration INT NOT NULL CHECK (estimated_duration BETWEEN 1 AND 1140),
    priority INT NOT NULL CHECK (priority BETWEEN 1 AND 5),
    status VARCHAR(20) NOT NULL DEFAULT 'pending' CHECK (status IN ('pending','in-progress','completed')),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    completed_at TIMESTAMP NULL,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_user_id (user_id),
    INDEX idx_task_status (status),
    INDEX idx_priority (priority)
);

-- Sub-tasks table
CREATE TABLE IF NOT EXISTS sub_tasks (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    parent_task_id BIGINT NOT NULL,
    user_id BIGINT NOT NULL,
    title VARCHAR(200) NOT NULL,
    estimated_duration INT NOT NULL CHECK (estimated_duration BETWEEN 1 AND 1140),
    priority INT NOT NULL CHECK (priority BETWEEN 1 AND 5),
    status VARCHAR(20) NOT NULL DEFAULT 'pending' CHECK (status IN ('pending','in-progress','completed')),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    completed_at TIMESTAMP NULL,
    FOREIGN KEY (parent_task_id) REFERENCES tasks(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_parent_task_id (parent_task_id),
    INDEX idx_sub_task_user_id (user_id),
    INDEX idx_sub_task_status (status)
);

-- Sub-task dependencies table
CREATE TABLE IF NOT EXISTS sub_task_dependencies (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    sub_task_id BIGINT NOT NULL,
    prereq_sub_task_id BIGINT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (sub_task_id) REFERENCES sub_tasks(id) ON DELETE CASCADE,
    FOREIGN KEY (prereq_sub_task_id) REFERENCES sub_tasks(id) ON DELETE CASCADE,
    UNIQUE KEY uk_sub_task_prereq (sub_task_id, prereq_sub_task_id),
    INDEX idx_sub_task_id (sub_task_id),
    INDEX idx_prereq_sub_task_id (prereq_sub_task_id)
);

-- Time blocks table
CREATE TABLE IF NOT EXISTS time_blocks (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT NOT NULL,
    type VARCHAR(20) NOT NULL CHECK (type IN ('recurring','one-time')),
    day_of_week TINYINT NULL CHECK (day_of_week BETWEEN 1 AND 7),
    date DATE NULL,
    start_time TIME NOT NULL,
    end_time TIME NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_user_id (user_id),
    INDEX idx_day_of_week (day_of_week),
    INDEX idx_date (date),
    CONSTRAINT chk_type CHECK ((type='recurring' AND day_of_week IS NOT NULL AND date IS NULL) OR (type='one-time' AND day_of_week IS NULL AND date IS NOT NULL))
);

-- Scheduled sub-tasks table
CREATE TABLE IF NOT EXISTS scheduled_sub_tasks (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    sub_task_id BIGINT NOT NULL,
    user_id BIGINT NOT NULL,
    scheduled_date DATE NOT NULL,
    start_time TIME NOT NULL,
    end_time TIME NOT NULL,
    duration INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (sub_task_id) REFERENCES sub_tasks(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_sub_task_id (sub_task_id),
    INDEX idx_scheduled_date (scheduled_date),
    INDEX idx_user_scheduled_date (user_id, scheduled_date)
);