CREATE DATABASE IF NOT EXISTS love_u_essentials
CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;

USE love_u_essentials;

SET FOREIGN_KEY_CHECKS = 0;

DROP TABLE IF EXISTS admin_activity_logs;
DROP TABLE IF EXISTS backups;
DROP TABLE IF EXISTS email_logs;
DROP TABLE IF EXISTS email_templates;
DROP TABLE IF EXISTS abandoned_cart_items;
DROP TABLE IF EXISTS abandoned_carts;
DROP TABLE IF EXISTS newsletter_subscribers;
DROP TABLE IF EXISTS site_settings;
DROP TABLE IF EXISTS banners;
DROP TABLE IF EXISTS pages;
DROP TABLE IF EXISTS faqs;
DROP TABLE IF EXISTS referral_rewards;
DROP TABLE IF EXISTS referrals;
DROP TABLE IF EXISTS loyalty_transactions;
DROP TABLE IF EXISTS customer_loyalty;
DROP TABLE IF EXISTS gift_orders;
DROP TABLE IF EXISTS review_media;
DROP TABLE IF EXISTS reviews;
DROP TABLE IF EXISTS payments;
DROP TABLE IF EXISTS order_items;
DROP TABLE IF EXISTS orders;
DROP TABLE IF EXISTS coupon_usages;
DROP TABLE IF EXISTS coupons;
DROP TABLE IF EXISTS wishlist_items;
DROP TABLE IF EXISTS cart_items;
DROP TABLE IF EXISTS carts;
DROP TABLE IF EXISTS product_tag_map;
DROP TABLE IF EXISTS product_tags;
DROP TABLE IF EXISTS product_variations;
DROP TABLE IF EXISTS product_images;
DROP TABLE IF EXISTS products;
DROP TABLE IF EXISTS categories;
DROP TABLE IF EXISTS shipping_rates;
DROP TABLE IF EXISTS shipping_zones;
DROP TABLE IF EXISTS currencies;
DROP TABLE IF EXISTS customer_addresses;
DROP TABLE IF EXISTS customers;
DROP TABLE IF EXISTS admin_users;
DROP TABLE IF EXISTS roles;

SET FOREIGN_KEY_CHECKS = 1;

CREATE TABLE roles (
    id INT AUTO_INCREMENT PRIMARY KEY,
    role_name VARCHAR(100) NOT NULL UNIQUE,
    description TEXT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE admin_users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    role_id INT NULL,
    full_name VARCHAR(150) NOT NULL,
    email VARCHAR(150) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    phone VARCHAR(50) NULL,
    avatar VARCHAR(255) NULL,
    two_factor_enabled TINYINT(1) DEFAULT 0,
    two_factor_secret VARCHAR(255) NULL,
    status ENUM('active','inactive','blocked') DEFAULT 'active',
    last_login_at DATETIME NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (role_id) REFERENCES roles(id) ON DELETE SET NULL
);

CREATE TABLE customers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(100) NOT NULL,
    last_name VARCHAR(100) NULL,
    email VARCHAR(150) NOT NULL UNIQUE,
    phone VARCHAR(50) NULL,
    password_hash VARCHAR(255) NULL,
    google_id VARCHAR(255) NULL,
    avatar VARCHAR(255) NULL,
    referral_code VARCHAR(50) UNIQUE NULL,
    referred_by_customer_id INT NULL,
    two_factor_enabled TINYINT(1) DEFAULT 0,
    two_factor_secret VARCHAR(255) NULL,
    email_verified_at DATETIME NULL,
    status ENUM('active','inactive','blocked') DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (referred_by_customer_id) REFERENCES customers(id) ON DELETE SET NULL
);

CREATE TABLE customer_addresses (
    id INT AUTO_INCREMENT PRIMARY KEY,
    customer_id INT NOT NULL,
    address_type ENUM('billing','shipping') DEFAULT 'shipping',
    full_name VARCHAR(150) NOT NULL,
    phone VARCHAR(50) NULL,
    address_line_1 VARCHAR(255) NOT NULL,
    address_line_2 VARCHAR(255) NULL,
    city VARCHAR(100) NOT NULL,
    state_region VARCHAR(100) NULL,
    postal_code VARCHAR(50) NULL,
    country VARCHAR(100) NOT NULL,
    is_default TINYINT(1) DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE
);

