-- Hostxolo Business Consulting Website - Database Schema
-- Version: 1.0
-- Author: Hostxolo Team

-- Create Database
CREATE DATABASE IF NOT EXISTS hostxolo CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE hostxolo;

-- ============================================
-- CORE TABLES
-- ============================================

-- Admin Users Table
CREATE TABLE admin_users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    role ENUM('super_admin', 'admin') DEFAULT 'admin',
    avatar VARCHAR(255) DEFAULT NULL,
    last_login DATETIME DEFAULT NULL,
    status TINYINT(1) DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Website Users Table
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    full_name VARCHAR(100) NOT NULL,
    email VARCHAR(100) NOT NULL UNIQUE,
    phone VARCHAR(20) DEFAULT NULL,
    password VARCHAR(255) NOT NULL,
    company_name VARCHAR(100) DEFAULT NULL,
    address TEXT DEFAULT NULL,
    city VARCHAR(50) DEFAULT NULL,
    state VARCHAR(50) DEFAULT NULL,
    country VARCHAR(50) DEFAULT 'India',
    pincode VARCHAR(10) DEFAULT NULL,
    avatar VARCHAR(255) DEFAULT NULL,
    email_verified TINYINT(1) DEFAULT 0,
    phone_verified TINYINT(1) DEFAULT 0,
    status ENUM('active', 'inactive', 'suspended') DEFAULT 'active',
    last_login DATETIME DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Site Settings Table
