-- =====================================================================
--  Migration financière normalisée (Phase 1)
--  Date: 2025-08-26
--  Objectif: Introduire schéma double-écriture + structures support
--            sans casser l'existant (bank, charges, etc.).
--  Stratégie: Ajout de nouvelles tables; aucune suppression.
--  Préfixe copropriété: toutes les tables ont id_copro pour isolation.
--  Montants: stockés en centimes (BIGINT) pour éviter flottants.
--  Conventions: debit/cREDIT vus du point de vue du compte (positif = débit).
-- =====================================================================

-- 1. PÉRIODES COMPTABLES ------------------------------------------------
CREATE TABLE IF NOT EXISTS accounting_periods (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    id_copro INT NOT NULL,
    code VARCHAR(16) NOT NULL,           -- ex: 2025-01, 2025
    date_start DATE NOT NULL,
    date_end DATE NOT NULL,
    status ENUM('open','locked','archived') NOT NULL DEFAULT 'open',
    locked_at DATETIME NULL,
    locked_by BIGINT NULL,
    UNIQUE KEY uniq_period (id_copro, code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 2. PLAN COMPTABLE -----------------------------------------------------
CREATE TABLE IF NOT EXISTS accounts (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    id_copro INT NOT NULL,
    code VARCHAR(32) NOT NULL,           -- 512, 401, 706...
    name VARCHAR(255) NOT NULL,
    type ENUM('asset','liability','equity','income','expense') NOT NULL,
    parent_id BIGINT NULL,
    is_leaf TINYINT(1) NOT NULL DEFAULT 1,
    active TINYINT(1) NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uniq_account (id_copro, code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 3. JOURNAL (HEADER) ---------------------------------------------------
CREATE TABLE IF NOT EXISTS journal_entries (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    id_copro INT NOT NULL,
    period_id BIGINT NULL,
    ref VARCHAR(50) NULL,                -- numéro interne / pièce
    date DATE NOT NULL,
    description VARCHAR(255) NOT NULL,
    status ENUM('draft','posted','locked') NOT NULL DEFAULT 'draft',
    posted_at DATETIME NULL,
    posted_by BIGINT NULL,
    locked_at DATETIME NULL,
    locked_by BIGINT NULL,
    source VARCHAR(50) NULL,             -- bank_import, manuel, recurrent, api...
    hash VARCHAR(64) NULL,               -- hash de contrôle (intégrité)
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_journal_copro_date (id_copro, date),
    INDEX idx_journal_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 4. JOURNAL LIGNES -----------------------------------------------------
CREATE TABLE IF NOT EXISTS journal_lines (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    entry_id BIGINT NOT NULL,
    id_copro INT NOT NULL,
    account_id BIGINT NOT NULL,
    label VARCHAR(255) NOT NULL,
    debit_cents BIGINT NOT NULL DEFAULT 0,
    credit_cents BIGINT NOT NULL DEFAULT 0,
    tax_code VARCHAR(20) NULL,
    meta JSON NULL,
    INDEX idx_lines_entry (entry_id),
    INDEX idx_lines_account (account_id),
    INDEX idx_lines_copro_account (id_copro, account_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 5. SOLDES (CACHE INCRÉMENTAL) ----------------------------------------
CREATE TABLE IF NOT EXISTS account_balances (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    id_copro INT NOT NULL,
    account_id BIGINT NOT NULL,
    period_code VARCHAR(16) NOT NULL, -- même format que accounting_periods.code
    debit_cents BIGINT NOT NULL DEFAULT 0,
    credit_cents BIGINT NOT NULL DEFAULT 0,
    last_recalc_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uniq_balance (id_copro, account_id, period_code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 6. RAPPROCHEMENT BANCAIRE --------------------------------------------
CREATE TABLE IF NOT EXISTS reconciliation_batches (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    id_copro INT NOT NULL,
    bank_account_id BIGINT NOT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    status ENUM('open','closed') NOT NULL DEFAULT 'open'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS reconciliation_matches (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    batch_id BIGINT NOT NULL,
    id_copro INT NOT NULL,
    journal_entry_id BIGINT NULL,
    bank_row_id BIGINT NULL,            -- référence ligne importée banque (table existante ou future import)
    confidence TINYINT NULL,            -- 0-100
    matched_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 7. RÈGLES (CLASSIFICATION) -------------------------------------------
CREATE TABLE IF NOT EXISTS classification_rules (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    id_copro INT NOT NULL,
    pattern VARCHAR(255) NOT NULL,       -- REGEXP ou LIKE transformé
    account_id BIGINT NULL,
    tax_code VARCHAR(20) NULL,
    auto_post TINYINT(1) NOT NULL DEFAULT 0,
    priority INT NOT NULL DEFAULT 100,
    active TINYINT(1) NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 8. TEMPLATES RÉCURRENTS ----------------------------------------------
CREATE TABLE IF NOT EXISTS recurring_templates (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    id_copro INT NOT NULL,
    name VARCHAR(100) NOT NULL,
    cron_expr VARCHAR(64) NOT NULL,      -- ex: '0 0 1 * *'
    payload JSON NOT NULL,               -- structure lignes proposées
    last_run_at DATETIME NULL,
    next_run_at DATETIME NULL,
    active TINYINT(1) NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 9. PIÈCES / FICHIERS --------------------------------------------------
CREATE TABLE IF NOT EXISTS attachments (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    id_copro INT NOT NULL,
    entry_id BIGINT NULL,                -- rattachement journal
    line_id BIGINT NULL,                 -- optionnel ligne
    file_name VARCHAR(255) NOT NULL,
    file_orig VARCHAR(255) NOT NULL,
    mime_type VARCHAR(120) NOT NULL,
    size_bytes BIGINT NOT NULL,
    sha256 CHAR(64) NULL,
    uploaded_by BIGINT NULL,
    uploaded_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_attach_entry (entry_id),
    INDEX idx_attach_copro (id_copro)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 10. AUDIT LOG ---------------------------------------------------------
CREATE TABLE IF NOT EXISTS audit_log (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    id_copro INT NOT NULL,
    actor_id BIGINT NULL,
    entity_type VARCHAR(40) NOT NULL,    -- journal_entry, account, rule...
    entity_id BIGINT NULL,
    action VARCHAR(30) NOT NULL,         -- create, update, post, lock...
    before_json JSON NULL,
    after_json JSON NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_audit_entity (id_copro, entity_type, entity_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 11. INDEXES COMPLÉMENTAIRES (OPTIONNEL SELON VOLUME) -----------------
-- NOTE: MySQL < 8.0.21 ne supporte pas "ADD INDEX IF NOT EXISTS".
-- Bloc idempotent: crée l'index seulement s'il est absent.
SET @idx := (SELECT COUNT(1) FROM information_schema.statistics
                         WHERE table_schema = DATABASE()
                             AND table_name = 'journal_entries'
                             AND index_name = 'idx_journal_copro_status');
SET @sql := IF(@idx = 0,
                            'ALTER TABLE journal_entries ADD INDEX idx_journal_copro_status (id_copro, status)',
                            'SELECT 1');
PREPARE st FROM @sql; EXECUTE st; DEALLOCATE PREPARE st;

SET @idx2 := (SELECT COUNT(1) FROM information_schema.statistics
                            WHERE table_schema = DATABASE()
                                AND table_name = 'journal_lines'
                                AND index_name = 'idx_lines_copro_account_entry');
SET @sql2 := IF(@idx2 = 0,
                             'ALTER TABLE journal_lines ADD INDEX idx_lines_copro_account_entry (id_copro, account_id, entry_id)',
                             'SELECT 1');
PREPARE st2 FROM @sql2; EXECUTE st2; DEALLOCATE PREPARE st2;

-- =====================================================================
--  FIN MIGRATION PHASE 1
-- =====================================================================
