

-- Les statuts de prospection sont personnalisables par l'utilisateur (table statuts_prospection
-- plus bas) : plus de liste figée en CHECK, juste un texte libre qui doit correspondre à un
-- libellé existant dans cette table (contrôlé côté application, pas en contrainte SQL).
CREATE TABLE IF NOT EXISTS prospects (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  statut TEXT NOT NULL DEFAULT 'À ANALYSER',
  temperature TEXT NULL CHECK(temperature IN ('FROID','TIEDE','CHAUD','BRULANT','URGENT') OR temperature IS NULL),
  commentaire TEXT,
  referent_id INTEGER NULL,
  score INTEGER NOT NULL DEFAULT 0,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (referent_id) REFERENCES users(id) ON DELETE SET NULL
);

CREATE TABLE IF NOT EXISTS statuts_prospection (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  libelle TEXT NOT NULL UNIQUE
);
-- Pas de trigger automatique sur updated_at ici (contrairement aux autres tables) : le recalcul
-- de score (src/scoring.php) met à jour TOUS les prospects en masse, et un trigger inconditionnel
-- ferait croire au compteur "traitées aujourd'hui" (src/stats.php) que l'agent a traité des
-- dizaines de milliers de lignes. Chaque écriture représentant un vrai travail (statut,
-- température, commentaire, référent) met donc à jour `updated_at` explicitement en PHP.

CREATE TABLE IF NOT EXISTS personnes (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  prospect_id INTEGER NULL,
  civilite TEXT,
  nom TEXT,
  prenom TEXT,
  nom_usage TEXT,
  date_naissance DATE NULL,
  date_naissance_approximative INTEGER NOT NULL DEFAULT 0,
  type_proprietaire TEXT,
  telephone TEXT,
  email TEXT,
  linkedin TEXT,
  notes TEXT,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (prospect_id) REFERENCES prospects(id) ON DELETE CASCADE
);
CREATE TRIGGER IF NOT EXISTS trg_personnes_updated_at AFTER UPDATE ON personnes
BEGIN
  UPDATE personnes SET updated_at = CURRENT_TIMESTAMP WHERE id = NEW.id;
END;