CREATE TABLE currencies (
    id INT AUTO_INCREMENT PRIMARY KEY,
    currency_code VARCHAR(10) NOT NULL UNIQUE,
    currency_name VARCHAR(100) NOT NULL,
    symbol VARCHAR(10) NOT NULL,
    exchange_rate DECIMAL(12,6) DEFAULT 1.000000,
    is_default TINYINT(1) DEFAULT 0,
    status ENUM('active','inactive') DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE shipping_zones (
    id INT AUTO_INCREMENT PRIMARY KEY,
    zone_name VARCHAR(150) NOT NULL,
    country VARCHAR(100) NOT NULL,
    region VARCHAR(100) NULL,
    message VARCHAR(255) NULL,
    status ENUM('active','inactive') DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE shipping_rates (
    id INT AUTO_INCREMENT PRIMARY KEY,
    zone_id INT NOT NULL,
    rate_name VARCHAR(150) NOT NULL,
    min_order_amount DECIMAL(10,2) DEFAULT 0.00,
    max_order_amount DECIMAL(10,2) NULL,
    shipping_fee DECIMAL(10,2) DEFAULT 0.00,
    free_shipping_threshold DECIMAL(10,2) NULL,
    status ENUM('active','inactive') DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (zone_id) REFERENCES shipping_zones(id) ON DELETE CASCADE
);

CREATE TABLE categories (
    id INT AUTO_INCREMENT PRIMARY KEY,
    parent_id INT NULL,
    name VARCHAR(150) NOT NULL,
    slug VARCHAR(180) NOT NULL UNIQUE,
    description TEXT NULL,
    image VARCHAR(255) NULL,
    seo_title VARCHAR(180) NULL,
    seo_description VARCHAR(255) NULL,
    sort_order INT DEFAULT 0,
    status ENUM('active','inactive') DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (parent_id) REFERENCES categories(id) ON DELETE SET NULL
);

CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    category_id INT NULL,
    product_name VARCHAR(200) NOT NULL,
    slug VARCHAR(220) NOT NULL UNIQUE,
    short_description TEXT NULL,
    description LONGTEXT NULL,
    sku VARCHAR(100) NULL UNIQUE,
    regular_price DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    sale_price DECIMAL(10,2) NULL,
    currency_code VARCHAR(10) DEFAULT 'GBP',
    stock_quantity INT DEFAULT 0,
    stock_status ENUM('in_stock','only_few_left','out_of_stock','back_in_stock') DEFAULT 'in_stock',
    badge VARCHAR(80) NULL,
    is_featured TINYINT(1) DEFAULT 0,
    is_best_seller TINYINT(1) DEFAULT 0,
    is_new_arrival TINYINT(1) DEFAULT 0,
    weight DECIMAL(10,2) NULL,
    scent_family VARCHAR(150) NULL,
    ingredients TEXT NULL,
    how_to_use TEXT NULL,
    shipping_return_info TEXT NULL,
    seo_title VARCHAR(180) NULL,
    seo_description VARCHAR(255) NULL,
    status ENUM('active','inactive','draft') DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE SET NULL
);

CREATE TABLE product_images (
    id INT AUTO_INCREMENT PRIMARY KEY,
    product_id INT NOT NULL,
    image_path VARCHAR(255) NOT NULL,
    alt_text VARCHAR(180) NULL,
    sort_order INT DEFAULT 0,
    is_primary TINYINT(1) DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE
);

CREATE TABLE product_variations (
    id INT AUTO_INCREMENT PRIMARY KEY,
    product_id INT NOT NULL,
    variation_name VARCHAR(120) NOT NULL,
    variation_value VARCHAR(150) NOT NULL,
    sku VARCHAR(100) NULL,
    price_adjustment DECIMAL(10,2) DEFAULT 0.00,
    stock_quantity INT DEFAULT 0,
    status ENUM('active','inactive') DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE
);

CREATE TABLE product_tags (
    id INT AUTO_INCREMENT PRIMARY KEY,
    tag_name VARCHAR(100) NOT NULL UNIQUE,
    slug VARCHAR(120) NOT NULL UNIQUE
);

CREATE TABLE product_tag_map (
    product_id INT NOT NULL,
    tag_id INT NOT NULL,
    PRIMARY KEY (product_id, tag_id),
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE,
    FOREIGN KEY (tag_id) REFERENCES product_tags(id) ON DELETE CASCADE
);

CREATE TABLE carts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    customer_id INT NULL,
    session_id VARCHAR(255) NULL,
    status ENUM('active','converted','abandoned') DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE
);

CREATE TABLE cart_items (
    id INT AUTO_INCREMENT PRIMARY KEY,
    cart_id INT NOT NULL,
    product_id INT NOT NULL,
    variation_id INT NULL,
    quantity INT NOT NULL DEFAULT 1,
    unit_price DECIMAL(10,2) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (cart_id) REFERENCES carts(id) ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE,
    FOREIGN KEY (variation_id) REFERENCES product_variations(id) ON DELETE SET NULL
);

