-- ============================================================
-- PLANET FITNESS MEMBER MANAGEMENT SYSTEM
-- Database Creation Script
-- 
-- Target Platform: Oracle APEX / Oracle Database
-- Version: 1.1 (Fixed for clean first-run)
-- 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.
--
-- Note: This script can be run multiple times without errors.
--       DROP statements silently skip tables that don't exist.
-- ============================================================


-- ============================================================
-- SECTION 1: DROP EXISTING OBJECTS (for clean re-runs)
-- ============================================================
-- Using PL/SQL blocks to avoid errors when tables don't exist

BEGIN EXECUTE IMMEDIATE 'DROP TABLE guest_visit CASCADE CONSTRAINTS'; EXCEPTION WHEN OTHERS THEN NULL; END;
/
BEGIN EXECUTE IMMEDIATE 'DROP TABLE check_in CASCADE CONSTRAINTS'; EXCEPTION WHEN OTHERS THEN NULL; END;
/
BEGIN EXECUTE IMMEDIATE 'DROP TABLE guest CASCADE CONSTRAINTS'; EXCEPTION WHEN OTHERS THEN NULL; END;
/
BEGIN EXECUTE IMMEDIATE 'DROP TABLE staff_location CASCADE CONSTRAINTS'; EXCEPTION WHEN OTHERS THEN NULL; END;
/
BEGIN EXECUTE IMMEDIATE 'DROP TABLE member CASCADE CONSTRAINTS'; EXCEPTION WHEN OTHERS THEN NULL; END;
/
BEGIN EXECUTE IMMEDIATE 'DROP TABLE membership_type CASCADE CONSTRAINTS'; EXCEPTION WHEN OTHERS THEN NULL; END;
/
BEGIN EXECUTE IMMEDIATE 'DROP TABLE location_amenity CASCADE CONSTRAINTS'; EXCEPTION WHEN OTHERS THEN NULL; END;
/
BEGIN EXECUTE IMMEDIATE 'DROP TABLE amenity CASCADE CONSTRAINTS'; EXCEPTION WHEN OTHERS THEN NULL; END;
/
BEGIN EXECUTE IMMEDIATE 'DROP TABLE location CASCADE CONSTRAINTS'; EXCEPTION WHEN OTHERS THEN NULL; END;
/
BEGIN EXECUTE IMMEDIATE 'DROP TABLE staff CASCADE CONSTRAINTS'; EXCEPTION WHEN OTHERS THEN NULL; END;
/


-- ============================================================
-- 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),
    closing_time        VARCHAR2(10),
    manager_id          NUMBER,
    
    CONSTRAINT location_name_unique UNIQUE (name),
    CONSTRAINT fk_location_manager 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 'General Manager for this location';


-- ------------------------------------------------------------
-- 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,
    notes               VARCHAR2(500),
    
    CONSTRAINT pk_location_amenity PRIMARY KEY (location_id, amenity_id),
    CONSTRAINT fk_locamen_location FOREIGN KEY (location_id) REFERENCES location(location_id),
    CONSTRAINT fk_locamen_amenity FOREIGN KEY (amenity_id) REFERENCES amenity(amenity_id)
);

COMMENT ON TABLE location_amenity IS 'Which amenities are available at each location (allows phased rollouts)';


-- ------------------------------------------------------------
-- MEMBERSHIP_TYPE
-- Defines membership tiers (Classic, Black Card)
-- ------------------------------------------------------------
CREATE TABLE membership_type (
    membership_type_id  NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name                VARCHAR2(50) NOT NULL,
    monthly_fee         NUMBER(10,2) NOT NULL,
    annual_fee          NUMBER(10,2) DEFAULT 49.00,
    guest_privileges    CHAR(1) DEFAULT 'N' CHECK (guest_privileges IN ('Y', 'N')),
    all_location_access CHAR(1) DEFAULT 'N' CHECK (all_location_access IN ('Y', 'N')),
    amenity_access      CHAR(1) DEFAULT 'N' CHECK (amenity_access IN ('Y', 'N')),
    
    CONSTRAINT membership_type_name_unique UNIQUE (name)
);

COMMENT ON TABLE membership_type IS 'Membership tiers with different pricing and privileges';
COMMENT ON COLUMN membership_type.guest_privileges IS 'Y = can bring guests; N = no guest privileges';
COMMENT ON COLUMN membership_type.all_location_access IS 'Y = any location; N = home gym only';
COMMENT ON COLUMN membership_type.amenity_access IS 'Y = can use Black Card amenities; N = no amenity access';


