-- SiteCraft 1.0 — complete MySQL 8 schema (generated from the migrations; do not edit by hand)
-- Generated: 2026-11-10T06:25Z
-- Import:  mysql -u root -p sitecraft < database/database.sql   then: php spark db:seed DatabaseSeeder
-- Includes the migrations table, so later releases can upgrade with: php spark migrate
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

/*M!999999\- enable the sandbox mode */ 
CREATE TABLE `activity_logs` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `tenant_id` bigint(20) unsigned DEFAULT NULL,
  `user_id` bigint(20) unsigned DEFAULT NULL,
  `action` varchar(80) NOT NULL,
  `subject_type` varchar(60) DEFAULT NULL,
  `subject_id` bigint(20) unsigned DEFAULT NULL,
  `description` varchar(255) DEFAULT NULL,
  `properties` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`properties`)),
  `ip_address` varchar(45) DEFAULT NULL,
  `user_agent` varchar(255) DEFAULT NULL,
  `created_at` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_al_tenant_time` (`tenant_id`,`created_at`),
  KEY `idx_al_user` (`user_id`),
  KEY `idx_al_subject` (`subject_type`,`subject_id`),
  CONSTRAINT `fk_al_tenant` FOREIGN KEY (`tenant_id`) REFERENCES `tenants` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_al_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `analytics_daily` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `tenant_id` bigint(20) unsigned NOT NULL,
  `website_id` bigint(20) unsigned NOT NULL,
  `stat_date` date NOT NULL,
  `path_hash` char(40) NOT NULL COMMENT 'sha1(path); keeps the unique key short',
  `path` varchar(500) NOT NULL,
  `views` int(10) unsigned NOT NULL DEFAULT 0,
  `unique_visitors` int(10) unsigned NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_ad_day_path` (`website_id`,`stat_date`,`path_hash`),
  KEY `idx_ad_tenant_date` (`tenant_id`,`stat_date`),
  CONSTRAINT `fk_ad_tenant` FOREIGN KEY (`tenant_id`) REFERENCES `tenants` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_ad_website` FOREIGN KEY (`website_id`) REFERENCES `websites` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `analytics_sources` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `tenant_id` bigint(20) unsigned NOT NULL,
  `website_id` bigint(20) unsigned NOT NULL,
  `stat_date` date NOT NULL,
  `dimension` varchar(12) NOT NULL COMMENT 'referrer|device|browser',
  `value` varchar(100) NOT NULL,
  `views` int(10) unsigned NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_as` (`website_id`,`stat_date`,`dimension`,`value`),
  KEY `idx_as_tenant` (`tenant_id`,`stat_date`),
  CONSTRAINT `fk_as_tenant` FOREIGN KEY (`tenant_id`) REFERENCES `tenants` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_as_website` FOREIGN KEY (`website_id`) REFERENCES `websites` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `analytics_visitors` (
  `website_id` bigint(20) unsigned NOT NULL,
  `stat_date` date NOT NULL,
  `path_hash` char(40) NOT NULL,
  `visitor_hash` char(32) NOT NULL COMMENT 'HMAC(ip|user agent|site, daily key) — not reversible, changes every day',
  PRIMARY KEY (`website_id`,`stat_date`,`path_hash`,`visitor_hash`),
  KEY `idx_av_date` (`stat_date`)
) ENGINE=InnoDB DEFAULT CHARSET=ascii COLLATE=ascii_bin;

CREATE TABLE `blog_categories` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `tenant_id` bigint(20) unsigned NOT NULL,
  `website_id` bigint(20) unsigned NOT NULL,
  `name` varchar(100) NOT NULL,
  `slug` varchar(120) NOT NULL,
  `description` varchar(500) DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_bc_slug` (`website_id`,`slug`),
  KEY `idx_bc_tenant` (`tenant_id`),
  CONSTRAINT `fk_bc_tenant` FOREIGN KEY (`tenant_id`) REFERENCES `tenants` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_bc_website` FOREIGN KEY (`website_id`) REFERENCES `websites` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `blog_tag_map` (
  `blog_id` bigint(20) unsigned NOT NULL,
  `tag_id` bigint(20) unsigned NOT NULL,
  PRIMARY KEY (`blog_id`,`tag_id`),
  KEY `idx_btm_tag` (`tag_id`),
  CONSTRAINT `fk_btm_blog` FOREIGN KEY (`blog_id`) REFERENCES `blogs` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_btm_tag` FOREIGN KEY (`tag_id`) REFERENCES `blog_tags` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `blog_tags` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `tenant_id` bigint(20) unsigned NOT NULL,
  `website_id` bigint(20) unsigned NOT NULL,
  `name` varchar(60) NOT NULL,
  `slug` varchar(70) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_bt_slug` (`website_id`,`slug`),
  KEY `idx_bt_tenant` (`tenant_id`),
  CONSTRAINT `fk_bt_tenant` FOREIGN KEY (`tenant_id`) REFERENCES `tenants` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_bt_website` FOREIGN KEY (`website_id`) REFERENCES `websites` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `blogs` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `tenant_id` bigint(20) unsigned NOT NULL,
  `website_id` bigint(20) unsigned NOT NULL,
  `category_id` bigint(20) unsigned DEFAULT NULL,
  `author_user_id` bigint(20) unsigned DEFAULT NULL,
  `author_name` varchar(120) DEFAULT NULL COMMENT 'Display name override',
  `title` varchar(200) NOT NULL,
  `slug` varchar(220) NOT NULL,
  `excerpt` varchar(500) DEFAULT NULL,
  `content` longtext DEFAULT NULL,
  `featured_image` varchar(500) DEFAULT NULL,
  `status` varchar(20) NOT NULL DEFAULT 'draft' COMMENT 'draft|published|scheduled',
  `published_at` datetime DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  `deleted_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_blogs_slug` (`website_id`,`slug`),
  KEY `idx_blogs_listing` (`website_id`,`status`,`published_at`),
  KEY `idx_blogs_tenant` (`tenant_id`),
  KEY `fk_blogs_category` (`category_id`),
  KEY `fk_blogs_author` (`author_user_id`),
  CONSTRAINT `fk_blogs_author` FOREIGN KEY (`author_user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_blogs_category` FOREIGN KEY (`category_id`) REFERENCES `blog_categories` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_blogs_tenant` FOREIGN KEY (`tenant_id`) REFERENCES `tenants` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_blogs_website` FOREIGN KEY (`website_id`) REFERENCES `websites` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `cart_items` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `tenant_id` bigint(20) unsigned NOT NULL,
  `cart_id` bigint(20) unsigned NOT NULL,
  `product_id` bigint(20) unsigned NOT NULL,
  `variant_id` bigint(20) unsigned DEFAULT NULL,
  `quantity` int(10) unsigned NOT NULL DEFAULT 1,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_ci_line` (`cart_id`,`product_id`,`variant_id`),
  KEY `idx_ci_cart` (`cart_id`),
  KEY `idx_ci_tenant` (`tenant_id`),
  KEY `fk_ci_product` (`product_id`),
  KEY `fk_ci_variant` (`variant_id`),
  CONSTRAINT `fk_ci_cart` FOREIGN KEY (`cart_id`) REFERENCES `carts` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_ci_product` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_ci_tenant` FOREIGN KEY (`tenant_id`) REFERENCES `tenants` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_ci_variant` FOREIGN KEY (`variant_id`) REFERENCES `product_variants` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `carts` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `tenant_id` bigint(20) unsigned NOT NULL,
  `website_id` bigint(20) unsigned NOT NULL,
  `customer_id` bigint(20) unsigned DEFAULT NULL,
  `token` char(64) NOT NULL COMMENT 'Opaque cookie token',
  `coupon_code` varchar(40) DEFAULT NULL,
  `expires_at` datetime NOT NULL,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_carts_token` (`token`),
  KEY `idx_carts_customer` (`customer_id`),
  KEY `idx_carts_tenant` (`tenant_id`),
  KEY `fk_carts_website` (`website_id`),
  CONSTRAINT `fk_carts_customer` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_carts_tenant` FOREIGN KEY (`tenant_id`) REFERENCES `tenants` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_carts_website` FOREIGN KEY (`website_id`) REFERENCES `websites` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `coupon_usage` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `tenant_id` bigint(20) unsigned NOT NULL,
  `coupon_id` bigint(20) unsigned NOT NULL,
  `order_id` bigint(20) unsigned NOT NULL,
  `customer_id` bigint(20) unsigned DEFAULT NULL,
  `customer_email` varchar(191) NOT NULL,
  `discount_amount` decimal(12,2) NOT NULL,
  `created_at` datetime NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_cu_order` (`order_id`),
  KEY `idx_cu_coupon_email` (`coupon_id`,`customer_email`),
  KEY `idx_cu_tenant` (`tenant_id`),
  KEY `fk_cu_customer` (`customer_id`),
  CONSTRAINT `fk_cu_coupon` FOREIGN KEY (`coupon_id`) REFERENCES `coupons` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_cu_customer` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_cu_order` FOREIGN KEY (`order_id`) REFERENCES `orders` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_cu_tenant` FOREIGN KEY (`tenant_id`) REFERENCES `tenants` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `coupons` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `tenant_id` bigint(20) unsigned NOT NULL,
  `website_id` bigint(20) unsigned NOT NULL,
  `code` varchar(40) NOT NULL,
  `type` varchar(20) NOT NULL COMMENT 'percent|fixed|free_shipping',
  `value` decimal(12,2) NOT NULL DEFAULT 0.00,
  `min_order_amount` decimal(12,2) DEFAULT NULL,
  `max_discount` decimal(12,2) DEFAULT NULL,
  `usage_limit` int(10) unsigned DEFAULT NULL,
  `usage_limit_per_customer` int(10) unsigned DEFAULT NULL,
  `used_count` int(10) unsigned NOT NULL DEFAULT 0,
  `starts_at` datetime DEFAULT NULL,
  `expires_at` datetime DEFAULT NULL,
  `status` varchar(20) NOT NULL DEFAULT 'active' COMMENT 'active|disabled',
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_coupons_code` (`website_id`,`code`),
  KEY `idx_coupons_tenant` (`tenant_id`),
  CONSTRAINT `fk_coupons_tenant` FOREIGN KEY (`tenant_id`) REFERENCES `tenants` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_coupons_website` FOREIGN KEY (`website_id`) REFERENCES `websites` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `customer_addresses` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `tenant_id` bigint(20) unsigned NOT NULL,
  `customer_id` bigint(20) unsigned NOT NULL,
  `label` varchar(40) DEFAULT NULL,
  `full_name` varchar(160) NOT NULL,
  `phone` varchar(20) NOT NULL,
  `line1` varchar(255) NOT NULL,
  `line2` varchar(255) DEFAULT NULL,
  `city` varchar(100) NOT NULL,
  `state` varchar(100) NOT NULL,
  `pincode` varchar(10) NOT NULL,
  `country` char(2) NOT NULL DEFAULT 'IN',
  `is_default_billing` tinyint(1) NOT NULL DEFAULT 0,
  `is_default_shipping` tinyint(1) NOT NULL DEFAULT 0,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_ca_customer` (`customer_id`),
  KEY `idx_ca_tenant` (`tenant_id`),
  CONSTRAINT `fk_ca_customer` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_ca_tenant` FOREIGN KEY (`tenant_id`) REFERENCES `tenants` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `customer_groups` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `tenant_id` bigint(20) unsigned NOT NULL,
  `website_id` bigint(20) unsigned NOT NULL,
  `name` varchar(80) NOT NULL,
  `discount_percent` decimal(5,2) NOT NULL DEFAULT 0.00,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_cg_tenant` (`tenant_id`),
  KEY `fk_cg_website` (`website_id`),
  CONSTRAINT `fk_cg_tenant` FOREIGN KEY (`tenant_id`) REFERENCES `tenants` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_cg_website` FOREIGN KEY (`website_id`) REFERENCES `websites` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `customer_tokens` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `tenant_id` bigint(20) unsigned NOT NULL,
  `customer_id` bigint(20) unsigned NOT NULL,
  `type` varchar(20) NOT NULL COMMENT 'password_reset',
  `selector` char(24) NOT NULL,
  `token_hash` char(64) NOT NULL,
  `expires_at` datetime NOT NULL,
  `used_at` datetime DEFAULT NULL,
  `created_at` datetime NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_ct_selector` (`selector`),
  KEY `idx_ct_customer` (`customer_id`,`type`),
  KEY `fk_ct_tenant` (`tenant_id`),
  CONSTRAINT `fk_ct_customer` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_ct_tenant` FOREIGN KEY (`tenant_id`) REFERENCES `tenants` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `customers` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `tenant_id` bigint(20) unsigned NOT NULL,
  `website_id` bigint(20) unsigned NOT NULL,
  `group_id` bigint(20) unsigned DEFAULT NULL,
  `first_name` varchar(80) NOT NULL,
  `last_name` varchar(80) DEFAULT NULL,
  `email` varchar(191) NOT NULL,
  `phone` varchar(20) DEFAULT NULL,
  `password_hash` varchar(255) DEFAULT NULL COMMENT 'NULL for guest checkout customers',
  `status` varchar(20) NOT NULL DEFAULT 'active' COMMENT 'active|blocked',
  `accepts_marketing` tinyint(1) NOT NULL DEFAULT 0,
  `email_verified_at` datetime DEFAULT NULL,
  `last_login_at` datetime DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  `deleted_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_customers_email` (`website_id`,`email`),
  KEY `idx_customers_tenant` (`tenant_id`),
  KEY `fk_customers_group` (`group_id`),
  CONSTRAINT `fk_customers_group` FOREIGN KEY (`group_id`) REFERENCES `customer_groups` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_customers_tenant` FOREIGN KEY (`tenant_id`) REFERENCES `tenants` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_customers_website` FOREIGN KEY (`website_id`) REFERENCES `websites` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `email_templates` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `slug` varchar(80) NOT NULL,
  `name` varchar(150) NOT NULL,
  `subject` varchar(200) NOT NULL,
  `body_html` mediumtext NOT NULL,
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_et_slug` (`slug`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `form_submissions` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `tenant_id` bigint(20) unsigned NOT NULL,
  `website_id` bigint(20) unsigned NOT NULL,
  `form` varchar(30) NOT NULL COMMENT 'contact',
  `page_path` varchar(255) DEFAULT NULL,
  `name` varchar(120) DEFAULT NULL,
  `email` varchar(191) DEFAULT NULL,
  `phone` varchar(25) DEFAULT NULL,
  `message` text DEFAULT NULL,
  `ip_address` varchar(45) DEFAULT NULL,
  `user_agent` varchar(255) DEFAULT NULL,
  `read_at` datetime DEFAULT NULL,
  `created_at` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_fs_site` (`website_id`,`created_at`),
  KEY `idx_fs_tenant_read` (`tenant_id`,`read_at`),
  CONSTRAINT `fk_fs_tenant` FOREIGN KEY (`tenant_id`) REFERENCES `tenants` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_fs_website` FOREIGN KEY (`website_id`) REFERENCES `websites` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `inventory` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `tenant_id` bigint(20) unsigned NOT NULL,
  `product_id` bigint(20) unsigned NOT NULL,
  `variant_id` bigint(20) unsigned DEFAULT NULL,
  `stock_key` varchar(40) GENERATED ALWAYS AS (concat(`product_id`,':',ifnull(`variant_id`,0))) STORED COMMENT 'Enforces one row per product/variant',
  `quantity` int(11) NOT NULL DEFAULT 0,
  `reserved` int(11) NOT NULL DEFAULT 0 COMMENT 'Held by unpaid orders',
  `low_stock_threshold` int(10) unsigned NOT NULL DEFAULT 5,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_inv_stock_key` (`stock_key`),
  KEY `idx_inv_tenant` (`tenant_id`),
  KEY `idx_inv_variant` (`variant_id`),
  KEY `fk_inv_product` (`product_id`),
  CONSTRAINT `fk_inv_product` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_inv_tenant` FOREIGN KEY (`tenant_id`) REFERENCES `tenants` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_inv_variant` FOREIGN KEY (`variant_id`) REFERENCES `product_variants` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `inventory_movements` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `tenant_id` bigint(20) unsigned NOT NULL,
  `inventory_id` bigint(20) unsigned NOT NULL,
  `change_qty` int(11) NOT NULL,
  `reason` varchar(20) NOT NULL COMMENT 'order|cancel|refund|restock|adjustment',
  `reference_type` varchar(30) DEFAULT NULL,
  `reference_id` bigint(20) unsigned DEFAULT NULL,
  `note` varchar(255) DEFAULT NULL,
  `created_by` bigint(20) unsigned DEFAULT NULL,
  `created_at` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_im_inventory` (`inventory_id`,`created_at`),
  KEY `idx_im_tenant` (`tenant_id`),
  CONSTRAINT `fk_im_inventory` FOREIGN KEY (`inventory_id`) REFERENCES `inventory` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_im_tenant` FOREIGN KEY (`tenant_id`) REFERENCES `tenants` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `login_attempts` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `email` varchar(191) NOT NULL,
  `ip_address` varchar(45) NOT NULL,
  `user_agent` varchar(255) DEFAULT NULL,
  `success` tinyint(1) NOT NULL DEFAULT 0,
  `created_at` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_la_email_time` (`email`,`created_at`),
  KEY `idx_la_ip_time` (`ip_address`,`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `media_files` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `tenant_id` bigint(20) unsigned NOT NULL,
  `folder_id` bigint(20) unsigned DEFAULT NULL,
  `original_name` varchar(255) NOT NULL,
  `file_name` varchar(255) NOT NULL COMMENT 'Random stored name',
  `disk_path` varchar(500) NOT NULL COMMENT 'Relative to tenant media root',
  `webp_path` varchar(500) DEFAULT NULL,
  `mime_type` varchar(100) NOT NULL,
  `extension` varchar(10) NOT NULL,
  `file_size` bigint(20) unsigned NOT NULL,
  `width` int(10) unsigned DEFAULT NULL,
  `height` int(10) unsigned DEFAULT NULL,
  `alt_text` varchar(255) DEFAULT NULL,
  `uploaded_by` bigint(20) unsigned DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  `deleted_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_mf_tenant_folder` (`tenant_id`,`folder_id`),
  KEY `idx_mf_tenant_name` (`tenant_id`,`original_name`(100)),
  KEY `fk_mf_folder` (`folder_id`),
  KEY `fk_mf_user` (`uploaded_by`),
  KEY `idx_mf_tenant_created` (`tenant_id`,`id`),
  CONSTRAINT `fk_mf_folder` FOREIGN KEY (`folder_id`) REFERENCES `media_folders` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_mf_tenant` FOREIGN KEY (`tenant_id`) REFERENCES `tenants` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_mf_user` FOREIGN KEY (`uploaded_by`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `media_folders` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `tenant_id` bigint(20) unsigned NOT NULL,
  `parent_id` bigint(20) unsigned DEFAULT NULL,
  `name` varchar(100) NOT NULL,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_mfo_tenant_parent` (`tenant_id`,`parent_id`),
  KEY `fk_mfo_parent` (`parent_id`),
  CONSTRAINT `fk_mfo_parent` FOREIGN KEY (`parent_id`) REFERENCES `media_folders` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_mfo_tenant` FOREIGN KEY (`tenant_id`) REFERENCES `tenants` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `migrations` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `version` varchar(255) NOT NULL,
  `class` varchar(255) NOT NULL,
  `group` varchar(255) NOT NULL,
  `namespace` varchar(255) NOT NULL,
  `time` int(11) NOT NULL,
  `batch` int(11) unsigned NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `newsletter_subscribers` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `tenant_id` bigint(20) unsigned NOT NULL,
  `website_id` bigint(20) unsigned NOT NULL,
  `email` varchar(191) NOT NULL,
  `status` varchar(20) NOT NULL DEFAULT 'subscribed' COMMENT 'subscribed|unsubscribed',
  `source_path` varchar(255) DEFAULT NULL,
  `ip_address` varchar(45) DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_ns_email` (`website_id`,`email`),
  KEY `idx_ns_tenant` (`tenant_id`),
  CONSTRAINT `fk_ns_tenant` FOREIGN KEY (`tenant_id`) REFERENCES `tenants` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_ns_website` FOREIGN KEY (`website_id`) REFERENCES `websites` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `notifications` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `tenant_id` bigint(20) unsigned DEFAULT NULL,
  `user_id` bigint(20) unsigned NOT NULL,
  `type` varchar(60) NOT NULL,
  `title` varchar(150) NOT NULL,
  `message` varchar(500) DEFAULT NULL,
  `link` varchar(255) DEFAULT NULL,
  `read_at` datetime DEFAULT NULL,
  `created_at` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_notif_user_read` (`user_id`,`read_at`),
  KEY `idx_notif_tenant` (`tenant_id`),
  CONSTRAINT `fk_notif_tenant` FOREIGN KEY (`tenant_id`) REFERENCES `tenants` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_notif_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `order_items` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `tenant_id` bigint(20) unsigned NOT NULL,
  `order_id` bigint(20) unsigned NOT NULL,
  `product_id` bigint(20) unsigned DEFAULT NULL,
  `variant_id` bigint(20) unsigned DEFAULT NULL,
  `name` varchar(200) NOT NULL COMMENT 'Snapshot at purchase time',
  `variant_title` varchar(150) DEFAULT NULL,
  `sku` varchar(64) DEFAULT NULL,
  `quantity` int(10) unsigned NOT NULL,
  `unit_price` decimal(12,2) NOT NULL,
  `discount_amount` decimal(12,2) NOT NULL DEFAULT 0.00,
  `tax_rate` decimal(5,2) NOT NULL DEFAULT 0.00,
  `tax_amount` decimal(12,2) NOT NULL DEFAULT 0.00,
  `line_total` decimal(12,2) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_oi_order` (`order_id`),
  KEY `idx_oi_tenant` (`tenant_id`),
  KEY `idx_oi_product` (`product_id`),
  KEY `fk_oi_variant` (`variant_id`),
  CONSTRAINT `fk_oi_order` FOREIGN KEY (`order_id`) REFERENCES `orders` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_oi_product` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_oi_tenant` FOREIGN KEY (`tenant_id`) REFERENCES `tenants` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_oi_variant` FOREIGN KEY (`variant_id`) REFERENCES `product_variants` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `order_status_history` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `tenant_id` bigint(20) unsigned NOT NULL,
  `order_id` bigint(20) unsigned NOT NULL,
  `from_status` varchar(20) DEFAULT NULL,
  `to_status` varchar(20) NOT NULL,
  `note` varchar(500) DEFAULT NULL,
  `customer_notified` tinyint(1) NOT NULL DEFAULT 0,
  `created_by` bigint(20) unsigned DEFAULT NULL,
  `created_at` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_osh_order` (`order_id`),
  KEY `idx_osh_tenant` (`tenant_id`),
  CONSTRAINT `fk_osh_order` FOREIGN KEY (`order_id`) REFERENCES `orders` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_osh_tenant` FOREIGN KEY (`tenant_id`) REFERENCES `tenants` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `orders` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `tenant_id` bigint(20) unsigned NOT NULL,
  `website_id` bigint(20) unsigned NOT NULL,
  `customer_id` bigint(20) unsigned DEFAULT NULL,
  `order_number` varchar(32) NOT NULL,
  `access_hash` char(64) DEFAULT NULL COMMENT 'SHA-256 of the order page secret given to the buyer',
  `status` varchar(20) NOT NULL DEFAULT 'pending' COMMENT 'pending|processing|shipped|delivered|cancelled|refunded',
  `payment_status` varchar(20) NOT NULL DEFAULT 'pending' COMMENT 'pending|paid|failed|refunded|partially_refunded',
  `payment_method` varchar(20) DEFAULT NULL COMMENT 'razorpay|cod',
  `currency` char(3) NOT NULL DEFAULT 'INR',
  `subtotal` decimal(12,2) NOT NULL,
  `discount_total` decimal(12,2) NOT NULL DEFAULT 0.00,
  `shipping_total` decimal(12,2) NOT NULL DEFAULT 0.00,
  `cod_fee` decimal(12,2) NOT NULL DEFAULT 0.00,
  `tax_total` decimal(12,2) NOT NULL DEFAULT 0.00,
  `grand_total` decimal(12,2) NOT NULL,
  `coupon_id` bigint(20) unsigned DEFAULT NULL,
  `coupon_code` varchar(40) DEFAULT NULL,
  `customer_email` varchar(191) NOT NULL,
  `customer_phone` varchar(20) DEFAULT NULL,
  `billing_address` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL CHECK (json_valid(`billing_address`)),
  `shipping_address` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL CHECK (json_valid(`shipping_address`)),
  `shipping_rate_id` bigint(20) unsigned DEFAULT NULL,
  `shipping_method` varchar(100) DEFAULT NULL,
  `customer_note` varchar(1000) DEFAULT NULL,
  `merchant_note` varchar(1000) DEFAULT NULL,
  `tracking_carrier` varchar(60) DEFAULT NULL,
  `tracking_number` varchar(80) DEFAULT NULL,
  `tracking_url` varchar(500) DEFAULT NULL,
  `invoice_number` varchar(32) DEFAULT NULL,
  `placed_at` datetime NOT NULL,
  `paid_at` datetime DEFAULT NULL,
  `payment_expires_at` datetime DEFAULT NULL COMMENT 'Online payment must complete before this; stock is held until then',
  `stock_state` varchar(10) NOT NULL DEFAULT 'committed' COMMENT 'reserved|committed|released',
  `shipped_at` datetime DEFAULT NULL,
  `delivered_at` datetime DEFAULT NULL,
  `cancelled_at` datetime DEFAULT NULL,
  `ip_address` varchar(45) DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_orders_number` (`website_id`,`order_number`),
  UNIQUE KEY `uq_orders_invoice` (`website_id`,`invoice_number`),
  KEY `idx_orders_tenant_status` (`tenant_id`,`status`,`placed_at`),
  KEY `idx_orders_customer` (`customer_id`),
  KEY `fk_orders_coupon` (`coupon_id`),
  KEY `fk_orders_rate` (`shipping_rate_id`),
  KEY `idx_orders_unpaid` (`payment_status`,`payment_expires_at`),
  KEY `idx_orders_website_placed` (`website_id`,`placed_at`),
  CONSTRAINT `fk_orders_coupon` FOREIGN KEY (`coupon_id`) REFERENCES `coupons` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_orders_customer` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_orders_rate` FOREIGN KEY (`shipping_rate_id`) REFERENCES `shipping_rates` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_orders_tenant` FOREIGN KEY (`tenant_id`) REFERENCES `tenants` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_orders_website` FOREIGN KEY (`website_id`) REFERENCES `websites` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `payments` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `tenant_id` bigint(20) unsigned NOT NULL,
  `website_id` bigint(20) unsigned NOT NULL,
  `order_id` bigint(20) unsigned NOT NULL,
  `gateway` varchar(20) NOT NULL DEFAULT 'razorpay',
  `gateway_order_id` varchar(64) NOT NULL,
  `gateway_payment_id` varchar(64) DEFAULT NULL,
  `method` varchar(20) DEFAULT NULL COMMENT 'upi|card|netbanking|wallet|emi',
  `amount` decimal(12,2) NOT NULL COMMENT 'Copied from order.grand_total server-side',
  `amount_refunded` decimal(12,2) NOT NULL DEFAULT 0.00,
  `currency` char(3) NOT NULL DEFAULT 'INR',
  `status` varchar(20) NOT NULL DEFAULT 'created' COMMENT 'created|authorized|captured|failed|refunded',
  `signature_verified` tinyint(1) NOT NULL DEFAULT 0,
  `error_code` varchar(60) DEFAULT NULL,
  `error_description` varchar(255) DEFAULT NULL,
  `captured_at` datetime DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_pay_gateway_order` (`gateway_order_id`),
  KEY `idx_pay_order` (`order_id`),
  KEY `idx_pay_payment_id` (`gateway_payment_id`),
  KEY `idx_pay_tenant_status` (`tenant_id`,`status`),
  KEY `fk_pay_website` (`website_id`),
  CONSTRAINT `fk_pay_order` FOREIGN KEY (`order_id`) REFERENCES `orders` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_pay_tenant` FOREIGN KEY (`tenant_id`) REFERENCES `tenants` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_pay_website` FOREIGN KEY (`website_id`) REFERENCES `websites` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `permissions` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `slug` varchar(100) NOT NULL,
  `name` varchar(150) NOT NULL,
  `module` varchar(50) NOT NULL,
  `scope` varchar(20) NOT NULL COMMENT 'platform|tenant',
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_permissions_slug` (`slug`),
  KEY `idx_permissions_module` (`module`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `platform_coupons` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `code` varchar(40) NOT NULL,
  `type` varchar(10) NOT NULL COMMENT 'percent|fixed',
  `value` decimal(12,2) NOT NULL,
  `applies_to_plan_id` int(10) unsigned DEFAULT NULL COMMENT 'NULL = any plan',
  `max_redemptions` int(10) unsigned DEFAULT NULL,
  `redeemed_count` int(10) unsigned NOT NULL DEFAULT 0,
  `starts_at` datetime DEFAULT NULL,
  `expires_at` datetime DEFAULT NULL,
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_pc_code` (`code`),
  KEY `fk_pc_plan` (`applies_to_plan_id`),
  CONSTRAINT `fk_pc_plan` FOREIGN KEY (`applies_to_plan_id`) REFERENCES `subscription_plans` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `platform_settings` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `setting_group` varchar(40) NOT NULL COMMENT 'general|cms|builder|smtp|payments|seo',
  `setting_key` varchar(100) NOT NULL,
  `setting_value` text DEFAULT NULL,
  `is_encrypted` tinyint(1) NOT NULL DEFAULT 0,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_ps_key` (`setting_key`),
  KEY `idx_ps_group` (`setting_group`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `product_attributes` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `tenant_id` bigint(20) unsigned NOT NULL,
  `product_id` bigint(20) unsigned NOT NULL,
  `name` varchar(60) NOT NULL COMMENT 'e.g. Size',
  `values` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL COMMENT 'e.g. ["S","M","L"]' CHECK (json_valid(`values`)),
  `is_variation` tinyint(1) NOT NULL DEFAULT 1,
  `sort_order` smallint(6) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  KEY `idx_pattr_product` (`product_id`),
  KEY `idx_pattr_tenant` (`tenant_id`),
  CONSTRAINT `fk_pattr_product` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_pattr_tenant` FOREIGN KEY (`tenant_id`) REFERENCES `tenants` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `product_categories` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `tenant_id` bigint(20) unsigned NOT NULL,
  `website_id` bigint(20) unsigned NOT NULL,
  `parent_id` bigint(20) unsigned DEFAULT NULL,
  `name` varchar(120) NOT NULL,
  `slug` varchar(140) NOT NULL,
  `description` text DEFAULT NULL,
  `image` varchar(500) DEFAULT NULL,
  `sort_order` smallint(6) NOT NULL DEFAULT 0,
  `status` varchar(20) NOT NULL DEFAULT 'active' COMMENT 'active|hidden',
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_pcat_slug` (`website_id`,`slug`),
  KEY `idx_pcat_tenant` (`tenant_id`),
  KEY `fk_pcat_parent` (`parent_id`),
  CONSTRAINT `fk_pcat_parent` FOREIGN KEY (`parent_id`) REFERENCES `product_categories` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_pcat_tenant` FOREIGN KEY (`tenant_id`) REFERENCES `tenants` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_pcat_website` FOREIGN KEY (`website_id`) REFERENCES `websites` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `product_category_map` (
  `product_id` bigint(20) unsigned NOT NULL,
  `category_id` bigint(20) unsigned NOT NULL,
  PRIMARY KEY (`product_id`,`category_id`),
  KEY `idx_pcm_category` (`category_id`),
  CONSTRAINT `fk_pcm_category` FOREIGN KEY (`category_id`) REFERENCES `product_categories` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_pcm_product` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `product_images` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `tenant_id` bigint(20) unsigned NOT NULL,
  `product_id` bigint(20) unsigned NOT NULL,
  `media_file_id` bigint(20) unsigned DEFAULT NULL,
  `path` varchar(500) NOT NULL,
  `alt_text` varchar(255) DEFAULT NULL,
  `sort_order` smallint(6) NOT NULL DEFAULT 0,
  `is_primary` tinyint(1) NOT NULL DEFAULT 0,
  `created_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_pimg_product` (`product_id`,`sort_order`),
  KEY `idx_pimg_tenant` (`tenant_id`),
  KEY `fk_pimg_media` (`media_file_id`),
  CONSTRAINT `fk_pimg_media` FOREIGN KEY (`media_file_id`) REFERENCES `media_files` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_pimg_product` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_pimg_tenant` FOREIGN KEY (`tenant_id`) REFERENCES `tenants` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `product_tag_map` (
  `product_id` bigint(20) unsigned NOT NULL,
  `tag_id` bigint(20) unsigned NOT NULL,
  PRIMARY KEY (`product_id`,`tag_id`),
  KEY `idx_ptm_tag` (`tag_id`),
  CONSTRAINT `fk_ptm_product` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_ptm_tag` FOREIGN KEY (`tag_id`) REFERENCES `product_tags` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `product_tags` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `tenant_id` bigint(20) unsigned NOT NULL,
  `website_id` bigint(20) unsigned NOT NULL,
  `name` varchar(60) NOT NULL,
  `slug` varchar(70) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_ptag_slug` (`website_id`,`slug`),
  KEY `idx_ptag_tenant` (`tenant_id`),
  CONSTRAINT `fk_ptag_tenant` FOREIGN KEY (`tenant_id`) REFERENCES `tenants` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_ptag_website` FOREIGN KEY (`website_id`) REFERENCES `websites` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `product_variants` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `tenant_id` bigint(20) unsigned NOT NULL,
  `product_id` bigint(20) unsigned NOT NULL,
  `title` varchar(150) NOT NULL COMMENT 'e.g. M / Red',
  `options` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL COMMENT 'e.g. {"Size":"M","Color":"Red"}' CHECK (json_valid(`options`)),
  `sku` varchar(64) DEFAULT NULL,
  `regular_price` decimal(12,2) NOT NULL,
  `sale_price` decimal(12,2) DEFAULT NULL,
  `weight_grams` int(10) unsigned DEFAULT NULL,
  `image_id` bigint(20) unsigned DEFAULT NULL,
  `status` varchar(20) NOT NULL DEFAULT 'active',
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_pvar_product` (`product_id`),
  KEY `idx_pvar_tenant` (`tenant_id`),
  KEY `fk_pvar_image` (`image_id`),
  CONSTRAINT `fk_pvar_image` FOREIGN KEY (`image_id`) REFERENCES `product_images` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_pvar_product` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_pvar_tenant` FOREIGN KEY (`tenant_id`) REFERENCES `tenants` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `products` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `tenant_id` bigint(20) unsigned NOT NULL,
  `website_id` bigint(20) unsigned NOT NULL,
  `name` varchar(200) NOT NULL,
  `slug` varchar(220) NOT NULL,
  `short_description` varchar(500) DEFAULT NULL,
  `description` longtext DEFAULT NULL,
  `sku` varchar(64) DEFAULT NULL,
  `regular_price` decimal(12,2) NOT NULL DEFAULT 0.00,
  `sale_price` decimal(12,2) DEFAULT NULL,
  `cost_price` decimal(12,2) DEFAULT NULL,
  `tax_rate` decimal(5,2) DEFAULT NULL COMMENT 'NULL = use store default',
  `hsn_code` varchar(10) DEFAULT NULL,
  `track_inventory` tinyint(1) NOT NULL DEFAULT 1,
  `allow_backorder` tinyint(1) NOT NULL DEFAULT 0,
  `weight_grams` int(10) unsigned DEFAULT NULL,
  `has_variants` tinyint(1) NOT NULL DEFAULT 0,
  `is_featured` tinyint(1) NOT NULL DEFAULT 0,
  `status` varchar(20) NOT NULL DEFAULT 'draft' COMMENT 'draft|active|archived',
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  `deleted_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_products_slug` (`website_id`,`slug`),
  UNIQUE KEY `uq_products_sku` (`website_id`,`sku`),
  KEY `idx_products_tenant` (`tenant_id`),
  KEY `idx_products_listing` (`website_id`,`status`,`is_featured`),
  FULLTEXT KEY `ft_products_search` (`name`,`short_description`),
  CONSTRAINT `fk_products_tenant` FOREIGN KEY (`tenant_id`) REFERENCES `tenants` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_products_website` FOREIGN KEY (`website_id`) REFERENCES `websites` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `redirects` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `tenant_id` bigint(20) unsigned NOT NULL,
  `website_id` bigint(20) unsigned NOT NULL,
  `source_path` varchar(255) NOT NULL,
  `target_url` varchar(500) NOT NULL,
  `status_code` smallint(5) unsigned NOT NULL DEFAULT 301,
  `hits` int(10) unsigned NOT NULL DEFAULT 0,
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_redirect_source` (`website_id`,`source_path`),
  KEY `idx_redirect_tenant` (`tenant_id`),
  CONSTRAINT `fk_redirect_tenant` FOREIGN KEY (`tenant_id`) REFERENCES `tenants` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_redirect_website` FOREIGN KEY (`website_id`) REFERENCES `websites` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `refunds` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `tenant_id` bigint(20) unsigned NOT NULL,
  `payment_id` bigint(20) unsigned NOT NULL,
  `order_id` bigint(20) unsigned NOT NULL,
  `gateway_refund_id` varchar(64) DEFAULT NULL,
  `amount` decimal(12,2) NOT NULL,
  `reason` varchar(255) DEFAULT NULL,
  `status` varchar(20) NOT NULL DEFAULT 'pending' COMMENT 'pending|processed|failed',
  `created_by` bigint(20) unsigned DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_refund_gateway` (`gateway_refund_id`),
  KEY `idx_refund_payment` (`payment_id`),
  KEY `idx_refund_tenant` (`tenant_id`),
  KEY `fk_refund_order` (`order_id`),
  CONSTRAINT `fk_refund_order` FOREIGN KEY (`order_id`) REFERENCES `orders` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_refund_payment` FOREIGN KEY (`payment_id`) REFERENCES `payments` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_refund_tenant` FOREIGN KEY (`tenant_id`) REFERENCES `tenants` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `role_permissions` (
  `role_id` int(10) unsigned NOT NULL,
  `permission_id` int(10) unsigned NOT NULL,
  PRIMARY KEY (`role_id`,`permission_id`),
  KEY `idx_rp_permission` (`permission_id`),
  CONSTRAINT `fk_rp_permission` FOREIGN KEY (`permission_id`) REFERENCES `permissions` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_rp_role` FOREIGN KEY (`role_id`) REFERENCES `roles` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `roles` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(80) NOT NULL,
  `slug` varchar(60) NOT NULL,
  `scope` varchar(20) NOT NULL COMMENT 'platform|tenant',
  `description` varchar(255) DEFAULT NULL,
  `is_system` tinyint(1) NOT NULL DEFAULT 1,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_roles_slug` (`slug`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `seo_settings` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `tenant_id` bigint(20) unsigned NOT NULL,
  `website_id` bigint(20) unsigned NOT NULL,
  `entity_type` varchar(20) NOT NULL COMMENT 'website|page|product|category|blog',
  `entity_id` bigint(20) unsigned NOT NULL,
  `meta_title` varchar(70) DEFAULT NULL,
  `meta_description` varchar(170) DEFAULT NULL,
  `meta_keywords` varchar(255) DEFAULT NULL,
  `canonical_url` varchar(500) DEFAULT NULL,
  `og_title` varchar(100) DEFAULT NULL,
  `og_description` varchar(200) DEFAULT NULL,
  `og_image` varchar(500) DEFAULT NULL,
  `robots` varchar(40) NOT NULL DEFAULT 'index,follow',
  `structured_data` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`structured_data`)),
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_seo_entity` (`website_id`,`entity_type`,`entity_id`),
  KEY `idx_seo_tenant` (`tenant_id`),
  CONSTRAINT `fk_seo_tenant` FOREIGN KEY (`tenant_id`) REFERENCES `tenants` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_seo_website` FOREIGN KEY (`website_id`) REFERENCES `websites` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `shipping_rates` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `tenant_id` bigint(20) unsigned NOT NULL,
  `zone_id` bigint(20) unsigned NOT NULL,
  `name` varchar(100) NOT NULL,
  `type` varchar(20) NOT NULL COMMENT 'flat|free|weight|price',
  `cost` decimal(12,2) NOT NULL DEFAULT 0.00,
  `cost_per_kg` decimal(12,2) DEFAULT NULL,
  `min_order_amount` decimal(12,2) DEFAULT NULL,
  `max_order_amount` decimal(12,2) DEFAULT NULL,
  `min_weight_grams` int(10) unsigned DEFAULT NULL,
  `max_weight_grams` int(10) unsigned DEFAULT NULL,
  `estimated_days` varchar(20) DEFAULT NULL,
  `provider` varchar(30) DEFAULT NULL COMMENT 'Reserved for carrier APIs, e.g. shiprocket',
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_sr_zone` (`zone_id`),
  KEY `idx_sr_tenant` (`tenant_id`),
  CONSTRAINT `fk_sr_tenant` FOREIGN KEY (`tenant_id`) REFERENCES `tenants` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_sr_zone` FOREIGN KEY (`zone_id`) REFERENCES `shipping_zones` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `shipping_zones` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `tenant_id` bigint(20) unsigned NOT NULL,
  `website_id` bigint(20) unsigned NOT NULL,
  `name` varchar(100) NOT NULL,
  `countries` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT 'ISO codes; NULL = all' CHECK (json_valid(`countries`)),
  `states` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`states`)),
  `pincode_rules` text DEFAULT NULL COMMENT 'Comma list of pincodes, prefixes (11*) or ranges (110001-110099)',
  `sort_order` smallint(6) NOT NULL DEFAULT 0 COMMENT 'Most specific zone first',
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_sz_website` (`website_id`,`sort_order`),
  KEY `idx_sz_tenant` (`tenant_id`),
  CONSTRAINT `fk_sz_tenant` FOREIGN KEY (`tenant_id`) REFERENCES `tenants` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_sz_website` FOREIGN KEY (`website_id`) REFERENCES `websites` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `subscription_plans` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(80) NOT NULL,
  `slug` varchar(60) NOT NULL,
  `description` varchar(500) DEFAULT NULL,
  `price_monthly` decimal(12,2) NOT NULL DEFAULT 0.00,
  `price_yearly` decimal(12,2) NOT NULL DEFAULT 0.00,
  `currency` char(3) NOT NULL DEFAULT 'INR',
  `max_websites` int(10) unsigned NOT NULL DEFAULT 1,
  `max_products` int(10) unsigned DEFAULT NULL COMMENT 'NULL = unlimited',
  `max_pages` int(10) unsigned DEFAULT NULL COMMENT 'NULL = unlimited',
  `max_storage_mb` int(10) unsigned NOT NULL DEFAULT 500,
  `allow_custom_domain` tinyint(1) NOT NULL DEFAULT 0,
  `allow_premium_templates` tinyint(1) NOT NULL DEFAULT 0,
  `allow_ecommerce` tinyint(1) NOT NULL DEFAULT 1,
  `allow_remove_branding` tinyint(1) NOT NULL DEFAULT 0,
  `seo_level` varchar(20) NOT NULL DEFAULT 'basic' COMMENT 'basic|advanced',
  `analytics_level` varchar(20) NOT NULL DEFAULT 'basic' COMMENT 'basic|advanced',
  `priority_support` tinyint(1) NOT NULL DEFAULT 0,
  `trial_days` smallint(5) unsigned NOT NULL DEFAULT 0,
  `grace_days` smallint(5) unsigned NOT NULL DEFAULT 7,
  `features` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT 'Marketing bullet list for pricing page' CHECK (json_valid(`features`)),
  `sort_order` smallint(6) NOT NULL DEFAULT 0,
  `is_default` tinyint(1) NOT NULL DEFAULT 0 COMMENT 'Assigned on registration',
  `is_public` tinyint(1) NOT NULL DEFAULT 1,
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_plans_slug` (`slug`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `subscription_transactions` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `tenant_id` bigint(20) unsigned NOT NULL,
  `subscription_id` bigint(20) unsigned DEFAULT NULL,
  `plan_id` int(10) unsigned NOT NULL,
  `billing_cycle` varchar(10) NOT NULL,
  `type` varchar(20) NOT NULL COMMENT 'new|renewal|upgrade|downgrade|refund',
  `subtotal` decimal(12,2) NOT NULL,
  `discount` decimal(12,2) NOT NULL DEFAULT 0.00,
  `tax_amount` decimal(12,2) NOT NULL DEFAULT 0.00,
  `amount` decimal(12,2) NOT NULL COMMENT 'Server-computed total charged',
  `currency` char(3) NOT NULL DEFAULT 'INR',
  `status` varchar(20) NOT NULL DEFAULT 'created' COMMENT 'created|paid|failed|refunded',
  `platform_coupon_id` int(10) unsigned DEFAULT NULL,
  `gateway` varchar(20) NOT NULL DEFAULT 'razorpay',
  `gateway_order_id` varchar(64) DEFAULT NULL,
  `gateway_payment_id` varchar(64) DEFAULT NULL,
  `invoice_number` varchar(32) DEFAULT NULL,
  `paid_at` datetime DEFAULT NULL,
  `meta` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`meta`)),
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_st_gateway_order` (`gateway_order_id`),
  UNIQUE KEY `uq_st_invoice` (`invoice_number`),
  KEY `idx_st_tenant` (`tenant_id`),
  KEY `idx_st_status_paid` (`status`,`paid_at`),
  KEY `fk_st_sub` (`subscription_id`),
  KEY `fk_st_plan` (`plan_id`),
  KEY `fk_st_coupon` (`platform_coupon_id`),
  CONSTRAINT `fk_st_coupon` FOREIGN KEY (`platform_coupon_id`) REFERENCES `platform_coupons` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_st_plan` FOREIGN KEY (`plan_id`) REFERENCES `subscription_plans` (`id`),
  CONSTRAINT `fk_st_sub` FOREIGN KEY (`subscription_id`) REFERENCES `subscriptions` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_st_tenant` FOREIGN KEY (`tenant_id`) REFERENCES `tenants` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `subscriptions` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `tenant_id` bigint(20) unsigned NOT NULL,
  `plan_id` int(10) unsigned NOT NULL,
  `billing_cycle` varchar(10) NOT NULL COMMENT 'free|monthly|yearly',
  `status` varchar(20) NOT NULL COMMENT 'trialing|active|grace|past_due|cancelled|expired',
  `amount` decimal(12,2) NOT NULL DEFAULT 0.00,
  `currency` char(3) NOT NULL DEFAULT 'INR',
  `started_at` datetime NOT NULL,
  `current_period_start` datetime NOT NULL,
  `current_period_end` datetime DEFAULT NULL COMMENT 'NULL for free plan (never expires)',
  `grace_ends_at` datetime DEFAULT NULL,
  `cancel_at_period_end` tinyint(1) NOT NULL DEFAULT 0,
  `cancelled_at` datetime DEFAULT NULL,
  `scheduled_plan_id` int(10) unsigned DEFAULT NULL COMMENT 'Pending downgrade applied at period end',
  `renewal_reminded_at` datetime DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_subs_tenant_status` (`tenant_id`,`status`),
  KEY `idx_subs_period_end` (`current_period_end`),
  KEY `fk_subs_plan` (`plan_id`),
  KEY `fk_subs_sched_plan` (`scheduled_plan_id`),
  CONSTRAINT `fk_subs_plan` FOREIGN KEY (`plan_id`) REFERENCES `subscription_plans` (`id`),
  CONSTRAINT `fk_subs_sched_plan` FOREIGN KEY (`scheduled_plan_id`) REFERENCES `subscription_plans` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_subs_tenant` FOREIGN KEY (`tenant_id`) REFERENCES `tenants` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `support_ticket_replies` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `tenant_id` bigint(20) unsigned NOT NULL,
  `ticket_id` bigint(20) unsigned NOT NULL,
  `user_id` bigint(20) unsigned DEFAULT NULL,
  `is_staff` tinyint(1) NOT NULL DEFAULT 0,
  `message` text NOT NULL,
  `attachments` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`attachments`)),
  `created_at` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_str_ticket` (`ticket_id`,`created_at`),
  KEY `idx_str_tenant` (`tenant_id`),
  KEY `fk_str_user` (`user_id`),
  CONSTRAINT `fk_str_tenant` FOREIGN KEY (`tenant_id`) REFERENCES `tenants` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_str_ticket` FOREIGN KEY (`ticket_id`) REFERENCES `support_tickets` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_str_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `support_tickets` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `tenant_id` bigint(20) unsigned NOT NULL,
  `user_id` bigint(20) unsigned NOT NULL,
  `ticket_number` varchar(20) NOT NULL,
  `subject` varchar(200) NOT NULL,
  `category` varchar(30) NOT NULL DEFAULT 'general' COMMENT 'general|billing|technical|domain|other',
  `priority` varchar(10) NOT NULL DEFAULT 'normal' COMMENT 'low|normal|high|urgent',
  `status` varchar(20) NOT NULL DEFAULT 'open' COMMENT 'open|awaiting_customer|answered|closed',
  `assigned_to` bigint(20) unsigned DEFAULT NULL,
  `last_reply_at` datetime DEFAULT NULL,
  `closed_at` datetime DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_tickets_number` (`ticket_number`),
  KEY `idx_tickets_tenant` (`tenant_id`,`status`),
  KEY `idx_tickets_status` (`status`,`priority`),
  KEY `fk_tickets_user` (`user_id`),
  KEY `fk_tickets_assignee` (`assigned_to`),
  CONSTRAINT `fk_tickets_assignee` FOREIGN KEY (`assigned_to`) REFERENCES `users` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_tickets_tenant` FOREIGN KEY (`tenant_id`) REFERENCES `tenants` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_tickets_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `tenants` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `uuid` char(36) NOT NULL,
  `name` varchar(150) NOT NULL,
  `slug` varchar(80) NOT NULL,
  `owner_user_id` bigint(20) unsigned DEFAULT NULL,
  `status` varchar(20) NOT NULL DEFAULT 'active' COMMENT 'active|suspended|deactivated',
  `status_reason` varchar(255) DEFAULT NULL,
  `country` char(2) NOT NULL DEFAULT 'IN',
  `timezone` varchar(64) NOT NULL DEFAULT 'Asia/Kolkata',
  `trial_used_at` datetime DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  `deleted_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_tenants_uuid` (`uuid`),
  UNIQUE KEY `uq_tenants_slug` (`slug`),
  KEY `idx_tenants_status` (`status`),
  KEY `idx_tenants_owner` (`owner_user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `user_tokens` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) unsigned NOT NULL,
  `type` varchar(20) NOT NULL COMMENT 'email_verify|password_reset|invite',
  `selector` char(24) NOT NULL,
  `token_hash` char(64) NOT NULL COMMENT 'sha256 of validator; raw validator never stored',
  `expires_at` datetime NOT NULL,
  `used_at` datetime DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_user_tokens_selector` (`selector`),
  KEY `idx_user_tokens_user_type` (`user_id`,`type`),
  CONSTRAINT `fk_user_tokens_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `users` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `tenant_id` bigint(20) unsigned DEFAULT NULL COMMENT 'NULL for platform staff',
  `role_id` int(10) unsigned NOT NULL,
  `first_name` varchar(80) NOT NULL,
  `last_name` varchar(80) DEFAULT NULL,
  `email` varchar(191) NOT NULL,
  `phone` varchar(20) DEFAULT NULL,
  `password_hash` varchar(255) NOT NULL,
  `status` varchar(20) NOT NULL DEFAULT 'active' COMMENT 'active|pending|suspended|deactivated',
  `email_verified_at` datetime DEFAULT NULL,
  `last_login_at` datetime DEFAULT NULL,
  `last_login_ip` varchar(45) DEFAULT NULL,
  `failed_login_count` smallint(5) unsigned NOT NULL DEFAULT 0,
  `locked_until` datetime DEFAULT NULL,
  `password_changed_at` datetime DEFAULT NULL,
  `must_change_password` tinyint(1) NOT NULL DEFAULT 0,
  `avatar` varchar(255) DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  `deleted_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_users_email` (`email`),
  KEY `idx_users_tenant` (`tenant_id`),
  KEY `idx_users_status` (`status`),
  KEY `fk_users_role` (`role_id`),
  CONSTRAINT `fk_users_role` FOREIGN KEY (`role_id`) REFERENCES `roles` (`id`),
  CONSTRAINT `fk_users_tenant` FOREIGN KEY (`tenant_id`) REFERENCES `tenants` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `webhook_events` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `source` varchar(20) NOT NULL COMMENT 'platform|store',
  `tenant_id` bigint(20) unsigned DEFAULT NULL,
  `website_id` bigint(20) unsigned DEFAULT NULL,
  `event_id` varchar(100) NOT NULL COMMENT 'Gateway event id, used for idempotency',
  `event_type` varchar(60) NOT NULL,
  `signature_valid` tinyint(1) NOT NULL DEFAULT 0,
  `payload` longtext NOT NULL,
  `processed_at` datetime DEFAULT NULL,
  `error` varchar(500) DEFAULT NULL,
  `created_at` datetime NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_we_source_event` (`source`,`event_id`),
  KEY `idx_we_tenant` (`tenant_id`),
  KEY `idx_we_type_time` (`event_type`,`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `website_builder_projects` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `tenant_id` bigint(20) unsigned NOT NULL,
  `website_id` bigint(20) unsigned NOT NULL,
  `project_data` longtext NOT NULL COMMENT 'GrapesJS project JSON (draft)',
  `draft_version` int(10) unsigned NOT NULL DEFAULT 1 COMMENT 'Optimistic lock, bumped on each save',
  `autosaved_at` datetime DEFAULT NULL,
  `updated_by` bigint(20) unsigned DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_wbp_website` (`website_id`),
  KEY `idx_wbp_tenant` (`tenant_id`),
  KEY `fk_wbp_user` (`updated_by`),
  CONSTRAINT `fk_wbp_tenant` FOREIGN KEY (`tenant_id`) REFERENCES `tenants` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_wbp_user` FOREIGN KEY (`updated_by`) REFERENCES `users` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_wbp_website` FOREIGN KEY (`website_id`) REFERENCES `websites` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `website_builder_revisions` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `tenant_id` bigint(20) unsigned NOT NULL,
  `website_id` bigint(20) unsigned NOT NULL,
  `version` int(10) unsigned NOT NULL,
  `label` varchar(120) DEFAULT NULL,
  `project_data` longtext NOT NULL,
  `created_by` bigint(20) unsigned DEFAULT NULL,
  `created_at` datetime NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_wbr_website_version` (`website_id`,`version`),
  KEY `idx_wbr_tenant` (`tenant_id`),
  KEY `fk_wbr_user` (`created_by`),
  CONSTRAINT `fk_wbr_tenant` FOREIGN KEY (`tenant_id`) REFERENCES `tenants` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_wbr_user` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_wbr_website` FOREIGN KEY (`website_id`) REFERENCES `websites` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `website_domains` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `tenant_id` bigint(20) unsigned NOT NULL,
  `website_id` bigint(20) unsigned NOT NULL,
  `domain` varchar(253) NOT NULL,
  `is_primary` tinyint(1) NOT NULL DEFAULT 0,
  `verification_token` varchar(64) NOT NULL,
  `verification_status` varchar(20) NOT NULL DEFAULT 'pending' COMMENT 'pending|verified|failed',
  `verification_method` varchar(10) NOT NULL DEFAULT 'cname' COMMENT 'cname|txt',
  `verified_at` datetime DEFAULT NULL,
  `last_checked_at` datetime DEFAULT NULL,
  `last_check_error` varchar(255) DEFAULT NULL,
  `routing_status` varchar(20) NOT NULL DEFAULT 'pending' COMMENT 'pending|ok|failed',
  `routing_checked_at` datetime DEFAULT NULL,
  `check_attempts` smallint(5) unsigned NOT NULL DEFAULT 0,
  `ssl_status` varchar(20) NOT NULL DEFAULT 'none' COMMENT 'none|pending|active|failed',
  `ssl_expires_at` datetime DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_wd_domain` (`domain`),
  KEY `idx_wd_tenant` (`tenant_id`),
  KEY `idx_wd_website` (`website_id`),
  CONSTRAINT `fk_wd_tenant` FOREIGN KEY (`tenant_id`) REFERENCES `tenants` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_wd_website` FOREIGN KEY (`website_id`) REFERENCES `websites` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `website_menu_items` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `tenant_id` bigint(20) unsigned NOT NULL,
  `menu_id` bigint(20) unsigned NOT NULL,
  `parent_id` bigint(20) unsigned DEFAULT NULL,
  `label` varchar(100) NOT NULL,
  `link_type` varchar(20) NOT NULL DEFAULT 'url' COMMENT 'page|product|category|blog|url',
  `link_ref_id` bigint(20) unsigned DEFAULT NULL,
  `url` varchar(500) DEFAULT NULL,
  `open_new_tab` tinyint(1) NOT NULL DEFAULT 0,
  `sort_order` smallint(6) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  KEY `idx_wmi_menu` (`menu_id`,`sort_order`),
  KEY `idx_wmi_tenant` (`tenant_id`),
  KEY `fk_wmi_parent` (`parent_id`),
  CONSTRAINT `fk_wmi_menu` FOREIGN KEY (`menu_id`) REFERENCES `website_menus` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_wmi_parent` FOREIGN KEY (`parent_id`) REFERENCES `website_menu_items` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `website_menus` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `tenant_id` bigint(20) unsigned NOT NULL,
  `website_id` bigint(20) unsigned NOT NULL,
  `name` varchar(80) NOT NULL,
  `location` varchar(20) NOT NULL DEFAULT 'custom' COMMENT 'header|footer|custom',
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_wm_website` (`website_id`),
  KEY `idx_wm_tenant` (`tenant_id`),
  CONSTRAINT `fk_wm_tenant` FOREIGN KEY (`tenant_id`) REFERENCES `tenants` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_wm_website` FOREIGN KEY (`website_id`) REFERENCES `websites` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `website_pages` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `tenant_id` bigint(20) unsigned NOT NULL,
  `website_id` bigint(20) unsigned NOT NULL,
  `builder_page_id` varchar(64) DEFAULT NULL COMMENT 'GrapesJS page id inside project_data',
  `title` varchar(150) NOT NULL,
  `slug` varchar(150) NOT NULL,
  `page_type` varchar(20) NOT NULL DEFAULT 'page' COMMENT 'home|page|product|collection|blog|cart|checkout|system',
  `is_homepage` tinyint(1) NOT NULL DEFAULT 0,
  `status` varchar(20) NOT NULL DEFAULT 'draft' COMMENT 'draft|published',
  `sort_order` smallint(6) NOT NULL DEFAULT 0,
  `published_html` longtext DEFAULT NULL COMMENT 'Sanitized render output',
  `published_css` longtext DEFAULT NULL,
  `published_at` datetime DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  `deleted_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_wp_website_slug` (`website_id`,`slug`),
  KEY `idx_wp_tenant` (`tenant_id`),
  CONSTRAINT `fk_wp_tenant` FOREIGN KEY (`tenant_id`) REFERENCES `tenants` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_wp_website` FOREIGN KEY (`website_id`) REFERENCES `websites` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `website_saved_sections` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `tenant_id` bigint(20) unsigned NOT NULL,
  `name` varchar(120) NOT NULL,
  `category` varchar(40) NOT NULL DEFAULT 'custom',
  `component_json` longtext NOT NULL,
  `css` mediumtext DEFAULT NULL,
  `created_by` bigint(20) unsigned DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_wss_tenant` (`tenant_id`),
  CONSTRAINT `fk_wss_tenant` FOREIGN KEY (`tenant_id`) REFERENCES `tenants` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `website_settings` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `tenant_id` bigint(20) unsigned NOT NULL,
  `website_id` bigint(20) unsigned NOT NULL,
  `theme` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT 'colors, fonts, button radius' CHECK (json_valid(`theme`)),
  `currency` char(3) NOT NULL DEFAULT 'INR',
  `timezone` varchar(64) NOT NULL DEFAULT 'Asia/Kolkata',
  `contact_email` varchar(191) DEFAULT NULL,
  `contact_phone` varchar(20) DEFAULT NULL,
  `address` varchar(500) DEFAULT NULL,
  `social_links` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`social_links`)),
  `store_enabled` tinyint(1) NOT NULL DEFAULT 0,
  `tax_rate` decimal(5,2) NOT NULL DEFAULT 0.00,
  `prices_include_tax` tinyint(1) NOT NULL DEFAULT 1,
  `gstin` varchar(15) DEFAULT NULL,
  `legal_name` varchar(160) DEFAULT NULL COMMENT 'Seller name on invoices',
  `business_state` varchar(100) DEFAULT NULL COMMENT 'For CGST+SGST vs IGST on invoices',
  `shipping_flat_rate` decimal(12,2) NOT NULL DEFAULT 0.00,
  `free_shipping_min` decimal(12,2) DEFAULT NULL COMMENT 'Order subtotal for free shipping; NULL = never',
  `cod_enabled` tinyint(1) NOT NULL DEFAULT 1,
  `cod_max_amount` decimal(12,2) DEFAULT NULL COMMENT 'Hide COD above this total; NULL = no limit',
  `order_prefix` varchar(10) NOT NULL DEFAULT '',
  `order_seq` int(10) unsigned NOT NULL DEFAULT 1000,
  `invoice_seq` int(10) unsigned NOT NULL DEFAULT 0,
  `checkout_note` varchar(500) DEFAULT NULL COMMENT 'Shown on the checkout page (returns policy etc.)',
  `razorpay_key_id` varchar(64) DEFAULT NULL,
  `razorpay_key_secret_enc` text DEFAULT NULL COMMENT 'Encrypted with app encryption key',
  `razorpay_webhook_secret_enc` text DEFAULT NULL,
  `razorpay_verified_at` datetime DEFAULT NULL COMMENT 'Last successful key check',
  `online_payments_enabled` tinyint(1) NOT NULL DEFAULT 0,
  `robots_txt` text DEFAULT NULL,
  `custom_head_html` text DEFAULT NULL,
  `custom_css` mediumtext DEFAULT NULL,
  `published_theme` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`published_theme`)),
  `published_custom_css` mediumtext DEFAULT NULL,
  `analytics_id` varchar(40) DEFAULT NULL,
  `site_verification` text DEFAULT NULL COMMENT 'JSON: google, bing, pinterest, facebook verification codes',
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_ws_website` (`website_id`),
  KEY `idx_ws_tenant` (`tenant_id`),
  CONSTRAINT `fk_ws_tenant` FOREIGN KEY (`tenant_id`) REFERENCES `tenants` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_ws_website` FOREIGN KEY (`website_id`) REFERENCES `websites` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `website_template_categories` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(80) NOT NULL,
  `slug` varchar(80) NOT NULL,
  `sort_order` smallint(6) NOT NULL DEFAULT 0,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_wtc_slug` (`slug`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `website_templates` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `category_id` int(10) unsigned DEFAULT NULL,
  `name` varchar(120) NOT NULL,
  `slug` varchar(120) NOT NULL,
  `description` varchar(500) DEFAULT NULL,
  `pages_summary` varchar(255) DEFAULT NULL,
  `thumbnail` varchar(255) DEFAULT NULL,
  `is_premium` tinyint(1) NOT NULL DEFAULT 0,
  `is_ecommerce` tinyint(1) NOT NULL DEFAULT 0,
  `project_data` longtext NOT NULL COMMENT 'GrapesJS multi-page project JSON',
  `theme` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT 'Default colors/fonts' CHECK (json_valid(`theme`)),
  `custom_css` mediumtext DEFAULT NULL,
  `version` int(10) unsigned NOT NULL DEFAULT 1,
  `status` varchar(20) NOT NULL DEFAULT 'draft' COMMENT 'draft|published|archived',
  `created_by` bigint(20) unsigned DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  `deleted_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_wt_slug` (`slug`),
  KEY `idx_wt_status` (`status`),
  KEY `fk_wt_creator` (`created_by`),
  KEY `idx_wt_category` (`category_id`,`status`),
  CONSTRAINT `fk_wt_category` FOREIGN KEY (`category_id`) REFERENCES `website_template_categories` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_wt_creator` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `websites` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `tenant_id` bigint(20) unsigned NOT NULL,
  `template_id` int(10) unsigned DEFAULT NULL,
  `name` varchar(120) NOT NULL,
  `category` varchar(40) NOT NULL DEFAULT 'other',
  `subdomain` varchar(63) NOT NULL,
  `status` varchar(20) NOT NULL DEFAULT 'draft' COMMENT 'draft|published|unpublished|suspended',
  `status_reason` varchar(255) DEFAULT NULL,
  `published_version` int(10) unsigned DEFAULT NULL,
  `published_at` datetime DEFAULT NULL,
  `logo` varchar(255) DEFAULT NULL,
  `favicon` varchar(255) DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  `deleted_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_websites_subdomain` (`subdomain`),
  KEY `idx_websites_tenant` (`tenant_id`,`status`),
  KEY `fk_websites_template` (`template_id`),
  CONSTRAINT `fk_websites_template` FOREIGN KEY (`template_id`) REFERENCES `website_templates` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_websites_tenant` FOREIGN KEY (`tenant_id`) REFERENCES `tenants` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Applied migrations
/*M!999999\- enable the sandbox mode */ 
INSERT INTO `migrations` VALUES (226,'2026-11-08-000001','App\\Database\\Migrations\\CreateCoreAuthTables','default','App',1794291672,1);
INSERT INTO `migrations` VALUES (227,'2026-11-08-000002','App\\Database\\Migrations\\CreateSubscriptionTables','default','App',1794291672,1);
INSERT INTO `migrations` VALUES (228,'2026-11-08-000003','App\\Database\\Migrations\\CreateWebsiteTables','default','App',1794291672,1);
INSERT INTO `migrations` VALUES (229,'2026-11-08-000004','App\\Database\\Migrations\\CreateCommerceTables','default','App',1794291672,1);
INSERT INTO `migrations` VALUES (230,'2026-11-08-000005','App\\Database\\Migrations\\CreateContentSupportTables','default','App',1794291672,1);
INSERT INTO `migrations` VALUES (231,'2026-11-08-000006','App\\Database\\Migrations\\Phase2Additions','default','App',1794291672,1);
INSERT INTO `migrations` VALUES (232,'2026-11-08-000007','App\\Database\\Migrations\\Phase3Builder','default','App',1794291672,1);
INSERT INTO `migrations` VALUES (233,'2026-11-09-000008','App\\Database\\Migrations\\Phase4Storefront','default','App',1794291672,1);
INSERT INTO `migrations` VALUES (234,'2026-11-09-000009','App\\Database\\Migrations\\Phase5Commerce','default','App',1794291672,1);
INSERT INTO `migrations` VALUES (235,'2026-11-09-000010','App\\Database\\Migrations\\Phase6Payments','default','App',1794291672,1);
INSERT INTO `migrations` VALUES (236,'2026-11-09-000011','App\\Database\\Migrations\\Phase7Content','default','App',1794291672,1);
INSERT INTO `migrations` VALUES (237,'2026-11-10-000012','App\\Database\\Migrations\\Phase8Performance','default','App',1794291672,1);

SET FOREIGN_KEY_CHECKS = 1;