---

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.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
);

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,

  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.rate_type NOT NULL
);

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.rate_type NOT NULL,
  description VARCHAR(255) NOT NULL,
  target_value SMALLINT NOT NULL
);

CREATE TABLE FitServe.athlete_achievement( -- Andriy
  athlete_achievement_id INTEGER 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),
  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,

  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 INTEGER 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,

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

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

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

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

  studio_name FitServe.studio_name_enum NOT NULL UNIQUE,
  max_capacity INTEGER NOT NULL CHECK (max_capacity > 0),
  studio_status BOOLEAN NOT NULL DEFAULT TRUE
);

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

  activity_name VARCHAR(50) NOT NULL CHECK (TRIM(activity_name) <> ''),
  studio_id INTEGER NOT NULL,

  UNIQUE (studio_id, activity_name),
  FOREIGN KEY (studio_id) REFERENCES FitServe.studios(studio_id)
    ON DELETE CASCADE
    ON UPDATE CASCADE
);

CREATE TABLE FitServe.session ( -- lily
  session_id INTEGER 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,

  CHECK (time_end > time_start),
  CHECK (time_end <= time_start + INTERVAL '3 hours'),
  FOREIGN KEY (studio_id) REFERENCES FitServe.studios(studio_id)
    ON DELETE SET NULL
    ON UPDATE CASCADE
);

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

  session_id INTEGER NOT NULL,
  athlete_id INTEGER NOT NULL,

  UNIQUE (session_id, athlete_id),
  FOREIGN KEY (session_id) REFERENCES FitServe.session(session_id)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  FOREIGN KEY (athlete_id) REFERENCES FitServe.athletes(athlete_id)
    ON DELETE CASCADE
    ON UPDATE CASCADE
);

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

  trainer_id INTEGER NOT NULL,
  session_id INTEGER NOT NULL,

  UNIQUE (trainer_id, session_id),
  FOREIGN KEY (trainer_id) REFERENCES FitServe.trainers(trainer_id)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  FOREIGN KEY (session_id) REFERENCES FitServe.session(session_id)
    ON DELETE CASCADE
    ON UPDATE CASCADE
);

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,

  UNIQUE(athlete_id, pack_id),
  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 UNIQUE,
  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 UNIQUE,
  
  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 DEFAULT TRUE  
);

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 NOT NULL,

  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 UNIQUE,
  description VARCHAR(500) NOT NULL,
  start_date DATE NOT NULL,
  end_date DATE NOT NULL
);

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

  discount_id INTEGER NOT NULL,
  payment_id INTEGER NOT NULL,

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


--DROP SCHEMA fitserve CASCADE;