-- Tabela para armazenar o histórico de arquivos de cada contrato
CREATE TABLE IF NOT EXISTS contract_files (
    id INT AUTO_INCREMENT PRIMARY KEY,
    contract_id INT NOT NULL,
    file_path VARCHAR(255) NOT NULL,
    uploaded_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (contract_id) REFERENCES contracts(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Migrar os arquivos existentes da tabela `contracts` para a nova tabela `contract_files`
INSERT INTO contract_files (contract_id, file_path, uploaded_at)
SELECT id, file_path, created_at
FROM contracts
WHERE file_path IS NOT NULL AND file_path != '';
