---

-- ================================================================
-- SQL DDL TEMPLATE (TOPIC 04)
-- ================================================================
-- WHAT SHOULD BE ADDED HERE:
-- 1) Full PostgreSQL DDL for your finalized schema.
-- 2) CREATE TABLE statements for all entities from your ER diagram.
-- 3) Primary keys, foreign keys, NOT NULL, UNIQUE, CHECK constraints.
-- 4) Indexes for important search/join columns.
-- 5) Clean structure and comments (group by tables/constraints/indexes).
--
-- RECOMMENDED ORDER:
-- 1) Tables
-- 2) Constraints (if not inline)
-- 3) Indexes
--
-- TEAM NOTE:
-- Add short attribution comments for who implemented which part.
-- Example:
-- [Name] - users, roles, permissions tables
-- [Name] - orders, payments, invoices tables
--
-- IMPORTANT:
-- The script must run in PostgreSQL and produce a working schema that
-- matches your approved ER diagram and conceptual schema.
-- Submit this as one SQL file.
-- ================================================================

-- Add your DDL below this line

CREATE SCHEMA FitServe;

-- ENUMs
CREATE TYPE FitServe.sex_type AS ENUM ( -- Oleksii
  'Male',
  'Female',
  'Other'
);

CREATE TYPE FitServe.role_type AS ENUM ( -- Oleksii
  'Administrator',
  'Athlete',
  'Trainer'
);

CREATE TYPE FitServe.achievement_rate_type AS ENUM ( -- Oleksii
  'Bronze', 
  'Silver', 
  'Gold', 
  'Platinum', 
  'Diamond'
);

CREATE TYPE FitServe.interest_rate_type AS ENUM ( -- Oleksii
  'Bronze', 
  'Silver', 
  'Gold', 
  'Platinum', 
  'Diamond'
);

CREATE TYPE FitServe.contract_type_enum AS ENUM ( -- Oleksii
  'individual', 
  'salaried', 
  'both'
);

CREATE TYPE FitServe.severity_level AS ENUM ( -- Oleksii
  'mild', 
  'moderate', 
  'severe' 
);

CREATE TYPE FitServe.studio_name_enum AS ENUM ( -- lily
  'aerobics',
  'yoga',
  'pilates',
  'weightlifting',
  'cycling',
  'powerlifting',
  'boxing',
  'swimming_pool',
  'gymnastics'
);

CREATE TYPE FitServe.booking_status_enum AS ENUM ( -- lily
  'planned',
  'confirmed',
  'completed',
  'cancelled'
);

CREATE TYPE FitServe.status_type AS ENUM ( -- Oleksii
  'ready', 
  'repairing', 
  'occupied'
);

CREATE TYPE FitServe.warranty_type AS ENUM ( -- Oleksii
  'full', 
  'partial'
);

CREATE TYPE FitServe.payment_status_type AS ENUM ( -- Oleksii
  'unpaid', 
  'in_process', 
  'completed'
);


-- TABLEs
CREATE TABLE FitServe.user_info ( -- Oleksii
  user_info_id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,

  registration_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

  first_name VARCHAR(50) NOT NULL,
  last_name VARCHAR(50) NOT NULL,
  date_of_birth DATE NOT NULL,
  phone_number VARCHAR(20) NOT NULL UNIQUE,

  sex FitServe.sex_type NOT NULL,

  preferred_pronouns VARCHAR(20),
  address VARCHAR(255) NOT NULL,

  CONSTRAINT chk_user_info_minimum_age
    CHECK (
      registration_date >= date_of_birth + INTERVAL '14 years'
    ) -- lily-CHECK
);

CREATE TABLE FitServe.users ( -- Oleksii
  user_id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,

  role FitServe.role_type NOT NULL,
  login varchar(255) NOT NULL UNIQUE,
  password_hash varchar(1024) NOT NULL,
  user_info_id INTEGER,

  CONSTRAINT uq_users_user_info_role -- lily-UNIQUE 
    UNIQUE (user_info_id, role),
  FOREIGN KEY (user_info_id) REFERENCES FitServe.user_info(user_info_id)
);

