CREATE TABLE lgpd_requests (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    protocol VARCHAR(30) NOT NULL UNIQUE,
    request_type ENUM('access','rectification','portability','deletion') NOT NULL,
    status ENUM('pending','approved','rejected','executed','cancelled') NOT NULL DEFAULT 'pending',
    requester_name VARCHAR(150) NOT NULL,
    requester_email VARCHAR(150) NOT NULL,
    requester_cpf VARCHAR(20) NULL,
    requester_phone VARCHAR(30) NULL,
    request_reason TEXT NOT NULL,
    requested_scope TEXT NULL,
    data_snapshot LONGTEXT NULL,
    due_at DATETIME NULL,
    approved_by INT UNSIGNED NULL,
    approved_at DATETIME NULL,
    rejected_by INT UNSIGNED NULL,
    rejected_at DATETIME NULL,
    rejection_reason TEXT NULL,
    executed_by INT UNSIGNED NULL,
    executed_at DATETIME NULL,
    execution_notes TEXT NULL,
    request_ip VARCHAR(45) NULL,
    user_agent VARCHAR(255) NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_lgpd_requests_approved_by FOREIGN KEY (approved_by) REFERENCES users (id) ON DELETE SET NULL,
    CONSTRAINT fk_lgpd_requests_rejected_by FOREIGN KEY (rejected_by) REFERENCES users (id) ON DELETE SET NULL,
    CONSTRAINT fk_lgpd_requests_executed_by FOREIGN KEY (executed_by) REFERENCES users (id) ON DELETE SET NULL,
    INDEX idx_lgpd_requests_status (status),
    INDEX idx_lgpd_requests_type (request_type),
    INDEX idx_lgpd_requests_email (requester_email),
    INDEX idx_lgpd_requests_cpf (requester_cpf)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE lgpd_request_logs (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    request_id INT UNSIGNED NOT NULL,
    action VARCHAR(50) NOT NULL,
    description TEXT NOT NULL,
    actor_type ENUM('user','admin','system') NOT NULL DEFAULT 'system',
    actor_id INT UNSIGNED NULL,
    ip_address VARCHAR(45) NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_lgpd_logs_request FOREIGN KEY (request_id) REFERENCES lgpd_requests (id) ON DELETE CASCADE,
    CONSTRAINT fk_lgpd_logs_actor FOREIGN KEY (actor_id) REFERENCES users (id) ON DELETE SET NULL,
    INDEX idx_lgpd_logs_request (request_id),
    INDEX idx_lgpd_logs_action (action)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;