-- Script de rattrapage structure PROD
-- Objectif:
-- 1) Uniformiser charset/collation pour eviter les erreurs #1267 / #1271
-- 2) Verifier/creer la structure des tables d'autorisations
-- 3) Re-seeder les permissions de maniere idempotente (sans dependre des IDs)
--
-- Usage:
--   USE association_app;
--   SOURCE sql/prod_structure_repair.sql;
--
-- Recommande avant execution:
--   - sauvegarde complete de la base
--   - execution sur une fenetre de maintenance

SET @target_db := DATABASE();

-- Si aucun schema n'est selectionne, stop.
SELECT IF(@target_db IS NULL, 'ERROR: no database selected (USE ...)', CONCAT('Database: ', @target_db)) AS context_check;

-- 1) Collation/charset de la base.
-- IMPORTANT:
-- ALTER DATABASE via PREPARE peut echouer selon la version MySQL/MariaDB.
-- On l'execute donc en direct pour la base cible de l'application.
-- ALTER DATABASE `association_app` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- 2) Convertir chaque table en utf8mb4/utf8mb4_unicode_ci.
--    "CONVERT TO" aligne a la fois la collation des colonnes textuelles et celle de la table.
DROP PROCEDURE IF EXISTS sp_convert_all_tables_to_utf8mb4_unicode_ci;
DELIMITER $$
CREATE PROCEDURE sp_convert_all_tables_to_utf8mb4_unicode_ci()
BEGIN
    DECLARE done INT DEFAULT 0;
    DECLARE v_table_name VARCHAR(255);
    DECLARE cur_tables CURSOR FOR
        SELECT t.TABLE_NAME
        FROM INFORMATION_SCHEMA.TABLES t
        WHERE t.TABLE_SCHEMA = DATABASE()
          AND t.TABLE_TYPE = 'BASE TABLE';
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

    OPEN cur_tables;
    tables_loop: LOOP
        FETCH cur_tables INTO v_table_name;
        IF done = 1 THEN
            LEAVE tables_loop;
        END IF;

        SET @sql_convert_table := CONCAT(
            'ALTER TABLE `', v_table_name, '` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci'
        );
        PREPARE stmt_convert_table FROM @sql_convert_table;
        EXECUTE stmt_convert_table;
        DEALLOCATE PREPARE stmt_convert_table;
    END LOOP;
    CLOSE cur_tables;
END$$
DELIMITER ;

CALL sp_convert_all_tables_to_utf8mb4_unicode_ci();
DROP PROCEDURE IF EXISTS sp_convert_all_tables_to_utf8mb4_unicode_ci;

-- 3) Structure minimale des autorisations.
CREATE TABLE IF NOT EXISTS association_board_roles (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    association_id INT UNSIGNED NOT NULL,
    role_name VARCHAR(120) NOT NULL,
    display_order INT UNSIGNED NOT NULL DEFAULT 0,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_association_board_role_association
        FOREIGN KEY (association_id) REFERENCES associations(id)
        ON DELETE CASCADE,
    CONSTRAINT uq_association_board_role UNIQUE (association_id, role_name)
);

CREATE TABLE IF NOT EXISTS authorization_permission_groups (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    code VARCHAR(80) NOT NULL,
    label VARCHAR(140) NOT NULL,
    display_order INT UNSIGNED NOT NULL DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT uq_authorization_permission_group_code UNIQUE (code)
);

CREATE TABLE IF NOT EXISTS authorization_permissions (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    group_id INT UNSIGNED NOT NULL,
    permission_key VARCHAR(120) NOT NULL,
    label VARCHAR(190) NOT NULL,
    permission_scope ENUM('menu', 'data', 'action') NOT NULL DEFAULT 'action',
    resource_code VARCHAR(120) NULL,
    operation_code VARCHAR(40) NULL,
    display_order INT UNSIGNED NOT NULL DEFAULT 0,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_authorization_permission_group
        FOREIGN KEY (group_id) REFERENCES authorization_permission_groups(id)
        ON DELETE CASCADE,
    CONSTRAINT uq_authorization_permission_key UNIQUE (permission_key)
);

CREATE TABLE IF NOT EXISTS association_board_role_permissions (
    association_board_role_id INT UNSIGNED NOT NULL,
    permission_id INT UNSIGNED NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (association_board_role_id, permission_id),
    CONSTRAINT fk_board_role_permissions_role
        FOREIGN KEY (association_board_role_id) REFERENCES association_board_roles(id)
        ON DELETE CASCADE,
    CONSTRAINT fk_board_role_permissions_permission
        FOREIGN KEY (permission_id) REFERENCES authorization_permissions(id)
        ON DELETE CASCADE
);

-- 3.b) Contraintes manquantes (cas tables deja presentes avec structure partielle).
SET @has_uq_apg_code := (
    SELECT COUNT(*)
    FROM INFORMATION_SCHEMA.STATISTICS
    WHERE TABLE_SCHEMA = DATABASE()
      AND TABLE_NAME = 'authorization_permission_groups'
      AND COLUMN_NAME = 'code'
      AND NON_UNIQUE = 0
);

