-- Create the database
CREATE DATABASE user_auth;

-- Use the new database
USE user_auth;

-- Create the user_roles table first
CREATE TABLE user_roles (
    id CHAR(1) NOT NULL PRIMARY KEY,
    role_name VARCHAR(50) NOT NULL UNIQUE
);

-- Insert roles into user_roles table
INSERT INTO user_roles (id, role_name) VALUES 
('c', 'child'), 
('p', 'parent');

-- Create the parents table
CREATE TABLE parents (
    id INT(11) AUTO_INCREMENT PRIMARY KEY,
    accountID VARCHAR(10) NOT NULL UNIQUE, -- Unique ID for parent accounts
    username VARCHAR(50) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    emailAdd VARCHAR(100) NOT NULL UNIQUE,
    role_id CHAR(1) NOT NULL,
    FOREIGN KEY (role_id) REFERENCES user_roles(id) ON DELETE CASCADE
    -- Removed children_linked for now, to avoid circular reference
);

-- Create the children table
CREATE TABLE children (
    id INT(11) AUTO_INCREMENT PRIMARY KEY,
    accountID VARCHAR(10) NOT NULL UNIQUE, -- Unique ID for child accounts
    username VARCHAR(50) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    role_id CHAR(1) NOT NULL,    
	FOREIGN KEY (role_id) REFERENCES user_roles(id) ON DELETE CASCADE
);

-- Create the profiles table
CREATE TABLE profiles (
    id INT(11) AUTO_INCREMENT PRIMARY KEY,
    user_id VARCHAR(10) NOT NULL, -- ID of the user (parent or child)
    user_type ENUM('parent', 'child') NOT NULL, -- Indicates whether it's a parent or child
    sex ENUM('male', 'female', 'other') NOT NULL,
    email VARCHAR(100) NULL, -- Optional email
    date_of_birth DATE,
    UNIQUE(user_id, user_type) -- Ensures unique combinations of user_id and user_type
);


CREATE TABLE HWRecord (
    recordID INT AUTO_INCREMENT NOT NULL,
    userID VARCHAR(10) NOT NULL,
    recordTime DATETIME NOT NULL,
    wordSearched VARCHAR(20000) NOT NULL,
    times INT NOT NULL,
    totalStroke INT NOT NULL,
    totalMistake INT NOT NULL,
	score FLOAT NOT NULL,
    comments VARCHAR(10000) NOT NULL,
    PRIMARY KEY (recordID),
    FOREIGN KEY (userID) REFERENCES children(accountID)
);


CREATE TABLE AssignedHW(
	assignmentID INT AUTO_INCREMENT NOT NULL,
	userID_parent VARCHAR(10) NOT NULL,
	userID_child VARCHAR(10) NOT NULL,
	hwType VARCHAR(50) NOT NULL,
	characters VARCHAR(1000) NULL,
	systemSuggestion VARCHAR(1000) NULL,
	times INT NOT NULL,
	assign_time DATETIME NOT NULL,
	duration INT NOT NULL,
	startDate DATETIME NOT NULL,
	dueDate DATETIME NOT NULL,
	PRIMARY KEY (assignmentID),
	FOREIGN KEY (userID_child) REFERENCES children(accountID),
	FOREIGN KEY (userID_parent) REFERENCES parents(accountID)
);



CREATE TABLE TTSExerciseRecord(
	recordID INT AUTO_INCREMENT NOT NULL,
	userID VARCHAR(10) NOT NULL,
	assignmentID INT NOT NULL,
	recordTime DATETIME NOT NULL,
	times INT NOT NULL,
	audio varchar(255) NULL,
	PRIMARY KEY (recordID),
    	FOREIGN KEY (userID) REFERENCES children(accountID),
	FOREIGN KEY (assignmentID) REFERENCES AssignedHW(assignmentID)
);

CREATE TABLE TTSRecord(
	recordID INT AUTO_INCREMENT NOT NULL,
	userID_child VARCHAR(10) NULL,
	assignmentID INT NOT NULL,
	recordTime DATETIME NOT NULL,
	wordScanned VARCHAR(10000) NOT NULL,
	audio varchar(255) NULL,
	PRIMARY KEY (recordID),
    	FOREIGN KEY (userID_child) REFERENCES children(accountID),
	FOREIGN KEY (assignmentID) REFERENCES AssignedHW(assignmentID)
);

CREATE TABLE AssignedQuiz(
	assignmentID INT AUTO_INCREMENT NOT NULL,
	userID_parent VARCHAR(10) NOT NULL,
	userID_child VARCHAR(10) NOT NULL,
	quizType VARCHAR(50) NOT NULL,
	characters VARCHAR(1000) NULL,
	systemSuggestion VARCHAR(1000) NULL,
	times INT NOT NULL,
	assign_time DATETIME NOT NULL,
	duration INT NOT NULL,
	startDate DATETIME NOT NULL,
	dueDate DATETIME NOT NULL,
	PRIMARY KEY (assignmentID),
	FOREIGN KEY (userID_child) REFERENCES children(accountID),
	FOREIGN KEY (userID_parent) REFERENCES parents(accountID)
);

