-- Base de données pour site immobilier
-- Créé par : Reoleweb
-- Date : 2025-10-25

-- Suppression des tables existantes (dans l'ordre des dépendances)
DROP TABLE IF EXISTS `statistiques_vues`;
DROP TABLE IF EXISTS `avis`;
DROP TABLE IF EXISTS `contacts`;
DROP TABLE IF EXISTS `alertes_recherche`;
DROP TABLE IF EXISTS `favoris`;
DROP TABLE IF EXISTS `photos_annonces`;
DROP TABLE IF EXISTS `annonces`;
DROP TABLE IF EXISTS `types_transaction`;
DROP TABLE IF EXISTS `categories_biens`;
DROP TABLE IF EXISTS `users`;
DROP TABLE IF EXISTS `roles`;
DROP TABLE IF EXISTS `parametres_systeme`;

-- Table des rôles
CREATE TABLE `roles` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `nom_role` varchar(50) NOT NULL,
  `description` text,
  `niveau_acces` int(11) NOT NULL DEFAULT 1,
  `permissions` text,
  `date_creation` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `actif` tinyint(1) NOT NULL DEFAULT 1,
  PRIMARY KEY (`id`),
  UNIQUE KEY `nom_role` (`nom_role`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Insertion des rôles de base
INSERT INTO `roles` (`nom_role`, `description`, `niveau_acces`, `permissions`) VALUES
('Super Administrateur', 'Accès complet à toutes les fonctionnalités', 100, '{"manage_all": true, "manage_users": true, "manage_settings": true, "view_stats": true}'),
('Administrateur', 'Gestion des annonces et utilisateurs', 80, '{"manage_listings": true, "manage_users": true, "moderate_content": true, "view_reports": true}'),
('Modérateur', 'Validation des annonces et modération', 60, '{"validate_listings": true, "moderate_comments": true, "handle_reports": true}'),
('Agent Premium', 'Agent immobilier avec accès étendu', 50, '{"unlimited_listings": true, "premium_features": true, "detailed_stats": true}'),
('Agent Standard', 'Agent immobilier standard', 40, '{"limited_listings": true, "basic_stats": true, "manage_own_listings": true}'),
('Particulier', 'Utilisateur particulier', 20, '{"limited_listings": true, "manage_own_listings": true}'),
('Visiteur', 'Utilisateur visiteur', 10, '{"view_listings": true, "contact_sellers": true}');

-- Table des utilisateurs
CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(50) NOT NULL,
  `email` varchar(100) NOT NULL,
  `password` varchar(255) NOT NULL,
  `role_id` int(11) NOT NULL DEFAULT 7,
  `prenom` varchar(50),
  `nom` varchar(50),
  `telephone` varchar(20),
  `adresse` text,
  `ville` varchar(50),
  `code_postal` varchar(10),
  `pays` varchar(50) DEFAULT 'France',
  `photo_profil` varchar(255),
  `description` text,
  `date_creation` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `derniere_connexion` datetime NULL,
  `actif` tinyint(1) NOT NULL DEFAULT 1,
  `email_verifie` tinyint(1) NOT NULL DEFAULT 0,
  `telephone_verifie` tinyint(1) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  UNIQUE KEY `username` (`username`),
  UNIQUE KEY `email` (`email`),
  KEY `role_id` (`role_id`),
  FOREIGN KEY (`role_id`) REFERENCES `roles` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table des catégories de biens
CREATE TABLE `categories_biens` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `nom` varchar(50) NOT NULL,
  `description` text,
  `icone` varchar(50),
  `ordre_affichage` int(11) DEFAULT 0,
  `actif` tinyint(1) NOT NULL DEFAULT 1,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Insertion des catégories de base
INSERT INTO `categories_biens` (`nom`, `description`, `icone`, `ordre_affichage`) VALUES
('Appartement', 'Appartements et studios', 'fas fa-building', 1),
('Maison', 'Maisons individuelles', 'fas fa-home', 2),
('Villa', 'Villas et propriétés de luxe', 'fas fa-crown', 3),
('Terrain', 'Terrains constructibles et non constructibles', 'fas fa-map', 4),
('Local Commercial', 'Locaux commerciaux et bureaux', 'fas fa-store', 5),
('Garage', 'Garages et parkings', 'fas fa-car', 6);

-- Table des types de transaction
CREATE TABLE `types_transaction` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `nom` varchar(30) NOT NULL,
  `description` varchar(100),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `types_transaction` (`nom`, `description`) VALUES
('Vente', 'Vente définitive du bien'),
('Location', 'Location du bien'),
('Location saisonnière', 'Location courte durée');

-- Table des annonces
CREATE TABLE `annonces` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `categorie_id` int(11) NOT NULL,
  `type_transaction_id` int(11) NOT NULL,
  `titre` varchar(200) NOT NULL,
  `description` text,
  `prix` decimal(12,2) NOT NULL,
  `surface` decimal(8,2),
  `nb_pieces` int(11),
  `nb_chambres` int(11),
  `nb_salles_bain` int(11),
  `nb_etages` int(11),
  `annee_construction` int(11),
  `adresse` varchar(255),
  `ville` varchar(100) NOT NULL,
  `code_postal` varchar(10),
  `departement` varchar(50),
  `region` varchar(50),
  `pays` varchar(50) DEFAULT 'France',
  `latitude` decimal(10,8),
  `longitude` decimal(11,8),
  `caracteristiques` text,
  `date_creation` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `date_modification` datetime NULL,
  `date_publication` datetime NULL,
  `date_expiration` datetime NULL,
  `statut` enum('brouillon','en_attente','publiee','vendue','louee','expiree','supprimee') NOT NULL DEFAULT 'brouillon',
  `mise_en_avant` tinyint(1) NOT NULL DEFAULT 0,
  `date_mise_en_avant` datetime NULL,
  `vues` int(11) NOT NULL DEFAULT 0,
  `contacts` int(11) NOT NULL DEFAULT 0,
  `actif` tinyint(1) NOT NULL DEFAULT 1,
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`),
  KEY `categorie_id` (`categorie_id`),
  KEY `type_transaction_id` (`type_transaction_id`),
  KEY `ville` (`ville`),
  KEY `prix` (`prix`),
  KEY `statut` (`statut`),
  KEY `date_publication` (`date_publication`),
  FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  FOREIGN KEY (`categorie_id`) REFERENCES `categories_biens` (`id`),
  FOREIGN KEY (`type_transaction_id`) REFERENCES `types_transaction` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table des photos d'annonces
CREATE TABLE `photos_annonces` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `annonce_id` int(11) NOT NULL,
  `nom_fichier` varchar(255) NOT NULL,
  `chemin_fichier` varchar(500) NOT NULL,
  `alt_text` varchar(255),
  `ordre_affichage` int(11) DEFAULT 0,
  `principale` tinyint(1) NOT NULL DEFAULT 0,
  `date_upload` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `annonce_id` (`annonce_id`),
  FOREIGN KEY (`annonce_id`) REFERENCES `annonces` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table des favoris
CREATE TABLE `favoris` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `annonce_id` int(11) NOT NULL,
  `date_ajout` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `user_annonce` (`user_id`, `annonce_id`),
  KEY `user_id` (`user_id`),
  KEY `annonce_id` (`annonce_id`),
  FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  FOREIGN KEY (`annonce_id`) REFERENCES `annonces` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table des alertes de recherche
CREATE TABLE `alertes_recherche` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `nom_alerte` varchar(100) NOT NULL,
  `criteres_recherche` text NOT NULL,
  `frequence` enum('immediate','quotidienne','hebdomadaire') NOT NULL DEFAULT 'quotidienne',
  `active` tinyint(1) NOT NULL DEFAULT 1,
  `date_creation` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `derniere_execution` datetime NULL,
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`),
  FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table des contacts/messages
CREATE TABLE `contacts` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `annonce_id` int(11) NOT NULL,
  `expediteur_id` int(11) NULL,
  `nom` varchar(100),
  `email` varchar(100) NOT NULL,
  `telephone` varchar(20),
  `message` text NOT NULL,
  `date_envoi` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `lu` tinyint(1) NOT NULL DEFAULT 0,
  `repondu` tinyint(1) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  KEY `annonce_id` (`annonce_id`),
  KEY `expediteur_id` (`expediteur_id`),
  FOREIGN KEY (`annonce_id`) REFERENCES `annonces` (`id`) ON DELETE CASCADE,
  FOREIGN KEY (`expediteur_id`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table des avis/commentaires
CREATE TABLE `avis` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `annonce_id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `note` int(11) NOT NULL CHECK (`note` >= 1 AND `note` <= 5),
  `commentaire` text,
  `date_creation` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `modere` tinyint(1) NOT NULL DEFAULT 0,
  `valide` tinyint(1) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  KEY `annonce_id` (`annonce_id`),
  KEY `user_id` (`user_id`),
  FOREIGN KEY (`annonce_id`) REFERENCES `annonces` (`id`) ON DELETE CASCADE,
  FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table des statistiques de consultation
CREATE TABLE `statistiques_vues` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `annonce_id` int(11) NOT NULL,
  `user_id` int(11) NULL,
  `ip_address` varchar(45),
  `user_agent` text,
  `date_vue` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `annonce_id` (`annonce_id`),
  KEY `user_id` (`user_id`),
  KEY `date_vue` (`date_vue`),
  FOREIGN KEY (`annonce_id`) REFERENCES `annonces` (`id`) ON DELETE CASCADE,
  FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table des paramètres système
CREATE TABLE `parametres_systeme` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `cle` varchar(100) NOT NULL,
  `valeur` text,
  `description` text,
  `type` enum('string','integer','boolean','text') NOT NULL DEFAULT 'string',
  `date_modification` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `cle` (`cle`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Insertion des paramètres de base
INSERT INTO `parametres_systeme` (`cle`, `valeur`, `description`, `type`) VALUES
('site_nom', 'Reoleweb Immobilier', 'Nom du site', 'string'),
('max_annonces_particulier', '3', 'Nombre maximum d\'annonces pour un particulier', 'integer'),
('max_annonces_agent_standard', '10', 'Nombre maximum d\'annonces pour un agent standard', 'integer'),
('duree_validite_annonce', '90', 'Durée de validité d\'une annonce en jours', 'integer'),
('moderation_active', '1', 'Active la modération des annonces', 'boolean'),
('email_admin', 'admin@reoleweb.com', 'Email de l\'administrateur', 'string');

-- Index pour optimiser les performances
CREATE INDEX idx_annonces_search ON annonces (ville, type_transaction_id, categorie_id, prix, statut);
CREATE INDEX idx_annonces_location ON annonces (latitude, longitude);