-- ============================================================
-- Money+Xfer -- Migration v3.2 -- Onboarding Entreprise (KYB)
-- Version idempotente : chaque colonne/index est ajoute
-- seulement s'il n'existe pas deja. Rejouable sans risque,
-- meme si une execution precedente s'est arretee en cours de route.
-- Compatible MySQL 5.7 / 8.0 et MariaDB.
-- ============================================================

USE akuxfer;

-- ---- 1. Type de compte sur client_accounts --------------------
SET @col_exists := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'client_accounts' AND COLUMN_NAME = 'account_type');
SET @sql := IF(@col_exists = 0,
  'ALTER TABLE client_accounts ADD COLUMN account_type ENUM(''particulier'',''entreprise'') NOT NULL DEFAULT ''particulier'' AFTER last_name',
  'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
SET @col_exists := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'client_accounts' AND COLUMN_NAME = 'company_name');
SET @sql := IF(@col_exists = 0,
  'ALTER TABLE client_accounts ADD COLUMN company_name VARCHAR(160) DEFAULT NULL AFTER account_type',
  'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- ---- 2. Champs entreprise sur kyc_dossiers ---------------------
--    (les champs identite deja existants -- civility, first_name,
--     last_name, birth_date, nationality, id_type, id_number,
--     address_* -- sont reutilises pour decrire le REPRESENTANT
--     LEGAL quand dossier_type = 'entreprise')
SET @col_exists := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'kyc_dossiers' AND COLUMN_NAME = 'dossier_type');
SET @sql := IF(@col_exists = 0,
  'ALTER TABLE kyc_dossiers ADD COLUMN dossier_type ENUM(''particulier'',''entreprise'') NOT NULL DEFAULT ''particulier'' AFTER account_id',
  'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
SET @col_exists := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'kyc_dossiers' AND COLUMN_NAME = 'company_name');
SET @sql := IF(@col_exists = 0,
  'ALTER TABLE kyc_dossiers ADD COLUMN company_name VARCHAR(160) DEFAULT NULL',
  'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
SET @col_exists := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'kyc_dossiers' AND COLUMN_NAME = 'legal_form');
SET @sql := IF(@col_exists = 0,
  'ALTER TABLE kyc_dossiers ADD COLUMN legal_form VARCHAR(60) DEFAULT NULL',
  'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
SET @col_exists := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'kyc_dossiers' AND COLUMN_NAME = 'rccm_number');
SET @sql := IF(@col_exists = 0,
  'ALTER TABLE kyc_dossiers ADD COLUMN rccm_number VARCHAR(80) DEFAULT NULL',
  'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
SET @col_exists := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'kyc_dossiers' AND COLUMN_NAME = 'nif_number');
SET @sql := IF(@col_exists = 0,
  'ALTER TABLE kyc_dossiers ADD COLUMN nif_number VARCHAR(80) DEFAULT NULL',
  'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
SET @col_exists := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'kyc_dossiers' AND COLUMN_NAME = 'activity_sector');
SET @sql := IF(@col_exists = 0,
  'ALTER TABLE kyc_dossiers ADD COLUMN activity_sector VARCHAR(120) DEFAULT NULL',
  'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
SET @col_exists := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'kyc_dossiers' AND COLUMN_NAME = 'incorporation_date');
SET @sql := IF(@col_exists = 0,
  'ALTER TABLE kyc_dossiers ADD COLUMN incorporation_date DATE DEFAULT NULL',
  'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
SET @col_exists := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'kyc_dossiers' AND COLUMN_NAME = 'share_capital');
SET @sql := IF(@col_exists = 0,
  'ALTER TABLE kyc_dossiers ADD COLUMN share_capital VARCHAR(60) DEFAULT NULL',
  'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
SET @col_exists := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'kyc_dossiers' AND COLUMN_NAME = 'company_phone');
SET @sql := IF(@col_exists = 0,
  'ALTER TABLE kyc_dossiers ADD COLUMN company_phone VARCHAR(40) DEFAULT NULL',
  'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
SET @col_exists := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'kyc_dossiers' AND COLUMN_NAME = 'company_email');
SET @sql := IF(@col_exists = 0,
  'ALTER TABLE kyc_dossiers ADD COLUMN company_email VARCHAR(160) DEFAULT NULL',
  'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
SET @col_exists := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'kyc_dossiers' AND COLUMN_NAME = 'company_website');
SET @sql := IF(@col_exists = 0,
  'ALTER TABLE kyc_dossiers ADD COLUMN company_website VARCHAR(160) DEFAULT NULL',
  'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
SET @col_exists := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'kyc_dossiers' AND COLUMN_NAME = 'hq_address_line1');
SET @sql := IF(@col_exists = 0,
  'ALTER TABLE kyc_dossiers ADD COLUMN hq_address_line1 VARCHAR(200) DEFAULT NULL',
  'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
SET @col_exists := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'kyc_dossiers' AND COLUMN_NAME = 'hq_address_city');
SET @sql := IF(@col_exists = 0,
  'ALTER TABLE kyc_dossiers ADD COLUMN hq_address_city VARCHAR(80) DEFAULT NULL',
  'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
SET @col_exists := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'kyc_dossiers' AND COLUMN_NAME = 'hq_address_country');
SET @sql := IF(@col_exists = 0,
  'ALTER TABLE kyc_dossiers ADD COLUMN hq_address_country VARCHAR(80) DEFAULT NULL',
  'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
SET @col_exists := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'kyc_dossiers' AND COLUMN_NAME = 'legal_rep_position');
SET @sql := IF(@col_exists = 0,
  'ALTER TABLE kyc_dossiers ADD COLUMN legal_rep_position VARCHAR(100) DEFAULT NULL',
  'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
SET @col_exists := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'kyc_dossiers' AND COLUMN_NAME = 'business_activity_description');
SET @sql := IF(@col_exists = 0,
  'ALTER TABLE kyc_dossiers ADD COLUMN business_activity_description TEXT DEFAULT NULL',
  'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
SET @idx_exists := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.STATISTICS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'kyc_dossiers' AND INDEX_NAME = 'idx_dossier_type');
SET @sql := IF(@idx_exists = 0,
  'ALTER TABLE kyc_dossiers ADD INDEX idx_dossier_type(dossier_type)',
  'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- ---- 3. Documents de conformite entreprise (multi-documents) --
CREATE TABLE IF NOT EXISTS kyc_documents (
    id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    dossier_id  INT UNSIGNED NOT NULL,
    doc_type    VARCHAR(60)  NOT NULL,   -- rccm, nif (NIU), statuts, id_rep_recto, id_rep_verso, domicile_siege, activite, rib, organigramme, autre
    doc_label   VARCHAR(160) DEFAULT NULL,
    file_path   VARCHAR(255) NOT NULL,
    uploaded_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (dossier_id) REFERENCES kyc_dossiers(id) ON DELETE CASCADE,
    INDEX idx_dossier(dossier_id),
    INDEX idx_doctype(doc_type)
) ENGINE=InnoDB;

-- ---- 4. Beneficiaires effectifs (UBO) --------------------------
CREATE TABLE IF NOT EXISTS kyc_beneficial_owners (
    id                INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    dossier_id        INT UNSIGNED NOT NULL,
    full_name         VARCHAR(160)  NOT NULL,
    birth_date        DATE          DEFAULT NULL,
    nationality       VARCHAR(60)   DEFAULT NULL,
    ownership_percent DECIMAL(5,2)  DEFAULT NULL,
    id_type           VARCHAR(30)   DEFAULT NULL,
    id_number         VARCHAR(80)   DEFAULT NULL,
    id_document_path  VARCHAR(255)  DEFAULT NULL,
    is_pep            TINYINT(1)    NOT NULL DEFAULT 0,
    created_at        DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (dossier_id) REFERENCES kyc_dossiers(id) ON DELETE CASCADE,
    INDEX idx_dossier(dossier_id)
) ENGINE=InnoDB;

-- ============================================================
-- Fin migration v3.2
-- ============================================================