-- ============================================================
-- PLANET FITNESS MEMBER MANAGEMENT SYSTEM
-- Database Creation Script
-- 
-- Target Platform: Oracle APEX / Oracle Database
-- Version: 1.0
-- Created: January 2026
-- Author: Database Design Team
-- 
-- Description:
--   Complete schema for managing gym members, check-ins,
--   guest visits, staff, and locations for a Planet Fitness
--   franchise group operating multiple locations.
-- ============================================================


-- ============================================================
-- SECTION 1: DROP EXISTING OBJECTS (for clean re-runs)
-- ============================================================

-- Drop tables in reverse dependency order
DROP TABLE guest_visit CASCADE CONSTRAINTS;
DROP TABLE check_in CASCADE CONSTRAINTS;
DROP TABLE guest CASCADE CONSTRAINTS;
DROP TABLE staff_location CASCADE CONSTRAINTS;
DROP TABLE member CASCADE CONSTRAINTS;
DROP TABLE membership_type CASCADE CONSTRAINTS;
DROP TABLE location_amenity CASCADE CONSTRAINTS;
DROP TABLE amenity CASCADE CONSTRAINTS;
DROP TABLE location CASCADE CONSTRAINTS;
DROP TABLE staff CASCADE CONSTRAINTS;


-- ============================================================
-- SECTION 2: CREATE TABLES
-- ============================================================

-- ------------------------------------------------------------
-- AMENITY
-- Stores available amenities (Black Card perks)
-- ------------------------------------------------------------
CREATE TABLE amenity (
    amenity_id          NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name                VARCHAR2(100) NOT NULL,
    description         VARCHAR2(500),
    black_card_only     CHAR(1) DEFAULT 'Y' CHECK (black_card_only IN ('Y', 'N')),
    
    CONSTRAINT amenity_name_unique UNIQUE (name)
);

COMMENT ON TABLE amenity IS 'Black Card amenities available at Planet Fitness locations';
COMMENT ON COLUMN amenity.black_card_only IS 'Y = only Black Card members can use; N = all members';


-- ------------------------------------------------------------
-- STAFF
-- Gym employees across all locations
-- (Created before LOCATION because LOCATION references STAFF for manager)
-- ------------------------------------------------------------
CREATE TABLE staff (
    staff_id            NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    first_name          VARCHAR2(100) NOT NULL,
    last_name           VARCHAR2(100) NOT NULL,
    email               VARCHAR2(200),
    phone               VARCHAR2(20),
    role                VARCHAR2(50) NOT NULL,
    hire_date           DATE NOT NULL,
    
    CONSTRAINT staff_role_check CHECK (
        role IN ('Front Desk', 'Trainer', 'Maintenance', 'Manager', 'Assistant Manager')
    )
);

COMMENT ON TABLE staff IS 'Employees working at Planet Fitness locations';


-- ------------------------------------------------------------
-- LOCATION
-- Stores gym locations in the franchise group
-- ------------------------------------------------------------
CREATE TABLE location (
    location_id         NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name                VARCHAR2(200) NOT NULL,
    street_address      VARCHAR2(200) NOT NULL,
    city                VARCHAR2(100) NOT NULL,
    state               CHAR(2) NOT NULL,
    zip_code            VARCHAR2(10) NOT NULL,
    phone               VARCHAR2(20),
    opening_time        VARCHAR2(10),  -- Stored as 'HH:MI AM' format
    closing_time        VARCHAR2(10),
    manager_id          NUMBER,
    
    CONSTRAINT location_name_unique UNIQUE (name),
    CONSTRAINT location_manager_fk FOREIGN KEY (manager_id) 
        REFERENCES staff(staff_id)
);

COMMENT ON TABLE location IS 'Planet Fitness gym locations in the franchise group';
COMMENT ON COLUMN location.manager_id IS 'References staff.staff_id for location manager';


-- ------------------------------------------------------------
-- LOCATION_AMENITY
-- Junction table: which amenities are available at which locations
-- ------------------------------------------------------------
CREATE TABLE location_amenity (
    location_id         NUMBER NOT NULL,
    amenity_id          NUMBER NOT NULL,
    available_date      DATE DEFAULT SYSDATE,
    
    CONSTRAINT location_amenity_pk PRIMARY KEY (location_id, amenity_id),
    CONSTRAINT la_location_fk FOREIGN KEY (location_id) 
        REFERENCES location(location_id),
    CONSTRAINT la_amenity_fk FOREIGN KEY (amenity_id) 
        REFERENCES amenity(amenity_id)
);

COMMENT ON TABLE location_amenity IS 'Maps amenities to locations with availability dates';
COMMENT ON COLUMN location_amenity.available_date IS 'Date when amenity becomes available (handles phased rollouts)';


-- ------------------------------------------------------------
-- MEMBERSHIP_TYPE
-- Defines membership tiers and their privileges
-- ------------------------------------------------------------
CREATE TABLE membership_type (
    membership_type_id  NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name                VARCHAR2(50) NOT NULL,
    monthly_rate        NUMBER(6,2) NOT NULL,
    annual_fee          NUMBER(6,2) NOT NULL,
    allows_guests       CHAR(1) DEFAULT 'N' CHECK (allows_guests IN ('Y', 'N')),
    all_location_access CHAR(1) DEFAULT 'N' CHECK (all_location_access IN ('Y', 'N')),
    
    CONSTRAINT membership_type_name_unique UNIQUE (name)
);