CREATE TABLE FitServe.interest_rate ( -- Oleksii
  interest_rate_id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,

  trainer_share_rate NUMERIC(5, 2) NOT NULL,
  trainer_rate FitServe.interest_rate_type NOT NULL,

  CONSTRAINT chk_interest_rate_share_range
   CHECK (trainer_share_rate BETWEEN 0 AND 100) -- lily-CHECK
);

CREATE TABLE FitServe.trainers ( -- Oleksii
  trainer_id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,

  contract_type FitServe.contract_type_enum NOT NULL,

  user_id INTEGER NOT NULL UNIQUE,
  interest_rate_id INTEGER UNIQUE,

  FOREIGN KEY (user_id) REFERENCES FitServe.users(user_id),
  FOREIGN KEY (interest_rate_id) REFERENCES FitServe.interest_rate(interest_rate_id)
);

CREATE TABLE FitServe.trainer_specialization ( -- Oleksii
  trainer_specialization_id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,

  specialization_name VARCHAR(100) NOT NULL,

  trainer_id INTEGER NOT NULL,

  FOREIGN KEY (trainer_id) REFERENCES FitServe.trainers(trainer_id)
);

CREATE TABLE FitServe.certifications ( -- Oleksii
  certification_id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,

  certification_link VARCHAR(500) NOT NULL UNIQUE,
  receiving_date DATE,

  trainer_specialization_id INTEGER NOT NULL,

  FOREIGN KEY (trainer_specialization_id) REFERENCES FitServe.trainer_specialization(trainer_specialization_id)
);

CREATE TABLE FitServe.athletes( -- Andriy
  athlete_id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,

  goals VARCHAR(1500) NOT NULL,

  user_id INTEGER NOT NULL UNIQUE,

  FOREIGN KEY (user_id) REFERENCES FitServe.users(user_id) 
);

CREATE TABLE FitServe.achievements ( -- Andriy
  achievement_id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,

  name VARCHAR(100) NOT NULL,
  category VARCHAR(50),
  tier FitServe.achievement_rate_type NOT NULL,
  description VARCHAR(255) NOT NULL,
  target_value SMALLINT NOT NULL,

  CONSTRAINT chk_achievements_target_positive
   CHECK (target_value > 0) -- lily-CHECK
);

CREATE TABLE FitServe.athlete_achievement( -- Andriy
  athlete_achievement_id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,

  achievement_id INTEGER NOT NULL,
  athlete_id INTEGER NOT NULL,
  date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  current_state SMALLINT,

  UNIQUE (achievement_id, athlete_id),

  CONSTRAINT chk_athlete_achievement_date_not_future
   CHECK (date <= CURRENT_TIMESTAMP), -- lily-CHECK

  CONSTRAINT chk_athlete_achievement_state_nonnegative
   CHECK (current_state >= 0), -- lily-CHECK

  FOREIGN KEY (achievement_id) REFERENCES FitServe.achievements(achievement_id),
  FOREIGN KEY (athlete_id) REFERENCES FitServe.athletes(athlete_id)
);

CREATE TABLE FitServe.body_metrics( -- Andriy
  metric_id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  date DATE NOT NULL DEFAULT NOW(),
  weight NUMERIC(4,1) NOT NULL,
  athlete_id INTEGER NOT NULL,

  CONSTRAINT chk_body_metrics_date_not_future
   CHECK (date <= CURRENT_DATE), -- lily-CHECK

  CONSTRAINT chk_body_metrics_weight_range
   CHECK (weight BETWEEN 40.0 AND 200.0), -- lily-CHECK

  FOREIGN KEY (athlete_id) REFERENCES FitServe.athletes(athlete_id)
);

CREATE TABLE FitServe.health_restriction( -- Andriy
  health_restriction_id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  description VARCHAR(4000) NOT NULL,
  severity_level FitServe.severity_level NOT NULL,
  athlete_id INTEGER NOT NULL,

  FOREIGN KEY (athlete_id) REFERENCES FitServe.athletes(athlete_id)
);

