-- ============================================
-- IntelJunky Marketing/SEO Platform
-- Unified Database Schema
-- ============================================
-- Database: inteljun_ai (consolidated security model)
-- Prefixes:
--   marketing_* = Marketing Suite tables
--   seo_*       = SEO Promotion tables
--   lmna_users  = Core user table (SHARED - no separate user table)
-- ============================================

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";

-- ============================================
-- MARKETING SUITE TABLES
-- ============================================

-- Marketing Schedule (posts, banners, events, promotions)
CREATE TABLE IF NOT EXISTS `marketing_schedule` (
  `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `group_id` int(11) UNSIGNED NOT NULL,
  `user_id` int(11) UNSIGNED NOT NULL,
  `item_type` enum('post','banner','event','promotion','announcement') NOT NULL DEFAULT 'post',
  `encrypted_title` text,
  `encrypted_content` text,
  `encrypted_image_url` text,
  `encrypted_link_url` text,
  `placement` enum('group','pit','feed','kiosk','all') NOT NULL DEFAULT 'group',
  `scheduled_at` datetime NULL,
  `end_at` datetime NULL,
  `is_recurring` tinyint(1) NOT NULL DEFAULT 0,
  `recurring_rule` varchar(255) NOT NULL DEFAULT '',
  `status` enum('pending','active','paused','completed','expired') NOT NULL DEFAULT 'pending',
  `impressions` int(11) UNSIGNED NOT NULL DEFAULT 0,
  `clicks` int(11) UNSIGNED NOT NULL DEFAULT 0,
  `engagements` int(11) UNSIGNED NOT NULL DEFAULT 0,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime NULL,
  `published_at` datetime NULL,
  PRIMARY KEY (`id`),
  KEY `idx_marketing_group` (`group_id`),
  KEY `idx_marketing_user` (`user_id`),
  KEY `idx_marketing_status` (`status`),
  KEY `idx_marketing_scheduled` (`scheduled_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Marketing Kiosks
CREATE TABLE IF NOT EXISTS `marketing_kiosks` (
  `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `group_id` int(11) UNSIGNED NOT NULL,
  `device_name` varchar(128) NOT NULL,
  `device_token` varchar(64) NOT NULL,
  `location_name` varchar(255),
  `encrypted_location_data` text,
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `last_ping` datetime NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_kiosk_token` (`device_token`),
  KEY `idx_kiosk_group` (`group_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Marketing SEO Settings (per group)
CREATE TABLE IF NOT EXISTS `marketing_seo` (
  `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `group_id` int(11) UNSIGNED NOT NULL UNIQUE,
  `encrypted_og_title` text,
  `encrypted_og_description` text,
  `encrypted_og_image` text,
  `encrypted_twitter_title` text,
  `encrypted_twitter_description` text,
  `encrypted_twitter_image` text,
  `encrypted_meta_keywords` text,
  `updated_at` datetime NULL,
  PRIMARY KEY (`id`),
  KEY `idx_marketing_seo_group` (`group_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Marketing Injector Queue (The Pit posts)
CREATE TABLE IF NOT EXISTS `marketing_injector_queue` (
  `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `group_id` int(11) UNSIGNED NOT NULL DEFAULT 0 COMMENT '0 = global/pit',
  `user_id` int(11) UNSIGNED NOT NULL,
  `conversation_id` int(11) UNSIGNED NOT NULL DEFAULT 1 COMMENT '1 = The Pit',
  `encrypted_content` text NOT NULL,
  `message_type` enum('text','banner','promo','event','ai_generated') NOT NULL DEFAULT 'text',
  `fire_at` datetime NULL,
  `fired_at` datetime NULL,
  `is_recurring` tinyint(1) NOT NULL DEFAULT 0,
  `recurring_rule` varchar(128) NOT NULL DEFAULT '',
  `credit_cost` decimal(10,4) NOT NULL DEFAULT 0,
  `status` enum('pending','fired','failed','cancelled') NOT NULL DEFAULT 'pending',
  `attempts` tinyint(3) UNSIGNED NOT NULL DEFAULT 0,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_injector_status` (`status`),
  KEY `idx_injector_fire` (`fire_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Banner Ads
CREATE TABLE IF NOT EXISTS `banner_ads` (
  `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `group_id` int(11) UNSIGNED NOT NULL,
  `title` varchar(256) NOT NULL DEFAULT '',
  `image_url` varchar(512) NOT NULL DEFAULT '',
  `link_url` varchar(512) NOT NULL DEFAULT '',
  `link_text` varchar(128) NOT NULL DEFAULT 'Learn More',
  `bg_color` varchar(32) NOT NULL DEFAULT '#1a2332',
  `text_color` varchar(32) NOT NULL DEFAULT '#e6edf3',
  `placement` varchar(64) NOT NULL DEFAULT 'top' COMMENT 'top, sidebar, chat, kiosk, footer',
  `dismissible` tinyint(1) NOT NULL DEFAULT 1,
  `active` tinyint(1) NOT NULL DEFAULT 1,
  `sort_order` int(11) NOT NULL DEFAULT 0,
  `impressions` int(11) UNSIGNED NOT NULL DEFAULT 0,
  `clicks` int(11) UNSIGNED NOT NULL DEFAULT 0,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime NULL,
  PRIMARY KEY (`id`),
  KEY `idx_banner_group` (`group_id`),
  KEY `idx_banner_active` (`active`, `placement`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Polls
CREATE TABLE IF NOT EXISTS `polls` (
  `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` int(11) UNSIGNED NOT NULL,
  `group_id` int(11) UNSIGNED NOT NULL,
  `slug` varchar(128) NOT NULL,
  `encrypted_question` text NOT NULL,
  `context` varchar(64) NOT NULL DEFAULT 'general',
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `is_featured` tinyint(1) NOT NULL DEFAULT 0,
  `allow_multiple` tinyint(1) NOT NULL DEFAULT 0,
  `ends_at` datetime NULL,
  `embed_count` int(11) UNSIGNED NOT NULL DEFAULT 0,
  `total_votes` int(11) UNSIGNED NOT NULL DEFAULT 0,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_poll_slug` (`slug`),
  KEY `idx_poll_user` (`user_id`),
  KEY `idx_poll_group` (`group_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Poll Options
CREATE TABLE IF NOT EXISTS `poll_options` (
  `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `poll_id` int(11) UNSIGNED NOT NULL,
  `encrypted_option_text` text NOT NULL,
  `sort_order` int(11) NOT NULL DEFAULT 0,
  `vote_count` int(11) UNSIGNED NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  KEY `idx_poll_option_poll` (`poll_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Poll Votes
CREATE TABLE IF NOT EXISTS `poll_votes` (
  `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `poll_id` int(11) UNSIGNED NOT NULL,
  `option_id` int(11) UNSIGNED NOT NULL,
  `user_id` int(11) UNSIGNED NOT NULL,
  `ip_hash` varchar(64),
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_vote_unique` (`poll_id`, `user_id`),
  KEY `idx_vote_poll` (`poll_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- SEO PROMOTION TABLES
-- ============================================

-- Websites to promote (NO separate user table - uses lmna_users)
CREATE TABLE IF NOT EXISTS `seo_websites` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL COMMENT 'References lmna_users.id',
  `name` varchar(255) NOT NULL,
  `encrypted_url` text NOT NULL,
  `encrypted_title` text,
  `encrypted_description` text,
  `encrypted_keywords` text,
  `category` varchar(100),
  `encrypted_phone` varchar(50),
  `encrypted_email` varchar(255),
  `encrypted_address` text,
  `encrypted_city` varchar(100),
  `encrypted_state` varchar(100),
  `country` varchar(100) NOT NULL DEFAULT 'United States',
  `encrypted_zip_code` varchar(20),
  `logo_url` text,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `status` enum('active','paused','deleted') NOT NULL DEFAULT 'active',
  PRIMARY KEY (`id`),
  KEY `idx_seo_website_user` (`user_id`),
  KEY `idx_seo_website_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Promotion Sources (directories, search engines, social)
CREATE TABLE IF NOT EXISTS `seo_promotion_sources` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `type` enum('search_engine','directory','social_media','review_site','local','niche') NOT NULL,
  `url` text NOT NULL,
  `api_endpoint` text,
  `api_required` tinyint(1) NOT NULL DEFAULT 0,
  `submission_method` enum('api','form','manual') NOT NULL DEFAULT 'manual',
  `category` varchar(100),
  `da_score` int(11) DEFAULT NULL COMMENT 'Domain Authority',
  `pa_score` int(11) DEFAULT NULL COMMENT 'Page Authority',
  `cost` enum('free','paid','freemium') NOT NULL DEFAULT 'free',
  `difficulty` enum('easy','medium','hard') NOT NULL DEFAULT 'medium',
  `success_rate` decimal(5,2) DEFAULT NULL,
  `estimated_time` int(11) DEFAULT NULL COMMENT 'minutes',
  `active` tinyint(1) NOT NULL DEFAULT 1,
  `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_seo_source_type` (`type`),
  KEY `idx_seo_source_active` (`active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Promotion Campaigns
CREATE TABLE IF NOT EXISTS `seo_campaigns` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL COMMENT 'References lmna_users.id',
  `website_id` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  `encrypted_description` text,
  `encrypted_target_keywords` text,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `status` enum('draft','active','paused','completed') NOT NULL DEFAULT 'draft',
  PRIMARY KEY (`id`),
  KEY `idx_seo_campaign_user` (`user_id`),
  KEY `idx_seo_campaign_website` (`website_id`),
  KEY `idx_seo_campaign_status` (`status`),
  CONSTRAINT `seo_campaign_website_fk` FOREIGN KEY (`website_id`) REFERENCES `seo_websites` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Promotion Tasks
CREATE TABLE IF NOT EXISTS `seo_promotion_tasks` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `campaign_id` int(11) NOT NULL,
  `source_id` int(11) NOT NULL,
  `website_id` int(11) NOT NULL,
  `status` enum('pending','in_progress','completed','failed') NOT NULL DEFAULT 'pending',
  `attempt_count` int(11) NOT NULL DEFAULT 0,
  `max_attempts` int(11) NOT NULL DEFAULT 3,
  `encrypted_error_message` text,
  `encrypted_response_data` text,
  `started_at` timestamp NULL DEFAULT NULL,
  `completed_at` timestamp NULL 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_seo_task_campaign` (`campaign_id`),
  KEY `idx_seo_task_source` (`source_id`),
  KEY `idx_seo_task_status` (`status`),
  CONSTRAINT `seo_task_campaign_fk` FOREIGN KEY (`campaign_id`) REFERENCES `seo_campaigns` (`id`) ON DELETE CASCADE,
  CONSTRAINT `seo_task_source_fk` FOREIGN KEY (`source_id`) REFERENCES `seo_promotion_sources` (`id`) ON DELETE CASCADE,
  CONSTRAINT `seo_task_website_fk` FOREIGN KEY (`website_id`) REFERENCES `seo_websites` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Quality Scores
CREATE TABLE IF NOT EXISTS `seo_quality_scores` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `website_id` int(11) NOT NULL,
  `source_id` int(11) NOT NULL,
  `overall_score` decimal(5,2) NOT NULL,
  `completeness_score` decimal(5,2) NOT NULL,
  `relevance_score` decimal(5,2) NOT NULL,
  `trust_score` decimal(5,2) NOT NULL,
  `optimization_score` decimal(5,2) NOT NULL,
  `encrypted_analysis_data` text COMMENT 'JSON',
  `encrypted_recommendations` text COMMENT 'JSON',
  `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_seo_quality_website` (`website_id`),
  KEY `idx_seo_quality_score` (`overall_score`),
  CONSTRAINT `seo_quality_website_fk` FOREIGN KEY (`website_id`) REFERENCES `seo_websites` (`id`) ON DELETE CASCADE,
  CONSTRAINT `seo_quality_source_fk` FOREIGN KEY (`source_id`) REFERENCES `seo_promotion_sources` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- SEO Audit Log
CREATE TABLE IF NOT EXISTS `seo_audit_log` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) DEFAULT NULL COMMENT 'References lmna_users.id',
  `action` varchar(255) NOT NULL,
  `entity_type` varchar(100) NOT NULL,
  `entity_id` int(11) DEFAULT NULL,
  `ip_address` varchar(45),
  `user_agent` text,
  `encrypted_details` text,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_seo_audit_user` (`user_id`),
  KEY `idx_seo_audit_action` (`action`),
  KEY `idx_seo_audit_created` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- DEFAULT DATA: Promotion Sources
-- ============================================

INSERT IGNORE INTO `seo_promotion_sources` (`name`, `type`, `url`, `submission_method`, `category`, `cost`, `difficulty`, `active`) VALUES
('Google Business Profile', 'search_engine', 'https://business.google.com/', 'api', 'Local SEO', 'free', 'easy', 1),
('Bing Places for Business', 'search_engine', 'https://www.bingplaces.com/', 'api', 'Local SEO', 'free', 'easy', 1),
('Yahoo Localworks', 'search_engine', 'https://localworks.yahoo.com/', 'api', 'Local SEO', 'paid', 'medium', 1),
('Apple Maps Connect', 'search_engine', 'https://connect.apple.com/', 'manual', 'Local SEO', 'free', 'medium', 1),
('Yelp for Business', 'review_site', 'https://biz.yelp.com/', 'api', 'Reviews', 'freemium', 'medium', 1),
('TripAdvisor Business', 'review_site', 'https://www.tripadvisor.com/BusinessAdvantage', 'manual', 'Reviews', 'freemium', 'medium', 1),
('Facebook Business Page', 'social_media', 'https://www.facebook.com/business', 'manual', 'Social', 'free', 'easy', 1),
('Instagram Business', 'social_media', 'https://www.instagram.com/business', 'manual', 'Social', 'free', 'easy', 1),
('LinkedIn Company Page', 'social_media', 'https://www.linkedin.com/company/', 'manual', 'Social', 'free', 'medium', 1),
('Twitter Business', 'social_media', 'https://business.twitter.com/', 'manual', 'Social', 'free', 'easy', 1),
('YellowPages', 'directory', 'https://www.yellowpages.com/', 'form', 'Directory', 'freemium', 'easy', 1),
('Better Business Bureau', 'directory', 'https://www.bbb.org/', 'manual', 'Directory', 'freemium', 'medium', 1),
('Chamber of Commerce', 'directory', 'https://www.uschamber.com/', 'manual', 'Directory', 'paid', 'medium', 1),
('Angi (formerly Angie\'s List)', 'directory', 'https://www.angi.com/pro/', 'manual', 'Services', 'freemium', 'medium', 1),
('Thumbtack', 'directory', 'https://www.thumbtack.com/pro', 'manual', 'Services', 'freemium', 'medium', 1),
('Foursquare for Business', 'local', 'https://foursquare.com/business', 'api', 'Local', 'free', 'easy', 1),
('Waze Local', 'local', 'https://www.waze.com/business', 'api', 'Local', 'paid', 'medium', 1),
('Here WeGo', 'local', 'https://www.here.com/', 'api', 'Local', 'free', 'medium', 1),
('Nextdoor Business', 'local', 'https://business.nextdoor.com/', 'manual', 'Local', 'free', 'easy', 1),
('Citysearch', 'directory', 'https://www.citysearch.com/', 'form', 'Directory', 'freemium', 'easy', 1);

-- ============================================
-- PERFORMANCE INDEXES
-- ============================================

CREATE INDEX IF NOT EXISTS `idx_seo_websites_user_status` ON `seo_websites`(`user_id`, `status`);
CREATE INDEX IF NOT EXISTS `idx_seo_campaigns_user_status` ON `seo_campaigns`(`user_id`, `status`);
CREATE INDEX IF NOT EXISTS `idx_seo_tasks_status_created` ON `seo_promotion_tasks`(`status`, `created_at`);
CREATE INDEX IF NOT EXISTS `idx_seo_quality_score_website` ON `seo_quality_scores`(`website_id`, `overall_score`);