205 lines
7.3 KiB
MySQL
205 lines
7.3 KiB
MySQL
|
-- UK Data Services Database Initialization
|
||
|
|
-- Creates tables for contact forms, analytics, and user management
|
||
|
|
|
||
|
|
-- Set charset and collation
|
||
|
|
SET NAMES utf8mb4;
|
||
|
|
SET FOREIGN_KEY_CHECKS = 0;
|
||
|
|
|
||
|
|
-- Contact Form Submissions
|
||
|
|
CREATE TABLE IF NOT EXISTS `contact_submissions` (
|
||
|
|
`id` INT(11) NOT NULL AUTO_INCREMENT,
|
||
|
|
`name` VARCHAR(100) NOT NULL,
|
||
|
|
`email` VARCHAR(100) NOT NULL,
|
||
|
|
`company` VARCHAR(100) DEFAULT NULL,
|
||
|
|
`service` VARCHAR(50) DEFAULT NULL,
|
||
|
|
`message` TEXT NOT NULL,
|
||
|
|
`ip_address` VARCHAR(45) DEFAULT NULL,
|
||
|
|
`user_agent` TEXT DEFAULT NULL,
|
||
|
|
`submitted_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
|
|
`status` ENUM('new', 'contacted', 'converted', 'closed') DEFAULT 'new',
|
||
|
|
`notes` TEXT DEFAULT NULL,
|
||
|
|
PRIMARY KEY (`id`),
|
||
|
|
INDEX `idx_email` (`email`),
|
||
|
|
INDEX `idx_submitted_at` (`submitted_at`),
|
||
|
|
INDEX `idx_status` (`status`)
|
||
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
||
|
|
|
||
|
|
-- Quote Requests
|
||
|
|
CREATE TABLE IF NOT EXISTS `quote_requests` (
|
||
|
|
`id` INT(11) NOT NULL AUTO_INCREMENT,
|
||
|
|
`company_name` VARCHAR(100) NOT NULL,
|
||
|
|
`first_name` VARCHAR(50) NOT NULL,
|
||
|
|
`last_name` VARCHAR(50) NOT NULL,
|
||
|
|
`email` VARCHAR(100) NOT NULL,
|
||
|
|
`phone` VARCHAR(20) DEFAULT NULL,
|
||
|
|
`company_size` VARCHAR(20) DEFAULT NULL,
|
||
|
|
`services_needed` JSON DEFAULT NULL,
|
||
|
|
`project_details` TEXT DEFAULT NULL,
|
||
|
|
`budget` VARCHAR(20) DEFAULT NULL,
|
||
|
|
`timeline` VARCHAR(20) DEFAULT NULL,
|
||
|
|
`ip_address` VARCHAR(45) DEFAULT NULL,
|
||
|
|
`submitted_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
|
|
`status` ENUM('new', 'reviewing', 'quoted', 'accepted', 'declined') DEFAULT 'new',
|
||
|
|
`quote_amount` DECIMAL(10,2) DEFAULT NULL,
|
||
|
|
`notes` TEXT DEFAULT NULL,
|
||
|
|
PRIMARY KEY (`id`),
|
||
|
|
INDEX `idx_email` (`email`),
|
||
|
|
INDEX `idx_submitted_at` (`submitted_at`),
|
||
|
|
INDEX `idx_status` (`status`)
|
||
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
||
|
|
|
||
|
|
-- Site Analytics (basic tracking)
|
||
|
|
CREATE TABLE IF NOT EXISTS `site_analytics` (
|
||
|
|
`id` INT(11) NOT NULL AUTO_INCREMENT,
|
||
|
|
`page_url` VARCHAR(255) NOT NULL,
|
||
|
|
`referrer` VARCHAR(255) DEFAULT NULL,
|
||
|
|
`user_agent` TEXT DEFAULT NULL,
|
||
|
|
`ip_address` VARCHAR(45) DEFAULT NULL,
|
||
|
|
`session_id` VARCHAR(255) DEFAULT NULL,
|
||
|
|
`visit_time` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
|
|
`page_load_time` INT DEFAULT NULL,
|
||
|
|
`country` VARCHAR(2) DEFAULT NULL,
|
||
|
|
`city` VARCHAR(100) DEFAULT NULL,
|
||
|
|
PRIMARY KEY (`id`),
|
||
|
|
INDEX `idx_page_url` (`page_url`),
|
||
|
|
INDEX `idx_visit_time` (`visit_time`),
|
||
|
|
INDEX `idx_session_id` (`session_id`)
|
||
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
||
|
|
|
||
|
|
-- Error Logs
|
||
|
|
CREATE TABLE IF NOT EXISTS `error_logs` (
|
||
|
|
`id` INT(11) NOT NULL AUTO_INCREMENT,
|
||
|
|
`error_type` VARCHAR(50) NOT NULL,
|
||
|
|
`error_message` TEXT NOT NULL,
|
||
|
|
`file_path` VARCHAR(255) DEFAULT NULL,
|
||
|
|
`line_number` INT DEFAULT NULL,
|
||
|
|
`stack_trace` TEXT DEFAULT NULL,
|
||
|
|
`user_agent` TEXT DEFAULT NULL,
|
||
|
|
`ip_address` VARCHAR(45) DEFAULT NULL,
|
||
|
|
`occurred_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
|
|
PRIMARY KEY (`id`),
|
||
|
|
INDEX `idx_error_type` (`error_type`),
|
||
|
|
INDEX `idx_occurred_at` (`occurred_at`)
|
||
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
||
|
|
|
||
|
|
-- Newsletter Subscriptions
|
||
|
|
CREATE TABLE IF NOT EXISTS `newsletter_subscriptions` (
|
||
|
|
`id` INT(11) NOT NULL AUTO_INCREMENT,
|
||
|
|
`email` VARCHAR(100) NOT NULL UNIQUE,
|
||
|
|
`name` VARCHAR(100) DEFAULT NULL,
|
||
|
|
`company` VARCHAR(100) DEFAULT NULL,
|
||
|
|
`interests` JSON DEFAULT NULL,
|
||
|
|
`subscribed_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
|
|
`confirmed_at` TIMESTAMP NULL DEFAULT NULL,
|
||
|
|
`unsubscribed_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_subscribed_at` (`subscribed_at`)
|
||
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
||
|
|
|
||
|
|
-- FAQ Search Tracking
|
||
|
|
CREATE TABLE IF NOT EXISTS `faq_searches` (
|
||
|
|
`id` INT(11) NOT NULL AUTO_INCREMENT,
|
||
|
|
`search_term` VARCHAR(255) NOT NULL,
|
||
|
|
`results_found` INT DEFAULT 0,
|
||
|
|
`ip_address` VARCHAR(45) DEFAULT NULL,
|
||
|
|
`searched_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
|
|
PRIMARY KEY (`id`),
|
||
|
|
INDEX `idx_search_term` (`search_term`),
|
||
|
|
INDEX `idx_searched_at` (`searched_at`)
|
||
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
||
|
|
|
||
|
|
-- User Sessions (for analytics)
|
||
|
|
CREATE TABLE IF NOT EXISTS `user_sessions` (
|
||
|
|
`session_id` VARCHAR(255) NOT NULL,
|
||
|
|
`ip_address` VARCHAR(45) DEFAULT NULL,
|
||
|
|
`user_agent` TEXT DEFAULT NULL,
|
||
|
|
`started_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
|
|
`last_activity` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
||
|
|
`pages_viewed` INT DEFAULT 1,
|
||
|
|
`referrer` VARCHAR(255) DEFAULT NULL,
|
||
|
|
`country` VARCHAR(2) DEFAULT NULL,
|
||
|
|
`is_bot` BOOLEAN DEFAULT FALSE,
|
||
|
|
PRIMARY KEY (`session_id`),
|
||
|
|
INDEX `idx_started_at` (`started_at`),
|
||
|
|
INDEX `idx_last_activity` (`last_activity`)
|
||
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
||
|
|
|
||
|
|
-- Service Worker Cache Log
|
||
|
|
CREATE TABLE IF NOT EXISTS `sw_cache_log` (
|
||
|
|
`id` INT(11) NOT NULL AUTO_INCREMENT,
|
||
|
|
`event_type` VARCHAR(50) NOT NULL,
|
||
|
|
`resource_url` VARCHAR(255) DEFAULT NULL,
|
||
|
|
`cache_status` VARCHAR(20) DEFAULT NULL,
|
||
|
|
`response_time` INT DEFAULT NULL,
|
||
|
|
`timestamp` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
|
|
`user_agent` TEXT DEFAULT NULL,
|
||
|
|
PRIMARY KEY (`id`),
|
||
|
|
INDEX `idx_event_type` (`event_type`),
|
||
|
|
INDEX `idx_timestamp` (`timestamp`)
|
||
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
||
|
|
|
||
|
|
-- Insert default data
|
||
|
|
INSERT IGNORE INTO `contact_submissions`
|
||
|
|
(`name`, `email`, `company`, `service`, `message`, `status`)
|
||
|
|
VALUES
|
||
|
|
('Test User', 'test@example.com', 'Test Company', 'data-cleaning', 'This is a test submission', 'new');
|
||
|
|
|
||
|
|
-- Create database views for analytics
|
||
|
|
CREATE OR REPLACE VIEW `daily_contact_stats` AS
|
||
|
|
SELECT
|
||
|
|
DATE(submitted_at) as date,
|
||
|
|
COUNT(*) as total_submissions,
|
||
|
|
COUNT(DISTINCT email) as unique_contacts,
|
||
|
|
SUM(CASE WHEN status = 'converted' THEN 1 ELSE 0 END) as conversions
|
||
|
|
FROM contact_submissions
|
||
|
|
WHERE submitted_at >= DATE_SUB(CURRENT_DATE, INTERVAL 30 DAY)
|
||
|
|
GROUP BY DATE(submitted_at)
|
||
|
|
ORDER BY date DESC;
|
||
|
|
|
||
|
|
CREATE OR REPLACE VIEW `popular_services` AS
|
||
|
|
SELECT
|
||
|
|
service,
|
||
|
|
COUNT(*) as request_count,
|
||
|
|
COUNT(DISTINCT email) as unique_requesters
|
||
|
|
FROM contact_submissions
|
||
|
|
WHERE service IS NOT NULL
|
||
|
|
AND submitted_at >= DATE_SUB(CURRENT_DATE, INTERVAL 90 DAY)
|
||
|
|
GROUP BY service
|
||
|
|
ORDER BY request_count DESC;
|
||
|
|
|
||
|
|
-- Create stored procedure for analytics
|
||
|
|
DELIMITER //
|
||
|
|
CREATE PROCEDURE GetMonthlyStats(IN target_month DATE)
|
||
|
|
BEGIN
|
||
|
|
SELECT
|
||
|
|
'Contact Submissions' as metric,
|
||
|
|
COUNT(*) as value
|
||
|
|
FROM contact_submissions
|
||
|
|
WHERE YEAR(submitted_at) = YEAR(target_month)
|
||
|
|
AND MONTH(submitted_at) = MONTH(target_month)
|
||
|
|
|
||
|
|
UNION ALL
|
||
|
|
|
||
|
|
SELECT
|
||
|
|
'Quote Requests' as metric,
|
||
|
|
COUNT(*) as value
|
||
|
|
FROM quote_requests
|
||
|
|
WHERE YEAR(submitted_at) = YEAR(target_month)
|
||
|
|
AND MONTH(submitted_at) = MONTH(target_month)
|
||
|
|
|
||
|
|
UNION ALL
|
||
|
|
|
||
|
|
SELECT
|
||
|
|
'Page Views' as metric,
|
||
|
|
COUNT(*) as value
|
||
|
|
FROM site_analytics
|
||
|
|
WHERE YEAR(visit_time) = YEAR(target_month)
|
||
|
|
AND MONTH(visit_time) = MONTH(target_month);
|
||
|
|
END //
|
||
|
|
DELIMITER ;
|
||
|
|
|
||
|
|
SET FOREIGN_KEY_CHECKS = 1;
|