SET @sql_add_uq_apg_code := IF(
    @has_uq_apg_code = 0,
    'ALTER TABLE authorization_permission_groups ADD CONSTRAINT uq_authorization_permission_group_code UNIQUE (code)',
    'SELECT 1'
);
PREPARE stmt_add_uq_apg_code FROM @sql_add_uq_apg_code;
EXECUTE stmt_add_uq_apg_code;
DEALLOCATE PREPARE stmt_add_uq_apg_code;

SET @has_uq_ap_permission_key := (
    SELECT COUNT(*)
    FROM INFORMATION_SCHEMA.STATISTICS
    WHERE TABLE_SCHEMA = DATABASE()
      AND TABLE_NAME = 'authorization_permissions'
      AND COLUMN_NAME = 'permission_key'
      AND NON_UNIQUE = 0
);

SET @sql_add_uq_ap_permission_key := IF(
    @has_uq_ap_permission_key = 0,
    'ALTER TABLE authorization_permissions ADD CONSTRAINT uq_authorization_permission_key UNIQUE (permission_key)',
    'SELECT 1'
);
PREPARE stmt_add_uq_ap_permission_key FROM @sql_add_uq_ap_permission_key;
EXECUTE stmt_add_uq_ap_permission_key;
DEALLOCATE PREPARE stmt_add_uq_ap_permission_key;

SET @has_fk_ap_group := (
    SELECT COUNT(*)
    FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
    WHERE TABLE_SCHEMA = DATABASE()
      AND TABLE_NAME = 'authorization_permissions'
      AND COLUMN_NAME = 'group_id'
      AND REFERENCED_TABLE_NAME = 'authorization_permission_groups'
);

SET @sql_add_fk_ap_group := IF(
    @has_fk_ap_group = 0,
    'ALTER TABLE authorization_permissions ADD CONSTRAINT fk_authorization_permission_group FOREIGN KEY (group_id) REFERENCES authorization_permission_groups(id) ON DELETE CASCADE',
    'SELECT 1'
);
PREPARE stmt_add_fk_ap_group FROM @sql_add_fk_ap_group;
EXECUTE stmt_add_fk_ap_group;
DEALLOCATE PREPARE stmt_add_fk_ap_group;

SET @has_fk_abrp_role := (
    SELECT COUNT(*)
    FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
    WHERE TABLE_SCHEMA = DATABASE()
      AND TABLE_NAME = 'association_board_role_permissions'
      AND COLUMN_NAME = 'association_board_role_id'
      AND REFERENCED_TABLE_NAME = 'association_board_roles'
);

SET @sql_add_fk_abrp_role := IF(
    @has_fk_abrp_role = 0,
    'ALTER TABLE association_board_role_permissions ADD CONSTRAINT fk_board_role_permissions_role FOREIGN KEY (association_board_role_id) REFERENCES association_board_roles(id) ON DELETE CASCADE',
    'SELECT 1'
);
PREPARE stmt_add_fk_abrp_role FROM @sql_add_fk_abrp_role;
EXECUTE stmt_add_fk_abrp_role;
DEALLOCATE PREPARE stmt_add_fk_abrp_role;

SET @has_fk_abrp_permission := (
    SELECT COUNT(*)
    FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
    WHERE TABLE_SCHEMA = DATABASE()
      AND TABLE_NAME = 'association_board_role_permissions'
      AND COLUMN_NAME = 'permission_id'
      AND REFERENCED_TABLE_NAME = 'authorization_permissions'
);

SET @sql_add_fk_abrp_permission := IF(
    @has_fk_abrp_permission = 0,
    'ALTER TABLE association_board_role_permissions ADD CONSTRAINT fk_board_role_permissions_permission FOREIGN KEY (permission_id) REFERENCES authorization_permissions(id) ON DELETE CASCADE',
    'SELECT 1'
);
PREPARE stmt_add_fk_abrp_permission FROM @sql_add_fk_abrp_permission;
EXECUTE stmt_add_fk_abrp_permission;
DEALLOCATE PREPARE stmt_add_fk_abrp_permission;

-- 4) Seed groupes (idempotent).
INSERT INTO authorization_permission_groups (code, label, display_order)
VALUES
    ('dashboard', 'Dashboard', 10),
    ('associations', 'Associations', 20),
    ('adherents', 'Adherents', 30),
    ('adhesions', 'Adhesions', 40),
    ('events', 'Evenements', 50),
    ('treasury', 'Tresorerie', 60),
    ('billing', 'Facturation', 65),
    ('messaging', 'Messagerie', 70),
    ('contacts', 'Contacts', 80)
ON DUPLICATE KEY UPDATE
    label = VALUES(label),
    display_order = VALUES(display_order);