-- ------------------------------------------------------------
-- MEMBER
-- Gym members
-- ------------------------------------------------------------
CREATE TABLE member (
    member_id           NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    first_name          VARCHAR2(100) NOT NULL,
    last_name           VARCHAR2(100) NOT NULL,
    email               VARCHAR2(200) NOT NULL,
    phone               VARCHAR2(20),
    street_address      VARCHAR2(200),
    city                VARCHAR2(100),
    state               CHAR(2),
    zip_code            VARCHAR2(10),
    date_of_birth       DATE NOT NULL,
    emergency_contact   VARCHAR2(200),
    emergency_phone     VARCHAR2(20),
    membership_type_id  NUMBER NOT NULL,
    home_location_id    NUMBER NOT NULL,
    join_date           DATE DEFAULT SYSDATE,
    status              VARCHAR2(20) DEFAULT 'Active',
    
    CONSTRAINT member_email_unique UNIQUE (email),
    CONSTRAINT member_status_check CHECK (status IN ('Active', 'Frozen', 'Cancelled', 'Past Due')),
    CONSTRAINT fk_member_type FOREIGN KEY (membership_type_id) REFERENCES membership_type(membership_type_id),
    CONSTRAINT fk_member_home_location FOREIGN KEY (home_location_id) REFERENCES location(location_id)
);

COMMENT ON TABLE member IS 'Planet Fitness gym members';
COMMENT ON COLUMN member.home_location_id IS 'Primary gym location for this member';
COMMENT ON COLUMN member.status IS 'Active, Frozen (up to 3 months), Cancelled, or Past Due';


-- ------------------------------------------------------------
-- STAFF_LOCATION
-- Junction table: staff can work at multiple locations
-- ------------------------------------------------------------
CREATE TABLE staff_location (
    staff_id            NUMBER NOT NULL,
    location_id         NUMBER NOT NULL,
    primary_location    CHAR(1) DEFAULT 'N' CHECK (primary_location IN ('Y', 'N')),
    
    CONSTRAINT pk_staff_location PRIMARY KEY (staff_id, location_id),
    CONSTRAINT fk_staffloc_staff FOREIGN KEY (staff_id) REFERENCES staff(staff_id),
    CONSTRAINT fk_staffloc_location FOREIGN KEY (location_id) REFERENCES location(location_id)
);

COMMENT ON TABLE staff_location IS 'Which staff members work at which locations';


-- ------------------------------------------------------------
-- CHECK_IN
-- Records every member check-in at any location
-- ------------------------------------------------------------
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_time       TIMESTAMP DEFAULT SYSTIMESTAMP NOT NULL,
    flagged             CHAR(1) DEFAULT 'N' CHECK (flagged IN ('Y', 'N')),
    flag_reason         VARCHAR2(200),
    
    CONSTRAINT fk_checkin_member FOREIGN KEY (member_id) REFERENCES member(member_id),
    CONSTRAINT fk_checkin_location FOREIGN KEY (location_id) REFERENCES location(location_id)
);

COMMENT ON TABLE check_in IS 'Every member check-in transaction';
COMMENT ON COLUMN check_in.flagged IS 'Y = check-in violated a rule (e.g., Classic at wrong location)';
COMMENT ON COLUMN check_in.flag_reason IS 'Explanation if flagged (e.g., "Classic member at non-home location")';

-- Index for finding recent check-ins by member (ghost member reports)
CREATE INDEX idx_checkin_member_time ON check_in(member_id, check_in_time);

-- Index for peak hours analysis by location
CREATE INDEX idx_checkin_location_time ON check_in(location_id, check_in_time);


-- ------------------------------------------------------------
-- GUEST
-- Non-members who visit as guests of Black Card holders
-- ------------------------------------------------------------
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,
    waiver_signed       CHAR(1) DEFAULT 'N' CHECK (waiver_signed IN ('Y', 'N')),
    waiver_date         DATE,
    
    CONSTRAINT guest_phone_unique UNIQUE (phone)
);

COMMENT ON TABLE guest IS 'Non-members who visit as guests of Black Card members';
COMMENT ON COLUMN guest.phone IS 'Used as identifier to enforce once-per-location-per-month rule';


