-- ============================================================
-- Ação Net pSEO Hub — Schema Completo
-- MySQL 8.0+ | utf8mb4_unicode_ci
-- Execute este arquivo uma única vez na instalação
-- ============================================================

SET NAMES utf8mb4;
SET time_zone = '-03:00';
SET foreign_key_checks = 0;

-- ──────────────────────────────────────────────────────────────
-- PLANOS
-- ──────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `plans` (
    `id`            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `name`          VARCHAR(100)    NOT NULL,
    `monthly_price` DECIMAL(10,2)   NOT NULL DEFAULT 0,
    `max_projects`  INT UNSIGNED    NOT NULL DEFAULT 1,
    `max_pages`     INT UNSIGNED    NOT NULL DEFAULT 1000,
    `max_ai_calls`  INT UNSIGNED    NOT NULL DEFAULT 100,
    `features`      JSON            DEFAULT NULL,
    `is_active`     TINYINT(1)      NOT NULL DEFAULT 1,
    `created_at`    TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `plans` (`name`, `monthly_price`, `max_projects`, `max_pages`, `max_ai_calls`, `features`) VALUES
('Starter',    197.00,  1,    5000,    50,   '{"sitemaps":true,"indexing":false,"ai":false}'),
('Pro',        497.00,  5,   50000,   500,   '{"sitemaps":true,"indexing":true,"ai":true}'),
('Agency',    1297.00, 20,  500000,  5000,   '{"sitemaps":true,"indexing":true,"ai":true,"white_label":true}'),
('Enterprise', 2997.00, 999,9999999,99999,   '{"sitemaps":true,"indexing":true,"ai":true,"white_label":true,"priority_support":true}');

-- ──────────────────────────────────────────────────────────────
-- CLIENTES
-- ──────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `clients` (
    `id`         BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `name`       VARCHAR(150)    NOT NULL,
    `company`    VARCHAR(200)    DEFAULT NULL,
    `cnpj`       VARCHAR(18)     DEFAULT NULL,
    `email`      VARCHAR(150)    NOT NULL,
    `phone`      VARCHAR(20)     DEFAULT NULL,
    `domain`     VARCHAR(255)    DEFAULT NULL,
    `plan_id`    BIGINT UNSIGNED DEFAULT NULL,
    `notes`      TEXT            DEFAULT NULL,
    `status`     ENUM('active','suspended','cancelled','trial') NOT NULL DEFAULT 'trial',
    `created_at` TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uq_clients_email` (`email`),
    KEY `idx_clients_status`      (`status`),
    KEY `idx_clients_plan`        (`plan_id`),
    CONSTRAINT `fk_clients_plan` FOREIGN KEY (`plan_id`) REFERENCES `plans` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ──────────────────────────────────────────────────────────────
-- TOKENS
-- ──────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `tokens` (
    `id`              BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `client_id`       BIGINT UNSIGNED NOT NULL,
    `token`           VARCHAR(64)     NOT NULL,
    `api_key`         VARCHAR(64)     NOT NULL,
    `status`          ENUM('active','suspended','revoked','expired') NOT NULL DEFAULT 'active',
    `allowed_domains` JSON            DEFAULT NULL COMMENT 'Array de domínios permitidos',
    `rate_limit_rpm`  INT UNSIGNED    NOT NULL DEFAULT 60,
    `last_heartbeat`  TIMESTAMP       DEFAULT NULL,
    `expires_at`      TIMESTAMP       DEFAULT NULL,
    `revoked_at`      TIMESTAMP       DEFAULT NULL,
    `revoked_by`      BIGINT UNSIGNED DEFAULT NULL,
    `created_at`      TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at`      TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uq_tokens_token`   (`token`),
    UNIQUE KEY `uq_tokens_api_key` (`api_key`),
    KEY `idx_tokens_client`        (`client_id`),
    KEY `idx_tokens_status`        (`status`),
    CONSTRAINT `fk_tokens_client` FOREIGN KEY (`client_id`) REFERENCES `clients` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ──────────────────────────────────────────────────────────────
-- USUÁRIOS DO SISTEMA (painel admin)
-- ──────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `users` (
    `id`            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `client_id`     BIGINT UNSIGNED DEFAULT NULL COMMENT 'NULL = equipe interna Ação Net',
    `name`          VARCHAR(150)    NOT NULL,
    `email`         VARCHAR(150)    NOT NULL,
    `password_hash` VARCHAR(255)    NOT NULL,
    `role`          ENUM('admin_master','gestor','operador','cliente') NOT NULL DEFAULT 'operador',
    `avatar`        VARCHAR(500)    DEFAULT NULL,
    `2fa_secret`    VARCHAR(32)     DEFAULT NULL,
    `ip_whitelist`  JSON            DEFAULT NULL,
    `status`        ENUM('active','inactive') NOT NULL DEFAULT 'active',
    `last_login_at` TIMESTAMP       DEFAULT NULL,
    `last_login_ip` VARCHAR(45)     DEFAULT NULL,
    `created_at`    TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at`    TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uq_users_email`  (`email`),
    KEY `idx_users_role`         (`role`),
    KEY `idx_users_client`       (`client_id`),
    CONSTRAINT `fk_users_client` FOREIGN KEY (`client_id`) REFERENCES `clients` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Admin padrão (senha: Admin@2025 — TROQUE IMEDIATAMENTE)
INSERT INTO `users` (`name`, `email`, `password_hash`, `role`) VALUES
('Administrador', 'admin@acaonet.com.br', '$2y$12$placeholder_change_on_install', 'admin_master');

-- ──────────────────────────────────────────────────────────────
-- PROJETOS
-- ──────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `projects` (
    `id`          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `client_id`   BIGINT UNSIGNED NOT NULL,
    `name`        VARCHAR(200)    NOT NULL,
    `domain`      VARCHAR(255)    DEFAULT NULL,
    `niche`       VARCHAR(100)    DEFAULT NULL,
    `category`    VARCHAR(100)    DEFAULT NULL,
    `language`    VARCHAR(10)     NOT NULL DEFAULT 'pt-BR',
    `country`     VARCHAR(10)     NOT NULL DEFAULT 'BR',
    `state_id`    BIGINT UNSIGNED DEFAULT NULL,
    `city_id`     BIGINT UNSIGNED DEFAULT NULL,
    `render_mode` ENUM('realtime','db_cache','file_cache') NOT NULL DEFAULT 'db_cache',
    `cache_ttl`   INT UNSIGNED    NOT NULL DEFAULT 3600,
    `ai_provider` ENUM('none','openai','claude','gemini')  NOT NULL DEFAULT 'none',
    `ai_config`   JSON            DEFAULT NULL,
    `gsc_site_url`VARCHAR(500)    DEFAULT NULL,
    `ga4_id`      VARCHAR(50)     DEFAULT NULL,
    `status`      ENUM('draft','active','paused') NOT NULL DEFAULT 'draft',
    `created_at`  TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at`  TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_projects_client` (`client_id`),
    KEY `idx_projects_status` (`status`),
    CONSTRAINT `fk_projects_client` FOREIGN KEY (`client_id`) REFERENCES `clients` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ──────────────────────────────────────────────────────────────
-- ESTRUTURAS DE URL (padrões pSEO)
-- ──────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `url_structures` (
    `id`                 BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `project_id`         BIGINT UNSIGNED NOT NULL,
    `name`               VARCHAR(200)    NOT NULL,
    `pattern`            VARCHAR(500)    NOT NULL COMMENT 'Ex: /veterinario-em-{bairro}',
    `variables`          JSON            NOT NULL  COMMENT 'Ex: ["bairro","servico"]',
    `template_title`     TEXT            DEFAULT NULL,
    `template_h1`        TEXT            DEFAULT NULL,
    `template_meta_desc` TEXT            DEFAULT NULL,
    `template_intro`     LONGTEXT        DEFAULT NULL,
    `template_faq`       LONGTEXT        DEFAULT NULL COMMENT 'Q: ...\nA: ...\n---\nQ: ...',
    `template_cta`       TEXT            DEFAULT NULL,
    `schema_types`       JSON            DEFAULT NULL COMMENT 'Ex: ["LocalBusiness","FAQPage"]',
    `priority`           INT             NOT NULL DEFAULT 0,
    `status`             ENUM('active','paused') NOT NULL DEFAULT 'active',
    `created_at`         TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at`         TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_url_structures_project` (`project_id`, `status`, `priority`),
    CONSTRAINT `fk_url_structures_project` FOREIGN KEY (`project_id`) REFERENCES `projects` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ──────────────────────────────────────────────────────────────
-- PÁGINAS GERADAS
-- ──────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `generated_pages` (
    `id`               BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `project_id`       BIGINT UNSIGNED NOT NULL,
    `url_structure_id` BIGINT UNSIGNED NOT NULL,
    `slug`             VARCHAR(500)    NOT NULL,
    `resolved_vars`    JSON            DEFAULT NULL,
    `html_cache`       LONGTEXT        DEFAULT NULL,
    `cache_expires_at` TIMESTAMP       DEFAULT NULL,
    `file_cache_path`  VARCHAR(500)    DEFAULT NULL,
    `indexing_status`  ENUM('pending','submitted','indexed','removed') NOT NULL DEFAULT 'pending',
    `last_indexed_at`  TIMESTAMP       DEFAULT NULL,
    `gsc_clicks`       INT UNSIGNED    NOT NULL DEFAULT 0,
    `gsc_impressions`  INT UNSIGNED    NOT NULL DEFAULT 0,
    `gsc_ctr`          DECIMAL(5,2)    NOT NULL DEFAULT 0,
    `gsc_position`     DECIMAL(6,2)    NOT NULL DEFAULT 0,
    `created_at`       TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at`       TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uq_generated_pages_project_slug` (`project_id`, `slug`(191)),
    KEY `idx_generated_pages_structure`           (`url_structure_id`),
    KEY `idx_generated_pages_indexing`            (`project_id`, `indexing_status`),
    CONSTRAINT `fk_generated_pages_project`   FOREIGN KEY (`project_id`)       REFERENCES `projects` (`id`)       ON DELETE CASCADE,
    CONSTRAINT `fk_generated_pages_structure` FOREIGN KEY (`url_structure_id`) REFERENCES `url_structures` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ──────────────────────────────────────────────────────────────
-- LOCALIDADES (já definidas em localities_keywords.sql)
-- ──────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `states` (
    `id`         BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `name`       VARCHAR(150)    NOT NULL,
    `uf`         CHAR(2)         NOT NULL,
    `slug`       VARCHAR(160)    NOT NULL,
    `ibge_code`  VARCHAR(10)     DEFAULT NULL,
    `created_at` TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uq_states_uf`    (`uf`),
    UNIQUE KEY `uq_states_slug`  (`slug`),
    FULLTEXT KEY `ft_states_name`(`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `cities` (
    `id`         BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `state_id`   BIGINT UNSIGNED NOT NULL,
    `name`       VARCHAR(200)    NOT NULL,
    `slug`       VARCHAR(220)    NOT NULL,
    `ibge_code`  VARCHAR(10)     DEFAULT NULL,
    `lat`        DECIMAL(10,7)   DEFAULT NULL,
    `lng`        DECIMAL(10,7)   DEFAULT NULL,
    `created_at` TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uq_cities_state_slug` (`state_id`, `slug`),
    KEY `idx_cities_state`            (`state_id`),
    FULLTEXT KEY `ft_cities_name`     (`name`),
    CONSTRAINT `fk_cities_state` FOREIGN KEY (`state_id`) REFERENCES `states` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `neighborhoods` (
    `id`         BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `city_id`    BIGINT UNSIGNED NOT NULL,
    `name`       VARCHAR(200)    NOT NULL,
    `slug`       VARCHAR(220)    NOT NULL,
    `zone`       VARCHAR(100)    DEFAULT NULL,
    `created_at` TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uq_neighborhoods_city_slug` (`city_id`, `slug`),
    KEY `idx_neighborhoods_city`            (`city_id`),
    FULLTEXT KEY `ft_neighborhoods_name`    (`name`),
    CONSTRAINT `fk_neighborhoods_city` FOREIGN KEY (`city_id`) REFERENCES `cities` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ──────────────────────────────────────────────────────────────
-- KEYWORDS
-- ──────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `keywords` (
    `id`         BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `project_id` BIGINT UNSIGNED NOT NULL,
    `keyword`    VARCHAR(500)    NOT NULL,
    `volume`     INT UNSIGNED    DEFAULT NULL,
    `difficulty` TINYINT UNSIGNED DEFAULT NULL,
    `cpc`        DECIMAL(10,2)   DEFAULT NULL,
    `priority`   TINYINT UNSIGNED NOT NULL DEFAULT 5,
    `created_at` TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uq_keywords_project_kw` (`project_id`, `keyword`(191)),
    KEY `idx_keywords_priority`         (`project_id`, `priority`),
    KEY `idx_keywords_volume`           (`project_id`, `volume`),
    FULLTEXT KEY `ft_keywords_kw`       (`keyword`),
    CONSTRAINT `fk_keywords_project` FOREIGN KEY (`project_id`) REFERENCES `projects` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ──────────────────────────────────────────────────────────────
-- FINANCEIRO
-- ──────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `financial_records` (
    `id`                  BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `client_id`           BIGINT UNSIGNED NOT NULL,
    `amount`              DECIMAL(10,2)   NOT NULL,
    `description`         VARCHAR(255)    DEFAULT NULL,
    `due_date`            DATE            NOT NULL,
    `paid_at`             TIMESTAMP       DEFAULT NULL,
    `status`              ENUM('paid','pending','overdue') NOT NULL DEFAULT 'pending',
    `payment_method`      VARCHAR(50)     DEFAULT NULL,
    `gateway_ref`         VARCHAR(100)    DEFAULT NULL,
    `suspend_after_days`  INT UNSIGNED    NOT NULL DEFAULT 5,
    `created_at`          TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at`          TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_financial_client` (`client_id`),
    KEY `idx_financial_status` (`status`, `due_date`),
    CONSTRAINT `fk_financial_client` FOREIGN KEY (`client_id`) REFERENCES `clients` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ──────────────────────────────────────────────────────────────
-- LOGS DE REQUISIÇÃO DA API
-- Particionado por mês (retido por 90 dias)
-- ──────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `api_request_logs` (
    `id`               BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `token_hash`       VARCHAR(64)     NOT NULL,
    `slug`             VARCHAR(500)    DEFAULT NULL,
    `ip`               VARCHAR(45)     DEFAULT NULL,
    `response_time_ms` INT UNSIGNED    DEFAULT NULL,
    `cache_hit`        TINYINT(1)      NOT NULL DEFAULT 0,
    `status_code`      SMALLINT        NOT NULL DEFAULT 200,
    `created_at`       TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_api_logs_token`   (`token_hash`),
    KEY `idx_api_logs_created` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ──────────────────────────────────────────────────────────────
-- AUDIT LOG
-- ──────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `audit_logs` (
    `id`         BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `user_id`    BIGINT UNSIGNED DEFAULT NULL,
    `action`     VARCHAR(100)    NOT NULL COMMENT 'Ex: client.create, token.revoke',
    `entity`     VARCHAR(50)     DEFAULT NULL,
    `entity_id`  BIGINT UNSIGNED DEFAULT NULL,
    `payload`    JSON            DEFAULT NULL,
    `ip`         VARCHAR(45)     DEFAULT NULL,
    `ua`         VARCHAR(500)    DEFAULT NULL,
    `created_at` TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_audit_user`    (`user_id`),
    KEY `idx_audit_action`  (`action`),
    KEY `idx_audit_created` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ──────────────────────────────────────────────────────────────
-- CACHE DE IA
-- ──────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `ai_cache` (
    `cache_key`  VARCHAR(64)  NOT NULL,
    `content`    LONGTEXT     NOT NULL,
    `expires_at` TIMESTAMP    NOT NULL,
    `created_at` TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`cache_key`),
    KEY `idx_ai_cache_expires` (`expires_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ──────────────────────────────────────────────────────────────
-- IMPORT JOBS
-- ──────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `import_jobs` (
    `id`          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `type`        ENUM('states','cities','neighborhoods','keywords') NOT NULL,
    `project_id`  BIGINT UNSIGNED DEFAULT NULL,
    `filename`    VARCHAR(255)    NOT NULL,
    `total_lines` INT UNSIGNED    DEFAULT 0,
    `inserted`    INT UNSIGNED    DEFAULT 0,
    `updated`     INT UNSIGNED    DEFAULT 0,
    `errors_json` LONGTEXT        DEFAULT NULL,
    `status`      ENUM('processing','done','failed') NOT NULL DEFAULT 'processing',
    `created_by`  BIGINT UNSIGNED DEFAULT NULL,
    `created_at`  TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `finished_at` TIMESTAMP       DEFAULT NULL,
    PRIMARY KEY (`id`),
    KEY `idx_import_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ──────────────────────────────────────────────────────────────
-- ESTADOS BRASILEIROS (seed)
-- ──────────────────────────────────────────────────────────────
INSERT IGNORE INTO `states` (`name`, `uf`, `slug`, `ibge_code`) VALUES
('Acre','AC','acre','12'),('Alagoas','AL','alagoas','27'),('Amapá','AP','amapa','16'),
('Amazonas','AM','amazonas','13'),('Bahia','BA','bahia','29'),('Ceará','CE','ceara','23'),
('Distrito Federal','DF','distrito-federal','53'),('Espírito Santo','ES','espirito-santo','32'),
('Goiás','GO','goias','52'),('Maranhão','MA','maranhao','21'),('Mato Grosso','MT','mato-grosso','51'),
('Mato Grosso do Sul','MS','mato-grosso-do-sul','50'),('Minas Gerais','MG','minas-gerais','31'),
('Pará','PA','para','15'),('Paraíba','PB','paraiba','25'),('Paraná','PR','parana','41'),
('Pernambuco','PE','pernambuco','26'),('Piauí','PI','piaui','22'),
('Rio de Janeiro','RJ','rio-de-janeiro','33'),('Rio Grande do Norte','RN','rio-grande-do-norte','24'),
('Rio Grande do Sul','RS','rio-grande-do-sul','43'),('Rondônia','RO','rondonia','11'),
('Roraima','RR','roraima','14'),('Santa Catarina','SC','santa-catarina','42'),
('São Paulo','SP','sao-paulo','35'),('Sergipe','SE','sergipe','28'),('Tocantins','TO','tocantins','17');

SET foreign_key_checks = 1;
-- ============================================================
-- Ação Net pSEO Hub — Templates de Cliente
-- Adicionar ao schema.sql existente
-- ============================================================

SET NAMES utf8mb4;
SET foreign_key_checks = 0;

-- ──────────────────────────────────────────────────────────────
-- TEMPLATES DE CLIENTE
-- Armazena o HTML base + configurações visuais por projeto
-- ──────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `client_templates` (
    `id`              BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `project_id`      BIGINT UNSIGNED NOT NULL,
    `name`            VARCHAR(200)    NOT NULL DEFAULT 'Template Principal',

    -- HTML base com placeholders {TOKEN}, {CANONICAL} etc
    -- null = usar template padrão do sistema
    `html_base`       LONGTEXT        DEFAULT NULL,

    -- Identidade visual
    `logo_url`        VARCHAR(500)    DEFAULT NULL,
    `favicon_url`     VARCHAR(500)    DEFAULT NULL,
    `color_primary`   VARCHAR(7)      NOT NULL DEFAULT '#ff0100'
                          COMMENT 'Hex, ex: #ff0100',
    `color_secondary` VARCHAR(7)      NOT NULL DEFAULT '#25D366',
    `color_dark`      VARCHAR(7)      NOT NULL DEFAULT '#071267',
    `font_family`     VARCHAR(100)    NOT NULL DEFAULT 'Plus Jakarta Sans',

    -- Dados de contato
    `whatsapp`        VARCHAR(30)     DEFAULT NULL
                          COMMENT 'Número completo DDI+DDD+número, ex: 5511999999999',
    `phone_display`   VARCHAR(30)     DEFAULT NULL
                          COMMENT 'Número formatado para exibição, ex: (11) 9 9999-9999',
    `email`           VARCHAR(150)    DEFAULT NULL,
    `address`         VARCHAR(300)    DEFAULT NULL,

    -- Textos editáveis globais (valem para todas as páginas do projeto)
    `site_name`       VARCHAR(200)    DEFAULT NULL,
    `tagline`         VARCHAR(300)    DEFAULT NULL,
    `hero_cta_text`   VARCHAR(100)    DEFAULT 'Solicite um Orçamento',
    `hero_badge_text` VARCHAR(150)    DEFAULT NULL,
    `footer_text`     TEXT            DEFAULT NULL,

    -- Rastreamento
    `tracking_head`   TEXT            DEFAULT NULL
                          COMMENT 'Snippets <script> para inserir no <head>: GTM, GA4, Meta Pixel etc',
    `tracking_noscript` TEXT          DEFAULT NULL
                          COMMENT 'Noscript pixels para inserir após <body>',

    -- Interlinks — JSON array de {href, title, description}
    `interlinks`      JSON            DEFAULT NULL,

    -- Schema global do negócio (complementa o schema por página)
    `schema_organization` JSON        DEFAULT NULL,

    -- SEO global
    `meta_title_suffix` VARCHAR(100)  DEFAULT NULL
                          COMMENT 'Ex: " | Real Ponto" — será anexado ao title de cada página',
    `canonical_base`  VARCHAR(255)    DEFAULT NULL
                          COMMENT 'Base para URLs canônicas. Ex: https://realponto.net.br',

    `status`          ENUM('active','draft') NOT NULL DEFAULT 'active',
    `created_at`      TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at`      TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    PRIMARY KEY (`id`),
    KEY `idx_templates_project` (`project_id`, `status`),
    CONSTRAINT `fk_templates_project`
        FOREIGN KEY (`project_id`) REFERENCES `projects` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ──────────────────────────────────────────────────────────────
-- ASSETS DE TEMPLATE (imagens enviadas pelo painel)
-- ──────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `template_assets` (
    `id`          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `template_id` BIGINT UNSIGNED NOT NULL,
    `key`         VARCHAR(50)     NOT NULL
                      COMMENT 'Identificador do asset: logo, favicon, hero_img, etc',
    `filename`    VARCHAR(255)    NOT NULL,
    `url`         VARCHAR(500)    NOT NULL
                      COMMENT 'URL pública para acesso pelo connector',
    `mime_type`   VARCHAR(100)    DEFAULT NULL,
    `size_bytes`  INT UNSIGNED    DEFAULT NULL,
    `created_at`  TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uq_assets_template_key` (`template_id`, `key`),
    CONSTRAINT `fk_assets_template`
        FOREIGN KEY (`template_id`) REFERENCES `client_templates` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SET foreign_key_checks = 1;

-- ── COLUNAS ADICIONAIS (v2) ────────────────────────────────────────────────
-- Execute se já rodou a versão anterior do templates.sql
ALTER TABLE `client_templates`
    ADD COLUMN IF NOT EXISTS `color_secondary_dark`    VARCHAR(7)   DEFAULT '#128C7E'  AFTER `color_secondary`,
    ADD COLUMN IF NOT EXISTS `phone_raw`               VARCHAR(20)  DEFAULT NULL       AFTER `phone_display`,
    ADD COLUMN IF NOT EXISTS `instagram_url`           VARCHAR(255) DEFAULT NULL       AFTER `email`,
    ADD COLUMN IF NOT EXISTS `coverage_img_url`        VARCHAR(500) DEFAULT NULL       AFTER `address`,
    ADD COLUMN IF NOT EXISTS `coverage_cities_extra`   TEXT         DEFAULT NULL       COMMENT 'Uma cidade/bairro por linha — fallback quando banco não retorna resultados',
    ADD COLUMN IF NOT EXISTS `hero_title_highlight`    VARCHAR(150) DEFAULT NULL,
    ADD COLUMN IF NOT EXISTS `hero_subtitle`           TEXT         DEFAULT NULL,
    ADD COLUMN IF NOT EXISTS `hero_feature_1`          VARCHAR(100) DEFAULT NULL,
    ADD COLUMN IF NOT EXISTS `hero_feature_2`          VARCHAR(100) DEFAULT NULL,
    ADD COLUMN IF NOT EXISTS `hero_feature_3`          VARCHAR(100) DEFAULT NULL,
    ADD COLUMN IF NOT EXISTS `form_title`              VARCHAR(150) DEFAULT NULL,
    ADD COLUMN IF NOT EXISTS `form_subtitle`           VARCHAR(300) DEFAULT NULL,
    ADD COLUMN IF NOT EXISTS `form_cta_text`           VARCHAR(100) DEFAULT NULL,
    ADD COLUMN IF NOT EXISTS `form_service_options`    TEXT         DEFAULT NULL       COMMENT 'HTML das <option> do select de serviço',
    ADD COLUMN IF NOT EXISTS `problem_section_subtitle` TEXT        DEFAULT NULL,
    ADD COLUMN IF NOT EXISTS `problems_title`          VARCHAR(150) DEFAULT NULL,
    ADD COLUMN IF NOT EXISTS `problems_list`           TEXT         DEFAULT NULL       COMMENT 'HTML de <li> dos problemas',
    ADD COLUMN IF NOT EXISTS `solutions_title`         VARCHAR(150) DEFAULT NULL,
    ADD COLUMN IF NOT EXISTS `solutions_list`          TEXT         DEFAULT NULL       COMMENT 'HTML de <li> das soluções',
    ADD COLUMN IF NOT EXISTS `benefits_subtitle`       TEXT         DEFAULT NULL,
    ADD COLUMN IF NOT EXISTS `benefits_cards`          LONGTEXT     DEFAULT NULL       COMMENT 'HTML dos cards de benefícios',
    ADD COLUMN IF NOT EXISTS `services_subtitle`       TEXT         DEFAULT NULL,
    ADD COLUMN IF NOT EXISTS `services_cards`          LONGTEXT     DEFAULT NULL       COMMENT 'HTML dos cards de serviços',
    ADD COLUMN IF NOT EXISTS `coverage_subtitle`       TEXT         DEFAULT NULL,
    ADD COLUMN IF NOT EXISTS `testimonials_title`      VARCHAR(200) DEFAULT NULL,
    ADD COLUMN IF NOT EXISTS `testimonials_subtitle`   TEXT         DEFAULT NULL,
    ADD COLUMN IF NOT EXISTS `testimonials_cards`      LONGTEXT     DEFAULT NULL       COMMENT 'HTML dos cards de depoimentos',
    ADD COLUMN IF NOT EXISTS `faq_title`               VARCHAR(200) DEFAULT NULL,
    ADD COLUMN IF NOT EXISTS `faq_subtitle`            TEXT         DEFAULT NULL,
    ADD COLUMN IF NOT EXISTS `faq_items`               LONGTEXT     DEFAULT NULL       COMMENT 'HTML dos itens de FAQ fixos',
    ADD COLUMN IF NOT EXISTS `cta_title`               VARCHAR(200) DEFAULT NULL,
    ADD COLUMN IF NOT EXISTS `cta_subtitle`            TEXT         DEFAULT NULL,
    ADD COLUMN IF NOT EXISTS `footer_about`            TEXT         DEFAULT NULL,
    ADD COLUMN IF NOT EXISTS `footer_links_solutions`  TEXT         DEFAULT NULL       COMMENT 'HTML de <li> do footer',
    ADD COLUMN IF NOT EXISTS `popup_title`             VARCHAR(150) DEFAULT NULL,
    ADD COLUMN IF NOT EXISTS `popup_status`            VARCHAR(50)  DEFAULT NULL,
    ADD COLUMN IF NOT EXISTS `popup_subtitle`          TEXT         DEFAULT NULL,
    ADD COLUMN IF NOT EXISTS `tracker_url`             VARCHAR(500) DEFAULT NULL       COMMENT 'URL do salvar_log.php configurável por projeto';
-- ============================================================
-- Ação Net pSEO Hub — Schema: Localidades + Keywords
-- MySQL 8.0+ | utf8mb4_unicode_ci
-- ============================================================

SET NAMES utf8mb4;
SET foreign_key_checks = 0;

-- ──────────────────────────────────────────────
-- ESTADOS
-- ──────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `states` (
    `id`         BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `name`       VARCHAR(150)    NOT NULL,
    `uf`         CHAR(2)         NOT NULL,
    `slug`       VARCHAR(160)    NOT NULL,
    `ibge_code`  VARCHAR(10)     DEFAULT NULL,
    `created_at` TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uq_states_uf`   (`uf`),
    UNIQUE KEY `uq_states_slug` (`slug`),
    FULLTEXT KEY `ft_states_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ──────────────────────────────────────────────
-- CIDADES
-- Particionamento por state_id para suporte a
-- milhões de registros
-- ──────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `cities` (
    `id`         BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `state_id`   BIGINT UNSIGNED NOT NULL,
    `name`       VARCHAR(200)    NOT NULL,
    `slug`       VARCHAR(220)    NOT NULL,
    `ibge_code`  VARCHAR(10)     DEFAULT NULL,
    `lat`        DECIMAL(10,7)   DEFAULT NULL,
    `lng`        DECIMAL(10,7)   DEFAULT NULL,
    `created_at` TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uq_cities_state_slug` (`state_id`, `slug`),
    KEY `idx_cities_state`            (`state_id`),
    KEY `idx_cities_ibge`             (`ibge_code`),
    FULLTEXT KEY `ft_cities_name`     (`name`),
    CONSTRAINT `fk_cities_state` FOREIGN KEY (`state_id`) REFERENCES `states` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ──────────────────────────────────────────────
-- BAIRROS
-- ──────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `neighborhoods` (
    `id`         BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `city_id`    BIGINT UNSIGNED NOT NULL,
    `name`       VARCHAR(200)    NOT NULL,
    `slug`       VARCHAR(220)    NOT NULL,
    `zone`       VARCHAR(100)    DEFAULT NULL,
    `created_at` TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uq_neighborhoods_city_slug` (`city_id`, `slug`),
    KEY `idx_neighborhoods_city`            (`city_id`),
    FULLTEXT KEY `ft_neighborhoods_name`    (`name`),
    CONSTRAINT `fk_neighborhoods_city` FOREIGN KEY (`city_id`) REFERENCES `cities` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ──────────────────────────────────────────────
-- PALAVRAS-CHAVE (por projeto)
-- ──────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `keywords` (
    `id`         BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `project_id` BIGINT UNSIGNED NOT NULL,
    `keyword`    VARCHAR(500)    NOT NULL,
    `volume`     INT UNSIGNED    DEFAULT NULL COMMENT 'Volume mensal de buscas',
    `difficulty` TINYINT UNSIGNED DEFAULT NULL COMMENT '0-100',
    `cpc`        DECIMAL(10,2)   DEFAULT NULL COMMENT 'Custo por clique em BRL',
    `priority`   TINYINT UNSIGNED NOT NULL DEFAULT 5 COMMENT '1-10',
    `created_at` TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uq_keywords_project_kw` (`project_id`, `keyword`(191)),
    KEY `idx_keywords_project`          (`project_id`),
    KEY `idx_keywords_priority`         (`project_id`, `priority`),
    KEY `idx_keywords_volume`           (`project_id`, `volume`),
    FULLTEXT KEY `ft_keywords_kw`       (`keyword`),
    CONSTRAINT `fk_keywords_project` FOREIGN KEY (`project_id`) REFERENCES `projects` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ──────────────────────────────────────────────
-- IMPORT JOBS (rastrear progresso de imports)
-- ──────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `import_jobs` (
    `id`          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `type`        ENUM('states','cities','neighborhoods','keywords') NOT NULL,
    `project_id`  BIGINT UNSIGNED DEFAULT NULL,
    `filename`    VARCHAR(255)    NOT NULL,
    `total_lines` INT UNSIGNED    DEFAULT 0,
    `inserted`    INT UNSIGNED    DEFAULT 0,
    `updated`     INT UNSIGNED    DEFAULT 0,
    `errors_json` LONGTEXT        DEFAULT NULL,
    `status`      ENUM('processing','done','failed') NOT NULL DEFAULT 'processing',
    `created_by`  BIGINT UNSIGNED DEFAULT NULL,
    `created_at`  TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `finished_at` TIMESTAMP       DEFAULT NULL,
    PRIMARY KEY (`id`),
    KEY `idx_import_status` (`status`),
    KEY `idx_import_type`   (`type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ──────────────────────────────────────────────
-- DADOS INICIAIS — 27 estados brasileiros
-- ──────────────────────────────────────────────
INSERT IGNORE INTO `states` (`name`, `uf`, `slug`, `ibge_code`) VALUES
('Acre',                'AC', 'acre',                '12'),
('Alagoas',             'AL', 'alagoas',             '27'),
('Amapá',               'AP', 'amapa',               '16'),
('Amazonas',            'AM', 'amazonas',            '13'),
('Bahia',               'BA', 'bahia',               '29'),
('Ceará',               'CE', 'ceara',               '23'),
('Distrito Federal',    'DF', 'distrito-federal',    '53'),
('Espírito Santo',      'ES', 'espirito-santo',      '32'),
('Goiás',               'GO', 'goias',               '52'),
('Maranhão',            'MA', 'maranhao',            '21'),
('Mato Grosso',         'MT', 'mato-grosso',         '51'),
('Mato Grosso do Sul',  'MS', 'mato-grosso-do-sul',  '50'),
('Minas Gerais',        'MG', 'minas-gerais',        '31'),
('Pará',                'PA', 'para',                '15'),
('Paraíba',             'PB', 'paraiba',             '25'),
('Paraná',              'PR', 'parana',              '41'),
('Pernambuco',          'PE', 'pernambuco',          '26'),
('Piauí',               'PI', 'piaui',               '22'),
('Rio de Janeiro',      'RJ', 'rio-de-janeiro',      '33'),
('Rio Grande do Norte', 'RN', 'rio-grande-do-norte', '24'),
('Rio Grande do Sul',   'RS', 'rio-grande-do-sul',   '43'),
('Rondônia',            'RO', 'rondonia',            '11'),
('Roraima',             'RR', 'roraima',             '14'),
('Santa Catarina',      'SC', 'santa-catarina',      '42'),
('São Paulo',           'SP', 'sao-paulo',           '35'),
('Sergipe',             'SE', 'sergipe',             '28'),
('Tocantins',           'TO', 'tocantins',           '17');

SET foreign_key_checks = 1;
-- ============================================================
-- Ação Net pSEO Hub — Módulos de Conversão e Operacional
-- Adicionar ao schema.sql existente
-- MySQL 8.0+ | utf8mb4_unicode_ci
-- ============================================================

SET NAMES utf8mb4;
SET foreign_key_checks = 0;

-- ──────────────────────────────────────────────────────────────
-- LEADS — capturados pelo formulário nativo
-- ──────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `leads` (
    `id`           BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `project_id`   BIGINT UNSIGNED NOT NULL,
    `client_id`    BIGINT UNSIGNED NOT NULL,
    `name`         VARCHAR(150)    DEFAULT NULL,
    `email`        VARCHAR(150)    DEFAULT NULL,
    `phone`        VARCHAR(30)     DEFAULT NULL,
    `company`      VARCHAR(200)    DEFAULT NULL,
    `service`      VARCHAR(150)    DEFAULT NULL  COMMENT 'Serviço selecionado no form',
    `message`      TEXT            DEFAULT NULL,
    `source_url`   VARCHAR(500)    DEFAULT NULL  COMMENT 'Slug da página onde captou',
    `source_vars`  JSON            DEFAULT NULL  COMMENT 'Variáveis pSEO da página: {bairro, cidade}',
    `utm_source`   VARCHAR(100)    DEFAULT NULL,
    `utm_medium`   VARCHAR(100)    DEFAULT NULL,
    `utm_campaign` VARCHAR(100)    DEFAULT NULL,
    `ip`           VARCHAR(45)     DEFAULT NULL,
    `user_agent`   VARCHAR(500)    DEFAULT NULL,
    `status`       ENUM('new','contacted','qualified','lost') NOT NULL DEFAULT 'new',
    `ab_variant`   VARCHAR(10)     DEFAULT NULL  COMMENT 'Variante A/B que gerou o lead',
    `webhook_sent` TINYINT(1)      NOT NULL DEFAULT 0,
    `webhook_at`   TIMESTAMP       DEFAULT NULL,
    `created_at`   TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at`   TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_leads_project`    (`project_id`),
    KEY `idx_leads_client`     (`client_id`),
    KEY `idx_leads_status`     (`status`),
    KEY `idx_leads_created`    (`created_at`),
    KEY `idx_leads_source`     (`source_url`(191)),
    CONSTRAINT `fk_leads_project` FOREIGN KEY (`project_id`) REFERENCES `projects` (`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_leads_client`  FOREIGN KEY (`client_id`)  REFERENCES `clients` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ──────────────────────────────────────────────────────────────
-- A/B TESTS — experimentos de CTA e título por projeto
-- ──────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `ab_tests` (
    `id`           BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `project_id`   BIGINT UNSIGNED NOT NULL,
    `name`         VARCHAR(200)    NOT NULL,
    `element`      ENUM('hero_title','h1','cta_text','cta_button','form_title','popup_title','meta_desc') NOT NULL,
    `status`       ENUM('draft','running','paused','finished') NOT NULL DEFAULT 'draft',
    `winner`       CHAR(1)         DEFAULT NULL COMMENT 'A ou B',
    `started_at`   TIMESTAMP       DEFAULT NULL,
    `finished_at`  TIMESTAMP       DEFAULT NULL,
    `created_at`   TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_ab_project` (`project_id`, `status`),
    CONSTRAINT `fk_ab_project` FOREIGN KEY (`project_id`) REFERENCES `projects` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `ab_variants` (
    `id`           BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `test_id`      BIGINT UNSIGNED NOT NULL,
    `variant`      CHAR(1)         NOT NULL COMMENT 'A ou B',
    `content`      TEXT            NOT NULL COMMENT 'Texto desta variante',
    `impressions`  INT UNSIGNED    NOT NULL DEFAULT 0,
    `clicks`       INT UNSIGNED    NOT NULL DEFAULT 0,
    `leads`        INT UNSIGNED    NOT NULL DEFAULT 0,
    `ctr`          DECIMAL(6,3)    NOT NULL DEFAULT 0,
    `conversion`   DECIMAL(6,3)    NOT NULL DEFAULT 0,
    PRIMARY KEY (`id`),
    KEY `idx_variant_test` (`test_id`),
    CONSTRAINT `fk_variant_test` FOREIGN KEY (`test_id`) REFERENCES `ab_tests` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `ab_events` (
    `id`           BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `test_id`      BIGINT UNSIGNED NOT NULL,
    `variant_id`   BIGINT UNSIGNED NOT NULL,
    `event`        ENUM('impression','click','lead') NOT NULL,
    `session_hash` VARCHAR(64)     DEFAULT NULL,
    `source_url`   VARCHAR(500)    DEFAULT NULL,
    `created_at`   TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_ab_events_test`    (`test_id`, `event`),
    KEY `idx_ab_events_created` (`created_at`),
    CONSTRAINT `fk_ab_events_test`    FOREIGN KEY (`test_id`)    REFERENCES `ab_tests`    (`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_ab_events_variant` FOREIGN KEY (`variant_id`) REFERENCES `ab_variants` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ──────────────────────────────────────────────────────────────
-- RANKING MONITOR — posição por keyword/página
-- ──────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `ranking_checks` (
    `id`           BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `project_id`   BIGINT UNSIGNED NOT NULL,
    `keyword`      VARCHAR(500)    NOT NULL,
    `page_slug`    VARCHAR(500)    DEFAULT NULL,
    `target_url`   VARCHAR(500)    DEFAULT NULL,
    `engine`       ENUM('google','bing') NOT NULL DEFAULT 'google',
    `country`      VARCHAR(5)      NOT NULL DEFAULT 'br',
    `language`     VARCHAR(5)      NOT NULL DEFAULT 'pt',
    `check_freq`   ENUM('daily','weekly','monthly') NOT NULL DEFAULT 'weekly',
    `alert_drop`   INT UNSIGNED    DEFAULT 5  COMMENT 'Alertar se cair mais de N posições',
    `status`       ENUM('active','paused') NOT NULL DEFAULT 'active',
    `created_at`   TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_ranking_project` (`project_id`),
    CONSTRAINT `fk_ranking_project` FOREIGN KEY (`project_id`) REFERENCES `projects` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `ranking_history` (
    `id`           BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `check_id`     BIGINT UNSIGNED NOT NULL,
    `position`     SMALLINT UNSIGNED DEFAULT NULL COMMENT 'NULL = não encontrado no top 100',
    `url_found`    VARCHAR(500)    DEFAULT NULL,
    `checked_at`   TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_rh_check`   (`check_id`),
    KEY `idx_rh_checked` (`checked_at`),
    CONSTRAINT `fk_rh_check` FOREIGN KEY (`check_id`) REFERENCES `ranking_checks` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ──────────────────────────────────────────────────────────────
-- ALERTAS — notificações automáticas
-- ──────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `alert_rules` (
    `id`           BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `client_id`    BIGINT UNSIGNED NOT NULL,
    `project_id`   BIGINT UNSIGNED DEFAULT NULL COMMENT 'NULL = aplica a todos os projetos',
    `type`         ENUM('token_offline','ranking_drop','error_rate','lead_spike','payment_due','indexing_failed') NOT NULL,
    `threshold`    INT             DEFAULT NULL COMMENT 'Valor numérico do gatilho',
    `channels`     JSON            NOT NULL     COMMENT '["email","whatsapp","webhook"]',
    `recipients`   JSON            NOT NULL     COMMENT '[{"type":"email","value":"adm@..."}]',
    `is_active`    TINYINT(1)      NOT NULL DEFAULT 1,
    `last_fired_at`TIMESTAMP       DEFAULT NULL,
    `created_at`   TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_alert_client`  (`client_id`),
    KEY `idx_alert_project` (`project_id`),
    CONSTRAINT `fk_alert_client`  FOREIGN KEY (`client_id`)  REFERENCES `clients`  (`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_alert_project` FOREIGN KEY (`project_id`) REFERENCES `projects` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `alert_log` (
    `id`           BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `rule_id`      BIGINT UNSIGNED NOT NULL,
    `message`      TEXT            NOT NULL,
    `channel`      VARCHAR(20)     NOT NULL,
    `recipient`    VARCHAR(200)    DEFAULT NULL,
    `sent`         TINYINT(1)      NOT NULL DEFAULT 0,
    `sent_at`      TIMESTAMP       DEFAULT NULL,
    `error`        VARCHAR(500)    DEFAULT NULL,
    `created_at`   TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_alert_log_rule` (`rule_id`),
    CONSTRAINT `fk_alert_log_rule` FOREIGN KEY (`rule_id`) REFERENCES `alert_rules` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ──────────────────────────────────────────────────────────────
-- WEBHOOKS — integrações com CRMs externos
-- ──────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `webhooks` (
    `id`           BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `project_id`   BIGINT UNSIGNED NOT NULL,
    `name`         VARCHAR(200)    NOT NULL,
    `url`          VARCHAR(500)    NOT NULL,
    `secret`       VARCHAR(64)     DEFAULT NULL COMMENT 'HMAC secret para assinar payload',
    `method`       ENUM('POST','PUT') NOT NULL DEFAULT 'POST',
    `headers`      JSON            DEFAULT NULL COMMENT 'Headers extras ex: {"Authorization":"Bearer xxx"}',
    `events`       JSON            NOT NULL     COMMENT '["lead.new","lead.qualified"]',
    `crm`          ENUM('custom','rd_station','hubspot','kommo','pipedrive','activecamp') DEFAULT 'custom',
    `crm_config`   JSON            DEFAULT NULL COMMENT 'Config específica do CRM',
    `retry_count`  TINYINT         NOT NULL DEFAULT 3,
    `is_active`    TINYINT(1)      NOT NULL DEFAULT 1,
    `last_success_at` TIMESTAMP    DEFAULT NULL,
    `last_error_at`   TIMESTAMP    DEFAULT NULL,
    `created_at`   TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_webhook_project` (`project_id`),
    CONSTRAINT `fk_webhook_project` FOREIGN KEY (`project_id`) REFERENCES `projects` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `webhook_deliveries` (
    `id`           BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `webhook_id`   BIGINT UNSIGNED NOT NULL,
    `lead_id`      BIGINT UNSIGNED DEFAULT NULL,
    `event`        VARCHAR(50)     NOT NULL,
    `payload`      LONGTEXT        DEFAULT NULL,
    `response_code`SMALLINT        DEFAULT NULL,
    `response_body`TEXT            DEFAULT NULL,
    `attempt`      TINYINT         NOT NULL DEFAULT 1,
    `status`       ENUM('pending','success','failed','retrying') NOT NULL DEFAULT 'pending',
    `sent_at`      TIMESTAMP       DEFAULT NULL,
    `created_at`   TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_wd_webhook` (`webhook_id`),
    KEY `idx_wd_status`  (`status`),
    CONSTRAINT `fk_wd_webhook` FOREIGN KEY (`webhook_id`) REFERENCES `webhooks` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ──────────────────────────────────────────────────────────────
-- RELATÓRIOS PDF — controle de geração automática
-- ──────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `report_schedules` (
    `id`           BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `client_id`    BIGINT UNSIGNED NOT NULL,
    `project_id`   BIGINT UNSIGNED DEFAULT NULL,
    `frequency`    ENUM('weekly','monthly','quarterly') NOT NULL DEFAULT 'monthly',
    `send_day`     TINYINT         NOT NULL DEFAULT 1 COMMENT 'Dia do mês (1-28) ou dia da semana (0=dom)',
    `recipients`   JSON            NOT NULL COMMENT '["email1@...","email2@..."]',
    `include_leads`TINYINT(1)      NOT NULL DEFAULT 1,
    `include_gsc`  TINYINT(1)      NOT NULL DEFAULT 1,
    `include_ranking`TINYINT(1)    NOT NULL DEFAULT 1,
    `is_active`    TINYINT(1)      NOT NULL DEFAULT 1,
    `last_sent_at` TIMESTAMP       DEFAULT NULL,
    `created_at`   TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_report_client` (`client_id`),
    CONSTRAINT `fk_report_client` FOREIGN KEY (`client_id`) REFERENCES `clients` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `report_history` (
    `id`           BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `schedule_id`  BIGINT UNSIGNED NOT NULL,
    `period_start` DATE            NOT NULL,
    `period_end`   DATE            NOT NULL,
    `file_path`    VARCHAR(500)    DEFAULT NULL,
    `sent_to`      JSON            DEFAULT NULL,
    `status`       ENUM('generating','sent','failed') NOT NULL DEFAULT 'generating',
    `created_at`   TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_rh_schedule` (`schedule_id`),
    CONSTRAINT `fk_rh_schedule` FOREIGN KEY (`schedule_id`) REFERENCES `report_schedules` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ──────────────────────────────────────────────────────────────
-- CTA DINÂMICO — variações por horário/contexto
-- ──────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `dynamic_ctas` (
    `id`           BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `project_id`   BIGINT UNSIGNED NOT NULL,
    `name`         VARCHAR(200)    NOT NULL,
    `conditions`   JSON            NOT NULL  COMMENT 'Regras: hora, dia semana, URL match',
    `cta_text`     VARCHAR(200)    NOT NULL,
    `cta_sub`      VARCHAR(300)    DEFAULT NULL,
    `cta_color`    VARCHAR(7)      DEFAULT NULL,
    `urgency_text` VARCHAR(200)    DEFAULT NULL COMMENT 'Ex: "Últimas 3 vagas hoje"',
    `priority`     INT             NOT NULL DEFAULT 0,
    `is_active`    TINYINT(1)      NOT NULL DEFAULT 1,
    `created_at`   TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_dyn_cta_project` (`project_id`),
    CONSTRAINT `fk_dyn_cta_project` FOREIGN KEY (`project_id`) REFERENCES `projects` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SET foreign_key_checks = 1;
-- ============================================================
-- Ação Net pSEO Hub — Geração em Massa e A/B Event API
-- Adicionar ao schema existente
-- ============================================================

SET NAMES utf8mb4;
SET foreign_key_checks = 0;

-- ──────────────────────────────────────────────────────────────
-- JOBS DE GERAÇÃO EM MASSA
-- ──────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `bulk_generation_jobs` (
    `id`                  BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `project_id`          BIGINT UNSIGNED NOT NULL,
    `url_structure_id`    BIGINT UNSIGNED NOT NULL,
    `total_to_generate`   INT UNSIGNED    NOT NULL DEFAULT 0,
    `generated_count`     INT UNSIGNED    NOT NULL DEFAULT 0,
    `error_count`         INT UNSIGNED    NOT NULL DEFAULT 0,
    `priority`            TINYINT UNSIGNED NOT NULL DEFAULT 5,
    `status`              ENUM('pending','running','done','failed','paused') NOT NULL DEFAULT 'pending',
    `error`               VARCHAR(500)    DEFAULT NULL,
    `requested_by`        BIGINT UNSIGNED DEFAULT NULL,
    `started_at`          TIMESTAMP       DEFAULT NULL,
    `finished_at`         TIMESTAMP       DEFAULT NULL,
    `created_at`          TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at`          TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_bgj_project` (`project_id`),
    KEY `idx_bgj_status`  (`status`),
    CONSTRAINT `fk_bgj_project`   FOREIGN KEY (`project_id`)       REFERENCES `projects`        (`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_bgj_structure` FOREIGN KEY (`url_structure_id`) REFERENCES `url_structures`  (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ──────────────────────────────────────────────────────────────
-- SERVICE TYPES — catálogo de serviços por nicho
-- Usado como variável {servico} nas URLs
-- ──────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `service_types` (
    `id`         BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `project_id` BIGINT UNSIGNED NOT NULL,
    `name`       VARCHAR(200)    NOT NULL,
    `slug`       VARCHAR(220)    NOT NULL,
    `category`   VARCHAR(100)    DEFAULT NULL,
    `priority`   TINYINT UNSIGNED NOT NULL DEFAULT 5,
    `created_at` TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uq_st_project_slug` (`project_id`, `slug`),
    KEY `idx_st_project` (`project_id`),
    CONSTRAINT `fk_st_project` FOREIGN KEY (`project_id`) REFERENCES `projects` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SET foreign_key_checks = 1;