-- Legend Electronics & Toy Shop eCommerce Database
-- MySQL 8.0+ / MariaDB 10.5+ | Import with phpMyAdmin
-- Create the database first, then import this file.

CREATE DATABASE IF NOT EXISTS ilmvision_easytoybd
  CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE ilmvision_easytoybd;
SET FOREIGN_KEY_CHECKS = 0;

DROP TABLE IF EXISTS order_status_history, product_reviews, newsletter_subscribers,
 contact_messages, blog_comments, blog_posts, blog_categories, website_settings,
 banners, sliders, coupon_usages, coupons, payments, order_items, orders,
 wishlist_items, wishlists, cart_items, carts, product_images, product_variants,
 products, brands, categories, customer_addresses, customers, admins;

CREATE TABLE admins (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 name VARCHAR(150) NOT NULL,
 email VARCHAR(190) NOT NULL UNIQUE,
 password VARCHAR(255) NOT NULL,
 phone VARCHAR(30) NULL,
 avatar VARCHAR(255) NULL,
 role VARCHAR(40) NOT NULL DEFAULT 'admin',
 status TINYINT NOT NULL DEFAULT 1,
 last_login_at TIMESTAMP NULL,
 created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
 updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE customers (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 name VARCHAR(150) NOT NULL,
 email VARCHAR(190) NOT NULL UNIQUE,
 phone VARCHAR(30) NULL,
 password VARCHAR(255) NOT NULL,
 avatar VARCHAR(255) NULL,
 email_verified_at TIMESTAMP NULL,
 status TINYINT NOT NULL DEFAULT 1,
 remember_token VARCHAR(100) NULL,
 created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
 updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE customer_addresses (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 customer_id BIGINT UNSIGNED NOT NULL,
 address_type VARCHAR(30) NOT NULL DEFAULT 'shipping',
 full_name VARCHAR(150) NOT NULL,
 phone VARCHAR(30) NOT NULL,
 email VARCHAR(190) NULL,
 address_line1 VARCHAR(255) NOT NULL,
 address_line2 VARCHAR(255) NULL,
 city VARCHAR(100) NOT NULL,
 state VARCHAR(100) NULL,
 postal_code VARCHAR(20) NULL,
 country VARCHAR(100) NOT NULL DEFAULT 'Bangladesh',
 is_default TINYINT NOT NULL DEFAULT 0,
 created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
 updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 CONSTRAINT fk_address_customer FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE,
 INDEX idx_address_customer (customer_id)
) ENGINE=InnoDB;

CREATE TABLE categories (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 parent_id BIGINT UNSIGNED NULL,
 name VARCHAR(150) NOT NULL,
 slug VARCHAR(180) NOT NULL UNIQUE,
 description TEXT NULL,
 image VARCHAR(255) NULL,
 icon VARCHAR(100) NULL,
 sort_order INT NOT NULL DEFAULT 0,
 status TINYINT NOT NULL DEFAULT 1,
 created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
 updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 CONSTRAINT fk_category_parent FOREIGN KEY (parent_id) REFERENCES categories(id) ON DELETE SET NULL,
 INDEX idx_category_status_sort (status, sort_order)
) ENGINE=InnoDB;

CREATE TABLE brands (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 name VARCHAR(150) NOT NULL,
 slug VARCHAR(180) NOT NULL UNIQUE,
 logo VARCHAR(255) NULL,
 description TEXT NULL,
 status TINYINT NOT NULL DEFAULT 1,
 created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
 updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE products (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 category_id BIGINT UNSIGNED NULL,
 brand_id BIGINT UNSIGNED NULL,
 name VARCHAR(220) NOT NULL,
 slug VARCHAR(240) NOT NULL UNIQUE,
 sku VARCHAR(100) NULL UNIQUE,
 barcode VARCHAR(100) NULL,
 short_description TEXT NULL,
 description LONGTEXT NULL,
 regular_price DECIMAL(12,2) NOT NULL DEFAULT 0.00,
 sale_price DECIMAL(12,2) NULL,
 cost_price DECIMAL(12,2) NULL,
 stock_quantity INT NOT NULL DEFAULT 0,
 low_stock_threshold INT NOT NULL DEFAULT 5,
 stock_status VARCHAR(30) NOT NULL DEFAULT 'in_stock',
 weight DECIMAL(10,2) NULL,
 dimensions VARCHAR(100) NULL,
 featured TINYINT NOT NULL DEFAULT 0,
 new_arrival TINYINT NOT NULL DEFAULT 0,
 best_seller TINYINT NOT NULL DEFAULT 0,
 status TINYINT NOT NULL DEFAULT 1,
 meta_title VARCHAR(255) NULL,
 meta_description TEXT NULL,
 deleted_at TIMESTAMP NULL,
 created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
 updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 CONSTRAINT fk_product_category FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE SET NULL,
 CONSTRAINT fk_product_brand FOREIGN KEY (brand_id) REFERENCES brands(id) ON DELETE SET NULL,
 INDEX idx_product_category (category_id), INDEX idx_product_brand (brand_id),
 INDEX idx_product_status_featured (status, featured), INDEX idx_product_name (name)
) ENGINE=InnoDB;

CREATE TABLE product_images (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 product_id BIGINT UNSIGNED NOT NULL,
 image_path VARCHAR(255) NOT NULL,
 is_primary TINYINT NOT NULL DEFAULT 0,
 sort_order INT NOT NULL DEFAULT 0,
 created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
 CONSTRAINT fk_product_image_product FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE,
 INDEX idx_product_image_sort (product_id, sort_order)
) ENGINE=InnoDB;

CREATE TABLE product_variants (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 product_id BIGINT UNSIGNED NOT NULL,
 variant_name VARCHAR(120) NOT NULL,
 variant_value VARCHAR(180) NOT NULL,
 sku VARCHAR(100) NULL UNIQUE,
 price DECIMAL(12,2) NULL,
 stock_quantity INT NOT NULL DEFAULT 0,
 image_path VARCHAR(255) NULL,
 status TINYINT NOT NULL DEFAULT 1,
 created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
 updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 CONSTRAINT fk_variant_product FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE carts (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 customer_id BIGINT UNSIGNED NULL,
 session_key VARCHAR(120) NULL,
 created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
 updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 CONSTRAINT fk_cart_customer FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE,
 INDEX idx_cart_session (session_key), INDEX idx_cart_customer (customer_id)
) ENGINE=InnoDB;

CREATE TABLE cart_items (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 cart_id BIGINT UNSIGNED NOT NULL,
 product_id BIGINT UNSIGNED NOT NULL,
 variant_id BIGINT UNSIGNED NULL,
 quantity INT UNSIGNED NOT NULL DEFAULT 1,
 unit_price DECIMAL(12,2) NOT NULL,
 created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
 updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 CONSTRAINT fk_cart_item_cart FOREIGN KEY (cart_id) REFERENCES carts(id) ON DELETE CASCADE,
 CONSTRAINT fk_cart_item_product FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE,
 CONSTRAINT fk_cart_item_variant FOREIGN KEY (variant_id) REFERENCES product_variants(id) ON DELETE SET NULL,
 INDEX idx_cart_item_cart (cart_id)
) ENGINE=InnoDB;

CREATE TABLE wishlists (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 customer_id BIGINT UNSIGNED NOT NULL,
 created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
 CONSTRAINT fk_wishlist_customer FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE,
 UNIQUE KEY uq_wishlist_customer (customer_id)
) ENGINE=InnoDB;

CREATE TABLE wishlist_items (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 wishlist_id BIGINT UNSIGNED NOT NULL,
 product_id BIGINT UNSIGNED NOT NULL,
 created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
 CONSTRAINT fk_wishlist_item_list FOREIGN KEY (wishlist_id) REFERENCES wishlists(id) ON DELETE CASCADE,
 CONSTRAINT fk_wishlist_item_product FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE,
 UNIQUE KEY uq_wishlist_product (wishlist_id, product_id)
) ENGINE=InnoDB;

CREATE TABLE coupons (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 code VARCHAR(80) NOT NULL UNIQUE,
 discount_type VARCHAR(20) NOT NULL DEFAULT 'fixed',
 discount_value DECIMAL(12,2) NOT NULL DEFAULT 0,
 minimum_order DECIMAL(12,2) NOT NULL DEFAULT 0,
 maximum_discount DECIMAL(12,2) NULL,
 usage_limit INT NULL,
 used_count INT NOT NULL DEFAULT 0,
 starts_at DATETIME NULL,
 expires_at DATETIME NULL,
 status TINYINT NOT NULL DEFAULT 1,
 created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
 updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE orders (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 order_number VARCHAR(50) NOT NULL UNIQUE,
 customer_id BIGINT UNSIGNED NULL,
 customer_name VARCHAR(150) NOT NULL,
 customer_email VARCHAR(190) NULL,
 customer_phone VARCHAR(30) NOT NULL,
 shipping_address JSON NULL,
 billing_address JSON NULL,
 subtotal DECIMAL(12,2) NOT NULL DEFAULT 0,
 discount_amount DECIMAL(12,2) NOT NULL DEFAULT 0,
 shipping_amount DECIMAL(12,2) NOT NULL DEFAULT 0,
 tax_amount DECIMAL(12,2) NOT NULL DEFAULT 0,
 total_amount DECIMAL(12,2) NOT NULL DEFAULT 0,
 coupon_code VARCHAR(80) NULL,
 payment_method VARCHAR(60) NULL,
 payment_status VARCHAR(30) NOT NULL DEFAULT 'pending',
 order_status VARCHAR(30) NOT NULL DEFAULT 'pending',
 customer_note TEXT NULL,
 admin_note TEXT NULL,
 ordered_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
 created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
 updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 CONSTRAINT fk_order_customer FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE SET NULL,
 INDEX idx_order_customer (customer_id), INDEX idx_order_status (order_status),
 INDEX idx_order_payment_status (payment_status), INDEX idx_order_date (ordered_at)
) ENGINE=InnoDB;

CREATE TABLE order_items (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 order_id BIGINT UNSIGNED NOT NULL,
 product_id BIGINT UNSIGNED NULL,
 product_name VARCHAR(220) NOT NULL,
 sku VARCHAR(100) NULL,
 variant_description VARCHAR(255) NULL,
 product_image VARCHAR(255) NULL,
 quantity INT UNSIGNED NOT NULL,
 unit_price DECIMAL(12,2) NOT NULL,
 line_total DECIMAL(12,2) NOT NULL,
 created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
 CONSTRAINT fk_order_item_order FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE,
 CONSTRAINT fk_order_item_product FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE SET NULL,
 INDEX idx_order_item_order (order_id)
) ENGINE=InnoDB;

CREATE TABLE payments (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 order_id BIGINT UNSIGNED NOT NULL,
 transaction_id VARCHAR(150) NULL,
 payment_method VARCHAR(60) NOT NULL,
 amount DECIMAL(12,2) NOT NULL,
 currency VARCHAR(10) NOT NULL DEFAULT 'BDT',
 status VARCHAR(30) NOT NULL DEFAULT 'pending',
 gateway_response JSON NULL,
 paid_at TIMESTAMP NULL,
 created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
 updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 CONSTRAINT fk_payment_order FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE,
 INDEX idx_payment_transaction (transaction_id), INDEX idx_payment_status (status)
) ENGINE=InnoDB;

CREATE TABLE coupon_usages (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 coupon_id BIGINT UNSIGNED NOT NULL,
 order_id BIGINT UNSIGNED NOT NULL,
 customer_id BIGINT UNSIGNED NULL,
 discount_amount DECIMAL(12,2) NOT NULL,
 created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
 CONSTRAINT fk_coupon_usage_coupon FOREIGN KEY (coupon_id) REFERENCES coupons(id) ON DELETE CASCADE,
 CONSTRAINT fk_coupon_usage_order FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE,
 CONSTRAINT fk_coupon_usage_customer FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE order_status_history (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 order_id BIGINT UNSIGNED NOT NULL,
 status VARCHAR(30) NOT NULL,
 note TEXT NULL,
 changed_by_admin_id BIGINT UNSIGNED NULL,
 created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
 CONSTRAINT fk_order_history_order FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE,
 CONSTRAINT fk_order_history_admin FOREIGN KEY (changed_by_admin_id) REFERENCES admins(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE product_reviews (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 product_id BIGINT UNSIGNED NOT NULL,
 customer_id BIGINT UNSIGNED NULL,
 customer_name VARCHAR(150) NULL,
 rating TINYINT UNSIGNED NOT NULL,
 title VARCHAR(180) NULL,
 review TEXT NULL,
 status TINYINT NOT NULL DEFAULT 0,
 created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
 updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 CONSTRAINT fk_review_product FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE,
 CONSTRAINT fk_review_customer FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE SET NULL,
 INDEX idx_review_product_status (product_id, status)
) ENGINE=InnoDB;

CREATE TABLE sliders (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 title VARCHAR(220) NULL,
 subtitle VARCHAR(255) NULL,
 description TEXT NULL,
 image VARCHAR(255) NOT NULL,
 button_text VARCHAR(80) NULL,
 button_url VARCHAR(255) NULL,
 sort_order INT NOT NULL DEFAULT 0,
 status TINYINT NOT NULL DEFAULT 1,
 created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
 updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE banners (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 title VARCHAR(220) NULL,
 image VARCHAR(255) NOT NULL,
 target_url VARCHAR(255) NULL,
 position VARCHAR(80) NOT NULL DEFAULT 'home',
 sort_order INT NOT NULL DEFAULT 0,
 starts_at DATETIME NULL,
 ends_at DATETIME NULL,
 status TINYINT NOT NULL DEFAULT 1,
 created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
 updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 INDEX idx_banner_position_status (position, status)
) ENGINE=InnoDB;

CREATE TABLE website_settings (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 setting_key VARCHAR(150) NOT NULL UNIQUE,
 setting_value LONGTEXT NULL,
 setting_type VARCHAR(30) NOT NULL DEFAULT 'text',
 updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE blog_categories (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 name VARCHAR(150) NOT NULL,
 slug VARCHAR(180) NOT NULL UNIQUE,
 status TINYINT NOT NULL DEFAULT 1,
 created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
 updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE blog_posts (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 blog_category_id BIGINT UNSIGNED NULL,
 admin_id BIGINT UNSIGNED NULL,
 title VARCHAR(220) NOT NULL,
 slug VARCHAR(240) NOT NULL UNIQUE,
 excerpt TEXT NULL,
 content LONGTEXT NULL,
 featured_image VARCHAR(255) NULL,
 status VARCHAR(30) NOT NULL DEFAULT 'draft',
 published_at DATETIME NULL,
 meta_title VARCHAR(255) NULL,
 meta_description TEXT NULL,
 created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
 updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 CONSTRAINT fk_blog_post_category FOREIGN KEY (blog_category_id) REFERENCES blog_categories(id) ON DELETE SET NULL,
 CONSTRAINT fk_blog_post_admin FOREIGN KEY (admin_id) REFERENCES admins(id) ON DELETE SET NULL,
 INDEX idx_blog_status_published (status, published_at)
) ENGINE=InnoDB;

CREATE TABLE blog_comments (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 blog_post_id BIGINT UNSIGNED NOT NULL,
 customer_id BIGINT UNSIGNED NULL,
 name VARCHAR(150) NOT NULL,
 email VARCHAR(190) NULL,
 comment TEXT NOT NULL,
 status TINYINT NOT NULL DEFAULT 0,
 created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
 CONSTRAINT fk_blog_comment_post FOREIGN KEY (blog_post_id) REFERENCES blog_posts(id) ON DELETE CASCADE,
 CONSTRAINT fk_blog_comment_customer FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE contact_messages (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 name VARCHAR(150) NOT NULL,
 email VARCHAR(190) NOT NULL,
 phone VARCHAR(30) NULL,
 subject VARCHAR(220) NULL,
 message TEXT NOT NULL,
 is_read TINYINT NOT NULL DEFAULT 0,
 replied_at TIMESTAMP NULL,
 created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
 INDEX idx_contact_read (is_read)
) ENGINE=InnoDB;

CREATE TABLE newsletter_subscribers (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 email VARCHAR(190) NOT NULL UNIQUE,
 status TINYINT NOT NULL DEFAULT 1,
 subscribed_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
 unsubscribed_at TIMESTAMP NULL
) ENGINE=InnoDB;

-- Starter categories based on the supplied template navigation.
INSERT INTO categories (name, slug, sort_order, status) VALUES
 ('Headphones', 'headphones', 1, 1),
 ('Mobiles & Tablets', 'mobiles-tablets', 2, 1),
 ('Photo & Camera', 'photo-camera', 3, 1),
 ('Accessories', 'accessories', 4, 1),
 ('Computers', 'computers', 5, 1),
 ('TV & Audio', 'tv-audio', 6, 1),
 ('Storage', 'storage', 7, 1);

INSERT INTO website_settings (setting_key, setting_value, setting_type) VALUES
 ('site_name', 'Legend Electronics & Toy Shop', 'text'),
 ('currency', 'BDT', 'text'),
 ('currency_symbol', '৳', 'text'),
 ('shipping_notice', 'Free Shipping on Orders ৳5000+', 'text'),
 ('store_email', '', 'text'),
 ('store_phone', '', 'text'),
 ('store_address', '', 'textarea'),
 ('store_logo', '', 'image'),
 ('footer_description', '', 'textarea');

SET FOREIGN_KEY_CHECKS = 1;

-- SECURITY: No default admin/customer account is inserted.
-- Create an admin using Laravel Hash::make() or a secure admin registration process.