CREATE TABLE FitServe.nutrition( -- Andriy
  nutrition_id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  date DATE NOT NULL DEFAULT NOW(),
  calories SMALLINT NOT NULL,
  carbs_weight SMALLINT,
  fat_weight SMALLINT,
  athlete_id INTEGER NOT NULL,

  CONSTRAINT chk_nutrition_calories_nonnegative
   CHECK (calories >= 0), -- lily-CHECK 

  CONSTRAINT chk_nutrition_carbs
   CHECK (carbs_weight >= 0 AND carbs_weight < 1000), -- lily-CHECK

  CONSTRAINT chk_nutrition_fat
   CHECK (fat_weight >= 0 AND fat_weight < 1000), -- lily-CHECK

  FOREIGN KEY (athlete_id) REFERENCES FitServe.athletes(athlete_id)
);

CREATE TABLE FitServe.protein( -- Andriy
  protein_id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  weight SMALLINT NOT NULL,
  type VARCHAR(50) NOT NULL,
  nutrition_id BIGINT NOT NULL,

  CONSTRAINT chk_protein_weight
   CHECK (weight > 0 AND weight < 2000), -- lily-CHECK                                  !

  FOREIGN KEY (nutrition_id) REFERENCES FitServe.nutrition(nutrition_id)
);

CREATE TABLE FitServe.studios ( -- lily
  studio_id SMALLINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,

  studio_name FitServe.studio_name_enum NOT NULL UNIQUE,
  max_capacity INTEGER NOT NULL,
  studio_status BOOLEAN NOT NULL DEFAULT TRUE,

  CONSTRAINT chk_studios_capacity
   CHECK (max_capacity > 0 AND max_capacity < 500) -- lily-CHECK  
);

CREATE TABLE FitServe.studio_type ( -- lily
  studio_type_id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,

  activity_name VARCHAR(50) NOT NULL,
  studio_id INTEGER NOT NULL,

  UNIQUE (studio_id, activity_name),
  
  CONSTRAINT chk_studio_type_activity_not_blank
   CHECK (TRIM(activity_name) <> ''),

  FOREIGN KEY (studio_id) REFERENCES FitServe.studios(studio_id)
);

CREATE TABLE FitServe.session ( -- lily
  session_id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,

  time_start TIMESTAMP NOT NULL,
  time_end TIMESTAMP NOT NULL,
  booking FitServe.booking_status_enum NOT NULL DEFAULT 'planned',
  studio_id INTEGER,

  CONSTRAINT chk_session_end_after_start
   CHECK (time_end > time_start),
    
  CONSTRAINT chk_session_max_duration
   CHECK (time_end <= time_start + INTERVAL '3 hours'),

  FOREIGN KEY (studio_id) REFERENCES FitServe.studios(studio_id)
);

CREATE TABLE FitServe.athlete_session ( -- lily
  athlete_session_id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,

  session_id BIGINT NOT NULL,
  athlete_id INTEGER NOT NULL,

  UNIQUE (session_id, athlete_id),
  FOREIGN KEY (session_id) REFERENCES FitServe.session(session_id),
  FOREIGN KEY (athlete_id) REFERENCES FitServe.athletes(athlete_id)
);

CREATE TABLE FitServe.trainer_session ( -- lily
  trainer_session_id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,

  trainer_id INTEGER NOT NULL,
  session_id BIGINT NOT NULL,

  UNIQUE (trainer_id, session_id),
  FOREIGN KEY (trainer_id) REFERENCES FitServe.trainers(trainer_id),
  FOREIGN KEY (session_id) REFERENCES FitServe.session(session_id)
);

CREATE TABLE FitServe.packs ( -- Oleksii
  pack_id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,

  pack_name VARCHAR(100) NOT NULL UNIQUE,
  pack_type VARCHAR(50) NOT NULL,
  is_active BOOLEAN NOT NULL
);

CREATE TABLE FitServe.pack_subscription ( -- Oleksii
  pack_subscription_id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,

  start_date DATE NOT NULL,
  end_date DATE NOT NULL,

  athlete_id INTEGER NOT NULL,
  pack_id INTEGER NOT NULL,

  CONSTRAINT chk_pack_subscription_date_order
   CHECK (end_date >= start_date), -- lily-CHECK

  CONSTRAINT chk_pack_subscription_start_limit
   CHECK (start_date <= CURRENT_DATE + INTERVAL '1 year'), -- lily-CHECK  (перевірити INTERVAL)

  FOREIGN KEY (athlete_id) REFERENCES FitServe.athletes(athlete_id),
  FOREIGN KEY (pack_id) REFERENCES FitServe.packs(pack_id)
);

