CREATE DATABASE IF NOT EXISTS store CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE store;
SET FOREIGN_KEY_CHECKS=0;
CREATE TABLE IF NOT EXISTS `ad_campaigns` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(200) DEFAULT NULL,
`platform` varchar(50) DEFAULT NULL,
`status` varchar(20) DEFAULT 'draft',
`status_code` int(11) DEFAULT NULL,
`budget` decimal(10,2) DEFAULT NULL,
`created_at` datetime DEFAULT current_timestamp(),
PRIMARY KEY (`id`),
KEY `idx_status_code` (`status_code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE IF NOT EXISTS `admin_users` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`username` varchar(100) DEFAULT NULL,
`password_hash` varchar(255) DEFAULT NULL,
`admin_level` tinyint(4) DEFAULT 0,
`access_tier` varchar(20) DEFAULT NULL,
`pref_a` int(11) DEFAULT 0,
`pref_b` int(11) DEFAULT 0,
`pref_c` int(11) DEFAULT 0,
`pref_d` int(11) DEFAULT 0,
`is_active` tinyint(1) DEFAULT 1,
`last_login` datetime DEFAULT NULL,
`last_ip` varchar(45) DEFAULT NULL,
`created_at` datetime DEFAULT current_timestamp(),
PRIMARY KEY (`id`),
UNIQUE KEY `username` (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS `app_settings` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`setting_key` varchar(100) DEFAULT NULL,
`setting_value` text DEFAULT NULL,
`updated_at` datetime DEFAULT current_timestamp() ON UPDATE current_timestamp(),
PRIMARY KEY (`id`),
UNIQUE KEY `setting_key` (`setting_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE IF NOT EXISTS `audit_log` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`action` varchar(100) DEFAULT NULL,
`entity_type` varchar(50) DEFAULT NULL,
`entity_id` int(10) unsigned DEFAULT NULL,
`admin_id` int(10) unsigned DEFAULT NULL,
`ip` varchar(45) DEFAULT NULL,
`details` text DEFAULT NULL,
`created_at` datetime DEFAULT current_timestamp(),
PRIMARY KEY (`id`),
KEY `idx_action` (`action`),
KEY `idx_admin` (`admin_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE IF NOT EXISTS `blacklist` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`ip_address` varchar(45) DEFAULT NULL,
`reason` varchar(200) DEFAULT NULL,
`severity` enum('low','medium','high','critical') DEFAULT 'medium',
`expires_at` datetime DEFAULT NULL,
`created_at` datetime DEFAULT current_timestamp(),
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS `block_types` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`type_key` varchar(50) DEFAULT NULL,
`label` varchar(100) DEFAULT NULL,
`is_active` tinyint(1) DEFAULT 1,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE IF NOT EXISTS `campaign_hits` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`campaign_id` int(10) unsigned DEFAULT NULL,
`ip_address` varchar(45) DEFAULT NULL,
`user_agent` text DEFAULT NULL,
`referrer` text DEFAULT NULL,
`created_at` datetime DEFAULT current_timestamp(),
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE IF NOT EXISTS `campaign_visits` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`campaign_id` int(10) unsigned DEFAULT NULL,
`ip_address` varchar(45) DEFAULT NULL,
`referrer` varchar(500) DEFAULT NULL,
`converted` tinyint(1) DEFAULT 0,
`created_at` datetime DEFAULT current_timestamp(),
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS `categories` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`store_id` int(10) unsigned NOT NULL,
`name` varchar(200) DEFAULT NULL,
`nickname` varchar(200) DEFAULT NULL,
`slug` varchar(200) DEFAULT NULL,
`display_order` int(11) DEFAULT 0,
`show_in_nav` tinyint(1) DEFAULT 1,
`status` enum('active','hidden','deleted') DEFAULT 'active',
`status_code` int(11) DEFAULT 5001,
`created_at` datetime DEFAULT current_timestamp(),
`description` text DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx_store` (`store_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS `content_blocks` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`page` varchar(50) DEFAULT NULL,
`block_key` varchar(100) DEFAULT NULL,
`content` text DEFAULT NULL,
`store_id` int(10) unsigned DEFAULT NULL,
`is_active` tinyint(1) DEFAULT 1,
`created_at` datetime DEFAULT current_timestamp(),
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE IF NOT EXISTS `crypto_transactions` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`wallet_id` int(10) unsigned DEFAULT NULL,
`txid` varchar(255) DEFAULT NULL,
`amount` decimal(18,8) DEFAULT 0.00000000,
`status` varchar(20) DEFAULT 'pending',
`created_at` datetime DEFAULT current_timestamp(),
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE IF NOT EXISTS `crypto_wallets` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`currency` varchar(10) DEFAULT 'BTC',
`address` varchar(255) DEFAULT NULL,
`label` varchar(100) DEFAULT NULL,
`balance` decimal(18,8) DEFAULT 0.00000000,
`is_active` tinyint(1) DEFAULT 1,
`created_at` datetime DEFAULT current_timestamp(),
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE IF NOT EXISTS `customer_ip_logs` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`user_id` int(10) unsigned DEFAULT NULL,
`ip_address` varchar(45) DEFAULT NULL,
`user_agent` text DEFAULT NULL,
`created_at` datetime DEFAULT current_timestamp(),
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE IF NOT EXISTS `deception_tokens` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`token` varchar(255) DEFAULT NULL,
`token_type` varchar(50) DEFAULT NULL,
`is_triggered` tinyint(1) DEFAULT 0,
`created_at` datetime DEFAULT current_timestamp(),
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE IF NOT EXISTS `device_bindings` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`user_id` int(10) unsigned DEFAULT NULL,
`device_fingerprint` varchar(255) DEFAULT NULL,
`trust_score` decimal(5,2) DEFAULT 0.00,
`is_trusted` tinyint(1) DEFAULT 0,
`created_at` datetime DEFAULT current_timestamp(),
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE IF NOT EXISTS `devices` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`admin_id` int(10) unsigned NOT NULL,
`fingerprint` varchar(64) DEFAULT NULL,
`label` varchar(100) DEFAULT NULL,
`last_seen` datetime DEFAULT NULL,
`is_trusted` tinyint(1) DEFAULT 0,
`created_at` datetime DEFAULT current_timestamp(),
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS `email_log` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`recipient` varchar(255) DEFAULT NULL,
`subject` text DEFAULT NULL,
`template` varchar(100) DEFAULT NULL,
`status` varchar(20) DEFAULT 'sent',
`store_id` int(10) unsigned DEFAULT NULL,
`created_at` datetime DEFAULT current_timestamp(),
`relay` varchar(100) DEFAULT NULL,
`message_id` varchar(255) DEFAULT NULL,
`error_detail` text DEFAULT NULL,
`email_type` varchar(50) DEFAULT NULL,
`sent_to` varchar(255) DEFAULT NULL,
`order_id` int(10) unsigned DEFAULT NULL,
`error_message` text DEFAULT NULL,
`smtp_host` varchar(100) DEFAULT NULL,
`relay_method` varchar(20) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE IF NOT EXISTS `etransfer_inbox` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`gmail_uid` varchar(100) DEFAULT NULL,
`sender_email` varchar(255) DEFAULT NULL,
`sender_name` varchar(200) DEFAULT NULL,
`subject` text DEFAULT NULL,
`amount` decimal(10,2) DEFAULT NULL,
`reference_number` varchar(50) DEFAULT NULL,
`deposit_status` enum('auto_deposited','pending_accept','manual') DEFAULT 'auto_deposited',
`matched_order_id` int(10) unsigned DEFAULT NULL,
`verification_status` enum('pending','verified','rejected','expired') DEFAULT 'pending',
`verified_by` int(10) unsigned DEFAULT NULL,
`verified_at` datetime DEFAULT NULL,
`raw_body` text DEFAULT NULL,
`received_at` datetime DEFAULT NULL,
`created_at` datetime DEFAULT current_timestamp(),
PRIMARY KEY (`id`),
UNIQUE KEY `idx_gmail_uid` (`gmail_uid`),
KEY `idx_status` (`verification_status`),
KEY `idx_order` (`matched_order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE IF NOT EXISTS `events` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`category` varchar(50) DEFAULT NULL,
`action` varchar(100) DEFAULT NULL,
`target_type` varchar(50) DEFAULT NULL,
`target_id` int(10) unsigned DEFAULT NULL,
`ip_address` varchar(45) DEFAULT NULL,
`user_agent` text DEFAULT NULL,
`admin_id` int(10) unsigned DEFAULT NULL,
`meta` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`meta`)),
`created_at` datetime DEFAULT current_timestamp(),
`fields_accessed` text DEFAULT NULL,
`entity_type` varchar(50) DEFAULT NULL,
`entity_id` int(10) unsigned DEFAULT NULL,
`severity` varchar(20) DEFAULT 'info',
`details` text DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx_category` (`category`),
KEY `idx_created` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS `excluded_devices` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`device_fingerprint` varchar(255) DEFAULT NULL,
`reason` text DEFAULT NULL,
`created_at` datetime DEFAULT current_timestamp(),
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE IF NOT EXISTS `faq` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`store_id` int(10) unsigned DEFAULT NULL,
`tab_group` varchar(100) DEFAULT 'General',
`question` text DEFAULT NULL,
`answer` text DEFAULT NULL,
`display_order` int(11) DEFAULT 0,
`status` enum('active','hidden','deleted') DEFAULT 'active',
`created_at` datetime DEFAULT current_timestamp(),
`is_active` tinyint(1) DEFAULT 1,
`category_id` int(10) unsigned DEFAULT NULL,
`public_id` varchar(50) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS `fleet_node_meta` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`node_id` varchar(50) DEFAULT NULL,
`meta_key` varchar(100) DEFAULT NULL,
`meta_value` text DEFAULT NULL,
`updated_at` datetime DEFAULT current_timestamp() ON UPDATE current_timestamp(),
PRIMARY KEY (`id`),
UNIQUE KEY `idx_node_key` (`node_id`,`meta_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE IF NOT EXISTS `honeypot_hits` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`ip_address` varchar(45) DEFAULT NULL,
`trap_type` varchar(50) DEFAULT NULL,
`user_agent` text DEFAULT NULL,
`created_at` datetime DEFAULT current_timestamp(),
`request_method` varchar(10) DEFAULT NULL,
`trap_route` varchar(200) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS `legal_pages` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`slug` varchar(50) DEFAULT NULL,
`title` text DEFAULT NULL,
`body` mediumtext DEFAULT NULL,
`updated_at` datetime DEFAULT current_timestamp() ON UPDATE current_timestamp(),
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS `marketing_ad_accounts` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`account_id` varchar(100) DEFAULT NULL,
`account_name` varchar(200) DEFAULT NULL,
`access_token` text DEFAULT NULL,
`status` varchar(20) DEFAULT 'active',
`created_at` datetime DEFAULT current_timestamp(),
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE IF NOT EXISTS `marketing_campaigns` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(200) DEFAULT NULL,
`platform` enum('meta','google','tiktok','manual') DEFAULT 'manual',
`budget` decimal(10,2) DEFAULT 0.00,
`status` enum('active','paused','archived') DEFAULT 'active',
`meta` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`meta`)),
`created_at` datetime DEFAULT current_timestamp(),
`slug` varchar(200) DEFAULT NULL,
`campaign_name` text DEFAULT NULL,
`hits` int(11) DEFAULT 0,
`unique_visitors` int(11) DEFAULT 0,
`conversions` int(11) DEFAULT 0,
`nginx_active` tinyint(1) DEFAULT 0,
`daily_budget` decimal(10,2) DEFAULT NULL,
`ad_account_id` int(10) unsigned DEFAULT NULL,
`campaign_id` varchar(100) DEFAULT NULL,
`objective` varchar(50) DEFAULT NULL,
`lifetime_budget` decimal(10,2) DEFAULT NULL,
`total_spend` decimal(12,2) DEFAULT 0.00,
`impressions` bigint(20) DEFAULT 0,
`clicks` bigint(20) DEFAULT 0,
`last_synced_at` datetime DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS `marketing_links` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`slug` varchar(200) DEFAULT NULL,
`campaign_name` text DEFAULT NULL,
`hits` int(11) DEFAULT 0,
`unique_visitors` int(11) DEFAULT 0,
`conversions` int(11) DEFAULT 0,
`nginx_active` tinyint(1) DEFAULT 0,
`status` varchar(20) DEFAULT 'draft',
`status_code` int(11) DEFAULT NULL,
`created_at` datetime DEFAULT current_timestamp(),
PRIMARY KEY (`id`),
KEY `idx_status_code` (`status_code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE IF NOT EXISTS `marketing_social_assets` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`asset_type` varchar(50) DEFAULT NULL,
`content` text DEFAULT NULL,
`status` varchar(20) DEFAULT 'active',
`created_at` datetime DEFAULT current_timestamp(),
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE IF NOT EXISTS `marketing_social_nodes` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`platform` varchar(50) DEFAULT NULL,
`node_url` varchar(500) DEFAULT NULL,
`status` varchar(20) DEFAULT 'active',
`status_code` int(11) DEFAULT NULL,
`created_at` datetime DEFAULT current_timestamp(),
PRIMARY KEY (`id`),
KEY `idx_status_code` (`status_code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE IF NOT EXISTS `merkle_anchors` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`anchor_hash` varchar(255) DEFAULT NULL,
`table_name` varchar(100) DEFAULT NULL,
`row_count` int(11) DEFAULT 0,
`created_at` datetime DEFAULT current_timestamp(),
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE IF NOT EXISTS `node_commands` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`node_id` int(10) unsigned NOT NULL,
`command` text DEFAULT NULL,
`status` enum('queued','approved','executing','completed','failed','cancelled') DEFAULT 'queued',
`output` mediumtext DEFAULT NULL,
`exit_code` int(11) DEFAULT NULL,
`queued_by` int(10) unsigned DEFAULT NULL,
`approved_by` int(10) unsigned DEFAULT NULL,
`created_at` datetime DEFAULT current_timestamp(),
`completed_at` datetime DEFAULT NULL,
`status_code` int(11) DEFAULT 5010,
`priority_code` int(11) DEFAULT 5000,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS `nodes` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`node_id` varchar(50) DEFAULT NULL,
`role` varchar(50) DEFAULT NULL,
`node_role` varchar(50) DEFAULT NULL,
`hostname` varchar(200) DEFAULT NULL,
`public_ip` varchar(45) DEFAULT NULL,
`wg_ip` varchar(45) DEFAULT NULL,
`wg_pubkey` text DEFAULT NULL,
`ssh_port` int(11) DEFAULT 33022,
`status` enum('online','offline','maintenance','provisioning','decommissioned') DEFAULT 'offline',
`cpu_usage` decimal(5,2) DEFAULT 0.00,
`mem_usage` decimal(5,2) DEFAULT 0.00,
`disk_usage` decimal(5,2) DEFAULT 0.00,
`engine_version` varchar(50) DEFAULT NULL,
`last_heartbeat` datetime DEFAULT NULL,
`consecutive_misses` int(11) DEFAULT 0,
`created_at` datetime DEFAULT current_timestamp(),
`updated_at` datetime DEFAULT current_timestamp() ON UPDATE current_timestamp(),
`status_code` int(11) DEFAULT 5001,
`metrics` text DEFAULT NULL,
`role_code` int(11) DEFAULT NULL,
`wg_pub_key` text DEFAULT NULL,
`node_name` varchar(100) DEFAULT NULL,
`ip_address` varchar(45) DEFAULT NULL,
`latency_ms` int(11) DEFAULT NULL,
`ssh_user` varchar(50) DEFAULT 'admin',
`uptime_seconds` bigint(20) DEFAULT NULL,
`cpu_usage_pct` decimal(5,2) DEFAULT NULL,
`mem_usage_pct` decimal(5,2) DEFAULT NULL,
`disk_usage_pct` decimal(5,2) DEFAULT NULL,
`route_type` varchar(50) DEFAULT NULL,
`canvas_x` int(11) DEFAULT 0,
`canvas_y` int(11) DEFAULT 0,
`is_reachable` tinyint(1) DEFAULT 0,
`standby_node_id` int(10) unsigned DEFAULT NULL,
`is_active` tinyint(1) DEFAULT 1,
`monthly_cost_cad` varchar(100) DEFAULT NULL,
`location_city` varchar(200) DEFAULT NULL,
`location_country` varchar(200) DEFAULT NULL,
`datacenter` varchar(200) DEFAULT NULL,
`provider` varchar(200) DEFAULT NULL,
`os` varchar(100) DEFAULT NULL,
`cpu_cores` int(11) DEFAULT NULL,
`ram_gb` int(11) DEFAULT NULL,
`disk_gb` int(11) DEFAULT NULL,
`bandwidth_tb` int(11) DEFAULT NULL,
`payment_method` varchar(100) DEFAULT NULL,
`renewal_date` varchar(100) DEFAULT NULL,
`wg_peers` text DEFAULT NULL,
`notes` text DEFAULT NULL,
`api_port` int(11) DEFAULT NULL,
`standby_ready` tinyint(1) DEFAULT 0,
`service_url` varchar(500) DEFAULT NULL,
`last_seen` datetime DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `node_id` (`node_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS `order_items` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`order_id` int(10) unsigned NOT NULL,
`variant_id` int(10) unsigned NOT NULL,
`size_id` int(10) unsigned DEFAULT NULL,
`product_name` text DEFAULT NULL,
`quality_name` varchar(100) DEFAULT NULL,
`type_name` varchar(100) DEFAULT NULL,
`size_label` varchar(50) DEFAULT NULL,
`quantity` int(11) DEFAULT 1,
`unit_price` decimal(10,2) NOT NULL,
`total_price` decimal(10,2) NOT NULL,
`listing_id` int(10) unsigned DEFAULT NULL,
`listing_name` text DEFAULT NULL,
`qty` int(11) DEFAULT 1,
`unit_label` varchar(50) DEFAULT NULL,
`item_order` int(11) DEFAULT 0,
PRIMARY KEY (`id`),
KEY `order_id` (`order_id`),
CONSTRAINT `order_items_ibfk_1` FOREIGN KEY (`order_id`) REFERENCES `orders` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS `orders` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`store_id` int(10) unsigned NOT NULL,
`user_id` int(10) unsigned DEFAULT NULL,
`listing_id` int(10) unsigned DEFAULT NULL,
`reference` varchar(20) DEFAULT NULL,
`order_ref` varchar(20) DEFAULT NULL,
`email` text DEFAULT NULL,
`name` text DEFAULT NULL,
`address` text DEFAULT NULL,
`city` text DEFAULT NULL,
`province` varchar(100) DEFAULT NULL,
`postal_code` text DEFAULT NULL,
`country` varchar(50) DEFAULT 'CA',
`subtotal` decimal(10,2) DEFAULT 0.00,
`shipping` decimal(10,2) DEFAULT 0.00,
`total` decimal(10,2) DEFAULT 0.00,
`payment_method` enum('stripe','etransfer','crypto','credit') DEFAULT 'stripe',
`status` enum('pending','paid','processing','packed','shipped','delivered','cancelled','refunded','disputed','hold') DEFAULT 'pending',
`tracking_number` text DEFAULT NULL,
`tracking_carrier` varchar(50) DEFAULT NULL,
`fulfillment_log` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`fulfillment_log`)),
`notes` text DEFAULT NULL,
`created_at` datetime DEFAULT current_timestamp(),
`updated_at` datetime DEFAULT current_timestamp() ON UPDATE current_timestamp(),
`status_code` int(11) DEFAULT 5001,
`status_label` varchar(50) DEFAULT NULL,
`canada_post_tracking` varchar(100) DEFAULT NULL,
`etransfer_ref` varchar(100) DEFAULT NULL,
`fulfillment_json` text DEFAULT NULL,
`fulfillment_stage` varchar(50) DEFAULT NULL,
`ip_address` varchar(45) DEFAULT NULL,
`is_test` tinyint(1) DEFAULT 0,
`product_title` text DEFAULT NULL,
`quantity` int(11) DEFAULT 1,
`shipping_method` varchar(50) DEFAULT NULL,
`shipping_name` text DEFAULT NULL,
`stage_updated_at` datetime DEFAULT NULL,
`time_at_checkout` int(11) DEFAULT NULL,
`title` text DEFAULT NULL,
`tracking_ref` varchar(100) DEFAULT NULL,
`user_agent` text DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `reference` (`reference`),
KEY `idx_store` (`store_id`),
KEY `idx_user` (`user_id`),
KEY `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS `page_blocks` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`store_id` int(10) unsigned DEFAULT NULL,
`page` varchar(50) DEFAULT NULL,
`block_key` varchar(100) DEFAULT NULL,
`content` text DEFAULT NULL,
`display_order` int(11) DEFAULT 0,
`is_active` tinyint(1) DEFAULT 1,
`created_at` datetime DEFAULT current_timestamp(),
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE IF NOT EXISTS `payment_cards` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`user_id` int(10) unsigned DEFAULT NULL,
`nickname` varchar(100) DEFAULT NULL,
`last_four` varchar(4) DEFAULT NULL,
`brand` varchar(20) DEFAULT NULL,
`exp_month` tinyint(4) DEFAULT NULL,
`exp_year` smallint(6) DEFAULT NULL,
`is_default` tinyint(1) DEFAULT 0,
`created_at` datetime DEFAULT current_timestamp(),
`card_name` text DEFAULT NULL,
`card_number` text DEFAULT NULL,
`card_month` text DEFAULT NULL,
`card_year` text DEFAULT NULL,
`card_postal` text DEFAULT NULL,
`initial_balance` decimal(10,2) DEFAULT 0.00,
`current_balance` decimal(10,2) DEFAULT 0.00,
`expiry` varchar(5) DEFAULT NULL,
`updated_at` datetime DEFAULT NULL,
`last_4` varchar(4) DEFAULT NULL,
`status` varchar(20) DEFAULT 'active',
`status_code` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx_status_code` (`status_code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE IF NOT EXISTS `payment_ledger` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`order_id` int(10) unsigned DEFAULT NULL,
`payment_method` enum('etransfer','crypto','stripe','credit','manual') DEFAULT NULL,
`amount` decimal(10,2) NOT NULL DEFAULT 0.00,
`currency` varchar(5) DEFAULT 'CAD',
`direction` enum('credit','debit','refund') DEFAULT 'credit',
`reference` varchar(100) DEFAULT NULL,
`notes` text DEFAULT NULL,
`admin_id` int(10) unsigned DEFAULT NULL,
`created_at` datetime DEFAULT current_timestamp(),
`reference_code` varchar(100) DEFAULT NULL,
`status` varchar(30) DEFAULT 'pending',
`sender_name` varchar(200) DEFAULT NULL,
`sender_email` varchar(255) DEFAULT NULL,
`confirmed_at` datetime DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx_order` (`order_id`),
KEY `idx_method` (`payment_method`),
KEY `idx_date` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE IF NOT EXISTS `payment_logs` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`order_id` int(10) unsigned DEFAULT NULL,
`method` varchar(50) DEFAULT NULL,
`amount` decimal(10,2) DEFAULT 0.00,
`status` varchar(50) DEFAULT NULL,
`raw_response` text DEFAULT NULL,
`created_at` datetime DEFAULT current_timestamp(),
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE IF NOT EXISTS `payments` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`order_id` int(10) unsigned DEFAULT NULL,
`method` enum('stripe','etransfer','crypto','credit') DEFAULT NULL,
`amount` decimal(10,2) NOT NULL,
`currency` varchar(10) DEFAULT 'CAD',
`stripe_id` varchar(100) DEFAULT NULL,
`tx_hash` varchar(200) DEFAULT NULL,
`status` enum('pending','confirmed','failed','refunded') DEFAULT 'pending',
`meta` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`meta`)),
`created_at` datetime DEFAULT current_timestamp(),
`order_ref` varchar(20) DEFAULT NULL,
`status_code` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS `product_pricing_tiers` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`product_id` int(10) unsigned DEFAULT NULL,
`min_qty` int(11) DEFAULT 1,
`max_qty` int(11) DEFAULT NULL,
`price` decimal(10,2) DEFAULT 0.00,
`created_at` datetime DEFAULT current_timestamp(),
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE IF NOT EXISTS `product_qualities` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`product_id` int(10) unsigned DEFAULT NULL,
`name` varchar(100) DEFAULT NULL,
`quality_name` text DEFAULT NULL,
`base_price` decimal(10,2) DEFAULT 0.00,
`image_path` varchar(500) DEFAULT NULL,
`is_active` tinyint(1) DEFAULT 1,
`display_order` int(11) DEFAULT 0,
PRIMARY KEY (`id`),
KEY `product_id` (`product_id`),
CONSTRAINT `product_qualities_ibfk_1` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS `product_sizes` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`variant_id` int(10) unsigned DEFAULT NULL,
`label` varchar(50) DEFAULT NULL,
`amount` decimal(10,3) DEFAULT 0.000,
`price` decimal(10,2) DEFAULT 0.00,
`display_order` int(11) DEFAULT 0,
PRIMARY KEY (`id`),
KEY `variant_id` (`variant_id`),
CONSTRAINT `product_sizes_ibfk_1` FOREIGN KEY (`variant_id`) REFERENCES `product_variants` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS `product_types` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`product_id` int(10) unsigned DEFAULT NULL,
`name` varchar(100) DEFAULT NULL,
`type_name` text DEFAULT NULL,
`base_price` decimal(10,2) DEFAULT 0.00,
`image_path` varchar(500) DEFAULT NULL,
`is_active` tinyint(1) DEFAULT 1,
`display_order` int(11) DEFAULT 0,
PRIMARY KEY (`id`),
KEY `product_id` (`product_id`),
CONSTRAINT `product_types_ibfk_1` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS `product_units` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`product_id` int(10) unsigned NOT NULL,
`quality_id` int(10) unsigned DEFAULT NULL,
`unit_amount` text DEFAULT NULL,
`unit_price` decimal(10,2) NOT NULL DEFAULT 0.00,
`display_order` int(11) DEFAULT 0,
`created_at` datetime DEFAULT current_timestamp(),
PRIMARY KEY (`id`),
KEY `idx_product_id` (`product_id`),
KEY `idx_quality_id` (`quality_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE IF NOT EXISTS `product_variants` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`product_id` int(10) unsigned DEFAULT NULL,
`quality_id` int(10) unsigned DEFAULT NULL,
`type_id` int(10) unsigned DEFAULT NULL,
`sku` varchar(50) DEFAULT NULL,
`image` varchar(500) DEFAULT NULL,
`price` decimal(10,2) DEFAULT 0.00,
`sale_price` decimal(10,2) DEFAULT NULL,
`stock` int(11) DEFAULT -1,
`status` enum('active','paused','deleted') DEFAULT 'active',
`created_at` datetime DEFAULT current_timestamp(),
PRIMARY KEY (`id`),
UNIQUE KEY `sku` (`sku`),
KEY `product_id` (`product_id`),
CONSTRAINT `product_variants_ibfk_1` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS `products` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`store_id` int(10) unsigned DEFAULT 1,
`category_id` int(10) unsigned DEFAULT NULL,
`sku` varchar(50) DEFAULT NULL,
`name` text DEFAULT NULL,
`title` text DEFAULT NULL,
`nickname` varchar(200) DEFAULT NULL,
`slug` varchar(200) DEFAULT NULL,
`description` text DEFAULT NULL,
`quality` text DEFAULT NULL,
`type` text DEFAULT NULL,
`short_desc` text DEFAULT NULL,
`pitch_title` text DEFAULT NULL,
`pitch_body` text DEFAULT NULL,
`notes` text DEFAULT NULL,
`image` varchar(500) DEFAULT NULL,
`image_url` text DEFAULT NULL,
`image_path` text DEFAULT NULL,
`price` decimal(10,2) DEFAULT 0.00,
`sale_price` decimal(10,2) DEFAULT NULL,
`base_price` decimal(10,2) DEFAULT 0.00,
`has_qualities` tinyint(1) DEFAULT 0,
`has_types` tinyint(1) DEFAULT 0,
`unit_label` varchar(50) DEFAULT 'grams',
`status` varchar(20) DEFAULT 'draft',
`status_code` int(11) DEFAULT 3001,
`visibility_code` int(11) DEFAULT 5001,
`visibility` varchar(20) DEFAULT 'public',
`display_order` int(11) DEFAULT 0,
`created_at` datetime DEFAULT current_timestamp(),
`updated_at` datetime DEFAULT current_timestamp() ON UPDATE current_timestamp(),
`slot_size` int(11) DEFAULT 1,
`unit_increment` decimal(10,3) DEFAULT NULL,
`unit_price_per` decimal(10,2) DEFAULT NULL,
`unit_tiers` text DEFAULT NULL,
`unit_min_qty` int(11) DEFAULT 1,
`featured_code` int(11) DEFAULT NULL,
`unit_pricing_code` int(11) DEFAULT NULL,
`is_encrypted` tinyint(1) DEFAULT 0,
`gallery` text DEFAULT NULL,
`meta_json` text DEFAULT NULL,
`currency_code` varchar(5) DEFAULT 'CAD',
`product_code` varchar(50) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `sku` (`sku`),
KEY `idx_store` (`store_id`),
KEY `idx_category` (`category_id`),
KEY `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS `public_ids` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`public_id` varchar(50) DEFAULT NULL,
`internal_id` int(10) unsigned NOT NULL,
`entity_type` varchar(50) DEFAULT NULL,
`created_at` datetime DEFAULT current_timestamp(),
PRIMARY KEY (`id`),
UNIQUE KEY `public_id` (`public_id`),
KEY `idx_entity` (`entity_type`,`internal_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS `rate_limits` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`ip_address` varchar(45) DEFAULT NULL,
`endpoint` varchar(100) DEFAULT NULL,
`hits` int(11) DEFAULT 1,
`window_start` datetime DEFAULT current_timestamp(),
PRIMARY KEY (`id`),
KEY `idx_lookup` (`ip_address`,`endpoint`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS `review_codes` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`code` varchar(16) NOT NULL,
`user_id` int(10) unsigned NOT NULL,
`listing_id` int(10) unsigned DEFAULT NULL,
`credit_amount` decimal(10,2) DEFAULT 0.00,
`used` tinyint(1) NOT NULL DEFAULT 0,
`expires_at` datetime NOT NULL,
`created_at` datetime NOT NULL DEFAULT current_timestamp(),
PRIMARY KEY (`id`),
UNIQUE KEY `idx_code` (`code`),
KEY `idx_user_id` (`user_id`),
KEY `idx_used_expires` (`used`,`expires_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE IF NOT EXISTS `reviews` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`store_id` int(10) unsigned NOT NULL,
`user_id` int(10) unsigned DEFAULT NULL,
`product_id` int(10) unsigned DEFAULT NULL,
`rating` tinyint(4) DEFAULT 5,
`title` text DEFAULT NULL,
`body` text DEFAULT NULL,
`is_approved` tinyint(1) DEFAULT 0,
`is_verified` tinyint(1) DEFAULT 0,
`admin_reply` text DEFAULT NULL,
`helpful_count` int(11) DEFAULT 0,
`created_at` datetime DEFAULT current_timestamp(),
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS `security_events` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`event_type` varchar(100) DEFAULT NULL,
`ip_address` varchar(45) DEFAULT NULL,
`user_agent` text DEFAULT NULL,
`severity` varchar(20) DEFAULT 'info',
`details` text DEFAULT NULL,
`created_at` datetime DEFAULT current_timestamp(),
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE IF NOT EXISTS `security_keystroke_profiles` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`data` text DEFAULT NULL,
`created_at` datetime DEFAULT current_timestamp(),
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE IF NOT EXISTS `security_lockdown_log` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`data` text DEFAULT NULL,
`created_at` datetime DEFAULT current_timestamp(),
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE IF NOT EXISTS `security_policy_views` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`data` text DEFAULT NULL,
`created_at` datetime DEFAULT current_timestamp(),
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE IF NOT EXISTS `security_purchase_velocity` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`data` text DEFAULT NULL,
`created_at` datetime DEFAULT current_timestamp(),
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE IF NOT EXISTS `security_session_ip_changes` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`user_id` int(10) unsigned DEFAULT NULL,
`old_ip` varchar(45) DEFAULT NULL,
`new_ip` varchar(45) DEFAULT NULL,
`created_at` datetime DEFAULT current_timestamp(),
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE IF NOT EXISTS `security_threat_classes` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`data` text DEFAULT NULL,
`created_at` datetime DEFAULT current_timestamp(),
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE IF NOT EXISTS `security_trust_params` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`data` text DEFAULT NULL,
`created_at` datetime DEFAULT current_timestamp(),
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE IF NOT EXISTS `security_visitor_sessions` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`data` text DEFAULT NULL,
`created_at` datetime DEFAULT current_timestamp(),
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE IF NOT EXISTS `security_watchdog_events` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`event_type` varchar(100) DEFAULT NULL,
`severity` varchar(20) DEFAULT 'info',
`ip_address` varchar(45) DEFAULT NULL,
`details` text DEFAULT NULL,
`created_at` datetime DEFAULT current_timestamp(),
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE IF NOT EXISTS `self_exclusions` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`user_id` int(10) unsigned DEFAULT NULL,
`reason` text DEFAULT NULL,
`status` varchar(20) DEFAULT 'pending',
`created_at` datetime DEFAULT current_timestamp(),
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE IF NOT EXISTS `settings` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`setting_key` varchar(100) DEFAULT NULL,
`setting_value` text DEFAULT NULL,
`created_at` datetime DEFAULT current_timestamp(),
PRIMARY KEY (`id`),
UNIQUE KEY `setting_key` (`setting_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS `social_posts` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`platform` varchar(50) DEFAULT NULL,
`content` text DEFAULT NULL,
`status` varchar(20) DEFAULT 'draft',
`created_at` datetime DEFAULT current_timestamp(),
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE IF NOT EXISTS `storefront_blocks` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`store_id` int(10) unsigned DEFAULT NULL,
`category_id` int(10) unsigned DEFAULT NULL,
`title` text DEFAULT NULL,
`body` text DEFAULT NULL,
`block_type` varchar(50) DEFAULT 'product',
`slot_size` int(11) DEFAULT 1,
`display_order` int(11) DEFAULT 0,
`mobile_display` tinyint(1) DEFAULT 1,
`bg_color` varchar(20) DEFAULT NULL,
`text_color` varchar(20) DEFAULT NULL,
`image_url` text DEFAULT NULL,
`is_active` tinyint(1) DEFAULT 1,
`config_json` text DEFAULT NULL,
`status_code` int(11) DEFAULT 6001,
`block_config_enc` text DEFAULT NULL,
`created_at` datetime DEFAULT current_timestamp(),
`updated_at` datetime DEFAULT NULL ON UPDATE current_timestamp(),
PRIMARY KEY (`id`),
KEY `idx_store_id` (`store_id`),
KEY `idx_status` (`status_code`),
KEY `idx_display` (`display_order`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE IF NOT EXISTS `storefront_layouts` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`store_id` int(10) unsigned NOT NULL,
`layout_name` varchar(100) DEFAULT NULL,
`is_active` tinyint(1) DEFAULT 1,
`created_at` datetime DEFAULT current_timestamp(),
PRIMARY KEY (`id`),
KEY `idx_store` (`store_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE IF NOT EXISTS `storefront_rows` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`store_id` int(10) unsigned NOT NULL,
`row_type` varchar(50) NOT NULL DEFAULT 'banner',
`config` text DEFAULT NULL,
`display_order` int(11) DEFAULT 0,
`is_active` tinyint(1) DEFAULT 1,
`created_at` datetime DEFAULT current_timestamp(),
`row_order` int(11) DEFAULT 0,
PRIMARY KEY (`id`),
KEY `idx_store_id` (`store_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE IF NOT EXISTS `stores` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`domain` varchar(200) DEFAULT NULL,
`nickname` varchar(100) DEFAULT NULL,
`store_name` text DEFAULT NULL,
`tagline` text DEFAULT NULL,
`store_config` text DEFAULT NULL,
`accent_color` varchar(7) DEFAULT '#2D6A4F',
`color_master` varchar(7) DEFAULT '#2D6A4F',
`store_type` varchar(50) DEFAULT 'pharma',
`is_active` tinyint(1) DEFAULT 1,
`status_code` int(11) DEFAULT 5001,
`maintenance_mode` tinyint(1) DEFAULT 0,
`proxy_enabled` tinyint(1) DEFAULT 1,
`created_at` datetime DEFAULT current_timestamp(),
`campaign_name` text DEFAULT NULL,
`logo_url` varchar(500) DEFAULT NULL,
`slug` varchar(200) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `domain` (`domain`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS `support_messages` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`user_id` int(10) unsigned DEFAULT NULL,
`subject` text DEFAULT NULL,
`body` text NOT NULL,
`is_admin` tinyint(1) DEFAULT 0,
`is_read` tinyint(1) DEFAULT 0,
`parent_id` int(10) unsigned DEFAULT NULL,
`urgency` enum('low','normal','high','critical') DEFAULT 'normal',
`status` enum('open','waiting','escalated','resolved','closed') DEFAULT 'open',
`created_at` datetime DEFAULT current_timestamp(),
`thread_id` int(10) unsigned DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS `sys_settings` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`setting_key` varchar(100) NOT NULL,
`setting_value` text DEFAULT NULL,
`updated_at` datetime DEFAULT current_timestamp() ON UPDATE current_timestamp(),
PRIMARY KEY (`id`),
UNIQUE KEY `setting_key` (`setting_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE IF NOT EXISTS `terms` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`slug` varchar(50) NOT NULL,
`subtitle` varchar(255) DEFAULT NULL,
`t01` varchar(10) DEFAULT 'p',
`c01` text DEFAULT NULL,
`t02` varchar(10) DEFAULT 'p',
`c02` text DEFAULT NULL,
`t03` varchar(10) DEFAULT 'p',
`c03` text DEFAULT NULL,
`t04` varchar(10) DEFAULT 'p',
`c04` text DEFAULT NULL,
`t05` varchar(10) DEFAULT 'p',
`c05` text DEFAULT NULL,
`t06` varchar(10) DEFAULT 'p',
`c06` text DEFAULT NULL,
`t07` varchar(10) DEFAULT 'p',
`c07` text DEFAULT NULL,
`t08` varchar(10) DEFAULT 'p',
`c08` text DEFAULT NULL,
`t09` varchar(10) DEFAULT 'p',
`c09` text DEFAULT NULL,
`t10` varchar(10) DEFAULT 'p',
`c10` text DEFAULT NULL,
`t11` varchar(10) DEFAULT 'p',
`c11` text DEFAULT NULL,
`t12` varchar(10) DEFAULT 'p',
`c12` text DEFAULT NULL,
`t13` varchar(10) DEFAULT 'p',
`c13` text DEFAULT NULL,
`t14` varchar(10) DEFAULT 'p',
`c14` text DEFAULT NULL,
`t15` varchar(10) DEFAULT 'p',
`c15` text DEFAULT NULL,
`t16` varchar(10) DEFAULT 'p',
`c16` text DEFAULT NULL,
`t17` varchar(10) DEFAULT 'p',
`c17` text DEFAULT NULL,
`t18` varchar(10) DEFAULT 'p',
`c18` text DEFAULT NULL,
`t19` varchar(10) DEFAULT 'p',
`c19` text DEFAULT NULL,
`t20` varchar(10) DEFAULT 'p',
`c20` text DEFAULT NULL,
`t21` varchar(10) DEFAULT 'p',
`c21` text DEFAULT NULL,
`t22` varchar(10) DEFAULT 'p',
`c22` text DEFAULT NULL,
`t23` varchar(10) DEFAULT 'p',
`c23` text DEFAULT NULL,
`t24` varchar(10) DEFAULT 'p',
`c24` text DEFAULT NULL,
`t25` varchar(10) DEFAULT 'p',
`c25` text DEFAULT NULL,
`t26` varchar(10) DEFAULT 'p',
`c26` text DEFAULT NULL,
`t27` varchar(10) DEFAULT 'p',
`c27` text DEFAULT NULL,
`t28` varchar(10) DEFAULT 'p',
`c28` text DEFAULT NULL,
`t29` varchar(10) DEFAULT 'p',
`c29` text DEFAULT NULL,
`t30` varchar(10) DEFAULT 'p',
`c30` text DEFAULT NULL,
`updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
PRIMARY KEY (`id`),
UNIQUE KEY `slug` (`slug`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE IF NOT EXISTS `testimonials` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`author` varchar(100) DEFAULT NULL,
`content` text DEFAULT NULL,
`rating` tinyint(4) DEFAULT 5,
`is_active` tinyint(1) DEFAULT 1,
`store_id` int(10) unsigned DEFAULT NULL,
`created_at` datetime DEFAULT current_timestamp(),
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE IF NOT EXISTS `threat_intel_cache` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`ip_address` varchar(45) DEFAULT NULL,
`threat_score` decimal(5,2) DEFAULT 0.00,
`source` varchar(50) DEFAULT NULL,
`raw_data` text DEFAULT NULL,
`expires_at` datetime DEFAULT NULL,
`created_at` datetime DEFAULT current_timestamp(),
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE IF NOT EXISTS `ui_labels` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`label_key` varchar(100) NOT NULL,
`label_value` text DEFAULT NULL,
`locale` varchar(5) DEFAULT 'en',
PRIMARY KEY (`id`),
UNIQUE KEY `idx_key_locale` (`label_key`,`locale`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE IF NOT EXISTS `users` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`email` text NOT NULL,
`email_hash` varchar(64) DEFAULT NULL,
`username` varchar(100) DEFAULT NULL,
`password_hash` varchar(255) NOT NULL,
`name` text DEFAULT NULL,
`phone` text DEFAULT NULL,
`address` text DEFAULT NULL,
`city` text DEFAULT NULL,
`province` varchar(100) DEFAULT NULL,
`postal_code` text DEFAULT NULL,
`country` varchar(50) DEFAULT 'CA',
`credit_balance` decimal(10,2) DEFAULT 0.00,
`trust_score` int(11) DEFAULT 50,
`status` enum('active','suspended','banned','unverified','deleted') DEFAULT 'unverified',
`status_code` int(11) NOT NULL DEFAULT 1004,
`email_verified` tinyint(1) DEFAULT 0,
`verify_token` varchar(100) DEFAULT NULL,
`referral_code` varchar(20) DEFAULT NULL,
`referred_by` int(10) unsigned DEFAULT NULL,
`last_login` datetime DEFAULT NULL,
`last_ip` varchar(45) DEFAULT NULL,
`created_at` datetime DEFAULT current_timestamp(),
`is_verified` tinyint(1) DEFAULT 0,
`login_count` int(11) DEFAULT 0,
`access_tier` varchar(20) DEFAULT NULL,
`account_type` varchar(20) DEFAULT 'customer',
`admin_level` tinyint(4) DEFAULT 0,
`encrypted_email` text DEFAULT NULL,
`encrypted_username` text DEFAULT NULL,
`first_name` varchar(100) DEFAULT NULL,
`pref_a` int(11) DEFAULT 0,
`pref_b` int(11) DEFAULT 0,
`pref_c` int(11) DEFAULT 0,
`pref_d` int(11) DEFAULT 0,
`review_credits` int(11) DEFAULT 0,
PRIMARY KEY (`id`),
KEY `idx_email_hash` (`email_hash`),
KEY `idx_status_code` (`status_code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS `visitors` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`session_id` varchar(64) NOT NULL,
`ip_address` varchar(45) DEFAULT NULL,
`user_agent` text DEFAULT NULL,
`fingerprint` varchar(64) DEFAULT NULL,
`pages_viewed` int(11) DEFAULT 0,
`risk_score` int(11) DEFAULT 0,
`flags` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`flags`)),
`created_at` datetime DEFAULT current_timestamp(),
`last_seen` datetime DEFAULT current_timestamp() ON UPDATE current_timestamp(),
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS `wallet_transactions` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`wallet_id` int(10) unsigned NOT NULL,
`tx_hash` varchar(200) DEFAULT NULL,
`amount` decimal(20,8) DEFAULT NULL,
`confirmations` int(11) DEFAULT 0,
`status` enum('pending','confirmed','failed') DEFAULT 'pending',
`created_at` datetime DEFAULT current_timestamp(),
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS `wallets` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`currency` varchar(20) NOT NULL,
`address` text NOT NULL,
`label` varchar(100) DEFAULT NULL,
`is_active` tinyint(1) DEFAULT 1,
`created_at` datetime DEFAULT current_timestamp(),
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
SET FOREIGN_KEY_CHECKS=1;