Untitled

mail@pastecode.io avatar
unknown
plain_text
18 days ago
16 kB
6
Indexable
Never
-- Drop queries to remove existing tables (if they exist)
DROP TABLE IF EXISTS check_in_history;
DROP TABLE IF EXISTS user_feedback;
DROP TABLE IF EXISTS item_inventory;
DROP TABLE IF EXISTS notification;
DROP TABLE IF EXISTS order_plan_detail;
DROP TABLE IF EXISTS `order`;
DROP TABLE IF EXISTS `transaction`;
DROP TABLE IF EXISTS transfer;
DROP TABLE IF EXISTS gymer_booking;
DROP TABLE IF EXISTS shift;
DROP TABLE IF EXISTS gym_plan;
DROP TABLE IF EXISTS mst_kbn;
DROP TABLE IF EXISTS gym_department_services;
DROP TABLE IF EXISTS gym_department_schedule;
DROP TABLE IF EXISTS gym_department_albums;
DROP TABLE IF EXISTS gym_department_amenities;
DROP TABLE IF EXISTS gym_department_features;
DROP TABLE IF EXISTS features;
DROP TABLE IF EXISTS gym_department;
DROP TABLE IF EXISTS brand_albums;
DROP TABLE IF EXISTS brand_amenities;
DROP TABLE IF EXISTS brand;
DROP TABLE IF EXISTS user_role;
DROP TABLE IF EXISTS role;
DROP TABLE IF EXISTS wallet;
DROP TABLE IF EXISTS `user`;
DROP TABLE IF EXISTS user_detail;

CREATE TABLE IF NOT EXISTS user_detail (
                                           user_detail_id       INT AUTO_INCREMENT PRIMARY KEY,
                                           first_name    VARCHAR(50) NOT NULL,
    last_name     VARCHAR(50) NOT NULL,
    email         VARCHAR(100) NOT NULL,
    phone_number  VARCHAR(20) NOT NULL,
    address       VARCHAR(255) NOT NULL,
    date_of_birth DATE NOT NULL,
    gender        VARCHAR(10) NOT NULL,
    image_url TEXT NULL

    );
-- User table to store user information
CREATE TABLE IF NOT EXISTS `user` (
                                      user_id          INT AUTO_INCREMENT PRIMARY KEY,
                                      user_account     VARCHAR(250) NOT NULL,
    user_password    VARCHAR(100) NOT NULL,
    user_detail_id        INT NOT NULL,
    user_create_time VARCHAR(20) NOT NULL,
    user_deleted     TINYINT NOT NULL,
    FOREIGN KEY (user_detail_id) REFERENCES user_detail(user_detail_id)
    );
-- User Wallet table to store user wallet information
CREATE TABLE IF NOT EXISTS wallet (
                                      wallet_id     INT AUTO_INCREMENT PRIMARY KEY,
                                      user_id       INT NOT NULL,
                                      balance       DECIMAL(10, 2) DEFAULT 0.0,
    FOREIGN KEY (user_id) REFERENCES user(user_id)
    );
-- Role table to store user roles
CREATE TABLE IF NOT EXISTS role (
                                    role_id   INT AUTO_INCREMENT PRIMARY KEY,
                                    role_name VARCHAR(20) NOT NULL
    );

-- User Role table to store the relationship between users and roles
CREATE TABLE IF NOT EXISTS 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 TABLE IF NOT EXISTS brand (
                                     brand_id                    INT AUTO_INCREMENT PRIMARY KEY,
                                     user_id                     INT NOT NULL,
                                     name                        VARCHAR(50) NOT NULL,
    logo_url                    VARCHAR(255) NOT NULL,
    wallpaper_url               VARCHAR(255) NOT NULL,
    thumbnail_url               VARCHAR(255) NOT NULL,
    description                 text,
    rating                      DECIMAL(10, 2) DEFAULT 0,
    contact_number              VARCHAR(20) NOT NULL,
    contact_email				VARCHAR(50) NOT NULL,
    brand_status_key   		    INT NOT NULL,
    FOREIGN KEY (user_id) REFERENCES `user`(user_id)
    );

CREATE TABLE IF NOT EXISTS brand_amenities  (
                                                amenitie_id             INT AUTO_INCREMENT PRIMARY KEY,
                                                brand_id                INT NOT NULL,
                                                photo_url               VARCHAR(255) NOT NULL,
    amenitie_name           VARCHAR(50),
    description             text,
    amenitie_status INT NOT NULL,
    FOREIGN KEY (brand_id) REFERENCES brand(brand_id)
    );

