-- =============================================
-- GYM SAAS DATABASE - SCRIPT CPANEL
-- Version complète optimisée pour cPanel/phpMyAdmin
-- Dernière mise à jour: 2026-06-21
-- =============================================

-- IMPORTANT:
-- 1. Dans cPanel, créez d'abord la base de données et l'utilisateur via "MySQL Database Wizard"
-- 2. Copiez ce script dans phpMyAdmin (onglet SQL)
-- 3. Exécutez le script en entier

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

-- =============================================
-- CLUBS
-- =============================================
CREATE TABLE IF NOT EXISTS clubs (
    id_club               INT AUTO_INCREMENT PRIMARY KEY,
    nom                   VARCHAR(100) NOT NULL,
    logo                  VARCHAR(255),
    address               VARCHAR(255),
    telephone             VARCHAR(20) UNIQUE,
    description           TEXT,
    status                ENUM('active','en_retard') NOT NULL DEFAULT 'active',
    horaire_travaille      VARCHAR(255),
    subscription_end_date  DATE NULL COMMENT 'Date de fin d''abonnement du club',
    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 IF NOT EXISTS 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,
    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
-- =============================================
CREATE TABLE IF NOT EXISTS 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 idx_token (token),
    INDEX idx_user_id (user_id),
    INDEX idx_expires_at (expires_at),
    INDEX idx_used (used),

    FOREIGN KEY (user_id) REFERENCES users(id_user) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =============================================
-- CLUB_OFFRE
-- =============================================
CREATE TABLE IF NOT EXISTS 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 IF NOT EXISTS 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
-- =============================================
CREATE TABLE IF NOT EXISTS 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',
    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 IF NOT EXISTS 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 IF NOT EXISTS 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;

-- =============================================
-- SUPERADMIN_BANKING
-- Migration: Coordonnées bancaires du superAdmin
-- =============================================
CREATE TABLE IF NOT EXISTS superadmin_banking (
    id INT AUTO_INCREMENT PRIMARY KEY,
    bank_name VARCHAR(100) NOT NULL COMMENT 'Nom de la banque',
    account_holder VARCHAR(150) NOT NULL COMMENT 'Titulaire du compte',
    rib VARCHAR(24) NOT NULL COMMENT 'RIB (24 chiffres)',
    iban VARCHAR(34) NOT NULL COMMENT 'IBAN',
    swift_code VARCHAR(11) COMMENT 'Code SWIFT/BIC',
    bank_address TEXT COMMENT 'Adresse de la banque',
    is_active BOOLEAN DEFAULT TRUE COMMENT 'Coordonnées actives',
    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;

-- =============================================
-- PROCÉDURES STOCKÉES
-- =============================================

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
  SET p_deleted_users = 0;
  SET p_deleted_members = 0;
  SET p_deleted_payments = 0;
  SET p_deleted_club = 0;

  DELETE FROM users
  WHERE id_club = p_club_id
    AND roles IN ('admin', 'membre');
  SET p_deleted_users = ROW_COUNT();

  DELETE FROM club_membre
  WHERE id_club = p_club_id;
  SET p_deleted_members = ROW_COUNT();

  DELETE FROM club_payement
  WHERE id_club = p_club_id;
  SET p_deleted_payments = ROW_COUNT();

  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;

  DELETE FROM users
  WHERE id_club = p_club_id
    AND roles IN ('admin', 'membre');
  SET v_deleted_users = ROW_COUNT();

  DELETE FROM club_membre
  WHERE id_club = p_club_id;
  SET v_deleted_members = ROW_COUNT();

  DELETE FROM club_payement
  WHERE id_club = p_club_id;
  SET v_deleted_payments = ROW_COUNT();

  DELETE FROM clubs
  WHERE id_club = p_club_id;
  SET v_deleted_club = ROW_COUNT();

  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 ;

-- =============================================
-- TRIGGERS
-- =============================================

DELIMITER $$

DROP TRIGGER IF EXISTS check_member_expiry_on_insert$$

CREATE TRIGGER check_member_expiry_on_insert
BEFORE INSERT ON club_membre
FOR EACH ROW
BEGIN
  IF DATEDIFF(NEW.date_expiration, CURDATE()) < 0 THEN
    SET NEW.status = 'expiré';
  ELSE
    SET NEW.status = 'active';
  END IF;
END$$

DROP TRIGGER IF EXISTS check_member_expiry_on_update$$

CREATE TRIGGER check_member_expiry_on_update
BEFORE UPDATE ON club_membre
FOR EACH ROW
BEGIN
  IF NEW.date_expiration IS NOT NULL THEN
    IF DATEDIFF(NEW.date_expiration, CURDATE()) < 0 THEN
      SET NEW.status = 'expiré';
    ELSE
      SET NEW.status = 'active';
    END IF;
  END IF;
END$$

DELIMITER ;

-- =============================================
-- DONNÉES INITIALES
-- =============================================

-- SuperAdmin 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'
);

-- Coordonnées bancaires par défaut (à modifier)
INSERT INTO superadmin_banking (bank_name, account_holder, rib, iban, swift_code, bank_address)
VALUES (
    'CIH Bank',
    'FitOrga SAAS Platform',
    '012345678901234567890123',
    'MA7612345678901234567890123',
    'ATWMMAMCXXX',
    '123 Mohammed V Avenue, Casablanca, Maroc'
) ON DUPLICATE KEY UPDATE updated_at = CURRENT_TIMESTAMP;

-- =============================================
-- VALIDATION ET RAPPORT
-- =============================================

-- Vérifier les tables créées
SELECT '==================' AS '';
SELECT 'TABLES CRÉÉES' AS '';
SELECT '==================' AS '';
SELECT
    TABLE_NAME,
    TABLE_ROWS,
    CREATE_TIME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = DATABASE()
ORDER BY TABLE_NAME;

-- Vérifier les procédures stockées
SELECT '==================' AS '';
SELECT 'PROCÉDURES STOCKÉES' AS '';
SELECT '==================' AS '';
SELECT
    ROUTINE_NAME,
    ROUTINE_TYPE,
    CREATED
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_SCHEMA = DATABASE()
ORDER BY ROUTINE_NAME;

-- Vérifier les triggers
SELECT '==================' AS '';
SELECT 'TRIGGERS CRÉÉS' AS '';
SELECT '==================' AS '';
SELECT
    TRIGGER_NAME,
    EVENT_MANIPULATION,
    EVENT_OBJECT_TABLE,
    ACTION_TIMING,
    CREATED
FROM INFORMATION_SCHEMA.TRIGGERS
WHERE TRIGGER_SCHEMA = DATABASE()
ORDER BY TRIGGER_NAME;

-- Vérifier les utilisateurs créés
SELECT '==================' AS '';
SELECT 'UTILISATEURS CRÉÉS' AS '';
SELECT '==================' AS '';
SELECT
    id_user,
    nom,
    prenom,
    email,
    roles,
    telephone
FROM users;

-- Messages de confirmation
SELECT '==================' AS '';
SELECT '✅ INSTALLATION TERMINÉE' AS '';
SELECT '==================' AS '';
SELECT '✅ Database schema créé avec succès' AS Status;
SELECT '✅ Toutes les migrations intégrées' AS Status;
SELECT '✅ Procédures stockées créées' AS Status;
SELECT '✅ Triggers automatiques créés' AS Status;
SELECT '' AS '';
SELECT '==================' AS '';
SELECT 'INFORMATIONS DE CONNEXION' AS '';
SELECT '==================' AS '';
SELECT '📧 Email: idrisschrigui123@gmail.com' AS Info;
SELECT '🔑 Password: admin123456' AS Info;
SELECT '🎯 Prêt à utiliser!' AS Final;
SELECT '' AS '';
SELECT '📝 N''oubliez pas de mettre à jour vos coordonnées bancaires dans la table superadmin_banking' AS Reminder;