wuye / MicroCommunit

create_sql20190212.sql 114 KB
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332 333 334 335 336 337 338 339 340 341 342 343 344 345 346 347 348 349 350 351 352 353 354 355 356 357 358 359 360 361 362 363 364 365 366 367 368 369 370 371 372 373 374 375 376 377 378 379 380 381 382 383 384 385 386 387 388 389 390 391 392 393 394 395 396 397 398 399 400 401 402 403 404 405 406 407 408 409 410 411 412 413 414 415 416 417 418 419 420 421 422 423 424 425 426 427 428 429 430 431 432 433 434 435 436 437 438 439 440 441 442 443 444 445 446 447 448 449 450 451 452 453 454 455 456 457 458 459 460 461 462 463 464 465 466 467 468 469 470 471 472 473 474 475 476 477 478 479 480 481 482 483 484 485 486 487 488 489 490 491 492 493 494 495 496 497 498 499 500 501 502 503 504 505 506 507 508 509 510 511 512 513 514 515 516 517 518 519 520 521 522 523 524 525 526 527 528 529 530 531 532 533 534 535 536 537 538 539 540 541 542 543 544 545 546 547 548 549 550 551 552 553 554 555 556 557 558 559 560 561 562 563 564 565 566 567 568 569 570 571 572 573 574 575 576 577 578 579 580 581 582 583 584 585 586 587 588 589 590 591 592 593 594 595 596 597 598 599 600 601 602 603 604 605 606 607 608 609 610 611 612 613 614 615 616 617 618 619 620 621 622 623 624 625 626 627 628 629 630 631 632 633 634 635 636 637 638 639 640 641 642 643 644 645 646 647 648 649 650 651 652 653 654 655 656 657 658 659 660 661 662 663 664 665 666 667 668 669 670 671 672 673 674 675 676 677 678 679 680 681 682 683 684 685 686 687 688 689 690 691 692 693 694 695 696 697 698 699 700 701 702 703 704 705 706 707 708 709 710 711 712 713 714 715 716 717 718 719 720 721 722 723 724 725 726 727 728 729 730 731 732 733 734 735 736 737 738 739 740 741 742 743 744 745 746 747 748 749 750 751 752 753 754 755 756 757 758 759 760 761 762 763 764 765 766 767 768 769 770 771 772 773 774 775 776 777 778 779 780 781 782 783 784 785 786 787 788 789 790 791 792 793 794 795 796 797 798 799 800 801 802 803 804 805 806 807 808 809 810 811 812 813 814 815 816 817 818 819 820 821 822 823 824 825 826 827 828 829 830 831 832 833 834 835 836 837 838 839 840 841 842 843 844 845 846 847 848 849 850 851 852 853 854 855 856 857 858 859 860 861 862 863 864 865 866 867 868 869 870 871 872 873 874 875 876 877 878 879 880 881 882 883 884 885 886 887 888 889 890 891 892 893 894 895 896 897 898 899 900 901 902 903 904 905 906 907 908 909 910 911 912 913 914 915 916 917 918 919 920 921 922 923 924 925 926 927 928 929 930 931 932 933 934 935 936 937 938 939 940 941 942 943 944 945 946 947 948 949 950 951 952 953 954 955 956 957 958 959 960 961 962 963 964 965 966 967 968 969 970 971 972 973 974 975 976 977 978 979 980 981 982 983 984 985 986 987 988 989 990 991 992 993 994 995 996 997 998 999 1000 1001 1002 1003 1004 1005 1006 1007 1008 1009 1010 1011 1012 1013 1014 1015 1016 1017 1018 1019 1020 1021 1022 1023 1024 1025 1026 1027 1028 1029 1030 1031 1032 1033 1034 1035 1036 1037 1038 1039 1040 1041 1042 1043 1044 1045 1046 1047 1048 1049 1050 1051 1052 1053 1054 1055 1056 1057 1058 1059 1060 1061 1062 1063 1064 1065 1066 1067 1068 1069 1070 1071 1072 1073 1074 1075 1076 1077 1078 1079 1080 1081 1082 1083 1084 1085 1086 1087 1088 1089 1090 1091 1092 1093 1094 1095 1096 1097 1098 1099 1100 1101 1102 1103 1104 1105 1106 1107 1108 1109 1110 1111 1112 1113 1114 1115 1116 1117 1118 1119 1120 1121 1122 1123 1124 1125 1126 1127 1128 1129 1130 1131 1132 1133 1134 1135 1136 1137 1138 1139 1140 1141 1142 1143 1144 1145 1146 1147 1148 1149 1150 1151 1152 1153 1154 1155 1156 1157 1158 1159 1160 1161 1162 1163 1164 1165 1166 1167 1168 1169 1170 1171 1172 1173 1174 1175 1176 1177 1178 1179 1180 1181 1182 1183 1184 1185 1186 1187 1188 1189 1190 1191 1192 1193 1194 1195 1196 1197 1198 1199 1200 1201 1202 1203 1204 1205 1206 1207 1208 1209 1210 1211 1212 1213 1214 1215 1216 1217 1218 1219 1220 1221 1222 1223 1224 1225 1226 1227 1228 1229 1230 1231 1232 1233 1234 1235 1236 1237 1238 1239 1240 1241 1242 1243 1244 1245 1246 1247 1248 1249 1250 1251 1252 1253 1254 1255 1256 1257 1258 1259 1260 1261 1262 1263 1264 1265 1266 1267 1268 1269 1270 1271 1272 1273 1274 1275 1276 1277 1278 1279 1280 1281 1282 1283 1284 1285 1286 1287 1288 1289 1290 1291 1292 1293 1294 1295 1296 1297 1298 1299 1300 1301 1302 1303 1304 1305 1306 1307 1308 1309 1310 1311 1312 1313 1314 1315 1316 1317 1318 1319 1320 1321 1322 1323 1324 1325 1326 1327 1328 1329 1330 1331 1332 1333 1334 1335 1336 1337 1338 1339 1340 1341 1342 1343 1344 1345 1346 1347 1348 1349 1350 1351 1352 1353 1354 1355 1356 1357 1358 1359 1360 1361 1362 1363 1364 1365 1366 1367 1368 1369 1370 1371 1372 1373 1374 1375 1376 1377 1378 1379 1380 1381 1382 1383 1384 1385 1386 1387 1388 1389 1390 1391 1392 1393 1394 1395 1396 1397 1398 1399 1400 1401 1402 1403 1404 1405 1406 1407 1408 1409 1410 1411 1412 1413 1414 1415 1416 1417 1418 1419 1420 1421 1422 1423 1424 1425 1426 1427 1428 1429 1430 1431 1432 1433 1434 1435 1436 1437 1438 1439 1440 1441 1442 1443 1444 1445 1446 1447 1448 1449 1450 1451 1452 1453 1454 1455 1456 1457 1458 1459 1460 1461 1462 1463 1464 1465 1466 1467 1468 1469 1470 1471 1472 1473 1474 1475 1476 1477 1478 1479 1480 1481 1482 1483 1484 1485 1486 1487 1488 1489 1490 1491 1492 1493 1494 1495 1496 1497 1498 1499 1500 1501 1502 1503 1504 1505 1506 1507 1508 1509 1510 1511 1512 1513 1514 1515 1516 1517 1518 1519 1520 1521 1522 1523 1524 1525 1526 1527 1528 1529 1530 1531 1532 1533 1534 1535 1536 1537 1538 1539 1540 1541 1542 1543 1544 1545 1546 1547 1548 1549 1550 1551 1552 1553 1554 1555 1556 1557 1558 1559 1560 1561 1562 1563 1564 1565 1566 1567 1568 1569 1570 1571 1572 1573 1574 1575 1576 1577 1578 1579 1580 1581 1582 1583 1584 1585 1586 1587 1588 1589 1590 1591 1592 1593 1594 1595 1596 1597 1598 1599 1600 1601 1602 1603 1604 1605 1606 1607 1608 1609 1610 1611 1612 1613 1614 1615 1616 1617 1618 1619 1620 1621 1622 1623 1624 1625 1626 1627 1628 1629 1630 1631 1632 1633 1634 1635 1636 1637 1638 1639 1640 1641 1642 1643 1644 1645 1646 1647 1648 1649 1650 1651 1652 1653 1654 1655 1656 1657 1658 1659 1660 1661 1662 1663 1664 1665 1666 1667 1668 1669 1670 1671 1672 1673 1674 1675 1676 1677 1678 1679 1680 1681 1682 1683 1684 1685 1686 1687 1688 1689 1690 1691 1692 1693 1694 1695 1696 1697 1698 1699 1700 1701 1702 1703 1704 1705 1706 1707 1708 1709 1710 1711 1712 1713 1714 1715 1716 1717 1718 1719 1720 1721 1722 1723 1724 1725 1726 1727 1728 1729 1730 1731 1732 1733 1734 1735 1736 1737 1738 1739 1740 1741 1742 1743 1744 1745 1746 1747 1748 1749 1750 1751 1752 1753 1754 1755 1756 1757 1758 1759 1760 1761 1762 1763 1764 1765 1766 1767 1768 1769 1770 1771 1772 1773 1774 1775 1776 1777 1778 1779 1780 1781 1782 1783 1784 1785 1786 1787 1788 1789 1790 1791 1792 1793 1794 1795 1796 1797 1798 1799 1800 1801 1802 1803 1804 1805 1806 1807 1808 1809 1810 1811 1812 1813 1814 1815 1816 1817 1818 1819 1820 1821 1822 1823 1824 1825 1826 1827 1828 1829 1830 1831 1832 1833 1834 1835 1836 1837 1838 1839 1840 1841 1842 1843 1844 1845 1846 1847 1848 1849 1850 1851 1852 1853 1854 1855 1856 1857 1858 1859 1860 1861 1862 1863 

CREATE USER 'TT'@'%' IDENTIFIED BY 'TT@12345678';

GRANT ALL ON *.* TO 'TT'@'%';

create database `TT` default character set utf8 collate utf8_general_ci;

use TT;
-- c_orders
set character set utf8;

CREATE TABLE c_orders(
    o_id VARCHAR(30) NOT NULL UNIQUE COMMENT '订单ID',
    app_id VARCHAR(30) NOT NULL COMMENT '应用ID',
    ext_transaction_id VARCHAR(30) NOT NULL COMMENT '外部交易流水',
    user_id VARCHAR(30) NOT NULL COMMENT '用户ID',
    request_time VARCHAR(16) NOT NULL COMMENT '外部系统请求时间',
    create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    order_type_cd VARCHAR(4) NOT NULL COMMENT '订单类型，参考c_order_type表',
    finish_time DATE COMMENT '订单完成时间',
    remark VARCHAR(200) COMMENT '备注',
    status_cd VARCHAR(2) NOT NULL COMMENT '数据状态，详细参考c_status表'
);

-- c_orders_attrs

CREATE TABLE c_orders_attrs(
    o_id VARCHAR(30) NOT NULL COMMENT '订单ID',
    attr_id VARCHAR(30) NOT NULL COMMENT '属性id',
    spec_cd VARCHAR(12) NOT NULL COMMENT '规格id,参考spec表',
    `value` VARCHAR(50) NOT NULL COMMENT '属性值'
);



-- c_business

CREATE TABLE c_business(
    b_id VARCHAR(30) NOT NULL UNIQUE COMMENT '业务Id',
    o_id VARCHAR(30) NOT NULL COMMENT '订单ID',
    create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    business_type_cd VARCHAR(4) NOT NULL COMMENT '业务项类型，参考c_business_type表',
    finish_time DATE COMMENT '完成时间',
    remark VARCHAR(200) COMMENT '备注',
    status_cd VARCHAR(2) NOT NULL COMMENT '数据状态，详细参考c_status表'
);

-- business_attrs

CREATE TABLE c_business_attrs(
    b_id VARCHAR(30) NOT NULL COMMENT '订单ID',
    attr_id VARCHAR(30) NOT NULL COMMENT '属性id',
    spec_cd VARCHAR(12) NOT NULL COMMENT '规格id,参考spec表',
    `value` VARCHAR(50) NOT NULL COMMENT '属性值'
);

-- c_status

CREATE TABLE c_status(
    id INT NOT NULL AUTO_INCREMENT KEY COMMENT 'id',
    status_cd VARCHAR(4) NOT NULL UNIQUE COMMENT '状态',
    `name` VARCHAR(50) NOT NULL COMMENT '名称',
    description VARCHAR(200) COMMENT '描述',
    create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间'
);

INSERT INTO c_status(status_cd,NAME,description) VALUES('1','无效的，不在用的','无效的，不在用的');
INSERT INTO c_status(status_cd,NAME,description) VALUES('0','有效的，在用的','有效的，在用的');
INSERT INTO c_status(status_cd,NAME,description) VALUES('S','保存成功','保存成功');
INSERT INTO c_status(status_cd,NAME,description) VALUES('D','作废订单','作废订单');
INSERT INTO c_status(status_cd,NAME,description) VALUES('E','错误订单','错误订单');
INSERT INTO c_status(status_cd,NAME,description) VALUES('NE','通知错误订单','通知错误订单');
INSERT INTO c_status(status_cd,NAME,description) VALUES('C','订单完成','订单完成');
INSERT INTO c_status(status_cd,NAME,description) VALUES('B','Business过程','Business过程');
INSERT INTO c_status(status_cd,NAME,description) VALUES('I','Instance过程','Instance过程');


-- c_order_type

CREATE TABLE c_order_type(
    id INT NOT NULL AUTO_INCREMENT KEY COMMENT 'id',
    order_type_cd VARCHAR(4) NOT NULL UNIQUE COMMENT '订单类型',
    `name` VARCHAR(50) NOT NULL COMMENT '名称',
    description VARCHAR(200) COMMENT '描述',
    create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间'
);




-- c_business_type

CREATE TABLE c_business_type(
    id INT NOT NULL AUTO_INCREMENT KEY COMMENT 'id',
    business_type_cd VARCHAR(4) NOT NULL UNIQUE COMMENT '业务项类型',
    `name` VARCHAR(50) NOT NULL COMMENT '名称',
    description VARCHAR(200) COMMENT '描述',
    create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间'
);

insert into c_business_type(business_type_cd,name,description) values('DO','撤单','作废订单');

-- spec

CREATE TABLE spec(
    id INT NOT NULL AUTO_INCREMENT KEY COMMENT 'id',
    domain varchar(20) not null comment '属性域',
    spec_cd VARCHAR(4) NOT NULL UNIQUE COMMENT '业务项类型规格编码，从x00020001开始每次加一就可以(约定，x=10表示c_orders_attrs 中属性，x=11表示c_business_attrs 中的属性)',
    `name` VARCHAR(50) NOT NULL COMMENT '名称',
    description VARCHAR(200) COMMENT '描述',
    create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间'
);

insert into spec(domain,spec_cd,name,description) values('ORDERS','100001','订单来源','订单来源');
insert into spec(domain,spec_cd,name,description) values('BUSINESS','200001','推荐UserID','推荐UserID');

-- c_route

CREATE TABLE c_route(
    id INT NOT NULL AUTO_INCREMENT KEY COMMENT 'id',
    app_id VARCHAR(30) NOT NULL COMMENT '应用ID',
    service_id INT NOT NULL COMMENT '下游接口配置ID',
    order_type_cd VARCHAR(4) NOT NULL COMMENT '订单类型，参考c_order_type表',
    invoke_limit_times INT COMMENT '接口调用一分钟调用次数',
    invoke_model VARCHAR(1) NOT NULL COMMENT '1-同步方式 2-异步方式',
    create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    status_cd VARCHAR(2) NOT NULL DEFAULT '0' COMMENT '数据状态，详细参考c_status表，0在用，1失效，2 表示下线（当组件调用服务超过限制时自动下线）'
);



-- c_service

