

CREATE TABLE IF NOT EXISTS `hero_slides` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(500) NOT NULL,
  `subtitle` text DEFAULT NULL,
  `description` text DEFAULT NULL,
  `background_type` enum('image','video') NOT NULL DEFAULT 'image',
  `background_file` varchar(500) DEFAULT NULL,
  `background_url` varchar(500) DEFAULT NULL,
  `overlay_opacity` decimal(3,2) NOT NULL DEFAULT .60,
  `button1_text` varchar(100) DEFAULT 'Watch Portfolio',
  `button1_link` varchar(500) DEFAULT '#portfolio',
  `button1_style` varchar(50) DEFAULT 'primary',
  `button2_text` varchar(100) DEFAULT 'Explore Services',
  `button2_link` varchar(500) DEFAULT '#services',
  `button2_style` varchar(50) DEFAULT 'outline',
  `button3_text` varchar(100) DEFAULT NULL,
  `button3_link` varchar(500) DEFAULT NULL,
  `button3_style` varchar(50) DEFAULT 'outline',
  `animation_type` varchar(100) DEFAULT 'fade-up',
  `order_index` int(11) NOT NULL DEFAULT ,
  `status` enum('active','inactive') NOT NULL DEFAULT 'active',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT IGNORE INTO `hero_slides` (`title`, `subtitle`, `description`, `background_type`, `overlay_opacity`, `button1_text`, `button1_link`, `button2_text`, `button2_link`, `button3_text`, `button3_link`, `order_index`, `status`) VALUES
('Where Vision Meets AI Innovation', 'Scripted Perfect Media', 'We craft premium cinematic experiences powered by artificial intelligence. From blockbuster movies to brand commercials, we tell stories that captivate the world.', 'image', .60, 'Watch Portfolio', '#portfolio', 'Explore Services', '#services', 'Book Consultation', '#contact', , 'active'),
('Africa\'s Premier AI Creative Studio', 'Cinematic Excellence', 'Combining cutting-edge AI technology with world-class creative direction to produce visual content that competes on a global stage.', 'image', .60, 'View Our Work', '#portfolio', 'Start Training', '#training', 'Get in Touch', '#contact', 1, 'active');

CREATE TABLE IF NOT EXISTS `service_categories` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `slug` varchar(255) NOT NULL UNIQUE,
  `description` text DEFAULT NULL,
  `icon` varchar(100) DEFAULT NULL,
  `icon_type` enum('font','image') NOT NULL DEFAULT 'font',
  `image` varchar(500) DEFAULT NULL,
  `color` varchar(20) DEFAULT '#D4AF37',
  `order_index` int(11) NOT NULL DEFAULT ,
  `status` enum('active','inactive') NOT NULL DEFAULT 'active',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT IGNORE INTO `service_categories` (`name`, `slug`, `description`, `icon`, `color`, `order_index`) VALUES
('AI Entertainment & Blockbuster Productions', 'ai-entertainment', 'Creating original AI-powered blockbuster movies and cinematic series that captivate global audiences.', 'film', '#D4AF37', ),
('AI Commercial & Brand Advertisements', 'commercial-production', 'Professional commercial production with cutting-edge AI technology for brands and businesses.', 'briefcase', '#D4AF37', 1),
('Marketing & Advertising Solutions', 'marketing-advertising', 'Comprehensive digital marketing and advertising solutions to elevate your brand.', 'megaphone', '#D4AF37', 2),
('AI Training & Creative Education', 'ai-training', 'Empowering creators and businesses with AI skills through hands-on training and workshops.', 'graduation-cap', '#D4AF37', 3);

