-- -----------------------------------------------------
-- Database: `yourclassroom_prod_9e136a71fca2`
-- -----------------------------------------------------
DROP DATABASE IF EXISTS `yourclassroom_prod_9e136a71fca2`;
CREATE DATABASE `yourclassroom_prod_9e136a71fca2`;
USE `yourclassroom_prod_9e136a71fca2`;

-- -----------------------------------------------------
-- Table `users`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `users`
(
    `user_id`    INT AUTO_INCREMENT PRIMARY KEY,
    `username`   VARCHAR(50)                                       NOT NULL UNIQUE,
    `password`   VARCHAR(255)                                      NOT NULL, -- Store hashed passwords, *not* plain text!
    `email`      VARCHAR(100) UNIQUE,
    `first_name` VARCHAR(50),
    `last_name`  VARCHAR(50),
    `user_type`  JSON                                              NOT NULL,
    `parents`    JSON                                              DEFAULT NULL,
    `timezone`   VARCHAR(50)                                       DEFAULT 'America/New_York',
    `avatar`     LONGTEXT                                          DEFAULT NULL
);

CREATE INDEX idx_username ON users (username); -- For fast username lookups
-- For fast user type lookups

-- -----------------------------------------------------
-- Table `classes`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `classes`
(
    `class_id`      INT AUTO_INCREMENT PRIMARY KEY,
    `class_name`    VARCHAR(100) NOT NULL,
    `instructor_id` INT          NOT NULL,
    `description`   TEXT
);

-- Create a join table to represent the many-to-many relationship between classes and instructors
CREATE TABLE IF NOT EXISTS `class_instructors`
(
    `class_id` INT NOT NULL,
    `user_id`  INT NOT NULL,
    PRIMARY KEY (`class_id`, `user_id`), -- Composite primary key
    FOREIGN KEY (`class_id`) REFERENCES `classes` (`class_id`),
    FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`)
);

-- Optionally, add indexes for faster lookups
CREATE INDEX idx_class_id ON class_instructors (class_id);
CREATE INDEX idx_user_id ON class_instructors (user_id);

-- -----------------------------------------------------
-- Table `enrollments`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `enrollments`
(
    `enrollment_id`   INT AUTO_INCREMENT PRIMARY KEY,
    `user_id`         INT NOT NULL,
    `class_id`        INT NOT NULL,
    `enrollment_date` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`),
    FOREIGN KEY (`class_id`) REFERENCES `classes` (`class_id`),
    UNIQUE KEY `unique_enrollment` (`user_id`, `class_id`) -- Prevent duplicate enrollments
);

-- -----------------------------------------------------
-- Table `assignments`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `assignments`
(
    `assignment_id`   INT AUTO_INCREMENT PRIMARY KEY,
    `class_id`        INT          NULL,
    `assignment_name` VARCHAR(100) NOT NULL,
    `description`     TEXT,
    `due_date`        DATETIME,
    `max_points`      INT,
    FOREIGN KEY (`class_id`) REFERENCES `classes` (`class_id`)
);

-- Create a table to link assignments to instructors
CREATE TABLE IF NOT EXISTS `assignment_instructors`
(
    `assignment_id` INT NOT NULL,
    `user_id`       INT NOT NULL,
    PRIMARY KEY (`assignment_id`, `user_id`),
    FOREIGN KEY (`assignment_id`) REFERENCES `assignments` (`assignment_id`),
    FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`)
);

-- Add indexes for performance
CREATE INDEX idx_assignment_instructors_assignment_id ON assignment_instructors (assignment_id);
CREATE INDEX idx_assignment_instructors_user_id ON assignment_instructors (user_id);

-- -----------------------------------------------------
-- Table `submissions`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `submissions`
(
    `submission_id`   INT AUTO_INCREMENT PRIMARY KEY,
    `assignment_id`   INT                                                           NOT NULL,
    `submission_date` TIMESTAMP                                                              DEFAULT CURRENT_TIMESTAMP,
    `status`          ENUM ('assigned', 'draft', 'submitted', 'review', 'complete') NOT NULL DEFAULT 'assigned',
    `grade`           INT,          --  Can be NULL until graded
    `feedback`        TEXT,
    `document`        TEXT,
    FOREIGN KEY (`assignment_id`) REFERENCES `assignments` (`assignment_id`)
);

-- -----------------------------------------------------
-- Table `submission_files`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `submission_files` (
    `submission_file_id` INT AUTO_INCREMENT PRIMARY KEY,
    `submission_id` INT,
    `file_path` VARCHAR(255),
    FOREIGN KEY (`submission_id`) REFERENCES `submissions`(`submission_id`)
);

-- -----------------------------------------------------
-- Table `submission_users` to support group assignments
-- -----------------------------------------------------

-- Create a join table to represent the many-to-many relationship between submissions and users
CREATE TABLE IF NOT EXISTS `submission_users`
(
    `submission_id` INT NOT NULL,
    `user_id`       INT NOT NULL,
    PRIMARY KEY (`submission_id`, `user_id`), -- Composite primary key
    FOREIGN KEY (`submission_id`) REFERENCES `submissions` (`submission_id`),
    FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`)
);

-- Optionally, add indexes for faster lookups
CREATE INDEX idx_submission_id ON submission_users (submission_id);
CREATE INDEX idx_user_id_submission_users ON submission_users (user_id); -- to avoid conflict with other user_id indexes

