-- ============================================================
-- PORTAL DE NOTÍCIAS - SCHEMA MySQL - FASE 1 (Fundação)
-- Charset: utf8mb4 / Engine: InnoDB
-- ============================================================

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ------------------------------------------------------------
-- 1. USUÁRIOS E PERMISSÕES
-- ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS roles (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    slug VARCHAR(30) NOT NULL UNIQUE,        -- administrador, editor, redator, moderador
    nome VARCHAR(50) NOT NULL,
    descricao VARCHAR(255) DEFAULT NULL,
    criado_em DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS permissoes (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    slug VARCHAR(60) NOT NULL UNIQUE,        -- noticias.criar, noticias.publicar, usuarios.gerenciar, etc
    descricao VARCHAR(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS role_permissoes (
    role_id INT UNSIGNED NOT NULL,
    permissao_id INT UNSIGNED NOT NULL,
    PRIMARY KEY (role_id, permissao_id),
    FOREIGN KEY (role_id) REFERENCES roles(id) ON DELETE CASCADE,
    FOREIGN KEY (permissao_id) REFERENCES permissoes(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS usuarios (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    role_id INT UNSIGNED NOT NULL,
    nome VARCHAR(120) NOT NULL,
    email VARCHAR(150) NOT NULL UNIQUE,
    senha_hash VARCHAR(255) NOT NULL,
    avatar VARCHAR(255) DEFAULT NULL,
    bio TEXT DEFAULT NULL,
    status ENUM('ativo','inativo','bloqueado') NOT NULL DEFAULT 'ativo',
    ultimo_login DATETIME DEFAULT NULL,
    token_recuperacao VARCHAR(255) DEFAULT NULL,
    token_expira_em DATETIME DEFAULT NULL,
    criado_em DATETIME DEFAULT CURRENT_TIMESTAMP,
    atualizado_em DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (role_id) REFERENCES roles(id) ON DELETE RESTRICT,
    INDEX idx_usuarios_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS usuarios_log_acesso (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    usuario_id INT UNSIGNED NOT NULL,
    ip VARCHAR(45) DEFAULT NULL,
    user_agent VARCHAR(255) DEFAULT NULL,
    acao ENUM('login','logout','login_falho') NOT NULL,
    criado_em DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (usuario_id) REFERENCES usuarios(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ------------------------------------------------------------
-- 2. CATEGORIAS E TAGS
-- ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS categorias (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    categoria_pai_id INT UNSIGNED DEFAULT NULL,
    nome VARCHAR(80) NOT NULL,
    slug VARCHAR(100) NOT NULL UNIQUE,
    descricao TEXT DEFAULT NULL,
    cor VARCHAR(7) DEFAULT NULL,              -- hex, ex: #E63946 (cor de destaque do card/tag)
    icone VARCHAR(60) DEFAULT NULL,
    imagem_capa VARCHAR(255) DEFAULT NULL,
    ordem INT UNSIGNED NOT NULL DEFAULT 0,
    meta_title VARCHAR(160) DEFAULT NULL,
    meta_description VARCHAR(255) DEFAULT NULL,
    ativo TINYINT(1) NOT NULL DEFAULT 1,
    criado_em DATETIME DEFAULT CURRENT_TIMESTAMP,
    atualizado_em DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (categoria_pai_id) REFERENCES categorias(id) ON DELETE SET NULL,
    INDEX idx_categorias_ativo (ativo),
    INDEX idx_categorias_ordem (ordem)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS tags (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    nome VARCHAR(60) NOT NULL,
    slug VARCHAR(80) NOT NULL UNIQUE,
    criado_em DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ------------------------------------------------------------
-- 3. NOTÍCIAS (núcleo do sistema — manuais e via RSS)
-- ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS noticias (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    categoria_id INT UNSIGNED NOT NULL,
    autor_id INT UNSIGNED DEFAULT NULL,         -- NULL permitido p/ notícias 100% automáticas sem autor humano
    origem ENUM('manual','rss') NOT NULL DEFAULT 'manual',
    feed_id INT UNSIGNED DEFAULT NULL,          -- FK para rss_feeds, preenchido se origem = rss

    titulo VARCHAR(255) NOT NULL,
    subtitulo VARCHAR(255) DEFAULT NULL,
    slug VARCHAR(280) NOT NULL UNIQUE,
    resumo VARCHAR(500) DEFAULT NULL,
    conteudo LONGTEXT NOT NULL,

    imagem_destaque VARCHAR(255) DEFAULT NULL,
    imagem_destaque_alt VARCHAR(255) DEFAULT NULL,
    imagem_creditos VARCHAR(150) DEFAULT NULL,

    link_original VARCHAR(500) DEFAULT NULL,    -- usado quando origem = rss
    hash_conteudo CHAR(64) DEFAULT NULL,         -- sha256(titulo+link_original) p/ deduplicação

    status ENUM('publicado','rascunho','agendado','pendente_revisao','arquivado') NOT NULL DEFAULT 'rascunho',
    data_publicacao DATETIME DEFAULT NULL,       -- usado para agendamento e ordenação cronológica
    destaque TINYINT(1) NOT NULL DEFAULT 0,      -- aparece no slider/destaque da home
    destaque_ordem INT UNSIGNED DEFAULT NULL,

    visualizacoes BIGINT UNSIGNED NOT NULL DEFAULT 0,
    permite_comentarios TINYINT(1) NOT NULL DEFAULT 1,

    -- SEO
    meta_title VARCHAR(160) DEFAULT NULL,
    meta_description VARCHAR(255) DEFAULT NULL,
    meta_keywords VARCHAR(255) DEFAULT NULL,
    og_title VARCHAR(160) DEFAULT NULL,
    og_description VARCHAR(255) DEFAULT NULL,
    og_image VARCHAR(255) DEFAULT NULL,

    criado_em DATETIME DEFAULT CURRENT_TIMESTAMP,
    atualizado_em DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    FOREIGN KEY (categoria_id) REFERENCES categorias(id) ON DELETE RESTRICT,
    FOREIGN KEY (autor_id) REFERENCES usuarios(id) ON DELETE SET NULL,

    INDEX idx_noticias_status_pub (status, data_publicacao),
    INDEX idx_noticias_categoria (categoria_id),
    INDEX idx_noticias_destaque (destaque, destaque_ordem),
    INDEX idx_noticias_origem (origem),
    INDEX idx_noticias_hash (hash_conteudo),
    FULLTEXT INDEX ft_noticias_busca (titulo, subtitulo, resumo, conteudo)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS noticias_tags (
    noticia_id BIGINT UNSIGNED NOT NULL,
    tag_id INT UNSIGNED NOT NULL,
    PRIMARY KEY (noticia_id, tag_id),
    FOREIGN KEY (noticia_id) REFERENCES noticias(id) ON DELETE CASCADE,
    FOREIGN KEY (tag_id) REFERENCES tags(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Galeria de imagens (notícias manuais)
CREATE TABLE IF NOT EXISTS noticias_galeria (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    noticia_id BIGINT UNSIGNED NOT NULL,
    imagem VARCHAR(255) NOT NULL,
    legenda VARCHAR(255) DEFAULT NULL,
    ordem INT UNSIGNED NOT NULL DEFAULT 0,
    criado_em DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (noticia_id) REFERENCES noticias(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Histórico de revisões/edições (auditoria simples)
CREATE TABLE IF NOT EXISTS noticias_revisoes (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    noticia_id BIGINT UNSIGNED NOT NULL,
    usuario_id INT UNSIGNED DEFAULT NULL,
    titulo_anterior VARCHAR(255) DEFAULT NULL,
    conteudo_anterior LONGTEXT DEFAULT NULL,
    criado_em DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (noticia_id) REFERENCES noticias(id) ON DELETE CASCADE,
    FOREIGN KEY (usuario_id) REFERENCES usuarios(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Log diário de visualizações (para "mais lidas" por período e gráficos do dashboard)
CREATE TABLE IF NOT EXISTS noticias_views_diarias (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    noticia_id BIGINT UNSIGNED NOT NULL,
    data DATE NOT NULL,
    total INT UNSIGNED NOT NULL DEFAULT 0,
    UNIQUE KEY uq_noticia_data (noticia_id, data),
    FOREIGN KEY (noticia_id) REFERENCES noticias(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ------------------------------------------------------------
-- 4. RSS / FEEDS AUTOMÁTICOS (estrutura preparada p/ Fase 3)
-- ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS rss_feeds (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    categoria_id INT UNSIGNED NOT NULL,
    nome VARCHAR(120) NOT NULL,
    url VARCHAR(500) NOT NULL,
    ativo TINYINT(1) NOT NULL DEFAULT 1,
    intervalo_minutos INT UNSIGNED NOT NULL DEFAULT 30,
    aprovacao_automatica TINYINT(1) NOT NULL DEFAULT 0,
    ultima_execucao DATETIME DEFAULT NULL,
    ultimo_status ENUM('ok','erro','nunca_executado') NOT NULL DEFAULT 'nunca_executado',
    criado_em DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (categoria_id) REFERENCES categorias(id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS rss_logs (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    feed_id INT UNSIGNED NOT NULL,
    itens_encontrados INT UNSIGNED NOT NULL DEFAULT 0,
    itens_importados INT UNSIGNED NOT NULL DEFAULT 0,
    itens_duplicados INT UNSIGNED NOT NULL DEFAULT 0,
    erro TEXT DEFAULT NULL,
    executado_em DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (feed_id) REFERENCES rss_feeds(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Adiciona FK de noticias.feed_id agora que rss_feeds existe
ALTER TABLE noticias
    ADD CONSTRAINT fk_noticias_feed FOREIGN KEY (feed_id) REFERENCES rss_feeds(id) ON DELETE SET NULL;

-- ------------------------------------------------------------
-- 5. CONFIGURAÇÕES GERAIS DO SITE
-- ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS configuracoes (
    chave VARCHAR(80) NOT NULL PRIMARY KEY,
    valor TEXT DEFAULT NULL,
    tipo ENUM('texto','numero','booleano','json','imagem') NOT NULL DEFAULT 'texto',
    descricao VARCHAR(255) DEFAULT NULL,
    atualizado_em DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

SET FOREIGN_KEY_CHECKS = 1;

-- ------------------------------------------------------------
-- 6. SEEDS MÍNIMOS (roles, permissões básicas, categorias do prompt)
-- ------------------------------------------------------------

INSERT INTO roles (slug, nome, descricao) VALUES
('administrador', 'Administrador', 'Acesso total ao sistema'),
('editor', 'Editor', 'Pode publicar, editar e revisar notícias de todos os autores'),
('redator', 'Redator', 'Pode criar e editar apenas suas próprias notícias'),
('moderador', 'Moderador', 'Gerencia comentários e denúncias')
ON DUPLICATE KEY UPDATE nome = VALUES(nome);

INSERT INTO permissoes (slug, descricao) VALUES
('noticias.criar', 'Criar notícias'),
('noticias.editar_propria', 'Editar apenas as próprias notícias'),
('noticias.editar_todas', 'Editar notícias de qualquer autor'),
('noticias.publicar', 'Publicar/despublicar notícias'),
('noticias.excluir', 'Excluir notícias'),
('categorias.gerenciar', 'Criar/editar/excluir categorias'),
('usuarios.gerenciar', 'Gerenciar usuários do painel'),
('rss.gerenciar', 'Gerenciar feeds RSS'),
('comentarios.moderar', 'Aprovar/rejeitar comentários'),
('configuracoes.gerenciar', 'Alterar configurações do site')
ON DUPLICATE KEY UPDATE descricao = VALUES(descricao);

-- Administrador: todas as permissões
INSERT INTO role_permissoes (role_id, permissao_id)
SELECT r.id, p.id FROM roles r, permissoes p WHERE r.slug = 'administrador';

-- Editor: tudo de notícias + categorias + comentários, sem usuários/config
INSERT INTO role_permissoes (role_id, permissao_id)
SELECT r.id, p.id FROM roles r, permissoes p
WHERE r.slug = 'editor'
AND p.slug IN ('noticias.criar','noticias.editar_propria','noticias.editar_todas','noticias.publicar','noticias.excluir','categorias.gerenciar','comentarios.moderar','rss.gerenciar');

-- Redator: só cria e edita as próprias, sem publicar diretamente
INSERT INTO role_permissoes (role_id, permissao_id)
SELECT r.id, p.id FROM roles r, permissoes p
WHERE r.slug = 'redator'
AND p.slug IN ('noticias.criar','noticias.editar_propria');

-- Moderador: só comentários
INSERT INTO role_permissoes (role_id, permissao_id)
SELECT r.id, p.id FROM roles r, permissoes p
WHERE r.slug = 'moderador'
AND p.slug IN ('comentarios.moderar');

INSERT INTO categorias (nome, slug, ordem) VALUES
('Política', 'politica', 1),
('Economia', 'economia', 2),
('Esportes', 'esportes', 3),
('Tecnologia', 'tecnologia', 4),
('Entretenimento', 'entretenimento', 5),
('Saúde', 'saude', 6),
('Educação', 'educacao', 7),
('Mundo', 'mundo', 8),
('Polícia', 'policia', 9),
('Cidades', 'cidades', 10),
('Bahia', 'bahia', 11),
('Brasil', 'brasil', 12)
ON DUPLICATE KEY UPDATE nome = VALUES(nome);

INSERT INTO configuracoes (chave, valor, tipo, descricao) VALUES
('site_nome', 'Portal de Notícias', 'texto', 'Nome do site exibido no header e meta tags'),
('site_descricao', 'As principais notícias em tempo real', 'texto', 'Descrição padrão do site (meta description)'),
('site_logo', '', 'imagem', 'Logo do site'),
('site_favicon', '', 'imagem', 'Favicon do site'),
('modo_escuro_ativo', '1', 'booleano', 'Permite alternar modo escuro no front-end'),
('comentarios_aprovacao_manual', '1', 'booleano', 'Comentários precisam de aprovação manual'),
('google_adsense_id', '', 'texto', 'ID do Google AdSense'),
('items_por_pagina', '12', 'numero', 'Quantidade de notícias por página de listagem')
ON DUPLICATE KEY UPDATE valor = valor;