351 lines
16 KiB
MySQL
351 lines
16 KiB
MySQL
|
-- UK Data Services Database Update - Blog Integration
|
||
|
|
-- Adds blog functionality to existing database schema
|
||
|
|
-- Version: 2.0 (Post-Blog Integration)
|
||
|
|
|
||
|
|
-- Set charset and collation
|
||
|
|
SET NAMES utf8mb4;
|
||
|
|
SET FOREIGN_KEY_CHECKS = 0;
|
||
|
|
|
||
|
|
-- Blog Categories Table
|
||
|
|
CREATE TABLE IF NOT EXISTS `blog_categories` (
|
||
|
|
`id` INT(11) NOT NULL AUTO_INCREMENT,
|
||
|
|
`slug` VARCHAR(100) NOT NULL UNIQUE,
|
||
|
|
`name` VARCHAR(100) NOT NULL,
|
||
|
|
`description` TEXT DEFAULT NULL,
|
||
|
|
`meta_title` VARCHAR(160) DEFAULT NULL,
|
||
|
|
`meta_description` VARCHAR(320) DEFAULT NULL,
|
||
|
|
`is_active` BOOLEAN DEFAULT TRUE,
|
||
|
|
`sort_order` INT DEFAULT 0,
|
||
|
|
`created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
|
|
`updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
||
|
|
PRIMARY KEY (`id`),
|
||
|
|
UNIQUE KEY `unique_slug` (`slug`),
|
||
|
|
INDEX `idx_is_active` (`is_active`),
|
||
|
|
INDEX `idx_sort_order` (`sort_order`)
|
||
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
||
|
|
|
||
|
|
-- Blog Articles Table
|
||
|
|
CREATE TABLE IF NOT EXISTS `blog_articles` (
|
||
|
|
`id` INT(11) NOT NULL AUTO_INCREMENT,
|
||
|
|
`slug` VARCHAR(150) NOT NULL UNIQUE,
|
||
|
|
`title` VARCHAR(200) NOT NULL,
|
||
|
|
`subtitle` VARCHAR(300) DEFAULT NULL,
|
||
|
|
`excerpt` TEXT DEFAULT NULL,
|
||
|
|
`content` LONGTEXT NOT NULL,
|
||
|
|
`featured_image` VARCHAR(255) DEFAULT NULL,
|
||
|
|
`category_id` INT(11) NOT NULL,
|
||
|
|
`author_name` VARCHAR(100) DEFAULT 'UK Data Services Team',
|
||
|
|
`author_title` VARCHAR(100) DEFAULT NULL,
|
||
|
|
`meta_title` VARCHAR(160) DEFAULT NULL,
|
||
|
|
`meta_description` VARCHAR(320) DEFAULT NULL,
|
||
|
|
`meta_keywords` VARCHAR(500) DEFAULT NULL,
|
||
|
|
`reading_time_minutes` INT DEFAULT 5,
|
||
|
|
`word_count` INT DEFAULT 0,
|
||
|
|
`is_published` BOOLEAN DEFAULT FALSE,
|
||
|
|
`is_featured` BOOLEAN DEFAULT FALSE,
|
||
|
|
`published_at` TIMESTAMP NULL DEFAULT NULL,
|
||
|
|
`created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
|
|
`updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
||
|
|
PRIMARY KEY (`id`),
|
||
|
|
UNIQUE KEY `unique_slug` (`slug`),
|
||
|
|
FOREIGN KEY (`category_id`) REFERENCES `blog_categories`(`id`) ON DELETE CASCADE,
|
||
|
|
INDEX `idx_is_published` (`is_published`),
|
||
|
|
INDEX `idx_is_featured` (`is_featured`),
|
||
|
|
INDEX `idx_published_at` (`published_at`),
|
||
|
|
INDEX `idx_category_id` (`category_id`),
|
||
|
|
FULLTEXT KEY `search_content` (`title`, `excerpt`, `content`)
|
||
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
||
|
|
|
||
|
|
-- Blog Tags Table
|
||
|
|
CREATE TABLE IF NOT EXISTS `blog_tags` (
|
||
|
|
`id` INT(11) NOT NULL AUTO_INCREMENT,
|
||
|
|
`slug` VARCHAR(100) NOT NULL UNIQUE,
|
||
|
|
`name` VARCHAR(100) NOT NULL,
|
||
|
|
`description` TEXT DEFAULT NULL,
|
||
|
|
`created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
|
|
PRIMARY KEY (`id`),
|
||
|
|
UNIQUE KEY `unique_slug` (`slug`)
|
||
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
||
|
|
|
||
|
|
-- Blog Article Tags (Many-to-Many)
|
||
|
|
CREATE TABLE IF NOT EXISTS `blog_article_tags` (
|
||
|
|
`article_id` INT(11) NOT NULL,
|
||
|
|
`tag_id` INT(11) NOT NULL,
|
||
|
|
`created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
|
|
PRIMARY KEY (`article_id`, `tag_id`),
|
||
|
|
FOREIGN KEY (`article_id`) REFERENCES `blog_articles`(`id`) ON DELETE CASCADE,
|
||
|
|
FOREIGN KEY (`tag_id`) REFERENCES `blog_tags`(`id`) ON DELETE CASCADE
|
||
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
||
|
|
|
||
|
|
-- Blog Comments Table
|
||
|
|
CREATE TABLE IF NOT EXISTS `blog_comments` (
|
||
|
|
`id` INT(11) NOT NULL AUTO_INCREMENT,
|
||
|
|
`article_id` INT(11) NOT NULL,
|
||
|
|
`author_name` VARCHAR(100) NOT NULL,
|
||
|
|
`author_email` VARCHAR(100) NOT NULL,
|
||
|
|
`author_website` VARCHAR(255) DEFAULT NULL,
|
||
|
|
`content` TEXT NOT NULL,
|
||
|
|
`ip_address` VARCHAR(45) DEFAULT NULL,
|
||
|
|
`user_agent` TEXT DEFAULT NULL,
|
||
|
|
`is_approved` BOOLEAN DEFAULT FALSE,
|
||
|
|
`parent_id` INT(11) DEFAULT NULL,
|
||
|
|
`created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
|
|
`updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
||
|
|
PRIMARY KEY (`id`),
|
||
|
|
FOREIGN KEY (`article_id`) REFERENCES `blog_articles`(`id`) ON DELETE CASCADE,
|
||
|
|
FOREIGN KEY (`parent_id`) REFERENCES `blog_comments`(`id`) ON DELETE CASCADE,
|
||
|
|
INDEX `idx_article_id` (`article_id`),
|
||
|
|
INDEX `idx_is_approved` (`is_approved`),
|
||
|
|
INDEX `idx_created_at` (`created_at`)
|
||
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
||
|
|
|
||
|
|
-- Blog Analytics Table
|
||
|
|
CREATE TABLE IF NOT EXISTS `blog_analytics` (
|
||
|
|
`id` INT(11) NOT NULL AUTO_INCREMENT,
|
||
|
|
`article_id` INT(11) DEFAULT NULL,
|
||
|
|
`category_id` INT(11) DEFAULT NULL,
|
||
|
|
`page_type` ENUM('article', 'category', 'index', 'search') NOT NULL,
|
||
|
|
`page_url` VARCHAR(255) NOT NULL,
|
||
|
|
`referrer` VARCHAR(255) DEFAULT NULL,
|
||
|
|
`search_term` VARCHAR(255) DEFAULT NULL,
|
||
|
|
`reading_time_seconds` INT DEFAULT NULL,
|
||
|
|
`scroll_percentage` INT DEFAULT NULL,
|
||
|
|
`ip_address` VARCHAR(45) DEFAULT NULL,
|
||
|
|
`user_agent` TEXT DEFAULT NULL,
|
||
|
|
`session_id` VARCHAR(255) DEFAULT NULL,
|
||
|
|
`visited_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
|
|
PRIMARY KEY (`id`),
|
||
|
|
FOREIGN KEY (`article_id`) REFERENCES `blog_articles`(`id`) ON DELETE SET NULL,
|
||
|
|
FOREIGN KEY (`category_id`) REFERENCES `blog_categories`(`id`) ON DELETE SET NULL,
|
||
|
|
INDEX `idx_article_id` (`article_id`),
|
||
|
|
INDEX `idx_page_type` (`page_type`),
|
||
|
|
INDEX `idx_visited_at` (`visited_at`),
|
||
|
|
INDEX `idx_session_id` (`session_id`)
|
||
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
||
|
|
|
||
|
|
-- Blog Newsletter Subscriptions (specific to blog)
|
||
|
|
CREATE TABLE IF NOT EXISTS `blog_subscriptions` (
|
||
|
|
`id` INT(11) NOT NULL AUTO_INCREMENT,
|
||
|
|
`email` VARCHAR(100) NOT NULL,
|
||
|
|
`categories` JSON DEFAULT NULL,
|
||
|
|
`frequency` ENUM('immediate', 'daily', 'weekly', 'monthly') DEFAULT 'weekly',
|
||
|
|
`subscribed_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
|
|
`confirmed_at` TIMESTAMP NULL DEFAULT NULL,
|
||
|
|
`last_sent_at` TIMESTAMP NULL DEFAULT NULL,
|
||
|
|
`status` ENUM('pending', 'confirmed', 'unsubscribed') DEFAULT 'pending',
|
||
|
|
`confirmation_token` VARCHAR(255) DEFAULT NULL,
|
||
|
|
PRIMARY KEY (`id`),
|
||
|
|
UNIQUE KEY `unique_email` (`email`),
|
||
|
|
INDEX `idx_status` (`status`),
|
||
|
|
INDEX `idx_frequency` (`frequency`)
|
||
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
||
|
|
|
||
|
|
-- Insert default blog categories
|
||
|
|
INSERT IGNORE INTO `blog_categories` (`slug`, `name`, `description`, `meta_title`, `meta_description`, `sort_order`) VALUES
|
||
|
|
('web-scraping', 'Web Scraping', 'Expert guides on web scraping techniques, tools, and best practices for professional data extraction.', 'Web Scraping Articles & Guides | UK Data Services', 'Expert web scraping tutorials, techniques, and best practices from UK data professionals.', 1),
|
||
|
|
('data-analytics', 'Data Analytics', 'Business intelligence insights, data analysis methodologies, and advanced analytics techniques.', 'Data Analytics Articles & Insights | UK Data Services', 'Expert data analytics guides, business intelligence insights, and data science tutorials from UK professionals.', 2),
|
||
|
|
('business-intelligence', 'Business Intelligence', 'Strategic business intelligence solutions, automation strategies, and data-driven decision making.', 'Business Intelligence Insights | UK Data Services', 'Expert insights on business intelligence, data automation, and strategic data solutions.', 3);
|
||
|
|
|
||
|
|
-- Insert default blog tags
|
||
|
|
INSERT IGNORE INTO `blog_tags` (`slug`, `name`, `description`) VALUES
|
||
|
|
('uk-compliance', 'UK Compliance', 'UK-specific legal and regulatory compliance topics'),
|
||
|
|
('gdpr', 'GDPR', 'General Data Protection Regulation compliance and best practices'),
|
||
|
|
('automation', 'Automation', 'Data automation strategies and implementation'),
|
||
|
|
('roi-measurement', 'ROI Measurement', 'Return on investment calculation and measurement techniques'),
|
||
|
|
('web-scraping-tools', 'Web Scraping Tools', 'Tools and technologies for web scraping'),
|
||
|
|
('javascript', 'JavaScript', 'JavaScript-related web scraping and development topics'),
|
||
|
|
('python', 'Python', 'Python programming for data extraction and analysis'),
|
||
|
|
('competitive-intelligence', 'Competitive Intelligence', 'Competitive analysis and market intelligence'),
|
||
|
|
('data-quality', 'Data Quality', 'Data validation, cleaning, and quality assurance'),
|
||
|
|
('uk-business', 'UK Business', 'UK-specific business topics and strategies');
|
||
|
|
|
||
|
|
-- Insert current blog articles
|
||
|
|
INSERT IGNORE INTO `blog_articles`
|
||
|
|
(`slug`, `title`, `subtitle`, `excerpt`, `category_id`, `author_name`, `author_title`, `meta_title`, `meta_description`, `reading_time_minutes`, `is_published`, `is_featured`, `published_at`)
|
||
|
|
VALUES
|
||
|
|
(
|
||
|
|
'web-scraping-compliance-uk-guide',
|
||
|
|
'Web Scraping Compliance in the UK: Legal Framework and Best Practices',
|
||
|
|
'Navigate the complex legal landscape of UK data protection laws and ensure your web scraping activities remain fully compliant.',
|
||
|
|
'Comprehensive guide to UK web scraping compliance covering GDPR, legal frameworks, and best practices for professional data extraction.',
|
||
|
|
1,
|
||
|
|
'UK Data Services Legal Team',
|
||
|
|
'Legal and Compliance Specialists',
|
||
|
|
'UK Web Scraping Compliance Guide: Legal Framework & Best Practices',
|
||
|
|
'Navigate UK web scraping laws with our comprehensive compliance guide. GDPR, legal frameworks, and best practices for professional data extraction.',
|
||
|
|
12,
|
||
|
|
TRUE,
|
||
|
|
TRUE,
|
||
|
|
'2025-06-08 09:00:00'
|
||
|
|
),
|
||
|
|
(
|
||
|
|
'javascript-heavy-sites-scraping',
|
||
|
|
'Advanced Techniques for Scraping JavaScript-Heavy Websites',
|
||
|
|
'Master the challenges of extracting data from dynamic websites using modern browser automation and rendering techniques.',
|
||
|
|
'Learn advanced techniques for scraping modern JavaScript-heavy websites using browser automation, headless browsers, and dynamic content extraction.',
|
||
|
|
1,
|
||
|
|
'UK Data Services Technical Team',
|
||
|
|
'Web Scraping Specialists',
|
||
|
|
'Scraping JavaScript Websites: Advanced Techniques & Tools',
|
||
|
|
'Master JavaScript website scraping with advanced techniques, browser automation, and dynamic content extraction strategies.',
|
||
|
|
8,
|
||
|
|
TRUE,
|
||
|
|
FALSE,
|
||
|
|
'2025-06-01 10:00:00'
|
||
|
|
),
|
||
|
|
(
|
||
|
|
'competitive-intelligence-roi-metrics',
|
||
|
|
'Measuring ROI from Competitive Intelligence Programmes',
|
||
|
|
'Learn how to quantify the business value of competitive intelligence initiatives and demonstrate measurable returns.',
|
||
|
|
'Comprehensive guide to measuring ROI from competitive intelligence programmes with proven frameworks, metrics, and calculation methods.',
|
||
|
|
2,
|
||
|
|
'UK Data Services Analytics Team',
|
||
|
|
'Business Intelligence Specialists',
|
||
|
|
'Measuring ROI from Competitive Intelligence: UK Business Guide',
|
||
|
|
'Learn how to quantify competitive intelligence ROI with proven frameworks, metrics, and calculation methods for UK businesses.',
|
||
|
|
15,
|
||
|
|
TRUE,
|
||
|
|
TRUE,
|
||
|
|
'2025-06-05 09:00:00'
|
||
|
|
),
|
||
|
|
(
|
||
|
|
'data-automation-strategies-uk-businesses',
|
||
|
|
'Data Automation Strategies for UK Businesses: A Complete Implementation Guide',
|
||
|
|
'Transform your operations with intelligent automation that reduces costs by up to 40% while improving accuracy and decision-making speed.',
|
||
|
|
'Discover proven data automation strategies that UK businesses use to reduce costs by 40% and improve decision-making with complete implementation frameworks.',
|
||
|
|
3,
|
||
|
|
'UK Data Services Team',
|
||
|
|
'Business Intelligence Specialists',
|
||
|
|
'Data Automation Strategies for UK Businesses: Complete Guide',
|
||
|
|
'Discover proven data automation strategies that UK businesses use to reduce costs by 40% and improve decision-making. Complete guide with implementation frameworks.',
|
||
|
|
12,
|
||
|
|
TRUE,
|
||
|
|
TRUE,
|
||
|
|
'2025-06-08 09:00:00'
|
||
|
|
);
|
||
|
|
|
||
|
|
-- Create article-tag relationships
|
||
|
|
INSERT IGNORE INTO `blog_article_tags` (`article_id`, `tag_id`)
|
||
|
|
SELECT a.id, t.id FROM `blog_articles` a, `blog_tags` t
|
||
|
|
WHERE (a.slug = 'web-scraping-compliance-uk-guide' AND t.slug IN ('uk-compliance', 'gdpr', 'web-scraping-tools', 'uk-business'))
|
||
|
|
OR (a.slug = 'javascript-heavy-sites-scraping' AND t.slug IN ('javascript', 'web-scraping-tools', 'python'))
|
||
|
|
OR (a.slug = 'competitive-intelligence-roi-metrics' AND t.slug IN ('competitive-intelligence', 'roi-measurement', 'uk-business'))
|
||
|
|
OR (a.slug = 'data-automation-strategies-uk-businesses' AND t.slug IN ('automation', 'roi-measurement', 'uk-business'));
|
||
|
|
|
||
|
|
-- Create useful views for blog analytics
|
||
|
|
CREATE OR REPLACE VIEW `blog_popular_articles` AS
|
||
|
|
SELECT
|
||
|
|
a.id,
|
||
|
|
a.slug,
|
||
|
|
a.title,
|
||
|
|
a.published_at,
|
||
|
|
c.name as category_name,
|
||
|
|
COUNT(ba.id) as total_views,
|
||
|
|
COUNT(DISTINCT ba.session_id) as unique_visitors,
|
||
|
|
AVG(ba.reading_time_seconds) as avg_reading_time,
|
||
|
|
AVG(ba.scroll_percentage) as avg_scroll_percentage
|
||
|
|
FROM blog_articles a
|
||
|
|
LEFT JOIN blog_categories c ON a.category_id = c.id
|
||
|
|
LEFT JOIN blog_analytics ba ON a.id = ba.article_id
|
||
|
|
WHERE a.is_published = TRUE
|
||
|
|
GROUP BY a.id, a.slug, a.title, a.published_at, c.name
|
||
|
|
ORDER BY total_views DESC;
|
||
|
|
|
||
|
|
CREATE OR REPLACE VIEW `blog_category_stats` AS
|
||
|
|
SELECT
|
||
|
|
c.id,
|
||
|
|
c.slug,
|
||
|
|
c.name,
|
||
|
|
COUNT(a.id) as total_articles,
|
||
|
|
COUNT(CASE WHEN a.is_published = TRUE THEN 1 END) as published_articles,
|
||
|
|
COUNT(CASE WHEN a.is_featured = TRUE THEN 1 END) as featured_articles,
|
||
|
|
COUNT(ba.id) as total_views,
|
||
|
|
MAX(a.published_at) as latest_article_date
|
||
|
|
FROM blog_categories c
|
||
|
|
LEFT JOIN blog_articles a ON c.id = a.category_id
|
||
|
|
LEFT JOIN blog_analytics ba ON c.id = ba.category_id
|
||
|
|
GROUP BY c.id, c.slug, c.name
|
||
|
|
ORDER BY c.sort_order;
|
||
|
|
|
||
|
|
-- Create stored procedure for blog analytics
|
||
|
|
DELIMITER //
|
||
|
|
CREATE PROCEDURE GetBlogMonthlyStats(IN target_month DATE)
|
||
|
|
BEGIN
|
||
|
|
SELECT
|
||
|
|
'Total Articles Published' as metric,
|
||
|
|
COUNT(*) as value
|
||
|
|
FROM blog_articles
|
||
|
|
WHERE is_published = TRUE
|
||
|
|
AND YEAR(published_at) = YEAR(target_month)
|
||
|
|
AND MONTH(published_at) = MONTH(target_month)
|
||
|
|
|
||
|
|
UNION ALL
|
||
|
|
|
||
|
|
SELECT
|
||
|
|
'Total Blog Views' as metric,
|
||
|
|
COUNT(*) as value
|
||
|
|
FROM blog_analytics
|
||
|
|
WHERE YEAR(visited_at) = YEAR(target_month)
|
||
|
|
AND MONTH(visited_at) = MONTH(target_month)
|
||
|
|
|
||
|
|
UNION ALL
|
||
|
|
|
||
|
|
SELECT
|
||
|
|
'Unique Blog Visitors' as metric,
|
||
|
|
COUNT(DISTINCT session_id) as value
|
||
|
|
FROM blog_analytics
|
||
|
|
WHERE YEAR(visited_at) = YEAR(target_month)
|
||
|
|
AND MONTH(visited_at) = MONTH(target_month)
|
||
|
|
|
||
|
|
UNION ALL
|
||
|
|
|
||
|
|
SELECT
|
||
|
|
'Blog Newsletter Subscriptions' as metric,
|
||
|
|
COUNT(*) as value
|
||
|
|
FROM blog_subscriptions
|
||
|
|
WHERE status = 'confirmed'
|
||
|
|
AND YEAR(confirmed_at) = YEAR(target_month)
|
||
|
|
AND MONTH(confirmed_at) = MONTH(target_month);
|
||
|
|
END //
|
||
|
|
DELIMITER ;
|
||
|
|
|
||
|
|
-- Update existing contact_submissions table to link to blog articles if needed
|
||
|
|
ALTER TABLE `contact_submissions`
|
||
|
|
ADD COLUMN `source_article_id` INT(11) DEFAULT NULL AFTER `service`,
|
||
|
|
ADD COLUMN `source_page` VARCHAR(255) DEFAULT NULL AFTER `source_article_id`;
|
||
|
|
|
||
|
|
-- Add foreign key for source article
|
||
|
|
ALTER TABLE `contact_submissions`
|
||
|
|
ADD FOREIGN KEY `fk_source_article` (`source_article_id`) REFERENCES `blog_articles`(`id`) ON DELETE SET NULL;
|
||
|
|
|
||
|
|
-- Update quote_requests to track blog sources
|
||
|
|
ALTER TABLE `quote_requests`
|
||
|
|
ADD COLUMN `source_article_id` INT(11) DEFAULT NULL AFTER `timeline`,
|
||
|
|
ADD COLUMN `source_page` VARCHAR(255) DEFAULT NULL AFTER `source_article_id`;
|
||
|
|
|
||
|
|
-- Add foreign key for quote source article
|
||
|
|
ALTER TABLE `quote_requests`
|
||
|
|
ADD FOREIGN KEY `fk_quote_source_article` (`source_article_id`) REFERENCES `blog_articles`(`id`) ON DELETE SET NULL;
|
||
|
|
|
||
|
|
-- Create indexes for new columns
|
||
|
|
CREATE INDEX `idx_contact_source_article` ON `contact_submissions`(`source_article_id`);
|
||
|
|
CREATE INDEX `idx_contact_source_page` ON `contact_submissions`(`source_page`);
|
||
|
|
CREATE INDEX `idx_quote_source_article` ON `quote_requests`(`source_article_id`);
|
||
|
|
CREATE INDEX `idx_quote_source_page` ON `quote_requests`(`source_page`);
|
||
|
|
|
||
|
|
SET FOREIGN_KEY_CHECKS = 1;
|
||
|
|
|
||
|
|
-- Insert sample analytics data to test the system
|
||
|
|
INSERT IGNORE INTO `blog_analytics` (`article_id`, `page_type`, `page_url`, `reading_time_seconds`, `scroll_percentage`, `visited_at`)
|
||
|
|
SELECT
|
||
|
|
a.id,
|
||
|
|
'article',
|
||
|
|
CONCAT('/blog/articles/', a.slug),
|
||
|
|
FLOOR(RAND() * 300) + 60,
|
||
|
|
FLOOR(RAND() * 100) + 1,
|
||
|
|
DATE_SUB(NOW(), INTERVAL FLOOR(RAND() * 30) DAY)
|
||
|
|
FROM blog_articles a
|
||
|
|
WHERE a.is_published = TRUE
|
||
|
|
ORDER BY RAND()
|
||
|
|
LIMIT 50;
|