-- Script SQL para criar tabela de Material Publicitário
-- Execute este script no seu banco de dados MySQL

USE gabinete;

-- Criar tabela para material publicitário
CREATE TABLE IF NOT EXISTS material_publicitario (
    id INT AUTO_INCREMENT PRIMARY KEY,
    id_usuario INT NOT NULL,
    titulo VARCHAR(255) NOT NULL,
    descricao TEXT,
    nome_arquivo VARCHAR(255) NOT NULL,
    caminho_arquivo VARCHAR(500) NOT NULL,
    tipo_arquivo VARCHAR(100) NOT NULL,
    tamanho_arquivo BIGINT NOT NULL,
    
    -- Campos específicos para material publicitário
    categoria ENUM(
        'Banner Digital',
        'Cartaz Impresso', 
        'Flyer',
        'Post Redes Sociais',
        'Vídeo Promocional',
        'Spot Rádio',
        'Material TV',
        'Outdoor',
        'Busdoor',
        'Adesivo',
        'Santinho',
        'Folder',
        'Panfleto',
        'Outros'
    ) NOT NULL DEFAULT 'Outros',
    
    tipo_midia ENUM('imagem', 'video', 'audio', 'documento') NOT NULL DEFAULT 'documento',
    
    -- Informações da mídia
    largura INT NULL COMMENT 'Largura em pixels para imagens/vídeos',
    altura INT NULL COMMENT 'Altura em pixels para imagens/vídeos', 
    duracao INT NULL COMMENT 'Duração em segundos para vídeos/áudios',
    thumbnail VARCHAR(255) NULL COMMENT 'Caminho para thumbnail do vídeo',
    
    -- Informações da campanha
    campanha VARCHAR(255) NULL COMMENT 'Nome da campanha eleitoral',
    evento VARCHAR(255) NULL COMMENT 'Evento específico',
    data_evento DATE NULL COMMENT 'Data do evento relacionado',
    publico_alvo VARCHAR(255) NULL COMMENT 'Público-alvo da peça',
    
    -- Tags e classificação
    tags TEXT NULL COMMENT 'Tags separadas por vírgula',
    cor_predominante VARCHAR(50) NULL COMMENT 'Cor predominante da peça',
    formato_final VARCHAR(100) NULL COMMENT 'Formato final (A4, 1080x1080, etc)',
    
    -- Status e aprovação
    status ENUM('rascunho', 'aprovado', 'publicado', 'arquivado') DEFAULT 'rascunho',
    aprovado_por INT NULL COMMENT 'ID do usuário que aprovou',
    data_aprovacao DATETIME NULL,
    
    -- Metadados
    visualizacoes INT DEFAULT 0,
    downloads INT DEFAULT 0,
    data_upload TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    data_atualizacao TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    -- Chaves estrangeiras
    FOREIGN KEY (id_usuario) REFERENCES usuarios(id) ON DELETE CASCADE,
    FOREIGN KEY (aprovado_por) REFERENCES usuarios(id) ON DELETE SET NULL,
    
    -- Índices para performance
    INDEX idx_categoria (categoria),
    INDEX idx_tipo_midia (tipo_midia),
    INDEX idx_campanha (campanha),
    INDEX idx_status (status),
    INDEX idx_data_upload (data_upload),
    INDEX idx_tags (tags(100))
);

-- Inserir algumas categorias padrão se não existirem
INSERT IGNORE INTO material_publicitario (id_usuario, titulo, categoria, tipo_midia, nome_arquivo, caminho_arquivo, tipo_arquivo, tamanho_arquivo, status) 
VALUES 
(1, 'Exemplo - Banner Digital', 'Banner Digital', 'imagem', 'exemplo_banner.jpg', 'uploads/material/exemplo_banner.jpg', 'image/jpeg', 150000, 'rascunho'),
(1, 'Exemplo - Vídeo Promocional', 'Vídeo Promocional', 'video', 'exemplo_video.mp4', 'uploads/material/exemplo_video.mp4', 'video/mp4', 5000000, 'rascunho');

-- Criar view para relatórios
CREATE OR REPLACE VIEW vw_material_publicitario_stats AS
SELECT 
    categoria,
    tipo_midia,
    COUNT(*) as total_pecas,
    SUM(visualizacoes) as total_visualizacoes,
    SUM(downloads) as total_downloads,
    AVG(tamanho_arquivo) as tamanho_medio
FROM material_publicitario 
GROUP BY categoria, tipo_midia;

-- Criar procedure para busca avançada
DELIMITER //
CREATE PROCEDURE BuscarMaterialPublicitario(
    IN p_termo VARCHAR(255),
    IN p_categoria VARCHAR(100),
    IN p_tipo_midia VARCHAR(50),
    IN p_campanha VARCHAR(255),
    IN p_status VARCHAR(50)
)
BEGIN
    SELECT 
        mp.*,
        u.nome as usuario_nome,
        ua.nome as aprovado_por_nome
    FROM material_publicitario mp
    LEFT JOIN usuarios u ON mp.id_usuario = u.id
    LEFT JOIN usuarios ua ON mp.aprovado_por = ua.id
    WHERE 
        (p_termo IS NULL OR mp.titulo LIKE CONCAT('%', p_termo, '%') OR mp.descricao LIKE CONCAT('%', p_termo, '%') OR mp.tags LIKE CONCAT('%', p_termo, '%'))
        AND (p_categoria IS NULL OR mp.categoria = p_categoria)
        AND (p_tipo_midia IS NULL OR mp.tipo_midia = p_tipo_midia)
        AND (p_campanha IS NULL OR mp.campanha LIKE CONCAT('%', p_campanha, '%'))
        AND (p_status IS NULL OR mp.status = p_status)
    ORDER BY mp.data_upload DESC;
END //
DELIMITER ;

SELECT 'Tabela material_publicitario criada com sucesso!' as status;
SELECT 'Execute: CALL BuscarMaterialPublicitario("termo", "categoria", "tipo", "campanha", "status");' as exemplo_busca;