-- Tabela de Fornecedores
CREATE TABLE IF NOT EXISTS suppliers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    tax_id VARCHAR(20), -- CNPJ or CPF
    contact_person VARCHAR(100),
    email VARCHAR(255),
    phone VARCHAR(20),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Adicionar coluna supplier_id na tabela contracts
ALTER TABLE contracts ADD COLUMN supplier_id INT AFTER contract_number;
ALTER TABLE contracts ADD CONSTRAINT fk_supplier FOREIGN KEY (supplier_id) REFERENCES suppliers(id) ON DELETE SET NULL;

-- Migrar fornecedores existentes (nomes) para a nova tabela
INSERT INTO suppliers (name) 
SELECT DISTINCT supplier FROM contracts WHERE supplier IS NOT NULL AND supplier != '';

-- Atualizar supplier_id nos contratos existentes
UPDATE contracts c 
JOIN suppliers s ON c.supplier COLLATE utf8mb4_general_ci = s.name COLLATE utf8mb4_general_ci 
SET c.supplier_id = s.id;