-- Gym Department table to store gym department information
CREATE TABLE IF NOT EXISTS gym_department (
    gym_department_id           INT AUTO_INCREMENT PRIMARY KEY,
    gym_department_status_key   INT NOT NULL,
    brand_id                    INT NOT NULL,
    name                        VARCHAR(255) NOT NULL,
    address                     VARCHAR(255) NOT NULL,
    contact_number              VARCHAR(20) NOT NULL,
    logo_url                    VARCHAR(255) NOT NULL,
    wallpaper_url               VARCHAR(255) NOT NULL,
    thumbnail_url               VARCHAR(255) NOT NULL,
    description                 text,
    latitude 		            DECIMAL(10,8) NOT NULL,
    longitude 		            DECIMAL(11,8) NOT NULL,
    rating                      DECIMAL(10, 2) DEFAULT 0,
    capacity                    INT NOT NULL,
    area                        DECIMAL(10, 2),
    FOREIGN KEY (brand_id) REFERENCES brand(brand_id)
    );

CREATE TABLE IF NOT EXISTS gym_department_albums (
                                                     id INT PRIMARY KEY AUTO_INCREMENT,
                                                     gym_department_id INT NOT NULL,
                                                     photo_url VARCHAR(255) NOT NULL,
    description VARCHAR(255),
    FOREIGN KEY(gym_department_id) REFERENCES gym_department(gym_department_id)
    );

CREATE TABLE IF NOT EXISTS gym_department_schedule (
                                                       id INT AUTO_INCREMENT PRIMARY KEY,
                                                       gym_department_id INT NOT NULL,
                                                       day VARCHAR(10) NOT NULL,
    open_time VARCHAR(255),
    close_time VARCHAR(255),
    FOREIGN KEY (gym_department_id) REFERENCES gym_department(gym_department_id)
    );


CREATE TABLE IF NOT EXISTS features (
    feature_id INT AUTO_INCREMENT PRIMARY KEY,
    feature_icon varchar(150),
    feature_name VARCHAR(50),
    feature_status INT NOT NULL
    );

CREATE TABLE IF NOT EXISTS gym_department_features (
    gym_department_feature_id INT AUTO_INCREMENT PRIMARY KEY,
    feature_id INT,
    gym_department_id INT NOT NULL,
    feature_status INT NOT NULL,
    FOREIGN KEY (feature_id) REFERENCES features(feature_id),
    FOREIGN KEY (gym_department_id) REFERENCES gym_department(gym_department_id)
    );


-- table name mst_kbn to store type,status of all tables
CREATE TABLE IF NOT EXISTS mst_kbn (
                                       mst_kbn_id    INT AUTO_INCREMENT PRIMARY KEY,
                                       mst_kbn_name  VARCHAR(50) NOT NULL,
    mst_kbn_key   INT NOT NULL,
    mst_kbn_value VARCHAR(50) NOT NULL
    );

-- Gym Plan table to store gym plan information
CREATE TABLE IF NOT EXISTS gym_plan (
                                        plan_id        INT AUTO_INCREMENT PRIMARY KEY,
                                        gym_department_id         INT NOT NULL,
                                        user_id        INT NOT NULL,
                                        gym_plan_key   INT NOT NULL,
                                        gym_plan_status_key    INT NOT NULL,
                                        gym_plan_type_key  INT NOT NULL,
                                        name           VARCHAR(255) NOT NULL,
    description    text,
    price          DECIMAL(10, 2) ,
    price_per_hours         DECIMAL(10, 2) ,
    plan_sold      INT NOT NULL,
    duration       INT ,
    plan_before_active_validity       INT NOT NULL,
    plan_after_active_validity        INT NOT NULL,
    FOREIGN KEY (gym_department_id) REFERENCES gym_department(gym_department_id),
    foreign key (user_id)references user(user_id)
    );

-- Shift table to store shift information
CREATE TABLE IF NOT EXISTS shift (
                                     shift_id               INT AUTO_INCREMENT PRIMARY KEY,
                                     gym_department_id      INT NOT NULL,
                                     start_time             TIME NOT NULL,
                                     end_time               TIME NOT NULL,
                                     FOREIGN KEY (gym_department_id) REFERENCES gym_department(gym_department_id)
    );

-- Transfer table to do the function related to transferring credit
CREATE TABLE IF NOT EXISTS transfer (
                                        transfer_id    INT AUTO_INCREMENT PRIMARY KEY,
                                        sender_id      INT NOT NULL,
                                        receiver_id    INT NOT NULL,
                                        amount         DECIMAL(10, 2) NOT NULL,
    transfer_date  DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (sender_id) REFERENCES `user`(user_id),
    FOREIGN KEY (receiver_id) REFERENCES `user`(user_id)
    );

-- Transaction table to do the function with transactions between credit and real money
CREATE TABLE IF NOT EXISTS `transaction` (
                                             transaction_id   INT AUTO_INCREMENT PRIMARY KEY,
                                             wallet_id        INT NOT NULL,
                                             status           VARCHAR(30) NOT NULL,
                                             amount           DECIMAL(10, 2) NOT NULL,
    transaction_date DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (wallet_id) REFERENCES wallet(wallet_id)
    );

-- Order table to store order information
CREATE TABLE IF NOT EXISTS `order` (
                                       order_id        INT AUTO_INCREMENT PRIMARY KEY,
                                       user_id         INT NOT NULL,
                                       order_create_time      DATETIME NOT NULL,
                                       order_status_key    INT NOT NULL,
                                       discount              INT NOT NULL,
                                       order_total_money     DECIMAL(10, 2) NOT NULL,
    order_note                 VARCHAR(500),
    FOREIGN KEY (user_id) REFERENCES `user`(user_id)
    );