CREATE TABLE IF NOT EXISTS `services` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `category_id` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  `slug` varchar(255) NOT NULL,
  `description` text DEFAULT NULL,
  `long_description` longtext DEFAULT NULL,
  `icon` varchar(100) DEFAULT NULL,
  `image` varchar(500) DEFAULT NULL,
  `video_url` varchar(500) DEFAULT NULL,
  `price_range` varchar(100) DEFAULT NULL,
  `features` longtext DEFAULT NULL,
  `order_index` int(11) NOT NULL DEFAULT ,
  `featured` tinyint(1) NOT NULL DEFAULT ,
  `status` enum('active','inactive') NOT NULL DEFAULT 'active',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `category_id` (`category_id`),
  KEY `featured` (`featured`),
  FOREIGN KEY (`category_id`) REFERENCES `service_categories` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT IGNORE INTO `services` (`category_id`, `name`, `slug`, `description`, `icon`, `featured`, `order_index`) VALUES
(1, 'Original AI Blockbuster Movies', 'ai-blockbuster-movies', 'Full-length feature films created with advanced AI cinematography and storytelling.', 'film', 1, ),
(1, 'AI Cinematic Web Series', 'ai-web-series', 'Episodic content with cinematic quality, produced efficiently with AI technology.', 'tv', , 1),
(1, 'Short Films', 'short-films', 'Impactful short-form cinematic content for festivals and digital release.', 'play', , 2),
(1, 'Genre Productions (Romance, Drama, Thriller, Action)', 'genre-productions', 'Specialized genre content crafted with AI precision and creative direction.', 'clapperboard', , 3),
(1, 'African & Global Stories', 'african-global-stories', 'Authentic storytelling that celebrates African narratives and global perspectives.', 'globe', 1, 4),
(2, 'Product Advertisements', 'product-ads', 'Stunning product showcases that drive conversions and brand recall.', 'package', 1, ),
(2, 'Brand Commercials', 'brand-commercials', 'Emotional brand storytelling that builds lasting connections with audiences.', 'award', 1, 1),
(2, 'Fashion & Beauty Campaigns', 'fashion-beauty', 'Visually stunning campaigns for fashion and beauty brands.', 'sparkles', , 2),
(2, 'Real Estate Promotions', 'real-estate', 'Cinematic property tours and real estate marketing content.', 'building', , 3),
(2, 'Corporate Promotional Videos', 'corporate-videos', 'Professional corporate content for internal and external communication.', 'briefcase', , 4),
(3, 'Digital Advertising', 'digital-advertising', 'Targeted ad campaigns across all major digital platforms.', 'target', 1, ),
(3, 'Social Media Creatives', 'social-media-creatives', 'Engaging social content that stops the scroll and drives engagement.', 'message-circle', , 1),
(3, 'Brand Awareness Campaigns', 'brand-awareness', 'Strategic campaigns that put your brand in the spotlight.', 'eye', , 2),
(3, 'Campaign Strategy & Consulting', 'campaign-strategy', 'Data-driven strategies that maximize ROI and brand impact.', 'lightbulb', , 3),
(4, 'AI Content Creation', 'ai-content-creation', 'Master AI tools for creating stunning content across all media.', 'cpu', 1, ),
(4, 'Prompt Engineering', 'prompt-engineering', 'Learn the art and science of crafting effective AI prompts.', 'terminal', , 1),
(4, 'AI Video Production', 'ai-video-production', 'Hands-on training in AI-powered video creation and editing.', 'video', 1, 2),
(4, 'Business & Organization Training', 'business-training', 'Corporate training programs for AI adoption and integration.', 'users', , 3);

CREATE TABLE IF NOT EXISTS `project_categories` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `slug` varchar(255) NOT NULL UNIQUE,
  `order_index` int(11) NOT NULL DEFAULT ,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT IGNORE INTO `project_categories` (`name`, `slug`, `order_index`) VALUES
('All', 'all', ),
('Movies', 'movies', 1),
('Commercials', 'commercials', 2),
('AI Projects', 'ai-projects', 3),
('Training', 'training', 4),
('Photography', 'photography', 5);