CREATE TABLE QuizAssignment(
	quizID INT AUTO_INCREMENT NOT NULL,
	assignmentID INT NOT NULL,
	progress ENUM('Incomplete', 'In Progress', 'Complete') NOT NULL,
	PRIMARY KEY (quizID),
	FOREIGN KEY (assignmentID) REFERENCES AssignedQuiz(assignmentID)
);

CREATE TABLE HWAssignment(
	hwID INT AUTO_INCREMENT NOT NULL,
	assignmentID INT NOT NULL,
	progress ENUM('Incomplete', 'In Progress', 'Complete') NOT NULL,
	PRIMARY KEY (hwID),
	FOREIGN KEY (assignmentID) REFERENCES AssignedHW(assignmentID)
);

CREATE TABLE HZQuizResult(
	resultID INT AUTO_INCREMENT NOT NULL,
	userID_child VARCHAR(10) NOT NULL,
	assignmentID INT NOT NULL,
	recordTime DATETIME NOT NULL,
	characters VARCHAR(1000) NOT NULL,
	times INT NOT NULL,
    totalStroke INT NOT NULL,
    totalMistake INT NOT NULL,
	score FLOAT NOT NULL,
    comments VARCHAR(1000) NOT NULL,
	PRIMARY KEY (resultID),
	FOREIGN KEY (userID_child) REFERENCES children(accountID),
	FOREIGN KEY (assignmentID) REFERENCES AssignedQuiz(assignmentID)
);

CREATE TABLE TTSQuizResult(
	resultID INT AUTO_INCREMENT NOT NULL,
	userID_child VARCHAR(10) NOT NULL,
	assignmentID INT NOT NULL,
	recordTime DATETIME NOT NULL,
	times INT NOT NULL,
    audio varchar(255) NULL,
	score FLOAT NOT NULL,
	translateResult VARCHAR(1000) NOT NULL,
	correctWords text NULL,
	incorrectWords text NULL,
	PRIMARY KEY (resultID),
	FOREIGN KEY (userID_child) REFERENCES children(accountID),
	FOREIGN KEY (assignmentID) REFERENCES AssignedQuiz(assignmentID)
);


CREATE TABLE HWExerciseRecord (
    resultID INT AUTO_INCREMENT NOT NULL,
    userID_child VARCHAR(10) NOT NULL,
    assignmentID INT NOT NULL,
	recordTime DATETIME NOT NULL,
	characters VARCHAR(1000) NOT NULL,
	times INT NOT NULL,
    totalStroke INT NOT NULL,
    totalMistake INT NOT NULL,
	score FLOAT NOT NULL,
    comments VARCHAR(1000) NOT NULL,
	PRIMARY KEY (resultID),
	FOREIGN KEY (userID_child) REFERENCES children(accountID),
	FOREIGN KEY (assignmentID) REFERENCES AssignedHW(assignmentID)
);

CREATE TABLE HWResult(
	resultID INT AUTO_INCREMENT NOT NULL,
	userID_child VARCHAR(10) NOT NULL,
	characters VARCHAR(1000) NOT NULL,
	progress ENUM('Incomplete', 'In Progress', 'Complete') NOT NULL,
	score FLOAT NOT NULL,
	finish_time DATETIME NOT NULL,
	PRIMARY KEY (resultID),
	FOREIGN KEY (userID_child) REFERENCES children(accountID)
);


CREATE TABLE linkedAccounts (
    actionID INT(11) AUTO_INCREMENT PRIMARY KEY,
    parent_id VARCHAR(10) NOT NULL,
    child_id VARCHAR(10) NOT NULL,
    FOREIGN KEY (parent_id) REFERENCES parents(accountID) ON DELETE CASCADE,
    FOREIGN KEY (child_id) REFERENCES children(accountID) ON DELETE CASCADE
);

CREATE VIEW ParentChildView AS
SELECT 
    p.id AS parent_id,
    p.accountID AS parent_accountID,
    p.username AS parent_username,
    c.id AS child_id,
    c.accountID AS child_accountID,
    c.username AS child_username
FROM 
    parents p
JOIN 
    linkedAccounts la ON p.id = la.parent_id
JOIN 
    children c ON la.child_id = c.id;


CREATE TABLE SystemSuggestion(
	suggestionID INT AUTO_INCREMENT PRIMARY KEY,
	taskType VARCHAR(200) NOT NULL,
	taskLevel VARCHAR(10) NOT NULL,
	taskDescription VARCHAR(600) NOT NULL,
	taskContent VARCHAR(1000) NOT NULL
);