COMMENT ON TABLE membership_type IS 'Membership tiers with pricing and privileges';
COMMENT ON COLUMN membership_type.allows_guests IS 'Y = member can bring guests; N = no guest privileges';
COMMENT ON COLUMN membership_type.all_location_access IS 'Y = can visit any location; N = home gym only';


-- ------------------------------------------------------------
-- STAFF_LOCATION
-- Junction table: which staff work at which locations
-- ------------------------------------------------------------
CREATE TABLE staff_location (
    staff_id            NUMBER NOT NULL,
    location_id         NUMBER NOT NULL,
    is_primary          CHAR(1) DEFAULT 'Y' CHECK (is_primary IN ('Y', 'N')),
    
    CONSTRAINT staff_location_pk PRIMARY KEY (staff_id, location_id),
    CONSTRAINT sl_staff_fk FOREIGN KEY (staff_id) 
        REFERENCES staff(staff_id),
    CONSTRAINT sl_location_fk FOREIGN KEY (location_id) 
        REFERENCES location(location_id)
);

COMMENT ON TABLE staff_location IS 'Maps staff to locations (supports multi-location workers)';
COMMENT ON COLUMN staff_location.is_primary IS 'Y = primary work location; N = secondary/fill-in location';


-- ------------------------------------------------------------
-- MEMBER
-- Gym members (the core entity)
-- ------------------------------------------------------------
CREATE TABLE member (
    member_id               NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    first_name              VARCHAR2(100) NOT NULL,
    last_name               VARCHAR2(100) NOT NULL,
    date_of_birth           DATE NOT NULL,
    email                   VARCHAR2(200) NOT NULL,
    phone                   VARCHAR2(20),
    street_address          VARCHAR2(200),
    city                    VARCHAR2(100),
    state                   CHAR(2),
    zip_code                VARCHAR2(10),
    emergency_contact_name  VARCHAR2(200),
    emergency_contact_phone VARCHAR2(20),
    membership_type_id      NUMBER NOT NULL,
    home_location_id        NUMBER NOT NULL,
    status                  VARCHAR2(20) DEFAULT 'active' NOT NULL,
    join_date               DATE DEFAULT SYSDATE NOT NULL,
    guardian_member_id      NUMBER,
    
    CONSTRAINT member_email_unique UNIQUE (email),
    CONSTRAINT member_status_check CHECK (
        status IN ('active', 'frozen', 'cancelled', 'past_due')
    ),
    CONSTRAINT member_type_fk FOREIGN KEY (membership_type_id) 
        REFERENCES membership_type(membership_type_id),
    CONSTRAINT member_location_fk FOREIGN KEY (home_location_id) 
        REFERENCES location(location_id),
    CONSTRAINT member_guardian_fk FOREIGN KEY (guardian_member_id) 
        REFERENCES member(member_id)
);

COMMENT ON TABLE member IS 'Gym members - core entity for the system';
COMMENT ON COLUMN member.guardian_member_id IS 'Self-reference for minor members (13-17) linked to parent/guardian';
COMMENT ON COLUMN member.status IS 'active = current member; frozen = temporarily paused; cancelled = former member; past_due = payment issue';


-- Index for common queries
CREATE INDEX member_home_location_idx ON member(home_location_id);
CREATE INDEX member_status_idx ON member(status);
CREATE INDEX member_join_date_idx ON member(join_date);


-- ------------------------------------------------------------
-- CHECK_IN
-- Logs every member visit
-- ------------------------------------------------------------
CREATE TABLE check_in (
    check_in_id         NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    member_id           NUMBER NOT NULL,
    location_id         NUMBER NOT NULL,
    check_in_timestamp  TIMESTAMP DEFAULT SYSTIMESTAMP NOT NULL,
    
    CONSTRAINT checkin_member_fk FOREIGN KEY (member_id) 
        REFERENCES member(member_id),
    CONSTRAINT checkin_location_fk FOREIGN KEY (location_id) 
        REFERENCES location(location_id)
);

COMMENT ON TABLE check_in IS 'Transactional log of member visits';

-- Indexes for reporting queries
CREATE INDEX checkin_member_idx ON check_in(member_id);
CREATE INDEX checkin_location_idx ON check_in(location_id);
CREATE INDEX checkin_timestamp_idx ON check_in(check_in_timestamp);


-- ------------------------------------------------------------
-- GUEST
-- Stores guest information for repeat visit tracking
-- ------------------------------------------------------------
CREATE TABLE guest (
    guest_id            NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    first_name          VARCHAR2(100) NOT NULL,
    last_name           VARCHAR2(100) NOT NULL,
    phone               VARCHAR2(20) NOT NULL,
    
    CONSTRAINT guest_phone_unique UNIQUE (phone)
);

COMMENT ON TABLE guest IS 'Guests brought by Black Card members - tracked for visit limits';
COMMENT ON COLUMN guest.phone IS 'Phone used as identifier to match returning guests';


