-- Globe AI v2.0 SQL installation script
-- Creates database and tables for Globe AI module

CREATE DATABASE IF NOT EXISTS `globe_ai_db` CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;
USE `globe_ai_db`;

-- Categories
CREATE TABLE IF NOT EXISTS `ai_categories` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(191) NOT NULL,
  `slug` VARCHAR(191) NOT NULL UNIQUE,
  `description` TEXT,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Template groups
CREATE TABLE IF NOT EXISTS `ai_template_groups` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(191) NOT NULL,
  `description` TEXT,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Templates
CREATE TABLE IF NOT EXISTS `ai_templates` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `group_id` INT UNSIGNED NULL,
  `category_id` INT UNSIGNED NULL,
  `title` VARCHAR(255) NOT NULL,
  `content` TEXT NOT NULL,
  `weight` INT DEFAULT 10,
  `active` TINYINT(1) DEFAULT 1,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  INDEX (`group_id`),
  INDEX (`category_id`),
  CONSTRAINT `fk_templates_group` FOREIGN KEY (`group_id`) REFERENCES `ai_template_groups`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Template variables
CREATE TABLE IF NOT EXISTS `ai_template_variables` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `template_id` INT UNSIGNED NOT NULL,
  `name` VARCHAR(191) NOT NULL,
  `example` VARCHAR(255) NULL,
  INDEX (`template_id`),
  CONSTRAINT `fk_template_vars` FOREIGN KEY (`template_id`) REFERENCES `ai_templates`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Keywords
CREATE TABLE IF NOT EXISTS `ai_keywords` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `category_id` INT UNSIGNED NULL,
  `keyword` VARCHAR(255) NOT NULL,
  `weight` INT DEFAULT 1,
  INDEX (`category_id`),
  CONSTRAINT `fk_keywords_category` FOREIGN KEY (`category_id`) REFERENCES `ai_categories`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Generated content
CREATE TABLE IF NOT EXISTS `ai_generated` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `user_id` INT UNSIGNED NULL,
  `type` VARCHAR(50) NOT NULL,
  `input` JSON NULL,
  `output` LONGTEXT NOT NULL,
  `credits_used` INT DEFAULT 0,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Logs
CREATE TABLE IF NOT EXISTS `ai_logs` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `user_id` INT UNSIGNED NULL,
  `action` VARCHAR(100) NOT NULL,
  `type` VARCHAR(50) NULL,
  `meta` JSON NULL,
  `ip_address` VARCHAR(45) NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Credits
