-- ============================================================
-- Money+Xfer — Migration v1.9 — KYC Renforcé + Facturation Agence
-- Compatible MySQL 5.7+ / MariaDB 10.2+
-- Chaque ADD COLUMN est conditionnel via INFORMATION_SCHEMA
-- ============================================================

USE akuxfer;

-- ============================================================
-- Helper : macro conditionnelle
-- On vérifie l'existence de chaque colonne avant de l'ajouter
-- ============================================================

-- ── TABLE clients ──────────────────────────────────────────

-- type : ajouter PPE
ALTER TABLE clients MODIFY COLUMN type ENUM('particulier','entreprise','ppe') NOT NULL DEFAULT 'particulier';

SET @db = DATABASE();

-- date_naissance
SET @q = IF(
  (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=@db AND TABLE_NAME='clients' AND COLUMN_NAME='date_naissance')=0,
  'ALTER TABLE clients ADD COLUMN date_naissance DATE DEFAULT NULL AFTER address', 'SELECT 1');
PREPARE s FROM @q; EXECUTE s; DEALLOCATE PREPARE s;

-- lieu_naissance
SET @q = IF(
  (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=@db AND TABLE_NAME='clients' AND COLUMN_NAME='lieu_naissance')=0,
  'ALTER TABLE clients ADD COLUMN lieu_naissance VARCHAR(120) DEFAULT NULL AFTER date_naissance', 'SELECT 1');
PREPARE s FROM @q; EXECUTE s; DEALLOCATE PREPARE s;

-- profession
SET @q = IF(
  (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=@db AND TABLE_NAME='clients' AND COLUMN_NAME='profession')=0,
  'ALTER TABLE clients ADD COLUMN profession VARCHAR(120) DEFAULT NULL AFTER lieu_naissance', 'SELECT 1');
PREPARE s FROM @q; EXECUTE s; DEALLOCATE PREPARE s;

-- id_expiry
SET @q = IF(
  (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=@db AND TABLE_NAME='clients' AND COLUMN_NAME='id_expiry')=0,
  'ALTER TABLE clients ADD COLUMN id_expiry DATE DEFAULT NULL AFTER id_number', 'SELECT 1');
PREPARE s FROM @q; EXECUTE s; DEALLOCATE PREPARE s;

-- id_doc3 (justificatif domicile)
SET @q = IF(
  (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=@db AND TABLE_NAME='clients' AND COLUMN_NAME='id_doc3')=0,
  'ALTER TABLE clients ADD COLUMN id_doc3 VARCHAR(255) DEFAULT NULL AFTER id_doc2', 'SELECT 1');
PREPARE s FROM @q; EXECUTE s; DEALLOCATE PREPARE s;

-- source_fonds
SET @q = IF(
  (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=@db AND TABLE_NAME='clients' AND COLUMN_NAME='source_fonds')=0,
  'ALTER TABLE clients ADD COLUMN source_fonds VARCHAR(255) DEFAULT NULL AFTER selfie', 'SELECT 1');
PREPARE s FROM @q; EXECUTE s; DEALLOCATE PREPARE s;

-- kyc_level (1=simple 2=renforcé 3=PPE)
SET @q = IF(
  (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=@db AND TABLE_NAME='clients' AND COLUMN_NAME='kyc_level')=0,
  'ALTER TABLE clients ADD COLUMN kyc_level TINYINT UNSIGNED NOT NULL DEFAULT 1 AFTER risk_level', 'SELECT 1');
PREPARE s FROM @q; EXECUTE s; DEALLOCATE PREPARE s;

-- kyc_expiry
SET @q = IF(
  (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=@db AND TABLE_NAME='clients' AND COLUMN_NAME='kyc_expiry')=0,
  'ALTER TABLE clients ADD COLUMN kyc_expiry DATE DEFAULT NULL AFTER kyc_level', 'SELECT 1');
PREPARE s FROM @q; EXECUTE s; DEALLOCATE PREPARE s;

-- is_pep
SET @q = IF(
  (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=@db AND TABLE_NAME='clients' AND COLUMN_NAME='is_pep')=0,
  'ALTER TABLE clients ADD COLUMN is_pep TINYINT(1) NOT NULL DEFAULT 0 AFTER kyc_expiry', 'SELECT 1');
PREPARE s FROM @q; EXECUTE s; DEALLOCATE PREPARE s;

