/*1-User_db*/
CREATE TABLE tmt_User_db (
    UserID INT PRIMARY KEY AUTO_INCREMENT,
    UserName VARCHAR(50) NOT NULL,
    PasswordHash VARCHAR(255) NOT NULL,
    Email VARCHAR(100) NOT NULL,
    Gender CHAR(1),
    Age INT CHECK (Age >= 0),
    Country VARCHAR(50),
    profile_image VARCHAR(255)/*1*/
    target_language VARCHAR(50),
    lang_level INT CHECK (lang_level >= 1 AND lang_level <= 5),
    delete_ku INT DEFAULT 0
);

/*2-Preferences*/

CREATE TABLE tmt_Preferences (
    PrefID INT PRIMARY KEY AUTO_INCREMENT,
    UserID INT NOT NULL,
    shumi VARCHAR(255),
    Area VARCHAR(100),  /*1 location >Area */
    AgeRangeStart INT CHECK (AgeRangeStart >= 0),
    AgeRangeEnd INT CHECK (AgeRangeEnd >= 0),
    target_language VARCHAR(50),
    lang_level INT CHECK (lang_level >= 1 AND lang_level <= 5),
    delete_ku INT DEFAULT 0,
    FOREIGN KEY (UserID) REFERENCES tmt_User_db(UserID)
);

/*3-Matches*/
CREATE TABLE tmt_Matches (
    MatchID INT PRIMARY KEY AUTO_INCREMENT,
    UserID1 INT NOT NULL,
    UserID2 INT NOT NULL,
    MatchDate TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    delete_ku INT DEFAULT 0,
    FOREIGN KEY (UserID1) REFERENCES tmt_User_db(UserID),
    FOREIGN KEY (UserID2) REFERENCES tmt_User_db(UserID)
);

/*4-Chats*/
CREATE TABLE tmt_Chats (
    ChatID INT PRIMARY KEY AUTO_INCREMENT,
    SenderID INT NOT NULL,
    ReceiverID INT NOT NULL,
    Mes TEXT NOT NULL,/*1 Message>Mes*/
    SentDate DATETIME DEFAULT CURRENT_TIMESTAMP,
    delete_ku INT DEFAULT 0,
    FOREIGN KEY (SenderID) REFERENCES tmt_User_db(UserID),
    FOREIGN KEY (ReceiverID) REFERENCES tmt_User_db(UserID)
);

/*5-UserGroups*/
CREATE TABLE tmt_UserGroups (
    UserGroupID INT PRIMARY KEY AUTO_INCREMENT,
    UserID INT NOT NULL,
    GroupID INT NOT NULL,
    FOREIGN KEY (UserID) REFERENCES tmt_User_db(UserID),
    FOREIGN KEY (GroupID) REFERENCES tmt_Groups(GroupID)
);


/*6-Groups*/
CREATE TABLE tmt_Groups (
    GroupID INT PRIMARY KEY AUTO_INCREMENT,
    GroupName VARCHAR(100) NOT NULL,
    CreatedDate DATETIME DEFAULT CURRENT_TIMESTAMP
);

/*7-Hobby_MST*/趣味
CREATE TABLE tmt_Hobby_MST (
    Hobby_NO INT PRIMARY KEY,
    Hobby VARCHAR(255)
);

INSERT INTO tmt_Hobby_MST (Hobby_NO, Hobby) VALUES (1, 'Baseball（野球）');


/*8-PersonalDictionary*/
CREATE TABLE tmt_PersonalDictionary (
    EntryID INT PRIMARY KEY AUTO_INCREMENT,
    UserID INT NOT NULL,
    word VARCHAR(100) NOT NULL,
    def_imi VARCHAR(100),
    insert_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    update_at DATETIME ON UPDATE CURRENT_TIMESTAMP,
    delete_ku INT DEFAULT 0,
    FOREIGN KEY (UserID) REFERENCES tmt_User_db(UserID)
);
ALTER TABLE tmt_PersonalDictionary
CHANGE definition def_inition VARCHAR(100);

/*9-Flashcards*/
CREATE TABLE tmt_Flashcards (
    FlashcardID INT PRIMARY KEY AUTO_INCREMENT,
    UserID INT NOT NULL,
    word VARCHAR(100) NOT NULL,
    CorrectAnswers VARCHAR DEFAULT 0,/*1 int>varchar*/
    insert_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    update_at DATETIME ON UPDATE CURRENT_TIMESTAMP,
    delete_ku INT DEFAULT 0,
    FOREIGN KEY (UserID) REFERENCES tmt_User_db(UserID)
);

/*10-UserScores*/
CREATE TABLE tmt_UserScores (
    ScoreID INT PRIMARY KEY AUTO_INCREMENT,
    UserID INT NOT NULL,
    Score INT NOT NULL,
    insert_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (UserID) REFERENCES tmt_User_db(UserID)
);

