-- SEO Growth Intelligence Platform — Advanced tables

-- Keyword tracking & research
CREATE TABLE IF NOT EXISTS seo_growth_keywords (
    id           INT AUTO_INCREMENT PRIMARY KEY,
    keyword      VARCHAR(300) NOT NULL,
    service      VARCHAR(200),
    intent       ENUM('informational','commercial','transactional','navigational') DEFAULT 'informational',
    difficulty   TINYINT UNSIGNED DEFAULT 5,   -- 1-10 AI estimate
    volume_est   VARCHAR(50) DEFAULT 'Unknown',
    type         ENUM('head','long-tail','local','brand','question') DEFAULT 'long-tail',
    page_key     VARCHAR(300) DEFAULT NULL,     -- linked CMS page if any
    status       ENUM('researched','tracking','ranked','ignored') DEFAULT 'researched',
    notes        TEXT,
    created_at   DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_service (service(100)),
    INDEX idx_status  (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Competitor profiles
CREATE TABLE IF NOT EXISTS seo_growth_competitors (
    id           INT AUTO_INCREMENT PRIMARY KEY,
    domain       VARCHAR(300) NOT NULL UNIQUE,
    name         VARCHAR(200),
    services_json TEXT,       -- JSON array of their services
    keywords_json TEXT,       -- JSON array of {keyword,difficulty}
    gaps_json    TEXT,        -- JSON array of content gap opportunities
    backlinks_json TEXT,      -- JSON array of their backlink sources
    score        TINYINT DEFAULT 0,  -- overall threat score 0-100
    notes        TEXT,
    analysed_at  DATETIME DEFAULT NULL,
    created_at   DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Backlink opportunity radar
CREATE TABLE IF NOT EXISTS seo_growth_backlinks (
    id           INT AUTO_INCREMENT PRIMARY KEY,
    domain       VARCHAR(300) NOT NULL,
    url          VARCHAR(600),
    type         ENUM('directory','publication','forum','partner','press','social','blog') DEFAULT 'directory',
    da_est       TINYINT DEFAULT 0,   -- domain authority estimate
    service      VARCHAR(200),
    contact_email VARCHAR(200),
    status       ENUM('found','submitted','published','rejected') DEFAULT 'found',
    notes        TEXT,
    created_at   DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_status (status),
    INDEX idx_type   (type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- GEO (AI search) answer blocks cached per page
CREATE TABLE IF NOT EXISTS seo_growth_geo_blocks (
    id           INT AUTO_INCREMENT PRIMARY KEY,
    page_key     VARCHAR(300) NOT NULL,
    block_type   ENUM('answer','entity','faq','citation','snippet') DEFAULT 'answer',
    question     TEXT,
    answer       TEXT,
    injected     TINYINT(1) DEFAULT 0,
    created_at   DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_page (page_key(200))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Extend seo_growth_pages type to include comparison, pillar, whitepaper
ALTER TABLE seo_growth_pages
    MODIFY COLUMN type ENUM(
        'industry','location','authority','faq-page','case-study','tender',
        'comparison','pillar','whitepaper'
    ) NOT NULL;