-- OVH / Hébergement mutualisé: réparation minimale de la structure Facturation
-- Objectif: ajouter les colonnes manquantes utilisées par la facturation
-- (invoices.settled_date, invoice_lines.description_longue, invoice_lines.tva_id, etc.)
--
-- IMPORTANT:
-- - Exécuter ce script en étant POSITIONNÉ sur la base utilisée par l'application.
--   (Dans phpMyAdmin: sélectionne la base, puis onglet SQL)
-- - Ce script n'exécute PAS de CREATE DATABASE / USE et n'essaie PAS de ALTER DATABASE.

SET @db := DATABASE();
SELECT CONCAT('DB=', IFNULL(@db, 'NULL')) AS context_check;

-- 0) TVA: table + seed (si déjà présent, ne casse pas)
CREATE TABLE IF NOT EXISTS tva (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    taux DECIMAL(5,2) NOT NULL,
    is_default TINYINT(1) NOT NULL DEFAULT 0,
    label VARCHAR(32) NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT uq_tva_taux UNIQUE (taux)
);

INSERT INTO tva (taux, is_default, label)
VALUES
    (0.00, 0, 'TVA 0%'),
    (5.50, 0, 'TVA 5.5%'),
    (10.00, 0, 'TVA 10%'),
    (20.00, 1, 'TVA 20%')
ON DUPLICATE KEY UPDATE
    label = VALUES(label),
    is_default = VALUES(is_default);

SET @default_tva_id := (SELECT id FROM tva WHERE is_default = 1 ORDER BY id ASC LIMIT 1);
SET @default_tva_id := IFNULL(@default_tva_id, (SELECT MIN(id) FROM tva));
SELECT CONCAT('default_tva_id=', IFNULL(@default_tva_id, 'NULL')) AS tva_check;

-- 1) invoices.settled_date
SET @has_invoices_settled_date := (
    SELECT COUNT(*)
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE()
      AND TABLE_NAME = 'invoices'
      AND COLUMN_NAME = 'settled_date'
);
SET @sql_add_invoices_settled_date := IF(
    @has_invoices_settled_date = 0,
    'ALTER TABLE invoices ADD COLUMN settled_date DATE NULL AFTER settled_amount',
    'SELECT 1'
);
PREPARE stmt_add_invoices_settled_date FROM @sql_add_invoices_settled_date;
EXECUTE stmt_add_invoices_settled_date;
DEALLOCATE PREPARE stmt_add_invoices_settled_date;

-- 1b) invoices.payment_method (mode de règlement)
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;

-- 1c) invoices.association_year_id (rattachement à l'année via invoice_date)
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;

-- Index (accélère le filtre par année)
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;

-- FK (optionnel, si possible)
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
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;

-- 2) invoice_lines.description_longue
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;

-- 3) invoice_lines.tva_id (la facturation l'utilise au moment de l'INSERT)
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;

-- 4) Remplir tva_id si des anciennes lignes existent (évite soucis FK/index ensuite)
SET @has_invoice_lines_tva_id_after := (
    SELECT COUNT(*)
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE()
      AND TABLE_NAME = 'invoice_lines'
      AND COLUMN_NAME = 'tva_id'
);
SET @sql_backfill_invoice_lines_tva := IF(
    @has_invoice_lines_tva_id_after > 0 AND @default_tva_id IS NOT NULL,
    CONCAT('UPDATE invoice_lines SET tva_id = ', @default_tva_id, ' WHERE tva_id IS NULL OR tva_id = 0'),
    'SELECT 1'
);
PREPARE stmt_backfill_invoice_lines_tva FROM @sql_backfill_invoice_lines_tva;
EXECUTE stmt_backfill_invoice_lines_tva;
DEALLOCATE PREPARE stmt_backfill_invoice_lines_tva;