CREATE TABLE IF NOT EXISTS personne_coordonnees (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  personne_id INTEGER NOT NULL,
  type TEXT NOT NULL CHECK(type IN ('telephone','email')),
  valeur TEXT NOT NULL,
  label TEXT,
  FOREIGN KEY (personne_id) REFERENCES personnes(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS societes (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  prospect_id INTEGER NULL,
  raison_sociale TEXT NOT NULL,
  nom_commercial TEXT,
  siren TEXT,
  siret TEXT,
  adresse TEXT,
  ville TEXT,
  code_postal TEXT,
  forme_juridique TEXT,
  ape_code TEXT,
  procedure_collective TEXT,
  web TEXT,
  dirigeant_id INTEGER 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
);

CREATE TABLE IF NOT EXISTS actifs (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  adresse TEXT,
  ville TEXT,
  code_postal TEXT,
  surface_totale REAL,
  activite TEXT,
  ape_libelle TEXT,
  proprietaire_exploitant TEXT,
  retail_park INTEGER NOT NULL DEFAULT 0,
  type_actif TEXT NULL,
  commentaire TEXT NULL,
  societe_proprietaire_id INTEGER NULL,
  dossier_id INTEGER NULL,
  donnees_brutes TEXT,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (societe_proprietaire_id) REFERENCES societes(id) ON DELETE SET NULL,
  FOREIGN KEY (dossier_id) REFERENCES dossiers(id) ON DELETE SET NULL
);

CREATE TABLE IF NOT EXISTS evenements (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  entite_type TEXT NOT NULL CHECK(entite_type IN ('personne','prospect','dossier','investisseur')),
  entite_id INTEGER NOT NULL,
  type TEXT NOT NULL CHECK(type IN ('relance','rdv','appel','visite','courrier_envoye','sms','estimation','autre')),
  date_prevue DATE NOT NULL,
  statut TEXT NOT NULL DEFAULT 'a_faire' CHECK(statut IN ('a_faire','fait','annule')),
  note TEXT,
  resultat TEXT,
  recurrence TEXT NULL CHECK(recurrence IN ('quotidienne','hebdomadaire','mensuelle','annuelle') OR recurrence IS NULL),
  created_by INTEGER NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  done_at DATETIME NULL,
  FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
);
CREATE INDEX IF NOT EXISTS idx_evenements_entite ON evenements(entite_type, entite_id);
CREATE INDEX IF NOT EXISTS idx_evenements_date ON evenements(date_prevue);

CREATE TABLE IF NOT EXISTS investisseurs (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  personne_id INTEGER NULL,
  societe_id INTEGER NULL,
  statut TEXT NOT NULL DEFAULT 'A_CONTACTER' CHECK(statut IN ('A_CONTACTER','EN_QUALIFICATION','ACTIF','EN_VEILLE','SANS_SUITE','CLIENT')),
  budget_min REAL,
  budget_max REAL,
  rentabilite_cible REAL,
  zones TEXT,
  pays TEXT DEFAULT 'France',
  strategie TEXT,
  mode_acquisition TEXT,
  commentaire TEXT,
  referent_id INTEGER NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT 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
);
CREATE TRIGGER IF NOT EXISTS trg_investisseurs_updated_at AFTER UPDATE ON investisseurs
BEGIN
  UPDATE investisseurs SET updated_at = CURRENT_TIMESTAMP WHERE id = NEW.id;
END;

CREATE TABLE IF NOT EXISTS types_biens (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  libelle TEXT NOT NULL UNIQUE
);

CREATE TABLE IF NOT EXISTS investisseur_criteres (
  investisseur_id INTEGER NOT NULL,
  type_bien_id INTEGER 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
);

CREATE TABLE IF NOT EXISTS dossiers (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  nom TEXT NOT NULL,
  type_dossier TEXT NOT NULL DEFAULT 'mandat_propre' CHECK(type_dossier IN ('mandat_propre','confrere','off_market')),
  statut TEXT NOT NULL DEFAULT 'MANDAT_SIGNE' CHECK(statut IN ('MANDAT_SIGNE','EN_COMMERCIALISATION','SOUS_OFFRE','SOUS_COMPROMIS','VENDU','EN_STAND_BY','A_ACTUALISER','RETIRE')),
  ville TEXT,
  adresse TEXT,
  prix_net_vendeur REAL,
  honoraires_pourcentage REAL,
  rentabilite REAL,
  societe_proprietaire_id INTEGER NULL,
  actif_id INTEGER NULL,
  commentaire TEXT,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT 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
);
CREATE TRIGGER IF NOT EXISTS trg_dossiers_updated_at AFTER UPDATE ON dossiers
BEGIN
  UPDATE dossiers SET updated_at = CURRENT_TIMESTAMP WHERE id = NEW.id;
END;

CREATE TABLE IF NOT EXISTS dossier_referents (
  dossier_id INTEGER NOT NULL,
  user_id INTEGER 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
);

CREATE TABLE IF NOT EXISTS dossier_lots (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  dossier_id INTEGER NOT NULL,
  occupant TEXT,
  activite TEXT,
  surface_rdc REAL,
  surface_sous_sol REAL,
  surface_etage REAL,
  surface_totale REAL,
  loyer_annuel REAL,
  type_bail TEXT,
  date_debut_bail DATE,
  date_fin_bail DATE,
  FOREIGN KEY (dossier_id) REFERENCES dossiers(id) ON DELETE CASCADE
);

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

INSERT OR 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 OR IGNORE INTO statuts_prospection (libelle) VALUES
  ('À ANALYSER'), ('À CONTACTER'), ('BIEN VENDU PAR UN CONCURRENT'), ('DOSSIER À VENDRE'),
  ('REFUS DÉFINITIF'), ('VENDEUR OPPORTUNISTE'), ('VENDEUR PROCHAINEMENT');

-- 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 INTEGER PRIMARY KEY AUTOINCREMENT,
  nom TEXT NOT NULL UNIQUE,
  querystring TEXT NOT NULL,
  created_by INTEGER NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
);
