-- ══════════════════════════════════════════════════════════════════
--  KAP DSI — Schéma base de données MySQL v5
--  Architecture : Sociétés → Contacts (n), Documents sur disque
--  Base propre — tout recréer depuis zéro
-- ══════════════════════════════════════════════════════════════════

SET NAMES utf8mb4;
SET time_zone = '+01:00';
SET FOREIGN_KEY_CHECKS = 0;

-- ── Utilisateurs CRM ──────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `crm_users` (
  `id`            CHAR(36)     NOT NULL,
  `login`         VARCHAR(100) NOT NULL,
  `email`         VARCHAR(255) NOT NULL,
  `pwd_hash`      VARCHAR(255) NOT NULL,
  `nom`           VARCHAR(100) NOT NULL,
  `prenom`        VARCHAR(100) NOT NULL,
  `role`          ENUM('super_admin','user') NOT NULL DEFAULT 'user',
  `actif`         TINYINT(1)   NOT NULL DEFAULT 1,
  `date_creation` DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `derniere_cnx`  DATETIME     DEFAULT NULL,
  `date_maj`      DATETIME     DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_login` (`login`),
  UNIQUE KEY `uq_email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Super admin par défaut (login: thierry / mdp: Kapdsi@Thierry!)
INSERT IGNORE INTO `crm_users` (`id`,`login`,`email`,`pwd_hash`,`nom`,`prenom`,`role`)
VALUES (
  'crm-0000-0000-0000-000000000001',
  'thierry','thierry.dalaigre@kapdsi.fr',
  '$2y$12$htjWnMdE2IdNXToNt4hAHOkgEyWUXX.7H/eKL4fagjVmUYBFYcU8K',
  'Dalaigre','Thierry','super_admin'
);

-- ── Sociétés ──────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `societes` (
  `id`               CHAR(36)     NOT NULL,
  -- Identité
  `nom`              VARCHAR(255) NOT NULL,
  `siren`            VARCHAR(9)   DEFAULT NULL,
  `siret`            VARCHAR(14)  DEFAULT NULL,
  `tva_intra`        VARCHAR(20)  DEFAULT NULL,
  `site_web`         VARCHAR(255) DEFAULT NULL,
  -- Adresse
  `adresse1`         VARCHAR(255) DEFAULT NULL,
  `adresse2`         VARCHAR(255) DEFAULT NULL,
  `code_postal`      VARCHAR(10)  DEFAULT NULL,
  `ville`            VARCHAR(100) DEFAULT NULL,
  `pays`             VARCHAR(100) NOT NULL DEFAULT 'France',
  -- CRM
  `type_contact`     ENUM('prospect','client','inactif','perdu') NOT NULL DEFAULT 'prospect',
  `source`           ENUM('recommandation','kairos','linkedin','site_web','salon','autre') DEFAULT NULL,
  `secteur`          VARCHAR(100) DEFAULT NULL,
  `taille`           ENUM('TPE','PME','ETI','GE') DEFAULT NULL,
  `ca_fourchette`    ENUM('<500k','500k-2m','2m-10m','>10m') DEFAULT NULL,
  `prochain_contact`       DATE         DEFAULT NULL,
  `prochain_contact_objet` VARCHAR(255) DEFAULT NULL,
  `notes`            TEXT         DEFAULT NULL,
  -- Pipeline
  `pipeline_stade`      ENUM('decouverte','proposition','negociation','gagne','perdu') DEFAULT NULL,
  `pipeline_montant`    DECIMAL(10,2) DEFAULT NULL,
  `pipeline_proba`      TINYINT(3)    DEFAULT NULL,
  `pipeline_date_close` DATE          DEFAULT NULL,
  -- Responsable CRM
  `crm_user_id`      CHAR(36)     DEFAULT NULL,
  -- Méta
  `date_creation`    DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `date_maj`         DATETIME     DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_type` (`type_contact`),
  KEY `idx_pipeline` (`pipeline_stade`),
  KEY `idx_prochain` (`prochain_contact`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── Contacts (personnes) ──────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `contacts` (
  `id`            CHAR(36)     NOT NULL,
  `societe_id`    CHAR(36)     NOT NULL,
  -- Identité
  `prenom`        VARCHAR(100) NOT NULL DEFAULT '',
  `nom`           VARCHAR(100) NOT NULL,
  `poste`         VARCHAR(100) DEFAULT NULL,
  -- Coordonnées
  `email`         VARCHAR(255) DEFAULT NULL,
  `telephone`     VARCHAR(50)  DEFAULT NULL,
  `telephone2`    VARCHAR(50)  DEFAULT NULL,
  -- Espace client
  `pwd_hash`      VARCHAR(255) DEFAULT NULL,
  `statut_espace` ENUM('non_invite','pending','invited','active','inactive') NOT NULL DEFAULT 'non_invite',
  `date_invitation` DATETIME   DEFAULT NULL,
  -- CRM
  `est_principal` TINYINT(1)   NOT NULL DEFAULT 0,   -- contact principal de la société
  `notes`         TEXT         DEFAULT NULL,
  -- Méta
  `date_creation` DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `date_maj`      DATETIME     DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_societe` (`societe_id`),
  KEY `idx_email` (`email`),
  CONSTRAINT `fk_contact_societe`
    FOREIGN KEY (`societe_id`) REFERENCES `societes` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── Documents ─────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `documents` (
  `id`            CHAR(36)     NOT NULL,
  `societe_id`    CHAR(36)     NOT NULL,
  -- Métadonnées
  `categorie`     VARCHAR(50)  NOT NULL DEFAULT 'autre',
                               -- devis, proposition, contrat, brochure, rapport, autre
  `titre`         VARCHAR(255) NOT NULL,
  `description`   TEXT         DEFAULT NULL,
  `version`       VARCHAR(20)  DEFAULT NULL,  -- ex: v1.2, 2025-03
  `tags`          VARCHAR(255) DEFAULT NULL,  -- mots-clés séparés par virgule
  -- Fichier (stocké sur disque)
  `file_path`     VARCHAR(500) NOT NULL,      -- chemin relatif depuis uploads/
  `file_name`     VARCHAR(255) NOT NULL,      -- nom original du fichier
  `file_ext`      VARCHAR(10)  NOT NULL,      -- pdf, docx, xlsx, jpg…
  `file_size`     INT          NOT NULL,      -- octets
  `mime_type`     VARCHAR(100) DEFAULT NULL,
  -- Auteur
  `crm_user_id`   CHAR(36)     DEFAULT NULL,
  -- Méta
  `date_creation` DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `date_maj`      DATETIME     DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_societe` (`societe_id`),
  KEY `idx_categorie` (`categorie`),
  CONSTRAINT `fk_doc_societe`
    FOREIGN KEY (`societe_id`) REFERENCES `societes` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── Interactions ──────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `interactions` (
  `id`           CHAR(36)     NOT NULL,
  `societe_id`   CHAR(36)     NOT NULL,
  `contact_id`   CHAR(36)     DEFAULT NULL,  -- optionnel : contact spécifique
  `crm_user_id`  CHAR(36)     NOT NULL,
  `type`         ENUM('appel','email','rdv','proposition','relance','note','linkedin','autre') NOT NULL,
  `date`         DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `duree_min`    SMALLINT     DEFAULT NULL,
  `resume`       TEXT         NOT NULL,
  `suite`        TEXT         DEFAULT NULL,
  `date_creation` DATETIME   NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_societe` (`societe_id`),
  KEY `idx_contact` (`contact_id`),
  KEY `idx_date` (`date`),
  CONSTRAINT `fk_int_societe`
    FOREIGN KEY (`societe_id`) REFERENCES `societes` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_int_contact`
    FOREIGN KEY (`contact_id`) REFERENCES `contacts` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_int_user`
    FOREIGN KEY (`crm_user_id`) REFERENCES `crm_users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── Missions ──────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `missions` (
  `id`           CHAR(36)     NOT NULL,
  `societe_id`   CHAR(36)     NOT NULL,
  `crm_user_id`  CHAR(36)     DEFAULT NULL,
  `intitule`     VARCHAR(255) NOT NULL,
  `description`  TEXT         DEFAULT NULL,
  `statut`       ENUM('en_cours','suspendue','terminee') NOT NULL DEFAULT 'en_cours',
  `date_debut`   DATE         DEFAULT NULL,
  `date_fin`     DATE         DEFAULT NULL,
  `jours_mois`   DECIMAL(4,1) DEFAULT NULL,
  `tarif_jour`   DECIMAL(8,2) DEFAULT NULL,
  `date_creation` DATETIME   NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `date_maj`     DATETIME     DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_societe` (`societe_id`),
  CONSTRAINT `fk_mission_societe`
    FOREIGN KEY (`societe_id`) REFERENCES `societes` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── Factures ──────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `factures` (
  `id`             CHAR(36)     NOT NULL,
  `societe_id`     CHAR(36)     NOT NULL,
  `contact_id`     CHAR(36)     DEFAULT NULL,  -- contact destinataire
  `mission_id`     CHAR(36)     DEFAULT NULL,
  `numero`         VARCHAR(50)  NOT NULL,
  `libelle`        VARCHAR(255) DEFAULT NULL,
  `montant_ht`     DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  `tva`            DECIMAL(5,2) NOT NULL DEFAULT 20.00,
  `date_emission`  DATE         DEFAULT NULL,
  `date_echeance`  DATE         DEFAULT NULL,
  `statut`         ENUM('en attente','payée','en retard') NOT NULL DEFAULT 'en attente',
  -- Fichier stocké sur disque (comme les documents)
  `file_path`      VARCHAR(500) DEFAULT NULL,
  `file_name`      VARCHAR(255) DEFAULT NULL,
  `file_size`      INT          DEFAULT NULL,
  `date_upload`    DATETIME     DEFAULT NULL,
  `date_creation`  DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_societe` (`societe_id`),
  KEY `idx_statut` (`statut`),
  CONSTRAINT `fk_facture_societe`
    FOREIGN KEY (`societe_id`) REFERENCES `societes` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_facture_contact`
    FOREIGN KEY (`contact_id`) REFERENCES `contacts` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── Tokens (invitations & reset mdp) ─────────────────────────────
CREATE TABLE IF NOT EXISTS `tokens` (
  `token`       CHAR(64)   NOT NULL,
  `contact_id`  CHAR(36)   NOT NULL,
  `type`        ENUM('invitation','reset') NOT NULL DEFAULT 'invitation',
  `expires_at`  DATETIME   NOT NULL,
  `used_at`     DATETIME   DEFAULT NULL,
  `created_at`  DATETIME   NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`token`),
  KEY `idx_contact` (`contact_id`),
  CONSTRAINT `fk_token_contact`
    FOREIGN KEY (`contact_id`) REFERENCES `contacts` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── Sessions ──────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `sessions` (
  `id`         CHAR(64)     NOT NULL,
  `entity_id`  VARCHAR(40)  NOT NULL,  -- 'crm:{uuid}' ou contact UUID
  `ip`         VARCHAR(45)  DEFAULT NULL,
  `user_agent` VARCHAR(255) DEFAULT NULL,
  `created_at` DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `last_seen`  DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `expires_at` DATETIME     NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_entity` (`entity_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SET FOREIGN_KEY_CHECKS = 1;

-- ══════════════════════════════════════════════════════════════════
--  Module Comptabilité — Écritures (factures émises + charges)
-- ══════════════════════════════════════════════════════════════════
CREATE TABLE IF NOT EXISTS `ecritures` (
  `id`                CHAR(36)      NOT NULL,
  -- Type et classification
  `type`              ENUM('facture_emise','charge','note_frais','pret') NOT NULL,
  `categorie`         VARCHAR(100)  DEFAULT NULL,
                      -- facture_emise : mission, abonnement, conseil…
                      -- charge : loyer, telecom, logiciel, deplacement, formation, autre
  -- Identification
  `numero`            VARCHAR(50)   DEFAULT NULL,
  `libelle`           VARCHAR(255)  NOT NULL,
  `tiers`             VARCHAR(255)  DEFAULT NULL,  -- client ou fournisseur
  `societe_id`        CHAR(36)      DEFAULT NULL,  -- lien client CRM (optionnel)
  -- Montants
  `montant_ht`        DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  `tva_taux`          DECIMAL(5,2)  NOT NULL DEFAULT 20.00,
  -- Dates
  `date_piece`        DATE          NOT NULL,
  `date_echeance`     DATE          DEFAULT NULL,
  `periode`           CHAR(7)       NOT NULL,      -- YYYY-MM
  -- Statuts
  `statut_paiement`   ENUM('en_attente','paye','en_retard') NOT NULL DEFAULT 'en_attente',
  `statut_export`     ENUM('non_exporte','exporte') NOT NULL DEFAULT 'non_exporte',
  `date_export`       DATETIME      DEFAULT NULL,
  `export_ref`        VARCHAR(50)   DEFAULT NULL,  -- ref de l'export (ex: EXPORT-2025-01)
  -- Fichier joint (stocké sur disque)
  `file_path`         VARCHAR(500)  DEFAULT NULL,
  `file_name`         VARCHAR(255)  DEFAULT NULL,
  `file_size`         INT           DEFAULT NULL,
  -- Détection Factur-X / ZUGFeRD
  `facturx_status`    ENUM('non_analyse','non_detecte','facturx','zugferd') NOT NULL DEFAULT 'non_analyse',
  `facturx_version`   VARCHAR(20)   DEFAULT NULL,  -- ex: EN16931, MINIMUM…
  -- Auteur CRM
  `crm_user_id`       CHAR(36)      DEFAULT NULL,
  -- Méta
  `notes`             TEXT          DEFAULT NULL,
  `date_creation`     DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `date_maj`          DATETIME      DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_type`    (`type`),
  KEY `idx_periode` (`periode`),
  KEY `idx_export`  (`statut_export`),
  KEY `idx_paiement`(`statut_paiement`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── Uploads comptabilité ──────────────────────────────────────────
-- Répertoire : uploads/compta/{YYYY-MM}/{id}.pdf

-- Migration depuis v5 (si ecritures n'existe pas encore) :
-- Aucune modification des tables existantes requise.

-- ══════════════════════════════════════════════════════════════════
--  Module Tickets — Support client
-- ══════════════════════════════════════════════════════════════════

CREATE TABLE IF NOT EXISTS `tickets` (
  `id`              CHAR(36)     NOT NULL,
  `numero`          VARCHAR(20)  NOT NULL,   -- TK-2025-001
  `titre`           VARCHAR(255) NOT NULL,
  `description`     TEXT         DEFAULT NULL,
  `societe_id`      CHAR(36)     DEFAULT NULL,
  `contact_id`      CHAR(36)     DEFAULT NULL,  -- demandeur
  `crm_user_id`     CHAR(36)     DEFAULT NULL,  -- agent assigné
  `statut`          ENUM('nouveau','en_cours','attente_client','resolu','ferme') NOT NULL DEFAULT 'nouveau',
  `categorie`       ENUM('incident','demande','question','autre') NOT NULL DEFAULT 'question',
  `source`          ENUM('espace_client','email','crm','telephone','formulaire_contact') NOT NULL DEFAULT 'crm',
  `email_source`    VARCHAR(255) DEFAULT NULL,  -- email de l'expéditeur si source=email
  `date_resolution` DATETIME     DEFAULT NULL,
  `date_creation`   DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `date_maj`        DATETIME     DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_numero` (`numero`),
  KEY `idx_statut`   (`statut`),
  KEY `idx_societe`  (`societe_id`),
  KEY `idx_contact`  (`contact_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `ticket_messages` (
  `id`             CHAR(36)    NOT NULL,
  `ticket_id`      CHAR(36)    NOT NULL,
  `auteur_type`    ENUM('crm_user','contact','system') NOT NULL,
  `auteur_id`      CHAR(36)    DEFAULT NULL,
  `auteur_nom`     VARCHAR(150) DEFAULT NULL,  -- dénormalisé pour affichage
  `contenu`        TEXT        NOT NULL,
  `visible_client` TINYINT(1)  NOT NULL DEFAULT 1,  -- 0 = note interne CRM only
  `file_path`      VARCHAR(500) DEFAULT NULL,
  `file_name`      VARCHAR(255) DEFAULT NULL,
  `file_size`      INT          DEFAULT NULL,
  `file_mime`      VARCHAR(100) DEFAULT NULL,
  `date_creation`  DATETIME    NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_ticket` (`ticket_id`),
  CONSTRAINT `fk_msg_ticket` FOREIGN KEY (`ticket_id`) REFERENCES `tickets`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Séquence numéro de ticket (auto-increment annuel)
CREATE TABLE IF NOT EXISTS `ticket_seq` (
  `annee`  SMALLINT NOT NULL,
  `seq`    INT      NOT NULL DEFAULT 0,
  PRIMARY KEY (`annee`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ── Migration (si tickets n'existe pas encore) ─────────────────────
-- Aucune modification des tables existantes requise.
-- Exécuter les 3 CREATE TABLE ci-dessus dans phpMyAdmin.

-- ── Migration ticket_messages (ajouter colonnes fichiers) ─────────
-- ALTER TABLE `ticket_messages`
--   ADD COLUMN `file_path` VARCHAR(500) DEFAULT NULL AFTER `visible_client`,
--   ADD COLUMN `file_name` VARCHAR(255) DEFAULT NULL AFTER `file_path`,
--   ADD COLUMN `file_size` INT DEFAULT NULL AFTER `file_name`,
--   ADD COLUMN `file_mime` VARCHAR(100) DEFAULT NULL AFTER `file_size`;

-- ── Migration societes (ajouter colonnes SIREN + TVA intra) ──────
-- ALTER TABLE `societes`
--   ADD COLUMN `siren`     VARCHAR(9)  DEFAULT NULL AFTER `id`,
--   ADD COLUMN `tva_intra` VARCHAR(20) DEFAULT NULL AFTER `siret`;

-- ══════════════════════════════════════════════════════════════════
--  Paramètres de notification tickets
-- ══════════════════════════════════════════════════════════════════
CREATE TABLE IF NOT EXISTS `notification_settings` (
  `id`              TINYINT      NOT NULL AUTO_INCREMENT,
  `statut`          VARCHAR(30)  NOT NULL,   -- statut ticket ou 'message'
  `label`           VARCHAR(100) NOT NULL,
  `notify_client`   TINYINT(1)   NOT NULL DEFAULT 1,
  `updated_at`      DATETIME     DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_statut` (`statut`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Valeurs par défaut
INSERT IGNORE INTO `notification_settings` (`statut`,`label`,`notify_client`) VALUES
  ('nouveau',         'Ticket créé (Nouveau)',              1),
  ('en_cours',        'Statut → En cours',                  0),
  ('attente_client',  'Statut → En attente client',         1),
  ('resolu',          'Statut → Résolu',                    1),
  ('ferme',           'Statut → Fermé',                     0),
  ('message',         'Nouveau message visible au client',  1);

-- ── Migration tickets — ajouter source 'formulaire_contact' ──────
-- ALTER TABLE `tickets` MODIFY `source` ENUM('espace_client','email','crm','telephone','formulaire_contact') NOT NULL DEFAULT 'crm';

-- ══════════════════════════════════════════════════════════════════
--  Droits d'accès espace client (par société + surcharge par contact)
-- ══════════════════════════════════════════════════════════════════
-- ALTER TABLE `societes`
--   ADD COLUMN `acces_factures` TINYINT(1) NOT NULL DEFAULT 1 AFTER `crm_user_id`,
--   ADD COLUMN `acces_tickets`  TINYINT(1) NOT NULL DEFAULT 1 AFTER `acces_factures`;

-- ALTER TABLE `contacts`
--   ADD COLUMN `acces_factures` TINYINT(1) DEFAULT NULL AFTER `est_principal`,
--   ADD COLUMN `acces_tickets`  TINYINT(1) DEFAULT NULL AFTER `acces_factures`;
-- (NULL = hérite de la société, 0 = refusé, 1 = autorisé)

-- ══════════════════════════════════════════════════════════════════
--  Paramètres applicatifs (gérés depuis le Hub)
-- ══════════════════════════════════════════════════════════════════
CREATE TABLE IF NOT EXISTS `app_settings` (
  `cle`         VARCHAR(50)   NOT NULL,
  `valeur`      VARCHAR(500)  DEFAULT NULL,
  `label`       VARCHAR(150)  NOT NULL,
  `description` VARCHAR(300)  DEFAULT NULL,
  `updated_at`  DATETIME      DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`cle`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Valeurs par défaut
INSERT IGNORE INTO `app_settings` (`cle`,`valeur`,`label`,`description`) VALUES
  ('dest_email',   'thierry.dalaigre@kapdsi.fr', 'Email de réception des formulaires', 'Adresse qui reçoit les demandes du formulaire de contact et les alertes'),
  ('dest_name',    'Thierry Dalaigre – KAP DSI',  'Nom du destinataire',               'Nom affiché dans les emails reçus'),
  ('sender_name',  'KAP DSI',                     'Nom expéditeur des emails',          'Nom affiché dans les emails envoyés aux clients (notifications tickets, invitations…)'),
  ('support_email','support@kapdsi.fr',            'Adresse email support',              'Adresse communiquée aux clients pour ouvrir un ticket par email (Brevo Inbound)');

-- ── Migration — ajout champ LinkedIn ────────────────────────────
-- ALTER TABLE `societes`  ADD COLUMN `linkedin` VARCHAR(255) DEFAULT NULL AFTER `site_web`;
-- ALTER TABLE `contacts`  ADD COLUMN `linkedin` VARCHAR(255) DEFAULT NULL AFTER `telephone2`;

-- ── Migration — nouveaux types de société ───────────────────────
-- ALTER TABLE `societes` MODIFY `type_contact`
--   ENUM('prospect','client','partenaire','prescripteur','prestataire','fournisseur','reseau','inactif','perdu')
--   NOT NULL DEFAULT 'prospect';

-- ══════════════════════════════════════════════════════════════════
--  Module Campagnes email
-- ══════════════════════════════════════════════════════════════════
CREATE TABLE IF NOT EXISTS `campagnes` (
  `id`              CHAR(36)      NOT NULL,
  `nom`             VARCHAR(255)  NOT NULL,
  `sujet`           VARCHAR(255)  NOT NULL,
  `contenu_html`    MEDIUMTEXT    DEFAULT NULL,
  `contenu_texte`   TEXT          DEFAULT NULL,
  `expediteur_nom`  VARCHAR(100)  NOT NULL DEFAULT 'KAP DSI',
  `expediteur_email`VARCHAR(255)  NOT NULL DEFAULT 'noreply@kapdsi.fr',
  `statut`          ENUM('brouillon','planifiee','en_cours','envoyee','annulee') NOT NULL DEFAULT 'brouillon',
  `brevo_campaign_id` INT         DEFAULT NULL,
  `date_envoi_planifie` DATETIME  DEFAULT NULL,
  `date_envoi_reel`     DATETIME  DEFAULT NULL,
  `crm_user_id`     CHAR(36)      DEFAULT NULL,
  `date_creation`   DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `date_maj`        DATETIME      DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_statut` (`statut`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `campagne_destinataires` (
  `id`          CHAR(36)     NOT NULL,
  `campagne_id` CHAR(36)     NOT NULL,
  `contact_id`  CHAR(36)     DEFAULT NULL,
  `email`       VARCHAR(255) NOT NULL,
  `nom`         VARCHAR(200) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_campagne` (`campagne_id`),
  CONSTRAINT `fk_dest_campagne` FOREIGN KEY (`campagne_id`) REFERENCES `campagnes`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `campagne_stats` (
  `id`          CHAR(36)     NOT NULL,
  `campagne_id` CHAR(36)     NOT NULL,
  `email`       VARCHAR(255) NOT NULL,
  `event`       VARCHAR(50)  NOT NULL,  -- delivered, opened, clicked, softBounce, hardBounce, unsubscribe, spam
  `url`         VARCHAR(500) DEFAULT NULL,
  `date_event`  DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_camp_stat` (`campagne_id`,`event`),
  KEY `idx_email_stat` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ══════════════════════════════════════════════════════════════════
--  Module Notes de frais
-- ══════════════════════════════════════════════════════════════════

-- Véhicules paramétrables
CREATE TABLE IF NOT EXISTS `ndf_vehicules` (
  `id`          CHAR(36)     NOT NULL,
  `nom`         VARCHAR(100) NOT NULL,           -- ex: "Renault Clio", "Vélo"
  `type`        ENUM('voiture','moto','velo','autre') NOT NULL DEFAULT 'voiture',
  `cv_fiscaux`  TINYINT      DEFAULT NULL,        -- puissance fiscale (voiture/moto)
  `immatriculation` VARCHAR(20) DEFAULT NULL,
  `actif`       TINYINT(1)   NOT NULL DEFAULT 1,
  `crm_user_id` CHAR(36)     DEFAULT NULL,
  `date_creation` DATETIME   NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Barèmes kilométriques (mis à jour chaque année)
CREATE TABLE IF NOT EXISTS `ndf_baremes` (
  `id`          TINYINT      NOT NULL AUTO_INCREMENT,
  `annee`       SMALLINT     NOT NULL,
  `type`        ENUM('voiture','moto','velo') NOT NULL DEFAULT 'voiture',
  `cv_min`      TINYINT      DEFAULT NULL,        -- borne inférieure CV fiscaux
  `cv_max`      TINYINT      DEFAULT NULL,        -- borne supérieure CV fiscaux (NULL = illimité)
  `taux_km`     DECIMAL(6,4) NOT NULL,            -- €/km
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_bareme` (`annee`,`type`,`cv_min`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Barèmes 2026 voiture (source : impots.gouv.fr)
INSERT IGNORE INTO `ndf_baremes` (`annee`,`type`,`cv_min`,`cv_max`,`taux_km`) VALUES
  (2026,'voiture',1,3,  0.5290),
  (2026,'voiture',4,4,  0.6230),
  (2026,'voiture',5,5,  0.6860),
  (2026,'voiture',6,6,  0.7160),
  (2026,'voiture',7,NULL,0.7470),
  (2026,'moto',  1,1,  0.3950),
  (2026,'moto',  2,2,  0.4680),
  (2026,'moto',  3,NULL,0.5530),
  (2026,'velo',  NULL,NULL,0.2500);

-- Dépenses / notes de frais
CREATE TABLE IF NOT EXISTS `ndf_depenses` (
  `id`              CHAR(36)       NOT NULL,
  `crm_user_id`     CHAR(36)       DEFAULT NULL,
  `date_depense`    DATE           NOT NULL,
  `categorie`       ENUM('repas','deplacement','kilometrique','hebergement','fournitures','abonnement','representation','autre') NOT NULL DEFAULT 'autre',
  `libelle`         VARCHAR(255)   NOT NULL,
  `fournisseur`     VARCHAR(255)   DEFAULT NULL,
  `montant_ttc`     DECIMAL(10,2)  NOT NULL DEFAULT 0,
  `montant_ht`      DECIMAL(10,2)  DEFAULT NULL,
  `tva`             DECIMAL(10,2)  DEFAULT NULL,
  `tva_recuperable` TINYINT(1)     NOT NULL DEFAULT 0,
  -- Frais kilométriques
  `type_frais`      ENUM('depense','kilometrique') NOT NULL DEFAULT 'depense',
  `vehicule_id`     CHAR(36)       DEFAULT NULL,
  `km`              DECIMAL(8,2)   DEFAULT NULL,
  `depart`          VARCHAR(255)   DEFAULT NULL,
  `arrivee`         VARCHAR(255)   DEFAULT NULL,
  `taux_km`         DECIMAL(6,4)   DEFAULT NULL,
  -- Rattachement client
  `societe_id`      CHAR(36)       DEFAULT NULL,
  `refacturable`    TINYINT(1)     NOT NULL DEFAULT 0,
  `facture_id`      CHAR(36)       DEFAULT NULL,  -- rempli quand facturé
  -- Justificatif
  `justificatif_path` VARCHAR(500) DEFAULT NULL,
  `justificatif_nom`  VARCHAR(255) DEFAULT NULL,
  `justificatif_mime` VARCHAR(100) DEFAULT NULL,
  -- Comptabilité
  `ecriture_id`     CHAR(36)       DEFAULT NULL,
  -- Statut
  `statut`          ENUM('brouillon','validee','remboursee') NOT NULL DEFAULT 'brouillon',
  `notes`           TEXT           DEFAULT NULL,
  `date_creation`   DATETIME       NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `date_maj`        DATETIME       DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_date` (`date_depense`),
  KEY `idx_statut` (`statut`),
  KEY `idx_societe` (`societe_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ══════════════════════════════════════════════════════════════════
--  Listes de diffusion
-- ══════════════════════════════════════════════════════════════════
CREATE TABLE IF NOT EXISTS `diffusion_listes` (
  `id`            CHAR(36)      NOT NULL,
  `nom`           VARCHAR(255)  NOT NULL,
  `description`   TEXT          DEFAULT NULL,
  `crm_user_id`   CHAR(36)      DEFAULT NULL,
  `date_creation` DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `date_maj`      DATETIME      DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `diffusion_membres` (
  `id`        CHAR(36)      NOT NULL,
  `liste_id`  CHAR(36)      NOT NULL,
  `email`     VARCHAR(255)  NOT NULL,
  `nom`       VARCHAR(200)  DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_liste_email` (`liste_id`,`email`),
  CONSTRAINT `fk_dm_liste` FOREIGN KEY (`liste_id`) REFERENCES `diffusion_listes`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ══════════════════════════════════════════════════════════════════
--  GED — Partages
-- ══════════════════════════════════════════════════════════════════

CREATE TABLE IF NOT EXISTS `ged_partages` (
  `id`                   CHAR(36)     NOT NULL,
  `token`                CHAR(64)     NOT NULL UNIQUE,
  `type`                 ENUM('fichier','dossier') NOT NULL DEFAULT 'fichier',
  `drive_item_id`        VARCHAR(255) NOT NULL COMMENT 'ID Drive (fichier ou dossier)',
  `fichier_id`           CHAR(36)     DEFAULT NULL COMMENT 'NULL pour les dossiers',
  `nom_affichage`        VARCHAR(500) NOT NULL,
  `destinataire_email`   VARCHAR(255) DEFAULT NULL,
  `destinataire_user_id` CHAR(36)     DEFAULT NULL COMMENT 'Utilisateur Hub',
  `password_hash`        VARCHAR(255) DEFAULT NULL,
  `date_expiration`      DATETIME     DEFAULT NULL,
  `nb_acces_max`         INT          DEFAULT NULL,
  `nb_acces`             INT          NOT NULL DEFAULT 0,
  `actif`                TINYINT(1)   NOT NULL DEFAULT 1,
  `cree_par`             CHAR(36)     DEFAULT NULL,
  `date_creation`        DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `derniere_consultation` DATETIME    DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_token`  (`token`),
  KEY `idx_item`   (`drive_item_id`),
  KEY `idx_user`   (`destinataire_user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ══════════════════════════════════════════════════════════════════
--  Module Roadmap
-- ══════════════════════════════════════════════════════════════════

CREATE TABLE IF NOT EXISTS `roadmaps` (
  `id`            CHAR(36)     NOT NULL,
  `nom`           VARCHAR(255) NOT NULL,
  `description`   TEXT         DEFAULT NULL,
  `societe_id`    CHAR(36)     DEFAULT NULL,
  `date_creation` DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `cree_par`      CHAR(36)     DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `roadmap_items` (
  `id`            CHAR(36)     NOT NULL,
  `roadmap_id`    CHAR(36)     NOT NULL,
  `titre`         VARCHAR(500) NOT NULL,
  `description`   TEXT         DEFAULT NULL,
  `date_debut`    DATE         NOT NULL,
  `date_fin`      DATE         NOT NULL,
  `couleur`       VARCHAR(7)   NOT NULL DEFAULT '#1E3A5F',
  `statut`        ENUM('planifie','en_cours','livre','annule') NOT NULL DEFAULT 'planifie',
  `categorie`     VARCHAR(100) DEFAULT NULL,
  `projet_id`     CHAR(36)     DEFAULT NULL COMMENT 'Lien optionnel vers un projet',
  `ordre`         SMALLINT     NOT NULL DEFAULT 0,
  `date_creation` DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_roadmap` (`roadmap_id`),
  KEY `idx_projet`  (`projet_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ══════════════════════════════════════════════════════════════════
--  Module Roadmap
-- ══════════════════════════════════════════════════════════════════

CREATE TABLE IF NOT EXISTS `roadmap_items` (
  `id`            CHAR(36)     NOT NULL,
  `titre`         VARCHAR(255) NOT NULL,
  `description`   TEXT         DEFAULT NULL,
  `date_debut`    DATE         NOT NULL,
  `date_fin`      DATE         NOT NULL,
  `couleur`       VARCHAR(7)   NOT NULL DEFAULT '#1E3A5F',
  `statut`        ENUM('planifie','en_cours','livre','annule') NOT NULL DEFAULT 'planifie',
  `projet_id`     CHAR(36)     DEFAULT NULL,
  `societe_id`    CHAR(36)     DEFAULT NULL,
  `ordre`         INT          NOT NULL DEFAULT 0,
  `date_creation` DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_dates`   (`date_debut`,`date_fin`),
  KEY `idx_projet`  (`projet_id`),
  KEY `idx_societe` (`societe_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ══════════════════════════════════════════════════════════════════
--  GED — Boite de réception email
-- ══════════════════════════════════════════════════════════════════

CREATE TABLE IF NOT EXISTS `ged_inbox_log` (
  `id`         CHAR(36)    NOT NULL,
  `level`      VARCHAR(10) NOT NULL DEFAULT 'info',
  `message`    TEXT        NOT NULL,
  `created_at` DATETIME    NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_created` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
