-- ==============================================================
-- app_portage.sql – Schéma adapté : uniquement les tables dont
-- nous avons besoin pour le flux « rélevé bancaire seulement ».
-- ==============================================================

IF DB_ID('RapprochementBIAT') IS NULL
    CREATE DATABASE RapprochementBIAT;
GO

USE RapprochementBIAT;
GO

/* --------------------------------------------------------------
   1. Table Banques – inchangée
   -------------------------------------------------------------- */
IF OBJECT_ID('dbo.Banques','U') IS NULL
CREATE TABLE dbo.Banques (
    bank_id        NVARCHAR(50)  NOT NULL PRIMARY KEY,
    nom            NVARCHAR(100) NOT NULL,
    compte         NVARCHAR(100) NULL,
    excel_path     NVARCHAR(500) NULL,                -- **conservé uniquement pour écriture**
    feuille_gl     NVARCHAR(50)  DEFAULT 'GL',
    feuille_bq     NVARCHAR(50)  DEFAULT 'Relevé',
    feuille_export NVARCHAR(50)  DEFAULT 'DataExport',
    actif          BIT           DEFAULT 1,
    created_at     DATETIME      DEFAULT GETDATE(),
    updated_at     DATETIME      DEFAULT GETDATE()
);
GO

/* --------------------------------------------------------------
   2. Table ReleveBanque – inchangée
   -------------------------------------------------------------- */
IF OBJECT_ID('dbo.ReleveBanque','U') IS NULL
CREATE TABLE dbo.ReleveBanque (
    id              INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
    bank_id         NVARCHAR(50)   NOT NULL FOREIGN KEY REFERENCES dbo.Banques(bank_id),
    type_operation  NVARCHAR(100)  NULL,
    montant         DECIMAL(18,3)  NULL,
    date_operation  DATE           NULL,
    reference       NVARCHAR(100)  NULL,
    libelle         NVARCHAR(500)  NULL,
    rang            INT            NULL,
    cle_rapprochement NVARCHAR(100) NULL,
    periode         NVARCHAR(10)   NULL,
    import_batch    NVARCHAR(50)   NULL,
    created_at      DATETIME       DEFAULT GETDATE(),
    INDEX IX_RB_bank_periode (bank_id, periode),
    INDEX IX_RB_cle        (cle_rapprochement),
    INDEX IX_RB_date       (date_operation)
);
GO

/* --------------------------------------------------------------
   3. Table EcrituresReconciliation – légère modification
   -------------------------------------------------------------- */
IF OBJECT_ID('dbo.EcrituresReconciliation','U') IS NULL
CREATE TABLE dbo.EcrituresReconciliation (
    id              INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
    bank_id         NVARCHAR(50)   NOT NULL FOREIGN KEY REFERENCES dbo.Banques(bank_id),
    type_gl         NVARCHAR(100)  NULL,
    montant_gl      DECIMAL(18,3)  NULL,
    date_gl         DATE           NULL,
    num_gl          NVARCHAR(100)  NULL,
    des_gl          NVARCHAR(500)  NULL,
    cle_gl          NVARCHAR(100)  NULL,
    date_bq         DATE           NULL,
    libelle_bq      NVARCHAR(500)  NULL,
    montant_bq      DECIMAL(18,3)  NULL,
    cle_bq          NVARCHAR(100)  NULL,
    etat            NVARCHAR(50)   DEFAULT 'En attente Banque',
    ecart           DECIMAL(18,3)  DEFAULT 0,
    source          NVARCHAR(50)   DEFAULT 'Auto',
    is_manual       BIT            DEFAULT 0,
    manual_type     NVARCHAR(20)   NULL,
    groupe_rapprochement NVARCHAR(100) NULL,
    import_batch    NVARCHAR(50)   NULL,
    created_at      DATETIME       DEFAULT GETDATE(),
    updated_at      DATETIME       DEFAULT GETDATE(),
    INDEX IX_ER_bank_etat       (bank_id, etat),
    INDEX IX_ER_cle_gl          (cle_gl),
    INDEX IX_ER_cle_bq          (cle_bq)
);
GO

/* --------------------------------------------------------------
   4. Table Soldes – inchangée
   -------------------------------------------------------------- */
IF OBJECT_ID('dbo.Soldes','U') IS NULL
CREATE TABLE dbo.Soldes (
    bank_id         NVARCHAR(50) NOT NULL FOREIGN KEY REFERENCES dbo.Banques(bank_id),
    solde_gl        DECIMAL(18,3) DEFAULT 0,
    solde_bq        DECIMAL(18,3) DEFAULT 0,
    periode_gl      NVARCHAR(10)  NULL,
    periode_bq      NVARCHAR(10)  NULL,
    updated_at      DATETIME      DEFAULT GETDATE(),
    PRIMARY KEY (bank_id)
);
GO

/* --------------------------------------------------------------
   5. Table ImportBatch – pour tracer les lots d’import
   -------------------------------------------------------------- */
IF OBJECT_ID('dbo.ImportBatch','U') IS NULL
CREATE TABLE dbo.ImportBatch (
    batch_id        NVARCHAR(50) NOT NULL PRIMARY KEY,
    bank_id         NVARCHAR(50) NOT NULL FOREIGN KEY REFERENCES dbo.Banques(bank_id),
    type_import     NVARCHAR(20) NOT NULL,          -- 'RELEVE', 'GL', etc.
    fichier_source  NVARCHAR(500) NULL,
    nb_lignes       INT           DEFAULT 0,
    status          NVARCHAR(20)  DEFAULT 'OK',
    message         NVARCHAR(MAX) NULL,
    created_at      DATETIME      DEFAULT GETDATE()
);
GO

/* --------------------------------------------------------------
   6. (Optionnel) Vue pour exposer les relevés de façon simple
   -------------------------------------------------------------- */
IF OBJECT_ID('dbo.vw_Releves', 'V') IS NOT NULL DROP VIEW dbo.vw_Releves;
GO
CREATE VIEW dbo.vw_Releves AS
SELECT  r.bank_id,
        r.type_operation,
        r.montant,
        r.date_operation,
        r.reference,
        r.libelle,
        r.rang,
        r.cle_rapprochement,
        r.periode
FROM dbo.ReleveBanque r;
GO

PRINT 'Schéma adapté appliqué avec succès.';
GO