database handmerge

-- Bảng lưu trữ thông tin người dùng
CREATE TABLE user (
    user_id          INT AUTO_INCREMENT PRIMARY KEY,
    user_account     VARCHAR(250) CHARACTER SET utf8mb4 NOT NULL,
    user_password    VARCHAR(100) CHARACTER SET utf8mb4 NOT NULL,
    user_create_time VARCHAR(20) NOT NULL,
    balance 		 DECIMAL(10, 2) DEFAULT 0.0,
    user_deleted     TINYINT(1) NOT NULL
);

-- Bảng lưu trữ các vai trò của người dùng
CREATE TABLE role (
    role_id   INT AUTO_INCREMENT PRIMARY KEY,
    role_name VARCHAR(20) CHARACTER SET utf8mb4 NOT NULL
);

-- Bảng lưu trữ quan hệ giữa người dùng và vai trò
CREATE TABLE user_role (
    user_role_id INT AUTO_INCREMENT PRIMARY KEY,
    user_id      INT NOT NULL,
    role_id      INT NOT NULL,
    FOREIGN KEY (user_id) REFERENCES user (user_id),
    FOREIGN KEY (role_id) REFERENCES role (role_id)
);

-- Create the gym_department table
CREATE TABLE gym_department (
    gym_department_id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) CHARACTER SET utf8mb4 NOT NULL,
    address VARCHAR(255) CHARACTER SET utf8mb4 NOT NULL,
    contact_number VARCHAR(20) CHARACTER SET utf8mb4 NOT NULL,
    logo_url VARCHAR(255) CHARACTER SET utf8mb4,
    opening_hours VARCHAR(255) CHARACTER SET utf8mb4,
    image_url VARCHAR(255) CHARACTER SET utf8mb4,
    description TEXT CHARACTER SET utf8mb4
);

-- Create the gymer table
CREATE TABLE gymer (
    gymer_id INT PRIMARY KEY AUTO_INCREMENT,
    gym_id INT NOT NULL,
    image_url VARCHAR(255) CHARACTER SET utf8mb4,
    name VARCHAR(255) CHARACTER SET utf8mb4 NOT NULL,
    contact_number VARCHAR(20) CHARACTER SET utf8mb4 NOT NULL,
    plan_id INT,
    check_in_time DATETIME,
    check_out_time DATETIME,
    FOREIGN KEY (gym_id) REFERENCES Gym(id),
    FOREIGN KEY (plan_id) REFERENCES GymPlan(id)
);

-- Create the shift table
CREATE TABLE shift (
    id INT PRIMARY KEY AUTO_INCREMENT,
    employee_id INT NOT NULL,
    start_time TIME NOT NULL,
    end_time TIME NOT NULL,
    FOREIGN KEY (employee_id) REFERENCES Employee(id)
);

-- Create the gym_plan table
CREATE TABLE gym_plan (
    id INT PRIMARY KEY AUTO_INCREMENT,
    gym_id INT NOT NULL,
    name VARCHAR(255) CHARACTER SET utf8mb4 NOT NULL,
    description VARCHAR(255) CHARACTER SET utf8mb4 NOT NULL,
    price DECIMAL(10, 2) NOT NULL,
    duration INT NOT NULL,
    validity INT NOT NULL,
    gym_plan_status INT NOT NULL
    image_url VARCHAR(255) CHARACTER SET utf8mb4,
    FOREIGN KEY (gym_id) REFERENCES Gym(id)
);

-- Create the gym_employee table
CREATE TABLE gym_mployee (
    id INT PRIMARY KEY AUTO_INCREMENT,
    gym_id INT NOT NULL,
    name VARCHAR(255) CHARACTER SET utf8mb4 NOT NULL,
    contact_number VARCHAR(20) CHARACTER SET utf8mb4 NOT NULL,
    role VARCHAR(255) CHARACTER SET utf8mb4 NOT NULL,
    schedule VARCHAR(255) CHARACTER SET utf8mb4,
    image_url VARCHAR(255) CHARACTER SET utf8mb4,
    FOREIGN KEY (gym_id) REFERENCES Gym(id)
);


CREATE TABLE user_booking (
    booking_id INT PRIMARY KEY AUTO_INCREMENT,
    gymer_id INT NOT NULL,
    shift_id INT NOT NULL,
    booking_time DATETIME NOT NULL,
    booking_status INT NOT NULL,
    FOREIGN KEY (gymer_id) REFERENCES Gymer(id),
    FOREIGN KEY (shift_id) REFERENCES Shift(id)
);

CREATE TABLE user_cart
(
    user_cart_id INT PRIMARY KEY AUTO_INCREMENT,
    gymer_id INT NOT NULL,
    plan_id INT NOT NULL,
    quantity INT NOT NULL,
    FOREIGN KEY (gymer_id) REFERENCES Gymer(id),
    FOREIGN KEY (plan_id) REFERENCES GymPlan(id)
);

-- Create the user_wallet table
CREATE TABLE user_wallet (
    user_wallet_id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    balance DECIMAL(10, 2) NOT NULL,
    FOREIGN KEY (user_id) REFERENCES user(user_id)
);

-- Create the user_transaction table
CREATE TABLE user_transaction (
    user_transaction_id INT PRIMARY KEY AUTO_INCREMENT,
    wallet_id INT NOT NULL,
    amount DECIMAL(10, 2) NOT NULL,
    transaction_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (wallet_id) REFERENCES user_wallet(user_wallet_id)
);

-- Create the user_inventory table
CREATE TABLE user_inventory (
    user_inventory_id INT PRIMARY KEY AUTO_INCREMENT,
    gymer_id INT NOT NULL,
    plan_id INT NOT NULL,
    FOREIGN KEY (gymer_id) REFERENCES gymer(gymer_id),
    FOREIGN KEY (plan_id) REFERENCES gym_plan(id)
);

CREATE TABLE history
(
    history_id     INT AUTO_INCREMENT PRIMARY KEY,
    user_id        INT NOT NULL,
    action_type    VARCHAR(20) CHARACTER SET utf8mb4 NOT NULL,
    action_details TEXT CHARACTER SET utf8mb4,
    action_time    DATETIME NOT NULL,
    FOREIGN KEY (user_id) REFERENCES user (user_id)
);
		
CREATE TABLE user_feedback
(
    feedback_id   	INT AUTO_INCREMENT PRIMARY KEY,
    user_id       	INT NOT NULL,
    department_id 	INT NOT NULL,
    rating        	INT NOT NULL,
    comments      	TEXT CHARACTER SET utf8mb4,
    feedback_time 	DATETIME NOT NULL,
    feedback_status INT NOT NULL,
    FOREIGN KEY (user_id) REFERENCES user (user_id),
    FOREIGN KEY (department_id) REFERENCES gym_department (gym_department_id)
);