-- Flux d'activités système pour la messagerie CupiDose
-- Objectif: publier automatiquement des événements métier (insert/update/delete)
-- Compatibilité: MySQL 8+

CREATE TABLE IF NOT EXISTS `cupidose_activity_feed` (
    `ID` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `event_type` VARCHAR(20) NOT NULL,
    `table_name` VARCHAR(128) NOT NULL,
    `entity_id` BIGINT NULL,
    `title` VARCHAR(255) NOT NULL,
    `details` TEXT NULL,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`ID`),
    INDEX `idx_activity_created_at` (`created_at`),
    INDEX `idx_activity_table_name` (`table_name`),
    INDEX `idx_activity_event_type` (`event_type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Assurer l'existence de la table de réservation si pas encore créée
CREATE TABLE IF NOT EXISTS `cupidose_reservation_commande` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `client_nom` VARCHAR(180) NOT NULL,
    `client_contact` VARCHAR(180) NULL,
    `client_canal` VARCHAR(80) NULL,
    `client_note` TEXT NULL,
    `produit_reference` VARCHAR(200) NOT NULL,
    `quantite` DECIMAL(10,2) NOT NULL,
    `unite` VARCHAR(30) NOT NULL,
    `prix_prevu` DECIMAL(10,2) NULL,
    `acompte` DECIMAL(10,2) NULL,
    `date_prevue` DATETIME NOT NULL,
    `mode_reception` ENUM('Livraison','Retrait sur place') NOT NULL DEFAULT 'Retrait sur place',
    `lieu` VARCHAR(180) NOT NULL,
    `adresse_livraison` TEXT NULL,
    `statut` VARCHAR(60) NOT NULL DEFAULT 'Brouillon',
    `reserve_par_user_id` INT(11) NULL,
    `reserve_par_nom` VARCHAR(180) NULL,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME NULL ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    INDEX `idx_statut` (`statut`),
    INDEX `idx_date_prevue` (`date_prevue`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DELIMITER $$

-- Investissements / remboursements
DROP TRIGGER IF EXISTS `trg_activity_investissement_ai`$$
CREATE TRIGGER `trg_activity_investissement_ai`
AFTER INSERT ON `cupidose_investissement`
FOR EACH ROW
BEGIN
    INSERT INTO `cupidose_activity_feed` (`event_type`, `table_name`, `entity_id`, `title`, `details`)
    VALUES (
        'INSERT',
        'cupidose_investissement',
        NEW.`ID`,
        'Investissement ajouté',
        CONCAT('Montant: ', FORMAT(NEW.`Montant_de_investissement`, 2), ' | Date: ', DATE_FORMAT(NEW.`Date_de_investissement`, '%d/%m/%Y'))
    );
END$$

DROP TRIGGER IF EXISTS `trg_activity_investissement_au`$$
CREATE TRIGGER `trg_activity_investissement_au`
AFTER UPDATE ON `cupidose_investissement`
FOR EACH ROW
BEGIN
    DECLARE action_label VARCHAR(255) DEFAULT 'Investissement modifié';

    IF NEW.`Remboursé_de_investissement` <> OLD.`Remboursé_de_investissement` THEN
        SET action_label = 'Remboursement investi mis à jour';
    END IF;

    INSERT INTO `cupidose_activity_feed` (`event_type`, `table_name`, `entity_id`, `title`, `details`)
    VALUES (
        'UPDATE',
        'cupidose_investissement',
        NEW.`ID`,
        action_label,
        CONCAT('Montant: ', FORMAT(NEW.`Montant_de_investissement`, 2), ' | Remboursé: ', FORMAT(NEW.`Remboursé_de_investissement`, 2))
    );
END$$

-- Transactions / transferts
DROP TRIGGER IF EXISTS `trg_activity_compte_transfert_ai`$$
CREATE TRIGGER `trg_activity_compte_transfert_ai`
AFTER INSERT ON `cupidose_compte_historique_transfert`
FOR EACH ROW
BEGIN
    INSERT INTO `cupidose_activity_feed` (`event_type`, `table_name`, `entity_id`, `title`, `details`)
    VALUES (
        'INSERT',
        'cupidose_compte_historique_transfert',
        NEW.`ID`,
        'Transaction / transfert ajouté',
        CONCAT(
            'De ', NEW.`Compte_d'origine__de_compte_historique_transfert`,
            ' vers ', NEW.`Compte_de_destination_de_compte_historique_transfert`,
            ' | Montant: ', FORMAT(NEW.`Montant_de_compte_historique_transfert`, 2)
        )
    );
