Untitled

 -- User table to store user information 
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,
    user_deleted     TINYINT(1) NOT NULL
);

-- Role table to store user roles 
CREATE TABLE role (
    role_id   INT AUTO_INCREMENT PRIMARY KEY,
    role_name VARCHAR(20) CHARACTER SET utf8mb4 NOT NULL
);

-- User Role table to store the relationship between users and roles 
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)
);

-- Gym Department table to store gym department information 
CREATE TABLE gym_department (
    gym_department_id INT PRIMARY KEY AUTO_INCREMENT,
    user_id           INT NOT NULL,
    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,
    FOREIGN KEY (user_id) REFERENCES user(user_id)
);


-- Gym Plan table to store gym plan information 
CREATE TABLE gym_plan (
    plan_id        INT PRIMARY KEY AUTO_INCREMENT,
    gym_deparment_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,
    plan_sold      INT NOT NULL,
    plan_active_time DATETIME NOT NULL,
    plan_buy_time    DATETIME NOT NULL,
    duration       INT NOT NULL,
    validity       INT NOT NULL,
    plan_status    INT NOT NULL,
    image_url      VARCHAR(255) CHARACTER SET utf8mb4,
    FOREIGN KEY (gym_deparment_id) REFERENCES gym_department(gym_department_id)
);

-- type table to 
CREATE TABLE type (
    type_id        INT PRIMARY KEY AUTO_INCREMENT,
    name           VARCHAR(255) CHARACTER SET utf8mb4 NOT NULL,
    description    VARCHAR(255) CHARACTER SET utf8mb4 NOT NULL
);

-- gym_plan_type table to 
CREATE TABLE gym_plan_type (
    gym_plan_type_id        INT PRIMARY KEY AUTO_INCREMENT,
	gym_plan_id             INT NOT NULL,
    type_id                 INT NOT NULL,
    FOREIGN KEY (gym_plan_id) REFERENCES gym_plan (plan_id),
    FOREIGN KEY (type_id)     REFERENCES type (type_id)
);

-- Gymer table to store gymer information 
CREATE TABLE gymer (
    gymer_id        INT PRIMARY KEY AUTO_INCREMENT,
    user_id         INT NOT NULL,
    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 (user_id) REFERENCES user(user_id),
    FOREIGN KEY (gym_id) REFERENCES gym_department(gym_department_id),
    FOREIGN KEY (plan_id) REFERENCES gym_plan(plan_id)
);
-- Gym Employee table to store gym employee information 
CREATE TABLE gym_employee (
    employee_id     INT PRIMARY KEY AUTO_INCREMENT,
    user_id         INT NOT NULL,
    gym_id          INT NOT NULL,
    name            VARCHAR(255) CHARACTER SET utf8mb4 NOT NULL,
    contact_number  VARCHAR(20) CHARACTER SET utf8mb4 NOT NULL,
    schedule        VARCHAR(255) CHARACTER SET utf8mb4,
    image_url       VARCHAR(255) CHARACTER SET utf8mb4,
    FOREIGN KEY (user_id) REFERENCES user(user_id),
    FOREIGN KEY (gym_id) REFERENCES gym_department(gym_department_id)
);

-- Shift table to store shift information 
CREATE TABLE shift (
    shift_id     INT PRIMARY KEY AUTO_INCREMENT,
    gymer_id     INT NOT NULL,
    gym_department_id INT NOT NULL,
    employee_id  INT NOT NULL,
    shift_name   VARCHAR(255) CHARACTER SET utf8mb4 NOT NULL,
    start_time   TIME NOT NULL,
    end_time     TIME NOT NULL,
    FOREIGN KEY (employee_id) REFERENCES gym_employee(employee_id),
	FOREIGN KEY (gym_department_id) REFERENCES gym_department(gym_department_id),
    FOREIGN KEY (gymer_id) REFERENCES gymer(gymer_id)
);

-- User Booking table to store user booking information 
CREATE TABLE gymer_booking (
    booking_id       INT PRIMARY KEY AUTO_INCREMENT,
    gymer_id         INT NOT NULL,
    gym_department_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(gymer_id),
    FOREIGN KEY (gym_department_id) REFERENCES gym_department(gym_department_id),
    FOREIGN KEY (shift_id) REFERENCES shift(shift_id)
);

-- User Cart table to store user cart information 
CREATE TABLE user_cart (
    cart_id       INT PRIMARY KEY AUTO_INCREMENT,
    user_id       INT NOT NULL,
    plan_id       INT NOT NULL,
    quantity      INT NOT NULL,
    FOREIGN KEY (user_id) REFERENCES user(user_id),
    FOREIGN KEY (plan_id) REFERENCES gym_plan(plan_id)
);

-- User Wallet table to store user wallet information 
CREATE TABLE user_wallet (
    wallet_id     INT PRIMARY KEY AUTO_INCREMENT,
    user_id       INT NOT NULL,
    balance       DECIMAL(10, 2) DEFAULT 0.0,
    FOREIGN KEY (user_id) REFERENCES user(user_id)
);

-- User Transaction table to store user transaction information 
CREATE TABLE user_transaction (
    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(wallet_id)
);
-- Order table to store order information 
CREATE TABLE `order` (
    order_id        INT PRIMARY KEY AUTO_INCREMENT,
    user_id         INT NOT NULL,
    plan_id         INT NOT NULL,
    order_time      DATETIME NOT NULL,
    order_status    INT NOT NULL,
    FOREIGN KEY (user_id) REFERENCES user(user_id),
    FOREIGN KEY (plan_id) REFERENCES gym_plan(plan_id)
);

-- Order Plan Detail table to store order plan details
CREATE TABLE order_plan_detail (
    order_detail_id INT PRIMARY KEY AUTO_INCREMENT,
    order_id        INT NOT NULL,
    plan_id         INT NOT NULL,
    quantity        INT NOT NULL,
    price           DECIMAL(10, 2) NOT NULL,
    FOREIGN KEY (order_id) REFERENCES `order`(order_id),
    FOREIGN KEY (plan_id) REFERENCES gym_plan(plan_id)
);

-- User Inventory table to store user inventory information 
CREATE TABLE user_inventory (
    inventory_id   INT PRIMARY KEY AUTO_INCREMENT,
    user_id       INT NOT NULL,
    plan_id        INT NOT NULL,
    FOREIGN KEY (user_id) REFERENCES user(user_id),
    FOREIGN KEY (plan_id) REFERENCES gym_plan(plan_id)
);

-- History table to store user history information 
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)
);

-- User Feedback table to store user feedback information 
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,
    response       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)
);