-- 5) Seed permissions (idempotent, rattachees par group code).
INSERT INTO authorization_permissions (
    group_id,
    permission_key,
    label,
    permission_scope,
    resource_code,
    operation_code,
    display_order,
    is_active
)
SELECT g.id, x.permission_key, x.label, x.scope, x.resource_code, x.operation_code, x.display_order, 1
FROM authorization_permission_groups g
INNER JOIN (
    SELECT 'dashboard' AS group_code, 'dashboard_open' AS permission_key, 'Acces au menu Dashboard' AS label, 'menu' AS scope, NULL AS resource_code, 'open' AS operation_code, 10 AS display_order

    UNION ALL SELECT 'associations', 'associations_open', 'Acces au menu Associations', 'menu', NULL, 'open', 10
    UNION ALL SELECT 'associations', 'associations_add', 'Associations', 'data', 'associations', 'add', 20
    UNION ALL SELECT 'associations', 'associations_edit', 'Associations', 'data', 'associations', 'edit', 21
    UNION ALL SELECT 'associations', 'associations_delete', 'Associations', 'data', 'associations', 'delete', 22
    UNION ALL SELECT 'associations', 'poles_add', 'Poles', 'data', 'poles', 'add', 23
    UNION ALL SELECT 'associations', 'poles_edit', 'Poles', 'data', 'poles', 'edit', 24
    UNION ALL SELECT 'associations', 'poles_delete', 'Poles', 'data', 'poles', 'delete', 25
    UNION ALL SELECT 'associations', 'associations_permissions_manage', 'Gerer les autorisations', 'action', NULL, NULL, 30

    UNION ALL SELECT 'adherents', 'year_open', 'Acces au menu Adherents', 'menu', NULL, 'open', 10
    UNION ALL SELECT 'adherents', 'memberships_add', 'Adherents / adhesions annuelles', 'data', 'memberships', 'add', 20
    UNION ALL SELECT 'adherents', 'memberships_edit', 'Adherents / adhesions annuelles', 'data', 'memberships', 'edit', 21
    UNION ALL SELECT 'adherents', 'memberships_delete', 'Adherents / adhesions annuelles', 'data', 'memberships', 'delete', 22
    UNION ALL SELECT 'adherents', 'years_add', 'Annees associatives', 'data', 'years', 'add', 30
    UNION ALL SELECT 'adherents', 'years_edit', 'Annees associatives', 'data', 'years', 'edit', 31
    UNION ALL SELECT 'adherents', 'years_delete', 'Annees associatives', 'data', 'years', 'delete', 32
    UNION ALL SELECT 'adherents', 'btn_send_membership_access', 'Envoyer lien activation adherent (individuel + masse)', 'action', NULL, NULL, 40
    UNION ALL SELECT 'adherents', 'btn_send_board_access', 'Envoyer lien activation membres du CA', 'action', NULL, NULL, 41
    UNION ALL SELECT 'adherents', 'btn_send_receipt', 'Envoyer lien recepisse adhesion', 'action', NULL, NULL, 42

    UNION ALL SELECT 'adhesions', 'plans_open', 'Acces au menu Adhesions', 'menu', NULL, 'open', 10
    UNION ALL SELECT 'adhesions', 'plans_add', 'Plans d''adhesion', 'data', 'plans', 'add', 20
    UNION ALL SELECT 'adhesions', 'plans_edit', 'Plans d''adhesion', 'data', 'plans', 'edit', 21
    UNION ALL SELECT 'adhesions', 'plans_delete', 'Plans d''adhesion', 'data', 'plans', 'delete', 22

    UNION ALL SELECT 'events', 'events_open', 'Acces au menu Evenements', 'menu', NULL, 'open', 10
    UNION ALL SELECT 'events', 'events_add', 'Evenements', 'data', 'events', 'add', 20
    UNION ALL SELECT 'events', 'events_edit', 'Evenements', 'data', 'events', 'edit', 21
    UNION ALL SELECT 'events', 'events_delete', 'Evenements', 'data', 'events', 'delete', 22
    UNION ALL SELECT 'events', 'invitees_add', 'Invites evenement', 'data', 'invitees', 'add', 30
    UNION ALL SELECT 'events', 'invitees_edit', 'Invites evenement', 'data', 'invitees', 'edit', 31
    UNION ALL SELECT 'events', 'invitees_delete', 'Invites evenement', 'data', 'invitees', 'delete', 32
    UNION ALL SELECT 'events', 'btn_send_event_invite', 'Envoyer invitation evenement (individuel + masse)', 'action', NULL, NULL, 40
    UNION ALL SELECT 'events', 'btn_send_event_report', 'Envoyer compte rendu evenement (individuel + masse)', 'action', NULL, NULL, 41

    UNION ALL SELECT 'treasury', 'treasury_open', 'Acces au menu Tresorerie', 'menu', NULL, 'open', 10
    UNION ALL SELECT 'treasury', 'treasury_entries_add', 'Ecritures de tresorerie', 'data', 'treasury_entries', 'add', 20
    UNION ALL SELECT 'treasury', 'treasury_entries_edit', 'Ecritures de tresorerie', 'data', 'treasury_entries', 'edit', 21
    UNION ALL SELECT 'treasury', 'treasury_entries_delete', 'Ecritures de tresorerie', 'data', 'treasury_entries', 'delete', 22
    UNION ALL SELECT 'treasury', 'treasury_accounts_add', 'Comptes de tresorerie', 'data', 'treasury_accounts', 'add', 30
    UNION ALL SELECT 'treasury', 'treasury_accounts_edit', 'Comptes de tresorerie', 'data', 'treasury_accounts', 'edit', 31
    UNION ALL SELECT 'treasury', 'treasury_accounts_delete', 'Comptes de tresorerie', 'data', 'treasury_accounts', 'delete', 32
    UNION ALL SELECT 'treasury', 'btn_treasury_import', 'Importer ecritures tresorerie', 'action', NULL, NULL, 40

    UNION ALL SELECT 'billing', 'billing_open', 'Acces au menu Facturation', 'menu', NULL, 'open', 10
    UNION ALL SELECT 'billing', 'invoices_add', 'Factures', 'data', 'invoices', 'add', 20
    UNION ALL SELECT 'billing', 'invoices_edit', 'Factures', 'data', 'invoices', 'edit', 21
    UNION ALL SELECT 'billing', 'invoices_delete', 'Factures', 'data', 'invoices', 'delete', 22
    UNION ALL SELECT 'billing', 'btn_send_invoice', 'Envoyer lien facture (lien + code)', 'action', NULL, NULL, 40

    UNION ALL SELECT 'messaging', 'messaging_open', 'Acces au menu Messagerie', 'menu', NULL, 'open', 10
    UNION ALL SELECT 'messaging', 'mail_templates_add', 'Templates mails', 'data', 'mail_templates', 'add', 20
    UNION ALL SELECT 'messaging', 'mail_templates_edit', 'Templates mails', 'data', 'mail_templates', 'edit', 21
    UNION ALL SELECT 'messaging', 'mail_templates_delete', 'Templates mails', 'data', 'mail_templates', 'delete', 22
    UNION ALL SELECT 'messaging', 'mail_templates_open', 'Acces a la page Templates mails', 'action', NULL, NULL, 30
    UNION ALL SELECT 'messaging', 'gmail_inbox_open', 'Acces a la boite de reception', 'action', NULL, NULL, 31
    UNION ALL SELECT 'messaging', 'gmail_sent_open', 'Acces aux elements envoyes', 'action', NULL, NULL, 32
    UNION ALL SELECT 'messaging', 'btn_gmail_connect', 'Connecter Gmail', 'action', NULL, NULL, 33
    UNION ALL SELECT 'messaging', 'btn_gmail_disconnect', 'Deconnecter Gmail', 'action', NULL, NULL, 34
    UNION ALL SELECT 'messaging', 'btn_send_gmail', 'Envoyer e-mail Gmail', 'action', NULL, NULL, 35

    UNION ALL SELECT 'contacts', 'contacts_open', 'Acces au menu Contacts', 'menu', NULL, 'open', 10
    UNION ALL SELECT 'contacts', 'contacts_add', 'Contacts', 'data', 'contacts', 'add', 20
    UNION ALL SELECT 'contacts', 'contacts_edit', 'Contacts', 'data', 'contacts', 'edit', 21
    UNION ALL SELECT 'contacts', 'contacts_delete', 'Contacts', 'data', 'contacts', 'delete', 22
    UNION ALL SELECT 'contacts', 'contacts_import', 'Importer des contacts', 'action', NULL, NULL, 30
) x
    ON x.group_code = g.code
