-- Migration: Version20260211131917
-- Date: 2026-02-11 13:19:17
-- Description: Phase 1 - Database Schema for Dossier Feature

-- Create dossier table
CREATE TABLE dossier (
    id INT AUTO_INCREMENT NOT NULL,
    client_id INT NOT NULL,
    vehicule_id INT NOT NULL,
    assurance_id INT DEFAULT NULL,
    expert1_id INT DEFAULT NULL,
    expert2_id INT DEFAULT NULL,
    expert3_id INT DEFAULT NULL,
    magasin_id INT NOT NULL,
    utilisateur_id INT NOT NULL,
    num_increment INT NOT NULL,
    num_dossier VARCHAR(50) NOT NULL,
    date_dossier DATETIME NOT NULL,
    titre VARCHAR(255) NOT NULL,
    description LONGTEXT DEFAULT NULL,
    status VARCHAR(50) DEFAULT 'EN_PREPARATION' NOT NULL,
    priorite VARCHAR(20) DEFAULT 'NORMALE',
    date_ouverture DATETIME DEFAULT NULL,
    date_cloture DATETIME DEFAULT NULL,
    expert1_status VARCHAR(50) DEFAULT NULL,
    expert1_notes LONGTEXT DEFAULT NULL,
    expert2_status VARCHAR(50) DEFAULT NULL,
    expert2_notes LONGTEXT DEFAULT NULL,
    expert3_status VARCHAR(50) DEFAULT NULL,
    expert3_notes LONGTEXT DEFAULT NULL,
    expert_validation_finale TINYINT(1) DEFAULT 0 NOT NULL,
    montant_estime NUMERIC(10, 2) DEFAULT NULL,
    montant_final NUMERIC(10, 2) DEFAULT NULL,
    observations LONGTEXT DEFAULT NULL,
    date_time_creation DATETIME NOT NULL,
    date_time_modification DATETIME NOT NULL,
    INDEX IDX_3D48E037B288C3E3 (assurance_id),
    INDEX IDX_3D48E03771CE5D7E (expert1_id),
    INDEX IDX_3D48E037637BF290 (expert2_id),
    INDEX IDX_3D48E037DBC795F5 (expert3_id),
    INDEX IDX_3D48E03720096AE3 (magasin_id),
    INDEX IDX_3D48E037FB88E14F (utilisateur_id),
    INDEX idx_dossier_date (date_dossier),
    INDEX idx_dossier_status (status),
    INDEX idx_dossier_client (client_id),
    INDEX idx_dossier_vehicule (vehicule_id),
    UNIQUE INDEX unique_num_dossier (num_dossier),
    PRIMARY KEY(id)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE = InnoDB;

-- Create dossier_document table
CREATE TABLE dossier_document (
    id INT AUTO_INCREMENT NOT NULL,
    dossier_id INT NOT NULL,
    utilisateur_id INT NOT NULL,
    categorie VARCHAR(50) NOT NULL,
    nom VARCHAR(255) NOT NULL,
    chemin_fichier VARCHAR(500) NOT NULL,
    mime_type VARCHAR(100) DEFAULT NULL,
    taille INT DEFAULT NULL,
    description LONGTEXT DEFAULT NULL,
    date_upload DATETIME NOT NULL,
    INDEX IDX_F0296801FB88E14F (utilisateur_id),
    INDEX idx_dossier_document_dossier (dossier_id),
    INDEX idx_dossier_document_categorie (categorie),
    INDEX idx_dossier_document_date (date_upload),
    PRIMARY KEY(id)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE = InnoDB;

-- Add foreign keys for dossier table
ALTER TABLE dossier ADD CONSTRAINT FK_3D48E03719EB6921 FOREIGN KEY (client_id) REFERENCES client (id);
ALTER TABLE dossier ADD CONSTRAINT FK_3D48E0374A4A3511 FOREIGN KEY (vehicule_id) REFERENCES vehicule (id);
ALTER TABLE dossier ADD CONSTRAINT FK_3D48E037B288C3E3 FOREIGN KEY (assurance_id) REFERENCES organisme (id) ON DELETE SET NULL;
ALTER TABLE dossier ADD CONSTRAINT FK_3D48E03771CE5D7E FOREIGN KEY (expert1_id) REFERENCES organisme (id) ON DELETE SET NULL;
ALTER TABLE dossier ADD CONSTRAINT FK_3D48E037637BF290 FOREIGN KEY (expert2_id) REFERENCES organisme (id) ON DELETE SET NULL;
ALTER TABLE dossier ADD CONSTRAINT FK_3D48E037DBC795F5 FOREIGN KEY (expert3_id) REFERENCES organisme (id) ON DELETE SET NULL;
ALTER TABLE dossier ADD CONSTRAINT FK_3D48E03720096AE3 FOREIGN KEY (magasin_id) REFERENCES magasin (id);
ALTER TABLE dossier ADD CONSTRAINT FK_3D48E037FB88E14F FOREIGN KEY (utilisateur_id) REFERENCES utilisateur (id);

-- Add foreign keys for dossier_document table
ALTER TABLE dossier_document ADD CONSTRAINT FK_F0296801611C0C56 FOREIGN KEY (dossier_id) REFERENCES dossier (id) ON DELETE CASCADE;
ALTER TABLE dossier_document ADD CONSTRAINT FK_F0296801FB88E14F FOREIGN KEY (utilisateur_id) REFERENCES utilisateur (id);

-- Add dossier_id to achat table
ALTER TABLE achat ADD dossier_id INT DEFAULT NULL;
ALTER TABLE achat ADD CONSTRAINT FK_26A98456611C0C56 FOREIGN KEY (dossier_id) REFERENCES dossier (id) ON DELETE SET NULL;
CREATE INDEX IDX_26A98456611C0C56 ON achat (dossier_id);

-- Add dossier_id to commande table
ALTER TABLE commande ADD dossier_id INT DEFAULT NULL;
ALTER TABLE commande ADD CONSTRAINT FK_6EEAA67D611C0C56 FOREIGN KEY (dossier_id) REFERENCES dossier (id) ON DELETE SET NULL;
CREATE INDEX IDX_6EEAA67D611C0C56 ON commande (dossier_id);

-- Add dossier_id to devis table
ALTER TABLE devis ADD dossier_id INT DEFAULT NULL;
ALTER TABLE devis ADD CONSTRAINT FK_8B27C52B611C0C56 FOREIGN KEY (dossier_id) REFERENCES dossier (id) ON DELETE SET NULL;
CREATE INDEX IDX_8B27C52B611C0C56 ON devis (dossier_id);

-- Add organisme_type to organisme table
ALTER TABLE organisme ADD organisme_type VARCHAR(50) DEFAULT 'CREDIT' NOT NULL;

-- Update existing organisme records to have default type
UPDATE organisme SET organisme_type = 'CREDIT' WHERE organisme_type IS NULL OR organisme_type = '';

-- Add dossier_id to reparation table
ALTER TABLE reparation ADD dossier_id INT DEFAULT NULL;
ALTER TABLE reparation ADD CONSTRAINT FK_8FDF219D611C0C56 FOREIGN KEY (dossier_id) REFERENCES dossier (id) ON DELETE SET NULL;
CREATE INDEX IDX_8FDF219D611C0C56 ON reparation (dossier_id);

-- Add dossier_id to vente table
ALTER TABLE vente ADD dossier_id INT DEFAULT NULL;
ALTER TABLE vente ADD CONSTRAINT FK_888A2A4C611C0C56 FOREIGN KEY (dossier_id) REFERENCES dossier (id) ON DELETE SET NULL;
CREATE INDEX IDX_888A2A4C611C0C56 ON vente (dossier_id);

-- Fix produit_fournisseur foreign keys (part of migration)
ALTER TABLE produit_fournisseur DROP FOREIGN KEY fk_pf_fournisseur;
ALTER TABLE produit_fournisseur DROP FOREIGN KEY fk_pf_produit;
ALTER TABLE produit_fournisseur ADD CONSTRAINT FK_48868EB6F347EFB FOREIGN KEY (produit_id) REFERENCES produit (id);
ALTER TABLE produit_fournisseur ADD CONSTRAINT FK_48868EB6670C757F FOREIGN KEY (fournisseur_id) REFERENCES fournisseur (id);
ALTER TABLE produit_fournisseur RENAME INDEX idx_pf_produit TO IDX_48868EB6F347EFB;
ALTER TABLE produit_fournisseur RENAME INDEX idx_pf_fournisseur TO IDX_48868EB6670C757F;
