update database

-- Create the Gym table
CREATE TABLE Gym (
    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 GymPlan table
CREATE TABLE GymPlan (
    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,
    image_url VARCHAR(255) CHARACTER SET utf8mb4,
    FOREIGN KEY (gym_id) REFERENCES Gym(id)
);

-- Create the Employee table
CREATE TABLE Employee (
    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 the Wallet table
CREATE TABLE Wallet (
    id INT PRIMARY KEY AUTO_INCREMENT,
    gym_id INT NOT NULL,
    balance DECIMAL(10, 2) NOT NULL,
    FOREIGN KEY (gym_id) REFERENCES Gym(id)
);

-- Create the Transaction table
CREATE TABLE 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 Wallet(id)
);

-- Create the Review table
CREATE TABLE Review (
    id INT PRIMARY KEY AUTO_INCREMENT,
    gym_id INT NOT NULL,
    customer_name VARCHAR(255) CHARACTER SET utf8mb4 NOT NULL,
    rating INT NOT NULL,
    feedback VARCHAR(255) CHARACTER SET utf8mb4 NOT NULL,
    response VARCHAR(255) CHARACTER SET utf8mb4,
    FOREIGN KEY (gym_id) REFERENCES Gym(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 Gymer table
CREATE TABLE Gymer (
    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,
    plan_id INT NOT NULL,
    check_in_time DATETIME,
    check_out_time DATETIME,
    FOREIGN KEY (gym_id) REFERENCES Gym(id),
    FOREIGN KEY (plan_id) REFERENCES GymPlan(id)
);