1407 lines
133 KiB
SQL
1407 lines
133 KiB
SQL
-- MySQL dump 10.13 Distrib 8.2.0, for Win64 (x86_64)
|
||
--
|
||
-- Host: mysql.tonaspace.com Database: partsinquiry
|
||
-- ------------------------------------------------------
|
||
-- Server version 8.0.42
|
||
|
||
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
|
||
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
|
||
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
|
||
/*!50503 SET NAMES utf8mb4 */;
|
||
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
|
||
/*!40103 SET TIME_ZONE='+00:00' */;
|
||
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
|
||
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
|
||
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
|
||
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
|
||
|
||
--
|
||
-- Table structure for table `accounts`
|
||
--
|
||
|
||
DROP TABLE IF EXISTS `accounts`;
|
||
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
||
/*!50503 SET character_set_client = utf8mb4 */;
|
||
CREATE TABLE `accounts` (
|
||
`id` bigint unsigned NOT NULL AUTO_INCREMENT,
|
||
`shop_id` bigint unsigned NOT NULL,
|
||
`user_id` bigint unsigned NOT NULL,
|
||
`name` varchar(64) NOT NULL,
|
||
`type` enum('cash','bank','alipay','wechat','other') NOT NULL DEFAULT 'cash',
|
||
`bank_name` varchar(64) DEFAULT NULL,
|
||
`bank_account` varchar(64) DEFAULT NULL,
|
||
`balance` decimal(18,2) NOT NULL DEFAULT '0.00',
|
||
`status` tinyint unsigned NOT NULL DEFAULT '1',
|
||
`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
`updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
||
`deleted_at` datetime DEFAULT NULL,
|
||
PRIMARY KEY (`id`),
|
||
UNIQUE KEY `ux_accounts_shop_name` (`shop_id`,`name`),
|
||
KEY `idx_accounts_shop` (`shop_id`),
|
||
KEY `fk_accounts_user` (`user_id`),
|
||
CONSTRAINT `fk_accounts_shop` FOREIGN KEY (`shop_id`) REFERENCES `shops` (`id`),
|
||
CONSTRAINT `fk_accounts_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
|
||
) ENGINE=InnoDB AUTO_INCREMENT=27 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='缁撶畻璐︽埛';
|
||
/*!40101 SET character_set_client = @saved_cs_client */;
|
||
|
||
--
|
||
-- Table structure for table `admins`
|
||
--
|
||
|
||
DROP TABLE IF EXISTS `admins`;
|
||
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
||
/*!50503 SET character_set_client = utf8mb4 */;
|
||
CREATE TABLE `admins` (
|
||
`id` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT '绠＄悊鍛業D',
|
||
`username` varchar(64) NOT NULL COMMENT '鐧诲綍鍚嶏紙鎴栧睍绀哄悕锛?,
|
||
`phone` varchar(32) DEFAULT NULL COMMENT '鎵嬫満鍙凤紙鍙敤浜庣櫥褰曟垨鑱旂郴锛?,
|
||
`password_hash` varchar(255) DEFAULT NULL COMMENT '瀵嗙爜鍝堝笇锛堣嫢閲囩敤鐭俊鐧诲綍鍙负绌猴級',
|
||
`status` tinyint unsigned NOT NULL DEFAULT '1' COMMENT '鐘舵€侊細1鍚敤 0鍋滅敤',
|
||
`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
`updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
||
`deleted_at` datetime DEFAULT NULL,
|
||
PRIMARY KEY (`id`),
|
||
UNIQUE KEY `ux_admins_username` (`username`),
|
||
UNIQUE KEY `ux_admins_phone` (`phone`)
|
||
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='骞冲彴绠＄悊鍛橈紙涓嶉毝灞炲簵閾?鏃犻厤浠讹級';
|
||
/*!40101 SET character_set_client = @saved_cs_client */;
|
||
|
||
--
|
||
-- Table structure for table `attachments`
|
||
--
|
||
|
||
DROP TABLE IF EXISTS `attachments`;
|
||
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
||
/*!50503 SET character_set_client = utf8mb4 */;
|
||
CREATE TABLE `attachments` (
|
||
`id` bigint unsigned NOT NULL AUTO_INCREMENT,
|
||
`shop_id` bigint unsigned DEFAULT NULL COMMENT '鍏ㄥ眬璧勬簮鍙┖锛屾湰鍦拌祫婧愬睘浜庣鎴?,
|
||
`user_id` bigint unsigned DEFAULT NULL,
|
||
`owner_type` varchar(32) NOT NULL COMMENT '璧勬簮褰掑睘绫诲瀷锛歱roduct/part_submission/global_sku/...',
|
||
`owner_id` bigint unsigned NOT NULL,
|
||
`url` varchar(512) NOT NULL,
|
||
`hash` varchar(64) DEFAULT NULL,
|
||
`meta` json DEFAULT NULL,
|
||
`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
PRIMARY KEY (`id`),
|
||
UNIQUE KEY `ux_attachments_hash` (`hash`),
|
||
KEY `idx_attachments_owner` (`owner_type`,`owner_id`),
|
||
KEY `fk_att_shop` (`shop_id`),
|
||
KEY `fk_att_user` (`user_id`),
|
||
CONSTRAINT `fk_att_shop` FOREIGN KEY (`shop_id`) REFERENCES `shops` (`id`),
|
||
CONSTRAINT `fk_att_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
|
||
) ENGINE=InnoDB AUTO_INCREMENT=54 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='閫氱敤闄勪欢锛堝浘鐗囩瓑锛?;
|
||
/*!40101 SET character_set_client = @saved_cs_client */;
|
||
|
||
--
|
||
-- Table structure for table `consult_replies`
|
||
--
|
||
|
||
DROP TABLE IF EXISTS `consult_replies`;
|
||
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
||
/*!50503 SET character_set_client = utf8mb4 */;
|
||
CREATE TABLE `consult_replies` (
|
||
`id` bigint unsigned NOT NULL AUTO_INCREMENT,
|
||
`consult_id` bigint unsigned NOT NULL,
|
||
`user_id` bigint unsigned NOT NULL,
|
||
`content` text NOT NULL,
|
||
`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
PRIMARY KEY (`id`),
|
||
UNIQUE KEY `ux_reply_once` (`consult_id`),
|
||
KEY `idx_cr_consult` (`consult_id`),
|
||
KEY `fk_cr_user` (`user_id`),
|
||
CONSTRAINT `fk_cr_consult` FOREIGN KEY (`consult_id`) REFERENCES `consults` (`id`) ON DELETE CASCADE,
|
||
CONSTRAINT `fk_cr_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
|
||
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
|
||
/*!40101 SET character_set_client = @saved_cs_client */;
|
||
/*!50003 SET @saved_cs_client = @@character_set_client */ ;
|
||
/*!50003 SET @saved_cs_results = @@character_set_results */ ;
|
||
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
|
||
/*!50003 SET character_set_client = utf8mb4 */ ;
|
||
/*!50003 SET character_set_results = utf8mb4 */ ;
|
||
/*!50003 SET collation_connection = utf8mb4_unicode_ci */ ;
|
||
/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
|
||
/*!50003 SET sql_mode = 'IGNORE_SPACE,ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION' */ ;
|
||
DELIMITER ;;
|
||
/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`%`*/ /*!50003 TRIGGER `trg_consult_replies_ai` AFTER INSERT ON `consult_replies` FOR EACH ROW UPDATE consults SET status='resolved', updated_at=NOW() WHERE id=NEW.consult_id */;;
|
||
DELIMITER ;
|
||
/*!50003 SET sql_mode = @saved_sql_mode */ ;
|
||
/*!50003 SET character_set_client = @saved_cs_client */ ;
|
||
/*!50003 SET character_set_results = @saved_cs_results */ ;
|
||
/*!50003 SET collation_connection = @saved_col_connection */ ;
|
||
|
||
--
|
||
-- Table structure for table `consults`
|
||
--
|
||
|
||
DROP TABLE IF EXISTS `consults`;
|
||
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
||
/*!50503 SET character_set_client = utf8mb4 */;
|
||
CREATE TABLE `consults` (
|
||
`id` bigint unsigned NOT NULL AUTO_INCREMENT,
|
||
`shop_id` bigint unsigned NOT NULL,
|
||
`user_id` bigint unsigned NOT NULL,
|
||
`topic` varchar(120) NOT NULL,
|
||
`message` text NOT NULL,
|
||
`status` enum('open','resolved','closed') NOT NULL DEFAULT 'open',
|
||
`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
`updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
||
PRIMARY KEY (`id`),
|
||
KEY `idx_consult_shop_status` (`shop_id`,`status`),
|
||
KEY `fk_consult_user` (`user_id`),
|
||
CONSTRAINT `fk_consult_shop` FOREIGN KEY (`shop_id`) REFERENCES `shops` (`id`),
|
||
CONSTRAINT `fk_consult_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
|
||
) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
|
||
/*!40101 SET character_set_client = @saved_cs_client */;
|
||
|
||
--
|
||
-- Table structure for table `customers`
|
||
--
|
||
|
||
DROP TABLE IF EXISTS `customers`;
|
||
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
||
/*!50503 SET character_set_client = utf8mb4 */;
|
||
CREATE TABLE `customers` (
|
||
`id` bigint unsigned NOT NULL AUTO_INCREMENT,
|
||
`shop_id` bigint unsigned NOT NULL,
|
||
`user_id` bigint unsigned NOT NULL,
|
||
`name` varchar(120) NOT NULL,
|
||
`phone` varchar(32) DEFAULT NULL,
|
||
`address` varchar(255) DEFAULT NULL,
|
||
`mobile` varchar(32) DEFAULT NULL,
|
||
`contact_name` varchar(64) DEFAULT NULL,
|
||
`price_level` enum('闆跺敭浠?,'鎵瑰彂浠?,'澶у崟鎶ヤ环') NOT NULL DEFAULT '闆跺敭浠? COMMENT '榛樿鍞环鍒楋細闆跺敭浠?鎵瑰彂浠?澶у崟鎶ヤ环',
|
||
`status` tinyint unsigned NOT NULL DEFAULT '1',
|
||
`ar_opening` decimal(18,2) NOT NULL DEFAULT '0.00',
|
||
`remark` varchar(255) DEFAULT NULL,
|
||
`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
`updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
||
`deleted_at` datetime DEFAULT NULL,
|
||
PRIMARY KEY (`id`),
|
||
KEY `idx_customers_shop` (`shop_id`),
|
||
KEY `idx_customers_phone` (`phone`),
|
||
KEY `fk_customers_user` (`user_id`),
|
||
KEY `idx_customers_mobile` (`mobile`),
|
||
CONSTRAINT `fk_customers_shop` FOREIGN KEY (`shop_id`) REFERENCES `shops` (`id`),
|
||
CONSTRAINT `fk_customers_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
|
||
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='瀹㈡埛';
|
||
/*!40101 SET character_set_client = @saved_cs_client */;
|
||
|
||
--
|
||
-- Table structure for table `email_codes`
|
||
--
|
||
|
||
DROP TABLE IF EXISTS `email_codes`;
|
||
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
||
/*!50503 SET character_set_client = utf8mb4 */;
|
||
CREATE TABLE `email_codes` (
|
||
`id` bigint unsigned NOT NULL AUTO_INCREMENT,
|
||
`email` varchar(128) NOT NULL,
|
||
`scene` varchar(32) NOT NULL,
|
||
`code_hash` varchar(64) NOT NULL,
|
||
`salt` varchar(64) NOT NULL,
|
||
`expire_at` datetime NOT NULL,
|
||
`status` tinyint unsigned NOT NULL DEFAULT '0' COMMENT '0=unused,1=used,2=expired',
|
||
`fail_count` int NOT NULL DEFAULT '0',
|
||
`ip` varchar(64) DEFAULT NULL,
|
||
`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
`updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
||
PRIMARY KEY (`id`),
|
||
KEY `idx_email_scene_created` (`email`,`scene`,`created_at`),
|
||
KEY `idx_email_expire` (`expire_at`)
|
||
) ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='閭楠岃瘉鐮佽褰?;
|
||
/*!40101 SET character_set_client = @saved_cs_client */;
|
||
|
||
--
|
||
-- Table structure for table `finance_categories`
|
||
--
|
||
|
||
DROP TABLE IF EXISTS `finance_categories`;
|
||
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
||
/*!50503 SET character_set_client = utf8mb4 */;
|
||
CREATE TABLE `finance_categories` (
|
||
`id` bigint unsigned NOT NULL AUTO_INCREMENT,
|
||
`shop_id` bigint unsigned NOT NULL,
|
||
`type` enum('income','expense') NOT NULL,
|
||
`key` varchar(64) NOT NULL,
|
||
`label` varchar(64) NOT NULL,
|
||
`sort_order` int NOT NULL DEFAULT '0',
|
||
`status` tinyint unsigned NOT NULL DEFAULT '1',
|
||
`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
`updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
||
PRIMARY KEY (`id`),
|
||
UNIQUE KEY `ux_finance_cat` (`shop_id`,`type`,`key`),
|
||
KEY `idx_finance_cat_shop_type` (`shop_id`,`type`),
|
||
CONSTRAINT `fk_finance_cat_shop` FOREIGN KEY (`shop_id`) REFERENCES `shops` (`id`)
|
||
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='璐㈠姟鍒嗙被瀛楀吀';
|
||
/*!40101 SET character_set_client = @saved_cs_client */;
|
||
|
||
--
|
||
-- Table structure for table `global_skus`
|
||
--
|
||
|
||
DROP TABLE IF EXISTS `global_skus`;
|
||
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
||
/*!50503 SET character_set_client = utf8mb4 */;
|
||
CREATE TABLE `global_skus` (
|
||
`id` bigint unsigned NOT NULL AUTO_INCREMENT,
|
||
`name` varchar(120) NOT NULL COMMENT 'SKU鍚嶇О',
|
||
`brand` varchar(64) DEFAULT NULL,
|
||
`model` varchar(64) DEFAULT NULL,
|
||
`spec` varchar(128) DEFAULT NULL,
|
||
`barcode` varchar(32) DEFAULT NULL,
|
||
`unit_id` bigint unsigned DEFAULT NULL,
|
||
`tags` json DEFAULT NULL,
|
||
`status` enum('published','offline') NOT NULL DEFAULT 'published',
|
||
`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
`updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
||
`deleted_at` datetime DEFAULT NULL,
|
||
PRIMARY KEY (`id`),
|
||
UNIQUE KEY `ux_global_skus_barcode` (`barcode`),
|
||
KEY `idx_global_skus_brand_model` (`brand`,`model`),
|
||
KEY `fk_globalsku_unit` (`unit_id`),
|
||
CONSTRAINT `fk_globalsku_unit` FOREIGN KEY (`unit_id`) REFERENCES `product_units` (`id`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='鍏ㄥ眬SKU锛堜紬鍖咃級';
|
||
/*!40101 SET character_set_client = @saved_cs_client */;
|
||
|
||
--
|
||
-- Table structure for table `inventories`
|
||
--
|
||
|
||
DROP TABLE IF EXISTS `inventories`;
|
||
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
||
/*!50503 SET character_set_client = utf8mb4 */;
|
||
CREATE TABLE `inventories` (
|
||
`product_id` bigint unsigned NOT NULL,
|
||
`shop_id` bigint unsigned NOT NULL,
|
||
`user_id` bigint unsigned NOT NULL,
|
||
`quantity` decimal(18,3) NOT NULL DEFAULT '0.000',
|
||
`updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
||
PRIMARY KEY (`product_id`),
|
||
KEY `idx_inventories_shop` (`shop_id`),
|
||
KEY `fk_inv_user` (`user_id`),
|
||
CONSTRAINT `fk_inv_product` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`) ON DELETE CASCADE,
|
||
CONSTRAINT `fk_inv_shop` FOREIGN KEY (`shop_id`) REFERENCES `shops` (`id`),
|
||
CONSTRAINT `fk_inv_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`),
|
||
CONSTRAINT `ck_inv_qty_non_negative` CHECK ((`quantity` >= 0))
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='搴撳瓨';
|
||
/*!40101 SET character_set_client = @saved_cs_client */;
|
||
|
||
--
|
||
-- Table structure for table `inventory_movements`
|
||
--
|
||
|
||
DROP TABLE IF EXISTS `inventory_movements`;
|
||
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
||
/*!50503 SET character_set_client = utf8mb4 */;
|
||
CREATE TABLE `inventory_movements` (
|
||
`id` bigint unsigned NOT NULL AUTO_INCREMENT,
|
||
`shop_id` bigint unsigned NOT NULL,
|
||
`user_id` bigint unsigned NOT NULL,
|
||
`product_id` bigint unsigned NOT NULL,
|
||
`source_type` varchar(32) NOT NULL COMMENT '鏉ユ簮锛歴ale/purchase/adjust/audit/...',
|
||
`source_id` bigint unsigned DEFAULT NULL COMMENT '鏉ユ簮琛↖D锛堝彲绌猴級',
|
||
`qty_delta` decimal(18,3) NOT NULL COMMENT '鏁伴噺澧炲噺锛屽嚭搴撲负璐燂紝鍏ュ簱涓烘',
|
||
`amount_delta` decimal(18,2) DEFAULT NULL COMMENT '閲戦澧炲噺锛堝彲閫夛級',
|
||
`cost_price` decimal(18,2) DEFAULT NULL,
|
||
`cost_amount` decimal(18,2) DEFAULT NULL,
|
||
`reason` varchar(64) DEFAULT NULL COMMENT '鍘熷洜/绫诲埆',
|
||
`tx_time` datetime NOT NULL,
|
||
`remark` varchar(255) DEFAULT NULL,
|
||
`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
PRIMARY KEY (`id`),
|
||
KEY `idx_im_shop_time` (`shop_id`,`tx_time`),
|
||
KEY `idx_im_product` (`product_id`),
|
||
KEY `fk_im_user` (`user_id`),
|
||
CONSTRAINT `fk_im_product` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`),
|
||
CONSTRAINT `fk_im_shop` FOREIGN KEY (`shop_id`) REFERENCES `shops` (`id`),
|
||
CONSTRAINT `fk_im_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
|
||
) ENGINE=InnoDB AUTO_INCREMENT=73 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='搴撳瓨娴佹按';
|
||
/*!40101 SET character_set_client = @saved_cs_client */;
|
||
|
||
--
|
||
-- Table structure for table `normal_admin_audits`
|
||
--
|
||
|
||
DROP TABLE IF EXISTS `normal_admin_audits`;
|
||
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
||
/*!50503 SET character_set_client = utf8mb4 */;
|
||
CREATE TABLE `normal_admin_audits` (
|
||
`id` bigint unsigned NOT NULL AUTO_INCREMENT,
|
||
`shop_id` bigint unsigned NOT NULL,
|
||
`user_id` bigint unsigned NOT NULL,
|
||
`action` enum('apply','approve','reject','revoke','expire') NOT NULL,
|
||
`remark` varchar(255) DEFAULT NULL,
|
||
`operator_admin_id` bigint unsigned DEFAULT NULL COMMENT '骞冲彴绠＄悊鍛業D锛坅pply鏃跺彲绌猴級',
|
||
`previous_role` varchar(32) DEFAULT NULL,
|
||
`new_role` varchar(32) DEFAULT NULL,
|
||
`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
PRIMARY KEY (`id`),
|
||
KEY `idx_naudit_shop_time` (`shop_id`,`created_at`),
|
||
KEY `idx_naudit_user_time` (`user_id`,`created_at`),
|
||
KEY `fk_naudit_admin` (`operator_admin_id`),
|
||
CONSTRAINT `fk_naudit_admin` FOREIGN KEY (`operator_admin_id`) REFERENCES `admins` (`id`),
|
||
CONSTRAINT `fk_naudit_shop` FOREIGN KEY (`shop_id`) REFERENCES `shops` (`id`),
|
||
CONSTRAINT `fk_naudit_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
|
||
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='鏅€氱鐞嗗憳鐢宠/瀹℃壒瀹¤鏃ュ織';
|
||
/*!40101 SET character_set_client = @saved_cs_client */;
|
||
|
||
--
|
||
-- Table structure for table `notices`
|
||
--
|
||
|
||
DROP TABLE IF EXISTS `notices`;
|
||
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
||
/*!50503 SET character_set_client = utf8mb4 */;
|
||
CREATE TABLE `notices` (
|
||
`id` bigint unsigned NOT NULL AUTO_INCREMENT,
|
||
`title` varchar(120) NOT NULL,
|
||
`content` varchar(500) NOT NULL,
|
||
`tag` varchar(32) DEFAULT NULL,
|
||
`is_pinned` tinyint(1) NOT NULL DEFAULT '0',
|
||
`starts_at` datetime DEFAULT NULL,
|
||
`ends_at` datetime DEFAULT NULL,
|
||
`status` enum('draft','published','offline') NOT NULL DEFAULT 'published',
|
||
`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
`updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
||
`deleted_at` datetime DEFAULT NULL,
|
||
PRIMARY KEY (`id`),
|
||
KEY `idx_notices_time` (`starts_at`,`ends_at`)
|
||
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='鍏憡';
|
||
/*!40101 SET character_set_client = @saved_cs_client */;
|
||
|
||
--
|
||
-- Table structure for table `other_transactions`
|
||
--
|
||
|
||
DROP TABLE IF EXISTS `other_transactions`;
|
||
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
||
/*!50503 SET character_set_client = utf8mb4 */;
|
||
CREATE TABLE `other_transactions` (
|
||
`id` bigint unsigned NOT NULL AUTO_INCREMENT,
|
||
`shop_id` bigint unsigned NOT NULL,
|
||
`user_id` bigint unsigned NOT NULL,
|
||
`type` enum('income','expense') NOT NULL,
|
||
`category` varchar(64) NOT NULL,
|
||
`counterparty_type` varchar(32) DEFAULT NULL COMMENT 'customer/supplier/other',
|
||
`counterparty_id` bigint unsigned DEFAULT NULL,
|
||
`account_id` bigint unsigned NOT NULL,
|
||
`amount` decimal(18,2) NOT NULL,
|
||
`tx_time` datetime NOT NULL,
|
||
`remark` varchar(255) DEFAULT NULL,
|
||
`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
`updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
||
`deleted_at` datetime DEFAULT NULL,
|
||
PRIMARY KEY (`id`),
|
||
KEY `idx_ot_shop_time` (`shop_id`,`tx_time`),
|
||
KEY `idx_ot_account` (`account_id`),
|
||
KEY `fk_ot_user` (`user_id`),
|
||
CONSTRAINT `fk_ot_account` FOREIGN KEY (`account_id`) REFERENCES `accounts` (`id`),
|
||
CONSTRAINT `fk_ot_shop` FOREIGN KEY (`shop_id`) REFERENCES `shops` (`id`),
|
||
CONSTRAINT `fk_ot_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`),
|
||
CONSTRAINT `ck_ot_amount` CHECK ((`amount` > 0))
|
||
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='鍏朵粬鏀跺叆/鏀嚭';
|
||
/*!40101 SET character_set_client = @saved_cs_client */;
|
||
|
||
--
|
||
-- Table structure for table `part_attribute_dictionary`
|
||
--
|
||
|
||
DROP TABLE IF EXISTS `part_attribute_dictionary`;
|
||
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
||
/*!50503 SET character_set_client = utf8mb4 */;
|
||
CREATE TABLE `part_attribute_dictionary` (
|
||
`id` bigint unsigned NOT NULL AUTO_INCREMENT,
|
||
`category_id` bigint unsigned NOT NULL,
|
||
`name` varchar(120) NOT NULL,
|
||
`model` varchar(128) DEFAULT NULL,
|
||
`attributes` json DEFAULT NULL,
|
||
`sort_order` int NOT NULL DEFAULT '0',
|
||
`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
`updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
||
PRIMARY KEY (`id`),
|
||
KEY `fk_attr_dict_cat` (`category_id`),
|
||
CONSTRAINT `fk_attr_dict_cat` FOREIGN KEY (`category_id`) REFERENCES `part_categories` (`id`) ON DELETE CASCADE
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
|
||
/*!40101 SET character_set_client = @saved_cs_client */;
|
||
|
||
--
|
||
-- Table structure for table `part_attribute_templates`
|
||
--
|
||
|
||
DROP TABLE IF EXISTS `part_attribute_templates`;
|
||
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
||
/*!50503 SET character_set_client = utf8mb4 */;
|
||
CREATE TABLE `part_attribute_templates` (
|
||
`id` bigint unsigned NOT NULL AUTO_INCREMENT,
|
||
`category_id` bigint unsigned NOT NULL,
|
||
`field_key` varchar(64) NOT NULL,
|
||
`display_name` varchar(64) NOT NULL,
|
||
`data_type` enum('text','number','integer','bool','select') NOT NULL DEFAULT 'text',
|
||
`unit` varchar(32) DEFAULT NULL,
|
||
`options` json DEFAULT NULL,
|
||
`required` tinyint(1) NOT NULL DEFAULT '0',
|
||
`sort_order` int NOT NULL DEFAULT '0',
|
||
`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
`updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
||
PRIMARY KEY (`id`),
|
||
UNIQUE KEY `ux_cat_field` (`category_id`,`field_key`),
|
||
CONSTRAINT `fk_attr_tpl_category` FOREIGN KEY (`category_id`) REFERENCES `part_categories` (`id`) ON DELETE CASCADE
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
|
||
/*!40101 SET character_set_client = @saved_cs_client */;
|
||
|
||
--
|
||
-- Table structure for table `part_categories`
|
||
--
|
||
|
||
DROP TABLE IF EXISTS `part_categories`;
|
||
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
||
/*!50503 SET character_set_client = utf8mb4 */;
|
||
CREATE TABLE `part_categories` (
|
||
`id` bigint unsigned NOT NULL AUTO_INCREMENT,
|
||
`parent_id` bigint unsigned DEFAULT NULL,
|
||
`name` varchar(64) NOT NULL,
|
||
`sort_order` int NOT NULL DEFAULT '0',
|
||
`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
`updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
||
PRIMARY KEY (`id`),
|
||
KEY `idx_parent` (`parent_id`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='閰嶄欢鍒嗙被';
|
||
/*!40101 SET character_set_client = @saved_cs_client */;
|
||
|
||
--
|
||
-- Table structure for table `part_category_attributes`
|
||
--
|
||
|
||
DROP TABLE IF EXISTS `part_category_attributes`;
|
||
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
||
/*!50503 SET character_set_client = utf8mb4 */;
|
||
CREATE TABLE `part_category_attributes` (
|
||
`id` bigint unsigned NOT NULL AUTO_INCREMENT,
|
||
`category_id` bigint unsigned NOT NULL,
|
||
`key` varchar(64) NOT NULL,
|
||
`label` varchar(64) NOT NULL,
|
||
`type` enum('text','number','bool','enum','length') NOT NULL DEFAULT 'text',
|
||
`unit` varchar(16) DEFAULT NULL,
|
||
`required` tinyint(1) NOT NULL DEFAULT '0',
|
||
`sort_order` int NOT NULL DEFAULT '0',
|
||
`options` json DEFAULT NULL,
|
||
`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
`updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
||
PRIMARY KEY (`id`),
|
||
UNIQUE KEY `ux_cat_key` (`category_id`,`key`),
|
||
KEY `idx_cat` (`category_id`),
|
||
CONSTRAINT `fk_pca_category` FOREIGN KEY (`category_id`) REFERENCES `part_categories` (`id`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='鍒嗙被鍙傛暟瀹氫箟';
|
||
/*!40101 SET character_set_client = @saved_cs_client */;
|
||
|
||
--
|
||
-- Table structure for table `part_submissions`
|
||
--
|
||
|
||
DROP TABLE IF EXISTS `part_submissions`;
|
||
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
||
/*!50503 SET character_set_client = utf8mb4 */;
|
||
CREATE TABLE `part_submissions` (
|
||
`id` bigint unsigned NOT NULL AUTO_INCREMENT,
|
||
`shop_id` bigint unsigned NOT NULL,
|
||
`user_id` bigint unsigned NOT NULL,
|
||
`name` varchar(120) DEFAULT NULL,
|
||
`external_code` varchar(64) DEFAULT NULL,
|
||
`model_unique` varchar(128) NOT NULL COMMENT '鍨嬪彿锛堝敮涓€锛?,
|
||
`brand` varchar(64) DEFAULT NULL,
|
||
`spec` varchar(128) DEFAULT NULL,
|
||
`unit_id` bigint unsigned DEFAULT NULL,
|
||
`category_id` bigint unsigned DEFAULT NULL,
|
||
`template_id` bigint unsigned DEFAULT NULL,
|
||
`tags` json DEFAULT NULL,
|
||
`attributes` json DEFAULT NULL,
|
||
`images` json DEFAULT NULL,
|
||
`size` varchar(64) DEFAULT NULL,
|
||
`aperture` varchar(64) DEFAULT NULL,
|
||
`compatible` text COMMENT '閫傞厤淇℃伅',
|
||
`barcode` varchar(64) DEFAULT NULL,
|
||
`dedupe_key` varchar(512) DEFAULT NULL,
|
||
`remark` text,
|
||
`status` enum('pending','approved','rejected') NOT NULL DEFAULT 'pending',
|
||
`reviewer_id` bigint unsigned DEFAULT NULL,
|
||
`product_id` bigint unsigned DEFAULT NULL,
|
||
`global_sku_id` bigint unsigned DEFAULT NULL,
|
||
`reviewed_at` datetime DEFAULT NULL,
|
||
`review_remark` varchar(255) DEFAULT NULL,
|
||
`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
`updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
||
`deleted_at` datetime DEFAULT NULL,
|
||
PRIMARY KEY (`id`),
|
||
UNIQUE KEY `ux_part_model_unique` (`model_unique`),
|
||
UNIQUE KEY `ux_ps_template_name_model` (`template_id`,`name`,`model_unique`),
|
||
KEY `idx_part_submissions_shop` (`shop_id`),
|
||
KEY `fk_part_user` (`user_id`),
|
||
KEY `idx_part_status` (`status`),
|
||
KEY `idx_part_brand_model` (`brand`,`model_unique`),
|
||
KEY `idx_sub_status_updated` (`status`,`updated_at`),
|
||
KEY `idx_sub_shop_status` (`shop_id`,`status`,`updated_at`),
|
||
KEY `idx_part_submissions_reviewer` (`reviewer_id`),
|
||
KEY `idx_part_submissions_created` (`created_at`),
|
||
KEY `fk_part_submission_product` (`product_id`),
|
||
KEY `fk_part_submission_global_sku` (`global_sku_id`),
|
||
KEY `idx_ps_template` (`template_id`),
|
||
KEY `idx_ps_dedupe` (`dedupe_key`),
|
||
CONSTRAINT `fk_part_shop` FOREIGN KEY (`shop_id`) REFERENCES `shops` (`id`),
|
||
CONSTRAINT `fk_part_submission_global_sku` FOREIGN KEY (`global_sku_id`) REFERENCES `global_skus` (`id`),
|
||
CONSTRAINT `fk_part_submission_product` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`),
|
||
CONSTRAINT `fk_part_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`),
|
||
CONSTRAINT `fk_ps_template` FOREIGN KEY (`template_id`) REFERENCES `part_templates` (`id`)
|
||
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='閰嶄欢鏁版嵁鎻愪氦锛堝鏍革級';
|
||
/*!40101 SET character_set_client = @saved_cs_client */;
|
||
|
||
--
|
||
-- Table structure for table `part_template_params`
|
||
--
|
||
|
||
DROP TABLE IF EXISTS `part_template_params`;
|
||
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
||
/*!50503 SET character_set_client = utf8mb4 */;
|
||
CREATE TABLE `part_template_params` (
|
||
`id` bigint unsigned NOT NULL AUTO_INCREMENT,
|
||
`template_id` bigint unsigned NOT NULL,
|
||
`field_key` varchar(64) NOT NULL COMMENT '鍙傛暟閿紙鑻辨枃瀛楁瘝/涓嬪垝绾匡級',
|
||
`field_label` varchar(120) NOT NULL COMMENT '鍙傛暟鍚嶏紙灞曠ず锛?,
|
||
`type` enum('string','number','boolean','enum','date') NOT NULL,
|
||
`required` tinyint(1) NOT NULL DEFAULT '0',
|
||
`unit` varchar(32) DEFAULT NULL COMMENT '鍗曚綅锛堢鐞嗗憳鑷畾涔夋枃鏈級',
|
||
`enum_options` json DEFAULT NULL COMMENT '鏋氫妇鍙€夐」锛屼粎type=enum浣跨敤',
|
||
`searchable` tinyint(1) NOT NULL DEFAULT '0' COMMENT '鏄惁鍙備笌妫€绱?,
|
||
`fuzzy_searchable` tinyint(1) NOT NULL DEFAULT '0' COMMENT '鏄惁鍏佽妯＄硦鏌ヨ锛堜粎鏁板€煎瀷锛?,
|
||
`fuzzy_tolerance` decimal(18,6) DEFAULT NULL COMMENT '瀹瑰樊锛汵ULL 琛ㄧず浣跨敤骞冲彴榛樿瀹瑰樊',
|
||
`card_display` tinyint(1) NOT NULL DEFAULT '0' COMMENT '鏄惁鍦ㄥ晢鍝佸崱鐗囧睍绀猴紙鏈€澶氬洓椤癸級',
|
||
`dedupe_participate` tinyint(1) NOT NULL DEFAULT '0' COMMENT '鏄惁鍙備笌鍘婚噸閿?,
|
||
`sort_order` int NOT NULL DEFAULT '0',
|
||
`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
`updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
||
PRIMARY KEY (`id`),
|
||
UNIQUE KEY `ux_ptp_field_key` (`template_id`,`field_key`),
|
||
KEY `idx_ptp_template` (`template_id`),
|
||
KEY `idx_ptp_sort` (`template_id`,`sort_order`),
|
||
CONSTRAINT `fk_ptp_template` FOREIGN KEY (`template_id`) REFERENCES `part_templates` (`id`) ON DELETE CASCADE
|
||
) ENGINE=InnoDB AUTO_INCREMENT=31 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
|
||
/*!40101 SET character_set_client = @saved_cs_client */;
|
||
|
||
--
|
||
-- Table structure for table `part_templates`
|
||
--
|
||
|
||
DROP TABLE IF EXISTS `part_templates`;
|
||
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
||
/*!50503 SET character_set_client = utf8mb4 */;
|
||
CREATE TABLE `part_templates` (
|
||
`id` bigint unsigned NOT NULL AUTO_INCREMENT,
|
||
`category_id` bigint unsigned NOT NULL,
|
||
`name` varchar(120) NOT NULL COMMENT '閰嶄欢鍚?,
|
||
`model_rule` varchar(255) DEFAULT NULL COMMENT '鍨嬪彿瑙勫垯锛堝彲閫夛紝姝ｅ垯鎴栬鏄庯級',
|
||
`status` tinyint unsigned NOT NULL DEFAULT '1' COMMENT '1鍚敤 0鍋滅敤',
|
||
`created_by_admin_id` bigint unsigned DEFAULT NULL,
|
||
`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
`updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
||
`deleted_at` datetime DEFAULT NULL,
|
||
PRIMARY KEY (`id`),
|
||
KEY `idx_pt_category` (`category_id`),
|
||
KEY `idx_pt_status` (`status`),
|
||
KEY `idx_pt_admin` (`created_by_admin_id`),
|
||
KEY `idx_pt_deleted` (`deleted_at`),
|
||
KEY `idx_part_templates_deleted_at` (`deleted_at`),
|
||
CONSTRAINT `fk_pt_admin` FOREIGN KEY (`created_by_admin_id`) REFERENCES `admins` (`id`),
|
||
CONSTRAINT `fk_pt_category` FOREIGN KEY (`category_id`) REFERENCES `product_categories` (`id`)
|
||
) ENGINE=InnoDB AUTO_INCREMENT=29 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
|
||
/*!40101 SET character_set_client = @saved_cs_client */;
|
||
|
||
--
|
||
-- Table structure for table `payments`
|
||
--
|
||
|
||
DROP TABLE IF EXISTS `payments`;
|
||
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
||
/*!50503 SET character_set_client = utf8mb4 */;
|
||
CREATE TABLE `payments` (
|
||
`id` bigint unsigned NOT NULL AUTO_INCREMENT,
|
||
`shop_id` bigint unsigned NOT NULL,
|
||
`user_id` bigint unsigned NOT NULL,
|
||
`biz_type` enum('sale','purchase','other') NOT NULL,
|
||
`biz_id` bigint unsigned DEFAULT NULL COMMENT '涓氬姟琛↖D锛歴ales_orders/purchase_orders/other_transactions',
|
||
`account_id` bigint unsigned NOT NULL,
|
||
`direction` enum('in','out') NOT NULL COMMENT '鏀舵/浠樻',
|
||
`amount` decimal(18,2) NOT NULL,
|
||
`pay_time` datetime NOT NULL,
|
||
`remark` varchar(255) DEFAULT NULL,
|
||
`category` varchar(64) DEFAULT NULL,
|
||
`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
PRIMARY KEY (`id`),
|
||
KEY `idx_payments_shop_time` (`shop_id`,`pay_time`),
|
||
KEY `idx_payments_biz` (`biz_type`,`biz_id`),
|
||
KEY `fk_payments_user` (`user_id`),
|
||
KEY `fk_payments_account` (`account_id`),
|
||
CONSTRAINT `fk_payments_account` FOREIGN KEY (`account_id`) REFERENCES `accounts` (`id`),
|
||
CONSTRAINT `fk_payments_shop` FOREIGN KEY (`shop_id`) REFERENCES `shops` (`id`),
|
||
CONSTRAINT `fk_payments_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`),
|
||
CONSTRAINT `ck_payments_amount` CHECK ((`amount` > 0))
|
||
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='鏀朵粯娆捐褰?;
|
||
/*!40101 SET character_set_client = @saved_cs_client */;
|
||
|
||
--
|
||
-- Table structure for table `product_aliases`
|
||
--
|
||
|
||
DROP TABLE IF EXISTS `product_aliases`;
|
||
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
||
/*!50503 SET character_set_client = utf8mb4 */;
|
||
CREATE TABLE `product_aliases` (
|
||
`id` bigint unsigned NOT NULL AUTO_INCREMENT,
|
||
`shop_id` bigint unsigned NOT NULL,
|
||
`user_id` bigint unsigned NOT NULL,
|
||
`product_id` bigint unsigned NOT NULL,
|
||
`alias` varchar(120) NOT NULL,
|
||
`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
`updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
||
`deleted_at` datetime DEFAULT NULL,
|
||
PRIMARY KEY (`id`),
|
||
UNIQUE KEY `ux_product_alias` (`product_id`,`alias`),
|
||
KEY `idx_product_alias_product` (`product_id`),
|
||
KEY `fk_alias_shop` (`shop_id`),
|
||
KEY `fk_alias_user` (`user_id`),
|
||
CONSTRAINT `fk_alias_product` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`),
|
||
CONSTRAINT `fk_alias_shop` FOREIGN KEY (`shop_id`) REFERENCES `shops` (`id`),
|
||
CONSTRAINT `fk_alias_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='鍟嗗搧鍒悕';
|
||
/*!40101 SET character_set_client = @saved_cs_client */;
|
||
/*!50003 SET @saved_cs_client = @@character_set_client */ ;
|
||
/*!50003 SET @saved_cs_results = @@character_set_results */ ;
|
||
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
|
||
/*!50003 SET character_set_client = utf8mb4 */ ;
|
||
/*!50003 SET character_set_results = utf8mb4 */ ;
|
||
/*!50003 SET collation_connection = utf8mb4_0900_ai_ci */ ;
|
||
/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
|
||
/*!50003 SET sql_mode = 'STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION' */ ;
|
||
DELIMITER ;;
|
||
/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`%`*/ /*!50003 TRIGGER `trg_palias_ai` AFTER INSERT ON `product_aliases` FOR EACH ROW UPDATE products p JOIN (SELECT pa.product_id, GROUP_CONCAT(pa.alias SEPARATOR ' ') AS aliases FROM product_aliases pa WHERE pa.product_id = NEW.product_id AND pa.deleted_at IS NULL GROUP BY pa.product_id) a ON a.product_id = p.id SET p.search_text = CONCAT_WS(' ', p.name, p.brand, p.model, p.spec, a.aliases) WHERE p.id = NEW.product_id */;;
|
||
DELIMITER ;
|
||
/*!50003 SET sql_mode = @saved_sql_mode */ ;
|
||
/*!50003 SET character_set_client = @saved_cs_client */ ;
|
||
/*!50003 SET character_set_results = @saved_cs_results */ ;
|
||
/*!50003 SET collation_connection = @saved_col_connection */ ;
|
||
/*!50003 SET @saved_cs_client = @@character_set_client */ ;
|
||
/*!50003 SET @saved_cs_results = @@character_set_results */ ;
|
||
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
|
||
/*!50003 SET character_set_client = utf8mb4 */ ;
|
||
/*!50003 SET character_set_results = utf8mb4 */ ;
|
||
/*!50003 SET collation_connection = utf8mb4_0900_ai_ci */ ;
|
||
/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
|
||
/*!50003 SET sql_mode = 'STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION' */ ;
|
||
DELIMITER ;;
|
||
/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`%`*/ /*!50003 TRIGGER `trg_palias_au` AFTER UPDATE ON `product_aliases` FOR EACH ROW UPDATE products p JOIN (SELECT pa.product_id, GROUP_CONCAT(pa.alias SEPARATOR ' ') AS aliases FROM product_aliases pa WHERE pa.product_id = NEW.product_id AND pa.deleted_at IS NULL GROUP BY pa.product_id) a ON a.product_id = p.id SET p.search_text = CONCAT_WS(' ', p.name, p.brand, p.model, p.spec, a.aliases) WHERE p.id = NEW.product_id */;;
|
||
DELIMITER ;
|
||
/*!50003 SET sql_mode = @saved_sql_mode */ ;
|
||
/*!50003 SET character_set_client = @saved_cs_client */ ;
|
||
/*!50003 SET character_set_results = @saved_cs_results */ ;
|
||
/*!50003 SET collation_connection = @saved_col_connection */ ;
|
||
/*!50003 SET @saved_cs_client = @@character_set_client */ ;
|
||
/*!50003 SET @saved_cs_results = @@character_set_results */ ;
|
||
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
|
||
/*!50003 SET character_set_client = utf8mb4 */ ;
|
||
/*!50003 SET character_set_results = utf8mb4 */ ;
|
||
/*!50003 SET collation_connection = utf8mb4_0900_ai_ci */ ;
|
||
/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
|
||
/*!50003 SET sql_mode = 'STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION' */ ;
|
||
DELIMITER ;;
|
||
/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`%`*/ /*!50003 TRIGGER `trg_palias_ad` AFTER DELETE ON `product_aliases` FOR EACH ROW UPDATE products p LEFT JOIN (SELECT pa.product_id, GROUP_CONCAT(pa.alias SEPARATOR ' ') AS aliases FROM product_aliases pa WHERE pa.product_id = OLD.product_id AND pa.deleted_at IS NULL GROUP BY pa.product_id) a ON a.product_id = p.id SET p.search_text = CONCAT_WS(' ', p.name, p.brand, p.model, p.spec, COALESCE(a.aliases, '')) WHERE p.id = OLD.product_id */;;
|
||
DELIMITER ;
|
||
/*!50003 SET sql_mode = @saved_sql_mode */ ;
|
||
/*!50003 SET character_set_client = @saved_cs_client */ ;
|
||
/*!50003 SET character_set_results = @saved_cs_results */ ;
|
||
/*!50003 SET collation_connection = @saved_col_connection */ ;
|
||
|
||
--
|
||
-- Table structure for table `product_categories`
|
||
--
|
||
|
||
DROP TABLE IF EXISTS `product_categories`;
|
||
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
||
/*!50503 SET character_set_client = utf8mb4 */;
|
||
CREATE TABLE `product_categories` (
|
||
`id` bigint unsigned NOT NULL AUTO_INCREMENT,
|
||
`shop_id` bigint unsigned NOT NULL,
|
||
`user_id` bigint unsigned NOT NULL,
|
||
`name` varchar(64) NOT NULL,
|
||
`parent_id` bigint unsigned DEFAULT NULL,
|
||
`sort_order` int NOT NULL DEFAULT '0',
|
||
`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
`updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
||
`deleted_at` datetime DEFAULT NULL,
|
||
PRIMARY KEY (`id`),
|
||
UNIQUE KEY `ux_categories_shop_name` (`shop_id`,`name`),
|
||
KEY `idx_categories_shop` (`shop_id`),
|
||
KEY `idx_categories_parent` (`parent_id`),
|
||
KEY `fk_categories_user` (`user_id`),
|
||
CONSTRAINT `fk_categories_parent` FOREIGN KEY (`parent_id`) REFERENCES `product_categories` (`id`),
|
||
CONSTRAINT `fk_categories_shop` FOREIGN KEY (`shop_id`) REFERENCES `shops` (`id`),
|
||
CONSTRAINT `fk_categories_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
|
||
) ENGINE=InnoDB AUTO_INCREMENT=56 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='鍟嗗搧绫诲埆';
|
||
/*!40101 SET character_set_client = @saved_cs_client */;
|
||
|
||
--
|
||
-- Table structure for table `product_images`
|
||
--
|
||
|
||
DROP TABLE IF EXISTS `product_images`;
|
||
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
||
/*!50503 SET character_set_client = utf8mb4 */;
|
||
CREATE TABLE `product_images` (
|
||
`id` bigint unsigned NOT NULL AUTO_INCREMENT,
|
||
`shop_id` bigint unsigned NOT NULL,
|
||
`user_id` bigint unsigned NOT NULL,
|
||
`product_id` bigint unsigned NOT NULL,
|
||
`url` varchar(512) NOT NULL,
|
||
`hash` varchar(64) DEFAULT NULL COMMENT '鍐呭鍝堝笇锛堝幓閲嶏級',
|
||
`sort_order` int NOT NULL DEFAULT '0',
|
||
`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
PRIMARY KEY (`id`),
|
||
UNIQUE KEY `ux_product_image_hash` (`product_id`,`hash`),
|
||
KEY `idx_product_images_product` (`product_id`),
|
||
KEY `fk_pimg_shop` (`shop_id`),
|
||
KEY `fk_pimg_user` (`user_id`),
|
||
CONSTRAINT `fk_pimg_product` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`) ON DELETE CASCADE,
|
||
CONSTRAINT `fk_pimg_shop` FOREIGN KEY (`shop_id`) REFERENCES `shops` (`id`),
|
||
CONSTRAINT `fk_pimg_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
|
||
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='鍟嗗搧鍥剧墖';
|
||
/*!40101 SET character_set_client = @saved_cs_client */;
|
||
|
||
--
|
||
-- Table structure for table `product_prices`
|
||
--
|
||
|
||
DROP TABLE IF EXISTS `product_prices`;
|
||
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
||
/*!50503 SET character_set_client = utf8mb4 */;
|
||
CREATE TABLE `product_prices` (
|
||
`product_id` bigint unsigned NOT NULL,
|
||
`shop_id` bigint unsigned NOT NULL,
|
||
`user_id` bigint unsigned NOT NULL,
|
||
`purchase_price` decimal(18,2) NOT NULL DEFAULT '0.00',
|
||
`retail_price` decimal(18,2) NOT NULL DEFAULT '0.00',
|
||
`wholesale_price` decimal(18,2) NOT NULL DEFAULT '0.00',
|
||
`big_client_price` decimal(18,2) NOT NULL DEFAULT '0.00',
|
||
`updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
||
PRIMARY KEY (`product_id`),
|
||
KEY `idx_prices_shop` (`shop_id`),
|
||
KEY `fk_prices_user` (`user_id`),
|
||
CONSTRAINT `fk_prices_product` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`) ON DELETE CASCADE,
|
||
CONSTRAINT `fk_prices_shop` FOREIGN KEY (`shop_id`) REFERENCES `shops` (`id`),
|
||
CONSTRAINT `fk_prices_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`),
|
||
CONSTRAINT `ck_prices_non_negative` CHECK (((`purchase_price` >= 0) and (`retail_price` >= 0) and (`wholesale_price` >= 0) and (`big_client_price` >= 0)))
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='鍟嗗搧浠锋牸锛堝惈鍥涘垪閿€鍞环锛?;
|
||
/*!40101 SET character_set_client = @saved_cs_client */;
|
||
|
||
--
|
||
-- Table structure for table `product_units`
|
||
--
|
||
|
||
DROP TABLE IF EXISTS `product_units`;
|
||
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
||
/*!50503 SET character_set_client = utf8mb4 */;
|
||
CREATE TABLE `product_units` (
|
||
`id` bigint unsigned NOT NULL AUTO_INCREMENT,
|
||
`shop_id` bigint unsigned NOT NULL,
|
||
`user_id` bigint unsigned NOT NULL,
|
||
`name` varchar(16) NOT NULL,
|
||
`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
`updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
||
`deleted_at` datetime DEFAULT NULL,
|
||
PRIMARY KEY (`id`),
|
||
UNIQUE KEY `ux_units_shop_name` (`shop_id`,`name`),
|
||
KEY `idx_units_shop` (`shop_id`),
|
||
KEY `fk_units_user` (`user_id`),
|
||
CONSTRAINT `fk_units_shop` FOREIGN KEY (`shop_id`) REFERENCES `shops` (`id`),
|
||
CONSTRAINT `fk_units_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
|
||
) ENGINE=InnoDB AUTO_INCREMENT=50 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='鍟嗗搧鍗曚綅';
|
||
/*!40101 SET character_set_client = @saved_cs_client */;
|
||
|
||
--
|
||
-- Table structure for table `products`
|
||
--
|
||
|
||
DROP TABLE IF EXISTS `products`;
|
||
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
||
/*!50503 SET character_set_client = utf8mb4 */;
|
||
CREATE TABLE `products` (
|
||
`id` bigint unsigned NOT NULL AUTO_INCREMENT,
|
||
`shop_id` bigint unsigned NOT NULL,
|
||
`user_id` bigint unsigned NOT NULL,
|
||
`name` varchar(120) NOT NULL,
|
||
`category_id` bigint unsigned DEFAULT NULL,
|
||
`template_id` bigint unsigned DEFAULT NULL,
|
||
`brand` varchar(64) DEFAULT NULL,
|
||
`model` varchar(64) DEFAULT NULL,
|
||
`spec` varchar(128) DEFAULT NULL,
|
||
`origin` varchar(64) DEFAULT NULL,
|
||
`barcode` varchar(32) DEFAULT NULL,
|
||
`dedupe_key` varchar(512) DEFAULT NULL,
|
||
`alias` varchar(120) DEFAULT NULL,
|
||
`is_blacklisted` tinyint(1) NOT NULL DEFAULT '0',
|
||
`description` text,
|
||
`global_sku_id` bigint unsigned DEFAULT NULL,
|
||
`source_submission_id` bigint unsigned DEFAULT NULL,
|
||
`attributes_json` json DEFAULT NULL,
|
||
`safe_min` decimal(18,3) DEFAULT NULL,
|
||
`safe_max` decimal(18,3) DEFAULT NULL,
|
||
`search_text` text COMMENT '渚涘叏鏂囨绱㈢殑鑱氬悎瀛楁锛堝悕绉?鍝佺墝/鍨嬪彿/瑙勬牸/鍒悕锛?,
|
||
`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
`updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
||
`deleted_at` datetime DEFAULT NULL,
|
||
`is_active` tinyint GENERATED ALWAYS AS ((case when (`deleted_at` is null) then 1 else 0 end)) STORED,
|
||
PRIMARY KEY (`id`),
|
||
UNIQUE KEY `ux_products_template_name_model` (`template_id`,`name`,`model`),
|
||
UNIQUE KEY `ux_products_shop_barcode_live` (`shop_id`,`barcode`,`is_active`),
|
||
KEY `idx_products_shop` (`shop_id`),
|
||
KEY `idx_products_category` (`category_id`),
|
||
KEY `fk_products_user` (`user_id`),
|
||
KEY `fk_products_globalsku` (`global_sku_id`),
|
||
KEY `idx_products_shop_blacklist` (`shop_id`,`is_blacklisted`),
|
||
KEY `fk_products_submission` (`source_submission_id`),
|
||
KEY `idx_products_template` (`template_id`),
|
||
KEY `idx_products_dedupe` (`dedupe_key`),
|
||
KEY `idx_products_deleted_at` (`deleted_at`),
|
||
FULLTEXT KEY `ft_products_search` (`name`,`brand`,`model`,`spec`,`search_text`),
|
||
CONSTRAINT `fk_products_category` FOREIGN KEY (`category_id`) REFERENCES `product_categories` (`id`),
|
||
CONSTRAINT `fk_products_globalsku` FOREIGN KEY (`global_sku_id`) REFERENCES `global_skus` (`id`),
|
||
CONSTRAINT `fk_products_shop` FOREIGN KEY (`shop_id`) REFERENCES `shops` (`id`),
|
||
CONSTRAINT `fk_products_submission` FOREIGN KEY (`source_submission_id`) REFERENCES `part_submissions` (`id`),
|
||
CONSTRAINT `fk_products_template` FOREIGN KEY (`template_id`) REFERENCES `part_templates` (`id`),
|
||
CONSTRAINT `fk_products_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`),
|
||
CONSTRAINT `ck_products_safe_range` CHECK (((`safe_min` is null) or (`safe_max` is null) or (`safe_min` <= `safe_max`)))
|
||
) ENGINE=InnoDB AUTO_INCREMENT=20 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='鍟嗗搧';
|
||
/*!40101 SET character_set_client = @saved_cs_client */;
|
||
/*!50003 SET @saved_cs_client = @@character_set_client */ ;
|
||
/*!50003 SET @saved_cs_results = @@character_set_results */ ;
|
||
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
|
||
/*!50003 SET character_set_client = utf8mb4 */ ;
|
||
/*!50003 SET character_set_results = utf8mb4 */ ;
|
||
/*!50003 SET collation_connection = utf8mb4_unicode_ci */ ;
|
||
/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
|
||
/*!50003 SET sql_mode = 'IGNORE_SPACE,ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION' */ ;
|
||
DELIMITER ;;
|
||
/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`%`*/ /*!50003 TRIGGER `trg_products_bi` BEFORE INSERT ON `products` FOR EACH ROW BEGIN SET NEW.search_text = CONCAT_WS(' ', NEW.name, NEW.brand, NEW.model, NEW.spec); END */;;
|
||
DELIMITER ;
|
||
/*!50003 SET sql_mode = @saved_sql_mode */ ;
|
||
/*!50003 SET character_set_client = @saved_cs_client */ ;
|
||
/*!50003 SET character_set_results = @saved_cs_results */ ;
|
||
/*!50003 SET collation_connection = @saved_col_connection */ ;
|
||
/*!50003 SET @saved_cs_client = @@character_set_client */ ;
|
||
/*!50003 SET @saved_cs_results = @@character_set_results */ ;
|
||
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
|
||
/*!50003 SET character_set_client = utf8mb4 */ ;
|
||
/*!50003 SET character_set_results = utf8mb4 */ ;
|
||
/*!50003 SET collation_connection = utf8mb4_0900_ai_ci */ ;
|
||
/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
|
||
/*!50003 SET sql_mode = 'STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION' */ ;
|
||
DELIMITER ;;
|
||
/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`%`*/ /*!50003 TRIGGER `trg_products_au` BEFORE UPDATE ON `products` FOR EACH ROW SET NEW.search_text = CONCAT_WS(' ', NEW.name, NEW.brand, NEW.model, NEW.spec) */;;
|
||
DELIMITER ;
|
||
/*!50003 SET sql_mode = @saved_sql_mode */ ;
|
||
/*!50003 SET character_set_client = @saved_cs_client */ ;
|
||
/*!50003 SET character_set_results = @saved_cs_results */ ;
|
||
/*!50003 SET collation_connection = @saved_col_connection */ ;
|
||
|
||
--
|
||
-- Table structure for table `purchase_order_items`
|
||
--
|
||
|
||
DROP TABLE IF EXISTS `purchase_order_items`;
|
||
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
||
/*!50503 SET character_set_client = utf8mb4 */;
|
||
CREATE TABLE `purchase_order_items` (
|
||
`id` bigint unsigned NOT NULL AUTO_INCREMENT,
|
||
`order_id` bigint unsigned NOT NULL,
|
||
`product_id` bigint unsigned NOT NULL,
|
||
`quantity` decimal(18,3) NOT NULL,
|
||
`unit_price` decimal(18,2) NOT NULL,
|
||
`amount` decimal(18,2) NOT NULL,
|
||
PRIMARY KEY (`id`),
|
||
KEY `idx_poi_order` (`order_id`),
|
||
KEY `idx_poi_product` (`product_id`),
|
||
CONSTRAINT `fk_poi_order` FOREIGN KEY (`order_id`) REFERENCES `purchase_orders` (`id`) ON DELETE CASCADE,
|
||
CONSTRAINT `fk_poi_product` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`),
|
||
CONSTRAINT `ck_poi_price` CHECK ((`unit_price` >= 0)),
|
||
CONSTRAINT `ck_poi_qty` CHECK ((`quantity` > 0))
|
||
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='杩涜揣鍗曟槑缁?;
|
||
/*!40101 SET character_set_client = @saved_cs_client */;
|
||
|
||
--
|
||
-- Table structure for table `purchase_orders`
|
||
--
|
||
|
||
DROP TABLE IF EXISTS `purchase_orders`;
|
||
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
||
/*!50503 SET character_set_client = utf8mb4 */;
|
||
CREATE TABLE `purchase_orders` (
|
||
`id` bigint unsigned NOT NULL AUTO_INCREMENT,
|
||
`shop_id` bigint unsigned NOT NULL,
|
||
`user_id` bigint unsigned NOT NULL,
|
||
`supplier_id` bigint unsigned DEFAULT NULL,
|
||
`order_no` varchar(32) NOT NULL,
|
||
`order_time` datetime NOT NULL,
|
||
`status` enum('draft','approved','void','returned') NOT NULL DEFAULT 'draft',
|
||
`amount` decimal(18,2) NOT NULL DEFAULT '0.00' COMMENT '搴斾粯鍚堣',
|
||
`paid_amount` decimal(18,2) NOT NULL DEFAULT '0.00' COMMENT '宸蹭粯鍚堣',
|
||
`remark` varchar(255) DEFAULT NULL,
|
||
`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
`updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
||
`deleted_at` datetime DEFAULT NULL,
|
||
PRIMARY KEY (`id`),
|
||
UNIQUE KEY `ux_purchase_order_no` (`shop_id`,`order_no`),
|
||
KEY `idx_purchase_shop_time` (`shop_id`,`order_time`),
|
||
KEY `idx_purchase_supplier` (`supplier_id`),
|
||
KEY `fk_purchase_user` (`user_id`),
|
||
CONSTRAINT `fk_purchase_shop` FOREIGN KEY (`shop_id`) REFERENCES `shops` (`id`),
|
||
CONSTRAINT `fk_purchase_supplier` FOREIGN KEY (`supplier_id`) REFERENCES `suppliers` (`id`),
|
||
CONSTRAINT `fk_purchase_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
|
||
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='杩涜揣鍗?;
|
||
/*!40101 SET character_set_client = @saved_cs_client */;
|
||
|
||
--
|
||
-- Table structure for table `purchase_return_order_items`
|
||
--
|
||
|
||
DROP TABLE IF EXISTS `purchase_return_order_items`;
|
||
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
||
/*!50503 SET character_set_client = utf8mb4 */;
|
||
CREATE TABLE `purchase_return_order_items` (
|
||
`id` bigint unsigned NOT NULL AUTO_INCREMENT,
|
||
`order_id` bigint unsigned NOT NULL,
|
||
`product_id` bigint unsigned NOT NULL,
|
||
`quantity` decimal(18,3) NOT NULL,
|
||
`unit_price` decimal(18,2) NOT NULL,
|
||
`amount` decimal(18,2) NOT NULL,
|
||
PRIMARY KEY (`id`),
|
||
KEY `idx_proi_order` (`order_id`),
|
||
KEY `idx_proi_product` (`product_id`),
|
||
CONSTRAINT `fk_proi_order` FOREIGN KEY (`order_id`) REFERENCES `purchase_return_orders` (`id`) ON DELETE CASCADE,
|
||
CONSTRAINT `fk_proi_product` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='杩涜揣閫€璐у崟鏄庣粏';
|
||
/*!40101 SET character_set_client = @saved_cs_client */;
|
||
|
||
--
|
||
-- Table structure for table `purchase_return_orders`
|
||
--
|
||
|
||
DROP TABLE IF EXISTS `purchase_return_orders`;
|
||
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
||
/*!50503 SET character_set_client = utf8mb4 */;
|
||
CREATE TABLE `purchase_return_orders` (
|
||
`id` bigint unsigned NOT NULL AUTO_INCREMENT,
|
||
`shop_id` bigint unsigned NOT NULL,
|
||
`user_id` bigint unsigned NOT NULL,
|
||
`supplier_id` bigint unsigned DEFAULT NULL,
|
||
`order_no` varchar(32) NOT NULL,
|
||
`order_time` datetime NOT NULL,
|
||
`status` enum('approved','void') NOT NULL DEFAULT 'approved',
|
||
`amount` decimal(18,2) NOT NULL DEFAULT '0.00',
|
||
`paid_amount` decimal(18,2) NOT NULL DEFAULT '0.00',
|
||
`remark` varchar(255) DEFAULT NULL,
|
||
`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
`updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
||
`deleted_at` datetime DEFAULT NULL,
|
||
PRIMARY KEY (`id`),
|
||
UNIQUE KEY `ux_pr_order_no` (`shop_id`,`order_no`),
|
||
KEY `idx_pr_shop_time` (`shop_id`,`order_time`),
|
||
KEY `fk_pr_user` (`user_id`),
|
||
KEY `fk_pr_supplier` (`supplier_id`),
|
||
CONSTRAINT `fk_pr_shop` FOREIGN KEY (`shop_id`) REFERENCES `shops` (`id`),
|
||
CONSTRAINT `fk_pr_supplier` FOREIGN KEY (`supplier_id`) REFERENCES `suppliers` (`id`),
|
||
CONSTRAINT `fk_pr_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='杩涜揣閫€璐у崟';
|
||
/*!40101 SET character_set_client = @saved_cs_client */;
|
||
|
||
--
|
||
-- Table structure for table `sales_order_items`
|
||
--
|
||
|
||
DROP TABLE IF EXISTS `sales_order_items`;
|
||
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
||
/*!50503 SET character_set_client = utf8mb4 */;
|
||
CREATE TABLE `sales_order_items` (
|
||
`id` bigint unsigned NOT NULL AUTO_INCREMENT,
|
||
`order_id` bigint unsigned NOT NULL,
|
||
`product_id` bigint unsigned NOT NULL,
|
||
`quantity` decimal(18,3) NOT NULL,
|
||
`unit_price` decimal(18,2) NOT NULL,
|
||
`discount_rate` decimal(5,2) NOT NULL DEFAULT '0.00' COMMENT '鎶樻墸鐧惧垎姣?-100',
|
||
`cost_price` decimal(18,2) NOT NULL DEFAULT '0.00',
|
||
`cost_amount` decimal(18,2) NOT NULL DEFAULT '0.00',
|
||
`amount` decimal(18,2) NOT NULL,
|
||
PRIMARY KEY (`id`),
|
||
KEY `idx_soi_order` (`order_id`),
|
||
KEY `idx_soi_product` (`product_id`),
|
||
CONSTRAINT `fk_soi_order` FOREIGN KEY (`order_id`) REFERENCES `sales_orders` (`id`) ON DELETE CASCADE,
|
||
CONSTRAINT `fk_soi_product` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`),
|
||
CONSTRAINT `ck_soi_discount` CHECK (((`discount_rate` >= 0) and (`discount_rate` <= 100))),
|
||
CONSTRAINT `ck_soi_price` CHECK ((`unit_price` >= 0)),
|
||
CONSTRAINT `ck_soi_qty` CHECK ((`quantity` > 0))
|
||
) ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='閿€鍞崟鏄庣粏';
|
||
/*!40101 SET character_set_client = @saved_cs_client */;
|
||
|
||
--
|
||
-- Table structure for table `sales_orders`
|
||
--
|
||
|
||
DROP TABLE IF EXISTS `sales_orders`;
|
||
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
||
/*!50503 SET character_set_client = utf8mb4 */;
|
||
CREATE TABLE `sales_orders` (
|
||
`id` bigint unsigned NOT NULL AUTO_INCREMENT,
|
||
`shop_id` bigint unsigned NOT NULL,
|
||
`user_id` bigint unsigned NOT NULL COMMENT '鍒涘缓浜?,
|
||
`customer_id` bigint unsigned DEFAULT NULL,
|
||
`order_no` varchar(32) NOT NULL,
|
||
`order_time` datetime NOT NULL,
|
||
`status` enum('draft','approved','returned','void') NOT NULL DEFAULT 'draft',
|
||
`amount` decimal(18,2) NOT NULL DEFAULT '0.00' COMMENT '搴旀敹鍚堣',
|
||
`paid_amount` decimal(18,2) NOT NULL DEFAULT '0.00' COMMENT '宸叉敹鍚堣',
|
||
`remark` varchar(255) DEFAULT NULL,
|
||
`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
`updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
||
`deleted_at` datetime DEFAULT NULL,
|
||
PRIMARY KEY (`id`),
|
||
UNIQUE KEY `ux_sales_order_no` (`shop_id`,`order_no`),
|
||
KEY `idx_sales_shop_time` (`shop_id`,`order_time`),
|
||
KEY `idx_sales_customer` (`customer_id`),
|
||
KEY `fk_sales_user` (`user_id`),
|
||
CONSTRAINT `fk_sales_customer` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`),
|
||
CONSTRAINT `fk_sales_shop` FOREIGN KEY (`shop_id`) REFERENCES `shops` (`id`),
|
||
CONSTRAINT `fk_sales_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
|
||
) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='閿€鍞崟';
|
||
/*!40101 SET character_set_client = @saved_cs_client */;
|
||
|
||
--
|
||
-- Table structure for table `sales_return_order_items`
|
||
--
|
||
|
||
DROP TABLE IF EXISTS `sales_return_order_items`;
|
||
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
||
/*!50503 SET character_set_client = utf8mb4 */;
|
||
CREATE TABLE `sales_return_order_items` (
|
||
`id` bigint unsigned NOT NULL AUTO_INCREMENT,
|
||
`order_id` bigint unsigned NOT NULL,
|
||
`product_id` bigint unsigned NOT NULL,
|
||
`quantity` decimal(18,3) NOT NULL,
|
||
`unit_price` decimal(18,2) NOT NULL,
|
||
`discount_rate` decimal(5,2) NOT NULL DEFAULT '0.00',
|
||
`cost_price` decimal(18,2) NOT NULL DEFAULT '0.00',
|
||
`cost_amount` decimal(18,2) NOT NULL DEFAULT '0.00',
|
||
`amount` decimal(18,2) NOT NULL,
|
||
PRIMARY KEY (`id`),
|
||
KEY `idx_sroi_order` (`order_id`),
|
||
KEY `idx_sroi_product` (`product_id`),
|
||
CONSTRAINT `fk_sroi_order` FOREIGN KEY (`order_id`) REFERENCES `sales_return_orders` (`id`) ON DELETE CASCADE,
|
||
CONSTRAINT `fk_sroi_product` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`)
|
||
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='閿€鍞€€璐у崟鏄庣粏';
|
||
/*!40101 SET character_set_client = @saved_cs_client */;
|
||
|
||
--
|
||
-- Table structure for table `sales_return_orders`
|
||
--
|
||
|
||
DROP TABLE IF EXISTS `sales_return_orders`;
|
||
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
||
/*!50503 SET character_set_client = utf8mb4 */;
|
||
CREATE TABLE `sales_return_orders` (
|
||
`id` bigint unsigned NOT NULL AUTO_INCREMENT,
|
||
`shop_id` bigint unsigned NOT NULL,
|
||
`user_id` bigint unsigned NOT NULL,
|
||
`customer_id` bigint unsigned DEFAULT NULL,
|
||
`order_no` varchar(32) NOT NULL,
|
||
`order_time` datetime NOT NULL,
|
||
`status` enum('approved','void') NOT NULL DEFAULT 'approved',
|
||
`amount` decimal(18,2) NOT NULL DEFAULT '0.00',
|
||
`paid_amount` decimal(18,2) NOT NULL DEFAULT '0.00',
|
||
`remark` varchar(255) DEFAULT NULL,
|
||
`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
`updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
||
`deleted_at` datetime DEFAULT NULL,
|
||
PRIMARY KEY (`id`),
|
||
UNIQUE KEY `ux_sr_order_no` (`shop_id`,`order_no`),
|
||
KEY `idx_sr_shop_time` (`shop_id`,`order_time`),
|
||
KEY `fk_sr_user` (`user_id`),
|
||
KEY `fk_sr_customer` (`customer_id`),
|
||
CONSTRAINT `fk_sr_customer` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`),
|
||
CONSTRAINT `fk_sr_shop` FOREIGN KEY (`shop_id`) REFERENCES `shops` (`id`),
|
||
CONSTRAINT `fk_sr_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
|
||
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='閿€鍞€€璐у崟';
|
||
/*!40101 SET character_set_client = @saved_cs_client */;
|
||
|
||
--
|
||
-- Table structure for table `shops`
|
||
--
|
||
|
||
DROP TABLE IF EXISTS `shops`;
|
||
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
||
/*!50503 SET character_set_client = utf8mb4 */;
|
||
CREATE TABLE `shops` (
|
||
`id` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT '搴楅摵/绉熸埛ID',
|
||
`name` varchar(100) NOT NULL COMMENT '搴楅摵鍚嶇О',
|
||
`status` tinyint unsigned NOT NULL DEFAULT '1' COMMENT '鐘舵€侊細1鍚敤 0鍋滅敤',
|
||
`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
`updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
||
`deleted_at` datetime DEFAULT NULL,
|
||
PRIMARY KEY (`id`),
|
||
KEY `idx_shops_status` (`status`)
|
||
) ENGINE=InnoDB AUTO_INCREMENT=1006 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='搴楅摵/绉熸埛';
|
||
/*!40101 SET character_set_client = @saved_cs_client */;
|
||
|
||
--
|
||
-- Table structure for table `sms_codes`
|
||
--
|
||
|
||
DROP TABLE IF EXISTS `sms_codes`;
|
||
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
||
/*!50503 SET character_set_client = utf8mb4 */;
|
||
CREATE TABLE `sms_codes` (
|
||
`id` bigint unsigned NOT NULL AUTO_INCREMENT,
|
||
`phone` varchar(32) NOT NULL,
|
||
`scene` varchar(32) NOT NULL DEFAULT 'login',
|
||
`code_hash` char(64) NOT NULL,
|
||
`salt` char(32) NOT NULL,
|
||
`expire_at` datetime NOT NULL,
|
||
`status` tinyint unsigned NOT NULL DEFAULT '0' COMMENT '0=active,1=used,2=expired,3=blocked',
|
||
`fail_count` tinyint unsigned NOT NULL DEFAULT '0',
|
||
`ip` varchar(45) DEFAULT NULL,
|
||
`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
`updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
||
PRIMARY KEY (`id`),
|
||
KEY `idx_phone_created_at` (`phone`,`created_at`),
|
||
KEY `idx_phone_scene_status` (`phone`,`scene`,`status`),
|
||
KEY `idx_expire_at` (`expire_at`),
|
||
KEY `idx_ip_created_at` (`ip`,`created_at`)
|
||
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
|
||
/*!40101 SET character_set_client = @saved_cs_client */;
|
||
|
||
--
|
||
-- Table structure for table `suppliers`
|
||
--
|
||
|
||
DROP TABLE IF EXISTS `suppliers`;
|
||
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
||
/*!50503 SET character_set_client = utf8mb4 */;
|
||
CREATE TABLE `suppliers` (
|
||
`id` bigint unsigned NOT NULL AUTO_INCREMENT,
|
||
`shop_id` bigint unsigned NOT NULL,
|
||
`user_id` bigint unsigned NOT NULL,
|
||
`name` varchar(120) NOT NULL,
|
||
`contact_name` varchar(64) DEFAULT NULL,
|
||
`mobile` varchar(32) DEFAULT NULL,
|
||
`phone` varchar(32) DEFAULT NULL,
|
||
`address` varchar(255) DEFAULT NULL,
|
||
`status` tinyint unsigned NOT NULL DEFAULT '1',
|
||
`ap_opening` decimal(18,2) NOT NULL DEFAULT '0.00',
|
||
`ap_payable` decimal(18,2) NOT NULL DEFAULT '0.00',
|
||
`remark` varchar(255) DEFAULT NULL,
|
||
`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
`updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
||
`deleted_at` datetime DEFAULT NULL,
|
||
PRIMARY KEY (`id`),
|
||
KEY `idx_suppliers_shop` (`shop_id`),
|
||
KEY `idx_suppliers_phone` (`phone`),
|
||
KEY `fk_suppliers_user` (`user_id`),
|
||
KEY `idx_suppliers_mobile` (`mobile`),
|
||
CONSTRAINT `fk_suppliers_shop` FOREIGN KEY (`shop_id`) REFERENCES `shops` (`id`),
|
||
CONSTRAINT `fk_suppliers_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
|
||
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='渚涘簲鍟?;
|
||
/*!40101 SET character_set_client = @saved_cs_client */;
|
||
|
||
--
|
||
-- Table structure for table `system_parameters`
|
||
--
|
||
|
||
DROP TABLE IF EXISTS `system_parameters`;
|
||
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
||
/*!50503 SET character_set_client = utf8mb4 */;
|
||
CREATE TABLE `system_parameters` (
|
||
`id` bigint unsigned NOT NULL AUTO_INCREMENT,
|
||
`shop_id` bigint unsigned NOT NULL,
|
||
`user_id` bigint unsigned NOT NULL COMMENT '鍒涘缓/鏈€鍚庝慨鏀逛汉',
|
||
`key` varchar(64) NOT NULL COMMENT '鍙傛暟閿?,
|
||
`value` json NOT NULL COMMENT '鍙傛暟鍊硷紙JSON锛?,
|
||
`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
`updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
||
PRIMARY KEY (`id`),
|
||
UNIQUE KEY `ux_sysparams_shop_key` (`shop_id`,`key`),
|
||
KEY `idx_sysparams_shop` (`shop_id`),
|
||
KEY `fk_sysparams_user` (`user_id`),
|
||
CONSTRAINT `fk_sysparams_shop` FOREIGN KEY (`shop_id`) REFERENCES `shops` (`id`),
|
||
CONSTRAINT `fk_sysparams_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='绯荤粺鍙傛暟锛堢鎴风骇锛?;
|
||
/*!40101 SET character_set_client = @saved_cs_client */;
|
||
|
||
--
|
||
-- Table structure for table `user_identities`
|
||
--
|
||
|
||
DROP TABLE IF EXISTS `user_identities`;
|
||
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
||
/*!50503 SET character_set_client = utf8mb4 */;
|
||
CREATE TABLE `user_identities` (
|
||
`id` bigint unsigned NOT NULL AUTO_INCREMENT,
|
||
`shop_id` bigint unsigned NOT NULL,
|
||
`user_id` bigint unsigned NOT NULL,
|
||
`provider` enum('wechat_mp','wechat_app') NOT NULL COMMENT '韬唤鎻愪緵鏂癸細灏忕▼搴?APP',
|
||
`openid` varchar(64) NOT NULL,
|
||
`unionid` varchar(64) DEFAULT NULL,
|
||
`nickname` varchar(64) DEFAULT NULL,
|
||
`avatar_url` varchar(512) DEFAULT NULL,
|
||
`last_login_at` datetime DEFAULT NULL,
|
||
`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
`updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
||
PRIMARY KEY (`id`),
|
||
UNIQUE KEY `ux_identity_provider_openid` (`provider`,`openid`),
|
||
UNIQUE KEY `ux_identity_unionid` (`unionid`),
|
||
KEY `idx_identity_user` (`user_id`),
|
||
KEY `idx_identity_shop` (`shop_id`),
|
||
CONSTRAINT `fk_identity_shop` FOREIGN KEY (`shop_id`) REFERENCES `shops` (`id`),
|
||
CONSTRAINT `fk_identity_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='绗笁鏂硅韩浠芥槧灏勶紙寰俊锛?;
|
||
/*!40101 SET character_set_client = @saved_cs_client */;
|
||
|
||
--
|
||
-- Table structure for table `users`
|
||
--
|
||
|
||
DROP TABLE IF EXISTS `users`;
|
||
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
||
/*!50503 SET character_set_client = utf8mb4 */;
|
||
CREATE TABLE `users` (
|
||
`id` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT '鐢ㄦ埛ID',
|
||
`shop_id` bigint unsigned NOT NULL COMMENT '鎵€灞炲簵閾?,
|
||
`phone` varchar(32) DEFAULT NULL COMMENT '鎵嬫満鍙?,
|
||
`email` varchar(128) DEFAULT NULL,
|
||
`avatar_url` varchar(512) DEFAULT NULL,
|
||
`name` varchar(64) NOT NULL COMMENT '濮撳悕',
|
||
`role` varchar(32) NOT NULL DEFAULT 'staff' COMMENT '瑙掕壊锛歰wner/staff/finance/...',
|
||
`password_hash` varchar(255) DEFAULT NULL COMMENT '瀵嗙爜鍝堝笇锛堣嫢閲囩敤鐭俊鐧诲綍鍙负绌猴級',
|
||
`status` tinyint unsigned NOT NULL DEFAULT '1' COMMENT '鐘舵€侊細1鍚敤 0鍋滅敤',
|
||
`is_owner` tinyint(1) NOT NULL DEFAULT '0' COMMENT '鏄惁搴椾富',
|
||
`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
`updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
||
`deleted_at` datetime DEFAULT NULL,
|
||
PRIMARY KEY (`id`),
|
||
UNIQUE KEY `ux_users_shop_phone` (`shop_id`,`phone`),
|
||
UNIQUE KEY `uk_users_phone` (`phone`),
|
||
UNIQUE KEY `ux_users_email` (`email`),
|
||
KEY `idx_users_shop` (`shop_id`),
|
||
CONSTRAINT `fk_users_shop` FOREIGN KEY (`shop_id`) REFERENCES `shops` (`id`)
|
||
) ENGINE=InnoDB AUTO_INCREMENT=1005 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='鐢ㄦ埛';
|
||
/*!40101 SET character_set_client = @saved_cs_client */;
|
||
|
||
--
|
||
-- Table structure for table `vip_price`
|
||
--
|
||
|
||
DROP TABLE IF EXISTS `vip_price`;
|
||
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
||
/*!50503 SET character_set_client = utf8mb4 */;
|
||
CREATE TABLE `vip_price` (
|
||
`price` decimal(10,2) NOT NULL
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='VIP浠锋牸锛堝叏灞€锛屼粎涓€鏉¤褰曪級';
|
||
/*!40101 SET character_set_client = @saved_cs_client */;
|
||
|
||
--
|
||
-- Table structure for table `vip_recharges`
|
||
--
|
||
|
||
DROP TABLE IF EXISTS `vip_recharges`;
|
||
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
||
/*!50503 SET character_set_client = utf8mb4 */;
|
||
CREATE TABLE `vip_recharges` (
|
||
`id` bigint unsigned NOT NULL AUTO_INCREMENT,
|
||
`shop_id` bigint unsigned NOT NULL,
|
||
`user_id` bigint unsigned NOT NULL,
|
||
`price` decimal(10,2) NOT NULL,
|
||
`duration_days` int NOT NULL,
|
||
`expire_from` datetime DEFAULT NULL,
|
||
`expire_to` datetime NOT NULL,
|
||
`channel` varchar(32) NOT NULL DEFAULT 'oneclick',
|
||
`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
PRIMARY KEY (`id`),
|
||
KEY `idx_vr_shop` (`shop_id`),
|
||
KEY `idx_vr_user` (`user_id`),
|
||
CONSTRAINT `fk_vr_shop` FOREIGN KEY (`shop_id`) REFERENCES `shops` (`id`),
|
||
CONSTRAINT `fk_vr_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
|
||
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='VIP鍏呭€艰褰?;
|
||
/*!40101 SET character_set_client = @saved_cs_client */;
|
||
|
||
--
|
||
-- Table structure for table `vip_users`
|
||
--
|
||
|
||
DROP TABLE IF EXISTS `vip_users`;
|
||
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
||
/*!50503 SET character_set_client = utf8mb4 */;
|
||
CREATE TABLE `vip_users` (
|
||
`id` bigint unsigned NOT NULL AUTO_INCREMENT,
|
||
`shop_id` bigint unsigned NOT NULL,
|
||
`user_id` bigint unsigned NOT NULL,
|
||
`is_vip` tinyint(1) NOT NULL DEFAULT '1',
|
||
`status` tinyint unsigned NOT NULL DEFAULT '0',
|
||
`expire_at` datetime DEFAULT NULL,
|
||
`remark` varchar(255) DEFAULT NULL,
|
||
`reviewer_id` bigint unsigned DEFAULT NULL,
|
||
`reviewed_at` datetime DEFAULT NULL,
|
||
`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
`updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
||
PRIMARY KEY (`id`),
|
||
KEY `idx_vu_shop_user` (`shop_id`,`user_id`),
|
||
KEY `idx_vu_shop_status` (`shop_id`,`status`),
|
||
KEY `fk_vu_user` (`user_id`),
|
||
KEY `fk_vu_reviewer` (`reviewer_id`),
|
||
CONSTRAINT `fk_vu_reviewer` FOREIGN KEY (`reviewer_id`) REFERENCES `users` (`id`),
|
||
CONSTRAINT `fk_vu_shop` FOREIGN KEY (`shop_id`) REFERENCES `shops` (`id`),
|
||
CONSTRAINT `fk_vu_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
|
||
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
|
||
/*!40101 SET character_set_client = @saved_cs_client */;
|
||
|
||
--
|
||
-- Table structure for table `wechat_sessions`
|
||
--
|
||
|
||
DROP TABLE IF EXISTS `wechat_sessions`;
|
||
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
||
/*!50503 SET character_set_client = utf8mb4 */;
|
||
CREATE TABLE `wechat_sessions` (
|
||
`id` bigint unsigned NOT NULL AUTO_INCREMENT,
|
||
`provider` enum('wechat_mp','wechat_app') NOT NULL,
|
||
`openid` varchar(64) NOT NULL,
|
||
`session_key` varchar(128) NOT NULL,
|
||
`expires_at` datetime NOT NULL,
|
||
`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
PRIMARY KEY (`id`),
|
||
UNIQUE KEY `ux_wechat_session` (`provider`,`openid`),
|
||
KEY `idx_wechat_session_expires` (`expires_at`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='寰俊浼氳瘽锛堜复鏃讹級';
|
||
/*!40101 SET character_set_client = @saved_cs_client */;
|
||
|
||
--
|
||
-- Dumping events for database 'partsinquiry'
|
||
--
|
||
|
||
--
|
||
-- Dumping routines for database 'partsinquiry'
|
||
--
|
||
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
|
||
|
||
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
|
||
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
|
||
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
|
||
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
|
||
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
|
||
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
|
||
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
|
||
|
||
-- Dump completed on 2025-10-05 17:29:20
|