-- Phoenix P1.2 - DocumentManager moteur unique
-- Compatible MariaDB : ajoute les colonnes uniquement si elles manquent.

CREATE TABLE IF NOT EXISTS documents (
    id INT AUTO_INCREMENT PRIMARY KEY,
    type_document VARCHAR(20) NOT NULL,
    parent_document_id INT NULL,
    source_table VARCHAR(60) NULL,
    source_id INT NULL,
    client_id INT NOT NULL,
    numero VARCHAR(30) NOT NULL,
    date_document DATE NOT NULL,
    date_echeance DATE NULL,
    statut VARCHAR(40) DEFAULT 'brouillon',
    total_ht DECIMAL(12,2) DEFAULT 0,
    total_tva DECIMAL(12,2) DEFAULT 0,
    total_ttc DECIMAL(12,2) DEFAULT 0,
    montant_regle DECIMAL(12,2) DEFAULT 0,
    reste_du DECIMAL(12,2) DEFAULT 0,
    marge_ht DECIMAL(12,2) DEFAULT 0,
    marge_pourcentage DECIMAL(6,2) DEFAULT 0,
    observations TEXT,
    actif TINYINT(1) DEFAULT 1,
    date_creation DATETIME DEFAULT CURRENT_TIMESTAMP,
    date_modification DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_documents_type (type_document),
    INDEX idx_documents_numero (numero),
    INDEX idx_documents_client (client_id),
    INDEX idx_documents_date (date_document),
    INDEX idx_documents_statut (statut)
);

CREATE TABLE IF NOT EXISTS documents_lignes (
    id INT AUTO_INCREMENT PRIMARY KEY,
    document_id INT NOT NULL,
    source_table VARCHAR(60) NULL,
    source_id INT NULL,
    article_id INT NULL,
    reference VARCHAR(50),
    designation VARCHAR(255) NOT NULL,
    unite VARCHAR(30) DEFAULT 'unité',
    quantite DECIMAL(12,2) DEFAULT 1,
    prix_achat_ht DECIMAL(12,2) DEFAULT 0,
    prix_vente_ht DECIMAL(12,2) DEFAULT 0,
    remise DECIMAL(6,2) DEFAULT 0,
    taux_tva DECIMAL(5,2) DEFAULT 20,
    total_ht DECIMAL(12,2) DEFAULT 0,
    total_tva DECIMAL(12,2) DEFAULT 0,
    total_ttc DECIMAL(12,2) DEFAULT 0,
    marge_ht DECIMAL(12,2) DEFAULT 0,
    marge_pourcentage DECIMAL(6,2) DEFAULT 0,
    ordre INT DEFAULT 1,
    INDEX idx_documents_lignes_document (document_id),
    INDEX idx_documents_lignes_article (article_id)
);

SET @add_parent := (
    SELECT IF(
        COUNT(*) = 0,
        'ALTER TABLE documents ADD COLUMN parent_document_id INT NULL AFTER type_document',
        'SELECT 1'
    )
    FROM information_schema.columns
    WHERE table_schema = DATABASE()
      AND table_name = 'documents'
      AND column_name = 'parent_document_id'
);
PREPARE stmt FROM @add_parent;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @add_parent_idx := (
    SELECT IF(
        COUNT(*) = 0,
        'ALTER TABLE documents ADD INDEX idx_documents_parent (parent_document_id)',
        'SELECT 1'
    )
    FROM information_schema.statistics
    WHERE table_schema = DATABASE()
      AND table_name = 'documents'
      AND index_name = 'idx_documents_parent'
);
PREPARE stmt FROM @add_parent_idx;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Les contraintes FK peuvent déjà exister selon les versions ; on garde cette migration volontairement prudente.