END$$

-- Clients
DROP TRIGGER IF EXISTS `trg_activity_client_ai`$$
CREATE TRIGGER `trg_activity_client_ai`
AFTER INSERT ON `cupidose_client`
FOR EACH ROW
BEGIN
    INSERT INTO `cupidose_activity_feed` (`event_type`, `table_name`, `entity_id`, `title`, `details`)
    VALUES (
        'INSERT',
        'cupidose_client',
        NEW.`ID`,
        'Client ajouté',
        CONCAT('Client: ', COALESCE(NULLIF(NEW.`Surnom_de_client`, ''), CONCAT(COALESCE(NEW.`Prénom_de_client`, ''), ' ', COALESCE(NEW.`Nom_de_client`, ''))))
    );
END$$

DROP TRIGGER IF EXISTS `trg_activity_client_au`$$
CREATE TRIGGER `trg_activity_client_au`
AFTER UPDATE ON `cupidose_client`
FOR EACH ROW
BEGIN
    INSERT INTO `cupidose_activity_feed` (`event_type`, `table_name`, `entity_id`, `title`, `details`)
    VALUES (
        'UPDATE',
        'cupidose_client',
        NEW.`ID`,
        'Client modifié',
        CONCAT('Client: ', COALESCE(NULLIF(NEW.`Surnom_de_client`, ''), CONCAT(COALESCE(NEW.`Prénom_de_client`, ''), ' ', COALESCE(NEW.`Nom_de_client`, ''))))
    );
END$$

DROP TRIGGER IF EXISTS `trg_activity_client_ad`$$
CREATE TRIGGER `trg_activity_client_ad`
AFTER DELETE ON `cupidose_client`
FOR EACH ROW
BEGIN
    INSERT INTO `cupidose_activity_feed` (`event_type`, `table_name`, `entity_id`, `title`, `details`)
    VALUES (
        'DELETE',
        'cupidose_client',
        OLD.`ID`,
        'Client supprimé',
        CONCAT('Client: ', COALESCE(NULLIF(OLD.`Surnom_de_client`, ''), CONCAT(COALESCE(OLD.`Prénom_de_client`, ''), ' ', COALESCE(OLD.`Nom_de_client`, ''))))
    );
END$$

-- Lieux
DROP TRIGGER IF EXISTS `trg_activity_lieu_ai`$$
CREATE TRIGGER `trg_activity_lieu_ai`
AFTER INSERT ON `CupiDose_lieu`
FOR EACH ROW
BEGIN
    INSERT INTO `cupidose_activity_feed` (`event_type`, `table_name`, `entity_id`, `title`, `details`)
    VALUES ('INSERT', 'CupiDose_lieu', NEW.`ID`, 'Lieu ajouté', CONCAT('Ville: ', NEW.`Nom_de_la_ville_de_lieu`));
END$$

DROP TRIGGER IF EXISTS `trg_activity_lieu_au`$$
CREATE TRIGGER `trg_activity_lieu_au`
AFTER UPDATE ON `CupiDose_lieu`
FOR EACH ROW
BEGIN
    INSERT INTO `cupidose_activity_feed` (`event_type`, `table_name`, `entity_id`, `title`, `details`)
    VALUES ('UPDATE', 'CupiDose_lieu', NEW.`ID`, 'Lieu modifié', CONCAT('Ville: ', NEW.`Nom_de_la_ville_de_lieu`));
END$$

DROP TRIGGER IF EXISTS `trg_activity_lieu_ad`$$
CREATE TRIGGER `trg_activity_lieu_ad`
AFTER DELETE ON `CupiDose_lieu`
FOR EACH ROW
BEGIN
    INSERT INTO `cupidose_activity_feed` (`event_type`, `table_name`, `entity_id`, `title`, `details`)
    VALUES ('DELETE', 'CupiDose_lieu', OLD.`ID`, 'Lieu supprimé', CONCAT('Ville: ', OLD.`Nom_de_la_ville_de_lieu`));