-- 5) Index invoice_lines.tva_id
SET @has_idx_invoice_lines_tva := (
    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_idx_invoice_lines_tva := IF(
    @has_idx_invoice_lines_tva = 0,
    'ALTER TABLE invoice_lines ADD INDEX idx_invoice_lines_tva (tva_id)',
    'SELECT 1'
);
PREPARE stmt_add_idx_invoice_lines_tva FROM @sql_add_idx_invoice_lines_tva;
EXECUTE stmt_add_idx_invoice_lines_tva;
DEALLOCATE PREPARE stmt_add_idx_invoice_lines_tva;

-- 6) FK invoice_lines -> tva (optionnel; si ça échoue sur OVH, tu peux ignorer cette étape)
SET @has_fk_invoice_lines_tva := (
    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_fk_invoice_lines_tva := IF(
    @has_fk_invoice_lines_tva = 0,
    'ALTER TABLE invoice_lines ADD CONSTRAINT fk_invoice_lines_tva FOREIGN KEY (tva_id) REFERENCES tva(id)',
    'SELECT 1'
);
PREPARE stmt_add_fk_invoice_lines_tva FROM @sql_add_fk_invoice_lines_tva;
EXECUTE stmt_add_fk_invoice_lines_tva;
DEALLOCATE PREPARE stmt_add_fk_invoice_lines_tva;

-- 7) year_memberships.invoice_number (utilisé pour lier l'adhésion à la facture)
SET @has_year_memberships_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_memberships_invoice_number := IF(
    @has_year_memberships_invoice_number = 0,
    'ALTER TABLE year_memberships ADD COLUMN invoice_number VARCHAR(64) NULL AFTER membership_paid_at',
    'SELECT 1'
);
PREPARE stmt_add_year_memberships_invoice_number FROM @sql_add_year_memberships_invoice_number;
EXECUTE stmt_add_year_memberships_invoice_number;
DEALLOCATE PREPARE stmt_add_year_memberships_invoice_number;

-- 8) associations.invoice_footer_legal_text (mention légale en pied de facture)
SET @has_association_invoice_footer_legal_text := (
    SELECT COUNT(*)
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE()
      AND TABLE_NAME = 'associations'
      AND COLUMN_NAME = 'invoice_footer_legal_text'
);
SET @sql_add_association_invoice_footer_legal_text := IF(
    @has_association_invoice_footer_legal_text = 0,
    'ALTER TABLE associations ADD COLUMN invoice_footer_legal_text TEXT NULL AFTER logo_path',
    'SELECT 1'
);
PREPARE stmt_add_association_invoice_footer_legal_text FROM @sql_add_association_invoice_footer_legal_text;
EXECUTE stmt_add_association_invoice_footer_legal_text;
DEALLOCATE PREPARE stmt_add_association_invoice_footer_legal_text;

-- 9) associations.default_treasury_account_id (compte bancaire par défaut pour afficher l'IBAN sur les factures)
SET @has_association_default_treasury_account_id := (
    SELECT COUNT(*)
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE()
      AND TABLE_NAME = 'associations'
      AND COLUMN_NAME = 'default_treasury_account_id'
);
SET @sql_add_association_default_treasury_account_id := IF(
    @has_association_default_treasury_account_id = 0,
    'ALTER TABLE associations ADD COLUMN default_treasury_account_id INT UNSIGNED NULL AFTER invoice_footer_legal_text',
    'SELECT 1'
);
PREPARE stmt_add_association_default_treasury_account_id FROM @sql_add_association_default_treasury_account_id;
EXECUTE stmt_add_association_default_treasury_account_id;
DEALLOCATE PREPARE stmt_add_association_default_treasury_account_id;

-- 10) associations.default_tva_id (taux de TVA par défaut pour les nouvelles factures)
SET @has_association_default_tva_id := (
    SELECT COUNT(*)
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE()
      AND TABLE_NAME = 'associations'
      AND COLUMN_NAME = 'default_tva_id'
);
SET @sql_add_association_default_tva_id := IF(
    @has_association_default_tva_id = 0,
    'ALTER TABLE associations ADD COLUMN default_tva_id INT UNSIGNED NULL AFTER default_treasury_account_id',
    'SELECT 1'
);
PREPARE stmt_add_association_default_tva_id FROM @sql_add_association_default_tva_id;
EXECUTE stmt_add_association_default_tva_id;
DEALLOCATE PREPARE stmt_add_association_default_tva_id;

-- Vérification rapide
SHOW COLUMNS FROM invoices LIKE 'settled_date';
SHOW COLUMNS FROM invoices LIKE 'payment_method';
SHOW COLUMNS FROM invoice_lines LIKE 'description_longue';
SHOW COLUMNS FROM invoice_lines LIKE 'tva_id';
SHOW COLUMNS FROM year_memberships LIKE 'invoice_number';
SHOW COLUMNS FROM associations LIKE 'invoice_footer_legal_text';
SHOW COLUMNS FROM associations LIKE 'default_treasury_account_id';
SHOW COLUMNS FROM associations LIKE 'default_tva_id';
SELECT COUNT(*) AS tva_count FROM tva;
