-- ============================================================
-- Content Hub — Schema MySQL completo
-- Uso: mysql -u root -p < database/content_hub_mysql.sql
-- ============================================================

CREATE DATABASE IF NOT EXISTS content_hub
    CHARACTER SET utf8mb4
    COLLATE utf8mb4_unicode_ci;

USE content_hub;

SET FOREIGN_KEY_CHECKS = 0;

-- Laravel / infra
DROP TABLE IF EXISTS failed_jobs;
DROP TABLE IF EXISTS job_batches;
DROP TABLE IF EXISTS jobs;
DROP TABLE IF EXISTS cache_locks;
DROP TABLE IF EXISTS cache;
DROP TABLE IF EXISTS sessions;
DROP TABLE IF EXISTS password_reset_tokens;

-- Spatie Permission
DROP TABLE IF EXISTS role_has_permissions;
DROP TABLE IF EXISTS model_has_roles;
DROP TABLE IF EXISTS model_has_permissions;
DROP TABLE IF EXISTS roles;
DROP TABLE IF EXISTS permissions;

-- Content Hub (ordem inversa FK)
DROP TABLE IF EXISTS discovery_logs;
DROP TABLE IF EXISTS site_pages;
DROP TABLE IF EXISTS block_templates;
DROP TABLE IF EXISTS deploy_logs;
DROP TABLE IF EXISTS keyword_rankings;
DROP TABLE IF EXISTS keywords;
DROP TABLE IF EXISTS post_revisions;
DROP TABLE IF EXISTS seo_meta;
DROP TABLE IF EXISTS post_tag;
DROP TABLE IF EXISTS posts;
DROP TABLE IF EXISTS tags;
DROP TABLE IF EXISTS categories;
DROP TABLE IF EXISTS site_user;
DROP TABLE IF EXISTS templates;
DROP TABLE IF EXISTS sites;
DROP TABLE IF EXISTS users;

SET FOREIGN_KEY_CHECKS = 1;

-- ============================================================
-- USUÁRIOS / AUTH (Laravel Breeze)
-- ============================================================
CREATE TABLE users (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    email VARCHAR(255) NOT NULL UNIQUE,
    email_verified_at TIMESTAMP NULL,
    password VARCHAR(255) NOT NULL,
    remember_token VARCHAR(100) NULL,
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL
) ENGINE=InnoDB;

CREATE TABLE password_reset_tokens (
    email VARCHAR(255) NOT NULL PRIMARY KEY,
    token VARCHAR(255) NOT NULL,
    created_at TIMESTAMP NULL
) ENGINE=InnoDB;

CREATE TABLE sessions (
    id VARCHAR(255) NOT NULL PRIMARY KEY,
    user_id BIGINT UNSIGNED NULL,
    ip_address VARCHAR(45) NULL,
    user_agent TEXT NULL,
    payload LONGTEXT NOT NULL,
    last_activity INT NOT NULL,
    INDEX sessions_user_id_index (user_id),
    INDEX sessions_last_activity_index (last_activity)
) ENGINE=InnoDB;

-- ============================================================
-- CACHE / FILAS (Laravel + Horizon)
-- ============================================================
CREATE TABLE cache (
    `key` VARCHAR(255) NOT NULL PRIMARY KEY,
    value MEDIUMTEXT NOT NULL,
    expiration BIGINT NOT NULL,
    INDEX cache_expiration_index (expiration)
) ENGINE=InnoDB;

CREATE TABLE cache_locks (
    `key` VARCHAR(255) NOT NULL PRIMARY KEY,
    owner VARCHAR(255) NOT NULL,
    expiration BIGINT NOT NULL,
    INDEX cache_locks_expiration_index (expiration)
) ENGINE=InnoDB;

CREATE TABLE jobs (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    queue VARCHAR(255) NOT NULL,
    payload LONGTEXT NOT NULL,
    attempts TINYINT UNSIGNED NOT NULL,
    reserved_at INT UNSIGNED NULL,
    available_at INT UNSIGNED NOT NULL,
    created_at INT UNSIGNED NOT NULL,
    INDEX jobs_queue_index (queue)
) ENGINE=InnoDB;

CREATE TABLE job_batches (
    id VARCHAR(255) NOT NULL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    total_jobs INT NOT NULL,
    pending_jobs INT NOT NULL,
    failed_jobs INT NOT NULL,
    failed_job_ids LONGTEXT NOT NULL,
    options MEDIUMTEXT NULL,
    cancelled_at INT NULL,
    created_at INT NOT NULL,
    finished_at INT NULL
) ENGINE=InnoDB;