ON DUPLICATE KEY UPDATE
    group_id = VALUES(group_id),
    label = VALUES(label),
    permission_scope = VALUES(permission_scope),
    resource_code = VALUES(resource_code),
    operation_code = VALUES(operation_code),
    display_order = VALUES(display_order),
    is_active = VALUES(is_active);

-- 6) Templates mails et invitations formulaire adhesion.
CREATE TABLE IF NOT EXISTS mail_templates (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    association_id INT UNSIGNED NULL,
    code VARCHAR(80) NOT NULL,
    label VARCHAR(190) NOT NULL,
    subject VARCHAR(255) NOT NULL,
    body_text TEXT NOT NULL,
    body_html MEDIUMTEXT NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_mail_templates_association
        FOREIGN KEY (association_id) REFERENCES associations(id)
        ON DELETE CASCADE,
    CONSTRAINT uq_mail_template_scope UNIQUE (association_id, code)
);

-- 6.b) Budgets d'evenement (lignes budgetaires hierarchiques)
CREATE TABLE IF NOT EXISTS event_budgets (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    event_id INT UNSIGNED NOT NULL,
    parent_id INT UNSIGNED NULL,
    name VARCHAR(190) NOT NULL,
    description_long TEXT NULL,
    quantity INT NOT NULL DEFAULT 1,
    expense_amount DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    income_amount DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    display_order INT NOT NULL DEFAULT 0,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL,
    INDEX idx_event_budgets_event_parent (event_id, parent_id),
    INDEX idx_event_budgets_event_order (event_id, display_order, id),
    CONSTRAINT fk_event_budgets_event FOREIGN KEY (event_id) REFERENCES events(id) ON DELETE CASCADE,
    CONSTRAINT fk_event_budgets_parent FOREIGN KEY (parent_id) REFERENCES event_budgets(id) ON DELETE CASCADE
);

SET @has_event_budgets_quantity := (
    SELECT COUNT(*)
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE()
      AND TABLE_NAME = 'event_budgets'
      AND COLUMN_NAME = 'quantity'
);

