
CREATE TABLE IF NOT EXISTS `blog_categories` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(200) NOT NULL,
  `slug` varchar(200) NOT NULL,
  `description` text DEFAULT NULL,
  `created_at` datetime DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `slug` (`slug`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE IF NOT EXISTS `blog_posts` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(500) NOT NULL,
  `slug` varchar(500) NOT NULL,
  `excerpt` text DEFAULT NULL,
  `content` longtext DEFAULT NULL,
  `featured_image` varchar(500) DEFAULT '',
  `categories` varchar(500) DEFAULT '',
  `tags` varchar(500) DEFAULT '',
  `meta_title` varchar(200) DEFAULT '',
  `meta_desc` varchar(500) DEFAULT '',
  `focus_keyword` varchar(200) DEFAULT '',
  `lang` varchar(5) DEFAULT 'en',
  `featured` tinyint(1) DEFAULT 0,
  `read_time` int(11) DEFAULT 0,
  `views` int(11) DEFAULT 0,
  `seo_score` tinyint(4) DEFAULT 0,
  `author` varchar(200) DEFAULT 'SPRIT NETWORK',
  `status` enum('published','draft','scheduled') DEFAULT 'draft',
  `created_at` datetime DEFAULT current_timestamp(),
  `updated_at` datetime DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `published_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `slug` (`slug`),
  FULLTEXT KEY `ft_blog` (`title`,`content`,`excerpt`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE IF NOT EXISTS `chat_conversations` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `session_key` varchar(64) NOT NULL,
  `visitor_name` varchar(150) DEFAULT NULL,
  `visitor_email` varchar(200) DEFAULT NULL,
  `visitor_phone` varchar(60) DEFAULT NULL,
  `lang` varchar(10) DEFAULT 'en',
  `page_url` varchar(500) DEFAULT NULL,
  `status` enum('active','escalated','answered','closed') DEFAULT 'active',
  `intent` varchar(100) DEFAULT NULL,
  `is_lead` tinyint(1) DEFAULT 0,
  `ip` varchar(60) DEFAULT NULL,
  `user_agent` varchar(400) DEFAULT NULL,
  `msg_count` int(11) DEFAULT 0,
  `last_message_at` datetime DEFAULT current_timestamp(),
  `created_at` datetime DEFAULT current_timestamp(),
  `mode` enum('ai','human') DEFAULT 'ai',
  `staff_id` int(11) DEFAULT 0,
  `country` varchar(8) DEFAULT NULL,
  `last_seen` datetime DEFAULT NULL,
  `visitor_typing` tinyint(1) DEFAULT 0,
  PRIMARY KEY (`id`),
  UNIQUE KEY `session_key` (`session_key`),
  KEY `status` (`status`),
  KEY `is_lead` (`is_lead`),
  KEY `last_message_at` (`last_message_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE IF NOT EXISTS `chat_escalations` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `conv_id` int(11) NOT NULL,
  `question` text NOT NULL,
  `staff_answer` text DEFAULT NULL,
  `status` enum('pending','answered','dismissed') DEFAULT 'pending',
  `answered_by` int(11) DEFAULT 0,
  `add_to_kb` tinyint(1) DEFAULT 1,
  `created_at` datetime DEFAULT current_timestamp(),
  `answered_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE IF NOT EXISTS `chat_kb` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `source` enum('page','manual','staff_answer','faq') DEFAULT 'page',
  `url` varchar(500) DEFAULT NULL,
  `title` varchar(300) DEFAULT NULL,
  `content` mediumtext DEFAULT NULL,
  `keywords` text DEFAULT NULL,
  `status` enum('active','draft') DEFAULT 'active',
  `updated_at` datetime DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `created_at` datetime DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  FULLTEXT KEY `ft_content` (`title`,`content`,`keywords`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE IF NOT EXISTS `chat_messages` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `conv_id` int(11) NOT NULL,
  `role` enum('user','assistant','staff','system') NOT NULL,
  `content` mediumtext DEFAULT NULL,
  `unanswered` tinyint(1) DEFAULT 0,
  `created_at` datetime DEFAULT current_timestamp(),
  `delivered` tinyint(1) DEFAULT 0,
  PRIMARY KEY (`id`),
  KEY `conv_id` (`conv_id`),
  KEY `unanswered` (`unanswered`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE IF NOT EXISTS `chat_settings` (
  `key` varchar(100) NOT NULL,
  `value` mediumtext DEFAULT NULL,
  PRIMARY KEY (`key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE IF NOT EXISTS `crm_lead_notes` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `lead_id` int(11) NOT NULL,
  `user_id` int(11) DEFAULT 0,
  `type` enum('note','task','status','assign','call','email') DEFAULT 'note',
  `body` text DEFAULT NULL,
  `due_date` date DEFAULT NULL,
  `done` tinyint(1) DEFAULT 0,
  `created_at` datetime DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `lead_id` (`lead_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE IF NOT EXISTS `crm_leads` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `conv_id` int(11) DEFAULT NULL,
  `name` varchar(150) DEFAULT NULL,
  `email` varchar(200) DEFAULT NULL,
  `phone` varchar(60) DEFAULT NULL,
  `company` varchar(200) DEFAULT NULL,
  `country` varchar(8) DEFAULT NULL,
  `source` varchar(50) DEFAULT 'chatbot',
  `interest` varchar(300) DEFAULT NULL,
  `message` text DEFAULT NULL,
  `status` enum('new','contacted','qualified','proposal','won','lost') DEFAULT 'new',
  `priority` enum('low','medium','high') DEFAULT 'medium',
  `assigned_to` int(11) DEFAULT 0,
  `value` decimal(12,2) DEFAULT 0.00,
  `next_followup` date DEFAULT NULL,
  `created_at` datetime DEFAULT current_timestamp(),
  `updated_at` datetime DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_contact` (`email`,`phone`),
  KEY `status` (`status`),
  KEY `assigned_to` (`assigned_to`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE IF NOT EXISTS `perf_converted` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `path` varchar(700) NOT NULL,
  `orig_size` int(11) DEFAULT NULL,
  `webp_size` int(11) DEFAULT NULL,
  `avif_size` int(11) DEFAULT 0,
  `converted_at` datetime DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `path` (`path`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE IF NOT EXISTS `perf_settings` (
  `key` varchar(100) NOT NULL,
  `value` mediumtext DEFAULT NULL,
  PRIMARY KEY (`key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE IF NOT EXISTS `sec_blocklist` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `ip` varchar(60) NOT NULL,
  `reason` varchar(300) DEFAULT NULL,
  `source` varchar(50) DEFAULT 'manual',
  `hits` int(11) DEFAULT 0,
  `expires_at` datetime DEFAULT NULL,
  `created_at` datetime DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `ip` (`ip`),
  KEY `ip_2` (`ip`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE IF NOT EXISTS `sec_events` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `type` varchar(50) NOT NULL,
  `severity` enum('info','low','medium','high','critical') DEFAULT 'info',
  `ip` varchar(60) DEFAULT NULL,
  `user` varchar(120) DEFAULT NULL,
  `message` varchar(500) DEFAULT NULL,
  `meta` text DEFAULT NULL,
  `chk` char(64) DEFAULT NULL,
  `created_at` datetime DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `type` (`type`),
  KEY `created_at` (`created_at`),
  KEY `ip` (`ip`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE IF NOT EXISTS `sec_file_baseline` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `path` varchar(700) NOT NULL,
  `hash` char(64) NOT NULL,
  `size` int(11) DEFAULT NULL,
  `mtime` int(11) DEFAULT NULL,
  `scope` varchar(50) DEFAULT 'cms',
  `created_at` datetime DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `path_scope` (`path`(255),`scope`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE IF NOT EXISTS `sec_findings` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `scan_id` varchar(40) DEFAULT NULL,
  `path` varchar(700) DEFAULT NULL,
  `type` varchar(60) DEFAULT NULL,
  `severity` enum('low','medium','high','critical') DEFAULT 'medium',
  `signature` varchar(200) DEFAULT NULL,
  `snippet` text DEFAULT NULL,
  `line_no` int(11) DEFAULT 0,
  `status` enum('open','reviewed','ignored','quarantined') DEFAULT 'open',
  `created_at` datetime DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `scan_id` (`scan_id`),
  KEY `status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE IF NOT EXISTS `sec_ratelimit` (
  `bucket` varchar(40) NOT NULL,
  `ip` varchar(60) NOT NULL,
  `hits` int(11) NOT NULL DEFAULT 1,
  `window_start` datetime NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`bucket`,`ip`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE IF NOT EXISTS `sec_scans` (
  `id` varchar(40) NOT NULL,
  `type` varchar(40) DEFAULT NULL,
  `scope` varchar(50) DEFAULT NULL,
  `files_scanned` int(11) DEFAULT 0,
  `findings` int(11) DEFAULT 0,
  `duration` decimal(8,2) DEFAULT 0.00,
  `status` enum('running','done','failed') DEFAULT 'running',
  `started_at` datetime DEFAULT current_timestamp(),
  `finished_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE IF NOT EXISTS `sec_settings` (
  `key` varchar(100) NOT NULL,
  `value` mediumtext DEFAULT NULL,
  PRIMARY KEY (`key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE IF NOT EXISTS `seo_ai_log` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `action` varchar(50) DEFAULT NULL,
  `ref` varchar(190) DEFAULT '',
  `summary` varchar(500) DEFAULT '',
  `created_at` datetime DEFAULT current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE IF NOT EXISTS `seo_audits` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `items_scanned` int(11) DEFAULT 0,
  `findings` int(11) DEFAULT 0,
  `crit` int(11) DEFAULT 0,
  `warn` int(11) DEFAULT 0,
  `notice` int(11) DEFAULT 0,
  `score` int(11) DEFAULT 100,
  `duration` float DEFAULT 0,
  `created_at` datetime DEFAULT current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE IF NOT EXISTS `seo_broken_links` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `link_url` varchar(700) NOT NULL,
  `source_id` int(11) DEFAULT 0,
  `source_title` varchar(320) DEFAULT '',
  `source_slug` varchar(320) DEFAULT '',
  `http_code` int(11) DEFAULT 0,
  `state` enum('pending','ok','broken') DEFAULT 'pending',
  `checked_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `u_url` (`link_url`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE IF NOT EXISTS `seo_competitors` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(190) DEFAULT NULL,
  `url` varchar(500) DEFAULT NULL,
  `title` varchar(320) DEFAULT '',
  `meta_desc` varchar(600) DEFAULT '',
  `words` int(11) DEFAULT 0,
  `h1` int(11) DEFAULT 0,
  `h2` int(11) DEFAULT 0,
  `links` int(11) DEFAULT 0,
  `https` tinyint(4) DEFAULT 0,
  `last_scan` datetime DEFAULT NULL,
  `created_at` datetime DEFAULT current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE IF NOT EXISTS `seo_findings` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `scope` enum('page','post') NOT NULL,
  `ref_id` varchar(190) NOT NULL,
  `url` varchar(500) DEFAULT '',
  `title` varchar(320) DEFAULT '',
  `check_code` varchar(50) NOT NULL,
  `severity` enum('critical','warning','notice') DEFAULT 'notice',
  `detail` varchar(600) DEFAULT '',
  `status` enum('open','fixed','ignored') DEFAULT 'open',
  `created_at` datetime DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `k_sev` (`severity`),
  KEY `k_st` (`status`),
  KEY `k_scope` (`scope`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE IF NOT EXISTS `seo_kb` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `kind` enum('page','post','business') NOT NULL,
  `ref_id` varchar(190) NOT NULL,
  `url` varchar(500) DEFAULT '',
  `title` varchar(320) DEFAULT '',
  `content` mediumtext DEFAULT NULL,
  `keywords` varchar(500) DEFAULT '',
  `updated_at` datetime DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `u_item` (`kind`,`ref_id`),
  FULLTEXT KEY `ft` (`title`,`content`,`keywords`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE IF NOT EXISTS `seo_link_suggestions` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `from_post` int(11) NOT NULL,
  `anchor` varchar(190) DEFAULT NULL,
  `to_url` varchar(500) DEFAULT NULL,
  `to_title` varchar(320) DEFAULT NULL,
  `status` enum('open','applied','ignored') DEFAULT 'open',
  `created_at` datetime DEFAULT current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE IF NOT EXISTS `seo_rank_history` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `kw_id` int(11) DEFAULT NULL,
  `pos` int(11) DEFAULT NULL,
  `checked_at` datetime DEFAULT current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE IF NOT EXISTS `seo_rank_keywords` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `keyword` varchar(200) DEFAULT NULL,
  `target_url` varchar(500) DEFAULT '',
  `current_pos` int(11) DEFAULT NULL,
  `best_pos` int(11) DEFAULT NULL,
  `prev_pos` int(11) DEFAULT NULL,
  `last_checked` datetime DEFAULT NULL,
  `created_at` datetime DEFAULT current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE IF NOT EXISTS `seo_settings` (
  `k` varchar(60) NOT NULL,
  `v` text DEFAULT NULL,
  PRIMARY KEY (`k`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE IF NOT EXISTS `shop_addresses` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `customer_id` int(11) NOT NULL,
  `type` enum('billing','shipping') DEFAULT 'billing',
  `first_name` varchar(100) DEFAULT NULL,
  `last_name` varchar(100) DEFAULT NULL,
  `company` varchar(200) DEFAULT NULL,
  `line1` varchar(300) DEFAULT NULL,
  `line2` varchar(300) DEFAULT NULL,
  `city` varchar(100) DEFAULT NULL,
  `state` varchar(100) DEFAULT NULL,
  `postcode` varchar(30) DEFAULT NULL,
  `country` varchar(100) DEFAULT 'LK',
  `phone` varchar(50) DEFAULT NULL,
  `is_default` tinyint(1) DEFAULT 0,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE IF NOT EXISTS `shop_categories` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `parent_id` int(11) DEFAULT 0,
  `name` varchar(200) NOT NULL,
  `slug` varchar(200) NOT NULL,
  `description` text DEFAULT NULL,
  `image` varchar(500) DEFAULT NULL,
  `sort_order` int(11) DEFAULT 0,
  `created_at` datetime DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `slug` (`slug`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE IF NOT EXISTS `shop_coupons` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `code` varchar(100) NOT NULL,
  `description` varchar(300) DEFAULT NULL,
  `type` enum('percent','fixed_cart','fixed_product','free_shipping') DEFAULT 'percent',
  `value` decimal(10,2) DEFAULT 0.00,
  `min_order` decimal(10,2) DEFAULT 0.00,
  `max_discount` decimal(10,2) DEFAULT NULL,
  `max_uses` int(11) DEFAULT NULL,
  `used_count` int(11) DEFAULT 0,
  `per_customer` int(11) DEFAULT NULL,
  `products` text DEFAULT NULL,
  `categories` text DEFAULT NULL,
  `expires_at` date DEFAULT NULL,
  `status` tinyint(1) DEFAULT 1,
  `created_at` datetime DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `code` (`code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE IF NOT EXISTS `shop_customers` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `email` varchar(255) NOT NULL,
  `password_hash` varchar(255) DEFAULT NULL,
  `first_name` varchar(100) DEFAULT NULL,
  `last_name` varchar(100) DEFAULT NULL,
  `phone` varchar(50) DEFAULT NULL,
  `currency` enum('USD','LKR') DEFAULT 'USD',
  `language` varchar(10) DEFAULT 'en',
  `email_verified` tinyint(1) DEFAULT 0,
  `verify_token` varchar(100) DEFAULT NULL,
  `reset_token` varchar(100) DEFAULT NULL,
  `reset_expiry` datetime DEFAULT NULL,
  `notes` text DEFAULT NULL,
  `created_at` datetime DEFAULT current_timestamp(),
  `last_login` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE IF NOT EXISTS `shop_download_log` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `download_id` int(11) NOT NULL,
  `order_id` int(11) DEFAULT NULL,
  `customer_id` int(11) DEFAULT NULL,
  `ip` varchar(50) DEFAULT NULL,
  `accessed_at` datetime DEFAULT current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE IF NOT EXISTS `shop_downloads` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `product_id` int(11) NOT NULL,
  `file_name` varchar(300) NOT NULL,
  `file_path` varchar(500) NOT NULL,
  `download_limit` int(11) DEFAULT NULL,
  `expiry_days` int(11) DEFAULT NULL,
  `created_at` datetime DEFAULT current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE IF NOT EXISTS `shop_order_items` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `order_id` int(11) NOT NULL,
  `product_id` int(11) DEFAULT NULL,
  `variant_id` int(11) DEFAULT NULL,
  `plan_id` int(11) DEFAULT NULL,
  `name` varchar(300) DEFAULT NULL,
  `sku` varchar(100) DEFAULT NULL,
  `type` varchar(50) DEFAULT NULL,
  `quantity` int(11) DEFAULT 1,
  `unit_price` decimal(12,2) DEFAULT 0.00,
  `subtotal` decimal(12,2) DEFAULT 0.00,
  `tax` decimal(12,2) DEFAULT 0.00,
  `meta` text DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE IF NOT EXISTS `shop_orders` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `order_number` varchar(50) NOT NULL,
  `customer_id` int(11) DEFAULT NULL,
  `customer_email` varchar(255) DEFAULT NULL,
  `status` enum('pending','processing','on-hold','completed','cancelled','refunded','failed') DEFAULT 'pending',
  `currency` enum('USD','LKR') DEFAULT 'USD',
  `exchange_rate` decimal(10,4) DEFAULT 1.0000,
  `subtotal` decimal(12,2) DEFAULT 0.00,
  `discount` decimal(12,2) DEFAULT 0.00,
  `shipping` decimal(12,2) DEFAULT 0.00,
  `tax` decimal(12,2) DEFAULT 0.00,
  `total` decimal(12,2) DEFAULT 0.00,
  `payment_method` enum('stripe','paypal','payhere','bank_transfer') DEFAULT 'bank_transfer',
  `payment_status` enum('unpaid','paid','refunded','partially_refunded') DEFAULT 'unpaid',
  `coupon_code` varchar(100) DEFAULT NULL,
  `notes` text DEFAULT NULL,
  `billing_address` text DEFAULT NULL,
  `shipping_address` text DEFAULT NULL,
  `ip_address` varchar(50) DEFAULT NULL,
  `created_at` datetime DEFAULT current_timestamp(),
  `updated_at` datetime DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `order_number` (`order_number`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE IF NOT EXISTS `shop_payment_logs` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `order_id` int(11) DEFAULT NULL,
  `gateway` varchar(50) DEFAULT NULL,
  `transaction_id` varchar(300) DEFAULT NULL,
  `amount` decimal(12,2) DEFAULT NULL,
  `currency` varchar(10) DEFAULT NULL,
  `status` varchar(50) DEFAULT NULL,
  `raw` mediumtext DEFAULT NULL,
  `created_at` datetime DEFAULT current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE IF NOT EXISTS `shop_plans` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `product_id` int(11) NOT NULL,
  `name` varchar(200) NOT NULL,
  `billing_interval` enum('day','week','month','year') DEFAULT 'month',
  `interval_count` int(11) DEFAULT 1,
  `price_usd` decimal(12,2) DEFAULT 0.00,
  `price_lkr` decimal(12,2) DEFAULT 0.00,
  `setup_fee_usd` decimal(12,2) DEFAULT 0.00,
  `setup_fee_lkr` decimal(12,2) DEFAULT 0.00,
  `trial_days` int(11) DEFAULT 0,
  `max_renewals` int(11) DEFAULT 0,
  `status` tinyint(1) DEFAULT 1,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE IF NOT EXISTS `shop_product_categories` (
  `product_id` int(11) NOT NULL,
  `category_id` int(11) NOT NULL,
  PRIMARY KEY (`product_id`,`category_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE IF NOT EXISTS `shop_products` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `sku` varchar(100) DEFAULT NULL,
  `name` varchar(300) NOT NULL,
  `slug` varchar(300) NOT NULL,
  `type` enum('physical','digital','service','subscription') DEFAULT 'service',
  `status` enum('active','draft','archived') DEFAULT 'draft',
  `featured` tinyint(1) DEFAULT 0,
  `short_desc` text DEFAULT NULL,
  `description` longtext DEFAULT NULL,
  `image` varchar(500) DEFAULT NULL,
  `gallery` text DEFAULT NULL,
  `price_usd` decimal(12,2) DEFAULT 0.00,
  `price_lkr` decimal(12,2) DEFAULT 0.00,
  `sale_price_usd` decimal(12,2) DEFAULT NULL,
  `sale_price_lkr` decimal(12,2) DEFAULT NULL,
  `sale_start` date DEFAULT NULL,
  `sale_end` date DEFAULT NULL,
  `tax_class` varchar(50) DEFAULT 'standard',
  `weight` decimal(8,3) DEFAULT NULL,
  `dimensions` varchar(100) DEFAULT NULL,
  `manage_stock` tinyint(1) DEFAULT 0,
  `stock_qty` int(11) DEFAULT NULL,
  `low_stock_qty` int(11) DEFAULT 5,
  `backorders` enum('no','notify','yes') DEFAULT 'no',
  `sold_count` int(11) DEFAULT 0,
  `meta_title` varchar(300) DEFAULT NULL,
  `meta_desc` varchar(500) DEFAULT NULL,
  `created_at` datetime DEFAULT current_timestamp(),
  `updated_at` datetime DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `slug` (`slug`),
  UNIQUE KEY `sku` (`sku`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE IF NOT EXISTS `shop_reviews` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `product_id` int(11) NOT NULL,
  `customer_id` int(11) DEFAULT NULL,
  `name` varchar(100) DEFAULT NULL,
  `email` varchar(200) DEFAULT NULL,
  `rating` tinyint(4) DEFAULT 5,
  `title` varchar(300) DEFAULT NULL,
  `content` text DEFAULT NULL,
  `status` enum('approved','pending','spam') DEFAULT 'pending',
  `created_at` datetime DEFAULT current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE IF NOT EXISTS `shop_settings` (
  `key` varchar(100) NOT NULL,
  `value` mediumtext DEFAULT NULL,
  PRIMARY KEY (`key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE IF NOT EXISTS `shop_shipping_zones` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(200) DEFAULT NULL,
  `countries` text DEFAULT NULL,
  `method` enum('flat','free','weight','local_pickup') DEFAULT 'flat',
  `cost_usd` decimal(10,2) DEFAULT 0.00,
  `cost_lkr` decimal(10,2) DEFAULT 0.00,
  `free_above_usd` decimal(10,2) DEFAULT NULL,
  `free_above_lkr` decimal(10,2) DEFAULT NULL,
  `weight_rate_usd` decimal(10,4) DEFAULT NULL,
  `weight_rate_lkr` decimal(10,4) DEFAULT NULL,
  `estimated_days` varchar(50) DEFAULT NULL,
  `status` tinyint(1) DEFAULT 1,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE IF NOT EXISTS `shop_subscription_invoices` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `subscription_id` int(11) NOT NULL,
  `order_id` int(11) DEFAULT NULL,
  `amount` decimal(12,2) DEFAULT NULL,
  `currency` enum('USD','LKR') DEFAULT 'USD',
  `status` enum('pending','paid','failed','waived') DEFAULT 'pending',
  `due_date` date DEFAULT NULL,
  `paid_at` datetime DEFAULT NULL,
  `created_at` datetime DEFAULT current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE IF NOT EXISTS `shop_subscriptions` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `sub_number` varchar(50) NOT NULL,
  `customer_id` int(11) NOT NULL,
  `plan_id` int(11) NOT NULL,
  `order_id` int(11) DEFAULT NULL,
  `status` enum('active','on-hold','cancelled','expired','pending') DEFAULT 'pending',
  `currency` enum('USD','LKR') DEFAULT 'USD',
  `amount` decimal(12,2) DEFAULT 0.00,
  `billing_interval` varchar(20) DEFAULT NULL,
  `interval_count` int(11) DEFAULT 1,
  `trial_end` date DEFAULT NULL,
  `next_payment` date DEFAULT NULL,
  `period_start` date DEFAULT NULL,
  `period_end` date DEFAULT NULL,
  `cancel_at` date DEFAULT NULL,
  `cancelled_at` datetime DEFAULT NULL,
  `renewal_count` int(11) DEFAULT 0,
  `created_at` datetime DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `sub_number` (`sub_number`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE IF NOT EXISTS `shop_tax_rates` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) DEFAULT NULL,
  `country` varchar(10) DEFAULT '*',
  `state` varchar(100) DEFAULT '*',
  `rate` decimal(6,4) DEFAULT 0.0000,
  `type` enum('inclusive','exclusive') DEFAULT 'exclusive',
  `applies_to` enum('all','shipping','products') DEFAULT 'all',
  `priority` int(11) DEFAULT 1,
  `status` tinyint(1) DEFAULT 1,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE IF NOT EXISTS `shop_translations` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `lang` varchar(10) NOT NULL,
  `context` varchar(100) DEFAULT 'shop',
  `original` text NOT NULL,
  `translated` text DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `lang_original` (`lang`,`context`(50),`original`(100))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE IF NOT EXISTS `shop_variants` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `product_id` int(11) NOT NULL,
  `name` varchar(200) NOT NULL,
  `sku` varchar(100) DEFAULT NULL,
  `attributes` text DEFAULT NULL,
  `price_usd` decimal(12,2) DEFAULT 0.00,
  `price_lkr` decimal(12,2) DEFAULT 0.00,
  `stock_qty` int(11) DEFAULT NULL,
  `image` varchar(500) DEFAULT NULL,
  `status` tinyint(1) DEFAULT 1,
  `sort_order` int(11) DEFAULT 0,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE IF NOT EXISTS `site_form_configs` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `form_id` varchar(100) NOT NULL,
  `form_name` varchar(200) DEFAULT NULL,
  `notify_email` varchar(300) DEFAULT NULL,
  `notify_subject` varchar(300) DEFAULT NULL,
  `notify_body` text DEFAULT NULL,
  `autoreply` tinyint(1) DEFAULT 0,
  `autoreply_subject` varchar(300) DEFAULT NULL,
  `autoreply_body` text DEFAULT NULL,
  `success_msg` varchar(500) DEFAULT NULL,
  `spam_protect` tinyint(1) DEFAULT 1,
  `created_at` datetime DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `form_id` (`form_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE IF NOT EXISTS `site_form_submissions` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `form_id` varchar(100) DEFAULT NULL,
  `form_name` varchar(200) DEFAULT NULL,
  `page_url` varchar(500) DEFAULT NULL,
  `fields` mediumtext DEFAULT NULL,
  `ip` varchar(60) DEFAULT NULL,
  `user_agent` varchar(400) DEFAULT NULL,
  `status` enum('new','read','replied','spam') DEFAULT 'new',
  `created_at` datetime DEFAULT current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE IF NOT EXISTS `site_settings` (
  `key` varchar(100) NOT NULL,
  `value` mediumtext DEFAULT NULL,
  PRIMARY KEY (`key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE IF NOT EXISTS `sn_activity_log` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) DEFAULT NULL,
  `action` varchar(200) NOT NULL,
  `details` text DEFAULT NULL,
  `ip_address` varchar(50) DEFAULT NULL,
  `created_at` datetime DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_user` (`user_id`),
  KEY `idx_action` (`action`),
  KEY `idx_created` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;
CREATE TABLE IF NOT EXISTS `sn_admin_users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(100) NOT NULL,
  `email` varchar(200) DEFAULT '',
  `password_hash` varchar(255) NOT NULL,
  `role` enum('super_admin','admin','editor') DEFAULT 'admin',
  `is_active` tinyint(4) DEFAULT 1,
  `last_login` datetime DEFAULT NULL,
  `login_attempts` int(11) DEFAULT 0,
  `locked_until` datetime DEFAULT NULL,
  `created_at` datetime DEFAULT current_timestamp(),
  `updated_at` datetime DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `totp_secret` varchar(64) DEFAULT NULL,
  `totp_enabled` tinyint(1) DEFAULT 0,
  `recovery_codes` text DEFAULT NULL,
  `pw_breached_count` int(11) NOT NULL DEFAULT 0,
  `must_change_pw` tinyint(1) NOT NULL DEFAULT 0,
  `pw_checked_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `username` (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;
CREATE TABLE IF NOT EXISTS `sn_ai_chats` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `error_id` int(11) NOT NULL,
  `role` enum('user','assistant') NOT NULL,
  `content` mediumtext DEFAULT NULL,
  `created_at` datetime DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `error_id` (`error_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE IF NOT EXISTS `sn_error_log` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `fingerprint` char(32) NOT NULL,
  `level` varchar(20) NOT NULL,
  `message` text NOT NULL,
  `file` varchar(500) DEFAULT NULL,
  `line` int(11) DEFAULT 0,
  `trace` mediumtext DEFAULT NULL,
  `url` varchar(500) DEFAULT NULL,
  `method` varchar(10) DEFAULT NULL,
  `user_id` int(11) DEFAULT 0,
  `username` varchar(120) DEFAULT NULL,
  `ip` varchar(60) DEFAULT NULL,
  `count` int(11) DEFAULT 1,
  `status` enum('open','resolved','ignored') DEFAULT 'open',
  `first_seen` datetime DEFAULT current_timestamp(),
  `last_seen` datetime DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_fp` (`fingerprint`),
  KEY `level` (`level`),
  KEY `status` (`status`),
  KEY `last_seen` (`last_seen`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE IF NOT EXISTS `sn_login_log` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(100) DEFAULT NULL,
  `ip_address` varchar(50) DEFAULT NULL,
  `success` tinyint(4) DEFAULT 0,
  `created_at` datetime DEFAULT current_timestamp(),
  `country` varchar(2) DEFAULT NULL,
  `region` varchar(120) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;
CREATE TABLE IF NOT EXISTS `sn_media` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `filename` varchar(300) NOT NULL,
  `original_name` varchar(300) DEFAULT NULL,
  `file_path` varchar(600) NOT NULL,
  `file_type` varchar(100) DEFAULT NULL,
  `file_size` int(11) DEFAULT 0,
  `uploaded_by` int(11) DEFAULT NULL,
  `created_at` datetime DEFAULT current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;
CREATE TABLE IF NOT EXISTS `sn_migrations` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `source_type` varchar(20) NOT NULL,
  `source` varchar(600) NOT NULL,
  `platform` varchar(40) DEFAULT '',
  `inventory` mediumtext DEFAULT NULL,
  `plan` mediumtext DEFAULT NULL,
  `created_at` datetime DEFAULT current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE IF NOT EXISTS `sn_navigation` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `nav_key` varchar(100) NOT NULL DEFAULT 'main',
  `label` varchar(200) NOT NULL,
  `url` varchar(600) NOT NULL,
  `parent_id` int(11) DEFAULT 0,
  `sort_order` int(11) DEFAULT 10,
  `is_active` tinyint(4) DEFAULT 1,
  `updated_at` datetime DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;
CREATE TABLE IF NOT EXISTS `sn_page_drafts` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(300) NOT NULL,
  `subtitle` varchar(400) DEFAULT '',
  `slug` varchar(300) NOT NULL,
  `ptype` varchar(20) NOT NULL DEFAULT 'service',
  `lang` varchar(5) NOT NULL DEFAULT 'en',
  `meta_desc` varchar(320) DEFAULT '',
  `focus_keyword` varchar(160) DEFAULT '',
  `template` varchar(600) DEFAULT '',
  `body_html` longtext DEFAULT NULL,
  `hero_image` varchar(600) DEFAULT '',
  `status` enum('draft','published') DEFAULT 'draft',
  `page_key` varchar(300) DEFAULT '',
  `created_at` datetime DEFAULT current_timestamp(),
  `updated_at` datetime DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE IF NOT EXISTS `sn_pages` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `page_key` varchar(300) NOT NULL,
  `meta_title` varchar(400) DEFAULT '',
  `meta_description` text DEFAULT NULL,
  `meta_keywords` varchar(400) DEFAULT '',
  `file_path` varchar(600) NOT NULL,
  `last_edited` datetime DEFAULT NULL,
  `last_edited_by` int(11) DEFAULT NULL,
  `created_at` datetime DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `page_key` (`page_key`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;
CREATE TABLE IF NOT EXISTS `sn_sessions` (
  `session_id` varchar(128) NOT NULL,
  `user_id` int(11) NOT NULL,
  `ip_address` varchar(50) DEFAULT NULL,
  `user_agent` varchar(500) DEFAULT NULL,
  `last_activity` datetime DEFAULT NULL,
  `created_at` datetime DEFAULT current_timestamp(),
  PRIMARY KEY (`session_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;
CREATE TABLE IF NOT EXISTS `sn_settings` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `setting_key` varchar(200) NOT NULL,
  `setting_value` longtext DEFAULT NULL,
  `setting_group` varchar(100) DEFAULT 'general',
  `updated_at` datetime DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `setting_key` (`setting_key`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;
CREATE TABLE IF NOT EXISTS `sn_site_plan` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(300) NOT NULL,
  `ptype` varchar(20) NOT NULL DEFAULT 'service',
  `kw` varchar(160) DEFAULT '',
  `lang` varchar(5) DEFAULT 'en',
  `imgmode` varchar(10) DEFAULT 'auto',
  `context` text DEFAULT NULL,
  `draft_id` int(11) DEFAULT 0,
  `status` enum('planned','done') DEFAULT 'planned',
  `created_at` datetime DEFAULT current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE IF NOT EXISTS `sn_social_posts` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `post_id` int(11) DEFAULT 0,
  `platform` varchar(20) NOT NULL,
  `text` text DEFAULT NULL,
  `hashtags` varchar(400) DEFAULT '',
  `image_url` varchar(600) DEFAULT '',
  `link` varchar(600) DEFAULT '',
  `status` enum('queued','published','failed') DEFAULT 'queued',
  `remote_id` varchar(120) DEFAULT '',
  `error` varchar(600) DEFAULT '',
  `created_at` datetime DEFAULT current_timestamp(),
  `published_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;