CREATE TABLE IF NOT EXISTS `settings`
(
    `id`                    INT AUTO_INCREMENT PRIMARY KEY,
    `app_name`              VARCHAR(50)  NOT NULL UNIQUE,
    `app_icon`              VARCHAR(255) NOT NULL, -- file path to uploaded file
    `admin_email`           VARCHAR(100) NOT NULL UNIQUE,
    `admin_name`            VARCHAR(150),
    `admin_password`        VARCHAR(255) NOT NULL,
    `admin_phone`           VARCHAR(20),
    `admin_address`         VARCHAR(255),
    `admin_city`            VARCHAR(50),
    `admin_state`           VARCHAR(50),
    `admin_zip`             VARCHAR(10),
    `admin_country`         VARCHAR(50),
    `admin_website`         VARCHAR(100),
    `admin_facebook`        VARCHAR(100),
    `admin_twitter`         VARCHAR(100),
    `admin_linkedin`        VARCHAR(100),
    `admin_pinterest`       VARCHAR(100),
    `admin_instagram`       VARCHAR(100),
    `admin_youtube`         VARCHAR(100),
    `admin_vimeo`           VARCHAR(100),
    `admin_flickr`          VARCHAR(100),
    `admin_dribbble`        VARCHAR(100),
    `timezone`              VARCHAR(50)  NOT NULL DEFAULT 'America/New_York',
    `google_calendar_id`    VARCHAR(255) DEFAULT NULL,
    `google_api_key`        VARCHAR(255) DEFAULT NULL,
    `created_at`            TIMESTAMP    DEFAULT CURRENT_TIMESTAMP,
    `updated_at`            TIMESTAMP    DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CREATE INDEX idx_app_name ON settings (app_name); -- For fast username lookups

-- -----------------------------------------------------
-- Table `announcements`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `announcements`
(
    `announcement_id` INT AUTO_INCREMENT PRIMARY KEY,
    `title`           VARCHAR(100) NOT NULL,
    `body`            TEXT,
    `created_by`      INT,
    `created_date`    TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `modified_by`     INT,
    `modified_date`   TIMESTAMP,
    `pinned`          BOOLEAN   DEFAULT FALSE,
    `pinned_on`       TIMESTAMP,
    `active`          BOOLEAN   DEFAULT TRUE,
    `link`            VARCHAR(255)
);

-- -----------------------------------------------------
-- Table `activities`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `activities`
(
    `activity_id`        INT AUTO_INCREMENT PRIMARY KEY,
    `user_id`            INT          NOT NULL,
    `activity_type`      VARCHAR(255) NOT NULL,
    `object_id`          INT          NULL,
    `activity_timestamp` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`)
);

CREATE INDEX idx_activity_user_id ON activities (user_id);

-- -----------------------------------------------------
-- Table `rooms`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `rooms`
(
    `room_id`    INT AUTO_INCREMENT PRIMARY KEY,
    `name`       VARCHAR(100) NOT NULL,
    `is_private` BOOLEAN   DEFAULT FALSE
);

-- -----------------------------------------------------
-- Table `room_participants`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `room_participants`
(
    `room_id` INT NOT NULL,
    `user_id` INT NOT NULL,
    PRIMARY KEY (`room_id`, `user_id`),
    FOREIGN KEY (`room_id`) REFERENCES `rooms` (`room_id`),
    FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`)
);

-- -----------------------------------------------------
-- Table `ydocs`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `ydocs`
(
    `id`         INT AUTO_INCREMENT PRIMARY KEY,
    `name`       VARCHAR(255) NOT NULL UNIQUE,
    `data`       LONGBLOB NOT NULL,
    `createdAt`  DATETIME NOT NULL,
    `updatedAt`  DATETIME NOT NULL
);

-- -----------------------------------------------------
-- Table `events`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `events`
(
    `event_id`         INT AUTO_INCREMENT PRIMARY KEY,
    `title`            VARCHAR(100) NOT NULL,
    `description`      TEXT,
    `start_date`       DATETIME NOT NULL,
    `end_date`         DATETIME,
    `color`            VARCHAR(50),
    `reminder_minutes` INT,
    `is_private`       BOOLEAN DEFAULT FALSE,
    `user_id`          INT NOT NULL,
    `class_id`         INT DEFAULT NULL,
    FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`),
    FOREIGN KEY (`class_id`) REFERENCES `classes` (`class_id`)
);

-- -----------------------------------------------------
-- Table `book_logs`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `book_logs`
(
    `book_log_id` INT AUTO_INCREMENT PRIMARY KEY,
    `user_id`     INT          NOT NULL,
    `title`       VARCHAR(255) NOT NULL,
    `author`      VARCHAR(255) NOT NULL,
    `date_read`   DATE         NOT NULL,
    FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`)
);

CREATE INDEX idx_book_log_user_id ON book_logs (user_id);
CREATE INDEX idx_book_log_title ON book_logs (title);
CREATE INDEX idx_book_log_author ON book_logs (author);

-- -----------------------------------------------------
-- Table `messages`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `messages`
(
    `message_id` INT AUTO_INCREMENT PRIMARY KEY,
    `room_id`    INT       NOT NULL,
    `user_id`    INT       NOT NULL,
    `content`    TEXT      NOT NULL,
    `parent_message_id` INT DEFAULT NULL,
    `flagged`    BOOLEAN   DEFAULT FALSE,
    `triggered_keyword` VARCHAR(255) DEFAULT NULL,
    `is_deleted` BOOLEAN   DEFAULT FALSE,
    `is_edited`  BOOLEAN   DEFAULT FALSE,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (`room_id`) REFERENCES `rooms` (`room_id`),
    FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`),
    FOREIGN KEY (`parent_message_id`) REFERENCES `messages` (`message_id`)
);