CREATE TABLE FitServe.equipment ( -- Oleksii
  equipment_id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,

  name VARCHAR(100) NOT NULL,
  model VARCHAR(100) NOT NULL,
  brand varchar(100) NOT NULL,
  status FitServe.status_type NOT NULL DEFAULT 'ready',

  update_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

  studio_id INTEGER NOT NULL,

  FOREIGN KEY (studio_id) REFERENCES FitServe.studios(studio_id)
);

CREATE TABLE FitServe.warranty ( -- Oleksii
  warranty_id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,

  warranty_status FitServe.warranty_type NOT NULL,
  expiry_date DATE NOT NULL,
  renewal_date DATE,
  terms_link VARCHAR(500) NOT NULL,
  
  equipment_id INTEGER NOT NULL UNIQUE,

  FOREIGN KEY (equipment_id) REFERENCES FitServe.equipment(equipment_id)
);

CREATE TABLE FitServe.pack_equipment ( -- Oleksii
  pack_equipment_id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,

  pack_id INTEGER NOT NULL,
  equipment_id INTEGER NOT NULL,

  UNIQUE (pack_id, equipment_id),
  FOREIGN KEY (pack_id) REFERENCES FitServe.packs(pack_id),
  FOREIGN KEY (equipment_id) REFERENCES FitServe.equipment(equipment_id)
);

CREATE TABLE FitServe.promocodes ( -- Oleksii
  promocode_id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,

  code VARCHAR(16) NOT NULL UNIQUE,
  quantity SMALLINT NOT NULL,
  is_active BOOLEAN NOT NULL DEFAULT TRUE,  

  CONSTRAINT chk_promocodes_quantity_nonnegative
   CHECK (quantity >= 0) -- lily-CHECK
);

CREATE TABLE FitServe.payment ( -- Oleksii
  payment_id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,

  price NUMERIC(10, 2) NOT NULL,
  payment_method VARCHAR(50),
  payment_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  amount_paid NUMERIC(10, 2),
  payment_status FitServe.payment_status_type NOT NULL DEFAULT 'unpaid',

  pack_subscription_id INTEGER NOT NULL UNIQUE,
  promocode_id INTEGER,

  CONSTRAINT chk_payment_price_positive
   CHECK (price > 0), -- lily-CHECK

  CONSTRAINT chk_payment_amount_positive
   CHECK (amount_paid > 0), -- lily-CHECK

  FOREIGN KEY (pack_subscription_id) REFERENCES FitServe.pack_subscription(pack_subscription_id),
  FOREIGN KEY (promocode_id) REFERENCES FitServe.promocodes(promocode_id)
);

CREATE TABLE FitServe.discounts ( -- Oleksii
  discount_id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,

  discount_name VARCHAR(50) NOT NULL,
  description VARCHAR(500) NOT NULL,
  start_date DATE NOT NULL,
  end_date DATE NOT NULL,

  CONSTRAINT chk_discounts_date_order
   CHECK (end_date > start_date) -- lily-CHECK
);

CREATE TABLE FitServe.payment_discount ( -- Oleksii
  payment_discount_id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,

  discount_id INTEGER NOT NULL,
  payment_id INTEGER NOT NULL,

  CONSTRAINT uq_payment_discount_pair
  UNIQUE (discount_id, payment_id), -- lily-UNIQUE

  FOREIGN KEY (discount_id) REFERENCES FitServe.discounts(discount_id),
  FOREIGN KEY (payment_id) REFERENCES FitServe.payment(payment_id)
);


-- =========================================================
-- FOREIGN KEY UPDATE / DELETE RULES
-- =========================================================

ALTER TABLE FitServe.users
    DROP CONSTRAINT IF EXISTS users_user_info_id_fkey,
    ADD CONSTRAINT fk_users_user_info
        FOREIGN KEY (user_info_id)
        REFERENCES FitServe.user_info (user_info_id)
        ON UPDATE CASCADE
        ON DELETE RESTRICT;

ALTER TABLE FitServe.trainers
    DROP CONSTRAINT IF EXISTS trainers_user_id_fkey,
    ADD CONSTRAINT fk_trainers_user
        FOREIGN KEY (user_id)
        REFERENCES FitServe.users (user_id)
        ON UPDATE CASCADE
        ON DELETE CASCADE;