CREATE TABLE c_service(
    id INT NOT NULL AUTO_INCREMENT KEY COMMENT 'id',
    service_id VARCHAR(12) NOT NULL COMMENT '服务ID',
    service_code VARCHAR(50) NOT NULL UNIQUE COMMENT '自定义，命名方式查询类query.+目标系统+.+业务名称 保存类 save.+目标系统+.+业务名称 修改类 modify.+目标系统+.+业务名称 删除类 remove.+目标系统+.+业务名称 例如：query.user.userinfo save.user.adduserinfo',
    business_type_cd VARCHAR(4) NOT NULL COMMENT '业务项类型，参考c_business_type表',
    `name` VARCHAR(50) NOT NULL COMMENT '服务名称',
    seq INT NOT NULL COMMENT '顺序 只有同步方式下根据seq从小到大调用接口',
    messageQueueName VARCHAR(50) COMMENT '消息队里名称 只有异步时有用',
    is_instance varchar(2) not null default 'N' comment '是否Instance Y 需要，N不需要，NT透传类',
    url VARCHAR(200) COMMENT '目标地址',
    method VARCHAR(50) COMMENT '方法 空 为http post LOCAL_SERVICE 为调用本地服务 其他为webservice方式调用',
    timeout INT NOT NULL DEFAULT 60 COMMENT '超时时间',
    retry_count INT NOT NULL DEFAULT 3 COMMENT '重试次数',
    provide_app_id VARCHAR(30) NOT NULL COMMENT '应用ID',
    create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    status_cd VARCHAR(2) NOT NULL default '0' COMMENT '数据状态，详细参考c_status表，0在用，1失效',
    UNIQUE KEY (service_id)
);


-- c_mapping

CREATE TABLE c_mapping(
    id INT NOT NULL AUTO_INCREMENT KEY COMMENT 'id',
    domain VARCHAR(50) NOT NULL COMMENT '域',
    `name` VARCHAR(50) NOT NULL COMMENT '名称',
    `key` VARCHAR(100) NOT NULL COMMENT 'key',
    `value` VARCHAR(1000) NOT NULL COMMENT 'value',
    remark VARCHAR(200) COMMENT '描述',
    create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    status_cd VARCHAR(2) NOT NULL DEFAULT '0' COMMENT '数据状态，详细参考c_status表，0在用，1失效'
);

INSERT c_mapping(domain,`name`,`key`,`value`,remark) VALUES('DOMAIN.COMMON','日志开关','LOG_ON_OFF','ON','日志开关');
INSERT c_mapping(domain,`name`,`key`,`value`,remark) VALUES('DOMAIN.COMMON','耗时开关','COST_TIME_ON_OFF','ON','耗时开关');
INSERT c_mapping(domain,`name`,`key`,`value`,remark) VALUES('DOMAIN.COMMON','规则开关','RULE_ON_OFF','OFF','规则开关');
INSERT c_mapping(domain,`name`,`key`,`value`,remark) VALUES('DOMAIN.COMMON','不调规则服务的订单类型','NO_NEED_RULE_VALDATE_ORDER','Q','不调规则服务的订单类型');
INSERT c_mapping(domain,`name`,`key`,`value`,remark) VALUES('DOMAIN.COMMON','不保存订单信息','NO_SAVE_ORDER','Q,T','不保存订单信息');
INSERT c_mapping(domain,`name`,`key`,`value`,remark) VALUES('DOMAIN.COMMON','不用调用 下游系统的配置','NO_INVOKE_BUSINESS_SYSTEM','Q','不用调用 下游系统的配置(一般不存在这种情况，这里主要是在没有下游系统的情况下测试中心服务用)');
INSERT c_mapping(domain,`name`,`key`,`value`,remark) VALUES('DOMAIN.COMMON','不用调用 作废下游系统的配置','NO_INVALID_BUSINESS_SYSTEM','Q','不用调用 作废下游系统的配置 (一般不存在这种情况，这里主要是在没有下游系统的情况下测试中心服务用)');
INSERT c_mapping(domain,`name`,`key`,`value`,remark) VALUES('DOMAIN.COMMON','需要调用服务生成各个ID','NEED_INVOKE_SERVICE_GENERATE_ID','OFF','需要调用服务生成各个ID');
INSERT c_mapping(domain,`name`,`key`,`value`,remark) VALUES('DOMAIN.COMMON','公钥','PUBLIC_STRING','MIGfMA0GCSqGSIb3DQEBAQUAA4GNADCBiQKBgQDloKXSBA5+tP39uS8yi5RZOs6Jdrt0znRQetyXX2l/IUCi1x1QAMgoZbnEavmdZ5jOZN/T1WYFbt/VomXEHaTdStAiYm3DCnxxy5CMMyRKai0+6n4lLJQpUmnAQPFENrOV8b70gBSBVjUXksImgui5qYaNqX90pjEzcyq+6CugBwIDAQAB','公钥');
INSERT c_mapping(domain,`name`,`key`,`value`,remark) VALUES('DOMAIN.COMMON','私钥','PRIVATE_STRING','MIICdgIBADANBgkqhkiG9w0BAQEFAASCAmAwggJcAgEAAoGBAJbtQYV+VpWZvifoc0R11MyAfIyMGoJKHDrWQau7oxLHotDDJM80o7ea7oL2onaHWOXaybpUp5FpZgjuixYMhlQOA/VXosrJOGJhgNv0dAL6VVXxmjlg2UWqIEoyTS7IzF3BuQCqy2k9aT7mGiC0RYRpndTuwe/0DKwFx70dIIIrAgMBAAECgYBMNMHnqLIJWZa1Sd6hy6lGFP5ObROZg9gbMUH5d4XQnrKsHEyCvz6HH5ic0fGYTaDqdn1zMvllJ8XYbrIV0P8lvHr9/LCnoXessmf61hKZyTKi5ycNkiPHTjmJZCoVTQFprcNgYeVX4cvNsqB2zWwzoAk8bbdWY6X5jB6YEpfBmQJBANiO9GiBtw+T9h60MpyV1xhJKsx0eCvxRZcsDB1OLZvQ7dHnsHmh0xUBL2MraHKnQyxOlrIzOtyttxSTrQzwcM0CQQCyajkzxpF6EjrXWHYVHb3AXFSoz5krjOkLEHegYlGhx0gtytBNVwftCn6hqtaxCxKMp00ZJoXIxo8d9tQy4N7XAkBljnTT5bEBnzPWpk7t298pRnbJtvz8LoOiJ0fvHlCJN+uvemXqRJeGzC165kpvKj14M8q7+wZpoxWukqqe3MspAkAuFYHw/blV7p+EQDU//w6kQTUc5YKK3TrUwMwlgT/UqcTbDyf+0hwZ/jv3RkluMY35Br3DYU/tLFyLQNZOzgbBAkEApWARXVlleEYbv8dPUL+56S0ky1hZSuPfVOBda4V3p0q18LjcHIyYcVhKGqkpii5JgblaYyjUriNDisFalCp8jQ==','私钥');
INSERT c_mapping(domain,`name`,`key`,`value`,remark) VALUES('DOMAIN.COMMON','外部应用公钥','PUBLIC_STRING','MIGfMA0GCSqGSIb3DQEBAQUAA4GNADCBiQKBgQCW7UGFflaVmb4n6HNEddTMgHyMjBqCShw61kGru6MSx6LQwyTPNKO3mu6C9qJ2h1jl2sm6VKeRaWYI7osWDIZUDgP1V6LKyThiYYDb9HQC+lVV8Zo5YNlFqiBKMk0uyMxdwbkAqstpPWk+5hogtEWEaZ3U7sHv9AysBce9HSCCKwIDAQAB','外部应用公钥');
INSERT c_mapping(domain,`name`,`key`,`value`,remark) VALUES('DOMAIN.COMMON','外部应用私钥','PRIVATE_STRING','MIICdgIBADANBgkqhkiG9w0BAQEFAASCAmAwggJcAgEAAoGBAOWgpdIEDn60/f25LzKLlFk6zol2u3TOdFB63JdfaX8hQKLXHVAAyChlucRq+Z1nmM5k39PVZgVu39WiZcQdpN1K0CJibcMKfHHLkIwzJEpqLT7qfiUslClSacBA8UQ2s5XxvvSAFIFWNReSwiaC6Lmpho2pf3SmMTNzKr7oK6AHAgMBAAECgYEAlfR5FVNM2/X6QC0k408/i53Zru94r2j7kGsLj1bhoAHpIe502AAKtkboL5rkc6Rpp688dCvRug6T4gFxj8cEF7rOOU4CHqVCHUHa4sWSDL2Rg7pMbQOVB7PGmM4C/hEgXcwM6tmLiU3xkkQDrpgT1bPpAm7iwDx4HkZBv1naYnECQQDyk40+KDvyUgp/r1tKbkMLoQOAfTZPXy+HgeAkU3PCUTFQlvn2OU6Fsl3Yjlp6utxPVnd00DoPZ8qvx1falaeLAkEA8lWoIDgyYwnibv2fr3A715PkieJ0exKfLb5lSD9UIfGJ9s7oTcltl7pprykfSP46heWjScS7YJRZHPfqb1/Y9QJAJNUQqjJzv7yDSZX3t5p8ZaSiIn1gpLagQeQPg5SETCoF4eW6uI9FA/nsU/hxdpcu4oEPjFYdqr8owH31MgRtNwJAaE+6qPPHrJ3qnAAMJoZXG/qLG1cg8IEZh6U3D5xC6MGBs31ovWMBC5iwOTeoQdE8+7nXSb+nMHFq0m9cuEg3qQJAH4caPSQ9RjVOP9on+niy9b1mATbvurepIB95KUtaHLz1hpihCLR7dTwrz8JOitgFE75Wzt4a00GZYxnaq3jsjA==','外部应用私钥');
INSERT c_mapping(domain,`name`,`key`,`value`,remark) VALUES('DOMAIN.COMMON','默认KEY_SIZE','DEFAULT_DECRYPT_KEY_SIZE','2048','默认KEY_SIZE');
INSERT c_mapping(domain,`name`,`key`,`value`,remark) VALUES('DOMAIN.COMMON','中心服务地址','CENTER_SERVICE_URL','http://center-service/httpApi/service','中心服务地址');
INSERT c_mapping(domain,`name`,`key`,`value`,remark) VALUES('DOMAIN.COMMON','控制中心APP_ID','CONSOLE_SERVICE_APP_ID','8000418002','控制中心APP_ID');
INSERT c_mapping(domain,`name`,`key`,`value`,remark) VALUES('DOMAIN.COMMON','控制服务加密开关','KEY_CONSOLE_SERVICE_SECURITY_ON_OFF','ON','控制服务加密开关');
INSERT c_mapping(domain,`name`,`key`,`value`,remark) VALUES('DOMAIN.COMMON','控制服务鉴权秘钥','CONSOLE_SECURITY_CODE','WEBURFPKIFJUHNCJUEIKMKJUJHULSMNCHDY89KMC','控制服务鉴权秘钥');
INSERT c_mapping(domain,`name`,`key`,`value`,remark) VALUES('DOMAIN.COMMON','编码生成服务地址','CODE_PATH','http://code-service/codeApi/generate','编码生成服务地址');

-- c_app
CREATE TABLE c_app(
    id INT NOT NULL AUTO_INCREMENT KEY COMMENT 'id',
    app_id VARCHAR(30) NOT NULL UNIQUE COMMENT '应用ID',
    `name` VARCHAR(50) NOT NULL COMMENT '名称 对应系统名称',
    security_code VARCHAR(64) NOT NULL COMMENT '签名码 sign签名时用',
    while_list_ip VARCHAR(200) COMMENT '白名单ip 多个之间用;隔开',
    black_list_ip VARCHAR(200) COMMENT '黑名单ip 多个之间用;隔开',
    remark VARCHAR(200) COMMENT '描述',
    create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    status_cd VARCHAR(2) NOT NULL DEFAULT '0' COMMENT '数据状态，详细参考c_status表，0在用，1失效',
    UNIQUE KEY (app_id)
);

-- c_service_sql
CREATE TABLE c_service_sql(
    id INT NOT NULL AUTO_INCREMENT KEY COMMENT 'id',
    service_code VARCHAR(50) NOT NULL UNIQUE COMMENT '对应c_service表',
    `name` VARCHAR(50) NOT NULL COMMENT '名称',
    params VARCHAR(500) NOT NULL COMMENT '参数',
    query_model VARCHAR(1) NOT NULL COMMENT '查询方式 1、sql,2、存储过程',
    `sql` LONGTEXT COMMENT '执行sql',
    proc VARCHAR(200) COMMENT '存储过程名称',
    java_script LONGTEXT COMMENT '执行java脚本代码',
    template LONGTEXT COMMENT '输出模板',
    remark VARCHAR(200) COMMENT '描述',
    create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    status_cd VARCHAR(2) NOT NULL DEFAULT '0' COMMENT '数据状态，详细参考c_status表，0在用，1失效'
);

-- 测试用

insert into c_order_type(order_type_cd,`name`,description) values('Q','查询单','查询单');
insert into c_order_type(order_type_cd,`name`,description) values('D','受理单','受理单');

insert into c_app(app_id,`name`,security_code,remark,status_cd)
values('8000418001','内部测试应用','WEBURFPKIFJUHNCJUEIKMKJUJHULSMNCHDY89KMC','记得删除','0');

insert into c_app(app_id,`name`,security_code,remark,status_cd)
values('8000418002','控制中心应用','WEBURFPKIFJUHNCJUEIKMKJUJHULSMNCHDY89KMC','控制中心应用','0');

insert into c_app(app_id,`name`,security_code,remark,status_cd)
values('8000418003','用户管理应用','WEBURFPKIFJUHNCJUEIKMKJUJHULSMNCHDY89KMC','用户管理应用','0');


insert into c_service(service_id,service_code,business_type_cd,`name`,seq,url,provide_app_id,status_cd)
values('1','query.user.userInfo','Q','用户信息查询',1,'http://...','8000418001','0');

insert into c_service(service_id,service_code,business_type_cd,`name`,seq,url,provide_app_id,status_cd)
values('2','query.order.orderInfo','Q','订单信息',1,'http://center-service/businessApi/query','8000418001','0');

insert into c_service(service_id,service_code,business_type_cd,`name`,seq,url,provide_app_id,status_cd)
values('3','query.console.menu','Q','查询菜单',1,'http://center-service/businessApi/query','8000418002','0');

insert into c_service(service_id,service_code,business_type_cd,`name`,seq,url,provide_app_id,status_cd)
values('4','query.user.loginInfo','Q','查询用户登录信息',1,'http://center-service/businessApi/query','8000418002','0');

INSERT INTO c_service(service_id,service_code,business_type_cd,`name`,seq,url,provide_app_id)
VALUES('5','query.console.template','Q','查询模板信息',1,'http://center-service/businessApi/query','8000418002');

INSERT INTO c_service(service_id,service_code,business_type_cd,`name`,seq,url,provide_app_id)
VALUES('6','query.console.templateCol','Q','查询列模板信息',1,'http://center-service/businessApi/query','8000418002');

INSERT INTO c_service(service_id,service_code,business_type_cd,`name`,seq,url,provide_app_id)
VALUES('7','query.center.mapping','Q','查询映射数据',1,'http://center-service/businessApi/query','8000418002');

INSERT INTO c_service(service_id,service_code,business_type_cd,`name`,seq,url,provide_app_id)
VALUES('8','query.center.apps','Q','查询外部应用',1,'http://center-service/businessApi/query','8000418002');

INSERT INTO c_service(service_id,service_code,business_type_cd,`name`,seq,url,provide_app_id)
VALUES('9','query.center.services','Q','查询服务',1,'http://center-service/businessApi/query','8000418002');

INSERT INTO c_service(service_id,service_code,business_type_cd,`name`,seq,url,provide_app_id)
VALUES('10','query.center.routes','Q','查询路由',1,'http://center-service/businessApi/query','8000418002');