CREATE TABLE site_settings (
    id INT PRIMARY KEY AUTO_INCREMENT,
    setting_key VARCHAR(100) NOT NULL UNIQUE,
    setting_value TEXT,
    setting_group VARCHAR(50) DEFAULT 'general',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- ============================================
-- SUBSCRIPTION & PLANS TABLES
-- ============================================

-- Subscription Plans
CREATE TABLE subscription_plans (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    slug VARCHAR(100) NOT NULL UNIQUE,
    description TEXT,
    monthly_price DECIMAL(10,2) DEFAULT 0.00,
    yearly_price DECIMAL(10,2) DEFAULT 0.00,
    features TEXT,
    is_featured TINYINT(1) DEFAULT 0,
    sort_order INT DEFAULT 0,
    status TINYINT(1) DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- User Subscriptions
CREATE TABLE user_subscriptions (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    plan_id INT NOT NULL,
    subscription_type ENUM('monthly', 'yearly') DEFAULT 'monthly',
    amount DECIMAL(10,2) NOT NULL,
    start_date DATE NOT NULL,
    end_date DATE NOT NULL,
    status ENUM('active', 'expired', 'cancelled', 'pending') DEFAULT 'pending',
    payment_status ENUM('paid', 'unpaid', 'refunded') DEFAULT 'unpaid',
    payment_id VARCHAR(100) DEFAULT NULL,
    payment_method VARCHAR(50) DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (plan_id) REFERENCES subscription_plans(id)
);

-- ============================================
-- SERVICES & SOFTWARE TABLES
-- ============================================

-- Services Categories
CREATE TABLE service_categories (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    slug VARCHAR(100) NOT NULL UNIQUE,
    description TEXT,
    icon VARCHAR(50) DEFAULT 'bi-grid',
    image VARCHAR(255) DEFAULT NULL,
    sort_order INT DEFAULT 0,
    status TINYINT(1) DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Services
CREATE TABLE services (
    id INT PRIMARY KEY AUTO_INCREMENT,
    category_id INT DEFAULT NULL,
    name VARCHAR(100) NOT NULL,
    slug VARCHAR(100) NOT NULL UNIQUE,
    short_description TEXT,
    description LONGTEXT,
    features TEXT,
    icon VARCHAR(50) DEFAULT 'bi-briefcase',
    image VARCHAR(255) DEFAULT NULL,
    price_starting_from DECIMAL(10,2) DEFAULT NULL,
    meta_title VARCHAR(200) DEFAULT NULL,
    meta_description TEXT DEFAULT NULL,
    sort_order INT DEFAULT 0,
    status TINYINT(1) DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (category_id) REFERENCES service_categories(id) ON DELETE SET NULL
);

-- Software Products
CREATE TABLE software_products (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    slug VARCHAR(100) NOT NULL UNIQUE,
    category ENUM('crm', 'telecalling', 'erp', 'marketing', 'website_builder', 'ai_engine', 'other') DEFAULT 'other',
    short_description TEXT,
    description LONGTEXT,
    features TEXT,
    screenshots TEXT,
    demo_link VARCHAR(255) DEFAULT NULL,
    pricing_type ENUM('free', 'one_time', 'subscription', 'contact') DEFAULT 'contact',
    price DECIMAL(10,2) DEFAULT NULL,
    icon VARCHAR(50) DEFAULT 'bi-box',
    image VARCHAR(255) DEFAULT NULL,
    meta_title VARCHAR(200) DEFAULT NULL,
    meta_description TEXT DEFAULT NULL,
    sort_order INT DEFAULT 0,
    status TINYINT(1) DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- ============================================
-- CONTENT TABLES
-- ============================================

-- Blog Categories
CREATE TABLE blog_categories (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    slug VARCHAR(100) NOT NULL UNIQUE,
    description TEXT,
    meta_title VARCHAR(200) DEFAULT NULL,
    meta_description TEXT DEFAULT NULL,
    status TINYINT(1) DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Blog Posts
CREATE TABLE blog_posts (
    id INT PRIMARY KEY AUTO_INCREMENT,
    category_id INT DEFAULT NULL,
    title VARCHAR(200) NOT NULL,
    slug VARCHAR(200) NOT NULL UNIQUE,
    excerpt TEXT,
    content LONGTEXT,
    featured_image VARCHAR(255) DEFAULT NULL,
    author_name VARCHAR(100) DEFAULT 'Admin',
    author_id INT DEFAULT NULL,
    views INT DEFAULT 0,
    meta_title VARCHAR(200) DEFAULT NULL,
    meta_description TEXT DEFAULT NULL,
    meta_keywords TEXT DEFAULT NULL,
    status ENUM('published', 'draft', 'archived') DEFAULT 'draft',
    published_at DATETIME DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (category_id) REFERENCES blog_categories(id) ON DELETE SET NULL,
    FOREIGN KEY (author_id) REFERENCES admin_users(id) ON DELETE SET NULL
);

-- Homepage Sections
CREATE TABLE homepage_sections (
    id INT PRIMARY KEY AUTO_INCREMENT,
    section_key VARCHAR(50) NOT NULL UNIQUE,
    section_name VARCHAR(100) NOT NULL,
    title VARCHAR(200) DEFAULT NULL,
    subtitle TEXT,
    content LONGTEXT,
    image VARCHAR(255) DEFAULT NULL,
    button_text VARCHAR(50) DEFAULT NULL,
    button_link VARCHAR(255) DEFAULT NULL,
    sort_order INT DEFAULT 0,
    status TINYINT(1) DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Testimonials
CREATE TABLE testimonials (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    designation VARCHAR(100) DEFAULT NULL,
    company VARCHAR(100) DEFAULT NULL,
    content TEXT NOT NULL,
    image VARCHAR(255) DEFAULT NULL,
    rating TINYINT DEFAULT 5,
    sort_order INT DEFAULT 0,
    status TINYINT(1) DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Team Members
CREATE TABLE team_members (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    designation VARCHAR(100) NOT NULL,
    bio TEXT,
    image VARCHAR(255) DEFAULT NULL,
    email VARCHAR(100) DEFAULT NULL,
    phone VARCHAR(20) DEFAULT NULL,
    linkedin VARCHAR(255) DEFAULT NULL,
    twitter VARCHAR(255) DEFAULT NULL,
    facebook VARCHAR(255) DEFAULT NULL,
    instagram VARCHAR(255) DEFAULT NULL,
    sort_order INT DEFAULT 0,
    status TINYINT(1) DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Pages (CMS)
CREATE TABLE pages (
    id INT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(200) NOT NULL,
    slug VARCHAR(100) NOT NULL UNIQUE,
    content LONGTEXT,
    meta_title VARCHAR(200) DEFAULT NULL,
    meta_description TEXT DEFAULT NULL,
    meta_keywords TEXT DEFAULT NULL,
    template VARCHAR(50) DEFAULT 'default',
    status TINYINT(1) DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- ============================================
-- SUPPORT & COMMUNICATION TABLES
-- ============================================

-- Contact Inquiries
CREATE TABLE contact_inquiries (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) NOT NULL,
    phone VARCHAR(20) DEFAULT NULL,
    subject VARCHAR(200) NOT NULL,
    message TEXT NOT NULL,
    status ENUM('new', 'read', 'replied', 'archived') DEFAULT 'new',
    ip_address VARCHAR(45) DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Support Tickets
CREATE TABLE support_tickets (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    ticket_number VARCHAR(20) NOT NULL UNIQUE,
    subject VARCHAR(200) NOT NULL,
    description TEXT NOT NULL,
    category VARCHAR(50) DEFAULT 'general',
    priority ENUM('low', 'medium', 'high', 'urgent') DEFAULT 'medium',
    status ENUM('open', 'in_progress', 'resolved', 'closed') DEFAULT 'open',
    assigned_to INT DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (assigned_to) REFERENCES admin_users(id) ON DELETE SET NULL
);

-- Ticket Replies
CREATE TABLE ticket_replies (
    id INT PRIMARY KEY AUTO_INCREMENT,
    ticket_id INT NOT NULL,
    user_id INT DEFAULT NULL,
    admin_id INT DEFAULT NULL,
    message TEXT NOT NULL,
    attachments TEXT,
    is_admin_reply TINYINT(1) DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (ticket_id) REFERENCES support_tickets(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL,
    FOREIGN KEY (admin_id) REFERENCES admin_users(id) ON DELETE SET NULL
);

-- ============================================
-- PAYMENT & TRANSACTION TABLES
-- ============================================

-- Transactions
CREATE TABLE transactions (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    transaction_number VARCHAR(50) NOT NULL UNIQUE,
    transaction_type ENUM('subscription', 'service', 'software', 'refund') DEFAULT 'subscription',
    item_id INT DEFAULT NULL,
    item_type VARCHAR(50) DEFAULT NULL,
    amount DECIMAL(10,2) NOT NULL,
    tax_amount DECIMAL(10,2) DEFAULT 0.00,
    total_amount DECIMAL(10,2) NOT NULL,
    currency VARCHAR(3) DEFAULT 'INR',
    gateway VARCHAR(50) DEFAULT NULL,
    gateway_transaction_id VARCHAR(100) DEFAULT NULL,
    status ENUM('pending', 'success', 'failed', 'refunded', 'cancelled') DEFAULT 'pending',
    payment_method VARCHAR(50) DEFAULT NULL,
    invoice_number VARCHAR(50) DEFAULT NULL,
    notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- ============================================
-- SEO TABLES
-- ============================================

-- SEO Settings
CREATE TABLE seo_settings (
    id INT PRIMARY KEY AUTO_INCREMENT,
    page_type VARCHAR(50) NOT NULL,
    page_id INT DEFAULT NULL,
    meta_title VARCHAR(200) DEFAULT NULL,
    meta_description TEXT DEFAULT NULL,
    meta_keywords TEXT DEFAULT NULL,
    og_title VARCHAR(200) DEFAULT NULL,
    og_description TEXT DEFAULT NULL,
    og_image VARCHAR(255) DEFAULT NULL,
    canonical_url VARCHAR(255) DEFAULT NULL,
    schema_markup TEXT,
    robots_meta VARCHAR(50) DEFAULT 'index,follow',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY unique_seo (page_type, page_id)
);

-- URL Redirects
CREATE TABLE url_redirects (
    id INT PRIMARY KEY AUTO_INCREMENT,
    old_url VARCHAR(500) NOT NULL,
    new_url VARCHAR(500) NOT NULL,
    redirect_type INT DEFAULT 301,
    hits INT DEFAULT 0,
    status TINYINT(1) DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- ============================================
-- API & INTEGRATION TABLES
-- ============================================

-- API Credentials
CREATE TABLE api_credentials (
    id INT PRIMARY KEY AUTO_INCREMENT,
    service_name VARCHAR(50) NOT NULL,
    api_key VARCHAR(255) DEFAULT NULL,
    api_secret VARCHAR(255) DEFAULT NULL,
    api_endpoint VARCHAR(255) DEFAULT NULL,
    additional_settings TEXT,
    is_active TINYINT(1) DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Email Templates
CREATE TABLE email_templates (
    id INT PRIMARY KEY AUTO_INCREMENT,
    template_key VARCHAR(50) NOT NULL UNIQUE,
    template_name VARCHAR(100) NOT NULL,
    subject VARCHAR(200) NOT NULL,
    body_html LONGTEXT,
    body_text LONGTEXT,
    variables TEXT,
    status TINYINT(1) DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- SMS Logs
CREATE TABLE sms_logs (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT DEFAULT NULL,
    phone VARCHAR(20) NOT NULL,
    message TEXT NOT NULL,
    template_id VARCHAR(50) DEFAULT NULL,
    gateway VARCHAR(50) DEFAULT NULL,
    status ENUM('pending', 'sent', 'failed', 'delivered') DEFAULT 'pending',
    response TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
);

-- Email Logs
CREATE TABLE email_logs (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT DEFAULT NULL,
    email_to VARCHAR(100) NOT NULL,
    email_from VARCHAR(100) NOT NULL,
    subject VARCHAR(200) NOT NULL,
    body TEXT,
    template_id INT DEFAULT NULL,
    status ENUM('pending', 'sent', 'failed', 'bounced') DEFAULT 'pending',
    error_message TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL,
    FOREIGN KEY (template_id) REFERENCES email_templates(id) ON DELETE SET NULL
);

-- Activity Logs
CREATE TABLE activity_logs (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT DEFAULT NULL,
    user_type ENUM('user', 'admin') DEFAULT 'user',
    action VARCHAR(100) NOT NULL,
    description TEXT,
    ip_address VARCHAR(45) DEFAULT NULL,
    user_agent TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
);

-- ============================================
-- DEFAULT DATA INSERTION
-- ============================================

-- Default Admin User (Password: admin123)
INSERT INTO admin_users (name, email, password, role) VALUES
('Super Admin', 'admin@hostxolo.com', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'super_admin');

-- Site Settings
INSERT INTO site_settings (setting_key, setting_value, setting_group) VALUES
('site_name', 'Hostxolo', 'general'),
('site_tagline', 'Your Business Growth Partner', 'general'),
('site_email', 'support@hostxolo.com', 'general'),
('site_phone', '+91-6388758325', 'general'),
('site_address', 'India', 'general'),
('site_logo', 'assets/images/logo.png', 'general'),
('site_favicon', 'assets/images/favicon.ico', 'general'),
('footer_text', 'All rights reserved.', 'general'),
('social_facebook', 'https://facebook.com/hostxolo', 'social'),
('social_twitter', 'https://twitter.com/hostxolo', 'social'),
('social_instagram', 'https://instagram.com/hostxolo', 'social'),
('social_linkedin', 'https://linkedin.com/company/hostxolo', 'social'),
('smtp_host', 'smtp.gmail.com', 'email'),
('smtp_port', '587', 'email'),
('smtp_username', '', 'email'),
('smtp_password', '', 'email'),
('smtp_encryption', 'tls', 'email'),
('sms_gateway', '', 'sms'),
('sms_api_key', '', 'sms'),
('razorpay_key_id', '', 'payment'),
('razorpay_key_secret', '', 'payment'),
('stripe_publishable_key', '', 'payment'),
('stripe_secret_key', '', 'payment'),
('paypal_client_id', '', 'payment'),
('currency', 'INR', 'payment'),
('currency_symbol', '₹', 'payment');

-- Default Homepage Sections
INSERT INTO homepage_sections (section_key, section_name, title, subtitle, content, button_text, button_link, sort_order, status) VALUES
('hero', 'Hero Section', 'बिजनेस से जुड़ीं सभी Services के लिए एक ही प्लान, Business Growth Plan', 'हम आपके Business को Digitally Grow कराने में आपकी मदद करते हैं ..', 'Get all business services under one roof. No need to hire multiple service providers or employees. We provide complete business growth solutions.', 'GET APPOINTMENT', 'https://wa.me/916388758325?text=Hi..', 1, 1),
('stats', 'Statistics Section', 'Our Growth Story', NULL, NULL, NULL, NULL, 2, 1),
('services', 'Services Section', 'All Business Solutions Under One Roof', 'No More Services, No More Employees', 'We offer comprehensive business consulting services to help your business grow digitally.', 'SEE PLAN', '/packages', 3, 1),
('software', 'Software Section', 'Powerful Software Solutions', NULL, 'Transform your business with our cutting-edge software products designed for modern enterprises.', 'Explore Software', '/software', 4, 1),
('testimonials', 'Testimonials Section', 'Customer Review', NULL, NULL, NULL, NULL, 5, 1),
('blog', 'Blog Section', 'Have a look at the HOSXOLO latest Blogs', NULL, NULL, NULL, NULL, 6, 1),
('cta', 'Call to Action', 'Ready to Grow Your Business?', 'Join 250+ satisfied customers who trust Hostxolo', 'Start your business growth journey today with our comprehensive solutions.', 'Get Started', '/register', 7, 1);

-- Default Service Categories
INSERT INTO service_categories (name, slug, description, icon, sort_order, status) VALUES
('Development Services', 'development-services', 'Custom software and website development solutions', 'bi-code-square', 1, 1),
('Digital Marketing', 'digital-marketing', 'Complete digital marketing and advertising services', 'bi-graph-up-arrow', 2, 1),
('Business Automation', 'business-automation', 'IT consulting and business automation solutions', 'bi-gear', 3, 1),
('Cloud Solutions', 'cloud-solutions', 'Cloud telecalling and communication services', 'bi-cloud', 4, 1);

-- Default Services
INSERT INTO services (category_id, name, slug, short_description, description, features, icon, price_starting_from, sort_order, status) VALUES
(1, 'Website Development', 'website-development', 'Professional website development for your business', 'We create stunning, responsive websites that help your business establish a strong online presence. From simple landing pages to complex e-commerce solutions.', '["Responsive Design","SEO Optimized","Fast Loading","Modern UI/UX","Mobile Friendly","Content Management System"]', 'bi-globe', 9999.00, 1, 1),
(1, 'Mobile App Development', 'mobile-app-development', 'iOS and Android mobile application development', 'Custom mobile applications tailored to your business needs. We build native and cross-platform apps with cutting-edge technology.', '["iOS Development","Android Development","Cross-Platform","UI/UX Design","App Store Publishing","Maintenance & Support"]', 'bi-phone', 24999.00, 2, 1),
(1, 'Custom Software Development', 'custom-software-development', 'Bespoke software solutions for your business', 'Tailor-made software solutions designed specifically for your business processes and requirements.', '["Business Analysis","Custom Solutions","Integration","Scalable Architecture","Training & Support","Regular Updates"]', 'bi-laptop', 49999.00, 3, 1),
(2, 'Social Media Marketing', 'social-media-marketing', 'Grow your brand on social media platforms', 'Strategic social media marketing to build your brand presence and engage with your target audience across all major platforms.', '["Content Strategy","Post Creation","Community Management","Analytics","Ad Campaigns","Influencer Marketing"]', 'bi-share', 4999.00, 4, 1),
(2, 'SEO Services', 'seo-services', 'Rank higher on search engines', 'Comprehensive SEO services to improve your website visibility and drive organic traffic from search engines.', '["Keyword Research","On-Page SEO","Off-Page SEO","Technical SEO","Content Optimization","Monthly Reporting"]', 'bi-search', 5999.00, 5, 1),
(2, 'Google Ads Management', 'google-ads-management', 'Targeted advertising on Google', 'Expert Google Ads management to maximize your ROI and drive qualified leads to your business.', '["Campaign Setup","Keyword Targeting","Ad Copywriting","Bid Management","Conversion Tracking","Performance Reports"]', 'bi-google', 2999.00, 6, 1),
(3, 'Business Process Automation', 'business-process-automation', 'Automate repetitive business tasks', 'Streamline your business operations with intelligent automation solutions that save time and reduce errors.', '["Workflow Automation","Document Management","Email Automation","Reporting Automation","Integration","Training"]', 'bi-robot', 9999.00, 7, 1),
(3, 'IT Consulting', 'it-consulting', 'Strategic technology consulting', 'Expert IT consulting services to help you make informed technology decisions and optimize your IT infrastructure.', '["Technology Assessment","Digital Strategy","System Integration","Security Audit","Cloud Migration","IT Roadmap"]', 'bi-headset', 4999.00, 8, 1),
(4, 'Cloud Telecalling', 'cloud-telecalling', 'Professional cloud-based calling solutions', 'Advanced cloud telecalling software with CRM integration, call recording, and analytics.', '["Virtual Numbers","Call Recording","CRM Integration","IVR System","Call Analytics","Auto Dialer"]', 'bi-telephone', 1499.00, 9, 1);

-- Default Software Products
INSERT INTO software_products (name, slug, category, short_description, description, features, pricing_type, price, icon, sort_order, status) VALUES
('WhatsApp CRM', 'whatsapp-crm', 'crm', 'Manage customer relationships via WhatsApp', 'Comprehensive WhatsApp CRM solution to manage customer conversations, automate responses, and track leads all in one place.', '["Bulk Messaging","Contact Management","Conversation History","Automated Responses","Team Collaboration","Analytics Dashboard","API Integration","Broadcast Lists"]', 'subscription', 999.00, 'bi-whatsapp', 1, 1),
('Cloud Telecalling Software', 'cloud-telecalling-software', 'telecalling', 'Professional cloud-based calling solution', 'Advanced cloud telecalling system with auto-dialer, call recording, and comprehensive analytics for your sales team.', '["Auto Dialer","Call Recording","Call Analytics","CRM Integration","Virtual Numbers","IVR System","Call Scheduling","Performance Reports"]', 'subscription', 1499.00, 'bi-telephone', 2, 1),
('ERP Software', 'erp-software', 'erp', 'Complete enterprise resource planning solution', 'All-in-one ERP solution to manage your business operations including inventory, accounting, HR, and more.', '["Inventory Management","Accounting","HR Management","Sales Management","Purchase Management","Report Generation","Multi-Location","Role Management"]', 'subscription', 2999.00, 'bi-building', 3, 1),
('SMS Marketing', 'sms-marketing', 'marketing', 'Bulk SMS marketing platform', 'Reach thousands of customers instantly with our reliable SMS marketing platform with delivery reports.', '["Bulk SMS","Schedule SMS","Delivery Reports","Templates","API Integration","Contact Management","Campaign Analytics","DND Filter"]', 'subscription', 499.00, 'bi-chat-dots', 4, 1),
('Email Marketing', 'email-marketing', 'marketing', 'Professional email marketing solution', 'Create, send, and track professional email campaigns with our easy-to-use email marketing platform.', '["Drag & Drop Editor","Email Templates","A/B Testing","Automation","Analytics","List Management","Spam Testing","Integration"]', 'subscription', 799.00, 'bi-envelope', 5, 1),
('Social Media Marketing', 'social-media-marketing-software', 'marketing', 'Manage all social media from one dashboard', 'Schedule posts, monitor engagement, and analyze performance across all major social media platforms.', '["Post Scheduling","Content Calendar","Analytics","Multi-Account","Team Collaboration","Content Library","Auto Publishing","Reports"]', 'subscription', 999.00, 'bi-share', 6, 1),
('Website Builder', 'website-builder', 'website_builder', 'Create professional websites without coding', 'Drag and drop website builder with beautiful templates to create stunning websites in minutes.', '["Drag & Drop Builder","100+ Templates","Mobile Responsive","SEO Tools","E-commerce","Blog","Custom Domain","SSL Certificate"]', 'subscription', 599.00, 'bi-globe2', 7, 1),
('Digital vCard Builder', 'digital-vcard-builder', 'website_builder', 'Create digital business cards', 'Create professional digital business cards that can be shared instantly via QR code or link.', '["QR Code","Contact Sharing","Social Links","Custom Design","Analytics","NFC Support","Multiple Templates","Instant Updates"]', 'subscription', 299.00, 'bi-person-vcard', 8, 1),
('AI Content Generator', 'ai-content-generator', 'ai_engine', 'Generate content with AI', 'Create blog posts, social media content, and marketing copy in seconds with our AI content generator.', '["Blog Writing","Social Posts","Ad Copy","Email Templates","SEO Content","Multiple Languages","Tone Selection","Plagiarism Free"]', 'subscription', 1299.00, 'bi-stars', 9, 1),
('AI Agents for Business', 'ai-agents-business', 'ai_engine', 'Automate business tasks with AI agents', 'Intelligent AI agents that can handle customer queries, schedule appointments, and automate routine business tasks.', '["Customer Support","Appointment Booking","Lead Qualification","24/7 Availability","Multi-Language","Integration","Custom Training","Analytics"]', 'subscription', 1999.00, 'bi-cpu', 10, 1);

-- Default Subscription Plans
INSERT INTO subscription_plans (name, slug, description, monthly_price, yearly_price, features, is_featured, sort_order, status) VALUES
('Basic Growth Plan', 'basic-growth', 'Perfect for small businesses just starting their digital journey', 999.00, 9990.00, '["WhatsApp CRM Access","Email Support","Basic Analytics","1 User","5GB Storage","Monthly Reports"]', 0, 1, 1),
('Professional Growth Plan', 'professional-growth', 'Ideal for growing businesses with expanding needs', 2999.00, 29990.00, '["All Basic Features","Priority Support","Advanced Analytics","5 Users","50GB Storage","Weekly Reports","SMS Marketing","Email Marketing"]', 1, 2, 1),
('Enterprise Growth Plan', 'enterprise-growth', 'Complete solution for established businesses', 5999.00, 59990.00, '["All Professional Features","24/7 Phone Support","Custom Integrations","Unlimited Users","Unlimited Storage","Daily Reports","All Software Access","Dedicated Manager","White Label Options"]', 0, 3, 1);

-- Default Testimonials
INSERT INTO testimonials (name, designation, company, content, rating, sort_order, status) VALUES
('Rahul Gupta', 'CEO', 'Vintage Pvt. Ltd.', 'I was struggling with promoting my small business online. Thanks to their advertising support and easy-to-use software, I now get regular inquiries every week. Truly a game-changer!', 5, 1, 1),
('Sonu Singh', 'Founder', 'Kaveri Clotha', 'I had no idea how to grow my shop online. Their social media posts, management softwares, and ad support made it easy. Now more people know about my business and I have started getting more calls and orders within weeks.', 5, 2, 1),
('Prakash Chandra', 'Co-founder', 'Pepperberg Inv.', 'I run a small business and had trouble promoting it online. With their ads and management softwares, I now get new customers every week. Very helpful!', 5, 3, 1);

-- Default CMS Pages
INSERT INTO pages (title, slug, content, meta_title, meta_description, status) VALUES
('About Us', 'about-us', '<h2>Welcome to Hostxolo</h2><p>Hostxolo is a leading business consulting company dedicated to helping businesses grow digitally. With over 7 years of experience, we have served 250+ customers across 10+ countries, completing 480+ successful projects.</p><h3>Our Mission</h3><p>To empower businesses with digital solutions that drive growth and success in the modern marketplace.</p><h3>Our Vision</h3><p>To become the most trusted business growth partner for small and medium enterprises worldwide.</p>', 'About Us - Hostxolo Business Consulting', 'Learn about Hostxolo - your trusted partner for business growth consulting and digital transformation services.', 1),
('Privacy Policy', 'privacy-policy', '<h2>Privacy Policy</h2><p>At Hostxolo, we take your privacy seriously. This Privacy Policy describes how we collect, use, and protect your personal information.</p><h3>Information We Collect</h3><ul><li>Personal identification information (Name, email address, phone number)</li><li>Billing and payment information</li><li>Usage data and analytics</li></ul><h3>How We Use Your Information</h3><ul><li>To provide and maintain our services</li><li>To notify you about changes to our services</li><li>To provide customer support</li><li>To gather analysis and improve our services</li></ul><p>For complete privacy information, please contact us.</p>', 'Privacy Policy - Hostxolo', 'Read Hostxolo privacy policy to understand how we collect, use, and protect your personal information.', 1),
('Terms & Conditions', 'terms-conditions', '<h2>Terms and Conditions</h2><p>By accessing and using Hostxolo services, you accept and agree to be bound by these Terms and Conditions.</p><h3>Service Usage</h3><ul><li>You must provide accurate information when creating an account</li><li>You are responsible for maintaining the confidentiality of your account</li><li>You agree not to use the service for any illegal purposes</li></ul><h3>Payment Terms</h3><ul><li>All payments are processed securely through our payment gateways</li><li>Subscriptions auto-renew unless cancelled</li><li>Refunds are subject to our refund policy</li></ul><p>For complete terms, please contact our support team.</p>', 'Terms & Conditions - Hostxolo', 'Read the terms and conditions for using Hostxolo business consulting services and software products.', 1),
('Refund & Cancellation Policy', 'refund-cancellation', '<h2>Refund & Cancellation Policy</h2><p>We want you to be satisfied with our services. Please read our refund and cancellation policy carefully.</p><h3>Cancellation Policy</h3><ul><li>You can cancel your subscription at any time</li><li>Cancellation will take effect at the end of the current billing period</li><li>Annual plans can be cancelled with prorated refund within 30 days</li></ul><h3>Refund Policy</h3><ul><li>7-day money-back guarantee for new subscriptions</li><li>Prorated refunds for annual plans cancelled within 30 days</li><li>No refunds for monthly subscriptions after 7 days</li><li>Refunds processed within 5-7 business days</li></ul>', 'Refund & Cancellation Policy - Hostxolo', 'Learn about Hostxolo refund and cancellation policy for subscriptions and services.', 1),
('Disclaimer', 'disclaimer', '<h2>Disclaimer</h2><p>The information provided on Hostxolo is for general informational purposes only.</p><h3>Accuracy of Information</h3><p>While we strive to keep the information up to date and correct, we make no representations or warranties of any kind about the completeness, accuracy, reliability, or availability of the information.</p><h3>Professional Advice</h3><p>Our services and content are not intended to replace professional business, legal, or financial advice. Always seek the advice of qualified professionals for your specific situation.</p><p>By using our services, you acknowledge that you have read, understood, and agree to this disclaimer.</p>', 'Disclaimer - Hostxolo', 'Read Hostxolo disclaimer regarding the use of our business consulting services and website content.', 1),
('Contact Us', 'contact-us', '<h2>Contact Us</h2><p>We would love to hear from you. Reach out to us through any of the following channels:</p><h3>Contact Information</h3><ul><li><strong>Email:</strong> support@hostxolo.com</li><li><strong>Phone:</strong> +91-6388758325</li><li><strong>WhatsApp:</strong> +91-6388758325</li></ul><h3>Business Hours</h3><p>Monday to Saturday: 9:00 AM - 6:00 PM IST</p><p>Sunday: Closed</p>', 'Contact Us - Hostxolo Business Consulting', 'Contact Hostxolo for business consulting services, software solutions, and digital marketing services.', 1),
('Support / Help Desk', 'support', '<h2>Help & Support Center</h2><p>Welcome to the Hostxolo Support Center. We are here to help you with any questions or issues you may have.</p><h3>Frequently Asked Questions</h3><h4>How do I get started?</h4><p>Simply create an account and choose a subscription plan that fits your needs.</p><h4>What payment methods do you accept?</h4><p>We accept all major credit cards, UPI, net banking, and digital wallets.</p><h4>How can I contact support?</h4><p>You can reach our support team via email, phone, or by creating a support ticket from your dashboard.</p><h3>Need More Help?</h3><p>If you cannot find the answer to your question, please create a support ticket or contact us directly.</p>', 'Support & Help Desk - Hostxolo', 'Get help and support for Hostxolo services. Find FAQs, create support tickets, and contact our support team.', 1);

-- Default Blog Categories
INSERT INTO blog_categories (name, slug, description, status) VALUES
('Business Growth', 'business-growth', 'Tips and strategies for growing your business', 1),
('Digital Marketing', 'digital-marketing-blog', 'Latest trends in digital marketing', 1),
('Technology', 'technology', 'Tech insights and software solutions', 1),
('Success Stories', 'success-stories', 'Customer success stories and case studies', 1);

-- Default Blog Posts
INSERT INTO blog_posts (category_id, title, slug, excerpt, content, author_name, featured_image, status, published_at) VALUES
(1, 'How to Pivot Your Business Model When Growth Stalls', 'how-to-pivot-business-model', 'Learn strategies to pivot your business model and reignite growth when your business hits a plateau.', '<h2>Understanding Business Plateaus</h2><p>Every business faces periods where growth seems to stall. This is a natural part of the business lifecycle, but how you respond determines your future success.</p><h2>Signs You Need to Pivot</h2><ul><li>Declining sales over multiple quarters</li><li>Increased customer churn</li><li>Market saturation in your current segment</li><li>New competitors disrupting your space</li></ul><h2>Strategies for Successful Pivoting</h2><p>1. <strong>Listen to Your Customers</strong> - They will tell you what they need.</p><p>2. <strong>Analyze Market Trends</strong> - Stay ahead of industry changes.</p><p>3. <strong>Test New Ideas</strong> - Start small and validate before scaling.</p><p>4. <strong>Leverage Your Strengths</strong> - Build on what you do well.</p>', 'Admin', 'assets/images/blog/pivot-business.jpg', 'published', NOW()),
(1, 'Top Metrics Every Business Owner Should Monitor', 'top-metrics-business-owner', 'Discover the key performance indicators that every business owner should track for sustainable growth.', '<h2>Why Metrics Matter</h2><p>What gets measured gets managed. Tracking the right metrics helps you make informed decisions and identify opportunities for improvement.</p><h2>Essential Business Metrics</h2><h3>1. Customer Acquisition Cost (CAC)</h3><p>How much it costs to acquire a new customer. Lower CAC means more efficient marketing.</p><h3>2. Customer Lifetime Value (CLV)</h3><p>The total revenue you can expect from a single customer. Higher CLV justifies higher CAC.</p><h3>3. Monthly Recurring Revenue (MRR)</h3><p>For subscription businesses, MRR is the lifeblood of predictable growth.</p><h3>4. Churn Rate</h3><p>The percentage of customers who stop using your service. Lower churn means stickier product.</p><h3>5. Net Promoter Score (NPS)</h3><p>Measures customer satisfaction and likelihood to recommend your business.</p>', 'Admin', 'assets/images/blog/metrics.jpg', 'published', NOW()),
(2, 'How to Use Social Media to Drive Online Business Growth', 'social-media-drive-growth', 'Learn effective strategies to leverage social media platforms for business growth and customer engagement.', '<h2>The Power of Social Media</h2><p>Social media has transformed how businesses connect with customers. With billions of users across platforms, the opportunity for growth is immense.</p><h2>Platform-Specific Strategies</h2><h3>Instagram</h3><ul><li>Visual storytelling through posts and stories</li><li>Reels for maximum reach</li><li>Shopping features for direct sales</li></ul><h3>LinkedIn</h3><ul><li>Thought leadership content</li><li>B2B networking</li><li>Employee advocacy programs</li></ul><h3>Facebook</h3><ul><li>Community building through groups</li><li>Targeted advertising</li><li>Event promotion</li></ul><h2>Content Strategy Tips</h2><p>Create a content calendar, post consistently, engage with your audience, and always provide value.</p>', 'Admin', 'assets/images/blog/social-media.jpg', 'published', NOW()),
(3, '10 Proven Strategies for Online Business Growth in 2025', '10-strategies-business-growth-2025', 'Discover the top strategies that will help your online business thrive in 2025 and beyond.', '<h2>The Digital Landscape in 2025</h2><p>As we move further into the digital age, businesses must adapt to new technologies and consumer behaviors.</p><h2>10 Growth Strategies</h2><h3>1. AI-Powered Personalization</h3><p>Use AI to deliver personalized experiences at scale.</p><h3>2. Omnichannel Presence</h3><p>Be where your customers are across all touchpoints.</p><h3>3. Video Marketing</h3><p>Video continues to dominate content consumption.</p><h3>4. Voice Search Optimization</h3><p>Optimize for voice assistants and smart speakers.</p><h3>5. Sustainability Marketing</h3><p>Consumers prefer eco-conscious brands.</p><h3>6. Community Building</h3><p>Create loyal communities around your brand.</p><h3>7. Influencer Partnerships</h3><p>Micro-influencers drive authentic engagement.</p><h3>8. Mobile-First Approach</h3><p>Most traffic comes from mobile devices.</p><h3>9. Data-Driven Decisions</h3><p>Let analytics guide your strategy.</p><h3>10. Exceptional Customer Experience</h3><p>CX is the ultimate differentiator.</p>', 'Admin', 'assets/images/blog/strategies-2025.jpg', 'published', NOW());

-- Default Email Templates
INSERT INTO email_templates (template_key, template_name, subject, body_html, body_text, variables) VALUES
('welcome', 'Welcome Email', 'Welcome to Hostxolo - Your Business Growth Partner', '<html><body><h2>Welcome {{name}}!</h2><p>Thank you for joining Hostxolo. We are excited to help you grow your business.</p><p>Get started by exploring our services and subscription plans.</p><p><a href="{{login_url}}" style="padding:10px 20px;background:#007bff;color:white;text-decoration:none;">Login to Your Account</a></p><p>Need help? Contact us at support@hostxolo.com</p></body></html>', 'Welcome {{name}}! Thank you for joining Hostxolo. Login to your account: {{login_url}}', '["name","login_url"]'),
('password_reset', 'Password Reset', 'Reset Your Hostxolo Password', '<html><body><h2>Password Reset Request</h2><p>Hello {{name}},</p><p>You requested a password reset. Click the link below to reset your password:</p><p><a href="{{reset_url}}" style="padding:10px 20px;background:#007bff;color:white;text-decoration:none;">Reset Password</a></p><p>If you did not request this, please ignore this email.</p></body></html>', 'Password Reset: Click here to reset: {{reset_url}}', '["name","reset_url"]'),
('subscription_confirmation', 'Subscription Confirmation', 'Your Hostxolo Subscription is Active', '<html><body><h2>Subscription Confirmed!</h2><p>Hi {{name}},</p><p>Your subscription to {{plan_name}} is now active.</p><p><strong>Amount Paid:</strong> {{amount}}</p><p><strong>Valid Until:</strong> {{end_date}}</p><p>Thank you for choosing Hostxolo!</p></body></html>', 'Subscription confirmed. Plan: {{plan_name}}, Amount: {{amount}}, Valid until: {{end_date}}', '["name","plan_name","amount","end_date"]'),
('ticket_reply', 'Support Ticket Reply', 'New Reply to Your Support Ticket', '<html><body><h2>Support Update</h2><p>Hi {{name}},</p><p>There is a new reply to your support ticket #{{ticket_number}}.</p><p><a href="{{ticket_url}}" style="padding:10px 20px;background:#007bff;color:white;text-decoration:none;">View Reply</a></p></body></html>', 'New reply to ticket #{{ticket_number}}. View at: {{ticket_url}}', '["name","ticket_number","ticket_url"]');

-- Default SEO Settings for Pages
INSERT INTO seo_settings (page_type, page_id, meta_title, meta_description, robots_meta) VALUES
('home', 0, 'Hostxolo - Business Growth Plan | All Services Under One Roof', 'Hostxolo provides business consulting, digital marketing, software solutions and more. Join 250+ satisfied customers. One plan for all your business needs.', 'index,follow'),
('services', 0, 'Our Services - Hostxolo Business Consulting', 'Explore our comprehensive business consulting services including development, digital marketing, automation, and cloud solutions.', 'index,follow'),
('software', 0, 'Software Products - Hostxolo', 'Discover our powerful software solutions: WhatsApp CRM, Cloud Telecalling, ERP, Marketing Automation, Website Builder, and AI Tools.', 'index,follow'),
('plans', 0, 'Business Growth Plans - Hostxolo Subscription Plans', 'Choose the perfect business growth plan for your needs. Monthly and yearly subscription options available.', 'index,follow'),
('blog', 0, 'Blog - Business Growth Tips & Insights | Hostxolo', 'Read our latest articles on business growth, digital marketing strategies, technology insights, and success stories.', 'index,follow'),
('contact', 0, 'Contact Us - Hostxolo Business Consulting', 'Contact Hostxolo for business consulting services. We are here to help your business grow digitally.', 'index,follow');

-- ============================================
-- INDEXES FOR PERFORMANCE
-- ============================================

CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_users_status ON users(status);
CREATE INDEX idx_blog_posts_status ON blog_posts(status);
CREATE INDEX idx_blog_posts_published ON blog_posts(published_at);
CREATE INDEX idx_services_status ON services(status);
CREATE INDEX idx_software_status ON software_products(status);
CREATE INDEX idx_transactions_user ON transactions(user_id);
CREATE INDEX idx_subscriptions_user ON user_subscriptions(user_id);
CREATE INDEX idx_tickets_user ON support_tickets(user_id);
CREATE INDEX idx_tickets_status ON support_tickets(status);

-- ============================================
-- UPGRADE TABLES (Added for PRD Requirements)
-- ============================================

-- Email Queue System
CREATE TABLE email_queue (
    id INT PRIMARY KEY AUTO_INCREMENT,
    to_email VARCHAR(255) NOT NULL,
    to_name VARCHAR(100) DEFAULT NULL,
    subject VARCHAR(255) NOT NULL,
    body_html LONGTEXT,
    body_text LONGTEXT,
    template_key VARCHAR(50) DEFAULT NULL,
    template_data TEXT,
    status ENUM('pending', 'sending', 'sent', 'failed', 'cancelled') DEFAULT 'pending',
    priority INT DEFAULT 5,
    attempts INT DEFAULT 0,
    max_attempts INT DEFAULT 3,
    error_message TEXT,
    scheduled_at TIMESTAMP NULL,
    sent_at TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Coupons / Promo Codes
CREATE TABLE coupons (
    id INT PRIMARY KEY AUTO_INCREMENT,
    code VARCHAR(50) NOT NULL UNIQUE,
    description VARCHAR(255) DEFAULT NULL,
    discount_type ENUM('percentage', 'fixed') NOT NULL,
    discount_value DECIMAL(10,2) NOT NULL,
    min_amount DECIMAL(10,2) DEFAULT 0.00,
    max_discount DECIMAL(10,2) DEFAULT NULL,
    max_uses INT DEFAULT NULL,
    used_count INT DEFAULT 0,
    user_limit INT DEFAULT NULL,
    applies_to ENUM('all', 'plans', 'services', 'software') DEFAULT 'all',
    specific_ids TEXT DEFAULT NULL,
    starts_at DATE DEFAULT NULL,
    expires_at DATE DEFAULT NULL,
    is_active TINYINT(1) DEFAULT 1,
    created_by INT DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- FAQ Management
CREATE TABLE faqs (
    id INT PRIMARY KEY AUTO_INCREMENT,
    question TEXT NOT NULL,
    answer TEXT NOT NULL,
    category VARCHAR(100) DEFAULT 'general',
    sort_order INT DEFAULT 0,
    is_active TINYINT(1) DEFAULT 1,
    views INT DEFAULT 0,
    helpful_count INT DEFAULT 0,
    not_helpful_count INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Newsletter Subscribers
CREATE TABLE newsletter_subscribers (
    id INT PRIMARY KEY AUTO_INCREMENT,
    email VARCHAR(255) NOT NULL UNIQUE,
    name VARCHAR(100) DEFAULT NULL,
    phone VARCHAR(20) DEFAULT NULL,
    source VARCHAR(50) DEFAULT 'website',
    status ENUM('active', 'unsubscribed', 'bounced', 'complained') DEFAULT 'active',
    verified_at TIMESTAMP NULL,
    unsubscribed_at TIMESTAMP NULL,
    unsubscribe_reason TEXT,
    ip_address VARCHAR(45) DEFAULT NULL,
    user_agent TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Login Attempts (Rate Limiting & Security)
CREATE TABLE login_attempts (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(100) NOT NULL,
    ip_address VARCHAR(45) NOT NULL,
    user_agent TEXT,
    attempt_type ENUM('user', 'admin') DEFAULT 'user',
    status ENUM('success', 'failed', 'blocked') DEFAULT 'failed',
    failure_reason VARCHAR(100) DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Password Resets
CREATE TABLE password_resets (
    id INT PRIMARY KEY AUTO_INCREMENT,
    email VARCHAR(100) NOT NULL,
    token VARCHAR(255) NOT NULL,
    user_type ENUM('user', 'admin') DEFAULT 'user',
    ip_address VARCHAR(45) DEFAULT NULL,
    expires_at TIMESTAMP NOT NULL,
    used_at TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- User Sessions (for session management)
CREATE TABLE user_sessions (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    user_type ENUM('user', 'admin') DEFAULT 'user',
    session_token VARCHAR(255) NOT NULL,
    ip_address VARCHAR(45) DEFAULT NULL,
    user_agent TEXT,
    last_activity TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    expires_at TIMESTAMP NOT NULL,
    is_valid TINYINT(1) DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- User Notifications
CREATE TABLE user_notifications (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    type VARCHAR(50) NOT NULL,
    title VARCHAR(255) NOT NULL,
    message TEXT NOT NULL,
    action_url VARCHAR(255) DEFAULT NULL,
    is_read TINYINT(1) DEFAULT 0,
    read_at TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- Referral System
CREATE TABLE referrals (
    id INT PRIMARY KEY AUTO_INCREMENT,
    referrer_id INT NOT NULL,
    referred_id INT DEFAULT NULL,
    referral_code VARCHAR(50) NOT NULL UNIQUE,
    status ENUM('pending', 'registered', 'subscribed', 'rewarded', 'expired') DEFAULT 'pending',
    reward_amount DECIMAL(10,2) DEFAULT 0.00,
    reward_given_at TIMESTAMP NULL,
    expires_at TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (referrer_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (referred_id) REFERENCES users(id) ON DELETE SET NULL
);

-- Payment Webhook Logs
CREATE TABLE payment_webhook_logs (
    id INT PRIMARY KEY AUTO_INCREMENT,
    gateway VARCHAR(50) NOT NULL,
    event_type VARCHAR(100) DEFAULT NULL,
    payload TEXT,
    headers TEXT,
    ip_address VARCHAR(45) DEFAULT NULL,
    status ENUM('received', 'processed', 'failed', 'ignored') DEFAULT 'received',
    response TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Export History
CREATE TABLE export_history (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    user_type ENUM('user', 'admin') DEFAULT 'admin',
    export_type VARCHAR(50) NOT NULL,
    file_name VARCHAR(255) NOT NULL,
    file_path VARCHAR(500) NOT NULL,
    record_count INT DEFAULT 0,
    filters TEXT,
    status ENUM('processing', 'completed', 'failed') DEFAULT 'processing',
    downloaded_at TIMESTAMP NULL,
    expires_at TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- System Settings (for maintenance mode, etc.)
CREATE TABLE system_settings (
    id INT PRIMARY KEY AUTO_INCREMENT,
    setting_key VARCHAR(100) NOT NULL UNIQUE,
    setting_value TEXT,
    data_type ENUM('string', 'integer', 'boolean', 'json', 'array') DEFAULT 'string',
    description TEXT,
    is_editable TINYINT(1) DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Insert default system settings
INSERT INTO system_settings (setting_key, setting_value, data_type, description) VALUES
('maintenance_mode', '0', 'boolean', 'Enable/disable maintenance mode'),
('maintenance_message', 'We are currently performing scheduled maintenance. Please check back soon.', 'string', 'Message to display during maintenance'),
('registration_enabled', '1', 'boolean', 'Allow new user registrations'),
('social_login_enabled', '1', 'boolean', 'Enable social login (Google, Facebook)'),
('max_login_attempts', '5', 'integer', 'Maximum failed login attempts before lockout'),
('lockout_duration_minutes', '30', 'integer', 'Account lockout duration in minutes'),
('session_timeout_minutes', '120', 'integer', 'Session timeout duration in minutes'),
('password_min_length', '8', 'integer', 'Minimum password length'),
('require_strong_password', '1', 'boolean', 'Require strong passwords (uppercase, lowercase, number, special char)'),
('email_verification_required', '0', 'boolean', 'Require email verification for new accounts'),
('otp_enabled', '0', 'boolean', 'Enable OTP verification'),
('otp_method', 'email', 'string', 'OTP delivery method: email, sms, both'),
('otp_expiry_minutes', '10', 'integer', 'OTP expiry time in minutes'),
('default_currency', 'INR', 'string', 'Default currency code'),
('currency_symbol', '₹', 'string', 'Currency symbol'),
('timezone', 'Asia/Kolkata', 'string', 'Default timezone'),
('date_format', 'd M Y', 'string', 'Date format for display'),
('time_format', 'h:i A', 'string', 'Time format for display'),
('items_per_page', '10', 'integer', 'Default pagination limit'),
('max_upload_size_mb', '5', 'integer', 'Maximum file upload size in MB'),
('allowed_image_types', '["jpg","jpeg","png","gif","webp"]', 'json', 'Allowed image file extensions'),
('allowed_doc_types', '["pdf","doc","docx","xls","xlsx"]', 'json', 'Allowed document file extensions'),
('google_analytics_id', '', 'string', 'Google Analytics Tracking ID'),
('google_tag_manager_id', '', 'string', 'Google Tag Manager ID'),
('facebook_pixel_id', '', 'string', 'Facebook Pixel ID'),
('recaptcha_site_key', '', 'string', 'Google reCAPTCHA Site Key'),
('recaptcha_secret_key', '', 'string', 'Google reCAPTCHA Secret Key'),
('whatsapp_number', '+91-6388758325', 'string', 'WhatsApp contact number'),
('whatsapp_message', 'Hi, I would like to know more about Hostxolo services.', 'string', 'Default WhatsApp message'),
('live_chat_enabled', '0', 'boolean', 'Enable live chat widget'),
('announcement_bar_enabled', '0', 'boolean', 'Enable announcement bar'),
('announcement_bar_message', '', 'string', 'Announcement bar message'),
('announcement_bar_link', '', 'string', 'Announcement bar link URL'),
('popup_banner_enabled', '0', 'boolean', 'Enable popup banner/modal'),
('popup_banner_content', '', 'string', 'Popup banner HTML content'),
('newsletter_popup_enabled', '1', 'boolean', 'Enable newsletter signup popup'),
('newsletter_popup_delay_seconds', '10', 'integer', 'Delay before showing newsletter popup'),
('seo_default_title', 'Hostxolo - Business Growth Plan | All Services Under One Roof', 'string', 'Default SEO title'),
('seo_default_description', 'Hostxolo provides business consulting, digital marketing, software solutions and more. Join 250+ satisfied customers.', 'string', 'Default SEO description'),
('seo_default_keywords', 'business consulting, digital marketing, software solutions, india, delhi, noida', 'string', 'Default SEO keywords'),
('smtp_from_email', 'noreply@hostxolo.com', 'string', 'SMTP from email address'),
('smtp_from_name', 'Hostxolo', 'string', 'SMTP from name'),
('company_name', 'Hostxolo Business Solutions Pvt. Ltd.', 'string', 'Company legal name'),
('company_gst_number', '', 'string', 'Company GST number'),
('support_phone', '+91-6388758325', 'string', 'Support phone number'),
('support_email', 'support@hostxolo.com', 'string', 'Support email address'),
('business_hours', 'Mon-Sat: 9:00 AM - 6:00 PM IST', 'string', 'Business hours display text');

-- Add indexes for new tables
CREATE INDEX idx_email_queue_status ON email_queue(status);
CREATE INDEX idx_email_queue_scheduled ON email_queue(scheduled_at);
CREATE INDEX idx_coupons_code ON coupons(code);
CREATE INDEX idx_coupons_active ON coupons(is_active, expires_at);
CREATE INDEX idx_faqs_category ON faqs(category);
CREATE INDEX idx_login_attempts_ip ON login_attempts(ip_address);
CREATE INDEX idx_login_attempts_time ON login_attempts(created_at);
CREATE INDEX idx_password_resets_token ON password_resets(token);
CREATE INDEX idx_user_sessions_token ON user_sessions(session_token);
CREATE INDEX idx_notifications_user ON user_notifications(user_id, is_read);
CREATE INDEX idx_newsletter_email ON newsletter_subscribers(email);
CREATE INDEX idx_referrals_code ON referrals(referral_code);
