-- =====================================================
-- Script de configuration de la base de données Bridge
-- Date: 1 novembre 2025
-- =====================================================

USE auth_db;

-- =====================================================
-- 0. Désactiver temporairement le mode strict SQL
-- =====================================================

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;

-- =====================================================
-- 2. 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 NOT NULL COMMENT 'Référence vers users.id',
  `bridge_uuid` VARCHAR(36) NOT NULL COMMENT 'UUID de l\'utilisateur Bridge',
  `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_id` (`user_id`),
  UNIQUE 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';

-- =====================================================
-- 3. 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.)',
  `account_currency` VARCHAR(3) NULL DEFAULT 'EUR' COMMENT 'Devise du compte',
  `account_iban` VARCHAR(34) NULL COMMENT 'IBAN du compte',
  `account_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';

-- =====================================================
-- 4. 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';

-- =====================================================
-- 5. 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';

-- =====================================================
-- 6. Migrer les données existantes
-- =====================================================

-- Copier l'UUID existant de l'utilisateur 1 dans bridge_users
INSERT INTO `bridge_users` (`user_id`, `bridge_uuid`, `bridge_email`, `created_at`, `updated_at`)
SELECT 
  `id`,
  `uuid`,
  `email`,
  NOW(),
  NOW()
FROM `users`
WHERE `uuid` IS NOT NULL AND `uuid` != ''
ON DUPLICATE KEY UPDATE 
  `bridge_uuid` = VALUES(`bridge_uuid`),
  `bridge_email` = VALUES(`bridge_email`),
  `updated_at` = NOW();

-- =====================================================
-- 7. 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.account_iban,
  ba.account_balance,
  ba.bank_name,
  ba.status,
  ba.last_sync,
  bu.user_id,
  u.email AS user_email,
  u.first_name,
  u.last_name,
  bu.bridge_uuid
FROM bridge_accounts ba
INNER JOIN bridge_users bu ON ba.bridge_user_id = bu.id
INNER JOIN users u ON bu.user_id = u.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,
  u.email AS user_email,
  u.first_name,
  u.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 u ON bu.user_id = u.id
WHERE bt.transaction_date >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)
  AND bt.is_deleted = 0
ORDER BY bt.transaction_date DESC;

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

DELIMITER $$

-- Procédure pour récupérer tous les comptes d'un utilisateur
CREATE PROCEDURE IF NOT EXISTS `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
CREATE PROCEDURE IF NOT EXISTS `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
CREATE PROCEDURE IF NOT EXISTS `sp_update_account_balance`(
  IN p_bridge_account_id BIGINT,
  IN p_new_balance DECIMAL(15,2)
)
BEGIN
  UPDATE bridge_accounts
  SET 
    account_balance = p_new_balance,
    last_sync = NOW(),
    updated_at = NOW()
  WHERE bridge_account_id = p_bridge_account_id;
END$$

DELIMITER ;

-- =====================================================
-- 9. Triggers pour maintenir la cohérence
-- =====================================================

-- Note: Les triggers sont optionnels et peuvent nécessiter des privilèges SUPER
-- Si vous n'avez pas ces privilèges, commentez cette section

-- Supprimer les triggers s'ils existent déjà
DROP TRIGGER IF EXISTS `tr_bridge_users_update_uuid`;
DROP TRIGGER IF EXISTS `tr_bridge_accounts_update_user_sync`;

DELIMITER $$

-- Trigger pour synchroniser l'UUID dans users quand bridge_users change
CREATE TRIGGER `tr_bridge_users_update_uuid`
AFTER INSERT ON `bridge_users`
FOR EACH ROW
BEGIN
  UPDATE `users` 
  SET `uuid` = NEW.bridge_uuid
  WHERE `id` = NEW.user_id;
END$$

-- Trigger pour mettre à jour last_sync de bridge_users quand un compte est synchronisé
CREATE TRIGGER `tr_bridge_accounts_update_user_sync`
AFTER UPDATE ON `bridge_accounts`
FOR EACH ROW
BEGIN
  IF NEW.last_sync IS NOT NULL AND (OLD.last_sync IS NULL OR NEW.last_sync != OLD.last_sync) THEN
    UPDATE `bridge_users`
    SET `last_sync` = NEW.last_sync
    WHERE `id` = NEW.bridge_user_id;
  END IF;
END$$

DELIMITER ;

-- =====================================================
-- 10. Données de test (optionnel - décommenter si besoin)
-- =====================================================

/*
-- Exemple d'insertion d'un compte de test
INSERT INTO bridge_accounts (
  bridge_user_id, 
  bridge_account_id, 
  account_name, 
  account_type, 
  account_iban,
  account_balance,
  bank_name,
  status
) VALUES (
  1,  -- Supposant que bridge_user_id 1 existe
  123456789,
  'Compte Courant',
  'checking',
  'FR7612345678901234567890123',
  1500.00,
  'Banque de Test',
  'ok'
);
*/

-- =====================================================
-- 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;