-- ------------------------------------------------------------
-- GUEST_VISIT
-- Logs every guest visit (enforces once-per-location-per-month)
-- ------------------------------------------------------------
CREATE TABLE guest_visit (
    guest_visit_id      NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    guest_id            NUMBER NOT NULL,
    member_id           NUMBER NOT NULL,
    location_id         NUMBER NOT NULL,
    visit_timestamp     TIMESTAMP DEFAULT SYSTIMESTAMP NOT NULL,
    waiver_signed       CHAR(1) DEFAULT 'Y' CHECK (waiver_signed IN ('Y', 'N')),
    waiver_signed_date  DATE,
    
    CONSTRAINT gv_guest_fk FOREIGN KEY (guest_id) 
        REFERENCES guest(guest_id),
    CONSTRAINT gv_member_fk FOREIGN KEY (member_id) 
        REFERENCES member(member_id),
    CONSTRAINT gv_location_fk FOREIGN KEY (location_id) 
        REFERENCES location(location_id)
);

COMMENT ON TABLE guest_visit IS 'Log of guest visits - used to enforce once-per-location-per-month rule';

-- Indexes for guest visit lookups
CREATE INDEX gv_guest_idx ON guest_visit(guest_id);
CREATE INDEX gv_location_idx ON guest_visit(location_id);
CREATE INDEX gv_timestamp_idx ON guest_visit(visit_timestamp);


-- ============================================================
-- SECTION 3: INSERT TEST DATA
-- ============================================================

-- ------------------------------------------------------------
-- AMENITIES
-- ------------------------------------------------------------
INSERT INTO amenity (name, description, black_card_only) VALUES
    ('Tanning Booth', 'UV tanning beds with adjustable intensity', 'Y');
INSERT INTO amenity (name, description, black_card_only) VALUES
    ('HydroMassage Bed', 'Water-based massage lounger', 'Y');
INSERT INTO amenity (name, description, black_card_only) VALUES
    ('Massage Chair', 'Full-body massage chairs in relaxation area', 'Y');
INSERT INTO amenity (name, description, black_card_only) VALUES
    ('Red Light Therapy', 'Total body enhancement booth', 'Y');


-- ------------------------------------------------------------
-- STAFF
-- ------------------------------------------------------------
INSERT INTO staff (first_name, last_name, email, phone, role, hire_date) VALUES
    ('Maria', 'Santos', 'maria.santos@pfhbg.com', '717-555-1001', 'Manager', DATE '2022-03-15');
INSERT INTO staff (first_name, last_name, email, phone, role, hire_date) VALUES
    ('James', 'Wilson', 'james.wilson@pfhbg.com', '717-555-1002', 'Manager', DATE '2023-01-10');
INSERT INTO staff (first_name, last_name, email, phone, role, hire_date) VALUES
    ('Aisha', 'Johnson', 'aisha.johnson@pfhbg.com', '717-555-1003', 'Manager', DATE '2023-08-20');
INSERT INTO staff (first_name, last_name, email, phone, role, hire_date) VALUES
    ('Tyler', 'Brooks', 'tyler.brooks@pfhbg.com', '717-555-1004', 'Front Desk', DATE '2023-06-01');
INSERT INTO staff (first_name, last_name, email, phone, role, hire_date) VALUES
    ('Samantha', 'Lee', 'samantha.lee@pfhbg.com', '717-555-1005', 'Front Desk', DATE '2023-09-15');
INSERT INTO staff (first_name, last_name, email, phone, role, hire_date) VALUES
    ('Devon', 'Martinez', 'devon.martinez@pfhbg.com', '717-555-1006', 'Trainer', DATE '2024-01-08');
INSERT INTO staff (first_name, last_name, email, phone, role, hire_date) VALUES
    ('Rachel', 'Kim', 'rachel.kim@pfhbg.com', '717-555-1007', 'Maintenance', DATE '2023-04-22');


-- ------------------------------------------------------------
-- LOCATIONS
-- ------------------------------------------------------------
INSERT INTO location (name, street_address, city, state, zip_code, phone, opening_time, closing_time, manager_id) VALUES
    ('Planet Fitness - Downtown Harrisburg', '245 Market Street', 'Harrisburg', 'PA', '17101', '717-555-0101', '5:00 AM', '11:00 PM', 1);
INSERT INTO location (name, street_address, city, state, zip_code, phone, opening_time, closing_time, manager_id) VALUES
    ('Planet Fitness - Camp Hill', '3432 Simpson Ferry Road', 'Camp Hill', 'PA', '17011', '717-555-0102', '5:00 AM', '10:00 PM', 2);
INSERT INTO location (name, street_address, city, state, zip_code, phone, opening_time, closing_time, manager_id) VALUES
    ('Planet Fitness - Mechanicsburg', '5101 Carlisle Pike', 'Mechanicsburg', 'PA', '17050', '717-555-0103', '6:00 AM', '10:00 PM', 3);


-- ------------------------------------------------------------
-- LOCATION_AMENITY (which amenities at which locations)
-- Downtown has all amenities
-- Camp Hill missing tanning (coming soon)
-- Mechanicsburg is newest, still adding amenities
-- ------------------------------------------------------------
-- Downtown Harrisburg - fully equipped
INSERT INTO location_amenity (location_id, amenity_id, available_date) VALUES (1, 1, DATE '2024-01-15');
INSERT INTO location_amenity (location_id, amenity_id, available_date) VALUES (1, 2, DATE '2024-01-15');
INSERT INTO location_amenity (location_id, amenity_id, available_date) VALUES (1, 3, DATE '2024-01-15');
INSERT INTO location_amenity (location_id, amenity_id, available_date) VALUES (1, 4, DATE '2024-01-15');

