Untitled

-- Drop tables if they exist, handle exceptions gracefully

BEGIN
    EXECUTE IMMEDIATE 'DROP TABLE member_service CASCADE CONSTRAINTS';
EXCEPTION
    WHEN OTHERS THEN
        IF SQLCODE != -942 THEN RAISE; END IF;
END;
/

BEGIN
    EXECUTE IMMEDIATE 'DROP TABLE feedback CASCADE CONSTRAINTS';
EXCEPTION
    WHEN OTHERS THEN
        IF SQLCODE != -942 THEN RAISE; END IF;
END;
/

BEGIN
    EXECUTE IMMEDIATE 'DROP TABLE classes CASCADE CONSTRAINTS';
EXCEPTION
    WHEN OTHERS THEN
        IF SQLCODE != -942 THEN RAISE; END IF;
END;
/

BEGIN
    EXECUTE IMMEDIATE 'DROP TABLE service CASCADE CONSTRAINTS';
EXCEPTION
    WHEN OTHERS THEN
        IF SQLCODE != -942 THEN RAISE; END IF;
END;
/

BEGIN
    EXECUTE IMMEDIATE 'DROP TABLE admins CASCADE CONSTRAINTS';
EXCEPTION
    WHEN OTHERS THEN
        IF SQLCODE != -942 THEN RAISE; END IF;
END;
/

BEGIN
    EXECUTE IMMEDIATE 'DROP TABLE trainer CASCADE CONSTRAINTS';
EXCEPTION
    WHEN OTHERS THEN
        IF SQLCODE != -942 THEN RAISE; END IF;
END;
/

BEGIN
    EXECUTE IMMEDIATE 'DROP TABLE member CASCADE CONSTRAINTS';
EXCEPTION
    WHEN OTHERS THEN
        IF SQLCODE != -942 THEN RAISE; END IF;
END;
/

BEGIN
    EXECUTE IMMEDIATE 'DROP TABLE users CASCADE CONSTRAINTS';
EXCEPTION
    WHEN OTHERS THEN
        IF SQLCODE != -942 THEN RAISE; END IF;
END;
/

-- Recreate tables

CREATE TABLE users (
    user_id NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY PRIMARY KEY,
    name VARCHAR2(100) NOT NULL,
    phone CHAR(10),
    username VARCHAR2(50) NOT NULL UNIQUE,
    password VARCHAR2(100) NOT NULL,
    email VARCHAR2(100),
    role VARCHAR2(20)
);

CREATE TABLE trainer (
    id NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY PRIMARY KEY,
    user_id NUMBER NOT NULL,
    CONSTRAINT fk_trainer_user FOREIGN KEY (user_id) REFERENCES users(user_id)
);

CREATE TABLE admins (
    id NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY PRIMARY KEY,
    user_id NUMBER NOT NULL,
    CONSTRAINT fk_admin_user FOREIGN KEY (user_id) REFERENCES users(user_id)
);

CREATE TABLE member (
    id NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY PRIMARY KEY,
    user_id NUMBER NOT NULL,
    membership VARCHAR2(50),
    CONSTRAINT fk_member_user FOREIGN KEY (user_id) REFERENCES users(user_id)
);

CREATE TABLE service (
    id NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY PRIMARY KEY,
    name VARCHAR2(100) NOT NULL,
    amount NUMBER NOT NULL
);

CREATE TABLE classes (
    id NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY PRIMARY KEY,
    name VARCHAR2(100) NOT NULL,
    trainer_id NUMBER NOT NULL,
    day VARCHAR2(10),
    time VARCHAR2(10),
    capacity VARCHAR2(10),
    description VARCHAR2(100),
    CONSTRAINT fk_trainer FOREIGN KEY (trainer_id) REFERENCES trainer(id)
);

CREATE TABLE feedback (
    id NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY PRIMARY KEY,
    member_id NUMBER NOT NULL,
    trainer_id NUMBER NOT NULL,
    feedback_message VARCHAR2(100) NOT NULL,
    CONSTRAINT fk_member_id FOREIGN KEY (member_id) REFERENCES member(id),
    CONSTRAINT fk_trainer_id FOREIGN KEY (trainer_id) REFERENCES trainer(id)
);

CREATE TABLE member_service (
    id NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY PRIMARY KEY,
    member_id NUMBER NOT NULL,
    service_id NUMBER NOT NULL,
    CONSTRAINT fk_member FOREIGN KEY (member_id) REFERENCES member(id),
    CONSTRAINT fk_service FOREIGN KEY (service_id) REFERENCES service(id)
);