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

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;

-- ──────────────────────────────────────────────────────────────
-- 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;

-- ──────────────────────────────────────────────────────────────
-- 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;