INSERT INTO c_service(service_id,service_code,business_type_cd,`name`,seq,url,provide_app_id)
VALUES('11','flush.center.cache','Q','CenterService缓存',1,'http://center-service/cacheApi/flush','8000418002');

INSERT INTO c_service(service_id,service_code,business_type_cd,`name`,seq,url,provide_app_id)
VALUES('12','query.console.caches','Q','查询所有缓存',1,'http://center-service/businessApi/query','8000418002');

INSERT INTO c_service(service_id,service_code,business_type_cd,`name`,seq,url,provide_app_id)
VALUES('13','query.console.cache','Q','查询所有缓存',1,'http://center-service/businessApi/query','8000418002');


INSERT INTO c_service(service_id,service_code,business_type_cd,`name`,seq,url,provide_app_id)
VALUES('14','save.center.mapping','Q','保存映射信息',1,'http://center-service/businessApi/do','8000418002');

INSERT INTO c_service(service_id,service_code,business_type_cd,`name`,seq,url,provide_app_id)
VALUES('15','delete.center.mapping','Q','删除映射信息',1,'http://center-service/businessApi/do','8000418002');

INSERT INTO c_service(service_id,service_code,business_type_cd,`name`,seq,url,provide_app_id)
VALUES('16','update.center.mapping','Q','修改映射信息',1,'http://center-service/businessApi/do','8000418002');

INSERT INTO c_service(service_id,service_code,business_type_cd,`name`,seq,url,provide_app_id)
VALUES('17','save.user.userInfo','D','保存用户信息',1,'http://user-service/userApi/service','8000418003');

INSERT INTO c_service(service_id,service_code,business_type_cd,`name`,seq,url,provide_app_id)
VALUES('18','save.store.info','D','保存商户信息',1,'http://store-service/storeApi/service','8000418003');

INSERT INTO c_service(service_id,service_code,business_type_cd,`name`,seq,url,provide_app_id)
VALUES('19','update.store.info','D','修改商户信息',1,'http://store-service/storeApi/service','8000418003');

INSERT INTO c_service(service_id,service_code,business_type_cd,`name`,seq,url,provide_app_id)
VALUES('20','delete.store.info','D','删除商户信息',1,'http://store-service/storeApi/service','8000418003');


INSERT INTO c_service(service_id,service_code,business_type_cd,`name`,seq,url,provide_app_id)
VALUES('21','transfer.console.menu','T','透传菜单查询',1,'http://192.168.31.199:8001/userApi/service','8000418001');

INSERT INTO c_service(service_id,service_code,business_type_cd,`name`,seq,url,provide_app_id)
VALUES('22','save.shop.info','D','保存商品信息',1,'http://shop-service/shopApi/service','8000418003');

INSERT INTO c_service(service_id,service_code,business_type_cd,`name`,seq,url,provide_app_id)
VALUES('23','update.shop.info','D','修改商品信息',1,'http://shop-service/shopApi/service','8000418003');

INSERT INTO c_service(service_id,service_code,business_type_cd,`name`,seq,url,provide_app_id)
VALUES('24','delete.shop.info','D','删除商品信息',1,'http://shop-service/shopApi/service','8000418003');

INSERT INTO c_service(service_id,service_code,business_type_cd,`name`,seq,url,provide_app_id)
VALUES('25','buy.shop.info','D','购买商品信息',1,'http://shop-service/shopApi/service','8000418003');

INSERT INTO c_service(service_id,service_code,business_type_cd,`name`,seq,url,provide_app_id)
VALUES('26','save.shop.catalog','D','保存商品目录信息',1,'http://shop-service/shopApi/service','8000418003');

INSERT INTO c_service(service_id,service_code,business_type_cd,`name`,seq,url,provide_app_id)
VALUES('27','update.shop.catalog','D','修改商品目录信息',1,'http://shop-service/shopApi/service','8000418003');

INSERT INTO c_service(service_id,service_code,business_type_cd,`name`,seq,url,provide_app_id)
VALUES('28','delete.shop.catalog','D','删除商品目录信息',1,'http://shop-service/shopApi/service','8000418003');


INSERT INTO c_service(service_id,service_code,business_type_cd,`name`,seq,url,provide_app_id)
VALUES('29','save.comment.info','D','保存评论',1,'http://comment-service/commentApi/service','8000418003');

INSERT INTO c_service(service_id,service_code,business_type_cd,`name`,seq,url,provide_app_id)
VALUES('30','delete.comment.info','D','删除评论',1,'http://comment-service/commentApi/service','8000418003');


INSERT INTO c_service(service_id,service_code,business_type_cd,`name`,seq,url,provide_app_id)
VALUES('31','member.joined.store','D','商户成员加入',1,'http://store-service/storeApi/service','8000418003');

INSERT INTO c_service(service_id,service_code,business_type_cd,`name`,seq,url,provide_app_id)
VALUES('32','member.quit.store','D','商户成员退出',1,'http://store-service/storeApi/service','8000418003');

insert into c_route(app_id,service_id,invoke_model,order_type_cd,status_cd) values(
'8000418001','1','S','Q','0'
);

insert into c_route(app_id,service_id,invoke_model,order_type_cd,status_cd) values(
'8000418001','3','S','Q','0'
);

insert into c_route(app_id,service_id,invoke_model,order_type_cd,status_cd) values(
'8000418002','3','S','Q','0'
);


insert into c_route(app_id,service_id,invoke_model,order_type_cd,status_cd) values(
'8000418002','4','S','Q','0'
);

insert into c_route(app_id,service_id,invoke_model,order_type_cd,status_cd) values(
'8000418002','5','S','Q','0'
);

INSERT INTO c_route(app_id,service_id,invoke_model,order_type_cd,status_cd) VALUES(
'8000418002','6','S','Q','0'
);

INSERT INTO c_route(app_id,service_id,invoke_model,order_type_cd,status_cd) VALUES(
'8000418002','7','S','Q','0'
);

INSERT INTO c_route(app_id,service_id,invoke_model,order_type_cd,status_cd) VALUES(
'8000418002','8','S','Q','0'
);

INSERT INTO c_route(app_id,service_id,invoke_model,order_type_cd,status_cd) VALUES(
'8000418002','9','S','Q','0'
);


INSERT INTO c_route(app_id,service_id,invoke_model,order_type_cd,status_cd) VALUES(
'8000418002','10','S','Q','0'
);

INSERT INTO c_route(app_id,service_id,invoke_model,order_type_cd,status_cd) VALUES(
'8000418002','11','S','Q','0'
);

INSERT INTO c_route(app_id,service_id,invoke_model,order_type_cd,status_cd) VALUES(
'8000418002','12','S','Q','0'
);
INSERT INTO c_route(app_id,service_id,invoke_model,order_type_cd,status_cd) VALUES(
'8000418002','13','S','Q','0'
);

INSERT INTO c_route(app_id,service_id,invoke_model,order_type_cd,status_cd) VALUES(
'8000418002','14','S','Q','0'
);
INSERT INTO c_route(app_id,service_id,invoke_model,order_type_cd,status_cd) VALUES(
'8000418002','15','S','Q','0'
);

INSERT INTO c_route(app_id,service_id,invoke_model,order_type_cd,status_cd) VALUES(
'8000418002','16','S','Q','0'
);
INSERT INTO c_route(app_id,service_id,invoke_model,order_type_cd,status_cd) VALUES(
'8000418002','17','S','Q','0'
);

insert into c_route(app_id,service_id,invoke_model,order_type_cd,status_cd) values(
'8000418001','21','S','Q','0'
);

insert into c_route(app_id,service_id,invoke_model,order_type_cd,status_cd) values(
'8000418001','22','S','Q','0'
);

insert into c_route(app_id,service_id,invoke_model,order_type_cd,status_cd) values(
'8000418001','23','S','Q','0'
);

insert into c_route(app_id,service_id,invoke_model,order_type_cd,status_cd) values(
'8000418001','24','S','Q','0'
);
insert into c_route(app_id,service_id,invoke_model,order_type_cd,status_cd) values(
'8000418001','25','S','Q','0'
);
insert into c_route(app_id,service_id,invoke_model,order_type_cd,status_cd) values(
'8000418001','26','S','Q','0'
);
insert into c_route(app_id,service_id,invoke_model,order_type_cd,status_cd) values(
'8000418001','27','S','Q','0'
);






INSERT INTO c_service_sql(service_code,`name`,params,query_model,`sql`,proc,template,remark,status_cd)
VALUES('query.order.orderInfo','订单信息','oId','1','{
                                                 	"param1":"SELECT co.app_id appId,co.create_time createTime,co.ext_transaction_id extTransactionId,co.finish_time finishTime
                                                 ,co.order_type_cd orderTypeCd,co.o_id oId ,co.remark remark ,co.request_time requestTime ,co.user_id userId,co.status_cd statusCd
                                                  FROM c_orders co WHERE co.o_id = #oId#",
                                                  "param2":"SELECT cb.b_id bId, cb.business_type_cd businessTypeCd,cb.create_time createTime,cb.finish_time finishTime ,cb.o_id oId,
                                                 cb.remark remark,cb.status_cd statusCd FROM c_business cb WHERE cb.o_id = #oId#"
                                                 }','','{"PARAM:"{
                                                            "param1": "$.#order#Object",
                                                            "param2": "$.#business#Array"
                                                            },"TEMPLATE":"{
                                                         "response": {
                                                           "code": "0000",
                                                           "message": "成功"
                                                         }
                                                       }"}','','0');

INSERT INTO c_service_sql(service_code,`name`,params,query_model,`sql`,proc,template,remark,status_cd)
VALUES('query.console.menu','查询菜单','manageId,menuGroup','1','{
                                                 	"param1":"select mm.m_id mId,mm.name name,mm.level level,mm.parent_id parentId,mm.menu_group menuGroup,mm.user_id userId,mm.create_time createTime,
                                                              mm.remark remark,mmc.url url,mmc.template template
                                                              from m_menu_2_user mm2u,m_menu mm, m_menu_ctg mmc
                                                              where mm2u.user_id = #manageId#
                                                              and mm2u.m_id = mm.m_id
                                                              AND mm.menu_group = #menuGroup#
                                                              and mm2u.status_cd = ''0''
                                                              and mm.status_cd = ''0''
                                                              and mmc.m_id = mm.m_id
                                                              and mmc.status_cd = ''0''
                                                              order by mm.seq asc"
                                                 }','','{"PARAM":{
                                                            "param1": "$.#menus#Array"
                                                            },"TEMPLATE":{
                                                         "response": {
                                                           "code": "0000",
                                                           "message": "成功"
                                                         }
                                                       }}','','0');

INSERT INTO c_service_sql(service_code,`name`,params,query_model,`sql`,proc,template,remark,status_cd)
VALUES('query.user.loginInfo','查询用户登录信息','userCode','1','{
                                                 	"param1":"SELECT ''10001'' userId,''admin'' userName,''d57167e07915c9428b1c3aae57003807'' userPwd FROM DUAL WHERE #userCode#=''admin''"
                                                 }','','{"PARAM":{
                                                            "param1": "$.#user#Object"
                                                            },"TEMPLATE":{
                                                         "response": {
                                                           "code": "0000",
                                                           "message": "成功"
                                                         }
                                                       }}','','0');

INSERT INTO c_service_sql(service_code,`name`,params,query_model,`sql`,proc,template,remark,status_cd)
VALUES('query.console.template','查询模板信息','templateCode','1','{
                                                 	"param1":"SELECT t.template_code templateCode,t.name templateName,t.`html_name` htmlName,t.`url` templateUrl
                                                              FROM c_template t WHERE t.status_cd = ''0'' AND t.template_code = #templateCode#"
                                                 }','','{"PARAM":{
                                                            "param1": "$.#template#Object"
                                                            },"TEMPLATE":{
                                                         "response": {
                                                           "code": "0000",
                                                           "message": "成功"
                                                         }
                                                       }}','','0');


INSERT INTO c_service_sql(service_code,`name`,params,query_model,`sql`,proc,template,remark,status_cd)
VALUES('query.console.templateCol','查询模板列信息','templateCode','1','{
                                                 	"param1":"SELECT t.template_code templateCode,t.name templateName,tc.col_name colName,tc.col_model colModel FROM c_template t,c_template_col tc WHERE t.status_cd = ''0'' AND t.template_code = tc.template_code
                                                              AND tc.status_cd = ''0''
                                                              AND t.template_code = #templateCode#"
                                                 }','','{"PARAM":{
                                                            "param1": "$.#template#Array"
                                                            },"TEMPLATE":{
                                                         "response": {
                                                           "code": "0000",
                                                           "message": "成功"
                                                         }
                                                       }}','','0');

INSERT INTO c_service_sql(service_code,`name`,params,query_model,`sql`,proc,template,remark,status_cd)
VALUES('query.center.mapping','查询映射数据','page,rows,sord','1','{"param1":"select count(1) records,ceil(#page#/#rows#)+1 page,ceil(count(1)/#rows#) total from c_mapping m where m.status_cd = ''0''",
                                                             "param2":"SELECT m.`id` id,m.`domain` domain,m.name name,m.`key`  `key` ,m.`value` `value`,m.`remark` remark FROM c_mapping m WHERE m.`status_cd` = ''0'' LIMIT #page#, #rows#"
                                                             }','','{
                                                        	"PARAM": {
                                                        		"param1": "$.##Object",
                                                        		"param2": "$.#rows#Array"
                                                        	},
                                                        	"TEMPLATE": {
                                                        		"response": {
                                                        			"code": "0000",
                                                        			"message": "成功"
                                                        		}
                                                        	}
                                                        }','','0');
INSERT INTO c_service_sql(service_code,`name`,params,query_model,`sql`,proc,template,remark,status_cd)
VALUES('query.center.apps','查询外部应用','page,rows,sord','1','{"param1":"select count(1) records,ceil(#page#/#rows#)+1 page,ceil(count(1)/#rows#) total from c_app a where a.status_cd = ''0''",
                                                             "param2":"SELECT m.`id` id,m.`app_id` appId,m.name `name`,m.`security_code`  securityCode ,m.`while_list_ip` whileListIp,m.`black_list_ip` blackListIp,m.`remark` remark FROM c_app m WHERE m.`status_cd` = ''0'' LIMIT #page#, #rows#"
                                                             }','','{
                                                        	"PARAM": {
                                                        		"param1": "$.##Object",
                                                        		"param2": "$.#rows#Array"
                                                        	},
                                                        	"TEMPLATE": {
                                                        		"response": {
                                                        			"code": "0000",
                                                        			"message": "成功"
                                                        		}
                                                        	}
                                                        }','','0');

INSERT INTO c_service_sql(service_code,`name`,params,query_model,`sql`,proc,template,remark,status_cd)
VALUES('query.center.services','查询服务数据','page,rows,sord','1','{"param1":"select count(1) records,ceil(#page#/#rows#)+1 page,ceil(count(1)/#rows#) total from c_service a where a.status_cd = ''0''",
                                                             "param2":"SELECT s.`service_id` serviceId,s.`service_code` serviceCode,s.`business_type_cd`  businessTypeCd,s.name `name`,s.is_instance isInstance,
                                                                       s.`messageQueueName` messageQueueName,s.url url,s.`provide_app_id` provideAppId FROM c_service s WHERE s.`status_cd` = ''0'' LIMIT #page#, #rows#"
                                                             }','','{
                                                        	"PARAM": {
                                                        		"param1": "$.##Object",
                                                        		"param2": "$.#rows#Array"
                                                        	},
                                                        	"TEMPLATE": {
                                                        		"response": {
                                                        			"code": "0000",
                                                        			"message": "成功"
                                                        		}
                                                        	}
                                                        }','','0');