ALTER TABLE FitServe.trainers
    DROP CONSTRAINT IF EXISTS trainers_interest_rate_id_fkey,
    ADD CONSTRAINT fk_trainers_interest_rate
        FOREIGN KEY (interest_rate_id)
        REFERENCES FitServe.interest_rate (interest_rate_id)
        ON UPDATE CASCADE
        ON DELETE SET NULL;

ALTER TABLE FitServe.trainer_specialization
    DROP CONSTRAINT IF EXISTS trainer_specialization_trainer_id_fkey,
    ADD CONSTRAINT fk_trainer_specialization_trainer
        FOREIGN KEY (trainer_id)
        REFERENCES FitServe.trainers (trainer_id)
        ON UPDATE CASCADE
        ON DELETE CASCADE;

ALTER TABLE FitServe.certifications
    DROP CONSTRAINT IF EXISTS certifications_trainer_specialization_id_fkey,
    ADD CONSTRAINT fk_certifications_trainer_specialization
        FOREIGN KEY (trainer_specialization_id)
        REFERENCES FitServe.trainer_specialization (trainer_specialization_id)
        ON UPDATE CASCADE
        ON DELETE CASCADE;

ALTER TABLE FitServe.athletes
    DROP CONSTRAINT IF EXISTS athletes_user_id_fkey,
    ADD CONSTRAINT fk_athletes_user
        FOREIGN KEY (user_id)
        REFERENCES FitServe.users (user_id)
        ON UPDATE CASCADE
        ON DELETE CASCADE;

ALTER TABLE FitServe.athlete_achievement
    DROP CONSTRAINT IF EXISTS athlete_achievement_achievement_id_fkey,
    ADD CONSTRAINT fk_athlete_achievement_achievement
        FOREIGN KEY (achievement_id)
        REFERENCES FitServe.achievements (achievement_id)
        ON UPDATE CASCADE
        ON DELETE CASCADE;

ALTER TABLE FitServe.athlete_achievement
    DROP CONSTRAINT IF EXISTS athlete_achievement_athlete_id_fkey,
    ADD CONSTRAINT fk_athlete_achievement_athlete
        FOREIGN KEY (athlete_id)
        REFERENCES FitServe.athletes (athlete_id)
        ON UPDATE CASCADE
        ON DELETE CASCADE;

ALTER TABLE FitServe.body_metrics
    DROP CONSTRAINT IF EXISTS body_metrics_athlete_id_fkey,
    ADD CONSTRAINT fk_body_metrics_athlete
        FOREIGN KEY (athlete_id)
        REFERENCES FitServe.athletes (athlete_id)
        ON UPDATE CASCADE
        ON DELETE CASCADE;

ALTER TABLE FitServe.health_restriction
    DROP CONSTRAINT IF EXISTS health_restriction_athlete_id_fkey,
    ADD CONSTRAINT fk_health_restriction_athlete
        FOREIGN KEY (athlete_id)
        REFERENCES FitServe.athletes (athlete_id)
        ON UPDATE CASCADE
        ON DELETE CASCADE;

ALTER TABLE FitServe.nutrition
    DROP CONSTRAINT IF EXISTS nutrition_athlete_id_fkey,
    ADD CONSTRAINT fk_nutrition_athlete
        FOREIGN KEY (athlete_id)
        REFERENCES FitServe.athletes (athlete_id)
        ON UPDATE CASCADE
        ON DELETE CASCADE;

ALTER TABLE FitServe.protein
    DROP CONSTRAINT IF EXISTS protein_nutrition_id_fkey,
    ADD CONSTRAINT fk_protein_nutrition
        FOREIGN KEY (nutrition_id)
        REFERENCES FitServe.nutrition (nutrition_id)
        ON UPDATE CASCADE
        ON DELETE CASCADE;

ALTER TABLE FitServe.studio_type
    DROP CONSTRAINT IF EXISTS studio_type_studio_id_fkey,
    ADD CONSTRAINT fk_studio_type_studio
        FOREIGN KEY (studio_id)
        REFERENCES FitServe.studios (studio_id)
        ON UPDATE CASCADE
        ON DELETE CASCADE;

