-- create a table
CREATE TABLE adherents (
  id INTEGER PRIMARY KEY,
  nom TEXT NOT NULL,
  prenom TEXT NOT NULL,
  email TEXT NOT NULL,
  date_naissance DATE,
  date_inscription DATE
);

CREATE TABLE abonnements (
  id INTEGER PRIMARY KEY,
  nom TEXT NOT NULL,
  duree_mois DATE NOT NULL,
  prix INTEGER NOT NULL,
  CONSTRAINT prix CHECK (prix >= 0)
);

CREATE TABLE adhesions (
  id INTEGER PRIMARY KEY,
  adherent_id INTEGER NOT NULL,
  abonnement_id INTEGER NOT NULL,
  date_debut DATE,
  date_fin DATE,
  statut TEXT
);

CREATE TABLE coachs (
  id INTEGER PRIMARY KEY,
  nom TEXT NOT NULL,
  prenom TEXT NOT NULL,
  specialite TEXT NOT NULL
);

CREATE TABLE cours (
  id INTEGER PRIMARY KEY,
  nom TEXT NOT NULL,
  coach_id INTEGER NOT NULL,
  date_cours DATE NOT NULL,
  heure_debut INTEGER,
  duree_minutes INTEGER,
  capacite INTEGER DEFAULT 0
);

CREATE TABLE reservations (
  id INTEGER PRIMARY KEY,
  adherent_id TEXT NOT NULL,
  cours_id INTEGER NOT NULL,
  date_reservation DATE NOT NULL,
  presence INTEGER
);


-- ==========================================
-- ABONNEMENTS
-- ==========================================

INSERT INTO abonnements (nom, prix, duree_mois) VALUES
('Essentiel', 29.99, 1),
('Premium', 49.99, 1),
('Étudiant', 19.99, 1),
('Annuel', 399.99, 12);


-- ==========================================
-- ADHERENTS
-- ==========================================

INSERT INTO adherents (nom, prenom, email, date_naissance, date_inscription) VALUES
('Martin', 'Thomas', 'thomas.martin@email.com', '1998-04-12', '2025-01-15'),
('Durand', 'Sarah', 'sarah.durand@email.com', '2001-07-23', '2025-02-03'),
('Bernard', 'Lucas', 'lucas.bernard@email.com', '1995-11-08', '2025-02-17'),
('Petit', 'Emma', 'emma.petit@email.com', '2003-03-19', '2025-03-01'),
('Robert', 'Hugo', 'hugo.robert@email.com', '1992-09-14', '2025-03-12'),
('Richard', 'Chloé', 'chloe.richard@email.com', '1999-12-05', '2025-03-25'),
('Moreau', 'Alexandre', 'alexandre.moreau@email.com', '1990-06-30', '2025-04-02'),
('Simon', 'Julie', 'julie.simon@email.com', '1997-01-17', '2025-04-18'),
('Laurent', 'Mehdi', 'mehdi.laurent@email.com', '2002-10-11', '2025-05-06'),
('Lefebvre', 'Camille', 'camille.lefebvre@email.com', '1996-08-27', '2025-05-20'),
('Michel', 'Nathan', 'nathan.michel@email.com', '2000-02-14', '2025-06-03'),
('Garcia', 'Laura', 'laura.garcia@email.com', '1994-05-22', '2025-06-15');


-- ==========================================
-- ADHESIONS
-- ==========================================

INSERT INTO adhesions (adherent_id, abonnement_id, date_debut, date_fin, statut) VALUES
(1, 2, '2025-01-15', '2025-02-15', 'active'),
(2, 3, '2025-02-03', '2025-03-03', 'active'),
(3, 4, '2025-02-17', '2026-02-17', 'active'),
(4, 3, '2025-03-01', '2025-04-01', 'active'),
(5, 1, '2025-03-12', '2025-04-12', 'active'),
(6, 2, '2025-03-25', '2025-04-25', 'active'),
(7, 4, '2025-04-02', '2026-04-02', 'active'),
(8, 2, '2025-04-18', '2025-05-18', 'active'),
(9, 3, '2025-05-06', '2025-06-06', 'active'),
(10, 1, '2025-05-20', '2025-06-20', 'active'),
(11, 2, '2025-06-03', '2025-07-03', 'active'),
(12, 1, '2025-06-15', '2025-07-15', 'active');


-- ==========================================
-- COACHS
-- ==========================================

INSERT INTO coachs (nom, prenom, specialite) VALUES
('Dupont', 'Thomas', 'Musculation'),
('Morel', 'Sarah', 'CrossFit'),
('Faure', 'Alexandre', 'Boxe'),
('Girard', 'Julie', 'Yoga'),
('Andre', 'Mehdi', 'Cardio'),
('Leroy', 'Sophie', 'Pilates');


-- ==========================================
-- COURS
-- ==========================================

INSERT INTO cours 
(nom, coach_id, date_cours, heure_debut, duree_minutes, capacite) VALUES
('Musculation débutant', 1, '2025-09-01', '18:00:00', 60, 15),
('Musculation avancée', 1, '2025-09-02', '19:00:00', 90, 12),
('CrossFit', 2, '2025-09-03', '18:30:00', 60, 20),
('CrossFit', 2, '2025-09-05', '19:00:00', 60, 20),
('Boxe débutant', 3, '2025-09-04', '18:00:00', 60, 15),
('Boxe avancée', 3, '2025-09-06', '19:00:00', 90, 10),
('Yoga détente', 4, '2025-09-07', '10:00:00', 60, 20),
('Yoga dynamique', 4, '2025-09-08', '18:00:00', 60, 20),
('Cardio HIIT', 5, '2025-09-09', '18:30:00', 45, 25),
('Cardio intense', 5, '2025-09-10', '19:00:00', 45, 20),
('Pilates débutant', 6, '2025-09-11', '17:30:00', 60, 15),
('Pilates avancé', 6, '2025-09-12', '18:30:00', 60, 12);


-- ==========================================
-- RESERVATIONS
-- ==========================================

INSERT INTO reservations 
(adherent_id, cours_id, date_reservation, presence) VALUES
(1, 1, '2025-08-28', TRUE),
(1, 3, '2025-08-29', TRUE),
(1, 5, '2025-08-30', TRUE),

(2, 7, '2025-08-29', TRUE),
(2, 8, '2025-09-01', TRUE),

(3, 2, '2025-08-28', TRUE),
(3, 3, '2025-08-29', TRUE),
(3, 4, '2025-08-30', TRUE),
(3, 9, '2025-09-01', TRUE),

(4, 7, '2025-08-30', TRUE),
(4, 11, '2025-09-01', TRUE),

(5, 5, '2025-08-29', TRUE),
(5, 6, '2025-08-30', FALSE),

(6, 3, '2025-08-28', TRUE),
(6, 9, '2025-08-29', TRUE),
(6, 10, '2025-09-01', TRUE),

(7, 1, '2025-08-28', TRUE),
(7, 2, '2025-08-29', TRUE),
(7, 4, '2025-08-30', TRUE),

(8, 7, '2025-08-29', TRUE),
(8, 8, '2025-08-30', TRUE),

(9, 3, '2025-08-28', TRUE),
(9, 9, '2025-08-29', TRUE),
(9, 10, '2025-08-30', TRUE),

(10, 11, '2025-08-29', TRUE),
(10, 12, '2025-08-30', TRUE),

(11, 1, '2025-08-28', TRUE),
(11, 5, '2025-08-29', FALSE),

(12, 6, '2025-08-30', TRUE);


select * from adherents