CREATE TABLE IF NOT EXISTS `ai_credits` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `user_id` INT UNSIGNED NOT NULL,
  `credits` INT DEFAULT 0,
  `month` YEAR NULL,
  `updated_at` TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  INDEX (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Admin users
CREATE TABLE IF NOT EXISTS `ai_admin_users` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `username` VARCHAR(100) NOT NULL UNIQUE,
  `password_hash` VARCHAR(255) NOT NULL,
  `email` VARCHAR(191) NOT NULL,
  `display_name` VARCHAR(191) DEFAULT NULL,
  `role` VARCHAR(50) NOT NULL DEFAULT 'admin',
  `active` TINYINT(1) DEFAULT 1,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- API keys
CREATE TABLE IF NOT EXISTS `ai_api_keys` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(191) NOT NULL,
  `api_key` VARCHAR(191) NOT NULL UNIQUE,
  `user_id` INT UNSIGNED NULL,
  `active` TINYINT(1) DEFAULT 1,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `last_used_at` TIMESTAMP NULL DEFAULT NULL,
  INDEX (`active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Platform service registry
CREATE TABLE IF NOT EXISTS `ai_services` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `slug` VARCHAR(191) NOT NULL UNIQUE,
  `name` VARCHAR(191) NOT NULL,
  `type` VARCHAR(100) NOT NULL,
  `endpoint` VARCHAR(255) NOT NULL,
  `metadata` JSON NULL,
  `active` TINYINT(1) DEFAULT 1,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `ai_modules` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `slug` VARCHAR(191) NOT NULL UNIQUE,
  `name` VARCHAR(191) NOT NULL,
  `description` TEXT NULL,
  `service_slug` VARCHAR(191) NULL,
  `metadata` JSON NULL,
  `active` TINYINT(1) DEFAULT 1,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  INDEX (`service_slug`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `ai_provider_registry` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `slug` VARCHAR(191) NOT NULL UNIQUE,
  `name` VARCHAR(191) NOT NULL,
  `capabilities` JSON NULL,
  `config` JSON NULL,
  `active` TINYINT(1) DEFAULT 1,
  `default_provider` TINYINT(1) DEFAULT 0,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `ai_health_checks` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `service_slug` VARCHAR(191) NULL,
  `module_slug` VARCHAR(191) NULL,
  `status` VARCHAR(50) NOT NULL,
  `message` TEXT NULL,
  `metadata` JSON NULL,
  `checked_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX (`service_slug`),
  INDEX (`module_slug`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT IGNORE INTO ai_services (slug, name, type, endpoint, metadata, active) VALUES
('content_generation', 'Content Generation', 'platform', '/api/gateway.php', JSON_OBJECT('description', 'Core content generation routing'), 1),
('platform', 'Platform Services', 'platform', '/api/gateway.php', JSON_OBJECT('description', 'Platform level health, diagnostics and registry'), 1);

INSERT IGNORE INTO ai_provider_registry (slug, name, capabilities, config, active, default_provider) VALUES
('template', 'Template Provider', JSON_ARRAY('business','gig','hashtag','job','post','product','seo','slogan'), JSON_OBJECT(), 1, 1),
('local_llm', 'Local LLM Provider', JSON_ARRAY('llm','text'), JSON_OBJECT(), 0, 0);

INSERT IGNORE INTO ai_modules (slug, name, description, service_slug, metadata, active) VALUES
('business', 'Business Generator', 'Generate business profile content', 'content_generation', JSON_OBJECT('endpoint','/api/business.php'), 1),
('gig', 'Gig Generator', 'Generate gig and freelancer content', 'content_generation', JSON_OBJECT('endpoint','/api/gig.php'), 1),
('hashtag', 'Hashtag Generator', 'Generate hashtags and SEO keywords', 'content_generation', JSON_OBJECT('endpoint','/api/hashtag.php'), 1),
('job', 'Job Generator', 'Generate job description content', 'content_generation', JSON_OBJECT('endpoint','/api/job.php'), 1),
('post', 'Post Generator', 'Generate social and blog post content', 'content_generation', JSON_OBJECT('endpoint','/api/post.php'), 1),
('product', 'Product Generator', 'Generate product catalog and ecommerce descriptions', 'content_generation', JSON_OBJECT('endpoint','/api/product.php'), 1),
('seo', 'SEO Generator', 'Generate SEO titles and meta descriptions', 'content_generation', JSON_OBJECT('endpoint','/api/seo.php'), 1),
('slogan', 'Slogan Generator', 'Generate slogans and brand taglines', 'content_generation', JSON_OBJECT('endpoint','/api/slogan.php'), 1),
('health', 'Health Check', 'Platform health status and subsystem checks', 'platform', JSON_OBJECT('endpoint','/api/health.php'), 1),
('diagnostics', 'System Diagnostics', 'System and platform diagnostics data', 'platform', JSON_OBJECT('endpoint','/api/diagnostics.php'), 1),
('registry', 'Platform Registry', 'List registered services, modules and providers', 'platform', JSON_OBJECT('endpoint','/api/registry.php'), 1);

-- Security logs
CREATE TABLE IF NOT EXISTS `ai_security_logs` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `action` VARCHAR(100) NOT NULL,
  `details` TEXT NULL,
  `ip_address` VARCHAR(45) NULL,
  `user_agent` VARCHAR(255) NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Audit logs
CREATE TABLE IF NOT EXISTS `ai_audit_logs` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `user_id` INT UNSIGNED NULL,
  `action` VARCHAR(100) NOT NULL,
  `details` TEXT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Rate limits
CREATE TABLE IF NOT EXISTS `ai_rate_limits` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `type` VARCHAR(50) NOT NULL,
  `identifier` VARCHAR(255) NOT NULL,
  `bucket` VARCHAR(20) NOT NULL,
  `request_count` INT UNSIGNED DEFAULT 0,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY `unique_limit` (`type`, `identifier`, `bucket`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Settings
CREATE TABLE IF NOT EXISTS `ai_settings` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(191) NOT NULL UNIQUE,
  `value` TEXT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- User preferences
CREATE TABLE IF NOT EXISTS `ai_user_preferences` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `user_id` INT UNSIGNED NOT NULL,
  `prefs` JSON NULL,
  `updated_at` TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  INDEX (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Index optimization
CREATE INDEX idx_ai_generated_type ON `ai_generated`(`type`);
CREATE INDEX idx_ai_templates_active ON `ai_templates`(`active`);

-- Default categories
INSERT INTO ai_categories (name, slug, description) VALUES
('Marketing', 'marketing', 'Templates for marketing and promotional content'),
('Business', 'business', 'Business profile and company content'),
('Jobs', 'jobs', 'Job description and hiring content'),
('Product', 'product', 'Product and ecommerce content'),
('Hashtags', 'hashtags', 'Hashtag and SEO related content');

-- Default keywords
INSERT INTO ai_keywords (category_id, keyword, weight) VALUES
(1, 'marketing', 5),
(1, 'promotion', 5),
(2, 'business', 5),
(2, 'company', 4),
(3, 'job', 5),
(3, 'hiring', 4),
(4, 'product', 5),
(4, 'features', 4),
(5, 'seo', 5),
(5, 'hashtags', 4);

-- Default templates
INSERT INTO ai_template_groups (name, description) VALUES
('Core templates', 'Built-in Globe AI templates for core generators');

INSERT INTO ai_templates (group_id, category_id, title, content, weight, active) VALUES
(1, 4, 'Product Summary', '{product_name} by {brand} is a breakthrough product designed for {target_audience}. It includes {features} and delivers {benefits}.', 15, 1),
(1, 2, 'Business About', '{business_name} is a {industry} business based in {location}. We deliver services in {services}. Our mission is {mission} and our vision is {vision}.', 15, 1),
(1, 3, 'Job Posting', 'We are hiring a {job_title} in the {industry} sector at {location}. The ideal candidate will have skills in {skills} and provide great value. Benefits include {benefits}.', 15, 1),
(1, 1, 'Marketing Post', 'Need a strong {topic}? Use a {tone} voice to share your message, focusing on {keywords}.', 15, 1),
(1, 5, 'Hashtag Builder', 'Generate hashtags for {category} and {keywords}', 15, 1),
(1, 2, 'Slogan Idea', '{business_name} — {tagline}', 15, 1),
(1, 4, 'SEO Meta', '{title} | {brand}', 15, 1);

INSERT INTO ai_settings (name, value) VALUES
('rate_limit_ip', '60'),
('rate_limit_user', '40'),
('provider', 'template');