ALTER TABLE FitServe.session
    DROP CONSTRAINT IF EXISTS session_studio_id_fkey,
    ADD CONSTRAINT fk_session_studio
        FOREIGN KEY (studio_id)
        REFERENCES FitServe.studios (studio_id)
        ON UPDATE CASCADE
        ON DELETE SET NULL;

ALTER TABLE FitServe.athlete_session
    DROP CONSTRAINT IF EXISTS athlete_session_session_id_fkey,
    ADD CONSTRAINT fk_athlete_session_session
        FOREIGN KEY (session_id)
        REFERENCES FitServe.session (session_id)
        ON UPDATE CASCADE
        ON DELETE CASCADE;

ALTER TABLE FitServe.athlete_session
    DROP CONSTRAINT IF EXISTS athlete_session_athlete_id_fkey,
    ADD CONSTRAINT fk_athlete_session_athlete
        FOREIGN KEY (athlete_id)
        REFERENCES FitServe.athletes (athlete_id)
        ON UPDATE CASCADE
        ON DELETE CASCADE;

ALTER TABLE FitServe.trainer_session
    DROP CONSTRAINT IF EXISTS trainer_session_trainer_id_fkey,
    ADD CONSTRAINT fk_trainer_session_trainer
        FOREIGN KEY (trainer_id)
        REFERENCES FitServe.trainers (trainer_id)
        ON UPDATE CASCADE
        ON DELETE CASCADE;

ALTER TABLE FitServe.trainer_session
    DROP CONSTRAINT IF EXISTS trainer_session_session_id_fkey,
    ADD CONSTRAINT fk_trainer_session_session
        FOREIGN KEY (session_id)
        REFERENCES FitServe.session (session_id)
        ON UPDATE CASCADE
        ON DELETE CASCADE;

ALTER TABLE FitServe.pack_subscription
    DROP CONSTRAINT IF EXISTS pack_subscription_athlete_id_fkey,
    ADD CONSTRAINT fk_pack_subscription_athlete
        FOREIGN KEY (athlete_id)
        REFERENCES FitServe.athletes (athlete_id)
        ON UPDATE CASCADE
        ON DELETE CASCADE;

ALTER TABLE FitServe.pack_subscription
    DROP CONSTRAINT IF EXISTS pack_subscription_pack_id_fkey,
    ADD CONSTRAINT fk_pack_subscription_pack
        FOREIGN KEY (pack_id)
        REFERENCES FitServe.packs (pack_id)
        ON UPDATE CASCADE
        ON DELETE RESTRICT;

ALTER TABLE FitServe.equipment
    DROP CONSTRAINT IF EXISTS equipment_studio_id_fkey,
    ADD CONSTRAINT fk_equipment_studio
        FOREIGN KEY (studio_id)
        REFERENCES FitServe.studios (studio_id)
        ON UPDATE CASCADE
        ON DELETE RESTRICT;

ALTER TABLE FitServe.warranty
    DROP CONSTRAINT IF EXISTS warranty_equipment_id_fkey,
    ADD CONSTRAINT fk_warranty_equipment
        FOREIGN KEY (equipment_id)
        REFERENCES FitServe.equipment (equipment_id)
        ON UPDATE CASCADE
        ON DELETE CASCADE;

ALTER TABLE FitServe.pack_equipment
    DROP CONSTRAINT IF EXISTS pack_equipment_pack_id_fkey,
    ADD CONSTRAINT fk_pack_equipment_pack
        FOREIGN KEY (pack_id)
        REFERENCES FitServe.packs (pack_id)
        ON UPDATE CASCADE
        ON DELETE CASCADE;

ALTER TABLE FitServe.pack_equipment
    DROP CONSTRAINT IF EXISTS pack_equipment_equipment_id_fkey,
    ADD CONSTRAINT fk_pack_equipment_equipment
        FOREIGN KEY (equipment_id)
        REFERENCES FitServe.equipment (equipment_id)
        ON UPDATE CASCADE
        ON DELETE CASCADE;

ALTER TABLE FitServe.payment
    DROP CONSTRAINT IF EXISTS payment_pack_subscription_id_fkey,
    ADD CONSTRAINT fk_payment_pack_subscription
        FOREIGN KEY (pack_subscription_id)
        REFERENCES FitServe.pack_subscription (pack_subscription_id)
        ON UPDATE CASCADE
        ON DELETE RESTRICT;