INSERT INTO c_service_sql(service_code,`name`,params,query_model,`sql`,proc,template,remark,status_cd)
VALUES('query.center.route','查询路由数据','page,rows,sord','1','{"param1":"select count(1) records,ceil(#page#/#rows#)+1 page,ceil(count(1)/#rows#) total from c_route a,c_service cs WHERE a.`service_id` = cs.`service_id` AND cs.`status_cd` = ''0'' and a.status_cd = ''0''",
                                                             "param2":"SELECT s.id id,s.`app_id` appId,s.`service_id` serviceId,s.`invoke_model` invokeModel,cs.`name` serviceName,cs.`service_code` serviceCode,s.`order_type_cd` orderTypeCd,s.`invoke_limit_times` invokelimitTimes FROM c_route s,c_service cs WHERE s.`service_id` = cs.`service_id` AND cs.`status_cd` = ''0'' AND s.`status_cd` = ''0'' LIMIT #page#, #rows#"
                                                             }','','{
                                                        	"PARAM": {
                                                        		"param1": "$.##Object",
                                                        		"param2": "$.#rows#Array"
                                                        	},
                                                        	"TEMPLATE": {
                                                        		"response": {
                                                        			"code": "0000",
                                                        			"message": "成功"
                                                        		}
                                                        	}
                                                        }','','0');

INSERT INTO c_service_sql(service_code,`name`,params,query_model,`sql`,proc,template,remark,status_cd)
VALUES('query.console.caches','查询缓存数据','userId','1','{
                                                             "param1":"SELECT c.id id,c.`cache_code` cacheCode, c.`name` cacheName FROM c_cache c, c_cache_2_user c2u WHERE c.`cache_code` = c2u.`cache_code` AND c.`status_cd` = ''0''
                                                                       AND c2u.`status_cd` = ''0'' AND c2u.`user_id` = #userId# AND c.`group` = ''COMMON'' ORDER BY c.`seq` ASC"
                                                             }','','{
                                                        	"PARAM": {
                                                        		"param1": "$.#rows#Array"
                                                        	},
                                                        	"TEMPLATE": {
                                                        		"response": {
                                                        			"code": "0000",
                                                        			"message": "成功"
                                                        		}
                                                        	}
                                                        }','','0');
INSERT INTO c_service_sql(service_code,`name`,params,query_model,`sql`,proc,template,remark,status_cd)
VALUES('query.console.cache','查询单条缓存信息','cacheCode','1','{
                                                             "param1":"SELECT c.id id,c.`cache_code` cacheCode, c.`name` cacheName,c.`param` param,c.`service_code` serviceCode FROM c_cache c WHERE  c.`status_cd` = ''0'' AND c.`cache_code` = #cacheCode#"
                                                             }','','{
                                                        	"PARAM": {
                                                        		"param1": "$.#cache#Object"
                                                        	},
                                                        	"TEMPLATE": {
                                                        		"response": {
                                                        			"code": "0000",
                                                        			"message": "成功"
                                                        		}
                                                        	}
                                                        }','','0');


INSERT INTO c_service_sql(service_code,`name`,params,query_model,`sql`,proc,template,remark,status_cd)
VALUES('save.center.mapping','保存映射信息','domain,name,key,value,remark','1','{
                                                             "param1":"INSERT c_mapping(domain,`name`,`key`,`value`,remark) VALUES(#domain#,#name#,#key#,#value#,#remark#)"
                                                             }','','{
                                                        	"TEMPLATE": {
                                                        		"response": {
                                                        			"code": "0000",
                                                        			"message": "成功"
                                                        		}
                                                        	}
                                                        }','','0');
INSERT INTO c_service_sql(service_code,`name`,params,query_model,`sql`,proc,template,remark,status_cd)
VALUES('delete.center.mapping','删除映射信息','id','1','{
                                                             "param1":"UPDATE c_mapping m SET m.status_cd = ''1'' WHERE m.status_cd = ''0'' AND m.id = #id#"
                                                             }','','{
                                                        	"TEMPLATE": {
                                                        		"response": {
                                                        			"code": "0000",
                                                        			"message": "成功"
                                                        		}
                                                        	}
                                                        }','','0');
INSERT INTO c_service_sql(service_code,`name`,params,query_model,`sql`,proc,template,remark,status_cd)
VALUES('update.center.mapping','修改映射信息','id,domain,name,key,value,remark','1','{
                                                             "param1":"UPDATE c_mapping m SET m.domain=#domain#,m.name = #name#,m.key=#key#,m.value=#value#,m.remark=#remark# WHERE m.status_cd = ''0'' AND m.id = #id#"
                                                             }','','{
                                                        	"TEMPLATE": {
                                                        		"response": {
                                                        			"code": "0000",
                                                        			"message": "成功"
                                                        		}
                                                        	}
                                                        }','','0');

create table c_comment(
    comment_id VARCHAR(30) NOT NULL COMMENT '评论ID',
    user_id varchar(30) not null comment '评论者用户ID',
    b_id VARCHAR(30) NOT NULL COMMENT '订单ID',
    comment_type_cd varchar(2) not null default 'S' comment '评论类型 S表示 商品 M表示 商户 T 物流',
    out_id varchar(30) not null comment '外部ID，如商品ID 商户ID等',
    `month` INT NOT NULL COMMENT '月份',
    create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    status_cd VARCHAR(2) NOT NULL DEFAULT '0' COMMENT '数据状态，详细参考c_status表，S 保存，0, 在用 1失效'
)
PARTITION BY RANGE (`month`) (
    PARTITION comment_1 VALUES LESS THAN (2),
    PARTITION comment_2 VALUES LESS THAN (3),
    PARTITION comment_3 VALUES LESS THAN (4),
    PARTITION comment_4 VALUES LESS THAN (5),
    PARTITION comment_5 VALUES LESS THAN (6),
    PARTITION comment_6 VALUES LESS THAN (7),
    PARTITION comment_7 VALUES LESS THAN (8),
    PARTITION comment_8 VALUES LESS THAN (9),
    PARTITION comment_9 VALUES LESS THAN (10),
    PARTITION comment_10 VALUES LESS THAN (11),
    PARTITION comment_11 VALUES LESS THAN (12),
    PARTITION comment_12 VALUES LESS THAN (13)
);

CREATE INDEX idx_comment_b_id ON c_comment(b_id);
CREATE INDEX idx_comment_out_id ON c_comment(out_id);
-- 评论子表
create table c_sub_comment(
    sub_comment_id varchar(30) not null comment '子评论ID',
    comment_id varchar(30) not null  comment '评论ID ',
    b_id VARCHAR(30) NOT NULL COMMENT '订单ID',
    parent_sub_comment_id varchar(30) not null default '-1' comment '父 子评论ID 如果不是回复 写成-1',
    sub_comment_type_cd varchar(2) not null default 'C' comment '评论类型 C 评论 R 回复 A 追加',
    comment_context LONGTEXT not null COMMENT '评论内容',
    `month` INT NOT NULL COMMENT '月份',
    create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    status_cd VARCHAR(2) NOT NULL DEFAULT '0' COMMENT '数据状态，详细参考c_status表，S 保存，0, 在用 1失效'
)
PARTITION BY RANGE (`month`) (
    PARTITION sub_comment_1 VALUES LESS THAN (2),
    PARTITION sub_comment_2 VALUES LESS THAN (3),
    PARTITION sub_comment_3 VALUES LESS THAN (4),
    PARTITION sub_comment_4 VALUES LESS THAN (5),
    PARTITION sub_comment_5 VALUES LESS THAN (6),
    PARTITION sub_comment_6 VALUES LESS THAN (7),
    PARTITION sub_comment_7 VALUES LESS THAN (8),
    PARTITION sub_comment_8 VALUES LESS THAN (9),
    PARTITION sub_comment_9 VALUES LESS THAN (10),
    PARTITION sub_comment_10 VALUES LESS THAN (11),
    PARTITION sub_comment_11 VALUES LESS THAN (12),
    PARTITION sub_comment_12 VALUES LESS THAN (13)
);

CREATE INDEX idx_sub_comment_b_id ON c_sub_comment(b_id);
CREATE INDEX idx_sub_comment_comment_id ON c_sub_comment(comment_id);
CREATE INDEX idx_sub_comment_parent_sub_comment_id ON c_sub_comment(parent_sub_comment_id);

-- 属性
create table c_sub_comment_attr(
    attr_id VARCHAR(30) NOT NULL COMMENT '属性id',
    sub_comment_id VARCHAR(30) NOT NULL COMMENT '子评论ID',
    b_id VARCHAR(30) NOT NULL COMMENT '订单ID',
    spec_cd VARCHAR(12) NOT NULL COMMENT '规格id,参考spec表',
    value VARCHAR(50) NOT NULL COMMENT '属性值',
    `month` INT NOT NULL COMMENT '月份',
    create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    status_cd VARCHAR(2) NOT NULL DEFAULT '0' COMMENT '数据状态，详细参考c_status表，S 保存，0, 在用 1失效'
)
PARTITION BY RANGE (`month`) (
    PARTITION sub_comment_attr_1 VALUES LESS THAN (2),
    PARTITION sub_comment_attr_2 VALUES LESS THAN (3),
    PARTITION sub_comment_attr_3 VALUES LESS THAN (4),
    PARTITION sub_comment_attr_4 VALUES LESS THAN (5),
    PARTITION sub_comment_attr_5 VALUES LESS THAN (6),
    PARTITION sub_comment_attr_6 VALUES LESS THAN (7),
    PARTITION sub_comment_attr_7 VALUES LESS THAN (8),
    PARTITION sub_comment_attr_8 VALUES LESS THAN (9),
    PARTITION sub_comment_attr_9 VALUES LESS THAN (10),
    PARTITION sub_comment_attr_10 VALUES LESS THAN (11),
    PARTITION sub_comment_attr_11 VALUES LESS THAN (12),
    PARTITION sub_comment_attr_12 VALUES LESS THAN (13)
);

CREATE INDEX idx_sub_comment_attr_b_id ON c_sub_comment_attr(b_id);
CREATE INDEX idx_sub_comment_attr_sub_comment_id ON c_sub_comment_attr(sub_comment_id);
CREATE INDEX idx_sub_comment_attr_spec_cd ON c_sub_comment_attr(spec_cd);



-- 评论 照片
CREATE TABLE c_sub_comment_photo(
    comment_photo_id VARCHAR(30) NOT NULL COMMENT '评论照片ID',
    b_id VARCHAR(30) NOT NULL COMMENT '业务Id',
    sub_comment_id VARCHAR(30) NOT NULL COMMENT '商店ID',
    comment_photo_type_cd VARCHAR(12) NOT NULL default 'S' COMMENT '评论照片类型,S 商品照片 M 商户ID',
    photo VARCHAR(100) NOT NULL COMMENT '照片',
    `month` INT NOT NULL COMMENT '月份',
    create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    status_cd VARCHAR(2) NOT NULL DEFAULT '0' COMMENT '数据状态，详细参考c_status表，S 保存，0, 在用 1失效'
)
PARTITION BY RANGE (`month`) (
    PARTITION sub_comment_photo_1 VALUES LESS THAN (2),
    PARTITION sub_comment_photo_2 VALUES LESS THAN (3),
    PARTITION sub_comment_photo_3 VALUES LESS THAN (4),
    PARTITION sub_comment_photo_4 VALUES LESS THAN (5),
    PARTITION sub_comment_photo_5 VALUES LESS THAN (6),
    PARTITION sub_comment_photo_6 VALUES LESS THAN (7),
    PARTITION sub_comment_photo_7 VALUES LESS THAN (8),
    PARTITION sub_comment_photo_8 VALUES LESS THAN (9),
    PARTITION sub_comment_photo_9 VALUES LESS THAN (10),
    PARTITION sub_comment_photo_10 VALUES LESS THAN (11),
    PARTITION sub_comment_photo_11 VALUES LESS THAN (12),
    PARTITION sub_comment_photo_12 VALUES LESS THAN (13)
);
CREATE INDEX idx_sub_comment_photo_b_id ON c_sub_comment_photo(b_id);
CREATE INDEX idx_sub_comment_photo_sub_comment_id ON c_sub_comment_photo(sub_comment_id);
-- 评论分数
CREATE TABLE c_comment_score(
    comment_score_id VARCHAR(30) NOT NULL COMMENT '评论分数ID',
    comment_id VARCHAR(30) NOT NULL COMMENT '评论ID',
    b_id VARCHAR(30) NOT NULL COMMENT '业务Id',
    score_type_cd VARCHAR(30) NOT NULL COMMENT '打分类别，S 商品相符，U 卖家打分，T 物流打分',
    `value` INT NOT NULL COMMENT '分数',
    `month` INT NOT NULL COMMENT '月份',
    create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    status_cd VARCHAR(2) NOT NULL DEFAULT '0' COMMENT '数据状态，详细参考c_status表，S 保存，0, 在用 1失效'
)
PARTITION BY RANGE (`month`) (
    PARTITION comment_score_1 VALUES LESS THAN (2),
    PARTITION comment_score_2 VALUES LESS THAN (3),
    PARTITION comment_score_3 VALUES LESS THAN (4),
    PARTITION comment_score_4 VALUES LESS THAN (5),
    PARTITION comment_score_5 VALUES LESS THAN (6),
    PARTITION comment_score_6 VALUES LESS THAN (7),
    PARTITION comment_score_7 VALUES LESS THAN (8),
    PARTITION comment_score_8 VALUES LESS THAN (9),
    PARTITION comment_score_9 VALUES LESS THAN (10),
    PARTITION comment_score_10 VALUES LESS THAN (11),
    PARTITION comment_score_11 VALUES LESS THAN (12),
    PARTITION comment_score_12 VALUES LESS THAN (13)
);

CREATE INDEX idx_comment_score_b_id ON c_comment_score(b_id);
CREATE INDEX idx_comment_score_comment_id ON c_comment_score(comment_id);


create table m_menu(
    m_id INT NOT NULL AUTO_INCREMENT KEY COMMENT '菜单ID',
    name varchar(10) not null comment '菜单名称',
    level varchar(2) not null comment '菜单级别 一级菜单 为 1 二级菜单为2',
    parent_id int not null comment '父类菜单id 如果是一类菜单则写为-1 如果是二类菜单则写父类的菜单id',
    menu_group varchar(10) not null comment '菜单组 left 显示在页面左边的菜单',
    user_id varchar(12) not null comment '创建菜单的用户id',
    remark VARCHAR(200) COMMENT '描述',
    seq INT NOT NULL  COMMENT '列顺序',
    create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    status_cd VARCHAR(2) NOT NULL DEFAULT '0' COMMENT '数据状态，详细参考c_status表，0在用，1失效'
);

-- m_menu_ctg

create table m_menu_ctg(
    m_ctg_id INT NOT NULL AUTO_INCREMENT KEY COMMENT '菜单配置ID',
    m_id INT NOT NULL COMMENT '菜单ID',
    url varchar(100) not null comment '菜单打开地址',
    template varchar(50) comment '页面模板 模板名称',
    create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    status_cd VARCHAR(2) NOT NULL DEFAULT '0' COMMENT '数据状态，详细参考c_status表，0在用，1失效'
);

-- m_menu_2_user

create table m_menu_2_user(
    m_user_id INT NOT NULL AUTO_INCREMENT KEY COMMENT '菜单用户ID',
    m_id int not null comment '菜单id',
    user_id varchar(30) not null comment '用户id',
    create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    status_cd VARCHAR(2) NOT NULL DEFAULT '0' COMMENT '数据状态，详细参考c_status表，0在用，1失效'
);

insert into m_menu(name,level,parent_id,menu_group,user_id,remark,seq) values('系统配置','1','-1','LEFT','10001','',1);
insert into m_menu(name,level,parent_id,menu_group,user_id,remark,seq) values('映射管理','2','1','LEFT','10001','',2);
insert into m_menu(name,level,parent_id,menu_group,user_id,remark,seq) values('外部应用','2','1','LEFT','10001','',3);
insert into m_menu(name,level,parent_id,menu_group,user_id,remark,seq) values('路由管理','2','1','LEFT','10001','',4);
insert into m_menu(name,level,parent_id,menu_group,user_id,remark,seq) values('服务管理','2','1','LEFT','10001','',5);
insert into m_menu(name,level,parent_id,menu_group,user_id,remark,seq) values('缓存管理','1','-1','LEFT','10001','',1);
insert into m_menu(name,level,parent_id,menu_group,user_id,remark,seq) values('刷新缓存','2','1','LEFT','10001','',2);