-- Camp Hill - no tanning yet
INSERT INTO location_amenity (location_id, amenity_id, available_date) VALUES (2, 2, DATE '2024-03-01');
INSERT INTO location_amenity (location_id, amenity_id, available_date) VALUES (2, 3, DATE '2024-03-01');
INSERT INTO location_amenity (location_id, amenity_id, available_date) VALUES (2, 4, DATE '2024-03-01');

-- Mechanicsburg - just massage chairs for now
INSERT INTO location_amenity (location_id, amenity_id, available_date) VALUES (3, 3, DATE '2024-06-01');


-- ------------------------------------------------------------
-- MEMBERSHIP TYPES
-- ------------------------------------------------------------
INSERT INTO membership_type (name, monthly_rate, annual_fee, allows_guests, all_location_access) VALUES
    ('Classic', 15.00, 49.00, 'N', 'N');
INSERT INTO membership_type (name, monthly_rate, annual_fee, allows_guests, all_location_access) VALUES
    ('Black Card', 24.99, 49.00, 'Y', 'Y');


-- ------------------------------------------------------------
-- STAFF_LOCATION (who works where)
-- ------------------------------------------------------------
INSERT INTO staff_location (staff_id, location_id, is_primary) VALUES (1, 1, 'Y');  -- Maria @ Downtown
INSERT INTO staff_location (staff_id, location_id, is_primary) VALUES (2, 2, 'Y');  -- James @ Camp Hill
INSERT INTO staff_location (staff_id, location_id, is_primary) VALUES (3, 3, 'Y');  -- Aisha @ Mechanicsburg
INSERT INTO staff_location (staff_id, location_id, is_primary) VALUES (4, 1, 'Y');  -- Tyler @ Downtown
INSERT INTO staff_location (staff_id, location_id, is_primary) VALUES (4, 2, 'N');  -- Tyler also covers Camp Hill
INSERT INTO staff_location (staff_id, location_id, is_primary) VALUES (5, 2, 'Y');  -- Samantha @ Camp Hill
INSERT INTO staff_location (staff_id, location_id, is_primary) VALUES (5, 3, 'N');  -- Samantha also covers Mechanicsburg
INSERT INTO staff_location (staff_id, location_id, is_primary) VALUES (6, 1, 'Y');  -- Devon (trainer) @ Downtown
INSERT INTO staff_location (staff_id, location_id, is_primary) VALUES (6, 2, 'N');  -- Devon also at Camp Hill
INSERT INTO staff_location (staff_id, location_id, is_primary) VALUES (7, 1, 'Y');  -- Rachel (maintenance) @ Downtown
INSERT INTO staff_location (staff_id, location_id, is_primary) VALUES (7, 2, 'N');  -- Rachel covers all locations
INSERT INTO staff_location (staff_id, location_id, is_primary) VALUES (7, 3, 'N');


-- ------------------------------------------------------------
-- MEMBERS
-- Mix of membership types, statuses, and scenarios
-- ------------------------------------------------------------

-- Active Black Card members (regular users)
INSERT INTO member (first_name, last_name, date_of_birth, email, phone, street_address, city, state, zip_code, emergency_contact_name, emergency_contact_phone, membership_type_id, home_location_id, status, join_date) VALUES
    ('Jennifer', 'Adams', DATE '1985-04-12', 'jadams@email.com', '717-555-2001', '123 Oak Lane', 'Harrisburg', 'PA', '17101', 'Robert Adams', '717-555-2002', 2, 1, 'active', DATE '2023-02-14');
INSERT INTO member (first_name, last_name, date_of_birth, email, phone, street_address, city, state, zip_code, emergency_contact_name, emergency_contact_phone, membership_type_id, home_location_id, status, join_date) VALUES
    ('Marcus', 'Thompson', DATE '1990-08-23', 'mthompson@email.com', '717-555-2003', '456 Pine Street', 'Camp Hill', 'PA', '17011', 'Lisa Thompson', '717-555-2004', 2, 2, 'active', DATE '2023-05-20');
INSERT INTO member (first_name, last_name, date_of_birth, email, phone, street_address, city, state, zip_code, emergency_contact_name, emergency_contact_phone, membership_type_id, home_location_id, status, join_date) VALUES
    ('Priya', 'Patel', DATE '1992-11-05', 'ppatel@email.com', '717-555-2005', '789 Maple Avenue', 'Mechanicsburg', 'PA', '17050', 'Raj Patel', '717-555-2006', 2, 3, 'active', DATE '2024-01-10');

-- Active Classic members
INSERT INTO member (first_name, last_name, date_of_birth, email, phone, street_address, city, state, zip_code, emergency_contact_name, emergency_contact_phone, membership_type_id, home_location_id, status, join_date) VALUES
    ('David', 'Chen', DATE '1988-02-28', 'dchen@email.com', '717-555-2007', '321 Elm Court', 'Harrisburg', 'PA', '17102', 'Wei Chen', '717-555-2008', 1, 1, 'active', DATE '2023-08-05');
