-- Script SQL pour initialiser la base de données SERVICES-AE
-- À exécuter dans phpMyAdmin ou MySQL

-- Création de la base de données
CREATE DATABASE IF NOT EXISTS serv 
CHARACTER SET utf8mb4 
COLLATE utf8mb4_unicode_ci;

USE serv;

-- Table des contacts
CREATE TABLE IF NOT EXISTS contacts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    nom VARCHAR(100) NOT NULL,
    email VARCHAR(255) NOT NULL,
    telephone VARCHAR(20),
    sujet VARCHAR(255),
    message TEXT NOT NULL,
    date_creation TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    statut ENUM('nouveau', 'lu', 'traite', 'archive') DEFAULT 'nouveau',
    ip_address VARCHAR(45),
    INDEX idx_date_creation (date_creation),
    INDEX idx_statut (statut),
    INDEX idx_email (email)
) ENGINE=InnoDB;

-- Table des services
CREATE TABLE IF NOT EXISTS services (
    id INT AUTO_INCREMENT PRIMARY KEY,
    titre VARCHAR(255) NOT NULL,
    description TEXT,
    description_longue LONGTEXT,
    prix_min DECIMAL(10,2),
    prix_max DECIMAL(10,2),
    duree_estimee VARCHAR(50),
    image_url VARCHAR(255),
    actif BOOLEAN DEFAULT TRUE,
    date_creation TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    date_modification TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    ordre_affichage INT DEFAULT 0,
    INDEX idx_actif (actif),
    INDEX idx_ordre (ordre_affichage)
) ENGINE=InnoDB;

-- Table des témoignages
CREATE TABLE IF NOT EXISTS testimonials (
    id INT AUTO_INCREMENT PRIMARY KEY,
    nom_client VARCHAR(100) NOT NULL,
    email VARCHAR(255) NOT NULL,
    entreprise VARCHAR(100),
    poste VARCHAR(100),
    message TEXT NOT NULL,
    note INT CHECK (note >= 1 AND note <= 5),
    service_utilise VARCHAR(255),
    image_client VARCHAR(255),
    ip_address VARCHAR(45),
    user_agent TEXT,
    approuve BOOLEAN DEFAULT FALSE,
    affiche_page_accueil BOOLEAN DEFAULT FALSE,
    date_creation TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    date_approbation TIMESTAMP NULL,
    INDEX idx_approuve (approuve),
    INDEX idx_affiche_accueil (affiche_page_accueil),
    INDEX idx_note (note),
    INDEX idx_email (email),
    INDEX idx_date_creation (date_creation)
) ENGINE=InnoDB;

-- Table des utilisateurs admin (pour futur panel d'administration)
CREATE TABLE IF NOT EXISTS admin_users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    nom_complet VARCHAR(100),
    actif BOOLEAN DEFAULT TRUE,
    dernier_login TIMESTAMP NULL,
    date_creation TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_username (username),
    INDEX idx_email (email)
) ENGINE=InnoDB;

-- Insertion des services de base
INSERT INTO services (titre, description, description_longue, prix_min, prix_max, duree_estimee, ordre_affichage) VALUES
('Maintenance Informatique', 'Service complet de maintenance préventive et curative pour vos équipements', 'Notre service de maintenance informatique couvre tous vos besoins : nettoyage des composants, mise à jour des logiciels, optimisation des performances, diagnostic des pannes, remplacement des pièces défectueuses. Nous intervenons rapidement sur site ou en atelier selon vos besoins.', 200.00, 1500.00, '2-4 heures', 1),

('Installation Réseaux', 'Conception et installation de réseaux informatiques professionnels', 'Installation complète de réseaux câblés et Wi-Fi pour entreprises et particuliers. Nous configurons vos routeurs, switches, points d\'accès, et sécurisons votre infrastructure réseau. Support technique inclus pour optimiser vos performances réseau.', 800.00, 5000.00, '1-3 jours', 2),

('Systèmes de Surveillance', 'Solutions de vidéosurveillance et sécurité électronique', 'Installation de caméras de surveillance IP et analogiques, systèmes d\'alarme, contrôle d\'accès. Configuration à distance, stockage sécurisé, applications mobiles pour surveillance en temps réel. Devis gratuit sur site.', 1500.00, 8000.00, '1-2 jours', 3),

('Développement Web', 'Création de sites web professionnels et e-commerce', 'Développement de sites web sur mesure, boutiques en ligne, applications web. Technologies modernes : PHP, HTML5, CSS3, JavaScript. Design responsive, optimisation SEO, hébergement et maintenance inclus.', 3000.00, 15000.00, '2-6 semaines', 4),

