-- Schéma V1 — CRM murs commerciaux (v2 : prospects multi-contacts)
-- Voir /Users/yonabenayoun/.claude/plans/iterative-finding-unicorn.md pour le contexte complet

CREATE DATABASE IF NOT EXISTS crm_murs_commerciaux CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE crm_murs_commerciaux;

-- Un "prospect" est le dossier de suivi central : il peut regrouper plusieurs contacts
-- (associés, co-indivisaires...) et plusieurs sociétés/actifs. Remplace l'ancienne table
-- "prospection" qui ne supportait qu'un seul contact par prospect.
-- Le statut est un texte libre (pas un ENUM) car il est personnalisable par l'utilisateur,
-- contrôlé côté application via la table statuts_prospection ci-dessous.
CREATE TABLE IF NOT EXISTS prospects (
  id INT AUTO_INCREMENT PRIMARY KEY,
  statut VARCHAR(100) NOT NULL DEFAULT 'À ANALYSER',
  temperature ENUM('FROID','TIEDE','CHAUD','BRULANT','URGENT') NULL,
  commentaire TEXT,
  referent_id INT NULL,
  score INT NOT NULL DEFAULT 0,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  -- Pas de "ON UPDATE CURRENT_TIMESTAMP" ici : le recalcul de score met à jour tous les
  -- prospects en masse, et un bump automatique casserait le compteur "traitées aujourd'hui"
  -- (src/stats.php) en comptant ces mises à jour de masse comme du travail de l'agent. Chaque
  -- écriture représentant un vrai travail (statut/température/commentaire/référent) met donc
  -- à jour updated_at explicitement en PHP.
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (referent_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS statuts_prospection (
  id INT AUTO_INCREMENT PRIMARY KEY,
  libelle VARCHAR(100) NOT NULL UNIQUE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS personnes (
  id INT AUTO_INCREMENT PRIMARY KEY,
  prospect_id INT NULL,
  civilite VARCHAR(10),
  nom VARCHAR(150),
  prenom VARCHAR(150),
  nom_usage VARCHAR(150),
  date_naissance DATE NULL,
  date_naissance_approximative TINYINT(1) NOT NULL DEFAULT 0,
  type_proprietaire VARCHAR(100),
  telephone VARCHAR(50),
  email VARCHAR(190),
  linkedin VARCHAR(255),
  notes TEXT,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (prospect_id) REFERENCES prospects(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- Coordonnées supplémentaires (un contact peut avoir plusieurs téléphones/emails)
CREATE TABLE IF NOT EXISTS personne_coordonnees (
  id INT AUTO_INCREMENT PRIMARY KEY,
  personne_id INT NOT NULL,
  type ENUM('telephone','email') NOT NULL,
  valeur VARCHAR(190) NOT NULL,
  label VARCHAR(100),
  FOREIGN KEY (personne_id) REFERENCES personnes(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS societes (
  id INT AUTO_INCREMENT PRIMARY KEY,
  prospect_id INT NULL,
  raison_sociale VARCHAR(255) NOT NULL,
  nom_commercial VARCHAR(255),
  siren VARCHAR(20),
  siret VARCHAR(20),
  adresse VARCHAR(255),
  ville VARCHAR(150),
  code_postal VARCHAR(10),
  forme_juridique VARCHAR(100),
  ape_code VARCHAR(10),
  procedure_collective VARCHAR(150),
  web VARCHAR(255),
  dirigeant_id INT NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (prospect_id) REFERENCES prospects(id) ON DELETE CASCADE,
  FOREIGN KEY (dirigeant_id) REFERENCES personnes(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS actifs (
  id INT AUTO_INCREMENT PRIMARY KEY,
  adresse VARCHAR(255),
  ville VARCHAR(150),
  code_postal VARCHAR(10),
  surface_totale DECIMAL(10,2),
  activite VARCHAR(255),
  ape_libelle VARCHAR(255),
  proprietaire_exploitant VARCHAR(150),
  retail_park TINYINT(1) NOT NULL DEFAULT 0,
  type_actif VARCHAR(50) NULL,
  commentaire TEXT NULL,
  societe_proprietaire_id INT NULL,
  dossier_id INT NULL COMMENT 'FK vers dossiers ajoutée plus bas (dossiers est défini après actifs dans ce fichier)',
  donnees_brutes TEXT COMMENT 'Colonnes du CSV source non modélisées, conservées en JSON pour ne rien perdre',
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (societe_proprietaire_id) REFERENCES societes(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS evenements (
  id INT AUTO_INCREMENT PRIMARY KEY,
  entite_type ENUM('personne','prospect','dossier','investisseur') NOT NULL,
  entite_id INT NOT NULL,
  type ENUM('relance','rdv','appel','visite','courrier_envoye','sms','estimation','autre') NOT NULL,
  date_prevue DATE NOT NULL,
  statut ENUM('a_faire','fait','annule') NOT NULL DEFAULT 'a_faire',
  note TEXT,
  resultat VARCHAR(190) NULL,
  recurrence ENUM('quotidienne','hebdomadaire','mensuelle','annuelle') NULL,
  created_by INT NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  done_at DATETIME NULL,
  FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL,
  INDEX idx_entite (entite_type, entite_id),
  INDEX idx_date (date_prevue)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS investisseurs (
  id INT AUTO_INCREMENT PRIMARY KEY,
  personne_id INT NULL,
  societe_id INT NULL,
  statut ENUM('A_CONTACTER','EN_QUALIFICATION','ACTIF','EN_VEILLE','SANS_SUITE','CLIENT') NOT NULL DEFAULT 'A_CONTACTER',
  budget_min DECIMAL(14,2),
  budget_max DECIMAL(14,2),
  rentabilite_cible DECIMAL(5,2),
  zones VARCHAR(255),
  pays VARCHAR(100) DEFAULT 'France',
  strategie VARCHAR(255),
  mode_acquisition VARCHAR(150),
  commentaire TEXT,
  referent_id INT NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (personne_id) REFERENCES personnes(id) ON DELETE SET NULL,
  FOREIGN KEY (societe_id) REFERENCES societes(id) ON DELETE SET NULL,
  FOREIGN KEY (referent_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS types_biens (
  id INT AUTO_INCREMENT PRIMARY KEY,
  libelle VARCHAR(100) NOT NULL UNIQUE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS investisseur_criteres (
  investisseur_id INT NOT NULL,
  type_bien_id INT NOT NULL,
  PRIMARY KEY (investisseur_id, type_bien_id),
  FOREIGN KEY (investisseur_id) REFERENCES investisseurs(id) ON DELETE CASCADE,
  FOREIGN KEY (type_bien_id) REFERENCES types_biens(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS dossiers (
  id INT AUTO_INCREMENT PRIMARY KEY,
  nom VARCHAR(255) NOT NULL,
  type_dossier ENUM('mandat_propre','confrere','off_market') NOT NULL DEFAULT 'mandat_propre',
  statut ENUM('MANDAT_SIGNE','EN_COMMERCIALISATION','SOUS_OFFRE','SOUS_COMPROMIS','VENDU','EN_STAND_BY','A_ACTUALISER','RETIRE') NOT NULL DEFAULT 'MANDAT_SIGNE',
  ville VARCHAR(150),
  adresse VARCHAR(255),
  prix_net_vendeur DECIMAL(14,2),
  honoraires_pourcentage DECIMAL(5,2),
  rentabilite DECIMAL(5,2),
  societe_proprietaire_id INT NULL,
  actif_id INT NULL,
  commentaire TEXT,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (societe_proprietaire_id) REFERENCES societes(id) ON DELETE SET NULL,
  FOREIGN KEY (actif_id) REFERENCES actifs(id) ON DELETE SET NULL
) ENGINE=InnoDB;

-- actifs.dossier_id référence dossiers, défini seulement au-dessus : la FK est ajoutée ici plutôt
-- qu'en ligne dans CREATE TABLE actifs (MySQL exige que la table référencée existe déjà).
ALTER TABLE actifs ADD CONSTRAINT fk_actifs_dossier FOREIGN KEY (dossier_id) REFERENCES dossiers(id) ON DELETE SET NULL;

CREATE TABLE IF NOT EXISTS dossier_referents (
  dossier_id INT NOT NULL,
  user_id INT NOT NULL,
  PRIMARY KEY (dossier_id, user_id),
  FOREIGN KEY (dossier_id) REFERENCES dossiers(id) ON DELETE CASCADE,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS dossier_lots (
  id INT AUTO_INCREMENT PRIMARY KEY,
  dossier_id INT NOT NULL,
  occupant VARCHAR(255),
  activite VARCHAR(255),
  surface_rdc DECIMAL(10,2),
  surface_sous_sol DECIMAL(10,2),
  surface_etage DECIMAL(10,2),
  surface_totale DECIMAL(10,2),
  loyer_annuel DECIMAL(14,2),
  type_bail VARCHAR(100),
  date_debut_bail DATE,
  date_fin_bail DATE,
  FOREIGN KEY (dossier_id) REFERENCES dossiers(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE INDEX idx_personnes_prospect ON personnes(prospect_id);
CREATE INDEX idx_personnes_identite ON personnes(nom, prenom, date_naissance);
CREATE INDEX idx_societes_prospect ON societes(prospect_id);
CREATE INDEX idx_societes_siren ON societes(siren);
CREATE INDEX idx_societes_dirigeant ON societes(dirigeant_id);
CREATE INDEX idx_actifs_societe ON actifs(societe_proprietaire_id);
CREATE INDEX idx_actifs_dossier ON actifs(dossier_id);
CREATE INDEX idx_dossiers_actif ON dossiers(actif_id);
CREATE INDEX idx_prospects_statut ON prospects(statut);
CREATE INDEX idx_prospects_temperature ON prospects(temperature);
CREATE INDEX idx_prospects_score ON prospects(score);
CREATE INDEX idx_investisseurs_personne ON investisseurs(personne_id);
CREATE INDEX idx_investisseurs_societe ON investisseurs(societe_id);
CREATE INDEX idx_investisseurs_statut ON investisseurs(statut);
CREATE INDEX idx_dossiers_statut ON dossiers(statut);
CREATE INDEX idx_dossiers_societe ON dossiers(societe_proprietaire_id);
CREATE INDEX idx_dossier_lots_dossier ON dossier_lots(dossier_id);

-- Vues sauvegardées : une combinaison de filtres nommée par l'utilisateur (querystring brute de
-- la liste de prospection), pour retrouver et réappliquer un filtrage complexe en un clic.
CREATE TABLE IF NOT EXISTS vues_sauvegardees_prospection (
  id INT AUTO_INCREMENT PRIMARY KEY,
  nom VARCHAR(150) NOT NULL UNIQUE,
  querystring VARCHAR(2000) NOT NULL,
  created_by INT NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

INSERT IGNORE INTO types_biens (libelle) VALUES
  ('CV Prime'), ('Retail Park'), ('Hôtel'), ('Coliving'), ('Airbnb'),
  ('Resto / Supermarché'), ('Industriel'), ('Galerie / CC'), ('Résidence'),
  ('Immeuble mixte'), ('Logistique'), ('Bureaux'), ("Locaux d'activités"),
  ('Habitation'), ('Terrain'), ('Santé'), ('Hôtel murs / fonds');

-- Statuts de prospection (axe "état du prospect", voir /Users/yonabenayoun/.claude/plans/iterative-finding-unicorn.md) :
-- volontairement séparé des actions (courrier envoyé, relance) qui vivent dans `evenements`.
INSERT IGNORE INTO statuts_prospection (libelle) VALUES
  ('À ANALYSER'), ('À CONTACTER'), ('BIEN VENDU PAR UN CONCURRENT'), ('DOSSIER À VENDRE'),
  ('REFUS DÉFINITIF'), ('VENDEUR OPPORTUNISTE'), ('VENDEUR PROCHAINEMENT');
