-- 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,
	icon VARCHAR(255) NULL, 
    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,
	score float NOT NULL,
	translateResult varchar(1000) NOT NULL,
	correctWords text NULL,
	incorrectWords text NULL,
	duration INT NOT NULL,

	feedback VARCHAR(10000) 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(10000) NULL,
	systemSuggestion VARCHAR(10000) 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,
	comments VARCHAR(10000) 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,
	comments VARCHAR(10000) 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(10000) NOT NULL,
	times INT NOT NULL,
    totalStroke INT NOT NULL,
    totalMistake INT NOT NULL,
	score FLOAT NOT NULL,
    comments VARCHAR(10000) NOT NULL,
	duration INT NOT NULL,
	feedback VARCHAR(10000) 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,
	duration INT NOT NULL,
    audio varchar(255) NULL,
	score float NOT NULL,
	translateResult varchar(1000) NOT NULL,
	correctWords text NULL,
	incorrectWords text NULL,
	feedback VARCHAR(1000) 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(10000) NOT NULL,
	times INT NOT NULL,
	duration INT NOT NULL,
    totalStroke INT NOT NULL,
    totalMistake INT NOT NULL,
	score FLOAT NOT NULL,
    comments VARCHAR(10000) NOT NULL,
	feedback VARCHAR(10000) 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(10000) 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 user_time (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id VARCHAR(10) NOT NULL,
	dateOfVisit DATE NOT NULL,
    start_timestamp TIME DEFAULT CURRENT_TIMESTAMP,
    end_timestamp TIMESTAMP NULL,
    duration INT DEFAULT 0,
    FOREIGN KEY (user_id) REFERENCES children(accountID) ON DELETE CASCADE
);


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,
	points int(11) NOT NULL DEFAULT 0,
	last_updated timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(), 
    PRIMARY KEY (userID),
	FOREIGN KEY (userID) REFERENCES CHILDREN(accountID)
)

CREATE TABLE LoginTrait(
	id INT AUTO_INCREMENT PRIMARY KEY,
	userid_child varchar(10) not null,
	dateOfLogin date not null,
	isLogin varchar(5) not null default 'false',
	gotPrize varchar(5) not null default 'false',
	FOREIGN KEY (userID_child) REFERENCES children(accountID) ON DELETE CASCADE
)

CREATE TABLE user_items (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id VARCHAR(10) NOT NULL,
    item_id INT NOT NULL,
    item_type VARCHAR(50) NOT NULL,
    item_image VARCHAR(255) NOT NULL,
	purchased TINYINT(1) NULL, 
	selected_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES children(accountID) ON DELETE CASCADE
);