END$$

-- Réservations / commandes
DROP TRIGGER IF EXISTS `trg_activity_reservation_ai`$$
CREATE TRIGGER `trg_activity_reservation_ai`
AFTER INSERT ON `cupidose_reservation_commande`
FOR EACH ROW
BEGIN
    INSERT INTO `cupidose_activity_feed` (`event_type`, `table_name`, `entity_id`, `title`, `details`)
    VALUES (
        'INSERT',
        'cupidose_reservation_commande',
        NEW.`id`,
        'Commande réservée',
        CONCAT('Client: ', NEW.`client_nom`, ' | Produit: ', NEW.`produit_reference`, ' | Qté: ', NEW.`quantite`, ' ', NEW.`unite`)
    );
END$$

DROP TRIGGER IF EXISTS `trg_activity_reservation_au`$$
CREATE TRIGGER `trg_activity_reservation_au`
AFTER UPDATE ON `cupidose_reservation_commande`
FOR EACH ROW
BEGIN
    DECLARE action_label VARCHAR(255) DEFAULT 'Réservation modifiée';

    IF NEW.`statut` <> OLD.`statut` THEN
        SET action_label = CONCAT('Statut réservation: ', NEW.`statut`);
    END IF;

    INSERT INTO `cupidose_activity_feed` (`event_type`, `table_name`, `entity_id`, `title`, `details`)
    VALUES (
        'UPDATE',
        'cupidose_reservation_commande',
        NEW.`id`,
        action_label,
        CONCAT('Client: ', NEW.`client_nom`, ' | Produit: ', NEW.`produit_reference`)
    );
END$$

DROP TRIGGER IF EXISTS `trg_activity_reservation_ad`$$
CREATE TRIGGER `trg_activity_reservation_ad`
AFTER DELETE ON `cupidose_reservation_commande`
FOR EACH ROW
BEGIN
    INSERT INTO `cupidose_activity_feed` (`event_type`, `table_name`, `entity_id`, `title`, `details`)
    VALUES (
        'DELETE',
        'cupidose_reservation_commande',
        OLD.`id`,
        'Réservation supprimée',
        CONCAT('Client: ', OLD.`client_nom`, ' | Produit: ', OLD.`produit_reference`)
    );
END$$

-- Produits finis formulation
DROP TRIGGER IF EXISTS `trg_activity_pf_form_ai`$$
CREATE TRIGGER `trg_activity_pf_form_ai`
AFTER INSERT ON `CupiDose_produit_finis_formulation`
FOR EACH ROW
BEGIN
    INSERT INTO `cupidose_activity_feed` (`event_type`, `table_name`, `entity_id`, `title`, `details`)
    VALUES (
        'INSERT',
        'CupiDose_produit_finis_formulation',
        NEW.`ID`,
        'Produit formulation ajouté',
        CONCAT('Produit: ', NEW.`Nom_de_produit_finis_formulation`, ' | Réf: ', NEW.`Référence_de_produit_finis_formulation`)
    );
END$$

DROP TRIGGER IF EXISTS `trg_activity_pf_form_au`$$
CREATE TRIGGER `trg_activity_pf_form_au`
AFTER UPDATE ON `CupiDose_produit_finis_formulation`
FOR EACH ROW
BEGIN
    DECLARE action_label VARCHAR(255) DEFAULT 'Produit formulation modifié';

    IF NEW.`Statut_de_produit_finis_formulation` <> OLD.`Statut_de_produit_finis_formulation` THEN
        SET action_label = CONCAT('Statut produit formulation: ', NEW.`Statut_de_produit_finis_formulation`);
    END IF;

    IF NEW.`Date_de_vente_de_produit_finis_formulation` IS NOT NULL
       AND (OLD.`Date_de_vente_de_produit_finis_formulation` IS NULL OR NEW.`Date_de_vente_de_produit_finis_formulation` <> OLD.`Date_de_vente_de_produit_finis_formulation`) THEN
        SET action_label = 'Vente produit formulation enregistrée';
    END IF;

    INSERT INTO `cupidose_activity_feed` (`event_type`, `table_name`, `entity_id`, `title`, `details`)
    VALUES (
        'UPDATE',
        'CupiDose_produit_finis_formulation',
        NEW.`ID`,
        action_label,
        CONCAT('Produit: ', NEW.`Nom_de_produit_finis_formulation`, ' | Réf: ', NEW.`Référence_de_produit_finis_formulation`)
    );
