-- Tabelas para Assinatura Digital e Histórico de Alterações

-- Tabela para armazenar os links de assinatura
CREATE TABLE IF NOT EXISTS contract_signatures (
    id INT AUTO_INCREMENT PRIMARY KEY,
    contract_id INT NOT NULL,
    token VARCHAR(255) NOT NULL UNIQUE,
    is_signed BOOLEAN DEFAULT FALSE,
    signed_at DATETIME,
    signer_ip VARCHAR(45),
    signer_name VARCHAR(255),
    signer_email VARCHAR(255),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (contract_id) REFERENCES contracts(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Tabela para o histórico de alterações (log)
CREATE TABLE IF NOT EXISTS contract_history (
    id INT AUTO_INCREMENT PRIMARY KEY,
    contract_id INT NOT NULL,
    user_id INT,
    username VARCHAR(255),
    action VARCHAR(255) NOT NULL, -- Ex: 'create', 'update', 'sign'
    details TEXT, -- JSON com os dados alterados (antes e depois)
    changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (contract_id) REFERENCES contracts(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