SET @sql_add_event_budgets_quantity := IF(
    @has_event_budgets_quantity = 0,
    'ALTER TABLE event_budgets ADD COLUMN quantity INT NOT NULL DEFAULT 1 AFTER description_long',
    'SELECT 1'
);
PREPARE stmt_add_event_budgets_quantity FROM @sql_add_event_budgets_quantity;
EXECUTE stmt_add_event_budgets_quantity;
DEALLOCATE PREPARE stmt_add_event_budgets_quantity;

-- 6.c) Invites evenement: conserver les ajouts manuels lors des regenerations.
SET @has_event_invitees_table := (
        SELECT COUNT(*)
        FROM INFORMATION_SCHEMA.TABLES
        WHERE TABLE_SCHEMA = DATABASE()
            AND TABLE_NAME = 'event_invitees'
);

SET @has_event_invitees_is_manual := (
        SELECT COUNT(*)
        FROM INFORMATION_SCHEMA.COLUMNS
        WHERE TABLE_SCHEMA = DATABASE()
            AND TABLE_NAME = 'event_invitees'
            AND COLUMN_NAME = 'is_manual'
);

SET @sql_add_event_invitees_is_manual := IF(
        @has_event_invitees_table = 1 AND @has_event_invitees_is_manual = 0,
        'ALTER TABLE event_invitees ADD COLUMN is_manual TINYINT(1) NOT NULL DEFAULT 0 AFTER member_id',
        'SELECT 1'
);
PREPARE stmt_add_event_invitees_is_manual FROM @sql_add_event_invitees_is_manual;
EXECUTE stmt_add_event_invitees_is_manual;
DEALLOCATE PREPARE stmt_add_event_invitees_is_manual;

INSERT INTO mail_templates (association_id, code, label, subject, body_text, body_html)
SELECT NULL,
       'formulaire_adhesion',
       'Formulaire adhesion',
       'Formulaire d adhesion - {{association_name}} - {{year_label}}',
       'Bonjour {{first_name}} {{last_name}},\n\nMerci de completer votre formulaire d adhesion pour {{association_name}} (annee {{year_label}}) via le lien ci-dessous :\n{{application_url}}\n\nCe lien expire le {{expires_at}}.\n\nCordialement.',
       '<div style="text-align:left;"><p>Bonjour {{first_name}} {{last_name}},</p><p>Merci de completer votre formulaire d adhesion pour <strong>{{association_name}}</strong> (annee <strong>{{year_label}}</strong>) via le lien ci-dessous :</p><p><a href="{{application_url}}">{{application_url}}</a></p><p>Ce lien expire le {{expires_at}}.</p><p>Cordialement.</p></div>'
WHERE NOT EXISTS (
    SELECT 1
    FROM mail_templates mt
    WHERE mt.association_id IS NULL
      AND mt.code = 'formulaire_adhesion'
);

INSERT INTO mail_templates (association_id, code, label, subject, body_text, body_html)
SELECT NULL,
             'message_evenement',
             'Message événement',
             'Message - {{event_title}}',
             'Bonjour {{first_name}},\n\n\nCordialement,\n{{sender_name}}\n{{association_name}}',
             '<div style="text-align:left;"><p>Bonjour {{first_name}},</p><p></p><p>Cordialement,</p><p>{{sender_name}}</p><p>{{association_name}}</p></div>'
WHERE NOT EXISTS (
        SELECT 1
        FROM mail_templates mt
        WHERE mt.association_id IS NULL
            AND mt.code = 'message_evenement'
);

CREATE TABLE IF NOT EXISTS membership_application_invites (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    association_id INT UNSIGNED NOT NULL,
    association_year_id INT UNSIGNED NOT NULL,
    member_id INT UNSIGNED NULL,
    recipient_email VARCHAR(190) NULL,
    token_hash CHAR(64) NOT NULL,
    expires_at DATETIME NOT NULL,
    created_by_member_id INT UNSIGNED NULL,
    sent_at DATETIME NULL,
    used_at DATETIME NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_membership_app_invite_association
        FOREIGN KEY (association_id) REFERENCES associations(id)
        ON DELETE CASCADE,
    CONSTRAINT fk_membership_app_invite_year
        FOREIGN KEY (association_year_id) REFERENCES association_years(id)
        ON DELETE CASCADE,
    CONSTRAINT fk_membership_app_invite_member
        FOREIGN KEY (member_id) REFERENCES members(id)
        ON DELETE SET NULL,
    CONSTRAINT fk_membership_app_invite_creator
        FOREIGN KEY (created_by_member_id) REFERENCES members(id)
        ON DELETE SET NULL,
    CONSTRAINT uq_membership_app_invite_token UNIQUE (token_hash)
);

CREATE TABLE IF NOT EXISTS membership_application_document_links (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    membership_id INT UNSIGNED NOT NULL,
    recipient_email VARCHAR(190) NOT NULL,
    token_hash CHAR(64) NOT NULL,
    expires_at DATETIME NOT NULL,
    created_by_member_id INT UNSIGNED NULL,
    sent_at DATETIME NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_membership_app_doc_membership
        FOREIGN KEY (membership_id) REFERENCES year_memberships(id)
        ON DELETE CASCADE,
    CONSTRAINT fk_membership_app_doc_created_by
        FOREIGN KEY (created_by_member_id) REFERENCES members(id)
        ON DELETE SET NULL,
    CONSTRAINT uq_membership_app_doc_token UNIQUE (token_hash)
);

