gym.sql

CREATE TABLE members (
    id BIGSERIAL PRIMARY KEY,
    full_name VARCHAR(120) NOT NULL,
    gender VARCHAR(10),
    phone VARCHAR(20) UNIQUE,
    email VARCHAR(100) UNIQUE,
    date_of_birth DATE,
    join_date DATE DEFAULT CURRENT_DATE,
    status VARCHAR(20) DEFAULT 'active'
);

CREATE TABLE trainers (
    id BIGSERIAL PRIMARY KEY,
    full_name VARCHAR(120) NOT NULL,
    specialty VARCHAR(120),
    phone VARCHAR(20),
    hired_at DATE DEFAULT CURRENT_DATE
);

CREATE TABLE plans (
    id BIGSERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,  -- e.g., Monthly, 3-Month VIP
    duration_days INT NOT NULL,
    price DECIMAL(10,2) NOT NULL
);

CREATE TABLE subscriptions (
    id BIGSERIAL PRIMARY KEY,
    member_id BIGINT REFERENCES members(id),
    plan_id BIGINT REFERENCES plans(id),
    start_date DATE NOT NULL,
    end_date DATE NOT NULL,
    status VARCHAR(20) DEFAULT 'active'
);

CREATE TABLE payments (
    id BIGSERIAL PRIMARY KEY,
    member_id BIGINT REFERENCES members(id),
    subscription_id BIGINT REFERENCES subscriptions(id),
    amount DECIMAL(10,2) NOT NULL,
    method VARCHAR(50),   -- Cash, ABA, Wing, Credit Card
    paid_at TIMESTAMP DEFAULT NOW()
);

CREATE TABLE classes (
    id BIGSERIAL PRIMARY KEY,
    name VARCHAR(120) NOT NULL,       -- e.g., Yoga, Muay Thai, Cardio Fit
    description TEXT
);

CREATE TABLE class_schedules (
    id BIGSERIAL PRIMARY KEY,
    class_id BIGINT REFERENCES classes(id),
    trainer_id BIGINT REFERENCES trainers(id),
    start_time TIMESTAMP NOT NULL,
    end_time TIMESTAMP NOT NULL,
    room VARCHAR(60)
);

CREATE TABLE class_attendance (
    id BIGSERIAL PRIMARY KEY,
    schedule_id BIGINT REFERENCES class_schedules(id),
    member_id BIGINT REFERENCES members(id),
    check_in_time TIMESTAMP DEFAULT NOW()
);

CREATE TABLE checkins (
    id BIGSERIAL PRIMARY KEY,
    member_id BIGINT REFERENCES members(id),
    check_in_time TIMESTAMP DEFAULT NOW()
);



-- | Table              | Purpose                                  |
-- | ------------------ | ---------------------------------------- |
-- | `members`          | Gym member/customer information          |
-- | `trainers`         | Personal trainers/instructors            |
-- | `plans`            | Membership plans (monthly, yearly, VIP…) |
-- | `subscriptions`    | Which plan a member is currently on      |
-- | `classes`          | Group class types (Yoga, Boxing, Zumba…) |
-- | `class_schedules`  | Timetable for each class                 |
-- | `class_attendance` | Which members attended which sessions    |
-- | `payments`         | Payments for subscriptions               |
-- | `checkins`         | Track member gym entry (QR/ID card scan) |