-- Adicionar tabela de permissões de coleções por equipe
USE webreq_db;

-- Tabela de Permissões de Coleções por Equipe
CREATE TABLE IF NOT EXISTS collection_team_permissions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    collection_id INT NOT NULL,
    team_id INT NOT NULL,
    permission_type ENUM('read', 'write', 'admin') NOT NULL DEFAULT 'read',
    granted_by INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (collection_id) REFERENCES collections(id) ON DELETE CASCADE,
    FOREIGN KEY (team_id) REFERENCES teams(id) ON DELETE CASCADE,
    FOREIGN KEY (granted_by) REFERENCES users(id) ON DELETE SET NULL,
    UNIQUE KEY unique_collection_team (collection_id, team_id),
    INDEX idx_collection_id (collection_id),
    INDEX idx_team_id (team_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;