SET @has_members_photo_publication_consent := (
    SELECT COUNT(*)
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE()
      AND TABLE_NAME = 'members'
      AND COLUMN_NAME = 'photo_publication_consent'
);

SET @sql_add_members_photo_publication_consent := IF(
    @has_members_photo_publication_consent = 0,
    'ALTER TABLE members ADD COLUMN photo_publication_consent TINYINT(1) NOT NULL DEFAULT 0 AFTER phone',
    'SELECT 1'
);
PREPARE stmt_add_members_photo_publication_consent FROM @sql_add_members_photo_publication_consent;
EXECUTE stmt_add_members_photo_publication_consent;
DEALLOCATE PREPARE stmt_add_members_photo_publication_consent;


-- 7) Tables de facturation.

-- 7.a) Lien facture <-> adhesion via year_memberships.invoice_number (idempotent).
SET @has_year_membership_invoice_number := (
    SELECT COUNT(*)
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE()
      AND TABLE_NAME = 'year_memberships'
      AND COLUMN_NAME = 'invoice_number'
);

SET @sql_add_year_membership_invoice_number := IF(
    @has_year_membership_invoice_number = 0,
    'ALTER TABLE year_memberships ADD COLUMN invoice_number VARCHAR(64) NULL AFTER membership_paid_at',
    'SELECT 1'
);
PREPARE stmt_add_year_membership_invoice_number FROM @sql_add_year_membership_invoice_number;
EXECUTE stmt_add_year_membership_invoice_number;
DEALLOCATE PREPARE stmt_add_year_membership_invoice_number;

CREATE TABLE IF NOT EXISTS invoices (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    association_id INT UNSIGNED NOT NULL,
    association_year_id INT UNSIGNED NULL,
    invoice_number VARCHAR(64) NOT NULL,
    invoice_date DATE NOT NULL,
    due_date DATE NULL,
    customer_name VARCHAR(190) NOT NULL,
    customer_address TEXT NULL,
    customer_email VARCHAR(190) NULL,
    notes TEXT NULL,
    settled_amount DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    settled_date DATE NULL,
    payment_method ENUM('cheque', 'especes', 'virement') NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_invoices_association
        FOREIGN KEY (association_id) REFERENCES associations(id)
        ON DELETE CASCADE,
    CONSTRAINT fk_invoices_year
        FOREIGN KEY (association_year_id) REFERENCES association_years(id)
        ON DELETE SET NULL,
    INDEX idx_invoices_year_date (association_id, association_year_id, invoice_date),
    CONSTRAINT uq_invoices_number UNIQUE (association_id, invoice_number)
);

-- Ajout idempotent: invoices.payment_method
SET @has_invoices_payment_method := (
    SELECT COUNT(*)
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE()
      AND TABLE_NAME = 'invoices'
      AND COLUMN_NAME = 'payment_method'
);

SET @sql_add_invoices_payment_method := IF(
    @has_invoices_payment_method = 0,
    "ALTER TABLE invoices ADD COLUMN payment_method ENUM('cheque','especes','virement') NULL AFTER settled_date",
    'SELECT 1'
);
PREPARE stmt_add_invoices_payment_method FROM @sql_add_invoices_payment_method;
EXECUTE stmt_add_invoices_payment_method;
DEALLOCATE PREPARE stmt_add_invoices_payment_method;

-- Ajout idempotent: invoices.association_year_id
SET @has_invoices_association_year_id := (
    SELECT COUNT(*)
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE()
      AND TABLE_NAME = 'invoices'
      AND COLUMN_NAME = 'association_year_id'
);

SET @sql_add_invoices_association_year_id := IF(
    @has_invoices_association_year_id = 0,
    'ALTER TABLE invoices ADD COLUMN association_year_id INT UNSIGNED NULL AFTER association_id',
    'SELECT 1'
);
PREPARE stmt_add_invoices_association_year_id FROM @sql_add_invoices_association_year_id;
EXECUTE stmt_add_invoices_association_year_id;
DEALLOCATE PREPARE stmt_add_invoices_association_year_id;

SET @has_idx_invoices_year_date := (
    SELECT COUNT(*)
    FROM INFORMATION_SCHEMA.STATISTICS
    WHERE TABLE_SCHEMA = DATABASE()
      AND TABLE_NAME = 'invoices'
      AND INDEX_NAME = 'idx_invoices_year_date'
);

SET @sql_add_idx_invoices_year_date := IF(
    @has_idx_invoices_year_date = 0,
    'CREATE INDEX idx_invoices_year_date ON invoices (association_id, association_year_id, invoice_date)',
    'SELECT 1'
);
PREPARE stmt_add_idx_invoices_year_date FROM @sql_add_idx_invoices_year_date;
EXECUTE stmt_add_idx_invoices_year_date;
DEALLOCATE PREPARE stmt_add_idx_invoices_year_date;

SET @has_fk_invoices_year := (
    SELECT COUNT(*)
    FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
    WHERE TABLE_SCHEMA = DATABASE()
      AND TABLE_NAME = 'invoices'
      AND CONSTRAINT_TYPE = 'FOREIGN KEY'
      AND CONSTRAINT_NAME = 'fk_invoices_year'
);