('Support Technique', 'Assistance technique à domicile et formation utilisateurs', 'Support technique personnalisé : dépannage informatique, formation logiciels, assistance utilisateurs, récupération de données. Intervention rapide dans la région de Marrakech. Contrats de maintenance disponibles.', 150.00, 800.00, '1-4 heures', 5);

-- Insertion de témoignages d'exemple
INSERT INTO testimonials (nom_client, email, entreprise, poste, message, note, approuve, affiche_page_accueil) VALUES
('Ahmed Benali', 'ahmed@restaurant-tajine.ma', 'Restaurant Tajine d\'Or', 'Gérant', 'Excellente prestation pour l\'installation de notre réseau Wi-Fi. L\'équipe SERVICES-AE est très professionnelle et réactive. Je recommande vivement leurs services !', 5, TRUE, TRUE),

('Fatima Alaoui', 'fatima@cabinet-alaoui.ma', 'Cabinet Médical Alaoui', 'Directrice', 'Installation parfaite de notre système de surveillance. Nous nous sentons beaucoup plus en sécurité. Le support technique est toujours disponible quand nous en avons besoin.', 5, TRUE, TRUE),

('Mohamed Chakir', 'mohamed@artisanat-marrakech.ma', 'Boutique Artisanat Marrakech', 'Propriétaire', 'Site web magnifique et fonctionnel ! Nos ventes en ligne ont augmenté de 200% depuis le lancement. Merci à toute l\'équipe pour ce travail exceptionnel.', 5, TRUE, TRUE);

-- Création d'un utilisateur admin par défaut (mot de passe: admin123)
-- ATTENTION: Changer ce mot de passe en production !
INSERT INTO admin_users (username, email, password_hash, nom_complet) VALUES
('admin', 'admin@services-ae.ma', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'Administrateur SERVICES-AE');

-- Vues utiles pour les statistiques
CREATE VIEW stats_contacts AS
SELECT 
    COUNT(*) as total_contacts,
    COUNT(CASE WHEN statut = 'nouveau' THEN 1 END) as nouveaux,
    COUNT(CASE WHEN DATE(date_creation) = CURDATE() THEN 1 END) as aujourd_hui,
    COUNT(CASE WHEN date_creation >= DATE_SUB(NOW(), INTERVAL 7 DAY) THEN 1 END) as cette_semaine,
    COUNT(CASE WHEN date_creation >= DATE_SUB(NOW(), INTERVAL 30 DAY) THEN 1 END) as ce_mois
FROM contacts;

CREATE VIEW stats_services AS
SELECT 
    COUNT(*) as total_services,
    COUNT(CASE WHEN actif = TRUE THEN 1 END) as services_actifs,
    AVG(prix_min) as prix_moyen_min,
    AVG(prix_max) as prix_moyen_max
FROM services;

-- Procédures stockées utiles
DELIMITER //

CREATE PROCEDURE GetContactsByStatus(IN status_filter VARCHAR(20))
BEGIN
    SELECT * FROM contacts 
    WHERE statut = status_filter 
    ORDER BY date_creation DESC;
END //

CREATE PROCEDURE UpdateContactStatus(IN contact_id INT, IN new_status VARCHAR(20))
BEGIN
    UPDATE contacts 
    SET statut = new_status 
    WHERE id = contact_id;
END //

CREATE PROCEDURE GetMonthlyContactStats()
BEGIN
    SELECT 
        YEAR(date_creation) as annee,
        MONTH(date_creation) as mois,
        COUNT(*) as nombre_contacts
    FROM contacts 
    WHERE date_creation >= DATE_SUB(NOW(), INTERVAL 12 MONTH)
    GROUP BY YEAR(date_creation), MONTH(date_creation)
    ORDER BY annee DESC, mois DESC;
END //

DELIMITER ;

-- Index pour améliorer les performances
CREATE INDEX idx_contacts_date_statut ON contacts(date_creation, statut);
CREATE INDEX idx_testimonials_approved_homepage ON testimonials(approuve, affiche_page_accueil);

-- Commentaires sur les tables
ALTER TABLE contacts COMMENT = 'Stockage des demandes de contact via le formulaire du site';
ALTER TABLE services COMMENT = 'Catalogue des services proposés par SERVICES-AE';
ALTER TABLE testimonials COMMENT = 'Témoignages clients approuvés pour affichage public';
ALTER TABLE admin_users COMMENT = 'Utilisateurs autorisés à accéder au panel d\'administration';

COMMIT;