CREATE TABLE wishlist_items (
    id INT AUTO_INCREMENT PRIMARY KEY,
    customer_id INT NOT NULL,
    product_id INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY unique_customer_product (customer_id, product_id),
    FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE
);

CREATE TABLE coupons (
    id INT AUTO_INCREMENT PRIMARY KEY,
    coupon_code VARCHAR(80) NOT NULL UNIQUE,
    coupon_type ENUM('percentage','fixed') NOT NULL,
    coupon_value DECIMAL(10,2) NOT NULL,
    min_order_amount DECIMAL(10,2) DEFAULT 0.00,
    max_discount_amount DECIMAL(10,2) NULL,
    usage_limit INT NULL,
    used_count INT DEFAULT 0,
    starts_at DATETIME NULL,
    expires_at DATETIME NULL,
    status ENUM('active','inactive','expired') DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE coupon_usages (
    id INT AUTO_INCREMENT PRIMARY KEY,
    coupon_id INT NOT NULL,
    customer_id INT NULL,
    order_id INT NULL,
    used_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (coupon_id) REFERENCES coupons(id) ON DELETE CASCADE,
    FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE SET NULL
);

CREATE TABLE orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    order_number VARCHAR(80) NOT NULL UNIQUE,
    customer_id INT NULL,
    customer_email VARCHAR(150) NOT NULL,
    customer_phone VARCHAR(50) NULL,
    billing_address TEXT NOT NULL,
    shipping_address TEXT NOT NULL,
    subtotal DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    discount_amount DECIMAL(10,2) DEFAULT 0.00,
    shipping_fee DECIMAL(10,2) DEFAULT 0.00,
    tax_amount DECIMAL(10,2) DEFAULT 0.00,
    total_amount DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    currency_code VARCHAR(10) DEFAULT 'GBP',
    coupon_code VARCHAR(80) NULL,
    order_notes TEXT NULL,
    is_gift TINYINT(1) DEFAULT 0,
    payment_status ENUM('pending','paid','failed','refunded','cancelled') DEFAULT 'pending',
    order_status ENUM('pending','processing','shipped','completed','cancelled','refunded') DEFAULT 'pending',
    shipping_status ENUM('not_shipped','packed','shipped','delivered') DEFAULT 'not_shipped',
    tracking_number VARCHAR(150) NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE SET NULL
);

CREATE TABLE order_items (
    id INT AUTO_INCREMENT PRIMARY KEY,
    order_id INT NOT NULL,
    product_id INT NULL,
    variation_id INT NULL,
    product_name VARCHAR(200) NOT NULL,
    sku VARCHAR(100) NULL,
    quantity INT NOT NULL,
    unit_price DECIMAL(10,2) NOT NULL,
    total_price DECIMAL(10,2) NOT NULL,
    FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE SET NULL,
    FOREIGN KEY (variation_id) REFERENCES product_variations(id) ON DELETE SET NULL
);

CREATE TABLE payments (
    id INT AUTO_INCREMENT PRIMARY KEY,
    order_id INT NOT NULL,
    payment_gateway ENUM('paypal','stripe','google_pay','manual') NOT NULL,
    transaction_id VARCHAR(255) NULL,
    payment_reference VARCHAR(255) NULL,
    amount DECIMAL(10,2) NOT NULL,
    currency_code VARCHAR(10) DEFAULT 'GBP',
    status ENUM('pending','success','failed','refunded') DEFAULT 'pending',
    gateway_response LONGTEXT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE
);

CREATE TABLE gift_orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    order_id INT NOT NULL,
    recipient_name VARCHAR(150) NOT NULL,
    recipient_email VARCHAR(150) NULL,
    recipient_phone VARCHAR(50) NULL,
    gift_message TEXT NULL,
    hide_price TINYINT(1) DEFAULT 1,
    gift_wrap TINYINT(1) DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE
);

CREATE TABLE reviews (
    id INT AUTO_INCREMENT PRIMARY KEY,
    product_id INT NOT NULL,
    customer_id INT NULL,
    order_id INT NULL,
    rating TINYINT NOT NULL,
    review_title VARCHAR(180) NULL,
    review_text TEXT NULL,
    verified_purchase TINYINT(1) DEFAULT 0,
    status ENUM('pending','approved','rejected') DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE,
    FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE SET NULL,
    FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE SET NULL
);

CREATE TABLE review_media (
    id INT AUTO_INCREMENT PRIMARY KEY,
    review_id INT NOT NULL,
    media_type ENUM('image','video') NOT NULL,
    media_path VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (review_id) REFERENCES reviews(id) ON DELETE CASCADE
);

