Untitled

-- Drop tables in reverse order of dependencies to avoid constraint issues
DROP TABLE Body_Measurements CASCADE CONSTRAINTS;
DROP TABLE Progresses CASCADE CONSTRAINTS;
DROP TABLE Member_Sessions CASCADE CONSTRAINTS;
DROP TABLE Sessions CASCADE CONSTRAINTS;
DROP TABLE Offers CASCADE CONSTRAINTS;
DROP TABLE Services CASCADE CONSTRAINTS;
DROP TABLE Memberships CASCADE CONSTRAINTS;
DROP TABLE Subscriptions CASCADE CONSTRAINTS;
DROP TABLE Members CASCADE CONSTRAINTS;
DROP TABLE Trainers CASCADE CONSTRAINTS;
DROP TABLE Administrators CASCADE CONSTRAINTS;
DROP TABLE Users CASCADE CONSTRAINTS;

-- Recreate tables

-- Users table to store common attributes for all user types
CREATE TABLE Users (
    user_id NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY PRIMARY KEY,
    username VARCHAR2(50) UNIQUE NOT NULL,
    password VARCHAR2(100) NOT NULL,
    full_name VARCHAR2(100) NOT NULL,
    email VARCHAR2(100) UNIQUE NOT NULL,
    contact_number VARCHAR2(15),
    user_type VARCHAR2(15) CHECK (user_type IN ('Administrator', 'Trainer', 'Member')) NOT NULL
);

-- Administrators table (inherits from Users)
CREATE TABLE Administrators (
    user_id NUMBER PRIMARY KEY,
    CONSTRAINT fk_Admin_User FOREIGN KEY (user_id) REFERENCES Users(user_id) ON DELETE CASCADE
);

-- Trainers table with specialization field
CREATE TABLE Trainers (
    user_id NUMBER PRIMARY KEY,
    specialization VARCHAR2(100),
    CONSTRAINT fk_Trainer_User FOREIGN KEY (user_id) REFERENCES Users(user_id) ON DELETE CASCADE
);

-- Members table with additional attributes specific to members
CREATE TABLE Members (
    user_id NUMBER PRIMARY KEY,
    current_weight NUMBER,
    target_weight NUMBER,
    fitness_goals VARCHAR2(4000),
    height NUMBER,
    age NUMBER,
    activity_level VARCHAR2(50),
    CONSTRAINT fk_Member_User FOREIGN KEY (user_id) REFERENCES Users(user_id) ON DELETE CASCADE
);

-- Subscriptions table
CREATE TABLE Subscriptions (
    subscription_id NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY PRIMARY KEY,
    subscription_name VARCHAR2(100) NOT NULL,
    duration_days NUMBER NOT NULL,
    price NUMBER NOT NULL,
    description VARCHAR2(4000)
);

-- Memberships table, linking members to subscription plans
CREATE TABLE Memberships (
    membership_id NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY PRIMARY KEY,
    member_id NUMBER NOT NULL,
    subscription_id NUMBER,
    start_date DATE NOT NULL,
    end_date DATE NOT NULL,
    status VARCHAR2(10) DEFAULT 'Active' CHECK (status IN ('Active', 'Expired', 'Cancelled')),
    CONSTRAINT fk_Membership_Member FOREIGN KEY (member_id) REFERENCES Members(user_id) ON DELETE CASCADE,
    CONSTRAINT fk_Membership_Subscription FOREIGN KEY (subscription_id) REFERENCES Subscriptions(subscription_id) ON DELETE SET NULL
);

-- Services table
CREATE TABLE Services (
    service_id NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY PRIMARY KEY,
    service_name VARCHAR2(100) NOT NULL,
    description VARCHAR2(4000)
);

-- Offers table, linked to specific services
CREATE TABLE Offers (
    offering_id NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY PRIMARY KEY,
    service_id NUMBER NOT NULL,
    offer_name VARCHAR2(100) NOT NULL,
    description VARCHAR2(4000),
    CONSTRAINT fk_Offer_Service FOREIGN KEY (service_id) REFERENCES Services(service_id) ON DELETE CASCADE
);

-- Sessions table for training sessions with Trainer association
CREATE TABLE Sessions (
    session_id NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY PRIMARY KEY,
    session_name VARCHAR2(100) NOT NULL,
    trainer_id NUMBER,
    schedule_time TIMESTAMP NOT NULL,
    session_type VARCHAR2(50),
    capacity NUMBER,
    CONSTRAINT fk_Session_Trainer FOREIGN KEY (trainer_id) REFERENCES Trainers(user_id) ON DELETE SET NULL
);

-- Member_Sessions table for many-to-many relationship between Members and Sessions
CREATE TABLE Member_Sessions (
    member_id NUMBER NOT NULL,
    session_id NUMBER NOT NULL,
    PRIMARY KEY (member_id, session_id),
    CONSTRAINT fk_MemberSession_Member FOREIGN KEY (member_id) REFERENCES Members(user_id) ON DELETE CASCADE,
    CONSTRAINT fk_MemberSession_Session FOREIGN KEY (session_id) REFERENCES Sessions(session_id) ON DELETE CASCADE
);

-- Progresses table for tracking member progress
CREATE TABLE Progresses (
    progress_id NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY PRIMARY KEY,
    member_id NUMBER NOT NULL,
    progress_date DATE NOT NULL,
    notes VARCHAR2(4000),
    CONSTRAINT fk_Progress_Member FOREIGN KEY (member_id) REFERENCES Members(user_id) ON DELETE CASCADE
);

-- Body_Measurements table for storing various measurements associated with progress
CREATE TABLE Body_Measurements (
    measurement_id NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY PRIMARY KEY,
    progress_id NUMBER NOT NULL,
    measurement_type VARCHAR2(50) NOT NULL, -- e.g., Chest, Waist, etc.
    measurement_value NUMBER NOT NULL,
    CONSTRAINT fk_BodyMeasurement_Progress FOREIGN KEY (progress_id) REFERENCES Progresses(progress_id) ON DELETE CASCADE
);