SET @sql_add_fk_invoices_year := IF(
    @has_fk_invoices_year = 0,
    'ALTER TABLE invoices ADD CONSTRAINT fk_invoices_year FOREIGN KEY (association_year_id) REFERENCES association_years(id) ON DELETE SET NULL',
    'SELECT 1'
);
PREPARE stmt_add_fk_invoices_year FROM @sql_add_fk_invoices_year;
EXECUTE stmt_add_fk_invoices_year;
DEALLOCATE PREPARE stmt_add_fk_invoices_year;

-- Backfill association_year_id depuis invoice_date (idempotent)
SET @sql_backfill_invoices_year := IF(
    @has_invoices_association_year_id > 0,
    'UPDATE invoices i\n     INNER JOIN (\n        SELECT i2.id AS invoice_id, MAX(ay.start_date) AS start_date\n        FROM invoices i2\n        INNER JOIN association_years ay\n            ON ay.association_id = i2.association_id\n           AND i2.invoice_date >= ay.start_date\n           AND i2.invoice_date < DATE_ADD(ay.start_date, INTERVAL 1 YEAR)\n        WHERE i2.association_year_id IS NULL\n        GROUP BY i2.id\n     ) m ON m.invoice_id = i.id\n     INNER JOIN association_years ay2\n        ON ay2.association_id = i.association_id\n       AND ay2.start_date = m.start_date\n     SET i.association_year_id = ay2.id\n     WHERE i.association_year_id IS NULL',
    'SELECT 1'
);
PREPARE stmt_backfill_invoices_year FROM @sql_backfill_invoices_year;
EXECUTE stmt_backfill_invoices_year;
DEALLOCATE PREPARE stmt_backfill_invoices_year;

SET @has_invoices_year_membership_id := (
    SELECT COUNT(*)
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE()
      AND TABLE_NAME = 'invoices'
      AND COLUMN_NAME = 'year_membership_id'
);