CREATE TABLE customer_loyalty (
    id INT AUTO_INCREMENT PRIMARY KEY,
    customer_id INT NOT NULL UNIQUE,
    points_balance INT DEFAULT 0,
    lifetime_points INT DEFAULT 0,
    updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE
);

CREATE TABLE loyalty_transactions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    customer_id INT NOT NULL,
    order_id INT NULL,
    transaction_type ENUM('earn','redeem','adjustment','expired') NOT NULL,
    points INT NOT NULL,
    description VARCHAR(255) NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE,
    FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE SET NULL
);

CREATE TABLE referrals (
    id INT AUTO_INCREMENT PRIMARY KEY,
    referrer_customer_id INT NOT NULL,
    referred_customer_id INT NULL,
    referral_code VARCHAR(50) NOT NULL,
    reward_status ENUM('pending','approved','rejected','paid') DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (referrer_customer_id) REFERENCES customers(id) ON DELETE CASCADE,
    FOREIGN KEY (referred_customer_id) REFERENCES customers(id) ON DELETE SET NULL
);

CREATE TABLE referral_rewards (
    id INT AUTO_INCREMENT PRIMARY KEY,
    referral_id INT NOT NULL,
    reward_type ENUM('coupon','points','fixed_amount') NOT NULL,
    reward_value DECIMAL(10,2) NOT NULL,
    issued_at DATETIME NULL,
    status ENUM('pending','issued','used','cancelled') DEFAULT 'pending',
    FOREIGN KEY (referral_id) REFERENCES referrals(id) ON DELETE CASCADE
);

CREATE TABLE abandoned_carts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    customer_id INT NULL,
    session_id VARCHAR(255) NULL,
    email VARCHAR(150) NULL,
    cart_total DECIMAL(10,2) DEFAULT 0.00,
    recovery_token VARCHAR(255) NULL,
    email_sent_count INT DEFAULT 0,
    last_email_sent_at DATETIME NULL,
    status ENUM('pending','recovered','expired') DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE SET NULL
);

CREATE TABLE abandoned_cart_items (
    id INT AUTO_INCREMENT PRIMARY KEY,
    abandoned_cart_id INT NOT NULL,
    product_id INT NULL,
    product_name VARCHAR(200) NOT NULL,
    quantity INT NOT NULL,
    unit_price DECIMAL(10,2) NOT NULL,
    FOREIGN KEY (abandoned_cart_id) REFERENCES abandoned_carts(id) ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE SET NULL
);

CREATE TABLE newsletter_subscribers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(150) NOT NULL UNIQUE,
    first_name VARCHAR(100) NULL,
    source VARCHAR(100) NULL,
    discount_code VARCHAR(80) NULL,
    status ENUM('subscribed','unsubscribed') DEFAULT 'subscribed',
    subscribed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE email_templates (
    id INT AUTO_INCREMENT PRIMARY KEY,
    template_key VARCHAR(100) NOT NULL UNIQUE,
    subject VARCHAR(200) NOT NULL,
    body LONGTEXT NOT NULL,
    status ENUM('active','inactive') DEFAULT 'active',
    updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE email_logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    customer_id INT NULL,
    order_id INT NULL,
    email_to VARCHAR(150) NOT NULL,
    subject VARCHAR(200) NOT NULL,
    status ENUM('pending','sent','failed') DEFAULT 'pending',
    error_message TEXT NULL,
    sent_at DATETIME NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE SET NULL,
    FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE SET NULL
);