CREATE TABLE failed_jobs (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    uuid VARCHAR(255) NOT NULL UNIQUE,
    connection TEXT NOT NULL,
    queue TEXT NOT NULL,
    payload LONGTEXT NOT NULL,
    exception LONGTEXT NOT NULL,
    failed_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX failed_jobs_connection_queue_failed_at_index (connection(191), queue(191), failed_at)
) ENGINE=InnoDB;

-- ============================================================
-- SPATIE LARAVEL-PERMISSION
-- ============================================================
CREATE TABLE permissions (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    guard_name VARCHAR(255) NOT NULL,
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL,
    UNIQUE KEY permissions_name_guard_name_unique (name, guard_name)
) ENGINE=InnoDB;

CREATE TABLE roles (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    guard_name VARCHAR(255) NOT NULL,
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL,
    UNIQUE KEY roles_name_guard_name_unique (name, guard_name)
) ENGINE=InnoDB;

CREATE TABLE model_has_permissions (
    permission_id BIGINT UNSIGNED NOT NULL,
    model_type VARCHAR(255) NOT NULL,
    model_id BIGINT UNSIGNED NOT NULL,
    PRIMARY KEY (permission_id, model_id, model_type),
    INDEX model_has_permissions_model_id_model_type_index (model_id, model_type),
    CONSTRAINT fk_mhp_permission FOREIGN KEY (permission_id) REFERENCES permissions(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE model_has_roles (
    role_id BIGINT UNSIGNED NOT NULL,
    model_type VARCHAR(255) NOT NULL,
    model_id BIGINT UNSIGNED NOT NULL,
    PRIMARY KEY (role_id, model_id, model_type),
    INDEX model_has_roles_model_id_model_type_index (model_id, model_type),
    CONSTRAINT fk_mhr_role FOREIGN KEY (role_id) REFERENCES roles(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE role_has_permissions (
    permission_id BIGINT UNSIGNED NOT NULL,
    role_id BIGINT UNSIGNED NOT NULL,
    PRIMARY KEY (permission_id, role_id),
    CONSTRAINT fk_rhp_permission FOREIGN KEY (permission_id) REFERENCES permissions(id) ON DELETE CASCADE,
    CONSTRAINT fk_rhp_role FOREIGN KEY (role_id) REFERENCES roles(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ============================================================
-- SITES (multi-tenant)
-- ============================================================
CREATE TABLE sites (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(150) NOT NULL,
    domain VARCHAR(255) NOT NULL UNIQUE,
    protocol VARCHAR(10) NOT NULL DEFAULT 'https',
    deploy_driver ENUM('sftp','ftp','ssh','git') NOT NULL DEFAULT 'sftp',
    deploy_host VARCHAR(255) NULL,
    deploy_port INT UNSIGNED NULL,
    deploy_username VARCHAR(255) NULL,
    deploy_password TEXT NULL COMMENT 'Laravel encrypt()',
    deploy_private_key TEXT NULL COMMENT 'Laravel encrypt()',
    remote_blog_path VARCHAR(255) NOT NULL,
    remote_sitemap_path VARCHAR(255) NULL,
    remote_rss_path VARCHAR(255) NULL,
    gsc_property_url VARCHAR(255) NULL,
    gsc_refresh_token TEXT NULL COMMENT 'Laravel encrypt()',
    active_template_id BIGINT UNSIGNED NULL,
    status ENUM('active','paused','archived') NOT NULL DEFAULT 'active',
    -- Pipeline autônomo (Discovery / Analyzer / Composer)
    site_dna JSON NULL COMMENT 'Shell, blocos, design tokens — gerado por IA',
    discovery_snapshot JSON NULL COMMENT 'Último crawl HTTP/SFTP',
    listing_config JSON NULL COMMENT 'Config listagem blog',
    analyzer_confidence DECIMAL(5,2) NULL,
    last_discovered_at TIMESTAMP NULL,
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL,
    deleted_at TIMESTAMP NULL,
    INDEX idx_sites_status (status),
    INDEX idx_sites_last_discovered (last_discovered_at)
) ENGINE=InnoDB;

-- ============================================================
-- TEMPLATES (Blade por site)
-- ============================================================
CREATE TABLE templates (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    site_id BIGINT UNSIGNED NOT NULL,
    name VARCHAR(150) NOT NULL,
    type ENUM('post','listing','category') NOT NULL DEFAULT 'post',
    blade_path VARCHAR(255) NOT NULL,
    version INT UNSIGNED NOT NULL DEFAULT 1,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL,
    CONSTRAINT fk_templates_site FOREIGN KEY (site_id) REFERENCES sites(id) ON DELETE CASCADE,
    INDEX idx_templates_site_type (site_id, type)
) ENGINE=InnoDB;

ALTER TABLE sites
    ADD CONSTRAINT fk_sites_active_template
    FOREIGN KEY (active_template_id) REFERENCES templates(id) ON DELETE SET NULL;

-- ============================================================
-- BLOCK TEMPLATES (catálogo blocos nativos por site)
-- ============================================================
CREATE TABLE block_templates (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    site_id BIGINT UNSIGNED NOT NULL,
    type VARCHAR(100) NOT NULL COMMENT 'h2_section, faq_accordion, etc.',
    label VARCHAR(150) NOT NULL,
    blade_partial VARCHAR(255) NOT NULL,
    schema_rules JSON NULL COMMENT 'Campos obrigatórios / validação',
    sort_order INT UNSIGNED NOT NULL DEFAULT 0,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL,
    UNIQUE KEY uk_block_templates_site_type (site_id, type),
    CONSTRAINT fk_block_templates_site FOREIGN KEY (site_id) REFERENCES sites(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ============================================================
-- SITE PAGES (URLs fixas p/ merge sitemap)
-- ============================================================
CREATE TABLE site_pages (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    site_id BIGINT UNSIGNED NOT NULL,
    url_path VARCHAR(500) NOT NULL,
    page_type ENUM('static','blog_listing','blog_post','other') NOT NULL DEFAULT 'static',
    changefreq ENUM('always','hourly','daily','weekly','monthly','yearly','never') NULL DEFAULT 'monthly',
    priority DECIMAL(2,1) NULL DEFAULT 0.5,
    is_managed_by_hub TINYINT(1) NOT NULL DEFAULT 0 COMMENT '1 = Hub regen sobrescreve',
    discovered_at TIMESTAMP NULL,
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL,
    UNIQUE KEY uk_site_pages_site_path (site_id, url_path(191)),
    CONSTRAINT fk_site_pages_site FOREIGN KEY (site_id) REFERENCES sites(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ============================================================
-- CATEGORIAS / TAGS
-- ============================================================
CREATE TABLE categories (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    site_id BIGINT UNSIGNED NOT NULL,
    name VARCHAR(150) NOT NULL,
    slug VARCHAR(150) NOT NULL,
    parent_id BIGINT UNSIGNED NULL,
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL,
    CONSTRAINT fk_categories_site FOREIGN KEY (site_id) REFERENCES sites(id) ON DELETE CASCADE,
    CONSTRAINT fk_categories_parent FOREIGN KEY (parent_id) REFERENCES categories(id) ON DELETE SET NULL,
    UNIQUE KEY uk_categories_site_slug (site_id, slug)
) ENGINE=InnoDB;

CREATE TABLE tags (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    site_id BIGINT UNSIGNED NOT NULL,
    name VARCHAR(100) NOT NULL,
    slug VARCHAR(100) NOT NULL,
    CONSTRAINT fk_tags_site FOREIGN KEY (site_id) REFERENCES sites(id) ON DELETE CASCADE,
    UNIQUE KEY uk_tags_site_slug (site_id, slug)
) ENGINE=InnoDB;

-- ============================================================
-- POSTS
-- ============================================================
CREATE TABLE posts (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    site_id BIGINT UNSIGNED NOT NULL,
    category_id BIGINT UNSIGNED NULL,
    author_id BIGINT UNSIGNED NOT NULL,
    title VARCHAR(255) NOT NULL,
    slug VARCHAR(255) NOT NULL,
    excerpt VARCHAR(500) NULL,
    content LONGTEXT NOT NULL COMMENT 'HTML renderizado (cache deploy)',
    content_blocks JSON NULL COMMENT 'Fonte verdade Block Composer',
    featured_image VARCHAR(255) NULL,
    status ENUM('draft','review','scheduled','published','archived') NOT NULL DEFAULT 'draft',
    publish_at DATETIME NULL,
    published_at DATETIME NULL,
    is_indexable TINYINT(1) NOT NULL DEFAULT 1,
    seo_score TINYINT UNSIGNED NULL COMMENT '0-100 checklist SEO',
    ai_generation_meta JSON NULL COMMENT 'prompt, model, keyword origem',
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL,
    deleted_at TIMESTAMP NULL,
    CONSTRAINT fk_posts_site FOREIGN KEY (site_id) REFERENCES sites(id) ON DELETE CASCADE,
    CONSTRAINT fk_posts_category FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE SET NULL,
    CONSTRAINT fk_posts_author FOREIGN KEY (author_id) REFERENCES users(id) ON DELETE RESTRICT,
    UNIQUE KEY uk_posts_site_slug (site_id, slug),
    INDEX idx_posts_status (status),
    INDEX idx_posts_publish_at (publish_at)
) ENGINE=InnoDB;

CREATE TABLE post_tag (
    post_id BIGINT UNSIGNED NOT NULL,
    tag_id BIGINT UNSIGNED NOT NULL,
    PRIMARY KEY (post_id, tag_id),
    CONSTRAINT fk_post_tag_post FOREIGN KEY (post_id) REFERENCES posts(id) ON DELETE CASCADE,
    CONSTRAINT fk_post_tag_tag FOREIGN KEY (tag_id) REFERENCES tags(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ============================================================
-- SEO META (1:1 posts)
-- ============================================================
CREATE TABLE seo_meta (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    post_id BIGINT UNSIGNED NOT NULL UNIQUE,
    meta_title VARCHAR(255) NULL,
    meta_description VARCHAR(500) NULL,
    canonical_url VARCHAR(255) NULL,
    focus_keyword VARCHAR(150) NULL,
    schema_json JSON NULL COMMENT 'JSON-LD Article, FAQPage, etc.',
    og_title VARCHAR(255) NULL,
    og_description VARCHAR(500) NULL,
    og_image VARCHAR(255) NULL,
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL,
    CONSTRAINT fk_seo_meta_post FOREIGN KEY (post_id) REFERENCES posts(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ============================================================
-- REVISÕES
-- ============================================================
CREATE TABLE post_revisions (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    post_id BIGINT UNSIGNED NOT NULL,
    editor_id BIGINT UNSIGNED NOT NULL,
    title VARCHAR(255) NOT NULL,
    content LONGTEXT NOT NULL,
    content_blocks JSON NULL,
    seo_snapshot JSON NULL,
    created_at TIMESTAMP NULL,
    CONSTRAINT fk_revisions_post FOREIGN KEY (post_id) REFERENCES posts(id) ON DELETE CASCADE,
    CONSTRAINT fk_revisions_editor FOREIGN KEY (editor_id) REFERENCES users(id) ON DELETE RESTRICT,
    INDEX idx_revisions_post (post_id)
) ENGINE=InnoDB;

-- ============================================================
-- KEYWORDS / RANKING
-- ============================================================
CREATE TABLE keywords (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    site_id BIGINT UNSIGNED NOT NULL,
    post_id BIGINT UNSIGNED NULL,
    term VARCHAR(255) NOT NULL,
    is_secondary TINYINT(1) NOT NULL DEFAULT 0,
    tracking_source ENUM('gsc','dataforseo','serpapi','manual') NOT NULL DEFAULT 'gsc',
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL,
    CONSTRAINT fk_keywords_site FOREIGN KEY (site_id) REFERENCES sites(id) ON DELETE CASCADE,
    CONSTRAINT fk_keywords_post FOREIGN KEY (post_id) REFERENCES posts(id) ON DELETE SET NULL,
    INDEX idx_keywords_site (site_id)
) ENGINE=InnoDB;

CREATE TABLE keyword_rankings (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    keyword_id BIGINT UNSIGNED NOT NULL,
    date DATE NOT NULL,
    avg_position DECIMAL(6,2) NULL,
    clicks INT UNSIGNED NULL,
    impressions INT UNSIGNED NULL,
    ctr DECIMAL(5,2) NULL,
    source ENUM('gsc','dataforseo','serpapi','manual') NOT NULL DEFAULT 'gsc',
    created_at TIMESTAMP NULL,
    CONSTRAINT fk_rankings_keyword FOREIGN KEY (keyword_id) REFERENCES keywords(id) ON DELETE CASCADE,
    UNIQUE KEY uk_ranking_keyword_date_source (keyword_id, date, source),
    INDEX idx_rankings_date (date)
) ENGINE=InnoDB;

-- ============================================================
-- DEPLOY LOGS
-- ============================================================
CREATE TABLE deploy_logs (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    site_id BIGINT UNSIGNED NOT NULL,
    post_id BIGINT UNSIGNED NULL,
    triggered_by BIGINT UNSIGNED NULL,
    action ENUM('publish','update','unpublish','sitemap_regen','rss_regen') NOT NULL,
    status ENUM('pending','success','failed') NOT NULL DEFAULT 'pending',
    driver_used ENUM('sftp','ftp','ssh','git') NOT NULL,
    message TEXT NULL,
    payload JSON NULL,
    attempt INT UNSIGNED NOT NULL DEFAULT 1,
    started_at TIMESTAMP NULL,
    finished_at TIMESTAMP NULL,
    created_at TIMESTAMP NULL,
    CONSTRAINT fk_deploy_logs_site FOREIGN KEY (site_id) REFERENCES sites(id) ON DELETE CASCADE,
    CONSTRAINT fk_deploy_logs_post FOREIGN KEY (post_id) REFERENCES posts(id) ON DELETE SET NULL,
    CONSTRAINT fk_deploy_logs_user FOREIGN KEY (triggered_by) REFERENCES users(id) ON DELETE SET NULL,
    INDEX idx_deploy_logs_status (status),
    INDEX idx_deploy_logs_site (site_id)
) ENGINE=InnoDB;

-- ============================================================
-- DISCOVERY LOGS (auditoria crawl/analyzer)
-- ============================================================
CREATE TABLE discovery_logs (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    site_id BIGINT UNSIGNED NOT NULL,
    triggered_by BIGINT UNSIGNED NULL,
    action ENUM('crawl','analyze','import_posts','regen_dna') NOT NULL,
    status ENUM('pending','success','failed') NOT NULL DEFAULT 'pending',
    message TEXT NULL,
    snapshot JSON NULL,
    started_at TIMESTAMP NULL,
    finished_at TIMESTAMP NULL,
    created_at TIMESTAMP NULL,
    CONSTRAINT fk_discovery_logs_site FOREIGN KEY (site_id) REFERENCES sites(id) ON DELETE CASCADE,
    CONSTRAINT fk_discovery_logs_user FOREIGN KEY (triggered_by) REFERENCES users(id) ON DELETE SET NULL,
    INDEX idx_discovery_logs_site (site_id),
    INDEX idx_discovery_logs_status (status)
) ENGINE=InnoDB;

-- ============================================================
-- USUÁRIOS <-> SITES
-- ============================================================
CREATE TABLE site_user (
    site_id BIGINT UNSIGNED NOT NULL,
    user_id BIGINT UNSIGNED NOT NULL,
    role_on_site ENUM('admin','editor','writer') NOT NULL DEFAULT 'writer',
    PRIMARY KEY (site_id, user_id),
    CONSTRAINT fk_site_user_site FOREIGN KEY (site_id) REFERENCES sites(id) ON DELETE CASCADE,
    CONSTRAINT fk_site_user_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ============================================================
-- SEED MÍNIMO (roles + permissions)
-- Admin: php artisan content-hub:install-admin
-- E-mail padrão: admin@hoogli.com.br (senha definida na hora)
-- ============================================================
INSERT INTO roles (name, guard_name, created_at, updated_at) VALUES
    ('Admin', 'web', NOW(), NOW()),
    ('Editor', 'web', NOW(), NOW()),
    ('Writer', 'web', NOW(), NOW());

INSERT INTO permissions (name, guard_name, created_at, updated_at) VALUES
    ('sites.view', 'web', NOW(), NOW()),
    ('sites.manage', 'web', NOW(), NOW()),
    ('posts.view', 'web', NOW(), NOW()),
    ('posts.create', 'web', NOW(), NOW()),
    ('posts.update', 'web', NOW(), NOW()),
    ('posts.delete', 'web', NOW(), NOW()),
    ('posts.publish', 'web', NOW(), NOW()),
    ('keywords.manage', 'web', NOW(), NOW()),
    ('users.manage', 'web', NOW(), NOW());

INSERT INTO role_has_permissions (permission_id, role_id)
SELECT p.id, r.id FROM permissions p CROSS JOIN roles r WHERE r.name = 'Admin';

INSERT INTO role_has_permissions (permission_id, role_id)
SELECT p.id, r.id FROM permissions p
JOIN roles r ON r.name = 'Editor'
WHERE p.name IN ('sites.view','posts.view','posts.create','posts.update','posts.publish','keywords.manage');

INSERT INTO role_has_permissions (permission_id, role_id)
SELECT p.id, r.id FROM permissions p
JOIN roles r ON r.name = 'Writer'
WHERE p.name IN ('sites.view','posts.view','posts.create','posts.update');