CREATE TABLE LinkingRequests (
    id INT AUTO_INCREMENT PRIMARY KEY,
    requesting_account_id INT,
    target_account_id INT,
    status ENUM('pending', 'accepted', 'declined') DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE notifications (
    id INT AUTO_INCREMENT PRIMARY KEY,   
    userID_child VARCHAR(10) NOT NULL,    
    userID_parent VARCHAR(10) NOT NULL,  
    assignmentID INT NOT NULL,     
    message TEXT NOT NULL,    
    is_read TINYINT DEFAULT 0,                -- 0 unread，1read
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, 
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (userID_child) REFERENCES children(accountID) ON DELETE CASCADE, 
    FOREIGN KEY (userID_parent) REFERENCES parents(accountID) ON DELETE CASCADE 
);

CREATE TABLE Points (
    userID VARCHAR(10) NOT NULL PRIMARY KEY,
    points INT NOT NULL DEFAULT 0,
    last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (userID) REFERENCES children(accountID) ON DELETE CASCADE
);

DELIMITER $$

CREATE TRIGGER after_child_insert
AFTER INSERT ON children
FOR EACH ROW
BEGIN
    INSERT INTO Points (userID, points)
    VALUES (NEW.accountID, 0);
END$$

DELIMITER ;

-- Create Missions table if not exists
CREATE TABLE IF NOT EXISTS `Missions` (
  `missionID` int(11) NOT NULL AUTO_INCREMENT,
  `missionName` varchar(100) NOT NULL,
  `missionDescription` varchar(255) DEFAULT NULL,
  `missionType` enum('daily','weekly') NOT NULL,
  `pointReward` int(11) NOT NULL DEFAULT 0,
  `isActive` tinyint(1) NOT NULL DEFAULT 1,
  `requiresParentMission` int(11) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`missionID`),
  KEY `requiresParentMission` (`requiresParentMission`),
  CONSTRAINT `Missions_ibfk_1` FOREIGN KEY (`requiresParentMission`) REFERENCES `Missions` (`missionID`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Insert basic mission data
INSERT INTO `Missions` (`missionID`, `missionName`, `missionType`, `pointReward`, `isActive`, `requiresParentMission`) VALUES
(1, 'Get the Login Bonus', 'daily', 0, 1, NULL),
(2, 'Complete Daily Request', 'daily', 20, 1, NULL),
(3, 'Complete All Daily Mission :D', 'daily', 50, 1, NULL),
(4, 'Get the 7 Days Login Bonus', 'weekly', 0, 1, NULL),
(5, 'Complete Week Challenge', 'weekly', 50, 1, NULL),
(6, 'Complete All Mission :D', 'weekly', 100, 1, NULL);

-- Create UserMissions table if not exists
CREATE TABLE IF NOT EXISTS `UserMissions` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `userID` varchar(10) NOT NULL,
  `missionID` int(11) NOT NULL,
  `isCompleted` tinyint(1) NOT NULL DEFAULT 0,
  `completedAt` datetime DEFAULT NULL,
  `resetAt` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `user_mission` (`userID`,`missionID`),
  KEY `missionID` (`missionID`),
  CONSTRAINT `UserMissions_ibfk_1` FOREIGN KEY (`userID`) REFERENCES `children` (`accountID`) ON DELETE CASCADE,
  CONSTRAINT `UserMissions_ibfk_2` FOREIGN KEY (`missionID`) REFERENCES `Missions` (`missionID`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Create UserMissionProgress table if not exists
CREATE TABLE IF NOT EXISTS `UserMissionProgress` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `userID` varchar(10) NOT NULL,
  `dailyCompletionDate` date DEFAULT NULL,
  `dailyStreak` int(11) NOT NULL DEFAULT 0,
  `weeklyCompletionDate` date DEFAULT NULL,
  `lastWeeklyReset` date DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `userID` (`userID`),
  CONSTRAINT `UserMissionProgress_ibfk_1` FOREIGN KEY (`userID`) REFERENCES `children` (`accountID`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE IF NOT EXISTS`UnlockedItems` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `userID` varchar(10) NOT NULL,
  `itemType` varchar(50) NOT NULL,
  `itemID` varchar(50) NOT NULL,
  `unlockDate` timestamp NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `user_item` (`userID`,`itemType`,`itemID`),
  CONSTRAINT `UnlockedItems_ibfk_1` FOREIGN KEY (`userID`) REFERENCES `children` (`accountID`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE IF NOT EXISTS `AssistantIcons` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `iconID` varchar(50) NOT NULL,
  `iconName` varchar(100) NOT NULL,
  `price` int(11) NOT NULL,
  `iconImage` varchar(200) DEFAULT NULL,
  `description` text DEFAULT NULL,
  `isActive` tinyint(1) NOT NULL DEFAULT 1,
  `createdAt` timestamp NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `iconID` (`iconID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE IF NOT EXISTS `UserSettings` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `userID` varchar(10) NOT NULL,
  `setting_key` varchar(50) NOT NULL,
  `setting_value` text NOT NULL,
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `user_setting` (`userID`,`setting_key`),
  CONSTRAINT `UserSettings_ibfk_1` FOREIGN KEY (`userID`) REFERENCES `children` (`accountID`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Create SpeakingRequests, SpeakingRequestAssignments, SpeakingExerciseRecords, and SpeakingResults tables--
CREATE TABLE IF NOT EXISTS `SpeakingRequests` (
  `requestID` INT AUTO_INCREMENT NOT NULL,
  `userID_child` VARCHAR(10) NOT NULL,
  `userID_parent` VARCHAR(10) NOT NULL,
  `requestType` VARCHAR(50) NOT NULL,
  `content` TEXT NOT NULL,
  `times` INT NOT NULL DEFAULT 1,
  `assign_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `duration` INT NOT NULL DEFAULT 30,
  `startDate` DATETIME NOT NULL,
  `dueDate` DATETIME NOT NULL,
  `status` ENUM('Upcoming', 'Active', 'Expired', 'Completed') NOT NULL DEFAULT 'Upcoming',
  PRIMARY KEY (`requestID`),
  FOREIGN KEY (`userID_child`) REFERENCES `children`(`accountID`) ON DELETE CASCADE,
  FOREIGN KEY (`userID_parent`) REFERENCES `parents`(`accountID`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE IF NOT EXISTS `SpeakingRequestAssignments` (
  `assignmentID` INT AUTO_INCREMENT NOT NULL,
  `requestID` INT NOT NULL,
  `progress` ENUM('Incomplete', 'In Progress', 'Complete') NOT NULL DEFAULT 'Incomplete',
  `last_updated` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`assignmentID`),
  FOREIGN KEY (`requestID`) REFERENCES `SpeakingRequests`(`requestID`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE IF NOT EXISTS `SpeakingExerciseRecords` (
  `recordID` INT AUTO_INCREMENT NOT NULL,
  `userID_child` VARCHAR(10) NOT NULL,
  `requestID` INT NOT NULL,
  `recordTime` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `times` INT NOT NULL DEFAULT 1,
  `audio` VARCHAR(255) NULL,
  `score` FLOAT NULL,
  `comments` VARCHAR(1000) NULL,
  PRIMARY KEY (`recordID`),
  FOREIGN KEY (`userID_child`) REFERENCES `children`(`accountID`) ON DELETE CASCADE,
  FOREIGN KEY (`requestID`) REFERENCES `SpeakingRequests`(`requestID`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE IF NOT EXISTS `SpeakingResults` (
  `resultID` INT AUTO_INCREMENT NOT NULL,
  `userID_child` VARCHAR(10) NOT NULL,
  `requestID` INT NOT NULL,
  `recordTime` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `audio` VARCHAR(255) NULL,
  `score` FLOAT NOT NULL DEFAULT 0,
  `transcription` TEXT NULL,
  `correctWords` TEXT NULL,
  `incorrectWords` TEXT NULL,
  `comments` VARCHAR(1000) NULL,
  PRIMARY KEY (`resultID`),
  FOREIGN KEY (`userID_child`) REFERENCES `children`(`accountID`) ON DELETE CASCADE,
  FOREIGN KEY (`requestID`) REFERENCES `SpeakingRequests`(`requestID`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

DELIMITER $$

CREATE TRIGGER IF NOT EXISTS update_speaking_request_status_to_active
BEFORE UPDATE ON SpeakingRequests
FOR EACH ROW
BEGIN
    IF NEW.status = 'Upcoming' AND NEW.startDate <= NOW() AND NEW.dueDate >= NOW() THEN
        SET NEW.status = 'Active';
    END IF;
END $$

CREATE TRIGGER IF NOT EXISTS update_speaking_request_status_to_expired
BEFORE UPDATE ON SpeakingRequests
FOR EACH ROW
BEGIN
    IF NEW.status = 'Active' AND NEW.dueDate < NOW() THEN
        SET NEW.status = 'Expired';
    END IF;
END $$

DELIMITER ;

DELIMITER $$

CREATE EVENT IF NOT EXISTS update_speaking_requests_status
ON SCHEDULE EVERY 1 HOUR
DO
BEGIN
    UPDATE SpeakingRequests
    SET status = 'Active'
    WHERE status = 'Upcoming' 
    AND startDate <= NOW() 
    AND dueDate >= NOW();
    
    UPDATE SpeakingRequests
    SET status = 'Expired'
    WHERE status != 'Completed' 
    AND dueDate < NOW();
END $$

DELIMITER ;

SET GLOBAL event_scheduler = ON;