-- ------------------------------------------------------------
-- GUEST_VISIT
-- Records guest visits (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_date          DATE DEFAULT SYSDATE NOT NULL,
    
    CONSTRAINT fk_guestvisit_guest FOREIGN KEY (guest_id) REFERENCES guest(guest_id),
    CONSTRAINT fk_guestvisit_member FOREIGN KEY (member_id) REFERENCES member(member_id),
    CONSTRAINT fk_guestvisit_location FOREIGN KEY (location_id) REFERENCES location(location_id)
);

COMMENT ON TABLE guest_visit IS 'Guest visit transactions - used to enforce once-per-location-per-month';

-- Index for checking if guest visited this location this month
CREATE INDEX idx_guestvisit_guest_loc_date ON guest_visit(guest_id, location_id, visit_date);


-- ============================================================
-- SECTION 3: INSERT SEED DATA
-- ============================================================

-- ------------------------------------------------------------
-- AMENITIES
-- ------------------------------------------------------------
INSERT INTO amenity (name, description, black_card_only) VALUES 
    ('Hydromassage Bed', 'Water-based massage lounger for post-workout recovery', '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 
    ('Tanning Booth', 'UV tanning booths (limit 1 session per day)', 'Y');
INSERT INTO amenity (name, description, black_card_only) VALUES 
    ('Total Body Enhancement', 'Red light therapy booth', 'Y');


-- ------------------------------------------------------------
-- STAFF
-- ------------------------------------------------------------
INSERT INTO staff (first_name, last_name, email, phone, role, hire_date) VALUES 
    ('Marcus', 'Johnson', 'marcus.johnson@pf-harrisburg.com', '717-555-0101', 'Manager', DATE '2022-03-15');
INSERT INTO staff (first_name, last_name, email, phone, role, hire_date) VALUES 
    ('Sarah', 'Chen', 'sarah.chen@pf-harrisburg.com', '717-555-0102', 'Manager', DATE '2023-01-10');
INSERT INTO staff (first_name, last_name, email, phone, role, hire_date) VALUES 
    ('David', 'Williams', 'david.williams@pf-harrisburg.com', '717-555-0103', 'Manager', DATE '2023-06-01');
INSERT INTO staff (first_name, last_name, email, phone, role, hire_date) VALUES 
    ('Emily', 'Rodriguez', 'emily.rodriguez@pf-harrisburg.com', '717-555-0104', 'Front Desk', DATE '2024-02-20');
INSERT INTO staff (first_name, last_name, email, phone, role, hire_date) VALUES 
    ('James', 'Taylor', 'james.taylor@pf-harrisburg.com', '717-555-0105', 'Front Desk', DATE '2024-05-15');
INSERT INTO staff (first_name, last_name, email, phone, role, hire_date) VALUES 
    ('Maria', 'Garcia', 'maria.garcia@pf-harrisburg.com', '717-555-0106', 'Trainer', DATE '2023-09-01');
INSERT INTO staff (first_name, last_name, email, phone, role, hire_date) VALUES 
    ('Kevin', 'Brown', 'kevin.brown@pf-harrisburg.com', '717-555-0107', 'Maintenance', DATE '2024-01-08');


-- ------------------------------------------------------------
-- LOCATIONS
-- ------------------------------------------------------------
INSERT INTO location (name, street_address, city, state, zip_code, phone, opening_time, closing_time, manager_id) VALUES 
    ('Planet Fitness - Downtown Harrisburg', '100 Market Street', 'Harrisburg', 'PA', '17101', '717-555-1000', '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', '3401 Simpson Ferry Rd', 'Camp Hill', 'PA', '17011', '717-555-2000', '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-3000', '6:00 AM', '10:00 PM', 3);


-- ------------------------------------------------------------
-- LOCATION_AMENITY (which amenities are at which locations)
-- Downtown has all amenities
-- Camp Hill has all except tanning (coming soon)
-- Mechanicsburg has only massage chairs and hydromassage
-- ------------------------------------------------------------
INSERT INTO location_amenity (location_id, amenity_id, available_date, notes) VALUES 
    (1, 1, DATE '2022-04-01', NULL);
INSERT INTO location_amenity (location_id, amenity_id, available_date, notes) VALUES 
    (1, 2, DATE '2022-04-01', NULL);
INSERT INTO location_amenity (location_id, amenity_id, available_date, notes) VALUES 
    (1, 3, DATE '2022-04-01', NULL);
INSERT INTO location_amenity (location_id, amenity_id, available_date, notes) VALUES 
    (1, 4, DATE '2022-06-15', 'Added after initial opening');
INSERT INTO location_amenity (location_id, amenity_id, available_date, notes) VALUES 
    (2, 1, DATE '2023-02-01', NULL);
INSERT INTO location_amenity (location_id, amenity_id, available_date, notes) VALUES 
    (2, 2, DATE '2023-02-01', NULL);
INSERT INTO location_amenity (location_id, amenity_id, available_date, notes) VALUES 
    (2, 4, DATE '2023-02-01', NULL);
INSERT INTO location_amenity (location_id, amenity_id, available_date, notes) VALUES 
    (3, 1, DATE '2023-07-01', NULL);
INSERT INTO location_amenity (location_id, amenity_id, available_date, notes) VALUES 
    (3, 2, DATE '2023-07-01', NULL);


-- ------------------------------------------------------------
-- STAFF_LOCATION (staff assignments)
-- ------------------------------------------------------------
INSERT INTO staff_location (staff_id, location_id, primary_location) VALUES (1, 1, 'Y');
INSERT INTO staff_location (staff_id, location_id, primary_location) VALUES (2, 2, 'Y');
INSERT INTO staff_location (staff_id, location_id, primary_location) VALUES (3, 3, 'Y');
INSERT INTO staff_location (staff_id, location_id, primary_location) VALUES (4, 1, 'Y');
INSERT INTO staff_location (staff_id, location_id, primary_location) VALUES (4, 2, 'N');
INSERT INTO staff_location (staff_id, location_id, primary_location) VALUES (5, 2, 'Y');
INSERT INTO staff_location (staff_id, location_id, primary_location) VALUES (5, 3, 'N');
INSERT INTO staff_location (staff_id, location_id, primary_location) VALUES (6, 1, 'Y');
INSERT INTO staff_location (staff_id, location_id, primary_location) VALUES (6, 2, 'N');
INSERT INTO staff_location (staff_id, location_id, primary_location) VALUES (6, 3, 'N');
INSERT INTO staff_location (staff_id, location_id, primary_location) VALUES (7, 1, 'N');
INSERT INTO staff_location (staff_id, location_id, primary_location) VALUES (7, 2, 'N');
INSERT INTO staff_location (staff_id, location_id, primary_location) VALUES (7, 3, 'Y');


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


-- ------------------------------------------------------------
-- MEMBERS (mix of Classic and Black Card, various statuses)
-- ------------------------------------------------------------
-- Active Black Card members
INSERT INTO member (first_name, last_name, email, phone, street_address, city, state, zip_code, date_of_birth, emergency_contact, emergency_phone, membership_type_id, home_location_id, join_date, status) VALUES 
    ('Jennifer', 'Adams', 'jadams@email.com', '717-555-2001', '123 Oak Lane', 'Harrisburg', 'PA', '17102', DATE '1988-05-15', 'Mike Adams', '717-555-2002', 2, 1, DATE '2023-01-15', 'Active');
INSERT INTO member (first_name, last_name, email, phone, street_address, city, state, zip_code, date_of_birth, emergency_contact, emergency_phone, membership_type_id, home_location_id, join_date, status) VALUES 
    ('Robert', 'Martinez', 'rmartinez@email.com', '717-555-2003', '456 Pine Street', 'Camp Hill', 'PA', '17011', DATE '1992-08-22', 'Ana Martinez', '717-555-2004', 2, 2, DATE '2023-03-20', 'Active');
INSERT INTO member (first_name, last_name, email, phone, street_address, city, state, zip_code, date_of_birth, emergency_contact, emergency_phone, membership_type_id, home_location_id, join_date, status) VALUES 
    ('Michelle', 'Thompson', 'mthompson@email.com', '717-555-2005', '789 Maple Ave', 'Mechanicsburg', 'PA', '17050', DATE '1985-11-03', 'Tom Thompson', '717-555-2006', 2, 3, DATE '2023-06-10', 'Active');

-- Active Classic members
INSERT INTO member (first_name, last_name, email, phone, street_address, city, state, zip_code, date_of_birth, emergency_contact, emergency_phone, membership_type_id, home_location_id, join_date, status) VALUES 
    ('Brian', 'Wilson', 'bwilson@email.com', '717-555-2007', '321 Elm Drive', 'Harrisburg', 'PA', '17103', DATE '1995-02-28', 'Linda Wilson', '717-555-2008', 1, 1, DATE '2024-01-05', 'Active');
INSERT INTO member (first_name, last_name, email, phone, street_address, city, state, zip_code, date_of_birth, emergency_contact, emergency_phone, membership_type_id, home_location_id, join_date, status) VALUES 
    ('Amanda', 'Davis', 'adavis@email.com', '717-555-2009', '654 Cedar Blvd', 'Camp Hill', 'PA', '17011', DATE '1990-07-14', 'Chris Davis', '717-555-2010', 1, 2, DATE '2024-02-18', 'Active');
INSERT INTO member (first_name, last_name, email, phone, street_address, city, state, zip_code, date_of_birth, emergency_contact, emergency_phone, membership_type_id, home_location_id, join_date, status) VALUES 
    ('Daniel', 'Lee', 'dlee@email.com', '717-555-2011', '987 Birch Road', 'Mechanicsburg', 'PA', '17050', DATE '1998-12-01', 'Susan Lee', '717-555-2012', 1, 3, DATE '2024-03-25', 'Active');

-- Ghost members (Active but haven't visited in 90+ days)
INSERT INTO member (first_name, last_name, email, phone, street_address, city, state, zip_code, date_of_birth, emergency_contact, emergency_phone, membership_type_id, home_location_id, join_date, status) VALUES 
    ('Patricia', 'Moore', 'pmoore@email.com', '717-555-2013', '111 Ghost Lane', 'Harrisburg', 'PA', '17101', DATE '1987-04-20', 'John Moore', '717-555-2014', 2, 1, DATE '2023-08-01', 'Active');
INSERT INTO member (first_name, last_name, email, phone, street_address, city, state, zip_code, date_of_birth, emergency_contact, emergency_phone, membership_type_id, home_location_id, join_date, status) VALUES 
    ('Steven', 'Clark', 'sclark@email.com', '717-555-2015', '222 Absent Ave', 'Camp Hill', 'PA', '17011', DATE '1993-09-12', 'Mary Clark', '717-555-2016', 1, 2, DATE '2024-01-10', 'Active');

-- Frozen member
INSERT INTO member (first_name, last_name, email, phone, street_address, city, state, zip_code, date_of_birth, emergency_contact, emergency_phone, membership_type_id, home_location_id, join_date, status) VALUES 
    ('Karen', 'Hall', 'khall@email.com', '717-555-2017', '333 Freeze Street', 'Harrisburg', 'PA', '17102', DATE '1991-06-25', 'Bill Hall', '717-555-2018', 2, 1, DATE '2023-05-15', 'Frozen');

-- Past Due member
INSERT INTO member (first_name, last_name, email, phone, street_address, city, state, zip_code, date_of_birth, emergency_contact, emergency_phone, membership_type_id, home_location_id, join_date, status) VALUES 
    ('George', 'Young', 'gyoung@email.com', '717-555-2019', '444 Overdue Blvd', 'Mechanicsburg', 'PA', '17050', DATE '1989-01-30', 'Nancy Young', '717-555-2020', 1, 3, DATE '2023-11-20', 'Past Due');


-- ------------------------------------------------------------
-- GUESTS
-- ------------------------------------------------------------
INSERT INTO guest (first_name, last_name, phone, waiver_signed, waiver_date) VALUES 
    ('Alex', 'Reynolds', '717-555-3001', 'Y', DATE '2024-11-15');
INSERT INTO guest (first_name, last_name, phone, waiver_signed, waiver_date) VALUES 
    ('Samantha', 'Brooks', '717-555-3002', 'Y', DATE '2024-12-01');
INSERT INTO guest (first_name, last_name, phone, waiver_signed, waiver_date) VALUES 
    ('Tyler', 'Morgan', '717-555-3003', 'Y', DATE '2024-12-10');
INSERT INTO guest (first_name, last_name, phone, waiver_signed, waiver_date) VALUES 
    ('Jessica', 'Foster', '717-555-3004', 'Y', DATE '2025-01-05');


-- ------------------------------------------------------------
-- CHECK-INS (recent activity for active members)
-- Using SYSDATE-based dates for realistic "recent" data
-- ------------------------------------------------------------
-- Jennifer Adams (Black Card, Downtown home) - regular visitor, visits multiple locations
INSERT INTO check_in (member_id, location_id, check_in_time, flagged, flag_reason) VALUES 
    (1, 1, SYSTIMESTAMP - INTERVAL '30' DAY, 'N', NULL);
INSERT INTO check_in (member_id, location_id, check_in_time, flagged, flag_reason) VALUES 
    (1, 1, SYSTIMESTAMP - INTERVAL '27' DAY, 'N', NULL);
INSERT INTO check_in (member_id, location_id, check_in_time, flagged, flag_reason) VALUES 
    (1, 2, SYSTIMESTAMP - INTERVAL '25' DAY, 'N', NULL);
INSERT INTO check_in (member_id, location_id, check_in_time, flagged, flag_reason) VALUES 
    (1, 1, SYSTIMESTAMP - INTERVAL '23' DAY, 'N', NULL);
INSERT INTO check_in (member_id, location_id, check_in_time, flagged, flag_reason) VALUES 
    (1, 1, SYSTIMESTAMP - INTERVAL '20' DAY, 'N', NULL);
INSERT INTO check_in (member_id, location_id, check_in_time, flagged, flag_reason) VALUES 
    (1, 3, SYSTIMESTAMP - INTERVAL '18' DAY, 'N', NULL);
INSERT INTO check_in (member_id, location_id, check_in_time, flagged, flag_reason) VALUES 
    (1, 1, SYSTIMESTAMP - INTERVAL '15' DAY, 'N', NULL);
INSERT INTO check_in (member_id, location_id, check_in_time, flagged, flag_reason) VALUES 
    (1, 1, SYSTIMESTAMP - INTERVAL '12' DAY, 'N', NULL);
INSERT INTO check_in (member_id, location_id, check_in_time, flagged, flag_reason) VALUES 
    (1, 1, SYSTIMESTAMP - INTERVAL '10' DAY, 'N', NULL);
INSERT INTO check_in (member_id, location_id, check_in_time, flagged, flag_reason) VALUES 
    (1, 1, SYSTIMESTAMP - INTERVAL '7' DAY, 'N', NULL);
INSERT INTO check_in (member_id, location_id, check_in_time, flagged, flag_reason) VALUES 
    (1, 1, SYSTIMESTAMP - INTERVAL '4' DAY, 'N', NULL);
INSERT INTO check_in (member_id, location_id, check_in_time, flagged, flag_reason) VALUES 
    (1, 1, SYSTIMESTAMP - INTERVAL '1' DAY, 'N', NULL);

-- Robert Martinez (Black Card, Camp Hill home) - moderate visitor
INSERT INTO check_in (member_id, location_id, check_in_time, flagged, flag_reason) VALUES 
    (2, 2, SYSTIMESTAMP - INTERVAL '28' DAY, 'N', NULL);
INSERT INTO check_in (member_id, location_id, check_in_time, flagged, flag_reason) VALUES 
    (2, 2, SYSTIMESTAMP - INTERVAL '21' DAY, 'N', NULL);
INSERT INTO check_in (member_id, location_id, check_in_time, flagged, flag_reason) VALUES 
    (2, 1, SYSTIMESTAMP - INTERVAL '14' DAY, 'N', NULL);
INSERT INTO check_in (member_id, location_id, check_in_time, flagged, flag_reason) VALUES 
    (2, 2, SYSTIMESTAMP - INTERVAL '7' DAY, 'N', NULL);
INSERT INTO check_in (member_id, location_id, check_in_time, flagged, flag_reason) VALUES 
    (2, 2, SYSTIMESTAMP - INTERVAL '2' DAY, 'N', NULL);

-- Michelle Thompson (Black Card, Mechanicsburg home)
INSERT INTO check_in (member_id, location_id, check_in_time, flagged, flag_reason) VALUES 
    (3, 3, SYSTIMESTAMP - INTERVAL '25' DAY, 'N', NULL);
INSERT INTO check_in (member_id, location_id, check_in_time, flagged, flag_reason) VALUES 
    (3, 3, SYSTIMESTAMP - INTERVAL '18' DAY, 'N', NULL);
INSERT INTO check_in (member_id, location_id, check_in_time, flagged, flag_reason) VALUES 
    (3, 3, SYSTIMESTAMP - INTERVAL '11' DAY, 'N', NULL);
INSERT INTO check_in (member_id, location_id, check_in_time, flagged, flag_reason) VALUES 
    (3, 3, SYSTIMESTAMP - INTERVAL '4' DAY, 'N', NULL);

-- Brian Wilson (Classic, Downtown home) - should only be at Downtown
INSERT INTO check_in (member_id, location_id, check_in_time, flagged, flag_reason) VALUES 
    (4, 1, SYSTIMESTAMP - INTERVAL '20' DAY, 'N', NULL);
INSERT INTO check_in (member_id, location_id, check_in_time, flagged, flag_reason) VALUES 
    (4, 1, SYSTIMESTAMP - INTERVAL '13' DAY, 'N', NULL);
INSERT INTO check_in (member_id, location_id, check_in_time, flagged, flag_reason) VALUES 
    (4, 1, SYSTIMESTAMP - INTERVAL '6' DAY, 'N', NULL);
-- FLAGGED: Classic member tried to use Camp Hill (not home location)
INSERT INTO check_in (member_id, location_id, check_in_time, flagged, flag_reason) VALUES 
    (4, 2, SYSTIMESTAMP - INTERVAL '3' DAY, 'Y', 'Classic member at non-home location');

-- Amanda Davis (Classic, Camp Hill home)
INSERT INTO check_in (member_id, location_id, check_in_time, flagged, flag_reason) VALUES 
    (5, 2, SYSTIMESTAMP - INTERVAL '22' DAY, 'N', NULL);
INSERT INTO check_in (member_id, location_id, check_in_time, flagged, flag_reason) VALUES 
    (5, 2, SYSTIMESTAMP - INTERVAL '15' DAY, 'N', NULL);
INSERT INTO check_in (member_id, location_id, check_in_time, flagged, flag_reason) VALUES 
    (5, 2, SYSTIMESTAMP - INTERVAL '8' DAY, 'N', NULL);
INSERT INTO check_in (member_id, location_id, check_in_time, flagged, flag_reason) VALUES 
    (5, 2, SYSTIMESTAMP - INTERVAL '1' DAY, 'N', NULL);

-- Daniel Lee (Classic, Mechanicsburg home)
INSERT INTO check_in (member_id, location_id, check_in_time, flagged, flag_reason) VALUES 
    (6, 3, SYSTIMESTAMP - INTERVAL '19' DAY, 'N', NULL);
INSERT INTO check_in (member_id, location_id, check_in_time, flagged, flag_reason) VALUES 
    (6, 3, SYSTIMESTAMP - INTERVAL '12' DAY, 'N', NULL);
INSERT INTO check_in (member_id, location_id, check_in_time, flagged, flag_reason) VALUES 
    (6, 3, SYSTIMESTAMP - INTERVAL '5' DAY, 'N', NULL);

-- Patricia Moore (Ghost member - no check-ins in 90+ days)
INSERT INTO check_in (member_id, location_id, check_in_time, flagged, flag_reason) VALUES 
    (7, 1, SYSTIMESTAMP - INTERVAL '120' DAY, 'N', NULL);
INSERT INTO check_in (member_id, location_id, check_in_time, flagged, flag_reason) VALUES 
    (7, 1, SYSTIMESTAMP - INTERVAL '115' DAY, 'N', NULL);

-- Steven Clark (Ghost member - no check-ins in 90+ days)
INSERT INTO check_in (member_id, location_id, check_in_time, flagged, flag_reason) VALUES 
    (8, 2, SYSTIMESTAMP - INTERVAL '100' DAY, 'N', NULL);


-- ------------------------------------------------------------
-- GUEST VISITS
-- ------------------------------------------------------------
-- Alex Reynolds visited Downtown with Jennifer Adams
INSERT INTO guest_visit (guest_id, member_id, location_id, visit_date) VALUES 
    (1, 1, 1, SYSDATE - 15);

-- Samantha Brooks visited Camp Hill with Robert Martinez
INSERT INTO guest_visit (guest_id, member_id, location_id, visit_date) VALUES 
    (2, 2, 2, SYSDATE - 10);

-- Tyler Morgan visited Downtown with Jennifer Adams, then tried same location again (should be blocked)
INSERT INTO guest_visit (guest_id, member_id, location_id, visit_date) VALUES 
    (3, 1, 1, SYSDATE - 20);
-- This second visit at a DIFFERENT location is allowed
INSERT INTO guest_visit (guest_id, member_id, location_id, visit_date) VALUES 
    (3, 2, 2, SYSDATE - 8);

-- Jessica Foster - multiple visits demonstrating the abuse scenario
INSERT INTO guest_visit (guest_id, member_id, location_id, visit_date) VALUES 
    (4, 1, 1, SYSDATE - 25);
INSERT INTO guest_visit (guest_id, member_id, location_id, visit_date) VALUES 
    (4, 2, 2, SYSDATE - 18);
INSERT INTO guest_visit (guest_id, member_id, location_id, visit_date) VALUES 
    (4, 3, 3, SYSDATE - 12);


-- ============================================================
-- SECTION 4: SAMPLE QUERIES FOR TESTING
-- ============================================================

-- These queries demonstrate the database solving Jen's problems.
-- Run them after the data loads to verify everything works.

/*
-- QUERY 1: Find Ghost Members (no check-in in 90+ days)
-- Solves: "Who should we call for re-engagement?"

SELECT 
    m.member_id,
    m.first_name || ' ' || m.last_name AS member_name,
    m.email,
    m.phone,
    mt.name AS membership_type,
    l.name AS home_location,
    MAX(c.check_in_time) AS last_visit,
    ROUND(SYSDATE - MAX(c.check_in_time)) AS days_since_visit
FROM member m
JOIN membership_type mt ON m.membership_type_id = mt.membership_type_id
JOIN location l ON m.home_location_id = l.location_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, mt.name, l.name
HAVING MAX(c.check_in_time) < SYSDATE - 90 OR MAX(c.check_in_time) IS NULL
ORDER BY days_since_visit DESC;


-- QUERY 2: Check if Guest Can Visit Location Today
-- Solves: "Has this guest already visited this location this month?"
-- Replace guest_id and location_id with actual values

SELECT 
    g.first_name || ' ' || g.last_name AS guest_name,
    l.name AS location,
    gv.visit_date AS last_visit_here,
    CASE 
        WHEN gv.visit_date >= TRUNC(SYSDATE, 'MM') THEN 'BLOCKED - Already visited this month'
        ELSE 'ALLOWED'
    END AS visit_status
FROM guest g
CROSS JOIN location l
LEFT JOIN guest_visit gv ON g.guest_id = gv.guest_id 
    AND l.location_id = gv.location_id
    AND gv.visit_date >= TRUNC(SYSDATE, 'MM')
WHERE g.guest_id = 3 AND l.location_id = 1;


-- QUERY 3: Flagged Check-ins (Classic members at wrong locations)
-- Solves: "Who's been sneaking into other gyms?"

SELECT 
    m.first_name || ' ' || m.last_name AS member_name,
    mt.name AS membership_type,
    home.name AS home_location,
    visited.name AS visited_location,
    c.check_in_time,
    c.flag_reason
FROM check_in c
JOIN member m ON c.member_id = m.member_id
JOIN membership_type mt ON m.membership_type_id = mt.membership_type_id
JOIN location home ON m.home_location_id = home.location_id
JOIN location visited ON c.location_id = visited.location_id
WHERE c.flagged = 'Y'
ORDER BY c.check_in_time DESC;


-- QUERY 4: Peak Hours by Location (for staffing)
-- Solves: "When should we schedule more staff?"

SELECT 
    l.name AS location,
    TO_CHAR(c.check_in_time, 'DY') AS day_of_week,
    TO_CHAR(c.check_in_time, 'HH24') || ':00' AS hour,
    COUNT(*) AS check_ins
FROM check_in c
JOIN location l ON c.location_id = l.location_id
WHERE c.check_in_time >= SYSDATE - 30
GROUP BY l.name, TO_CHAR(c.check_in_time, 'DY'), TO_CHAR(c.check_in_time, 'HH24')
ORDER BY l.name, COUNT(*) DESC;


-- QUERY 5: Guest Visit Frequency (abuse detection)
-- Solves: "Who's gaming the guest policy?"

SELECT 
    g.first_name || ' ' || g.last_name AS guest_name,
    g.phone,
    COUNT(*) AS total_visits,
    COUNT(DISTINCT gv.location_id) AS locations_visited,
    MIN(gv.visit_date) AS first_visit,
    MAX(gv.visit_date) AS last_visit
FROM guest g
JOIN guest_visit gv ON g.guest_id = gv.guest_id
GROUP BY g.guest_id, g.first_name, g.last_name, g.phone
HAVING COUNT(*) > 2
ORDER BY total_visits DESC;

*/

-- ============================================================
-- END OF SCRIPT
-- ============================================================