-- pep_notes
SET @q = IF(
  (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=@db AND TABLE_NAME='clients' AND COLUMN_NAME='pep_notes')=0,
  'ALTER TABLE clients ADD COLUMN pep_notes TEXT DEFAULT NULL AFTER is_pep', 'SELECT 1');
PREPARE s FROM @q; EXECUTE s; DEALLOCATE PREPARE s;

-- ── TABLE kyc_entreprises ───────────────────────────────────

CREATE TABLE IF NOT EXISTS kyc_entreprises (
    id                  INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    client_id           INT UNSIGNED NOT NULL UNIQUE,
    forme_juridique     ENUM('sarl','sa','sas','snc','gie','ong','association','autre') DEFAULT 'sarl',
    rccm                VARCHAR(60)  DEFAULT NULL,
    nif                 VARCHAR(60)  DEFAULT NULL,
    date_creation       DATE         DEFAULT NULL,
    pays_enregistrement VARCHAR(80)  DEFAULT NULL,
    secteur_activite    VARCHAR(120) DEFAULT NULL,
    effectif            ENUM('1-10','11-50','51-200','200+') DEFAULT NULL,
    chiffre_affaires    ENUM('<5M','5-50M','50-500M','>500M') DEFAULT NULL,
    doc_statuts         VARCHAR(255) DEFAULT NULL,
    doc_rccm            VARCHAR(255) DEFAULT NULL,
    doc_procuration     VARCHAR(255) DEFAULT NULL,
    rep_nom             VARCHAR(120) DEFAULT NULL,
    rep_prenom          VARCHAR(80)  DEFAULT NULL,
    rep_fonction        VARCHAR(80)  DEFAULT NULL,
    rep_id_type         ENUM('cni','passeport','permis') DEFAULT NULL,
    rep_id_number       VARCHAR(60)  DEFAULT NULL,
    rep_id_doc          VARCHAR(255) DEFAULT NULL,
    rep_id_expiry       DATE         DEFAULT NULL,
    beneficiaires_effectifs JSON     DEFAULT NULL,
    created_at          DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at          DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ── TABLE kyc_ppe ───────────────────────────────────────────

CREATE TABLE IF NOT EXISTS kyc_ppe (
    id                     INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    client_id              INT UNSIGNED NOT NULL UNIQUE,
    fonction_politique     VARCHAR(200) DEFAULT NULL,
    organisme              VARCHAR(120) DEFAULT NULL,
    pays_fonction          VARCHAR(80)  DEFAULT NULL,
    date_debut_fonction    DATE         DEFAULT NULL,
    date_fin_fonction      DATE         DEFAULT NULL,
    statut_ppe             ENUM('actif','ancien') NOT NULL DEFAULT 'actif',
    lien_ppe               ENUM('direct','conjoint','enfant','parent','associe') DEFAULT 'direct',
    ppe_reference_nom      VARCHAR(200) DEFAULT NULL,
    revenus_declares       VARCHAR(80)  DEFAULT NULL,
    patrimoine_estime      VARCHAR(80)  DEFAULT NULL,
    doc_declaration        VARCHAR(255) DEFAULT NULL,
    doc_source_fonds       VARCHAR(255) DEFAULT NULL,
    approuve_par_direction TINYINT(1)   NOT NULL DEFAULT 0,
    date_approbation       DATETIME     DEFAULT NULL,
    approbateur_id         INT UNSIGNED DEFAULT NULL,
    notes_conformite       TEXT         DEFAULT NULL,
    created_at             DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at             DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (client_id)      REFERENCES clients(id) ON DELETE CASCADE,
    FOREIGN KEY (approbateur_id) REFERENCES users(id)   ON DELETE SET NULL
) ENGINE=InnoDB;

-- ── TABLE kyc_alertes ───────────────────────────────────────

CREATE TABLE IF NOT EXISTS kyc_alertes (
    id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    client_id   INT UNSIGNED NOT NULL,
    type_alerte ENUM('revision_kyc','doc_expire','activite_suspecte','gel_avoirs','seuil_depasse','ppe_detecte') NOT NULL,
    description TEXT         DEFAULT NULL,
    statut      ENUM('ouverte','en_cours','cloturee','escaladee') NOT NULL DEFAULT 'ouverte',
    created_by  INT UNSIGNED DEFAULT NULL,
    assigned_to INT UNSIGNED DEFAULT NULL,
    closed_at   DATETIME     DEFAULT NULL,
    created_at  DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (client_id)   REFERENCES clients(id) ON DELETE CASCADE,
    FOREIGN KEY (created_by)  REFERENCES users(id)   ON DELETE SET NULL,
    FOREIGN KEY (assigned_to) REFERENCES users(id)   ON DELETE SET NULL,
    INDEX idx_client(client_id),
    INDEX idx_statut(statut),
    INDEX idx_type(type_alerte)
) ENGINE=InnoDB;

-- ── FACTURATION PAR AGENCE ──────────────────────────────────

-- agence_id sur invoices
SET @q = IF(
  (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=@db AND TABLE_NAME='invoices' AND COLUMN_NAME='agence_id')=0,
  'ALTER TABLE invoices ADD COLUMN agence_id INT UNSIGNED DEFAULT NULL AFTER user_id', 'SELECT 1');
PREPARE s FROM @q; EXECUTE s; DEALLOCATE PREPARE s;

-- FK invoices → agences (séparé, conditionnelle aussi)
SET @q = IF(
  (SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE TABLE_SCHEMA=@db AND TABLE_NAME='invoices' AND CONSTRAINT_NAME='fk_invoices_agence')=0,
  'ALTER TABLE invoices ADD CONSTRAINT fk_invoices_agence FOREIGN KEY (agence_id) REFERENCES agences(id) ON DELETE SET NULL',
  'SELECT 1');
PREPARE s FROM @q; EXECUTE s; DEALLOCATE PREPARE s;

-- Séquences de numérotation par agence
CREATE TABLE IF NOT EXISTS invoice_sequences (
    agence_code VARCHAR(20)  NOT NULL,
    year        SMALLINT     NOT NULL,
    last_seq    INT UNSIGNED NOT NULL DEFAULT 0,
    PRIMARY KEY (agence_code, year)
) ENGINE=InnoDB;

-- ── UTILISATEURS — flag Direction Générale ──────────────────

-- is_dg
SET @q = IF(
  (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=@db AND TABLE_NAME='users' AND COLUMN_NAME='is_dg')=0,
  'ALTER TABLE users ADD COLUMN is_dg TINYINT(1) NOT NULL DEFAULT 0 AFTER agence_id',
  'SELECT 1');
PREPARE s FROM @q; EXECUTE s; DEALLOCATE PREPARE s;

-- Marquer les admins/managers existants comme DG
UPDATE users SET is_dg=1
WHERE role_id IN (SELECT id FROM roles WHERE name IN ('super_admin','admin','manager'))
  AND agence_id IS NULL;

-- ── PERMISSIONS KYC renforcées ──────────────────────────────

INSERT IGNORE INTO permissions (module, action, label) VALUES
('kyc', 'ppe_approve',  'Approuver un dossier PPE (Direction)'),
('kyc', 'alert_manage', 'Gérer les alertes KYC'),
('kyc', 'export',       'Exporter les dossiers KYC');

INSERT IGNORE INTO role_permissions (role_id, permission_id)
SELECT r.id, p.id FROM roles r, permissions p
WHERE r.name IN ('super_admin','admin')
  AND p.module='kyc' AND p.action IN ('ppe_approve','alert_manage','export');

INSERT IGNORE INTO role_permissions (role_id, permission_id)
SELECT r.id, p.id FROM roles r, permissions p
WHERE r.name='manager'
  AND p.module='kyc' AND p.action IN ('alert_manage','export');

-- ── VUE : résumé factures par agence ───────────────────────

CREATE OR REPLACE VIEW v_invoices_par_agence AS
SELECT
  ag.id                                                              agence_id,
  ag.code                                                            agence_code,
  ag.name                                                            agence_name,
  COUNT(i.id)                                                        nb_factures,
  SUM(CASE WHEN i.status='emise' THEN i.total_amount ELSE 0 END)    montant_emis,
  SUM(CASE WHEN i.status='payee' THEN i.total_amount ELSE 0 END)    montant_encaisse,
  YEAR(CURDATE())                                                    annee
FROM agences ag
LEFT JOIN invoices i ON i.agence_id=ag.id AND YEAR(i.issued_at)=YEAR(CURDATE())
GROUP BY ag.id, ag.code, ag.name;