-- =============================================
-- GYM SAAS DATABASE - CONSOLIDATED SCHEMA
-- Version complète avec toutes les migrations intégrées
-- Dernière mise à jour: 2025-05-22
-- =============================================

DROP DATABASE IF EXISTS gym_saas;
CREATE DATABASE gym_saas CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE gym_saas;

-- =============================================
-- TABLES PRINCIPALES
-- =============================================

-- =============================================
-- CLUBS
-- =============================================
CREATE TABLE clubs (
    id_club               INT AUTO_INCREMENT PRIMARY KEY,
    nom                   VARCHAR(100) NOT NULL,
    logo                  VARCHAR(255),
    address               VARCHAR(255),
    telephone             VARCHAR(20) UNIQUE,  -- ✅ UNIQUE constraint (migration: add_unique_telephone.sql)
    description           TEXT,
    status                ENUM('active','en_retard') NOT NULL DEFAULT 'active',  -- ✅ Simplifié (migration: simplify_club_status.sql)
    horaire_travaille      VARCHAR(255),
    subscription_end_date  DATE NULL COMMENT 'Date de fin d''abonnement du club',  -- ✅ Ajouté (migration: add_subscription_end_date.sql)
    created_at            TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at            TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =============================================
-- USERS
-- =============================================
CREATE TABLE users (
    id_user    INT AUTO_INCREMENT PRIMARY KEY,
    nom        VARCHAR(100) NOT NULL,
    prenom     VARCHAR(100) NOT NULL,
    email      VARCHAR(150) NOT NULL UNIQUE,
    password   VARCHAR(255) NOT NULL,
    age        TINYINT UNSIGNED,
    roles      ENUM('super_admin','admin','membre') NOT NULL DEFAULT 'membre',
    sex        ENUM('homme','femme'),
    telephone  VARCHAR(20) UNIQUE,  -- ✅ UNIQUE constraint (migration: add_unique_telephone.sql)
    photo_profil VARCHAR(255) DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    id_club    INT,
    FOREIGN KEY (id_club) REFERENCES clubs(id_club) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =============================================
-- PASSWORD RESET TOKENS
-- ✅ Table ajoutée (migration: add_password_reset.sql)
-- =============================================
CREATE TABLE password_reset_tokens (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    token VARCHAR(255) NOT NULL UNIQUE,
    expires_at TIMESTAMP NOT NULL,
    used BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    -- Index pour optimiser les recherches
    INDEX idx_token (token),
    INDEX idx_user_id (user_id),
    INDEX idx_expires_at (expires_at),
    INDEX idx_used (used),

    -- Contrainte de clé étrangère
    FOREIGN KEY (user_id) REFERENCES users(id_user) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =============================================
-- CLUB_OFFRE
-- =============================================
CREATE TABLE club_offre (
    id_offre    INT AUTO_INCREMENT PRIMARY KEY,
    nom         VARCHAR(100) NOT NULL,
    description TEXT,
    prix        DECIMAL(10,2) NOT NULL,
    duree       VARCHAR(50) NOT NULL,
    is_active   BOOLEAN NOT NULL DEFAULT TRUE,
    id_club     INT NOT NULL,
    created_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (id_club) REFERENCES clubs(id_club) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =============================================
-- CLUB_PAYEMENT
-- =============================================
CREATE TABLE club_payement (
    id_payement_club INT AUTO_INCREMENT PRIMARY KEY,
    methode          ENUM('cash','online','reçu') NOT NULL,
    montant          DECIMAL(10,2) NOT NULL,
    createdAt        TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    url_facture      VARCHAR(255),
    id_club          INT NOT NULL,
    FOREIGN KEY (id_club) REFERENCES clubs(id_club) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =============================================
-- CLUB_MEMBRE
-- ✅ Statut simplifié (migration: simplify_member_status.sql)
-- =============================================
CREATE TABLE club_membre (
    id_membre      INT AUTO_INCREMENT PRIMARY KEY,
    poid           DECIMAL(5,2),
    heateur        DECIMAL(5,2),
    status         ENUM('active','expiré') NOT NULL DEFAULT 'active',  -- ✅ Simplifié: seulement 'active' et 'expiré'
    activite       ENUM('active','moyen','trés') NOT NULL DEFAULT 'moyen',
    date_expiration DATE,
    id_club        INT NOT NULL,
    id_user        INT NOT NULL,
    id_offre       INT,
    created_at     TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at     TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (id_club)  REFERENCES clubs(id_club) ON DELETE CASCADE,
    FOREIGN KEY (id_user)  REFERENCES users(id_user) ON DELETE CASCADE,
    FOREIGN KEY (id_offre) REFERENCES club_offre(id_offre) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =============================================
-- T_PROGRAMME
-- =============================================
CREATE TABLE t_programme (
    id_programme     INT AUTO_INCREMENT PRIMARY KEY,
    created_at       TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    titre            VARCHAR(150) NOT NULL,
    url_nutrition    VARCHAR(255),
    url_entrainement VARCHAR(255),
    type             ENUM('entrainement','nutrition','deux') NOT NULL,
    id_membre        INT NOT NULL,
    id_club          INT NOT NULL,
    FOREIGN KEY (id_membre) REFERENCES club_membre(id_membre) ON DELETE CASCADE,
    FOREIGN KEY (id_club)   REFERENCES clubs(id_club) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =============================================
-- PAYEMENT_MEMBRE
-- =============================================
CREATE TABLE payement_membre (
    id_payement_membre INT AUTO_INCREMENT PRIMARY KEY,
    methode            ENUM('cash','online','reçu') NOT NULL,
    montant            DECIMAL(10,2) NOT NULL,
    createdAt          TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    url_facture        VARCHAR(255),
    id_membre          INT NOT NULL,
    FOREIGN KEY (id_membre) REFERENCES club_membre(id_membre) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =============================================
-- PROCÉDURES STOCKÉES
-- ✅ Suppression sécurisée des clubs (migration: secure_delete_club.sql)
-- =============================================

DELIMITER $$

DROP PROCEDURE IF EXISTS deleteClubSafely$$

CREATE PROCEDURE deleteClubSafely(
  IN p_club_id INT,
  OUT p_deleted_users INT,
  OUT p_deleted_members INT,
  OUT p_deleted_payments INT,
  OUT p_deleted_club INT
)
BEGIN
  -- DÉCLARER les variables de sortie
  SET p_deleted_users = 0;
  SET p_deleted_members = 0;
  SET p_deleted_payments = 0;
  SET p_deleted_club = 0;

  -- 1. Supprimer tous les utilisateurs associés au club
  DELETE FROM users
  WHERE id_club = p_club_id
    AND roles IN ('admin', 'membre');

  SET p_deleted_users = ROW_COUNT();

  -- 2. Supprimer tous les membres du club
  DELETE FROM club_membre
  WHERE id_club = p_club_id;

  SET p_deleted_members = ROW_COUNT();

  -- 3. Supprimer tous les paiements du club
  DELETE FROM club_payement
  WHERE id_club = p_club_id;

  SET p_deleted_payments = ROW_COUNT();

  -- 4. Supprimer le club
  DELETE FROM clubs
  WHERE id_club = p_club_id;

  SET p_deleted_club = ROW_COUNT();

END$$

DROP FUNCTION IF EXISTS deleteClubSafelyFunc$$

CREATE FUNCTION deleteClubSafelyFunc(p_club_id INT)
RETURNS JSON
READS SQL DATA
DETERMINISTIC
COMMENT 'Supprime un club et toutes ses données associées de manière sécurisée'
BEGIN
  DECLARE v_deleted_users INT DEFAULT 0;
  DECLARE v_deleted_members INT DEFAULT 0;
  DECLARE v_deleted_payments INT DEFAULT 0;
  DECLARE v_deleted_club INT DEFAULT 0;
  DECLARE v_result JSON;

  -- 1. Supprimer les utilisateurs
  DELETE FROM users
  WHERE id_club = p_club_id
    AND roles IN ('admin', 'membre');

  SET v_deleted_users = ROW_COUNT();

  -- 2. Supprimer les membres
  DELETE FROM club_membre
  WHERE id_club = p_club_id;

  SET v_deleted_members = ROW_COUNT();

  -- 3. Supprimer les paiements
  DELETE FROM club_payement
  WHERE id_club = p_club_id;

  SET v_deleted_payments = ROW_COUNT();

  -- 4. Supprimer le club
  DELETE FROM clubs
  WHERE id_club = p_club_id;

  SET v_deleted_club = ROW_COUNT();

  -- Construire le résultat JSON
  SET v_result = JSON_OBJECT(
    'success', v_deleted_club > 0,
    'deleted_users', v_deleted_users,
    'deleted_members', v_deleted_members,
    'deleted_payments', v_deleted_payments,
    'deleted_club', v_deleted_club,
    'message', CASE
      WHEN v_deleted_club > 0 THEN 'Club supprimé avec succès'
      ELSE 'Club non trouvé'
    END
  );

  RETURN v_result;
END$$

DELIMITER ;

-- =============================================
-- INSERT UTILISATEUR PAR DÉFAUT
-- Password: admin123456 (hashed with bcryptjs 12 rounds)
-- =============================================
INSERT INTO users (nom, prenom, email, password, roles, age, sex, telephone)
VALUES (
    'Idriss',
    'Chrigui',
    'idrisschrigui123@gmail.com',
    '$2a$12$U4Fr7yVKjRTDWDuVGVBJumpZNOvPGD0cm.3SqSAp6jHAYEFge3eK',
    'super_admin',
    25,
    'homme',
    '0623392574'
);

-- =============================================
-- VALIDATION ET INFORMATIONS
-- =============================================

-- Vérifier que toutes les tables ont été créées
SELECT
    TABLE_NAME,
    TABLE_ROWS,
    CREATE_TIME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'gym_saas'
ORDER BY TABLE_NAME;

-- Vérifier que les procédures stockées existent
SELECT
    ROUTINE_NAME,
    ROUTINE_TYPE,
    CREATED
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_SCHEMA = 'gym_saas'
ORDER BY ROUTINE_NAME;

SELECT '✅ Database schema created successfully!' AS Status;
SELECT '✅ All migrations integrated' AS Status;
SELECT '✅ Stored procedures created' AS Status;
SELECT '📧 Email: idrisschrigui123@gmail.com' AS Info;
SELECT '🔑 Password: admin123456' AS Info;
SELECT '🎯 Ready to use!' AS Final;