insert into m_menu_ctg(m_id,url,template) values(1,'#','');
insert into m_menu_ctg(m_id,url,template) values(2,'/console/list?templateCode=mapping','');
insert into m_menu_ctg(m_id,url,template) values(3,'/console/list?templateCode=app','');
insert into m_menu_ctg(m_id,url,template) values(4,'/console/list?templateCode=service','');
insert into m_menu_ctg(m_id,url,template) values(5,'/console/list?templateCode=route','');
insert into m_menu_ctg(m_id,url,template) values(6,'#','');
insert into m_menu_ctg(m_id,url,template) values(7,'/','');


insert into m_menu_2_user(m_id,user_id) values(1,'10001');
insert into m_menu_2_user(m_id,user_id) values(2,'10001');
insert into m_menu_2_user(m_id,user_id) values(3,'10001');
insert into m_menu_2_user(m_id,user_id) values(4,'10001');
insert into m_menu_2_user(m_id,user_id) values(5,'10001');
insert into m_menu_2_user(m_id,user_id) values(6,'10001');
insert into m_menu_2_user(m_id,user_id) values(7,'10001');

-- c_template
create table c_template(
    id INT NOT NULL AUTO_INCREMENT KEY COMMENT '模板ID',
    template_code varchar(20) not null UNIQUE comment '模板编码 模板英文名',
    name varchar(50) not null comment '模板名称',
    html_name varchar(20) not null comment '对应HTML文件名称',
    url varchar(200) not null comment '查询数据，修改数据url 其真实地址对应于mapping表中 LIST->key 对应 查询多条数据 QUERY->key 对应单条数据 UPDATE-> 对应修改数据 DELETE->key 对应删除数据 多条之间用 ; 分隔',
    create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    status_cd VARCHAR(2) NOT NULL DEFAULT '0' COMMENT '数据状态，详细参考c_status表，0在用，1失效'
);

-- c_template_col

create table c_template_col(
    id INT NOT NULL AUTO_INCREMENT KEY COMMENT '模板ID',
    template_code varchar(20) not null comment '模板编码 模板英文名',
    col_name varchar(50) not null comment '前台显示名称',
    col_code varchar(20) not null comment '字段的编码',
    col_model longtext not null comment 'jqgrid的colmodel',
    seq INT NOT NULL  COMMENT '列顺序',
    create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    status_cd VARCHAR(2) NOT NULL DEFAULT '0' COMMENT '数据状态，详细参考c_status表，0在用，1失效'
);


insert into c_template(template_code,name,html_name,url) values('mapping','映射管理','list_template','LIST->query.center.mapping;QUERY->mapping_query_url;INSERT->save.center.mapping;UPDATE->update.center.mapping;DELETE->delete.center.mapping');

insert into c_template_col(template_code,col_name,col_code,col_model,seq) values('mapping','列ID','id','{ "name": "id","index": "id","width": "90",
                                                                                                             "editable": true,
                                                                                                             "sorttype": "int" }',1);
insert into c_template_col(template_code,col_name,col_code,col_model,seq) values('mapping','域','domain','{ "name": "domain","index": "domain","width": "90",
                                                                                                             "editable": true,
                                                                                                             "formatoptions": { "defaultValue": "DOMAIN.COMMON" }
                                                                                                           }',2);
insert into c_template_col(template_code,col_name,col_code,col_model,seq) values('mapping','名称','name','{ "name": "name","index": "name","width": "90",
                                                                                                             "editable": true }',3);

insert into c_template_col(template_code,col_name,col_code,col_model,seq) values('mapping','键','key','{ "name": "key","index": "key","width": "90",
                                                                                                             "editable": true }',4);

insert into c_template_col(template_code,col_name,col_code,col_model,seq) values('mapping','值','value','{ "name": "value","index": "value","width": "90",
                                                                                                             "editable": true }',5);
insert into c_template_col(template_code,col_name,col_code,col_model,seq) values('mapping','备注','value','{ "name": "remark","index": "remark","width": "90",
                                                                                                             "editable": true }',6);

INSERT INTO c_template_col(template_code,col_name,col_code,col_model,seq) VALUES('mapping','BUTTON','BUTTON','{
                                                                                                            "name": "detail",
                                                                                                            "index": "",
                                                                                                            "width": "40",
                                                                                                            "fixed": "true",
                                                                                                            "sortable": "false",
                                                                                                            "resize": "false",
                                                                                                            "formatter": "function(cellvalue, options, rowObject){\n var temp =\"<div style=''margin-left:8px;''><div title=''详情记录'' style=''float:left;cursor:pointer;'' class=''ui-pg-div'' id=''jEditButton_3'' onclick=''detail(\"+rowObject+\")'' onmouseover=''jQuery(this).addClass(''ui-state-hover'');'' onmouseout=''jQuery(this).removeClass(''ui-state-hover'');''><span class=''ui-icon fa-search-plus''/></div></div>\";\n return temp; \n}"
                                                                                                          }',7);


insert into c_template(template_code,name,html_name,url) values('app','外部应用','list_template','LIST->query.center.apps;QUERY->query.center.app');

insert into c_template_col(template_code,col_name,col_code,col_model,seq) values('app','列ID','id','{ "name": "id","index": "id","width": "20",
                                                                                                             "editable": true,
                                                                                                             "sorttype": "int" }',1);
insert into c_template_col(template_code,col_name,col_code,col_model,seq) values('app','AppId','domain','{ "name": "appId","index": "appId","width": "40",
                                                                                                             "editable": true
                                                                                                           }',2);
insert into c_template_col(template_code,col_name,col_code,col_model,seq) values('app','名称','name','{ "name": "name","index": "name","width": "50",
                                                                                                             "editable": true }',3);

insert into c_template_col(template_code,col_name,col_code,col_model,seq) values('app','秘钥','securityCode','{ "name": "securityCode","index": "securityCode","width": "50",
                                                                                                             "editable": true }',4);

insert into c_template_col(template_code,col_name,col_code,col_model,seq) values('app','白名单','whileListIp','{ "name": "whileListIp","index": "whileListIp","width": "90",
                                                                                                             "editable": true }',5);

insert into c_template_col(template_code,col_name,col_code,col_model,seq) values('app','黑名单','blackListIp','{ "name": "blackListIp","index": "blackListIp","width": "40",
                                                                                                             "editable": true }',6);

insert into c_template_col(template_code,col_name,col_code,col_model,seq) values('app','备注','value','{ "name": "remark","index": "remark","width": "90",
                                                                                                             "editable": true }',7);

INSERT INTO c_template_col(template_code,col_name,col_code,col_model,seq) VALUES('app','BUTTON','BUTTON','{
                                                                                                            "name": "detail",
                                                                                                            "index": "",
                                                                                                            "width": "40",
                                                                                                            "fixed": "true",
                                                                                                            "sortable": "false",
                                                                                                            "resize": "false",
                                                                                                            "formatter": "function(cellvalue, options, rowObject){\n var temp =\"<div style=''margin-left:8px;''><div title=''详情记录'' style=''float:left;cursor:pointer;'' class=''ui-pg-div'' id=''jEditButton_3'' onclick=''detail(\"+rowObject+\")'' onmouseover=''jQuery(this).addClass(''ui-state-hover'');'' onmouseout=''jQuery(this).removeClass(''ui-state-hover'');''><span class=''ui-icon fa-search-plus''/></div></div>\";\n return temp; \n}"
                                                                                                          }',8);


insert into c_template(template_code,name,html_name,url) values('service','服务管理','list_template','LIST->query.center.services;QUERY->query.center.service');

insert into c_template_col(template_code,col_name,col_code,col_model,seq) values('service','服务ID','serviceId','{ "name": "serviceId","index": "serviceId","width": "20",
                                                                                                             "editable": true,
                                                                                                             "sorttype": "int" }',1);
insert into c_template_col(template_code,col_name,col_code,col_model,seq) values('service','服务编码','serviceCode','{ "name": "serviceCode","index": "serviceCode","width": "40",
                                                                                                             "editable": true
                                                                                                           }',2);

insert into c_template_col(template_code,col_name,col_code,col_model,seq) values('service','业务类型','businessTypeCd','{ "name": "businessTypeCd","index": "businessTypeCd","width": "50",
                                                                                                             "editable": true }',3);

insert into c_template_col(template_code,col_name,col_code,col_model,seq) values('service','名称','name','{ "name": "name","index": "name","width": "40",
                                                                                                             "editable": true }',4);

insert into c_template_col(template_code,col_name,col_code,col_model,seq) values('service','消息队列','messageQueueName','{ "name": "messageQueueName","index": "messageQueueName","width": "10",
                                                                                                             "editable": true }',5);
insert into c_template_col(template_code,col_name,col_code,col_model,seq) values('service','需要Instance','isInstance','{ "name": "isInstance","index": "isInstance","width": "10",
                                                                                                             "editable": true }',6);

insert into c_template_col(template_code,col_name,col_code,col_model,seq) values('service','URL','url','{ "name": "url","index": "url","width": "60",
                                                                                                             "editable": true }',7);

insert into c_template_col(template_code,col_name,col_code,col_model,seq) values('service','提供者AppId','provideAppId','{ "name": "provideAppId","index": "provideAppId","width": "10",
                                                                                                             "editable": true }',8);

INSERT INTO c_template_col(template_code,col_name,col_code,col_model,seq) VALUES('service','BUTTON','BUTTON','{
                                                                                                            "name": "detail",
                                                                                                            "index": "",
                                                                                                            "width": "40",
                                                                                                            "fixed": "true",
                                                                                                            "sortable": "false",
                                                                                                            "resize": "false",
                                                                                                            "formatter": "function(cellvalue, options, rowObject){\n var temp =\"<div style=''margin-left:8px;''><div title=''详情记录'' style=''float:left;cursor:pointer;'' class=''ui-pg-div'' id=''jEditButton_3'' onclick=''detail(\"+rowObject+\")'' onmouseover=''jQuery(this).addClass(''ui-state-hover'');'' onmouseout=''jQuery(this).removeClass(''ui-state-hover'');''><span class=''ui-icon fa-search-plus''/></div></div>\";\n return temp; \n}"
                                                                                                          }',9);


insert into c_template(template_code,name,html_name,url) values('route','路由管理','list_template','LIST->query.center.routes;QUERY->query.center.route');

insert into c_template_col(template_code,col_name,col_code,col_model,seq) values('route','路由ID','id','{ "name": "id","index": "id","width": "10",
                                                                                                             "editable": true,
                                                                                                             "sorttype": "int" }',1);
insert into c_template_col(template_code,col_name,col_code,col_model,seq) values('route','AppId','appId','{ "name": "appId","index": "appId","width": "30",
                                                                                                             "editable": true
                                                                                                           }',2);
insert into c_template_col(template_code,col_name,col_code,col_model,seq) values('route','服务ID','serviceId','{ "name": "serviceId","index": "serviceId","width": "30",
                                                                                                             "editable": true }',3);
insert into c_template_col(template_code,col_name,col_code,col_model,seq) values('route','调用方式','invokeModel','{ "name": "invokeModel","index": "invokeModel","width": "50",
                                                                                                              "editable": true }',4);
insert into c_template_col(template_code,col_name,col_code,col_model,seq) values('route','服务名称','serviceName','{ "name": "serviceName","index": "serviceName","width": "30",
                                                                                                             "editable": true }',5);
insert into c_template_col(template_code,col_name,col_code,col_model,seq) values('route','服务编码','serviceCode','{ "name": "serviceCode","index": "serviceCode","width": "30",
                                                                                                             "editable": true }',6);

insert into c_template_col(template_code,col_name,col_code,col_model,seq) values('route','订单类型','orderTypeCd','{ "name": "orderTypeCd","index": "orderTypeCd","width": "30",
                                                                                                             "editable": true }',7);

insert into c_template_col(template_code,col_name,col_code,col_model,seq) values('route','调用次数限制','invokelimitTimes','{ "name": "invokelimitTimes","index": "invokelimitTimes","width": "40",
                                                                                                             "editable": true }',8);

INSERT INTO c_template_col(template_code,col_name,col_code,col_model,seq) VALUES('route','BUTTON','BUTTON','{
                                                                                                            "name": "detail",
                                                                                                            "index": "",
                                                                                                            "width": "40",
                                                                                                            "fixed": "true",
                                                                                                            "sortable": "false",
                                                                                                            "resize": "false",
                                                                                                            "formatter": "function(cellvalue, options, rowObject){\n var temp =\"<div style=''margin-left:8px;''><div title=''详情记录'' style=''float:left;cursor:pointer;'' class=''ui-pg-div'' id=''jEditButton_3'' onclick=''detail(\"+rowObject+\")'' onmouseover=''jQuery(this).addClass(''ui-state-hover'');'' onmouseout=''jQuery(this).removeClass(''ui-state-hover'');''><span class=''ui-icon fa-search-plus''/></div></div>\";\n return temp; \n}"}',9);


insert into c_template(template_code,name,html_name,url) values('cache','刷新缓存','list_template_cache','LIST->query.center.caches;QUERY->query.center.cacheOne');

insert into c_template_col(template_code,col_name,col_code,col_model,seq) values('cache','缓存ID','id','{ "name": "id","index": "id","width": "10",
                                                                                                             "editable": true,
                                                                                                             "sorttype": "int" }',1);

insert into c_template_col(template_code,col_name,col_code,col_model,seq) values('cache','缓存编码','cacheCode','{ "name": "cacheCode","index": "cacheCode","width": "30",
                                                                                                             "editable": true }',2);
insert into c_template_col(template_code,col_name,col_code,col_model,seq) values('cache','缓存名称','cacheName','{ "name": "cacheName","index": "cacheName","width": "30",
                                                                                                             "editable": true }',3);
