-- Mettre à jour la table expenses pour que payment_date puisse accepter des valeurs NULL
PRAGMA foreign_keys=off;
BEGIN TRANSACTION;

-- Créer une table temporaire avec la même structure mais sans la contrainte NOT NULL
CREATE TABLE expenses_temp (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    amount DECIMAL(10, 2) NOT NULL,
    category VARCHAR(50) NOT NULL,
    description TEXT NOT NULL,
    payment_method VARCHAR(50) NOT NULL,
    receipt_number VARCHAR(100),
    receipt_file VARCHAR(255),
    payment_date DATE,  -- Suppression de la contrainte NOT NULL
    date DATE,
    notes TEXT,
    created_by INTEGER,
    created_at DATETIME,
    updated_by INTEGER,
    updated_at DATETIME,
    FOREIGN KEY (created_by) REFERENCES users(id),
    FOREIGN KEY (updated_by) REFERENCES users(id)
);

-- Copier les données de l'ancienne table vers la nouvelle
INSERT INTO expenses_temp 
SELECT 
    id, amount, category, description, payment_method, receipt_number, 
    receipt_file, payment_date, date, notes, created_by, created_at, 
    updated_by, updated_at 
FROM expenses;

-- Supprimer l'ancienne table
DROP TABLE expenses;

-- Renommer la table temporaire
ALTER TABLE expenses_temp RENAME TO expenses;

-- Recréer les index
CREATE INDEX IF NOT EXISTS idx_expenses_payment_date ON expenses(payment_date);
CREATE INDEX IF NOT EXISTS idx_expenses_category ON expenses(category);

COMMIT;
PRAGMA foreign_keys=on;