CREATE TABLE IF NOT EXISTS `projects` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `category_id` int(11) DEFAULT NULL,
  `title` varchar(500) NOT NULL,
  `slug` varchar(500) NOT NULL UNIQUE,
  `description` text DEFAULT NULL,
  `content` longtext DEFAULT NULL,
  `media_type` enum('video','image','youtube') NOT NULL DEFAULT 'image',
  `media_file` varchar(500) DEFAULT NULL,
  `media_url` varchar(500) DEFAULT NULL,
  `thumbnail` varchar(500) DEFAULT NULL,
  `poster` varchar(500) DEFAULT NULL,
  `duration` varchar(20) DEFAULT NULL,
  `client` varchar(255) DEFAULT NULL,
  `date` date DEFAULT NULL,
  `tags` varchar(500) DEFAULT NULL,
  `featured` tinyint(1) NOT NULL DEFAULT ,
  `trending` tinyint(1) NOT NULL DEFAULT ,
  `views` int(11) NOT NULL DEFAULT ,
  `order_index` int(11) NOT NULL DEFAULT ,
  `status` enum('published','draft') NOT NULL DEFAULT 'draft',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `category_id` (`category_id`),
  KEY `featured` (`featured`),
  KEY `trending` (`trending`),
  KEY `status` (`status`),
  FOREIGN KEY (`category_id`) REFERENCES `project_categories` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT IGNORE INTO `projects` (`category_id`, `title`, `slug`, `description`, `media_type`, `media_url`, `featured`, `trending`, `status`) VALUES
(1, 'Echoes of Tomorrow', 'echoes-of-tomorrow', 'A groundbreaking AI-generated sci-fi feature film exploring the future of human consciousness.', 'youtube', 'https://www.youtube.com/watch?v=dQw4w9WgXcQ', 1, 1, 'published'),
(1, 'Lagos Love Story', 'lagos-love-story', 'A romantic drama set in the vibrant heart of Lagos, Nigeria.', 'youtube', 'https://www.youtube.com/watch?v=dQw4w9WgXcQ', 1, 1, 'published'),
(2, 'Luxury Heights - Real Estate', 'luxury-heights-real-estate', 'Cinematic commercial for a premium Lagos luxury real estate development.', 'youtube', 'https://www.youtube.com/watch?v=dQw4w9WgXcQ', 1, , 'published'),
(2, 'Essence Beauty Campaign', 'essence-beauty', 'AI-enhanced beauty campaign for a premium skincare brand.', 'youtube', 'https://www.youtube.com/watch?v=dQw4w9WgXcQ', , 1, 'published'),
(3, 'AI Genesis - Tech Demo', 'ai-genesis-demo', 'Showcasing the capabilities of our AI video generation technology.', 'youtube', 'https://www.youtube.com/watch?v=dQw4w9WgXcQ', 1, 1, 'published');

CREATE TABLE IF NOT EXISTS `testimonials` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `position` varchar(255) DEFAULT NULL,
  `company` varchar(255) DEFAULT NULL,
  `avatar` varchar(500) DEFAULT NULL,
  `content` text NOT NULL,
  `rating` tinyint(1) NOT NULL DEFAULT 5,
  `featured` tinyint(1) NOT NULL DEFAULT ,
  `order_index` int(11) NOT NULL DEFAULT ,
  `status` enum('active','inactive') NOT NULL DEFAULT 'active',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT IGNORE INTO `testimonials` (`name`, `position`, `company`, `content`, `rating`, `featured`) VALUES
('Chioma Okafor', 'CEO', 'Luxury Heights Properties', 'Scripted Perfect Media transformed our brand presence. Their cinematic approach to commercial production elevated our real estate marketing to an entirely new level.', 5, 1),
('Ahmed Bello', 'Creative Director', 'AfroBeat Records', 'Working with Scripted Perfect was a game-changer. Their AI-enhanced music videos and promotional content helped us reach millions across Africa.', 5, 1),
('Sarah Williams', 'Marketing Manager', 'Global Tech Solutions', 'The AI training program equipped our team with cutting-edge skills. The hands-on workshops were incredibly practical and immediately applicable.', 5, 1);

