/* ===========================================================
   EnglishSpeak
   Sentence Library V2
   =========================================================== */

ALTER TABLE sentences
ADD COLUMN topic_id INT NULL AFTER vietnamese_meaning,
ADD COLUMN level_id INT NULL AFTER topic_id,
ADD COLUMN book_id INT NULL AFTER level_id,
ADD COLUMN tags VARCHAR(255) NULL AFTER book_id;

CREATE TABLE IF NOT EXISTS sentence_sets (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    description TEXT NULL,
    book_id INT NULL,
    level_id INT NULL,
    topic_id INT NULL,
    status ENUM('active','inactive') DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS sentence_set_sentences (
    id INT AUTO_INCREMENT PRIMARY KEY,
    sentence_set_id INT NOT NULL,
    sentence_id INT NOT NULL,
    display_order INT DEFAULT 1,

    UNIQUE KEY unique_sentence (
        sentence_set_id,
        sentence_id
    )
);