CREATE DATABASE IF NOT EXISTS dynasqy CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; USE dynasqy; CREATE TABLE users ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, prenom VARCHAR(100) NOT NULL, nom VARCHAR(100) NOT NULL, societe VARCHAR(255) DEFAULT NULL, activite VARCHAR(255) DEFAULT NULL, email VARCHAR(255) NOT NULL UNIQUE, telephone VARCHAR(30) DEFAULT NULL, password_hash VARCHAR(255) NOT NULL, role ENUM('admin','member') NOT NULL DEFAULT 'member', date_adhesion DATE DEFAULT NULL, date_echeance DATE DEFAULT NULL, actif TINYINT(1) NOT NULL DEFAULT 1, invite_par_id INT UNSIGNED DEFAULT NULL, date_postulation DATE DEFAULT NULL, reception_connu INT UNSIGNED DEFAULT NULL, reception_inconnu INT UNSIGNED DEFAULT NULL, date_entretien_postulation DATE DEFAULT NULL, date_demande_whatsapp DATE DEFAULT NULL, fonction VARCHAR(255) DEFAULT NULL, fonction_emoji VARCHAR(10) DEFAULT NULL, kdrive_link TEXT DEFAULT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, FOREIGN KEY (invite_par_id) REFERENCES users(id) ON DELETE SET NULL, FOREIGN KEY (reception_connu) REFERENCES users(id) ON DELETE SET NULL, FOREIGN KEY (reception_inconnu) REFERENCES users(id) ON DELETE SET NULL ) ENGINE=InnoDB; CREATE TABLE invites ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, prenom VARCHAR(100) NOT NULL, nom VARCHAR(100) NOT NULL, societe VARCHAR(255) DEFAULT NULL, activite VARCHAR(255) DEFAULT NULL, telephone VARCHAR(30) DEFAULT NULL, email VARCHAR(255) DEFAULT NULL, site_web VARCHAR(255) DEFAULT NULL, commentaires TEXT DEFAULT NULL, mail_envoye TINYINT(1) NOT NULL DEFAULT 0, statut ENUM('en_attente','acceptee','refusee') NOT NULL DEFAULT 'en_attente', token VARCHAR(64) DEFAULT NULL, invitation_sent_at TIMESTAMP NULL DEFAULT NULL, photo_url VARCHAR(255) DEFAULT NULL, visit_number INT UNSIGNED NOT NULL DEFAULT 1, date_club DATE DEFAULT NULL, statut_candidature VARCHAR(20) DEFAULT NULL, candidature_date TIMESTAMP NULL DEFAULT NULL, invite_par_id INT UNSIGNED NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (invite_par_id) REFERENCES users(id) ON DELETE CASCADE ) ENGINE=InnoDB; CREATE TABLE recommandations ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, date DATE NOT NULL, membre_id INT UNSIGNED NOT NULL, destinataire_id INT UNSIGNED DEFAULT NULL, nom_prospect VARCHAR(255) NOT NULL, type ENUM('donnee','recue') NOT NULL DEFAULT 'donnee', transformee TINYINT(1) NOT NULL DEFAULT 0, ca_realise DECIMAL(12,2) DEFAULT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (membre_id) REFERENCES users(id) ON DELETE CASCADE, FOREIGN KEY (destinataire_id) REFERENCES users(id) ON DELETE SET NULL ) ENGINE=InnoDB; CREATE TABLE mises_en_honneur ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, date DATE NOT NULL, membre_id INT UNSIGNED DEFAULT NULL, societe VARCHAR(255) DEFAULT NULL, theme_sujet VARCHAR(500) NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (membre_id) REFERENCES users(id) ON DELETE SET NULL ) ENGINE=InnoDB; CREATE TABLE evenements ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, date DATE NOT NULL, nom_evenement VARCHAR(255) NOT NULL, lieu VARCHAR(255) DEFAULT NULL, horaire VARCHAR(50) DEFAULT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ) ENGINE=InnoDB; CREATE TABLE IF NOT EXISTS absences ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, membre_id INT UNSIGNED NOT NULL, date_absence DATE NOT NULL, motif VARCHAR(255) DEFAULT NULL, prevenu TINYINT(1) NOT NULL DEFAULT 0, remplace_par_id INT UNSIGNED DEFAULT NULL, remplace_par_type ENUM('member','guest') DEFAULT 'member', remplace_par_guest_id INT UNSIGNED DEFAULT NULL, created_by INT UNSIGNED NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (membre_id) REFERENCES users(id) ON DELETE CASCADE, FOREIGN KEY (remplace_par_id) REFERENCES users(id) ON DELETE SET NULL, FOREIGN KEY (remplace_par_guest_id) REFERENCES invites(id) ON DELETE SET NULL, FOREIGN KEY (created_by) REFERENCES users(id) ) ENGINE=InnoDB; CREATE TABLE presences ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, membre_id INT UNSIGNED NOT NULL, date_reunion DATE NOT NULL, present TINYINT(1) NOT NULL DEFAULT 1, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (membre_id) REFERENCES users(id) ON DELETE CASCADE, UNIQUE KEY uk_membre_reunion (membre_id, date_reunion) ) ENGINE=InnoDB; CREATE TABLE member_slides ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, member_id INT UNSIGNED NOT NULL, filename VARCHAR(255) NOT NULL, original_name VARCHAR(255) NOT NULL, date_key VARCHAR(8) NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (member_id) REFERENCES users(id) ON DELETE CASCADE ) ENGINE=InnoDB; INSERT INTO users (prenom, nom, societe, activite, email, telephone, password_hash, role, date_adhesion, date_echeance) VALUES ('Pierre', 'Admin', 'Dyna Biz', 'Administration', 'pierre@dynabiz.fr', '0600000001', '$2b$10$seedplaceholder','admin', '2024-01-01','2026-12-31'), ('Estelle', 'Admin', 'Dyna Biz', 'Administration', 'estelle@dynabiz.fr', '0600000002', '$2b$10$seedplaceholder','admin', '2024-01-01','2026-12-31'), ('Patricia', 'Membre', '', '', 'patricia@dynabiz.fr', '0600000003', '$2b$10$seedplaceholder','member', '2024-01-01','2026-12-31'), ('Jennifer', 'Membre', '', '', 'jennifer@dynabiz.fr', '0600000004', '$2b$10$seedplaceholder','member', '2024-01-01','2026-12-31'), ('Amandine', 'Membre', '', '', 'amandine@dynabiz.fr', '0600000005', '$2b$10$seedplaceholder','member', '2024-01-01','2026-12-31'), ('Anais', 'Membre', '', '', 'anais@dynabiz.fr', '0600000006', '$2b$10$seedplaceholder','member', '2024-01-01','2026-12-31'), ('Bastien', 'Membre', '', '', 'bastien@dynabiz.fr', '0600000007', '$2b$10$seedplaceholder','member', '2024-01-01','2026-12-31'), ('Henrique', 'Membre', '', '', 'henrique@dynabiz.fr', '0600000008', '$2b$10$seedplaceholder','member', '2024-01-01','2026-12-31'), ('Laetitia', 'Membre', '', '', 'laetitia@dynabiz.fr', '0600000009', '$2b$10$seedplaceholder','member', '2024-01-01','2026-12-31'), ('Marie', 'Membre', '', '', 'marie@dynabiz.fr', '0600000010', '$2b$10$seedplaceholder','member', '2024-01-01','2026-12-31'), ('Miguel', 'Membre', '', '', 'miguel@dynabiz.fr', '0600000011', '$2b$10$seedplaceholder','member', '2024-01-01','2026-12-31'), ('Seb', 'Membre', '', '', 'seb@dynabiz.fr', '0600000012', '$2b$10$seedplaceholder','member', '2024-01-01','2026-12-31'), ('Christelle','Membre', '', '', 'christelle@dynabiz.fr', '0600000013', '$2b$10$seedplaceholder','member', '2024-01-01','2026-12-31'), ('Mathieu', 'Membre', '', '', 'mathieu@dynabiz.fr', '0600000014', '$2b$10$seedplaceholder','member', '2024-01-01','2026-12-31'), ('Morgane', 'Membre', '', '', 'morgane@dynabiz.fr', '0600000015', '$2b$10$seedplaceholder','member', '2024-01-01','2026-12-31'); INSERT INTO evenements (date, nom_evenement, lieu, horaire) VALUES ('2025-03-14','Petit-déjeuner réseau','Hotel IBIS SQY','08h30'), ('2025-05-09','Petit-déjeuner réseau','Hotel IBIS SQY','08h30'), ('2025-06-13','Petit-déjeuner réseau','Hotel IBIS SQY','08h30'), ('2025-09-12','Petit-déjeuner réseau','Hotel IBIS SQY','08h30'), ('2025-11-14','Petit-déjeuner réseau','Hotel IBIS SQY','08h30'), ('2025-12-12','Petit-déjeuner réseau','Hotel IBIS SQY','08h30'), ('2026-01-13','Petit-déjeuner réseau','Hotel IBIS SQY','08h30'), ('2026-01-27','Petit-déjeuner réseau','Hotel IBIS SQY','08h30'), ('2026-02-10','Petit-déjeuner réseau','Hotel IBIS SQY','08h30'), ('2026-02-24','Petit-déjeuner réseau','Hotel IBIS SQY','08h30'), ('2026-03-10','Petit-déjeuner réseau','Hotel IBIS SQY','08h30'), ('2026-03-24','Petit-déjeuner réseau','Hotel IBIS SQY','08h30'), ('2026-04-07','Petit-déjeuner réseau','Hotel IBIS SQY','08h30'), ('2026-04-21','Petit-déjeuner réseau','Hotel IBIS SQY','08h30'), ('2026-05-05','Petit-déjeuner réseau','Hotel IBIS SQY','08h30'), ('2026-06-02','Petit-déjeuner réseau','Hotel IBIS SQY','08h30'), ('2026-06-12','Rendez-vous Club d''affaires','Hotel IBIS SQY','08h30'), ('2026-06-16','Petit-déjeuner réseau','Hotel IBIS SQY','08h30'), ('2026-07-10','After-work Club','Le Bistrot SQY','18h30'), ('2026-07-28','Petit-déjeuner réseau','Hotel IBIS SQY','08h30'), ('2026-09-11','Réunion plénière','CCI SQY','09h00');