INSERT INTO member (first_name, last_name, date_of_birth, email, phone, street_address, city, state, zip_code, emergency_contact_name, emergency_contact_phone, membership_type_id, home_location_id, status, join_date) VALUES
    ('Sarah', 'O''Brien', DATE '1995-06-17', 'sobrien@email.com', '717-555-2009', '654 Birch Road', 'Camp Hill', 'PA', '17011', 'Mike O''Brien', '717-555-2010', 1, 2, 'active', DATE '2024-02-28');

-- Ghost members (active but haven't visited in 60+ days)
INSERT INTO member (first_name, last_name, date_of_birth, email, phone, street_address, city, state, zip_code, emergency_contact_name, emergency_contact_phone, membership_type_id, home_location_id, status, join_date) VALUES
    ('Brian', 'Williams', DATE '1982-09-14', 'bwilliams@email.com', '717-555-2011', '987 Cedar Lane', 'Harrisburg', 'PA', '17101', 'Carol Williams', '717-555-2012', 2, 1, 'active', DATE '2023-01-20');
INSERT INTO member (first_name, last_name, date_of_birth, email, phone, street_address, city, state, zip_code, emergency_contact_name, emergency_contact_phone, membership_type_id, home_location_id, status, join_date) VALUES
    ('Michelle', 'Garcia', DATE '1979-12-03', 'mgarcia@email.com', '717-555-2013', '147 Spruce Street', 'Camp Hill', 'PA', '17011', 'Carlos Garcia', '717-555-2014', 1, 2, 'active', DATE '2023-06-11');

-- Past due member (within grace period - joined 2 days ago for testing)
INSERT INTO member (first_name, last_name, date_of_birth, email, phone, street_address, city, state, zip_code, emergency_contact_name, emergency_contact_phone, membership_type_id, home_location_id, status, join_date) VALUES
    ('Kevin', 'Brown', DATE '1991-03-25', 'kbrown@email.com', '717-555-2015', '258 Walnut Way', 'Mechanicsburg', 'PA', '17050', 'Amy Brown', '717-555-2016', 2, 3, 'past_due', DATE '2023-07-15');

-- Frozen member
INSERT INTO member (first_name, last_name, date_of_birth, email, phone, street_address, city, state, zip_code, emergency_contact_name, emergency_contact_phone, membership_type_id, home_location_id, status, join_date) VALUES
    ('Amanda', 'Taylor', DATE '1987-07-08', 'ataylor@email.com', '717-555-2017', '369 Hickory Hill', 'Harrisburg', 'PA', '17102', 'John Taylor', '717-555-2018', 2, 1, 'frozen', DATE '2023-03-30');

-- Cancelled member
INSERT INTO member (first_name, last_name, date_of_birth, email, phone, street_address, city, state, zip_code, emergency_contact_name, emergency_contact_phone, membership_type_id, home_location_id, status, join_date) VALUES
    ('Ryan', 'Murphy', DATE '1993-10-22', 'rmurphy@email.com', '717-555-2019', '741 Ash Avenue', 'Camp Hill', 'PA', '17011', 'Sean Murphy', '717-555-2020', 1, 2, 'cancelled', DATE '2022-11-08');

-- Minor member with guardian (guardian is Jennifer Adams, member_id = 1)
INSERT INTO member (first_name, last_name, date_of_birth, email, phone, street_address, city, state, zip_code, emergency_contact_name, emergency_contact_phone, membership_type_id, home_location_id, status, join_date, guardian_member_id) VALUES
    ('Ethan', 'Adams', DATE '2008-09-30', 'eadams@email.com', '717-555-2021', '123 Oak Lane', 'Harrisburg', 'PA', '17101', 'Jennifer Adams', '717-555-2001', 1, 1, 'active', DATE '2024-01-15', 1);

-- Black Card "roamer" (uses other locations more than home)
INSERT INTO member (first_name, last_name, date_of_birth, email, phone, street_address, city, state, zip_code, emergency_contact_name, emergency_contact_phone, membership_type_id, home_location_id, status, join_date) VALUES
    ('Nicole', 'Foster', DATE '1986-01-19', 'nfoster@email.com', '717-555-2022', '852 Cherry Lane', 'Harrisburg', 'PA', '17101', 'Dan Foster', '717-555-2023', 2, 1, 'active', DATE '2023-04-02');

-- Member who never checked in
INSERT INTO member (first_name, last_name, date_of_birth, email, phone, street_address, city, state, zip_code, emergency_contact_name, emergency_contact_phone, membership_type_id, home_location_id, status, join_date) VALUES
    ('Christopher', 'Young', DATE '1994-05-11', 'cyoung@email.com', '717-555-2024', '963 Poplar Place', 'Mechanicsburg', 'PA', '17050', 'Linda Young', '717-555-2025', 1, 3, 'active', DATE '2024-03-01');


-- ------------------------------------------------------------
-- CHECK_INS
-- Variety of timestamps to support reporting scenarios
-- ------------------------------------------------------------

-- Jennifer Adams (member 1) - regular visitor at Downtown
INSERT INTO check_in (member_id, location_id, check_in_timestamp) VALUES (1, 1, SYSTIMESTAMP - INTERVAL '1' DAY + INTERVAL '6' HOUR);
INSERT INTO check_in (member_id, location_id, check_in_timestamp) VALUES (1, 1, SYSTIMESTAMP - INTERVAL '3' DAY + INTERVAL '7' HOUR);
INSERT INTO check_in (member_id, location_id, check_in_timestamp) VALUES (1, 1, SYSTIMESTAMP - INTERVAL '5' DAY + INTERVAL '6' HOUR);
INSERT INTO check_in (member_id, location_id, check_in_timestamp) VALUES (1, 1, SYSTIMESTAMP - INTERVAL '7' DAY + INTERVAL '17' HOUR);
INSERT INTO check_in (member_id, location_id, check_in_timestamp) VALUES (1, 1, SYSTIMESTAMP - INTERVAL '10' DAY + INTERVAL '6' HOUR);

-- Marcus Thompson (member 2) - regular at Camp Hill, occasional Downtown
INSERT INTO check_in (member_id, location_id, check_in_timestamp) VALUES (2, 2, SYSTIMESTAMP - INTERVAL '1' DAY + INTERVAL '18' HOUR);
INSERT INTO check_in (member_id, location_id, check_in_timestamp) VALUES (2, 2, SYSTIMESTAMP - INTERVAL '2' DAY + INTERVAL '17' HOUR);
INSERT INTO check_in (member_id, location_id, check_in_timestamp) VALUES (2, 1, SYSTIMESTAMP - INTERVAL '4' DAY + INTERVAL '12' HOUR);  -- visited Downtown
INSERT INTO check_in (member_id, location_id, check_in_timestamp) VALUES (2, 2, SYSTIMESTAMP - INTERVAL '6' DAY + INTERVAL '18' HOUR);

-- Priya Patel (member 3) - new member, few check-ins
INSERT INTO check_in (member_id, location_id, check_in_timestamp) VALUES (3, 3, SYSTIMESTAMP - INTERVAL '2' DAY + INTERVAL '8' HOUR);
INSERT INTO check_in (member_id, location_id, check_in_timestamp) VALUES (3, 3, SYSTIMESTAMP - INTERVAL '5' DAY + INTERVAL '9' HOUR);

-- David Chen (member 4) - Classic member, home gym only
INSERT INTO check_in (member_id, location_id, check_in_timestamp) VALUES (4, 1, SYSTIMESTAMP - INTERVAL '1' DAY + INTERVAL '17' HOUR);
INSERT INTO check_in (member_id, location_id, check_in_timestamp) VALUES (4, 1, SYSTIMESTAMP - INTERVAL '3' DAY + INTERVAL '18' HOUR);
INSERT INTO check_in (member_id, location_id, check_in_timestamp) VALUES (4, 1, SYSTIMESTAMP - INTERVAL '4' DAY + INTERVAL '17' HOUR);

-- Sarah O'Brien (member 5) - Classic member
INSERT INTO check_in (member_id, location_id, check_in_timestamp) VALUES (5, 2, SYSTIMESTAMP - INTERVAL '2' DAY + INTERVAL '7' HOUR);

-- Brian Williams (member 6) - ghost member, last check-in 90 days ago
INSERT INTO check_in (member_id, location_id, check_in_timestamp) VALUES (6, 1, SYSTIMESTAMP - INTERVAL '90' DAY + INTERVAL '10' HOUR);
INSERT INTO check_in (member_id, location_id, check_in_timestamp) VALUES (6, 1, SYSTIMESTAMP - INTERVAL '95' DAY + INTERVAL '11' HOUR);

-- Michelle Garcia (member 7) - ghost member, last check-in 75 days ago
INSERT INTO check_in (member_id, location_id, check_in_timestamp) VALUES (7, 2, SYSTIMESTAMP - INTERVAL '75' DAY + INTERVAL '16' HOUR);

-- Kevin Brown (member 8) - past due, recent check-ins before status change
INSERT INTO check_in (member_id, location_id, check_in_timestamp) VALUES (8, 3, SYSTIMESTAMP - INTERVAL '10' DAY + INTERVAL '7' HOUR);

-- Ethan Adams (member 11) - minor, accompanied visits
INSERT INTO check_in (member_id, location_id, check_in_timestamp) VALUES (11, 1, SYSTIMESTAMP - INTERVAL '3' DAY + INTERVAL '10' HOUR);
INSERT INTO check_in (member_id, location_id, check_in_timestamp) VALUES (11, 1, SYSTIMESTAMP - INTERVAL '7' DAY + INTERVAL '10' HOUR);

-- Nicole Foster (member 12) - "roamer" - uses Camp Hill more than home (Downtown)
INSERT INTO check_in (member_id, location_id, check_in_timestamp) VALUES (12, 1, SYSTIMESTAMP - INTERVAL '15' DAY + INTERVAL '7' HOUR);  -- home gym
INSERT INTO check_in (member_id, location_id, check_in_timestamp) VALUES (12, 2, SYSTIMESTAMP - INTERVAL '1' DAY + INTERVAL '6' HOUR);   -- Camp Hill
INSERT INTO check_in (member_id, location_id, check_in_timestamp) VALUES (12, 2, SYSTIMESTAMP - INTERVAL '3' DAY + INTERVAL '7' HOUR);   -- Camp Hill
INSERT INTO check_in (member_id, location_id, check_in_timestamp) VALUES (12, 2, SYSTIMESTAMP - INTERVAL '5' DAY + INTERVAL '6' HOUR);   -- Camp Hill
INSERT INTO check_in (member_id, location_id, check_in_timestamp) VALUES (12, 2, SYSTIMESTAMP - INTERVAL '8' DAY + INTERVAL '18' HOUR);  -- Camp Hill
INSERT INTO check_in (member_id, location_id, check_in_timestamp) VALUES (12, 3, SYSTIMESTAMP - INTERVAL '10' DAY + INTERVAL '12' HOUR); -- Mechanicsburg

-- Yesterday peak hour testing (5-8 PM at Downtown)
INSERT INTO check_in (member_id, location_id, check_in_timestamp) VALUES (1, 1, SYSTIMESTAMP - INTERVAL '1' DAY + INTERVAL '17' HOUR + INTERVAL '15' MINUTE);
INSERT INTO check_in (member_id, location_id, check_in_timestamp) VALUES (4, 1, SYSTIMESTAMP - INTERVAL '1' DAY + INTERVAL '17' HOUR + INTERVAL '30' MINUTE);
INSERT INTO check_in (member_id, location_id, check_in_timestamp) VALUES (6, 1, SYSTIMESTAMP - INTERVAL '1' DAY + INTERVAL '18' HOUR);


-- ------------------------------------------------------------
-- GUESTS
-- ------------------------------------------------------------
INSERT INTO guest (first_name, last_name, phone) VALUES ('Alex', 'Rivera', '717-555-3001');
INSERT INTO guest (first_name, last_name, phone) VALUES ('Jordan', 'Smith', '717-555-3002');
INSERT INTO guest (first_name, last_name, phone) VALUES ('Casey', 'Nguyen', '717-555-3003');
INSERT INTO guest (first_name, last_name, phone) VALUES ('Taylor', 'Jackson', '717-555-3004');


-- ------------------------------------------------------------
-- GUEST_VISITS
-- Scenarios: normal visits, repeat guest, monthly limit reached
-- ------------------------------------------------------------

-- Alex Rivera visited Downtown with Jennifer this month (normal visit)
INSERT INTO guest_visit (guest_id, member_id, location_id, visit_timestamp, waiver_signed, waiver_signed_date) 
VALUES (1, 1, 1, SYSTIMESTAMP - INTERVAL '5' DAY, 'Y', TRUNC(SYSDATE) - 5);

-- Jordan Smith visited Downtown with Jennifer earlier this month
-- Jordan CANNOT visit Downtown again this month
INSERT INTO guest_visit (guest_id, member_id, location_id, visit_timestamp, waiver_signed, waiver_signed_date) 
VALUES (2, 1, 1, SYSTIMESTAMP - INTERVAL '12' DAY, 'Y', TRUNC(SYSDATE) - 12);

-- But Jordan CAN visit Camp Hill (different location) - came with Marcus
INSERT INTO guest_visit (guest_id, member_id, location_id, visit_timestamp, waiver_signed, waiver_signed_date) 
VALUES (2, 2, 2, SYSTIMESTAMP - INTERVAL '3' DAY, 'Y', TRUNC(SYSDATE) - 3);

-- Casey Nguyen visited Camp Hill with Marcus
INSERT INTO guest_visit (guest_id, member_id, location_id, visit_timestamp, waiver_signed, waiver_signed_date) 
VALUES (3, 2, 2, SYSTIMESTAMP - INTERVAL '8' DAY, 'Y', TRUNC(SYSDATE) - 8);

-- Taylor Jackson visited last month (can visit again this month)
INSERT INTO guest_visit (guest_id, member_id, location_id, visit_timestamp, waiver_signed, waiver_signed_date) 
VALUES (4, 1, 1, ADD_MONTHS(SYSTIMESTAMP, -1) + INTERVAL '5' DAY, 'Y', ADD_MONTHS(TRUNC(SYSDATE), -1) + 5);


-- ============================================================
-- SECTION 4: COMMIT
-- ============================================================
COMMIT;


-- ============================================================
-- SECTION 5: VERIFICATION QUERIES
-- Run these to confirm data loaded correctly
-- ============================================================

-- Count records in each table
SELECT 'amenity' AS table_name, COUNT(*) AS record_count FROM amenity
UNION ALL SELECT 'location', COUNT(*) FROM location
UNION ALL SELECT 'location_amenity', COUNT(*) FROM location_amenity
UNION ALL SELECT 'membership_type', COUNT(*) FROM membership_type
UNION ALL SELECT 'staff', COUNT(*) FROM staff
UNION ALL SELECT 'staff_location', COUNT(*) FROM staff_location
UNION ALL SELECT 'member', COUNT(*) FROM member
UNION ALL SELECT 'check_in', COUNT(*) FROM check_in
UNION ALL SELECT 'guest', COUNT(*) FROM guest
UNION ALL SELECT 'guest_visit', COUNT(*) FROM guest_visit;


-- ============================================================
-- SECTION 6: SAMPLE REPORTING QUERIES
-- ============================================================

-- ------------------------------------------------------------
-- Report 1: Daily Check-In Summary (Yesterday)
-- ------------------------------------------------------------
SELECT 
    l.name AS location_name,
    COUNT(c.check_in_id) AS total_checkins,
    SUM(CASE WHEN mt.name = 'Classic' THEN 1 ELSE 0 END) AS classic_checkins,
    SUM(CASE WHEN mt.name = 'Black Card' THEN 1 ELSE 0 END) AS black_card_checkins
FROM location l
LEFT JOIN check_in c 
    ON l.location_id = c.location_id
    AND TRUNC(c.check_in_timestamp) = TRUNC(SYSDATE - 1)
LEFT JOIN member m ON c.member_id = m.member_id
LEFT JOIN membership_type mt ON m.membership_type_id = mt.membership_type_id
GROUP BY l.location_id, l.name
ORDER BY l.name;


-- ------------------------------------------------------------
-- Report 2: Ghost Member List (No Check-In in 60+ Days)
-- ------------------------------------------------------------
SELECT 
    m.member_id,
    m.first_name,
    m.last_name,
    m.email,
    m.phone,
    l.name AS home_location,
    mt.name AS membership_type,
    NVL(
        TO_CHAR(MAX(c.check_in_timestamp), 'YYYY-MM-DD'), 
        'Never'
    ) AS last_check_in
FROM member m
JOIN location l ON m.home_location_id = l.location_id
JOIN membership_type mt ON m.membership_type_id = mt.membership_type_id
LEFT JOIN check_in c ON m.member_id = c.member_id
WHERE m.status = 'active'
GROUP BY 
    m.member_id, 
    m.first_name, 
    m.last_name, 
    m.email, 
    m.phone, 
    l.name, 
    mt.name
HAVING 
    MAX(c.check_in_timestamp) < SYSDATE - 60
    OR MAX(c.check_in_timestamp) IS NULL
ORDER BY last_check_in ASC;


-- ------------------------------------------------------------
-- Report 3: Guest Visit Log (Current Month)
-- ------------------------------------------------------------
SELECT 
    g.first_name AS guest_first_name,
    g.last_name AS guest_last_name,
    g.phone AS guest_phone,
    l.name AS location_visited,
    TO_CHAR(gv.visit_timestamp, 'YYYY-MM-DD') AS visit_date,
    m.first_name || ' ' || m.last_name AS member_who_brought
FROM guest_visit gv
JOIN guest g ON gv.guest_id = g.guest_id
JOIN location l ON gv.location_id = l.location_id
JOIN member m ON gv.member_id = m.member_id
WHERE 
    TRUNC(gv.visit_timestamp, 'MM') = TRUNC(SYSDATE, 'MM')
ORDER BY gv.visit_timestamp DESC;


-- ------------------------------------------------------------
-- Report 4: Membership Breakdown by Location
-- ------------------------------------------------------------
SELECT 
    l.name AS location_name,
    COUNT(m.member_id) AS total_members,
    SUM(CASE WHEN mt.name = 'Classic' THEN 1 ELSE 0 END) AS classic_members,
    SUM(CASE WHEN mt.name = 'Black Card' THEN 1 ELSE 0 END) AS black_card_members,
    ROUND(
        100.0 * SUM(CASE WHEN mt.name = 'Black Card' THEN 1 ELSE 0 END) / 
        NULLIF(COUNT(m.member_id), 0), 
        1
    ) AS black_card_percentage
FROM location l
LEFT JOIN member m ON l.location_id = m.home_location_id
LEFT JOIN membership_type mt ON m.membership_type_id = mt.membership_type_id
GROUP BY l.location_id, l.name
ORDER BY total_members DESC;


-- ------------------------------------------------------------
-- Report 5: Black Card "Roamers" (Last 90 Days)
-- ------------------------------------------------------------
WITH member_checkins AS (
    SELECT 
        m.member_id,
        m.first_name,
        m.last_name,
        m.home_location_id,
        l_home.name AS home_location_name,
        SUM(CASE 
            WHEN c.location_id = m.home_location_id THEN 1 
            ELSE 0 
        END) AS home_checkins,
        SUM(CASE 
            WHEN c.location_id != m.home_location_id THEN 1 
            ELSE 0 
        END) AS other_checkins
    FROM member m
    JOIN membership_type mt ON m.membership_type_id = mt.membership_type_id
    JOIN location l_home ON m.home_location_id = l_home.location_id
    LEFT JOIN check_in c 
        ON m.member_id = c.member_id
        AND c.check_in_timestamp >= SYSDATE - 90
    WHERE mt.name = 'Black Card'
    GROUP BY 
        m.member_id, 
        m.first_name, 
        m.last_name, 
        m.home_location_id,
        l_home.name
)
SELECT 
    first_name,
    last_name,
    home_location_name,
    home_checkins,
    other_checkins
FROM member_checkins
WHERE other_checkins > home_checkins
ORDER BY other_checkins DESC;


-- ------------------------------------------------------------
-- Guest Monthly Limit Check (for Form 3)
-- Replace :guest_id and :location_id with actual values
-- ------------------------------------------------------------
/*
SELECT COUNT(*) AS visits_this_month
FROM guest_visit
WHERE guest_id = :guest_id
  AND location_id = :location_id
  AND TRUNC(visit_timestamp, 'MM') = TRUNC(SYSDATE, 'MM');
-- If result > 0, deny entry
*/