ALTER TABLE FitServe.payment
    DROP CONSTRAINT IF EXISTS payment_promocode_id_fkey,
    ADD CONSTRAINT fk_payment_promocode
        FOREIGN KEY (promocode_id)
        REFERENCES FitServe.promocodes (promocode_id)
        ON UPDATE CASCADE
        ON DELETE SET NULL;

ALTER TABLE FitServe.payment_discount
    DROP CONSTRAINT IF EXISTS payment_discount_discount_id_fkey,
    ADD CONSTRAINT fk_payment_discount_discount
        FOREIGN KEY (discount_id)
        REFERENCES FitServe.discounts (discount_id)
        ON UPDATE CASCADE
        ON DELETE RESTRICT;

ALTER TABLE FitServe.payment_discount
    DROP CONSTRAINT IF EXISTS payment_discount_payment_id_fkey,
    ADD CONSTRAINT fk_payment_discount_payment
        FOREIGN KEY (payment_id)
        REFERENCES FitServe.payment (payment_id)
        ON UPDATE CASCADE
        ON DELETE CASCADE;


-- =========================================================
-- INDEXES
-- =========================================================

-- TRAINERS AND CERTIFICATIONS
CREATE INDEX IF NOT EXISTS idx_trainer_specialization_trainer_id
    ON FitServe.trainer_specialization (trainer_id);

CREATE INDEX IF NOT EXISTS idx_trainer_specialization_name
    ON FitServe.trainer_specialization (specialization_name);


-- ATHLETES, PROGRESS AND HEALTH
CREATE INDEX IF NOT EXISTS idx_athlete_achievement_athlete_id
    ON FitServe.athlete_achievement (athlete_id);

CREATE INDEX IF NOT EXISTS idx_body_metrics_athlete_date
    ON FitServe.body_metrics (athlete_id, date DESC);

CREATE INDEX IF NOT EXISTS idx_health_restriction_athlete_id
    ON FitServe.health_restriction (athlete_id);

CREATE INDEX IF NOT EXISTS idx_nutrition_athlete_date
    ON FitServe.nutrition (athlete_id, date DESC);


-- STUDIOS AND SESSIONS
CREATE INDEX IF NOT EXISTS idx_studio_type_activity_name
    ON FitServe.studio_type (activity_name);

CREATE INDEX IF NOT EXISTS idx_session_active_schedule
    ON FitServe.session (time_start, time_end, studio_id)
    WHERE booking IN ('planned', 'confirmed');

CREATE INDEX IF NOT EXISTS idx_athlete_session_athlete_session
    ON FitServe.athlete_session (athlete_id, session_id);

CREATE INDEX IF NOT EXISTS idx_trainer_session_session_trainer
    ON FitServe.trainer_session (session_id, trainer_id);


-- PACKS AND SUBSCRIPTIONS
CREATE INDEX IF NOT EXISTS idx_packs_type_active
    ON FitServe.packs (pack_type, is_active);

CREATE INDEX IF NOT EXISTS idx_pack_subscription_athlete_period
    ON FitServe.pack_subscription (athlete_id, start_date, end_date);


-- EQUIPMENT AND WARRANTY
CREATE INDEX IF NOT EXISTS idx_equipment_studio_status
    ON FitServe.equipment (studio_id, status);

CREATE INDEX IF NOT EXISTS idx_warranty_renewal_date
    ON FitServe.warranty (renewal_date)
    WHERE renewal_date IS NOT NULL;

CREATE INDEX IF NOT EXISTS idx_pack_equipment_equipment_id
    ON FitServe.pack_equipment (equipment_id);


-- PAYMENTS AND DISCOUNTS
CREATE INDEX IF NOT EXISTS idx_payment_status_date
    ON FitServe.payment (payment_status, payment_date DESC);

CREATE INDEX IF NOT EXISTS idx_discounts_active_period
    ON FitServe.discounts (start_date, end_date);

CREATE INDEX IF NOT EXISTS idx_payment_discount_payment_id
    ON FitServe.payment_discount (payment_id);

--DROP SCHEMA fitserve CASCADE;