INSERT INTO c_template_col(template_code,col_name,col_code,col_model,seq) VALUES('cache','BUTTON','BUTTON','{
                                                                                                            "name": "detail",
                                                                                                            "index": "",
                                                                                                            "width": "40",
                                                                                                            "fixed": "true",
                                                                                                            "sortable": "false",
                                                                                                            "resize": "false",
                                                                                                            "formatter": ""function(cellvalue, options, rowObject){ var temp =\"<div style=''margin-left:8px;''><button type=''button'' class=''btn btn-warning'' style=''border-radius: .25rem;'' onclick=''flush(this,\"+rowObject.cacheCode+\")''>刷新缓存</button></div>\";return temp; }"
                                                                                                          }',4);


-- 缓存配置表
CREATE TABLE c_cache(
    id INT NOT NULL AUTO_INCREMENT KEY COMMENT '缓存ID',
    cache_code  VARCHAR(10) NOT NULL UNIQUE COMMENT '缓存编码 开始于1001',
    service_code  VARCHAR(50) NOT NULL COMMENT '调用服务编码 对应 c_service',
    `name` VARCHAR(50) NOT NULL COMMENT '前台显示名称',
    param LONGTEXT NOT NULL COMMENT '请求缓存系统时的参数',
    seq INT NOT NULL  COMMENT '列顺序',
    `group` VARCHAR(10) NOT NULL DEFAULT 'COMMON' COMMENT '组，缓存属于哪个组',
    create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    status_cd VARCHAR(2) NOT NULL DEFAULT '0' COMMENT '数据状态，详细参考c_status表，0在用，1失效'
);
-- 缓存用户表
create table c_cache_2_user(
     id INT NOT NULL AUTO_INCREMENT KEY COMMENT '缓存用户ID',
     cache_code int not null comment '缓存编码',
     user_id varchar(30) not null comment '用户id',
     create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
     status_cd VARCHAR(2) NOT NULL DEFAULT '0' COMMENT '数据状态，详细参考c_status表，0在用，1失效'
);


insert into  c_cache (cache_code,service_code,`name`,param,seq) values('1001','flush.center.cache','映射缓存（c_mapping表）','{"cacheName":"MAPPING"}',1);
insert into  c_cache (cache_code,service_code,`name`,param,seq) values('1002','flush.center.cache','业务配置缓存（c_app,c_service,c_route表）','{"cacheName":"APP_ROUTE_SERVICE"}',2);
insert into  c_cache (cache_code,service_code,`name`,param,seq) values('1003','flush.center.cache','公用服务缓存（c_service_sql表）','{"cacheName":"SERVICE_SQL"}',3);

insert into c_cache_2_user(cache_code,user_id) values('1001','10001');
insert into c_cache_2_user(cache_code,user_id) values('1002','10001');
insert into c_cache_2_user(cache_code,user_id) values('1003','10001');



create table l_transaction_log(
    log_id varchar(30) not null  COMMENT 'id',
    transaction_id VARCHAR(30) NOT NULL COMMENT '外部交易流水',
    contract_id varchar(64) not null comment '上下文ID',
    ip varchar(20) not null comment '日志产生主机IP',
    port varchar(10) not null comment '日志产生端口',
    src_ip varchar(20) comment '调用方IP',
    src_port varchar(10) comment '调用方端口',
    app_id varchar(30) not null comment '调用方应用ID',
    user_id varchar(30) comment '用户ID',
    service_code varchar(50) comment '服务编码',
    service_name varchar(50) comment '服务名称',
    timestamp TIMESTAMP NOT NULL comment '日志交互时间，时间戳',
    cost_time int not null default 0 comment '耗时',
    status_cd varchar(2) not null comment '交互状态 S 成功 F 失败',
    month INT NOT NULL comment '月份',
    create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    unique KEY (log_id,month)
)
partition BY RANGE (month) (
    partition transaction_log_1 VALUES LESS THAN (2),
    partition transaction_log_2 VALUES LESS THAN (3),
    partition transaction_log_3 VALUES LESS THAN (4),
    partition transaction_log_4 VALUES LESS THAN (5),
    partition transaction_log_5 VALUES LESS THAN (6),
    partition transaction_log_6 VALUES LESS THAN (7),
    partition transaction_log_7 VALUES LESS THAN (8),
    partition transaction_log_8 VALUES LESS THAN (9),
    partition transaction_log_9 VALUES LESS THAN (10),
    partition transaction_log_10 VALUES LESS THAN (11),
    partition transaction_log_11 VALUES LESS THAN (12),
    partition transaction_log_12 VALUES LESS THAN (13)
);



-- 日志原始内容表

create table l_transaction_log_message(
    log_id varchar(30) not null  COMMENT 'id',
    request_header LONGTEXT COMMENT '请求头信息',
    response_header LONGTEXT COMMENT '返回头信息',
    request_message LONGTEXT comment '请求报文',
    response_message LONGTEXT comment '返回报文',
    remark varchar(200) comment '备注',
    month INT NOT NULL  comment '月份',
    create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
     UNIQUE KEY (log_id,`month`)
)
partition BY RANGE (month) (
    partition transaction_log_message_1 VALUES LESS THAN (2),
    partition transaction_log_message_2 VALUES LESS THAN (3),
    partition transaction_log_message_3 VALUES LESS THAN (4),
    partition transaction_log_message_4 VALUES LESS THAN (5),
    partition transaction_log_message_5 VALUES LESS THAN (6),
    partition transaction_log_message_6 VALUES LESS THAN (7),
    partition transaction_log_message_7 VALUES LESS THAN (8),
    partition transaction_log_message_8 VALUES LESS THAN (9),
    partition transaction_log_message_9 VALUES LESS THAN (10),
    partition transaction_log_message_10 VALUES LESS THAN (11),
    partition transaction_log_message_11 VALUES LESS THAN (12),
    partition transaction_log_message_12 VALUES LESS THAN (13)
);



create table business_shop(
    shop_id VARCHAR(30) NOT NULL COMMENT '商品ID',
    b_id VARCHAR(30) NOT NULL COMMENT '业务Id',
    catalog_id varchar(30) not null comment '目录ID',
    store_id VARCHAR(30) NOT NULL COMMENT '商店ID',
    `name` VARCHAR(100) NOT NULL COMMENT '商品名称',
    hot_buy varchar(2) not null default 'N' comment '是否热卖 Y是 N否',
    sale_price DECIMAL(10,2) not null comment '商品销售价,再没有打折情况下显示',
    open_shop_count varchar(2) not null default 'N' comment '是否开启库存管理，默认N Y开启，N关闭，开启后界面显示数量，如果为0 则下架',
    shop_count DECIMAL(10,0) not null default 10000 comment '商品库存',
    start_date date not null comment '商品开始时间',
    end_date date not null comment '商品结束时间',
    `month` INT NOT NULL COMMENT '月份',
    create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    operate VARCHAR(3) NOT NULL COMMENT '数据状态，添加ADD，修改MOD 删除DEL'
)
PARTITION BY RANGE (`month`) (
    PARTITION business_shop_1 VALUES LESS THAN (2),
    PARTITION business_shop_2 VALUES LESS THAN (3),
    PARTITION business_shop_3 VALUES LESS THAN (4),
    PARTITION business_shop_4 VALUES LESS THAN (5),
    PARTITION business_shop_5 VALUES LESS THAN (6),
    PARTITION business_shop_6 VALUES LESS THAN (7),
    PARTITION business_shop_7 VALUES LESS THAN (8),
    PARTITION business_shop_8 VALUES LESS THAN (9),
    PARTITION business_shop_9 VALUES LESS THAN (10),
    PARTITION business_shop_10 VALUES LESS THAN (11),
    PARTITION business_shop_11 VALUES LESS THAN (12),
    PARTITION business_shop_12 VALUES LESS THAN (13)
);
CREATE INDEX idx_business_shop_shop_id ON business_shop(shop_id);
CREATE INDEX idx_business_shop_b_id ON business_shop(b_id);
-- 商品属性表
create table business_shop_attr(
    b_id VARCHAR(30) NOT NULL COMMENT '订单ID',
    attr_id VARCHAR(30) NOT NULL COMMENT '属性id',
    shop_id VARCHAR(30) NOT NULL COMMENT '商品ID',
    spec_cd VARCHAR(12) NOT NULL COMMENT '规格id,参考spec表',
    value VARCHAR(50) NOT NULL COMMENT '属性值',
    `month` INT NOT NULL COMMENT '月份',
    create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    operate VARCHAR(3) NOT NULL COMMENT '数据状态，添加ADD，修改MOD 删除DEL'
)
PARTITION BY RANGE (`month`) (
    PARTITION business_shop_attr_1 VALUES LESS THAN (2),
    PARTITION business_shop_attr_2 VALUES LESS THAN (3),
    PARTITION business_shop_attr_3 VALUES LESS THAN (4),
    PARTITION business_shop_attr_4 VALUES LESS THAN (5),
    PARTITION business_shop_attr_5 VALUES LESS THAN (6),
    PARTITION business_shop_attr_6 VALUES LESS THAN (7),
    PARTITION business_shop_attr_7 VALUES LESS THAN (8),
    PARTITION business_shop_attr_8 VALUES LESS THAN (9),
    PARTITION business_shop_attr_9 VALUES LESS THAN (10),
    PARTITION business_shop_attr_10 VALUES LESS THAN (11),
    PARTITION business_shop_attr_11 VALUES LESS THAN (12),
    PARTITION business_shop_attr_12 VALUES LESS THAN (13)
);
CREATE INDEX idx_business_shop_attr_shop_id ON business_shop_attr(shop_id);
CREATE INDEX idx_business_shop_attr_b_id ON business_shop_attr(b_id);

-- 商品 目录
create table business_shop_catalog(
    catalog_id varchar(30) not null comment '目录ID',
    b_id VARCHAR(30) NOT NULL COMMENT '业务Id',
    store_id VARCHAR(30) NOT NULL COMMENT '商店ID',
    `name` varchar(100) not null comment '目录名称',
    level varchar(2) not null comment '目录等级',
    parent_catalog_id varchar(30) not null default '-1' comment '父目录ID，一级目录则写-1',
    `month` INT NOT NULL COMMENT '月份',
    create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    operate VARCHAR(3) NOT NULL COMMENT '数据状态，添加ADD，修改MOD 删除DEL'
)
PARTITION BY RANGE (`month`) (
    PARTITION business_shop_catalog_1 VALUES LESS THAN (2),
    PARTITION business_shop_catalog_2 VALUES LESS THAN (3),
    PARTITION business_shop_catalog_3 VALUES LESS THAN (4),
    PARTITION business_shop_catalog_4 VALUES LESS THAN (5),
    PARTITION business_shop_catalog_5 VALUES LESS THAN (6),
    PARTITION business_shop_catalog_6 VALUES LESS THAN (7),
    PARTITION business_shop_catalog_7 VALUES LESS THAN (8),
    PARTITION business_shop_catalog_8 VALUES LESS THAN (9),
    PARTITION business_shop_catalog_9 VALUES LESS THAN (10),
    PARTITION business_shop_catalog_10 VALUES LESS THAN (11),
    PARTITION business_shop_catalog_11 VALUES LESS THAN (12),
    PARTITION business_shop_catalog_12 VALUES LESS THAN (13)
);
CREATE INDEX idx_business_shop_catalog_store_id ON business_shop_catalog(store_id);
CREATE INDEX idx_business_shop_catalog_b_id ON business_shop_catalog(b_id);


-- 商店照片
CREATE TABLE business_shop_photo(
    shop_photo_id VARCHAR(30) NOT NULL COMMENT '商品照片ID',
    b_id VARCHAR(30) NOT NULL COMMENT '业务Id',
    shop_id VARCHAR(30) NOT NULL COMMENT '商店ID',
    shop_photo_type_cd VARCHAR(12) NOT NULL COMMENT '商品照片类型,L logo O 其他照片',
    photo VARCHAR(100) NOT NULL COMMENT '照片',
    `month` INT NOT NULL COMMENT '月份',
    create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    operate VARCHAR(3) NOT NULL COMMENT '数据状态，添加ADD，修改MOD 删除DEL'
)
PARTITION BY RANGE (`month`) (
    PARTITION business_shop_photo_1 VALUES LESS THAN (2),
    PARTITION business_shop_photo_2 VALUES LESS THAN (3),
    PARTITION business_shop_photo_3 VALUES LESS THAN (4),
    PARTITION business_shop_photo_4 VALUES LESS THAN (5),
    PARTITION business_shop_photo_5 VALUES LESS THAN (6),
    PARTITION business_shop_photo_6 VALUES LESS THAN (7),
    PARTITION business_shop_photo_7 VALUES LESS THAN (8),
    PARTITION business_shop_photo_8 VALUES LESS THAN (9),
    PARTITION business_shop_photo_9 VALUES LESS THAN (10),
    PARTITION business_shop_photo_10 VALUES LESS THAN (11),
    PARTITION business_shop_photo_11 VALUES LESS THAN (12),
    PARTITION business_shop_photo_12 VALUES LESS THAN (13)
);

CREATE INDEX idx_business_shop_photo_shop_id ON business_shop_photo(shop_id);
CREATE INDEX idx_business_shop_photo_b_id ON business_shop_photo(b_id);


-- 商品属性 离散值表，例如 手机颜色 黑 白 红
create table business_shop_attr_param(
    attr_param_id VARCHAR(30) NOT NULL COMMENT '商品属性参数ID',
    b_id VARCHAR(30) NOT NULL COMMENT '订单ID',
    shop_id VARCHAR(30) NOT NULL COMMENT '商品ID',
    spec_cd VARCHAR(12) NOT NULL COMMENT '规格id,参考spec表',
    param VARCHAR(50) NOT NULL COMMENT '参数值',
    `month` INT NOT NULL COMMENT '月份',
    create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    operate VARCHAR(3) NOT NULL COMMENT '数据状态，添加ADD，修改MOD 删除DEL'
)
PARTITION BY RANGE (`month`) (
    PARTITION business_shop_attr_param_1 VALUES LESS THAN (2),
    PARTITION business_shop_attr_param_2 VALUES LESS THAN (3),
    PARTITION business_shop_attr_param_3 VALUES LESS THAN (4),
    PARTITION business_shop_attr_param_4 VALUES LESS THAN (5),
    PARTITION business_shop_attr_param_5 VALUES LESS THAN (6),
    PARTITION business_shop_attr_param_6 VALUES LESS THAN (7),
    PARTITION business_shop_attr_param_7 VALUES LESS THAN (8),
    PARTITION business_shop_attr_param_8 VALUES LESS THAN (9),
    PARTITION business_shop_attr_param_9 VALUES LESS THAN (10),
    PARTITION business_shop_attr_param_10 VALUES LESS THAN (11),
    PARTITION business_shop_attr_param_11 VALUES LESS THAN (12),
    PARTITION business_shop_attr_param_12 VALUES LESS THAN (13)
);

CREATE INDEX idx_business_shop_attr_param_shop_id ON business_shop_attr_param(shop_id);
CREATE INDEX idx_business_shop_attr_param_b_id ON business_shop_attr_param(b_id);

-- 商品优惠表
CREATE TABLE business_shop_preferential(
    shop_preferential_id VARCHAR(30) NOT NULL COMMENT '商品ID',
    shop_id VARCHAR(30) NOT NULL COMMENT '商品ID',
    b_id VARCHAR(30) NOT NULL COMMENT '业务Id',
    original_price DECIMAL(10,2) NOT NULL COMMENT '商品销售价，再没有优惠的情况下和售价是一致的',
    discount_rate DECIMAL(3,2) NOT NULL DEFAULT 1.00 COMMENT '商品打折率',
    show_original_price VARCHAR(2) NOT NULL DEFAULT 'N' COMMENT '是否显示原价，Y显示，N 不显示',
    preferential_start_date DATE NOT NULL COMMENT '商品优惠开始时间',
    preferential_end_date DATE NOT NULL COMMENT '商品优惠结束时间',
    `month` INT NOT NULL COMMENT '月份',
    create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    operate VARCHAR(3) NOT NULL COMMENT '数据状态，添加ADD，修改MOD 删除DEL'
)
PARTITION BY RANGE (`month`) (
    PARTITION business_shop_preferential_1 VALUES LESS THAN (2),
    PARTITION business_shop_preferential_2 VALUES LESS THAN (3),
    PARTITION business_shop_preferential_3 VALUES LESS THAN (4),
    PARTITION business_shop_preferential_4 VALUES LESS THAN (5),
    PARTITION business_shop_preferential_5 VALUES LESS THAN (6),
    PARTITION business_shop_preferential_6 VALUES LESS THAN (7),
    PARTITION business_shop_preferential_7 VALUES LESS THAN (8),
    PARTITION business_shop_preferential_8 VALUES LESS THAN (9),
    PARTITION business_shop_preferential_9 VALUES LESS THAN (10),
    PARTITION business_shop_preferential_10 VALUES LESS THAN (11),
    PARTITION business_shop_preferential_11 VALUES LESS THAN (12),
    PARTITION business_shop_preferential_12 VALUES LESS THAN (13)
);
CREATE INDEX idx_business_shop_preferential_shop_id ON business_shop_preferential(shop_id);
CREATE INDEX idx_business_shop_preferential_b_id ON business_shop_preferential(b_id);
-- 商品描述
create table business_shop_desc(
    shop_desc_id VARCHAR(30) NOT NULL COMMENT '商品描述ID',
    shop_id VARCHAR(30) NOT NULL COMMENT '商品ID',
    b_id VARCHAR(30) NOT NULL COMMENT '业务Id',
    shop_describe LONGTEXT not null COMMENT '商品描述',
    `month` INT NOT NULL COMMENT '月份',
    create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    operate VARCHAR(3) NOT NULL COMMENT '数据状态，添加ADD，修改MOD 删除DEL'
)
PARTITION BY RANGE (`month`) (
    PARTITION business_shop_desc_1 VALUES LESS THAN (2),
    PARTITION business_shop_desc_2 VALUES LESS THAN (3),
    PARTITION business_shop_desc_3 VALUES LESS THAN (4),
    PARTITION business_shop_desc_4 VALUES LESS THAN (5),
    PARTITION business_shop_desc_5 VALUES LESS THAN (6),
    PARTITION business_shop_desc_6 VALUES LESS THAN (7),
    PARTITION business_shop_desc_7 VALUES LESS THAN (8),
    PARTITION business_shop_desc_8 VALUES LESS THAN (9),
    PARTITION business_shop_desc_9 VALUES LESS THAN (10),
    PARTITION business_shop_desc_10 VALUES LESS THAN (11),
    PARTITION business_shop_desc_11 VALUES LESS THAN (12),
    PARTITION business_shop_desc_12 VALUES LESS THAN (13)
);
CREATE INDEX idx_business_shop_desc_shop_id ON business_shop_desc(shop_id);
CREATE INDEX idx_business_shop_desc_b_id ON business_shop_desc(b_id);


-- create 商品表
create table s_shop(
    shop_id VARCHAR(30) NOT NULL COMMENT '商品ID',
    b_id VARCHAR(30) NOT NULL COMMENT '业务Id',
    store_id VARCHAR(30) NOT NULL COMMENT '商店ID',
    catalog_id varchar(30) not null comment '目录ID',
    `name` VARCHAR(100) NOT NULL COMMENT '商品名称',
    hot_buy varchar(2) not null default 'N' comment '是否热卖 Y是 N否',
    sale_price DECIMAL(10,2) not null comment '商品销售价,再没有打折情况下显示',
    open_shop_count varchar(2) not null default 'N' comment '是否开启库存管理，默认N Y开启，N关闭，开启后界面显示数量，如果为0 则下架',
    shop_count DECIMAL(10,0) not null default 10000 comment '商品库存',
    start_date date not null comment '商品开始时间',
    end_date date not null comment '商品结束时间',
    create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    status_cd VARCHAR(2) NOT NULL DEFAULT '0' COMMENT '数据状态，详细参考c_status表，S 保存，0, 在用 1失效',
    UNIQUE KEY (shop_id)
);
CREATE INDEX idx_store_b_id ON s_shop(b_id);
CREATE UNIQUE INDEX idx_shop_shop_id ON s_shop(shop_id);
-- 商品属性表
create table s_shop_attr(
    b_id VARCHAR(30) NOT NULL COMMENT '订单ID',
    attr_id VARCHAR(30) NOT NULL COMMENT '属性id',
    shop_id VARCHAR(30) NOT NULL COMMENT '商品ID',
    spec_cd VARCHAR(12) NOT NULL COMMENT '规格id,参考spec表',
    value VARCHAR(50) NOT NULL COMMENT '属性值',
    create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    status_cd VARCHAR(2) NOT NULL DEFAULT '0' COMMENT '数据状态，详细参考c_status表，S 保存，0, 在用 1失效',
    UNIQUE KEY (attr_id)
);
CREATE INDEX idx_store_attr_b_id ON s_shop_attr(b_id);
CREATE INDEX idx_shop_attr_shop_id ON s_shop_attr(shop_id);

-- 商品属性 离散值表，例如 手机颜色 黑 白 红
create table s_shop_attr_param(
    attr_param_id VARCHAR(30) NOT NULL COMMENT '商品属性参数ID',
    b_id VARCHAR(30) NOT NULL COMMENT '订单ID',
    shop_id VARCHAR(30) NOT NULL COMMENT '商品ID',
    spec_cd VARCHAR(12) NOT NULL COMMENT '规格id,参考spec表',
    param VARCHAR(50) NOT NULL COMMENT '参数值',
    create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    status_cd VARCHAR(2) NOT NULL DEFAULT '0' COMMENT '数据状态，详细参考c_status表，S 保存，0, 在用 1失效',
    UNIQUE KEY (attr_param_id)
);
CREATE INDEX idx_shop_attr_param_b_id ON s_shop_attr_param(b_id);
CREATE INDEX idx_shop_attr_param_shop_id ON s_shop_attr_param(shop_id);

-- 商品优惠表
create table s_shop_preferential(
    shop_preferential_id VARCHAR(30) NOT NULL COMMENT '商品ID',
    shop_id VARCHAR(30) NOT NULL COMMENT '商品ID',
    b_id VARCHAR(30) NOT NULL COMMENT '业务Id',
    original_price DECIMAL(10,2) not null comment '商品销售价，再没有优惠的情况下和售价是一致的',
    discount_rate decimal(3,2) not null default 1.00 comment '商品打折率',
    show_original_price varchar(2) not null default 'N' comment '是否显示原价，Y显示，N 不显示',
    preferential_start_date date not null comment '商品优惠开始时间',
    preferential_end_date date not null comment '商品优惠结束时间',
    create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    status_cd VARCHAR(2) NOT NULL DEFAULT '0' COMMENT '数据状态，详细参考c_status表，S 保存，0, 在用 1失效',
    UNIQUE KEY (shop_preferential_id)
);
CREATE INDEX idx_shop_preferential_b_id ON s_shop_preferential(b_id);
CREATE INDEX idx_shop_preferential_shop_id ON s_shop_preferential(shop_id);
-- 商品描述
create table s_shop_desc(
    shop_desc_id VARCHAR(30) NOT NULL COMMENT '商品描述ID',
    shop_id VARCHAR(30) NOT NULL COMMENT '商品ID',
    b_id VARCHAR(30) NOT NULL COMMENT '业务Id',
    shop_describe LONGTEXT not null COMMENT '商品描述',
    create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    status_cd VARCHAR(2) NOT NULL DEFAULT '0' COMMENT '数据状态，详细参考c_status表，S 保存，0, 在用 1失效',
    UNIQUE KEY (shop_desc_id)
);
CREATE INDEX idx_shop_desc_b_id ON s_shop_desc(b_id);
CREATE INDEX idx_shop_desc_shop_id ON s_shop_desc(shop_id);

-- 商店照片
CREATE TABLE s_shop_photo(
    shop_photo_id VARCHAR(30) NOT NULL COMMENT '商品照片ID',
    b_id VARCHAR(30) NOT NULL COMMENT '业务Id',
    shop_id VARCHAR(30) NOT NULL COMMENT '商品ID',
    shop_photo_type_cd VARCHAR(12) NOT NULL COMMENT '商品照片类型,L logo O 其他照片',
    photo VARCHAR(100) NOT NULL COMMENT '照片',
    create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    status_cd VARCHAR(2) NOT NULL DEFAULT '0' COMMENT '数据状态，详细参考c_status表，S 保存，0, 在用 1失效',
    UNIQUE KEY (shop_photo_id)
);
CREATE INDEX idx_shop_photo_b_id ON s_shop_photo(b_id);
CREATE INDEX idx_shop_photo_shop_id ON s_shop_photo(shop_id);
CREATE INDEX idx_shop_photo_shop_photo_id ON s_shop_photo(shop_photo_id);

create table s_shop_catalog(
    catalog_id varchar(30) not null comment '目录ID',
    b_id VARCHAR(30) NOT NULL COMMENT '业务Id',
    store_id VARCHAR(30) NOT NULL COMMENT '商店ID',
    `name` varchar(100) not null comment '目录名称',
    level varchar(2) not null comment '目录等级',
    parent_catalog_id varchar(30) not null default '-1' comment '父目录ID，一级目录则写-1',
    create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    status_cd VARCHAR(2) NOT NULL DEFAULT '0' COMMENT '数据状态，详细参考c_status表，S 保存，0, 在用 1失效',
     UNIQUE KEY (catalog_id)
);
CREATE INDEX idx_shop_catalog_b_id ON s_shop_catalog(b_id);
CREATE INDEX idx_shop_catalog_store_id ON s_shop_catalog(store_id);
CREATE INDEX idx_shop_catalog_catalog_id ON s_shop_catalog(catalog_id);

-- 商品购买记录

CREATE TABLE s_buy_shop(
    buy_id VARCHAR(30) NOT NULL COMMENT '购买ID',
    b_id VARCHAR(30) NOT NULL COMMENT '业务Id',
    shop_id VARCHAR(30) NOT NULL COMMENT '商品ID',
    buy_count DECIMAL(10,0) NOT NULL DEFAULT 1 COMMENT '购买商品数',
    `month` INT NOT NULL COMMENT '月份',
    create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    status_cd VARCHAR(2) NOT NULL DEFAULT '0' COMMENT '数据状态，详细参考c_status表，S 保存，0, 在用 1失效',
     UNIQUE KEY (buy_id,`month`)
)
PARTITION BY RANGE (`month`) (
    PARTITION buy_shop_1 VALUES LESS THAN (2),
    PARTITION buy_shop_2 VALUES LESS THAN (3),
    PARTITION buy_shop_3 VALUES LESS THAN (4),
    PARTITION buy_shop_4 VALUES LESS THAN (5),
    PARTITION buy_shop_5 VALUES LESS THAN (6),
    PARTITION buy_shop_6 VALUES LESS THAN (7),
    PARTITION buy_shop_7 VALUES LESS THAN (8),
    PARTITION buy_shop_8 VALUES LESS THAN (9),
    PARTITION buy_shop_9 VALUES LESS THAN (10),
    PARTITION buy_shop_10 VALUES LESS THAN (11),
    PARTITION buy_shop_11 VALUES LESS THAN (12),
    PARTITION buy_shop_12 VALUES LESS THAN (13)
);
CREATE INDEX idx_buy_shop_b_id ON s_buy_shop(b_id);
-- 商品购买属性
create table s_buy_shop_attr(
    b_id VARCHAR(30) NOT NULL COMMENT '订单ID',
    attr_id VARCHAR(30) NOT NULL COMMENT '属性id',
    buy_id VARCHAR(30) NOT NULL COMMENT '购买ID buy_id',
    spec_cd VARCHAR(12) NOT NULL COMMENT '规格id,参考spec表',
    value VARCHAR(50) NOT NULL COMMENT '属性值',
    `month` INT NOT NULL COMMENT '月份',
    create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    status_cd VARCHAR(2) NOT NULL DEFAULT '0' COMMENT '数据状态，详细参考c_status表，S 保存，0, 在用 1失效',
     UNIQUE KEY (attr_id,`month`)
)
PARTITION BY RANGE (`month`) (
    PARTITION buy_shop_attr_1 VALUES LESS THAN (2),
    PARTITION buy_shop_attr_2 VALUES LESS THAN (3),
    PARTITION buy_shop_attr_3 VALUES LESS THAN (4),
    PARTITION buy_shop_attr_4 VALUES LESS THAN (5),
    PARTITION buy_shop_attr_5 VALUES LESS THAN (6),
    PARTITION buy_shop_attr_6 VALUES LESS THAN (7),
    PARTITION buy_shop_attr_7 VALUES LESS THAN (8),
    PARTITION buy_shop_attr_8 VALUES LESS THAN (9),
    PARTITION buy_shop_attr_9 VALUES LESS THAN (10),
    PARTITION buy_shop_attr_10 VALUES LESS THAN (11),
    PARTITION buy_shop_attr_11 VALUES LESS THAN (12),
    PARTITION buy_shop_attr_12 VALUES LESS THAN (13)
);
CREATE INDEX idx_buy_shop_attr_b_id ON s_buy_shop_attr(b_id);
CREATE INDEX idx_buy_shop_attr_buy_id ON s_buy_shop_attr(buy_id);


CREATE TABLE business_store(
    store_id VARCHAR(30) NOT NULL COMMENT '商店ID',
    b_id VARCHAR(30) NOT NULL COMMENT '业务Id',
    user_id VARCHAR(30) NOT NULL COMMENT '用户ID',
    `name` VARCHAR(100) NOT NULL COMMENT '店铺名称',
    address VARCHAR(200) NOT NULL COMMENT '店铺地址',
    tel VARCHAR(11) NOT NULL COMMENT '电话',
    store_type_cd VARCHAR(10) NOT NULL COMMENT '店铺种类，对应表 store_type',
    nearby_landmarks VARCHAR(200) COMMENT '地标，如王府井北60米',
    map_x VARCHAR(20) NOT NULL COMMENT '地区 x坐标',
    map_y VARCHAR(20) NOT NULL COMMENT '地区 y坐标',
    `month` INT NOT NULL COMMENT '月份',
    create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    operate VARCHAR(3) NOT NULL COMMENT '数据状态，添加ADD，修改MOD 删除DEL'
)
PARTITION BY RANGE (`month`) (
    PARTITION business_store_1 VALUES LESS THAN (2),
    PARTITION business_store_2 VALUES LESS THAN (3),
    PARTITION business_store_3 VALUES LESS THAN (4),
    PARTITION business_store_4 VALUES LESS THAN (5),
    PARTITION business_store_5 VALUES LESS THAN (6),
    PARTITION business_store_6 VALUES LESS THAN (7),
    PARTITION business_store_7 VALUES LESS THAN (8),
    PARTITION business_store_8 VALUES LESS THAN (9),
    PARTITION business_store_9 VALUES LESS THAN (10),
    PARTITION business_store_10 VALUES LESS THAN (11),
    PARTITION business_store_11 VALUES LESS THAN (12),
    PARTITION business_store_12 VALUES LESS THAN (13)
);
CREATE INDEX idx_business_store_store_id ON business_store(store_id);
CREATE INDEX idx_business_store_b_id ON business_store(b_id);

create table business_store_attr(
    b_id VARCHAR(30) NOT NULL COMMENT '订单ID',
    attr_id VARCHAR(30) NOT NULL COMMENT '属性id',
    store_id VARCHAR(30) NOT NULL COMMENT '用户ID',
    spec_cd VARCHAR(12) NOT NULL COMMENT '规格id,参考spec表',
    value VARCHAR(50) NOT NULL COMMENT '属性值',
    `month` INT NOT NULL comment '月份',
    create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    operate VARCHAR(3) NOT NULL COMMENT '数据状态，添加ADD，修改MOD 删除DEL'
)
partition BY RANGE (`month`) (
    partition business_store_attr_1 VALUES LESS THAN (2),
    partition business_store_attr_2 VALUES LESS THAN (3),
    partition business_store_attr_3 VALUES LESS THAN (4),
    partition business_store_attr_4 VALUES LESS THAN (5),
    partition business_store_attr_5 VALUES LESS THAN (6),
    partition business_store_attr_6 VALUES LESS THAN (7),
    partition business_store_attr_7 VALUES LESS THAN (8),
    partition business_store_attr_8 VALUES LESS THAN (9),
    partition business_store_attr_9 VALUES LESS THAN (10),
    partition business_store_attr_10 VALUES LESS THAN (11),
    partition business_store_attr_11 VALUES LESS THAN (12),
    partition business_store_attr_12 VALUES LESS THAN (13)
);
CREATE INDEX idx_business_store_attr_store_id ON business_store_attr(store_id);
CREATE INDEX idx_business_store_attr_b_id ON business_store_attr(b_id);

-- 商店照片
CREATE TABLE business_store_photo(
    store_photo_id VARCHAR(30) NOT NULL COMMENT '商户照片ID',
    b_id VARCHAR(30) NOT NULL COMMENT '业务Id',
    store_id VARCHAR(30) NOT NULL COMMENT '商店ID',
    store_photo_type_cd VARCHAR(12) NOT NULL COMMENT '商户照片类型,T 门头照 I 内景照',
    photo VARCHAR(100) NOT NULL COMMENT '照片',
    `month` INT NOT NULL COMMENT '月份',
    create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    operate VARCHAR(3) NOT NULL COMMENT '数据状态，添加ADD，修改MOD 删除DEL'
)
PARTITION BY RANGE (`month`) (
    PARTITION business_store_photo_1 VALUES LESS THAN (2),
    PARTITION business_store_photo_2 VALUES LESS THAN (3),
    PARTITION business_store_photo_3 VALUES LESS THAN (4),
    PARTITION business_store_photo_4 VALUES LESS THAN (5),
    PARTITION business_store_photo_5 VALUES LESS THAN (6),
    PARTITION business_store_photo_6 VALUES LESS THAN (7),
    PARTITION business_store_photo_7 VALUES LESS THAN (8),
    PARTITION business_store_photo_8 VALUES LESS THAN (9),
    PARTITION business_store_photo_9 VALUES LESS THAN (10),
    PARTITION business_store_photo_10 VALUES LESS THAN (11),
    PARTITION business_store_photo_11 VALUES LESS THAN (12),
    PARTITION business_store_photo_12 VALUES LESS THAN (13)
);
CREATE INDEX idx_business_store_photo_store_id ON business_store_photo(store_id);
CREATE INDEX idx_business_store_photo_b_id ON business_store_photo(b_id);
-- 商户证件
create table business_store_cerdentials(
    store_cerdentials_id varchar(30) not null comment '商户证件ID',
    b_id VARCHAR(30) NOT NULL COMMENT '业务Id',
    store_id VARCHAR(30) NOT NULL COMMENT '商店ID',
    credentials_cd varchar(12) not null comment '证件类型，对应于 credentials表',
    value varchar(50) not null  comment '证件号码',
    validity_period DATE NOT NULL  COMMENT '有效期，如果是长期有效 写成 3000/1/1',
    positive_photo varchar(100) comment '正面照片',
    negative_photo varchar(100) comment '反面照片',
    `month` INT NOT NULL comment '月份',
    create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    operate VARCHAR(3) NOT NULL COMMENT '数据状态，添加ADD，修改MOD 删除DEL'
)
partition BY RANGE (`month`) (
    partition business_store_cerdentials_1 VALUES LESS THAN (2),
    partition business_store_cerdentials_2 VALUES LESS THAN (3),
    partition business_store_cerdentials_3 VALUES LESS THAN (4),
    partition business_store_cerdentials_4 VALUES LESS THAN (5),
    partition business_store_cerdentials_5 VALUES LESS THAN (6),
    partition business_store_cerdentials_6 VALUES LESS THAN (7),
    partition business_store_cerdentials_7 VALUES LESS THAN (8),
    partition business_store_cerdentials_8 VALUES LESS THAN (9),
    partition business_store_cerdentials_9 VALUES LESS THAN (10),
    partition business_store_cerdentials_10 VALUES LESS THAN (11),
    partition business_store_cerdentials_11 VALUES LESS THAN (12),
    partition business_store_cerdentials_12 VALUES LESS THAN (13)
);
CREATE INDEX idx_business_store_cerdentials_store_id ON business_store_cerdentials(store_id);
CREATE INDEX idx_business_store_cerdentials_b_id ON business_store_cerdentials(b_id);



CREATE TABLE s_store(
    store_id VARCHAR(30) NOT NULL COMMENT '商店ID',
    b_id VARCHAR(30) NOT NULL COMMENT '业务Id',
    user_id VARCHAR(30) NOT NULL COMMENT '用户ID',
    `name` VARCHAR(100) NOT NULL COMMENT '店铺名称',
    address VARCHAR(200) NOT NULL COMMENT '店铺地址',
    tel VARCHAR(11) NOT NULL COMMENT '电话',
    store_type_cd VARCHAR(10) NOT NULL COMMENT '店铺种类',
    nearby_landmarks VARCHAR(200) COMMENT '地标，如王府井北60米',
    map_x VARCHAR(20) NOT NULL COMMENT '地区 x坐标',
    map_y VARCHAR(20) NOT NULL COMMENT '地区 y坐标',
    create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    status_cd VARCHAR(2) NOT NULL DEFAULT '0' COMMENT '数据状态，详细参考c_status表，S 保存，0, 在用 1失效',
    UNIQUE KEY (store_id)
);
CREATE INDEX idx_store_b_id ON s_store(b_id);
CREATE UNIQUE INDEX idx_store_store_id ON s_store(store_id);

CREATE TABLE s_store_attr(
    b_id VARCHAR(30) NOT NULL COMMENT '订单ID',
    attr_id VARCHAR(30) NOT NULL COMMENT '属性id',
    store_id VARCHAR(30) NOT NULL COMMENT '用户ID',
    spec_cd VARCHAR(12) NOT NULL COMMENT '规格id,参考spec表',
    VALUE VARCHAR(50) NOT NULL COMMENT '属性值',
    create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    status_cd VARCHAR(2) NOT NULL DEFAULT '0' COMMENT '数据状态，详细参考c_status表，S 保存，0, 在用 1失效',
    UNIQUE KEY (attr_id)
);
CREATE INDEX idx_store_attr_b_id ON s_store_attr(b_id);
CREATE INDEX idx_store_attr_store_id ON s_store_attr(store_id);

-- 商店照片
CREATE TABLE s_store_photo(
    store_photo_id VARCHAR(30) NOT NULL COMMENT '商户照片ID',
    b_id VARCHAR(30) NOT NULL COMMENT '业务Id',
    store_id VARCHAR(30) NOT NULL COMMENT '商店ID',
    store_photo_type_cd VARCHAR(12) NOT NULL COMMENT '商户照片类型,T 门头照 I 内景照',
    photo VARCHAR(100) NOT NULL COMMENT '照片',
    create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    status_cd VARCHAR(2) NOT NULL DEFAULT '0' COMMENT '数据状态，详细参考c_status表，S 保存，0, 在用 1失效',
    UNIQUE KEY (store_photo_id)
);
CREATE INDEX idx_store_photo_b_id ON s_store_photo(b_id);
CREATE INDEX idx_store_photo_store_id ON s_store_photo(store_id);
CREATE INDEX idx_store_photo_store_photo_id ON s_store_photo(store_photo_id);

-- 商户证件
create table s_store_cerdentials(
    store_cerdentials_id varchar(30) not null comment '商户证件ID',
    b_id VARCHAR(30) NOT NULL COMMENT '业务Id',
    store_id VARCHAR(30) NOT NULL COMMENT '商店ID',
    credentials_cd varchar(12) not null comment '证件类型，对应于 credentials表',
    value varchar(50) not null  comment '证件号码',
    validity_period DATE NOT NULL  COMMENT '有效期，如果是长期有效 写成 3000/1/1',
    positive_photo varchar(100) comment '正面照片',
    negative_photo varchar(100) comment '反面照片',
    create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    status_cd VARCHAR(2) NOT NULL default '0' COMMENT '数据状态，详细参考c_status表，S 保存，0, 在用 1失效',
    unique KEY (store_cerdentials_id)
);

CREATE INDEX idx_store_cerdentials_b_id ON s_store_cerdentials(b_id);
CREATE INDEX idx_store_cerdentials_store_id ON s_store_cerdentials(store_id);
CREATE INDEX idx_store_cerdentials_store_cerdentials_id ON s_store_cerdentials(store_cerdentials_id);

-- 店铺种类
create table store_type(
    id INT NOT NULL AUTO_INCREMENT KEY COMMENT 'id',
    store_type_cd VARCHAR(12) NOT NULL COMMENT '店铺编码',
    `name` VARCHAR(50) NOT NULL COMMENT '店铺种类编码',
    description VARCHAR(200) COMMENT '描述',
    create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    unique KEY (store_type_cd)
);


create table business_user(
    id INT NOT NULL AUTO_INCREMENT KEY COMMENT 'id',
    b_id VARCHAR(30) NOT NULL COMMENT '业务Id',
    user_id VARCHAR(30) NOT NULL COMMENT '用户ID',
    name VARCHAR(50) NOT NULL COMMENT '名称',
    email VARCHAR(30) COMMENT '邮箱地址',
    address VARCHAR(200) COMMENT '现居住地址',
    password varchar(128) comment '用户密码，加密过后',
    location_cd varchar(8) comment '用户地区，编码详见 u_location',
    age int comment '用户年龄',
    sex varchar(1) comment '性别，0表示男孩 1表示女孩',
    tel varchar(11) comment '用户手机',
    level_cd varchar(2) not null default '0' comment '用户级别,关联user_level',
    create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    operate VARCHAR(3) NOT NULL COMMENT '数据状态，添加ADD，修改MOD 删除DEL'
);

-- user_level

CREATE TABLE user_level(
    id INT NOT NULL AUTO_INCREMENT KEY COMMENT 'id',
    level_cd VARCHAR(4) NOT NULL UNIQUE COMMENT '用户级别',
    `name` VARCHAR(50) NOT NULL COMMENT '名称',
    description VARCHAR(200) COMMENT '描述',
    create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间'
);

insert into user_level(level_cd,name,description) values('0','普通用户','普通用户');

-- u_location
CREATE TABLE u_location(
    id INT NOT NULL AUTO_INCREMENT KEY COMMENT 'id',
    location_cd VARCHAR(4) NOT NULL UNIQUE COMMENT '区域编码',
    level VARCHAR(4) not null comment '区域级别，1 表示一级地区',
    `name` VARCHAR(50) NOT NULL COMMENT '区域名称',
    description VARCHAR(200) COMMENT '描述',
    create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间'
);

-- business_user_attr
CREATE TABLE business_user_attr(
    b_id VARCHAR(30) NOT NULL COMMENT '订单ID',
    attr_id VARCHAR(30) NOT NULL COMMENT '属性id',
    user_id VARCHAR(30) NOT NULL COMMENT '用户ID',
    spec_cd VARCHAR(12) NOT NULL COMMENT '规格id,参考spec表',
    value VARCHAR(50) NOT NULL COMMENT '属性值',
    create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    operate VARCHAR(3) NOT NULL COMMENT '数据状态，添加ADD，修改MOD 删除DEL'
);

-- u_user
create table u_user(
    id INT NOT NULL AUTO_INCREMENT KEY COMMENT 'id',
    user_id VARCHAR(30) NOT NULL UNIQUE COMMENT '用户ID',
    name VARCHAR(50) NOT NULL COMMENT '名称',
    email VARCHAR(30) COMMENT '邮箱地址',
    address VARCHAR(200) COMMENT '现居住地址',
    password varchar(128) comment '用户密码，加密过后',
    location_cd varchar(8) comment '用户地区，编码详见 u_location',
    age int comment '用户年龄',
    sex varchar(1) comment '性别，0表示男孩 1表示女孩',
    tel varchar(11) comment '用户手机',
    level_cd varchar(2) not null default '0' comment '用户级别,关联user_level',
    b_id VARCHAR(30) NOT NULL COMMENT '业务Id',
    create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    status_cd VARCHAR(2) NOT NULL default '0' COMMENT '数据状态，详细参考c_status表，S 保存，0, 在用 1失效'
);


-- u_user_attr

CREATE TABLE u_user_attr(
    id INT NOT NULL AUTO_INCREMENT KEY COMMENT 'id',
    attr_id VARCHAR(30) NOT NULL COMMENT '属性id',
    user_id VARCHAR(30) NOT NULL COMMENT '用户ID',
    spec_cd VARCHAR(12) NOT NULL COMMENT '规格id,参考spec表',
    value VARCHAR(50) NOT NULL COMMENT '属性值',
    b_id VARCHAR(30) NOT NULL COMMENT '业务ID',
    create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    status_cd VARCHAR(2) NOT NULL default '0' COMMENT '数据状态，详细参考c_status表，S 保存，0在用，1失效'
);
-- 用户 物流地址
create table business_user_address(
    address_id VARCHAR(30) NOT NULL COMMENT '地址ID',
    b_id VARCHAR(30) NOT NULL COMMENT '业务Id',
    user_id VARCHAR(30) NOT NULL COMMENT '用户ID',
    tel varchar(11) not null comment '电话',
    postal_code varchar(10) not null comment '邮政编码',
    address varchar(200) not null comment '地址',
    is_default varchar(1) not null comment '是否为默认地址 1，表示默认地址 0 或空不是默认地址',
    create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    operate VARCHAR(3) NOT NULL COMMENT '数据状态，添加ADD，修改MOD 删除DEL'
);

-- u_user_address
create table u_user_address(
    id INT NOT NULL AUTO_INCREMENT KEY COMMENT 'id',
    address_id VARCHAR(30) NOT NULL COMMENT '地址ID',
    b_id VARCHAR(30) NOT NULL COMMENT '业务Id',
    user_id VARCHAR(30) NOT NULL COMMENT '用户ID',
    tel varchar(11) not null comment '电话',
    postal_code varchar(10) not null comment '邮政编码',
    address varchar(200) not null comment '地址',
    is_default varchar(1) not null comment '是否为默认地址 1，表示默认地址 0 或空不是默认地址',
    create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    status_cd VARCHAR(2) NOT NULL default '0' COMMENT '数据状态，详细参考c_status表，S 保存，0在用，1失效'
);
-- 用户标签
create table business_user_tag(
    tag_id VARCHAR(30) NOT NULL COMMENT '打标ID',
    b_id VARCHAR(30) NOT NULL COMMENT '业务Id',
    user_id VARCHAR(30) NOT NULL COMMENT '用户ID',
    tag_cd VARCHAR(12) NOT NULL COMMENT '标签编码,参考tag表',
    remark VARCHAR(200) COMMENT '备注',
    create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    operate VARCHAR(3) NOT NULL COMMENT '数据状态，添加ADD，修改MOD 删除DEL'

);

-- 用户标签
create table u_user_tag(
    id INT NOT NULL AUTO_INCREMENT KEY COMMENT 'id',
    tag_id VARCHAR(30) NOT NULL COMMENT '打标ID',
    user_id VARCHAR(30) NOT NULL COMMENT '用户ID',
    tag_cd VARCHAR(12) NOT NULL COMMENT '标签编码,参考tag表',
    b_id VARCHAR(30) NOT NULL COMMENT '业务Id',
    remark VARCHAR(200) COMMENT '备注',
    create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    status_cd VARCHAR(2) NOT NULL default '0' COMMENT '数据状态，详细参考c_status表，S 保存，0在用，1失效'
);

-- 标签
create table tag(
    id INT NOT NULL AUTO_INCREMENT KEY COMMENT 'id',
    domain varchar(20) not null comment '标签域',
    tag_cd VARCHAR(12) NOT NULL COMMENT '标签编码',
    `name` VARCHAR(50) NOT NULL COMMENT '标签名称',
    description VARCHAR(200) COMMENT '描述',
    create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间'
);
-- 用户证件 表（过程表）
create table business_user_credentials(
    credentials_id VARCHAR(30) NOT NULL COMMENT '证件ID',
    b_id VARCHAR(30) NOT NULL COMMENT '业务Id',
    user_id VARCHAR(30) NOT NULL COMMENT '用户ID',
    credentials_cd varchar(12) not null comment '证件类型',
    value varchar(50) not null  comment '证件号码',
    create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    operate VARCHAR(3) NOT NULL COMMENT '数据状态，添加ADD，修改MOD 删除DEL'
);


-- 用户证件表
create table u_user_credentials(
    id INT NOT NULL AUTO_INCREMENT KEY COMMENT 'id',
    credentials_id VARCHAR(30) NOT NULL COMMENT '证件ID',
    b_id VARCHAR(30) NOT NULL COMMENT '业务Id',
    user_id VARCHAR(30) NOT NULL COMMENT '用户ID',
    credentials_cd varchar(12) not null comment '证件类型',
    value varchar(50) not null  comment '证件号码',
    create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    status_cd VARCHAR(2) NOT NULL default '0' COMMENT '数据状态，详细参考c_status表，S 保存，0在用，1失效'
);

-- 标签
create table credentials(
    id INT NOT NULL AUTO_INCREMENT KEY COMMENT 'id',
    credentials_cd VARCHAR(12) NOT NULL COMMENT '证件编码',
    `name` VARCHAR(50) NOT NULL COMMENT '证件名称',
    description VARCHAR(200) COMMENT '描述',
    create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间'
);