CREATE TABLE banners (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(180) NOT NULL,
    subtitle VARCHAR(255) NULL,
    image_path VARCHAR(255) NOT NULL,
    button_text VARCHAR(100) NULL,
    button_link VARCHAR(255) NULL,
    placement ENUM('home_hero','home_promo','category','popup') DEFAULT 'home_hero',
    sort_order INT DEFAULT 0,
    status ENUM('active','inactive') DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE faqs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    question VARCHAR(255) NOT NULL,
    answer TEXT NOT NULL,
    sort_order INT DEFAULT 0,
    status ENUM('active','inactive') DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE pages (
    id INT AUTO_INCREMENT PRIMARY KEY,
    page_key VARCHAR(100) NOT NULL UNIQUE,
    title VARCHAR(180) NOT NULL,
    slug VARCHAR(180) NOT NULL UNIQUE,
    content LONGTEXT NULL,
    seo_title VARCHAR(180) NULL,
    seo_description VARCHAR(255) NULL,
    status ENUM('active','inactive') DEFAULT 'active',
    updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE site_settings (
    id INT AUTO_INCREMENT PRIMARY KEY,
    setting_key VARCHAR(150) NOT NULL UNIQUE,
    setting_value LONGTEXT NULL,
    setting_group VARCHAR(100) DEFAULT 'general',
    updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE backups (
    id INT AUTO_INCREMENT PRIMARY KEY,
    backup_name VARCHAR(180) NOT NULL,
    backup_path VARCHAR(255) NOT NULL,
    backup_type ENUM('database','files','full') DEFAULT 'database',
    created_by INT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (created_by) REFERENCES admin_users(id) ON DELETE SET NULL
);

CREATE TABLE admin_activity_logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    admin_id INT NULL,
    action VARCHAR(180) NOT NULL,
    description TEXT NULL,
    ip_address VARCHAR(100) NULL,
    user_agent TEXT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (admin_id) REFERENCES admin_users(id) ON DELETE SET NULL
);

CREATE INDEX idx_products_slug ON products(slug);
CREATE INDEX idx_products_status ON products(status);
CREATE INDEX idx_products_category ON products(category_id);
CREATE INDEX idx_orders_number ON orders(order_number);
CREATE INDEX idx_orders_customer ON orders(customer_id);
CREATE INDEX idx_orders_status ON orders(order_status);
CREATE INDEX idx_reviews_product ON reviews(product_id);
CREATE INDEX idx_carts_customer ON carts(customer_id);

INSERT INTO roles (role_name, description) VALUES
('Super Admin', 'Full system access'),
('Admin', 'Manage website, products, orders and customers'),
('Order Manager', 'Manage customer orders'),
('Product Manager', 'Manage products and categories'),
('Marketing Manager', 'Manage coupons, reviews, banners and marketing features');

INSERT INTO currencies (currency_code, currency_name, symbol, exchange_rate, is_default, status) VALUES
('GBP', 'British Pound', '£', 1.000000, 1, 'active'),
('USD', 'US Dollar', '$', 1.270000, 0, 'active'),
('XCD', 'Eastern Caribbean Dollar', 'EC$', 3.430000, 0, 'active'),
('LKR', 'Sri Lankan Rupee', 'Rs.', 390.000000, 0, 'inactive');

INSERT INTO shipping_zones (zone_name, country, region, message, status) VALUES
('United Kingdom', 'United Kingdom', NULL, 'Fast and secure delivery across the UK.', 'active'),
('Grenada', 'Grenada', NULL, 'Delivery options available for Grenada orders.', 'active'),
('International', 'International', NULL, 'International shipping rates may vary by location.', 'active');

INSERT INTO shipping_rates (zone_id, rate_name, min_order_amount, max_order_amount, shipping_fee, free_shipping_threshold, status) VALUES
(1, 'UK Standard Shipping', 0.00, NULL, 4.99, 50.00, 'active'),
(2, 'Grenada Standard Shipping', 0.00, NULL, 8.99, 75.00, 'active'),
(3, 'International Shipping', 0.00, NULL, 15.00, 150.00, 'active');

INSERT INTO site_settings (setting_key, setting_value, setting_group) VALUES
('site_name', 'LOVE-U Essentials', 'general'),
('site_email', 'info@loveuessentials.com', 'general'),
('site_phone', '', 'general'),
('default_currency', 'GBP', 'commerce'),
('free_shipping_message', 'Free shipping on selected orders.', 'commerce'),
('popup_offer_enabled', '1', 'marketing'),
('popup_offer_text', 'Sign up and unlock an exclusive offer.', 'marketing'),
('seo_default_title', 'LOVE-U Essentials | Luxury Essentials Online', 'seo'),
('seo_default_description', 'Shop premium LOVE-U Essentials products online with secure checkout and smooth delivery.', 'seo');

INSERT INTO email_templates (template_key, subject, body, status) VALUES
('order_confirmation', 'Your LOVE-U Essentials order confirmation', 'Thank you for your order. We are preparing it now.', 'active'),
('order_status_update', 'Your LOVE-U Essentials order has been updated', 'Your order status has been updated.', 'active'),
('abandoned_cart', 'You left something you love behind', 'Complete your order before your cart expires.', 'active'),
('welcome_customer', 'Welcome to LOVE-U Essentials', 'Thank you for creating your account.', 'active');

INSERT INTO faqs (question, answer, sort_order, status) VALUES
('How long does delivery take?', 'Delivery time depends on your selected shipping region and shipping method.', 1, 'active'),
('Can I send an order as a gift?', 'Yes, you can select the gift option during checkout where available.', 2, 'active'),
('Do you offer refunds or returns?', 'Refund and return eligibility will follow the official return policy.', 3, 'active');