
USE auth_db;

-- =====================================================

SET @old_sql_mode = @@sql_mode;
SET sql_mode = '';

-- =====================================================
-- 1. Nettoyer les données invalides avant modifications
-- =====================================================

-- Corriger les valeurs datetime invalides dans la table users
UPDATE `users` 
SET `last_co` = NULL 
WHERE `last_co` < '1000-01-01' OR `last_co` IS NULL OR `last_co` = '0000-00-00 00:00:00';

UPDATE `users` 
SET `reset_expiration` = NULL 
WHERE `reset_expiration` < '1000-01-01' OR `reset_expiration` IS NULL OR `reset_expiration` = '0000-00-00 00:00:00';

UPDATE `users` 
SET `created_at` = NOW() 
WHERE `created_at` < '1000-01-01' OR `created_at` = '0000-00-00 00:00:00';

-- =====================================================
-- 2. Optimiser la colonne UUID dans la table users
-- =====================================================

-- Modifier le type de uuid pour optimiser les performances
ALTER TABLE `users` 
MODIFY COLUMN `uuid` VARCHAR(36) NULL 
COMMENT 'UUID de l\'utilisateur Bridge';

-- Ajouter un index unique pour éviter les doublons (ignorer si existe déjà)
SET @sql = IF(
  (SELECT COUNT(*) FROM information_schema.statistics 
   WHERE table_schema = 'auth_db' 
   AND table_name = 'users' 
   AND index_name = 'idx_bridge_uuid') = 0,
  'ALTER TABLE `users` ADD UNIQUE INDEX `idx_bridge_uuid` (`uuid`)',
  'SELECT "Index idx_bridge_uuid existe déjà" AS Info'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- =====================================================
-- 3. Créer la table bridge_users pour gérer les tokens
-- =====================================================

CREATE TABLE IF NOT EXISTS `bridge_users` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `user_id` INT UNSIGNED NOT NULL COMMENT 'Référence vers auth_db.users.id',
  `service` VARCHAR(32) NOT NULL DEFAULT 'syndicos' COMMENT 'Contexte applicatif (syndicos, vendoros, rentos, ...)',
  `bridge_uuid` VARCHAR(36) NULL COMMENT 'UUID Bridge (si disponible)',
  `bridge_email` VARCHAR(255) NOT NULL COMMENT 'Email utilisé pour Bridge',
  `access_token` TEXT NULL COMMENT 'Token d\'accès Bridge (Bearer)',
  `token_expires_at` DATETIME NULL COMMENT 'Date d\'expiration du token',
  `last_sync` DATETIME NULL COMMENT 'Dernière synchronisation des comptes',
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE INDEX `idx_user_service` (`user_id`, `service`),
  INDEX `idx_bridge_uuid` (`bridge_uuid`),
  INDEX `idx_bridge_email` (`bridge_email`),
  CONSTRAINT `fk_bridge_users_user` 
    FOREIGN KEY (`user_id`) 
    REFERENCES `users` (`id`) 
    ON DELETE CASCADE 
    ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
COMMENT='Gestion des utilisateurs Bridge et leurs tokens';

-- Compat: si la table existait déjà sans colonne service, l'ajouter et ajuster les indexes
SET @sql = IF ((SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME='bridge_users' AND COLUMN_NAME='service')=0,
  'ALTER TABLE `bridge_users` ADD COLUMN `service` VARCHAR(32) NOT NULL DEFAULT ''syndicos'' COMMENT ''Contexte applicatif (syndicos, vendoros, rentos, ...)'' AFTER `user_id`', 'SELECT 1');
PREPARE bu1 FROM @sql; EXECUTE bu1; DEALLOCATE PREPARE bu1;

-- Remplacer l'unique index sur user_id par (user_id, service)
-- Ajouter l'unique (user_id, service) si manquant
SET @has_idx_user_service := (SELECT COUNT(*) FROM information_schema.statistics WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME='bridge_users' AND INDEX_NAME='idx_user_service');
SET @sql = IF (@has_idx_user_service = 0, 'ALTER TABLE `bridge_users` ADD UNIQUE INDEX `idx_user_service` (`user_id`, `service`)', 'SELECT 1');
PREPARE bu3 FROM @sql; EXECUTE bu3; DEALLOCATE PREPARE bu3;

-- Si un index UNIQUE sur user_id seul existe, le convertir en index non-unique en conservant la contrainte FK
SET @is_unique_user_id := (
  SELECT IFNULL(MIN(CASE WHEN NON_UNIQUE = 0 THEN 1 ELSE 0 END), 0)
  FROM information_schema.statistics
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME='bridge_users' AND INDEX_NAME='idx_user_id'
);

-- Récupérer le nom de la contrainte FK qui pointe sur users.id
SET @fk_name := (
  SELECT CONSTRAINT_NAME FROM information_schema.KEY_COLUMN_USAGE
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME='bridge_users' AND COLUMN_NAME='user_id' AND REFERENCED_TABLE_NAME='users'
  LIMIT 1
);

-- Si l'index idx_user_id est UNIQUE, on drop la FK, on remplace l'index par un non-unique, puis on recrée la FK
SET @sql = IF (@is_unique_user_id = 1 AND @fk_name IS NOT NULL, CONCAT('ALTER TABLE `bridge_users` DROP FOREIGN KEY `', @fk_name, '`'), 'SELECT 1');
PREPARE bu_fk1 FROM @sql; EXECUTE bu_fk1; DEALLOCATE PREPARE bu_fk1;

SET @has_idx_user_id := (SELECT COUNT(*) FROM information_schema.statistics WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME='bridge_users' AND INDEX_NAME='idx_user_id');
SET @sql = IF (@is_unique_user_id = 1 AND @has_idx_user_id > 0, 'ALTER TABLE `bridge_users` DROP INDEX `idx_user_id`', 'SELECT 1');
PREPARE bu2 FROM @sql; EXECUTE bu2; DEALLOCATE PREPARE bu2;

-- Recréer un index non-unique sur user_id si absent
SET @idx_user_id_exists := (SELECT COUNT(*) FROM information_schema.statistics WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME='bridge_users' AND INDEX_NAME='idx_user_id');
SET @sql = IF (@idx_user_id_exists = 0, 'ALTER TABLE `bridge_users` ADD INDEX `idx_user_id` (`user_id`)', 'SELECT 1');
PREPARE bu4 FROM @sql; EXECUTE bu4; DEALLOCATE PREPARE bu4;

-- Recréer la contrainte FK si elle avait été supprimée
SET @sql = IF (@is_unique_user_id = 1, 'ALTER TABLE `bridge_users` ADD CONSTRAINT `fk_bridge_users_user` FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE ON UPDATE CASCADE', 'SELECT 1');
PREPARE bu_fk2 FROM @sql; EXECUTE bu_fk2; DEALLOCATE PREPARE bu_fk2;

-- ============================================================
-- Service au niveau des comptes (préférence: scoper l'usage par compte)
-- ============================================================
-- Ajouter la colonne `service` sur bridge_accounts si manquante
SET @need_ba_service := (
  SELECT COUNT(*) = 0 FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME='bridge_accounts' AND COLUMN_NAME='service'
);
SET @sql = IF (@need_ba_service, 'ALTER TABLE `bridge_accounts` ADD COLUMN `service` VARCHAR(32) NULL DEFAULT NULL COMMENT ''Contexte applicatif (syndicos, vendoros, rentos, ...)'' AFTER `account_iban`', 'SELECT 1');
PREPARE ba1 FROM @sql; EXECUTE ba1; DEALLOCATE PREPARE ba1;

-- Index pour (bridge_user_id, service)
SET @has_idx_ba_user_service := (SELECT COUNT(*) FROM information_schema.statistics WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME='bridge_accounts' AND INDEX_NAME='idx_ba_user_service');
SET @sql = IF (@has_idx_ba_user_service = 0, 'ALTER TABLE `bridge_accounts` ADD INDEX `idx_ba_user_service` (`bridge_user_id`, `service`)', 'SELECT 1');
PREPARE ba2 FROM @sql; EXECUTE ba2; DEALLOCATE PREPARE ba2;

-- Backfill: si bridge_users.service existe, recopier vers bridge_accounts.service si NULL
SET @has_bu_service := (
  SELECT COUNT(*) FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME='bridge_users' AND COLUMN_NAME='service'
);
SET @sql = IF (@has_bu_service > 0, 'UPDATE `bridge_accounts` ba JOIN `bridge_users` bu ON ba.bridge_user_id = bu.id SET ba.`service` = COALESCE(ba.`service`, bu.`service`) WHERE ba.`service` IS NULL', 'SELECT 1');
PREPARE ba3 FROM @sql; EXECUTE ba3; DEALLOCATE PREPARE ba3;

-- =====================================================
-- 4. Créer la table bridge_accounts pour les comptes bancaires
-- =====================================================

CREATE TABLE IF NOT EXISTS `bridge_accounts` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `bridge_user_id` INT NOT NULL COMMENT 'Référence vers bridge_users.id',
  `bridge_account_id` BIGINT NOT NULL COMMENT 'ID du compte Bridge',
  `bridge_item_id` BIGINT NULL COMMENT 'ID de l\'item Bridge (institution)',
  `account_name` VARCHAR(255) NULL COMMENT 'Nom du compte',
  `account_type` VARCHAR(50) NULL COMMENT 'Type de compte (checking, savings, etc.)',
  `currency` VARCHAR(3) NULL DEFAULT 'EUR' COMMENT 'Devise du compte',
  `iban` VARCHAR(34) NULL COMMENT 'IBAN du compte',
  `balance` DECIMAL(15,2) NULL COMMENT 'Solde du compte',
  `bank_name` VARCHAR(255) NULL COMMENT 'Nom de la banque',
  `bank_logo` TEXT NULL COMMENT 'URL du logo de la banque',
  `is_active` TINYINT(1) NOT NULL DEFAULT 1 COMMENT 'Compte actif ou non',
  `status` ENUM('ok', 'error', 'syncing', 'disconnected') NOT NULL DEFAULT 'ok',
  `last_sync` DATETIME NULL COMMENT 'Dernière synchronisation',
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE INDEX `idx_bridge_account_id` (`bridge_account_id`),
  INDEX `idx_bridge_user_id` (`bridge_user_id`),
  INDEX `idx_bridge_item_id` (`bridge_item_id`),
  INDEX `idx_status` (`status`),
  CONSTRAINT `fk_bridge_accounts_user` 
    FOREIGN KEY (`bridge_user_id`) 
    REFERENCES `bridge_users` (`id`) 
    ON DELETE CASCADE 
    ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
COMMENT='Comptes bancaires synchronisés via Bridge';

-- Assurer la présence des colonnes attendues si la table existait déjà (compat MariaDB/MySQL sans IF NOT EXISTS)
SET @sql = IF (
  (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME='bridge_accounts' AND COLUMN_NAME='account_name') = 0,
  'ALTER TABLE `bridge_accounts` ADD COLUMN `account_name` VARCHAR(255) NULL COMMENT ''Nom du compte'' AFTER `bridge_item_id`',
  'SELECT 1'
); PREPARE s1 FROM @sql; EXECUTE s1; DEALLOCATE PREPARE s1;

SET @sql = IF (
  (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME='bridge_accounts' AND COLUMN_NAME='account_type') = 0,
  'ALTER TABLE `bridge_accounts` ADD COLUMN `account_type` VARCHAR(50) NULL COMMENT ''Type de compte'' AFTER `account_name`',
  'SELECT 1'
); PREPARE s2 FROM @sql; EXECUTE s2; DEALLOCATE PREPARE s2;

SET @sql = IF (
  (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME='bridge_accounts' AND COLUMN_NAME='currency') = 0,
  'ALTER TABLE `bridge_accounts` ADD COLUMN `currency` VARCHAR(3) NULL DEFAULT ''EUR'' COMMENT ''Devise du compte'' AFTER `account_type`',
  'SELECT 1'
); PREPARE s3 FROM @sql; EXECUTE s3; DEALLOCATE PREPARE s3;

SET @sql = IF (
  (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME='bridge_accounts' AND COLUMN_NAME='iban') = 0,
  'ALTER TABLE `bridge_accounts` ADD COLUMN `iban` VARCHAR(34) NULL COMMENT ''IBAN du compte'' AFTER `currency`',
  'SELECT 1'
); PREPARE s4 FROM @sql; EXECUTE s4; DEALLOCATE PREPARE s4;

SET @sql = IF (
  (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME='bridge_accounts' AND COLUMN_NAME='balance') = 0,
  'ALTER TABLE `bridge_accounts` ADD COLUMN `balance` DECIMAL(15,2) NULL COMMENT ''Solde du compte'' AFTER `iban`',
  'SELECT 1'
); PREPARE s5 FROM @sql; EXECUTE s5; DEALLOCATE PREPARE s5;

SET @sql = IF (
  (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME='bridge_accounts' AND COLUMN_NAME='bank_name') = 0,
  'ALTER TABLE `bridge_accounts` ADD COLUMN `bank_name` VARCHAR(255) NULL COMMENT ''Nom de la banque'' AFTER `balance`',
  'SELECT 1'
); PREPARE s6 FROM @sql; EXECUTE s6; DEALLOCATE PREPARE s6;

SET @sql = IF (
  (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME='bridge_accounts' AND COLUMN_NAME='bank_logo') = 0,
  'ALTER TABLE `bridge_accounts` ADD COLUMN `bank_logo` TEXT NULL COMMENT ''URL du logo de la banque'' AFTER `bank_name`',
  'SELECT 1'
); PREPARE s7 FROM @sql; EXECUTE s7; DEALLOCATE PREPARE s7;

SET @sql = IF (
  (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME='bridge_accounts' AND COLUMN_NAME='is_active') = 0,
  'ALTER TABLE `bridge_accounts` ADD COLUMN `is_active` TINYINT(1) NOT NULL DEFAULT 1 COMMENT ''Compte actif ou non'' AFTER `bank_logo`',
  'SELECT 1'
); PREPARE s8 FROM @sql; EXECUTE s8; DEALLOCATE PREPARE s8;

SET @sql = IF (
  (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME='bridge_accounts' AND COLUMN_NAME='status') = 0,
  'ALTER TABLE `bridge_accounts` ADD COLUMN `status` ENUM(''ok'',''error'',''syncing'',''disconnected'') NOT NULL DEFAULT ''ok'' AFTER `is_active`',
  'SELECT 1'
); PREPARE s9 FROM @sql; EXECUTE s9; DEALLOCATE PREPARE s9;

SET @sql = IF (
  (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME='bridge_accounts' AND COLUMN_NAME='last_sync') = 0,
  'ALTER TABLE `bridge_accounts` ADD COLUMN `last_sync` DATETIME NULL COMMENT ''Dernière synchronisation'' AFTER `status`',
  'SELECT 1'
); PREPARE s10 FROM @sql; EXECUTE s10; DEALLOCATE PREPARE s10;

SET @sql = IF (
  (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME='bridge_accounts' AND COLUMN_NAME='created_at') = 0,
  'ALTER TABLE `bridge_accounts` ADD COLUMN `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP AFTER `last_sync`',
  'SELECT 1'
); PREPARE s11 FROM @sql; EXECUTE s11; DEALLOCATE PREPARE s11;

SET @sql = IF (
  (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME='bridge_accounts' AND COLUMN_NAME='updated_at') = 0,
  'ALTER TABLE `bridge_accounts` ADD COLUMN `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP AFTER `created_at`',
  'SELECT 1'
); PREPARE s12 FROM @sql; EXECUTE s12; DEALLOCATE PREPARE s12;

-- =====================================================
-- 5. Créer la table bridge_transactions pour les opérations
-- =====================================================

CREATE TABLE IF NOT EXISTS `bridge_transactions` (
  `id` BIGINT NOT NULL AUTO_INCREMENT,
  `bridge_account_id` INT NOT NULL COMMENT 'Référence vers bridge_accounts.id',
  `bridge_transaction_id` BIGINT NOT NULL COMMENT 'ID de la transaction Bridge',
  `transaction_date` DATE NOT NULL COMMENT 'Date de la transaction',
  `value_date` DATE NULL COMMENT 'Date de valeur',
  `description` TEXT NULL COMMENT 'Description de l\'opération',
  `amount` DECIMAL(15,2) NOT NULL COMMENT 'Montant de la transaction',
  `currency` VARCHAR(3) NOT NULL DEFAULT 'EUR' COMMENT 'Devise',
  `category_id` INT NULL COMMENT 'Catégorie Bridge',
  `category_name` VARCHAR(255) NULL COMMENT 'Nom de la catégorie',
  `is_deleted` TINYINT(1) NOT NULL DEFAULT 0 COMMENT 'Transaction supprimée',
  `is_future` TINYINT(1) NOT NULL DEFAULT 0 COMMENT 'Transaction à venir',
  `raw_data` JSON NULL COMMENT 'Données brutes de Bridge',
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE INDEX `idx_bridge_transaction_id` (`bridge_transaction_id`),
  INDEX `idx_bridge_account_id` (`bridge_account_id`),
  INDEX `idx_transaction_date` (`transaction_date`),
  INDEX `idx_amount` (`amount`),
  INDEX `idx_category` (`category_id`),
  CONSTRAINT `fk_bridge_transactions_account` 
    FOREIGN KEY (`bridge_account_id`) 
    REFERENCES `bridge_accounts` (`id`) 
    ON DELETE CASCADE 
    ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
COMMENT='Transactions bancaires synchronisées via Bridge';

-- Assurer la présence des colonnes attendues si la table existait déjà (compat)
SET @sql = IF ((SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME='bridge_transactions' AND COLUMN_NAME='transaction_date')=0,
  'ALTER TABLE `bridge_transactions` ADD COLUMN `transaction_date` DATE NOT NULL COMMENT ''Date de la transaction'' AFTER `bridge_transaction_id`', 'SELECT 1');
PREPARE t1 FROM @sql; EXECUTE t1; DEALLOCATE PREPARE t1;

SET @sql = IF ((SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME='bridge_transactions' AND COLUMN_NAME='value_date')=0,
  'ALTER TABLE `bridge_transactions` ADD COLUMN `value_date` DATE NULL COMMENT ''Date de valeur'' AFTER `transaction_date`', 'SELECT 1');
PREPARE t2 FROM @sql; EXECUTE t2; DEALLOCATE PREPARE t2;

SET @sql = IF ((SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME='bridge_transactions' AND COLUMN_NAME='description')=0,
  'ALTER TABLE `bridge_transactions` ADD COLUMN `description` TEXT NULL COMMENT ''Description de l''''opération'' AFTER `value_date`', 'SELECT 1');
PREPARE t3 FROM @sql; EXECUTE t3; DEALLOCATE PREPARE t3;

SET @sql = IF ((SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME='bridge_transactions' AND COLUMN_NAME='amount')=0,
  'ALTER TABLE `bridge_transactions` ADD COLUMN `amount` DECIMAL(15,2) NOT NULL COMMENT ''Montant de la transaction'' AFTER `description`', 'SELECT 1');
PREPARE t4 FROM @sql; EXECUTE t4; DEALLOCATE PREPARE t4;

SET @sql = IF ((SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME='bridge_transactions' AND COLUMN_NAME='currency')=0,
  'ALTER TABLE `bridge_transactions` ADD COLUMN `currency` VARCHAR(3) NOT NULL DEFAULT ''EUR'' COMMENT ''Devise'' AFTER `amount`', 'SELECT 1');
PREPARE t5 FROM @sql; EXECUTE t5; DEALLOCATE PREPARE t5;

SET @sql = IF ((SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME='bridge_transactions' AND COLUMN_NAME='category_id')=0,
  'ALTER TABLE `bridge_transactions` ADD COLUMN `category_id` INT NULL COMMENT ''Catégorie Bridge'' AFTER `currency`', 'SELECT 1');
PREPARE t6 FROM @sql; EXECUTE t6; DEALLOCATE PREPARE t6;

SET @sql = IF ((SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME='bridge_transactions' AND COLUMN_NAME='category_name')=0,
  'ALTER TABLE `bridge_transactions` ADD COLUMN `category_name` VARCHAR(255) NULL COMMENT ''Nom de la catégorie'' AFTER `category_id`', 'SELECT 1');
PREPARE t7 FROM @sql; EXECUTE t7; DEALLOCATE PREPARE t7;

SET @sql = IF ((SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME='bridge_transactions' AND COLUMN_NAME='is_deleted')=0,
  'ALTER TABLE `bridge_transactions` ADD COLUMN `is_deleted` TINYINT(1) NOT NULL DEFAULT 0 COMMENT ''Transaction supprimée'' AFTER `category_name`', 'SELECT 1');
PREPARE t8 FROM @sql; EXECUTE t8; DEALLOCATE PREPARE t8;

SET @sql = IF ((SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME='bridge_transactions' AND COLUMN_NAME='is_future')=0,
  'ALTER TABLE `bridge_transactions` ADD COLUMN `is_future` TINYINT(1) NOT NULL DEFAULT 0 COMMENT ''Transaction à venir'' AFTER `is_deleted`', 'SELECT 1');
PREPARE t9 FROM @sql; EXECUTE t9; DEALLOCATE PREPARE t9;

SET @sql = IF ((SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME='bridge_transactions' AND COLUMN_NAME='raw_data')=0,
  'ALTER TABLE `bridge_transactions` ADD COLUMN `raw_data` JSON NULL COMMENT ''Données brutes de Bridge'' AFTER `is_future`', 'SELECT 1');
PREPARE t10 FROM @sql; EXECUTE t10; DEALLOCATE PREPARE t10;

SET @sql = IF ((SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME='bridge_transactions' AND COLUMN_NAME='created_at')=0,
  'ALTER TABLE `bridge_transactions` ADD COLUMN `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP AFTER `raw_data`', 'SELECT 1');
PREPARE t11 FROM @sql; EXECUTE t11; DEALLOCATE PREPARE t11;

SET @sql = IF ((SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME='bridge_transactions' AND COLUMN_NAME='updated_at')=0,
  'ALTER TABLE `bridge_transactions` ADD COLUMN `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP AFTER `created_at`', 'SELECT 1');
PREPARE t12 FROM @sql; EXECUTE t12; DEALLOCATE PREPARE t12;

-- Assurer la structure de bridge_users si existait déjà (compat)
SET @sql = IF ((SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME='bridge_users' AND COLUMN_NAME='bridge_uuid')=0,
  'ALTER TABLE `bridge_users` ADD COLUMN `bridge_uuid` VARCHAR(36) NULL COMMENT ''UUID Bridge (si disponible)'' AFTER `user_id`', 'SELECT 1');
PREPARE u1 FROM @sql; EXECUTE u1; DEALLOCATE PREPARE u1;

SET @sql = IF ((SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME='bridge_users' AND COLUMN_NAME='bridge_email')=0,
  'ALTER TABLE `bridge_users` ADD COLUMN `bridge_email` VARCHAR(255) NOT NULL COMMENT ''Email utilisé pour Bridge'' AFTER `bridge_uuid`', 'SELECT 1');
PREPARE u2 FROM @sql; EXECUTE u2; DEALLOCATE PREPARE u2;

SET @sql = IF ((SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME='bridge_users' AND COLUMN_NAME='access_token')=0,
  'ALTER TABLE `bridge_users` ADD COLUMN `access_token` TEXT NULL COMMENT ''Token d''accès Bridge (Bearer)'' AFTER `bridge_email`', 'SELECT 1');
PREPARE u3 FROM @sql; EXECUTE u3; DEALLOCATE PREPARE u3;

SET @sql = IF ((SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME='bridge_users' AND COLUMN_NAME='token_expires_at')=0,
  'ALTER TABLE `bridge_users` ADD COLUMN `token_expires_at` DATETIME NULL COMMENT ''Date d''expiration du token'' AFTER `access_token`', 'SELECT 1');
PREPARE u4 FROM @sql; EXECUTE u4; DEALLOCATE PREPARE u4;

SET @sql = IF ((SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME='bridge_users' AND COLUMN_NAME='last_sync')=0,
  'ALTER TABLE `bridge_users` ADD COLUMN `last_sync` DATETIME NULL COMMENT ''Dernière synchronisation des comptes'' AFTER `token_expires_at`', 'SELECT 1');
PREPARE u5 FROM @sql; EXECUTE u5; DEALLOCATE PREPARE u5;

SET @sql = IF ((SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME='bridge_users' AND COLUMN_NAME='created_at')=0,
  'ALTER TABLE `bridge_users` ADD COLUMN `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP AFTER `last_sync`', 'SELECT 1');
PREPARE u6 FROM @sql; EXECUTE u6; DEALLOCATE PREPARE u6;

SET @sql = IF ((SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME='bridge_users' AND COLUMN_NAME='updated_at')=0,
  'ALTER TABLE `bridge_users` ADD COLUMN `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP AFTER `created_at`', 'SELECT 1');
PREPARE u7 FROM @sql; EXECUTE u7; DEALLOCATE PREPARE u7;

-- =====================================================
-- 6. Créer la table bridge_webhooks pour les callbacks
-- =====================================================

CREATE TABLE IF NOT EXISTS `bridge_webhooks` (
  `id` BIGINT NOT NULL AUTO_INCREMENT,
  `event_type` VARCHAR(100) NOT NULL COMMENT 'Type d\'événement Bridge',
  `bridge_user_uuid` VARCHAR(36) NULL COMMENT 'UUID de l\'utilisateur concerné',
  `bridge_item_id` BIGINT NULL COMMENT 'ID de l\'item concerné',
  `payload` JSON NOT NULL COMMENT 'Payload complet du webhook',
  `processed` TINYINT(1) NOT NULL DEFAULT 0 COMMENT 'Webhook traité',
  `processed_at` DATETIME NULL COMMENT 'Date de traitement',
  `error` TEXT NULL COMMENT 'Erreur de traitement éventuelle',
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  INDEX `idx_event_type` (`event_type`),
  INDEX `idx_bridge_user_uuid` (`bridge_user_uuid`),
  INDEX `idx_processed` (`processed`),
  INDEX `idx_created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
COMMENT='Webhooks Bridge reçus pour audit et traitement asynchrone';

-- =====================================================
-- 7. Migrer/initialiser les données existantes
-- =====================================================

-- Copier/mettre à jour depuis auth_db.users (si uuid/email existants)
INSERT INTO `bridge_users` (`user_id`, `bridge_uuid`, `bridge_email`, `created_at`, `updated_at`)
SELECT 
  u.`id` AS user_id,
  NULLIF(u.`uuid`, '') AS bridge_uuid,
  COALESCE(u.`email`, '') AS bridge_email,
  NOW(),
  NOW()
FROM `users` u
WHERE u.`email` IS NOT NULL AND u.`email` != ''
ON DUPLICATE KEY UPDATE 
  `bridge_uuid` = VALUES(`bridge_uuid`),
  `bridge_email` = VALUES(`bridge_email`),
  `updated_at` = NOW();

-- =====================================================
-- 8. Vues utiles pour les requêtes courantes
-- =====================================================

-- Vue complète des comptes avec informations utilisateur
CREATE OR REPLACE VIEW `v_bridge_accounts_full` AS
SELECT 
  ba.id,
  ba.bridge_account_id,
  ba.account_name,
  ba.account_type,
  ba.iban,
  ba.balance,
  ba.bank_name,
  ba.status,
  ba.last_sync,
  bu.user_id,
  vu.email AS user_email,
  bu.bridge_uuid
FROM bridge_accounts ba
INNER JOIN bridge_users bu ON ba.bridge_user_id = bu.id
INNER JOIN users vu ON bu.user_id = vu.id
WHERE ba.is_active = 1;

-- Vue des transactions récentes (30 derniers jours)
CREATE OR REPLACE VIEW `v_bridge_transactions_recent` AS
SELECT 
  bt.id,
  bt.transaction_date,
  bt.description,
  bt.amount,
  bt.currency,
  bt.category_name,
  ba.account_name,
  ba.bank_name,
  vu.email AS user_email,
  NULL AS first_name,
  NULL AS last_name
FROM bridge_transactions bt
INNER JOIN bridge_accounts ba ON bt.bridge_account_id = ba.id
INNER JOIN bridge_users bu ON ba.bridge_user_id = bu.id
INNER JOIN users vu ON bu.user_id = vu.id
WHERE bt.transaction_date >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)
  AND bt.is_deleted = 0
ORDER BY bt.transaction_date DESC;

-- =====================================================
-- 9. Procédures stockées utiles
-- =====================================================

DELIMITER $$

-- Procédure pour récupérer tous les comptes d'un utilisateur
DROP PROCEDURE IF EXISTS `sp_get_user_accounts`$$
CREATE PROCEDURE `sp_get_user_accounts`(IN p_user_id INT)
BEGIN
  SELECT 
    ba.*,
    bu.bridge_uuid,
    bu.bridge_email
  FROM bridge_accounts ba
  INNER JOIN bridge_users bu ON ba.bridge_user_id = bu.id
  WHERE bu.user_id = p_user_id
    AND ba.is_active = 1
  ORDER BY ba.bank_name, ba.account_name;
END$$

-- Procédure pour récupérer les transactions d'un compte
DROP PROCEDURE IF EXISTS `sp_get_account_transactions`$$
CREATE PROCEDURE `sp_get_account_transactions`(
  IN p_account_id INT,
  IN p_limit INT,
  IN p_offset INT
)
BEGIN
  SELECT 
    bt.*
  FROM bridge_transactions bt
  WHERE bt.bridge_account_id = p_account_id
    AND bt.is_deleted = 0
  ORDER BY bt.transaction_date DESC, bt.id DESC
  LIMIT p_offset, p_limit;
END$$

-- Procédure pour mettre à jour le solde d'un compte
DROP PROCEDURE IF EXISTS `sp_update_account_balance`$$
CREATE PROCEDURE `sp_update_account_balance`(
  IN p_bridge_account_id BIGINT,
  IN p_new_balance DECIMAL(15,2)
)
BEGIN
  UPDATE bridge_accounts
  SET 
    balance = p_new_balance,
    last_sync = NOW(),
    updated_at = NOW()
  WHERE bridge_account_id = p_bridge_account_id;
END$$

DELIMITER ;

-- =====================================================
-- FIN DU SCRIPT
-- =====================================================

-- Afficher un résumé
SELECT 'Setup Bridge Database - Terminé ✅' AS Status;

SELECT 
  'Tables créées' AS Info,
  COUNT(*) AS Count
FROM information_schema.tables 
WHERE table_schema = 'auth_db' 
  AND table_name LIKE 'bridge_%';

SELECT 
  'Utilisateurs Bridge' AS Info,
  COUNT(*) AS Count
FROM bridge_users;

-- =====================================================
-- Restaurer le mode SQL d'origine
-- =====================================================

SET sql_mode = @old_sql_mode;
