-- ============================================
-- BLOG SYSTEM DATABASE TABLES
-- ============================================

-- Blog Posts Table
CREATE TABLE IF NOT EXISTS `blog_posts` (
  `post_id` int(11) NOT NULL AUTO_INCREMENT,
  `post_title` varchar(255) NOT NULL,
  `post_slug` varchar(255) NOT NULL,
  `post_content` longtext NOT NULL,
  `post_excerpt` text DEFAULT NULL,
  `post_featured_image` varchar(255) DEFAULT NULL,
  `post_featured_image_cdn_url` varchar(500) DEFAULT '',
  `post_use_cdn` tinyint(1) DEFAULT 0,
  `post_category` int(11) DEFAULT NULL,
  `post_author` int(11) NOT NULL,
  `post_status` enum('draft','published','scheduled') DEFAULT 'draft',
  `post_views` int(11) DEFAULT 0,
  `post_featured` tinyint(1) DEFAULT 0,
  `post_seo_title` varchar(255) DEFAULT NULL,
  `post_seo_description` text DEFAULT NULL,
  `post_tags` varchar(500) DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `published_at` datetime DEFAULT NULL,
  PRIMARY KEY (`post_id`),
  UNIQUE KEY `post_slug` (`post_slug`),
  KEY `post_category` (`post_category`),
  KEY `post_author` (`post_author`),
  KEY `post_status` (`post_status`),
  KEY `published_at` (`published_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;

-- Blog Categories Table
CREATE TABLE IF NOT EXISTS `blog_categories` (
  `blog_cat_id` int(11) NOT NULL AUTO_INCREMENT,
  `blog_cat_name` varchar(255) NOT NULL,
  `blog_cat_slug` varchar(255) NOT NULL,
  `blog_cat_description` text DEFAULT NULL,
  `blog_cat_image` varchar(255) DEFAULT NULL,
  `blog_cat_status` tinyint(1) DEFAULT 1,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`blog_cat_id`),
  UNIQUE KEY `blog_cat_slug` (`blog_cat_slug`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;

-- Blog Comments Table
CREATE TABLE IF NOT EXISTS `blog_comments` (
  `comment_id` int(11) NOT NULL AUTO_INCREMENT,
  `post_id` int(11) NOT NULL,
  `comment_author_name` varchar(255) NOT NULL,
  `comment_author_email` varchar(255) NOT NULL,
  `comment_content` text NOT NULL,
  `comment_status` enum('pending','approved','spam') DEFAULT 'pending',
  `comment_parent` int(11) DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`comment_id`),
  KEY `post_id` (`post_id`),
  KEY `comment_status` (`comment_status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;

-- Insert Default Blog Categories
INSERT INTO `blog_categories` (`blog_cat_name`, `blog_cat_slug`, `blog_cat_description`, `blog_cat_status`) VALUES
('Tech News', 'tech-news', 'Latest technology news and updates', 1),
('Shopping Tips', 'shopping-tips', 'Tips and tricks for smart shopping', 1),
('Deal Alerts', 'deal-alerts', 'Hot deals and discount alerts', 1),
('Product Reviews', 'product-reviews', 'Honest product reviews and comparisons', 1),
('Lifestyle', 'lifestyle', 'Lifestyle and trending topics', 1);

-- Sample Blog Post
INSERT INTO `blog_posts` (`post_title`, `post_slug`, `post_content`, `post_excerpt`, `post_category`, `post_author`, `post_status`, `post_seo_title`, `post_seo_description`, `published_at`) VALUES
('Welcome to Our Blog', 'welcome-to-our-blog', '<h2>Welcome to Alonedeals Blog!</h2><p>We are excited to launch our new blog where we will share the latest tech news, shopping tips, and exclusive deal alerts.</p><p>Stay tuned for amazing content!</p>', 'Welcome to our new blog! Get the latest updates on deals, tech news, and shopping tips.', 1, 1, 'published', 'Welcome to Alonedeals Blog - Latest Deals & Tech News', 'Discover the latest deals, tech news, and shopping tips on Alonedeals blog. Stay updated with exclusive offers and product reviews.', NOW());