-- Order Plan Detail table to store order plan details
CREATE TABLE IF NOT EXISTS order_plan_detail (
                                                 order_detail_id INT AUTO_INCREMENT PRIMARY KEY,
                                                 order_id        INT NOT NULL,
                                                 name              VARCHAR(255) NOT NULL,
    quantity        INT NOT NULL,
    price_per_hours         DECIMAL(10, 2) NOT NULL,
    price           DECIMAL(10, 2) NOT NULL,
    duration       INT NOT NULL,
    plan_before_active_validity       INT NOT NULL,
    plan_after_active_validity        INT NOT NULL,
    gym_department_id                 INT NOT NULL,
    `plan_active_time`  DATETIME DEFAULT NULL,
    `item_status_key`   INT NOT NULL,
    `plan_expired_time` DATETIME DEFAULT NULL,
    `description`       TEXT,
    use_status         VARCHAR(100),
    FOREIGN KEY (order_id) REFERENCES `order`(order_id),
    FOREIGN KEY (gym_department_id) REFERENCES gym_department(gym_department_id)
    );

-- User Inventory table to store user inventory information
CREATE TABLE IF NOT EXISTS item_inventory (
                                              inventory_id   INT AUTO_INCREMENT PRIMARY KEY,
                                              user_id       INT NOT NULL,
                                              plan_id        INT NOT NULL,
                                              plan_active_time DATETIME NOT NULL,
                                              item_status_key INT NOT NULL,
                                              plan_expired_time       DATETIME NOT NULL,
                                              FOREIGN KEY (user_id) REFERENCES `user`(user_id),
    FOREIGN KEY (plan_id) REFERENCES gym_plan(plan_id)
    );

-- User Feedback table to store user feedback information
CREATE TABLE IF NOT EXISTS 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,
                                             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)
    );


CREATE TABLE IF NOT EXISTS notification (
                                            notification_id INT auto_increment NOT NULL,
                                            user_id_send INT NOT NULL,
                                            user_id_receive INT NOT NULL,
                                            message TEXT NOT NULL,
                                            time_send DATETIME NOT NULL,
                                            department_id INT NOT NULL,
                                            message_type VARCHAR(100) NOT NULL,
    status INT NOT NULL,
    CONSTRAINT Notification_pk PRIMARY KEY (notification_id),
    CONSTRAINT Notification_FK FOREIGN KEY (department_id) REFERENCES fitpass.gym_department(gym_department_id),
    CONSTRAINT Notification_FK_1 FOREIGN KEY (user_id_send) REFERENCES fitpass.`user`(user_id),
    CONSTRAINT Notification_FK_2 FOREIGN KEY (user_id_receive) REFERENCES fitpass.`user`(user_id)
    );


CREATE TABLE IF NOT EXISTS check_in_history (
                                                check_in_history_id INT auto_increment NOT NULL,
                                                order_detail_id INT NOT NULL,
                                                status_key INT NOT NULL,
                                                `check_in_time` DATETIME NOT NULL,
                                                check_out_time DATETIME NULL,
                                                total_credit DECIMAL(10,2) NULL,
    emp_checkin_id INT NOT NULL,
    feedback_id INT NULL,
    CONSTRAINT check_in_history_pk PRIMARY KEY (check_in_history_id),
    CONSTRAINT check_in_history_FK FOREIGN KEY (order_detail_id) REFERENCES fitpass.order_plan_detail(order_detail_id),
    CONSTRAINT check_in_history_FK_1 FOREIGN KEY (emp_checkin_id) REFERENCES fitpass.`user`(user_id),
    CONSTRAINT check_in_history_FK_2 FOREIGN KEY (feedback_id) REFERENCES fitpass.user_feedback(feedback_id)
    );


DELIMITER $$
CREATE TRIGGER update_gym_rating_avg
    AFTER INSERT ON user_feedback
    FOR EACH ROW
BEGIN

    DECLARE dept_id INT;
    DECLARE total, count INT;

    SET dept_id = NEW.department_id;

    SELECT IFNULL(SUM(rating),0), COUNT(*) INTO total, count
    FROM user_feedback
    WHERE department_id = dept_id;

    UPDATE gym_department
    SET rating = total/count
    WHERE gym_department_id = dept_id;

    END$$
    DELIMITER ;

DELIMITER $$
    CREATE TRIGGER update_brand_rating
        AFTER UPDATE ON gym_department
        FOR EACH ROW
    BEGIN

        DECLARE avg_rating DECIMAL(3,1);

        SELECT AVG(rating) INTO avg_rating
        FROM gym_department
        WHERE brand_id = NEW.brand_id;

        UPDATE brand
        SET rating = avg_rating
        WHERE brand_id = NEW.brand_id;

        END$$
        DELIMITER ;