-- SEO AI Database Schema
CREATE DATABASE IF NOT EXISTS seoai_db;
USE seoai_db;

-- Brands table for SEO analysis
CREATE TABLE brands (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    website VARCHAR(255),
    description TEXT,
    industry VARCHAR(100),
    target_keywords TEXT,
    competitor_websites TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- SEO Analysis Reports
CREATE TABLE seo_reports (
    id INT AUTO_INCREMENT PRIMARY KEY,
    brand_id INT,
    report_type ENUM('health_check', 'competitor_analysis', 'keyword_analysis', 'content_audit') NOT NULL,
    title VARCHAR(255) NOT NULL,
    content LONGTEXT,
    status ENUM('pending', 'processing', 'completed', 'failed') DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (brand_id) REFERENCES brands(id) ON DELETE CASCADE
);

-- Content Planning
CREATE TABLE content_plans (
    id INT AUTO_INCREMENT PRIMARY KEY,
    brand_id INT,
    title VARCHAR(255) NOT NULL,
    content_type ENUM('blog_post', 'landing_page', 'product_page', 'service_page') NOT NULL,
    target_keywords TEXT,
    content_outline TEXT,
    status ENUM('draft', 'in_progress', 'published', 'scheduled') DEFAULT 'draft',
    scheduled_date DATETIME,
    published_date DATETIME,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (brand_id) REFERENCES brands(id) ON DELETE CASCADE
);

-- Generated Content
CREATE TABLE generated_content (
    id INT AUTO_INCREMENT PRIMARY KEY,
    content_plan_id INT,
    title VARCHAR(255) NOT NULL,
    content LONGTEXT,
    meta_description TEXT,
    featured_image VARCHAR(255),
    word_count INT,
    readability_score DECIMAL(3,1),
    seo_score DECIMAL(3,1),
    status ENUM('draft', 'review', 'approved', 'published') DEFAULT 'draft',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (content_plan_id) REFERENCES content_plans(id) ON DELETE CASCADE
);

-- SEO Tracking
CREATE TABLE seo_tracking (
    id INT AUTO_INCREMENT PRIMARY KEY,
    brand_id INT,
    keyword VARCHAR(255) NOT NULL,
    current_ranking INT,
    previous_ranking INT,
    search_volume INT,
    difficulty_score DECIMAL(3,1),
    tracking_date DATE NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (brand_id) REFERENCES brands(id) ON DELETE CASCADE,
    UNIQUE KEY unique_brand_keyword_date (brand_id, keyword, tracking_date)
);

-- System Settings
CREATE TABLE system_settings (
    id INT AUTO_INCREMENT PRIMARY KEY,
    setting_key VARCHAR(100) UNIQUE NOT NULL,
    setting_value TEXT,
    description TEXT,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Insert default settings
INSERT INTO system_settings (setting_key, setting_value, description) VALUES
('gpt_api_key', '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', 'OpenAI GPT API Key'),
('site_title', 'SEO AI Automation System', 'Website Title'),
('site_description', 'Professional SEO automation and content generation platform', 'Website Description');