-- Migration de l'ancienne liaison (year_membership_id) vers year_memberships.invoice_number
SET @sql_migrate_invoice_number_to_membership := IF(
    (@has_invoices_year_membership_id > 0) AND (@has_year_membership_invoice_number > 0),
    'UPDATE year_memberships ym\n     INNER JOIN association_years ay ON ay.id = ym.association_year_id\n     INNER JOIN invoices i\n        ON i.year_membership_id = ym.id\n       AND i.association_id = ay.association_id\n     SET ym.invoice_number = i.invoice_number,\n         ym.updated_at = CURRENT_TIMESTAMP\n     WHERE (ym.invoice_number IS NULL OR ym.invoice_number = '''')',
    'SELECT 1'
);
PREPARE stmt_migrate_invoice_number_to_membership FROM @sql_migrate_invoice_number_to_membership;
EXECUTE stmt_migrate_invoice_number_to_membership;
DEALLOCATE PREPARE stmt_migrate_invoice_number_to_membership;

-- Suppression de l'ancienne colonne/contraintes (idempotent)
SET @has_fk_invoices_year_membership := (
    SELECT COUNT(*)
    FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
    WHERE TABLE_SCHEMA = DATABASE()
      AND TABLE_NAME = 'invoices'
      AND CONSTRAINT_TYPE = 'FOREIGN KEY'
      AND CONSTRAINT_NAME = 'fk_invoices_year_membership'
);

SET @sql_drop_fk_invoices_year_membership := IF(
    @has_fk_invoices_year_membership > 0,
    'ALTER TABLE invoices DROP FOREIGN KEY fk_invoices_year_membership',
    'SELECT 1'
);
PREPARE stmt_drop_fk_invoices_year_membership FROM @sql_drop_fk_invoices_year_membership;
EXECUTE stmt_drop_fk_invoices_year_membership;
DEALLOCATE PREPARE stmt_drop_fk_invoices_year_membership;

SET @has_uq_invoices_year_membership := (
    SELECT COUNT(*)
    FROM INFORMATION_SCHEMA.STATISTICS
    WHERE TABLE_SCHEMA = DATABASE()
      AND TABLE_NAME = 'invoices'
      AND INDEX_NAME = 'uq_invoices_year_membership'
);

SET @sql_drop_uq_invoices_year_membership := IF(
    @has_uq_invoices_year_membership > 0,
    'ALTER TABLE invoices DROP INDEX uq_invoices_year_membership',
    'SELECT 1'
);
PREPARE stmt_drop_uq_invoices_year_membership FROM @sql_drop_uq_invoices_year_membership;
EXECUTE stmt_drop_uq_invoices_year_membership;
DEALLOCATE PREPARE stmt_drop_uq_invoices_year_membership;

SET @sql_drop_invoices_year_membership_id := IF(
    @has_invoices_year_membership_id > 0,
    'ALTER TABLE invoices DROP COLUMN year_membership_id',
    'SELECT 1'
);
PREPARE stmt_drop_invoices_year_membership_id FROM @sql_drop_invoices_year_membership_id;
EXECUTE stmt_drop_invoices_year_membership_id;
DEALLOCATE PREPARE stmt_drop_invoices_year_membership_id;

CREATE TABLE IF NOT EXISTS invoice_lines (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    invoice_id INT UNSIGNED NOT NULL,
    line_order INT UNSIGNED NOT NULL DEFAULT 1,
    article_code VARCHAR(80) NULL,
    label VARCHAR(255) NOT NULL,
    description_longue TEXT NULL,
    unit_price_ht DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    quantity DECIMAL(10,2) NOT NULL DEFAULT 1.00,
    tva_id INT UNSIGNED NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_invoice_lines_invoice
        FOREIGN KEY (invoice_id) REFERENCES invoices(id)
        ON DELETE CASCADE,
    CONSTRAINT fk_invoice_lines_tva
        FOREIGN KEY (tva_id) REFERENCES tva(id),
    INDEX idx_invoice_lines_invoice_order (invoice_id, line_order),
    INDEX idx_invoice_lines_tva (tva_id)
);

SET @has_invoice_lines_description_longue := (
    SELECT COUNT(*)
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE()
      AND TABLE_NAME = 'invoice_lines'
      AND COLUMN_NAME = 'description_longue'
);

SET @sql_add_invoice_lines_description_longue := IF(
    @has_invoice_lines_description_longue = 0,
    'ALTER TABLE invoice_lines ADD COLUMN description_longue TEXT NULL AFTER label',
    'SELECT 1'
);
PREPARE stmt_add_invoice_lines_description_longue FROM @sql_add_invoice_lines_description_longue;
EXECUTE stmt_add_invoice_lines_description_longue;
DEALLOCATE PREPARE stmt_add_invoice_lines_description_longue;

SET @has_invoice_lines_tva_id := (
    SELECT COUNT(*)
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE()
      AND TABLE_NAME = 'invoice_lines'
      AND COLUMN_NAME = 'tva_id'
);

SET @sql_add_invoice_lines_tva_id := IF(
    @has_invoice_lines_tva_id = 0,
    'ALTER TABLE invoice_lines ADD COLUMN tva_id INT UNSIGNED NULL AFTER quantity',
    'SELECT 1'
);
PREPARE stmt_add_invoice_lines_tva_id FROM @sql_add_invoice_lines_tva_id;
EXECUTE stmt_add_invoice_lines_tva_id;
DEALLOCATE PREPARE stmt_add_invoice_lines_tva_id;

UPDATE invoice_lines
SET tva_id = (SELECT id FROM tva WHERE is_default = 1 LIMIT 1)
WHERE tva_id IS NULL;

ALTER TABLE invoice_lines
    MODIFY tva_id INT UNSIGNED NOT NULL;

SET @has_invoice_lines_tva_index := (
    SELECT COUNT(*)
    FROM INFORMATION_SCHEMA.STATISTICS
    WHERE TABLE_SCHEMA = DATABASE()
      AND TABLE_NAME = 'invoice_lines'
      AND INDEX_NAME = 'idx_invoice_lines_tva'
);

SET @sql_add_invoice_lines_tva_index := IF(
    @has_invoice_lines_tva_index = 0,
    'ALTER TABLE invoice_lines ADD INDEX idx_invoice_lines_tva (tva_id)',
    'SELECT 1'
);
PREPARE stmt_add_invoice_lines_tva_index FROM @sql_add_invoice_lines_tva_index;
EXECUTE stmt_add_invoice_lines_tva_index;
DEALLOCATE PREPARE stmt_add_invoice_lines_tva_index;

SET @has_invoice_lines_tva_fk := (
    SELECT COUNT(*)
    FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
    WHERE TABLE_SCHEMA = DATABASE()
      AND TABLE_NAME = 'invoice_lines'
      AND CONSTRAINT_TYPE = 'FOREIGN KEY'
      AND CONSTRAINT_NAME = 'fk_invoice_lines_tva'
);

SET @sql_add_invoice_lines_tva_fk := IF(
    @has_invoice_lines_tva_fk = 0,
    'ALTER TABLE invoice_lines ADD CONSTRAINT fk_invoice_lines_tva FOREIGN KEY (tva_id) REFERENCES tva(id)',
    'SELECT 1'
);
PREPARE stmt_add_invoice_lines_tva_fk FROM @sql_add_invoice_lines_tva_fk;
EXECUTE stmt_add_invoice_lines_tva_fk;
DEALLOCATE PREPARE stmt_add_invoice_lines_tva_fk;

-- 8) Verifications post-correction.
SELECT TABLE_NAME, TABLE_COLLATION
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = DATABASE()
  AND TABLE_TYPE = 'BASE TABLE'
  AND (TABLE_COLLATION IS NULL OR TABLE_COLLATION <> 'utf8mb4_unicode_ci')
ORDER BY TABLE_NAME;

SELECT TABLE_NAME, COLUMN_NAME, COLLATION_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = DATABASE()
  AND COLLATION_NAME IS NOT NULL
  AND COLLATION_NAME <> 'utf8mb4_unicode_ci'
ORDER BY TABLE_NAME, ORDINAL_POSITION;

SELECT COUNT(*) AS permissions_count
FROM authorization_permissions
WHERE is_active = 1;
