-- skills type
CREATE TABLE skill_types (
    id CHAR(36) PRIMARY KEY,
    title VARCHAR(100) NOT NULL,
    sorting INT DEFAULT 0,
    createdAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
-- insert data
CREATE TABLE skill_types (
    id CHAR(36) PRIMARY KEY,
    title VARCHAR(100) NOT NULL,
    sorting INT DEFAULT 0,
    createdAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
-- skills table
CREATE TABLE skills (
    id CHAR(36) PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    courseDetails TEXT,
    skillTypesId CHAR(36) NOT NULL,

    totalLession INT DEFAULT 0,
    totalPrice DECIMAL(10,2) DEFAULT 0,
    discountPricePercentage DECIMAL(5,2) DEFAULT 0,
    totalHour DECIMAL(10,2) DEFAULT 0,

    galleries JSON,
    skillDetails JSON,

    sorting INT DEFAULT 0,

    createdAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    INDEX idx_skill_type (skillTypesId),

    CONSTRAINT fk_skills_skill_type
        FOREIGN KEY (skillTypesId)
        REFERENCES skill_types(id)
        ON DELETE RESTRICT
        ON UPDATE CASCADE
);

CREATE TABLE carts (
    id CHAR(36) PRIMARY KEY,
    userId CHAR(36) NOT NULL UNIQUE,

    createdAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        ON UPDATE CURRENT_TIMESTAMP,

    INDEX idx_cart_user (userId)
);
CREATE TABLE cart_items (
    id CHAR(36) PRIMARY KEY,
    cartId CHAR(36) NOT NULL,
    skillId CHAR(36) NOT NULL,

    createdAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        ON UPDATE CURRENT_TIMESTAMP,

    UNIQUE KEY unique_cart_skill (cartId, skillId),

    INDEX idx_cart_items_cart (cartId),
    INDEX idx_cart_items_skill (skillId),

    CONSTRAINT fk_cart_items_cart
        FOREIGN KEY (cartId)
        REFERENCES carts(id)
        ON DELETE CASCADE
        ON UPDATE CASCADE,

    CONSTRAINT fk_cart_items_skill
        FOREIGN KEY (skillId)
        REFERENCES skills(id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

CREATE TABLE checkouts (
    id CHAR(36) PRIMARY KEY,
    userId CHAR(36) NOT NULL,

    totalAmount DECIMAL(10,2) DEFAULT 0,
    totalDiscount DECIMAL(10,2) DEFAULT 0,
    payableAmount DECIMAL(10,2) DEFAULT 0,

    status VARCHAR(50) DEFAULT 'PENDING',

    createdAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        ON UPDATE CURRENT_TIMESTAMP,

    INDEX idx_checkout_user (userId)
);

CREATE TABLE checkout_details (
    id CHAR(36) PRIMARY KEY,
    checkoutId CHAR(36) NOT NULL,
    skillId CHAR(36) NOT NULL,

    coursePrice DECIMAL(10,2) DEFAULT 0,
    discountPrice DECIMAL(10,2) DEFAULT 0,
    discountPercentage DECIMAL(5,2) DEFAULT 0,

    createdAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    INDEX idx_checkout_detail_checkout (checkoutId),
    INDEX idx_checkout_detail_skill (skillId),

    CONSTRAINT fk_checkout_details_checkout
        FOREIGN KEY (checkoutId)
        REFERENCES checkouts(id)
        ON DELETE CASCADE
        ON UPDATE CASCADE,

    CONSTRAINT fk_checkout_details_skill
        FOREIGN KEY (skillId)
        REFERENCES skills(id)
        ON DELETE RESTRICT
        ON UPDATE CASCADE
);