END$$

DROP TRIGGER IF EXISTS `trg_activity_pf_form_ad`$$
CREATE TRIGGER `trg_activity_pf_form_ad`
AFTER DELETE ON `CupiDose_produit_finis_formulation`
FOR EACH ROW
BEGIN
    INSERT INTO `cupidose_activity_feed` (`event_type`, `table_name`, `entity_id`, `title`, `details`)
    VALUES (
        'DELETE',
        'CupiDose_produit_finis_formulation',
        OLD.`ID`,
        'Produit formulation supprimé',
        CONCAT('Produit: ', OLD.`Nom_de_produit_finis_formulation`, ' | Réf: ', OLD.`Référence_de_produit_finis_formulation`)
    );
END$$

-- Produits finis achat-revente
DROP TRIGGER IF EXISTS `trg_activity_pf_ar_ai`$$
CREATE TRIGGER `trg_activity_pf_ar_ai`
AFTER INSERT ON `CupiDose_produit_finis_achat-revente`
FOR EACH ROW
BEGIN
    INSERT INTO `cupidose_activity_feed` (`event_type`, `table_name`, `entity_id`, `title`, `details`)
    VALUES (
        'INSERT',
        'CupiDose_produit_finis_achat-revente',
        NEW.`ID`,
        'Produit achat-revente ajouté',
        CONCAT('Produit: ', NEW.`Nom_de_produit_finis_achat-revente`, ' | Réf: ', NEW.`Référence_de_produit_finis_achat-revente`)
    );
END$$

DROP TRIGGER IF EXISTS `trg_activity_pf_ar_au`$$
CREATE TRIGGER `trg_activity_pf_ar_au`
AFTER UPDATE ON `CupiDose_produit_finis_achat-revente`
FOR EACH ROW
BEGIN
    DECLARE action_label VARCHAR(255) DEFAULT 'Produit achat-revente modifié';

    IF NEW.`Statut_de_produit_finis_achat-revente` <> OLD.`Statut_de_produit_finis_achat-revente` THEN
        SET action_label = CONCAT('Statut produit achat-revente: ', NEW.`Statut_de_produit_finis_achat-revente`);
    END IF;

    IF NEW.`Date_de_vente_de_produit_finis_achat-revente` IS NOT NULL
       AND (OLD.`Date_de_vente_de_produit_finis_achat-revente` IS NULL OR NEW.`Date_de_vente_de_produit_finis_achat-revente` <> OLD.`Date_de_vente_de_produit_finis_achat-revente`) THEN
        SET action_label = 'Vente produit achat-revente enregistrée';
    END IF;

    INSERT INTO `cupidose_activity_feed` (`event_type`, `table_name`, `entity_id`, `title`, `details`)
    VALUES (
        'UPDATE',
        'CupiDose_produit_finis_achat-revente',
        NEW.`ID`,
        action_label,
        CONCAT('Produit: ', NEW.`Nom_de_produit_finis_achat-revente`, ' | Réf: ', NEW.`Référence_de_produit_finis_achat-revente`)
    );
END$$

DROP TRIGGER IF EXISTS `trg_activity_pf_ar_ad`$$
CREATE TRIGGER `trg_activity_pf_ar_ad`
AFTER DELETE ON `CupiDose_produit_finis_achat-revente`
FOR EACH ROW
BEGIN
    INSERT INTO `cupidose_activity_feed` (`event_type`, `table_name`, `entity_id`, `title`, `details`)
    VALUES (
        'DELETE',
        'CupiDose_produit_finis_achat-revente',
        OLD.`ID`,
        'Produit achat-revente supprimé',
        CONCAT('Produit: ', OLD.`Nom_de_produit_finis_achat-revente`, ' | Réf: ', OLD.`Référence_de_produit_finis_achat-revente`)
    );
END$$

DELIMITER ;