CREATE TABLE IF NOT EXISTS `partners` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `logo` varchar(500) DEFAULT NULL,
  `url` varchar(500) DEFAULT NULL,
  `order_index` int(11) NOT NULL DEFAULT ,
  `status` enum('active','inactive') NOT NULL DEFAULT 'active',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `statistics` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `label` varchar(255) NOT NULL,
  `value` varchar(50) NOT NULL,
  `suffix` varchar(20) DEFAULT '+',
  `icon` varchar(100) DEFAULT NULL,
  `order_index` int(11) NOT NULL DEFAULT ,
  `status` enum('active','inactive') NOT NULL DEFAULT 'active',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT IGNORE INTO `statistics` (`label`, `value`, `suffix`, `order_index`) VALUES
('Projects Completed', '500', '+', ),
('Happy Clients', '200', '+', 1),
('AI Movies Produced', '50', '+', 2),
('Students Trained', '100', '+', 3);

CREATE TABLE IF NOT EXISTS `bookings` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `type` enum('consultation','service','quotation','training','project') NOT NULL DEFAULT 'consultation',
  `name` varchar(255) NOT NULL,
  `email` varchar(255) NOT NULL,
  `phone` varchar(50) DEFAULT NULL,
  `company` varchar(255) DEFAULT NULL,
  `service_type` varchar(255) DEFAULT NULL,
  `message` text DEFAULT NULL,
  `details` longtext DEFAULT NULL,
  `status` enum('new','read','replied','completed','cancelled') NOT NULL DEFAULT 'new',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `status` (`status`),
  KEY `type` (`type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `subscribers` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `email` varchar(255) NOT NULL UNIQUE,
  `name` varchar(255) DEFAULT NULL,
  `status` enum('active','unsubscribed') NOT NULL DEFAULT 'active',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `blog_posts` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(500) NOT NULL,
  `slug` varchar(500) NOT NULL UNIQUE,
  `excerpt` text DEFAULT NULL,
  `content` longtext DEFAULT NULL,
  `image` varchar(500) DEFAULT NULL,
  `author_id` int(11) DEFAULT NULL,
  `tags` varchar(500) DEFAULT NULL,
  `featured` tinyint(1) NOT NULL DEFAULT ,
  `views` int(11) NOT NULL DEFAULT ,
  `status` enum('published','draft') NOT NULL DEFAULT 'draft',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `author_id` (`author_id`),
  KEY `status` (`status`),
  KEY `featured` (`featured`),
  FOREIGN KEY (`author_id`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `menus` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `location` varchar(100) NOT NULL DEFAULT 'main',
  `items` longtext 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`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT IGNORE INTO `menus` (`name`, `location`, `items`) VALUES
('Main Menu', 'main', '[{"label":"Home","url":"/","children":[]},{"label":"Services","url":"#services","children":[{"label":"AI Entertainment","url":"/services/ai-entertainment"},{"label":"Commercial Production","url":"/services/commercial-production"},{"label":"Marketing","url":"/services/marketing-advertising"},{"label":"AI Training","url":"/services/ai-training"}]},{"label":"Portfolio","url":"#portfolio","children":[]},{"label":"Training","url":"#training","children":[]},{"label":"About","url":"#about","children":[]},{"label":"Contact","url":"#contact","children":[]}]');

CREATE TABLE IF NOT EXISTS `analytics` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `page` varchar(500) DEFAULT NULL,
  `ip` varchar(45) DEFAULT NULL,
  `user_agent` text DEFAULT NULL,
  `referrer` varchar(500) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `page` (`page`),
  KEY `created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `media` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `file` varchar(500) NOT NULL,
  `type` enum('image','video','document','audio','other') NOT NULL DEFAULT 'image',
  `mime_type` varchar(100) DEFAULT NULL,
  `size` int(11) DEFAULT NULL,
  `width` int(11) DEFAULT NULL,
  `height` int(11) DEFAULT NULL,
  `alt` varchar(500) DEFAULT NULL,
  `caption` text DEFAULT NULL,
  `uploaded_by` int(11) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `uploaded_by` (`uploaded_by`),
  FOREIGN KEY (`uploaded_by`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
