-- 新华社工商信息同步接入
-- MySQL 5.7/8.0 幂等迁移，统一字符集与排序规则。
SET NAMES utf8mb4 COLLATE utf8mb4_general_ci;

CREATE TABLE IF NOT EXISTS `ccdi_enterpriseinfo_query_cache` (
  `info_id` bigint NOT NULL AUTO_INCREMENT,
  `query_param` varchar(255) COLLATE utf8mb4_general_ci NOT NULL,
  `query_result` longtext COLLATE utf8mb4_general_ci NOT NULL,
  `query_type` varchar(64) COLLATE utf8mb4_general_ci NOT NULL,
  `created_date` datetime NOT NULL,
  `valid_date` datetime NOT NULL,
  PRIMARY KEY (`info_id`),
  UNIQUE KEY `uk_enterpriseinfo_query` (`query_param`,`query_type`),
  KEY `idx_enterpriseinfo_valid_date` (`valid_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='工商信息原始响应缓存';

ALTER TABLE `ccdi_enterpriseinfo_query_cache`
  MODIFY `query_result` LONGTEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

CREATE TABLE IF NOT EXISTS `ccdi_enterprise_shareholder` (
  `shareholder_id` bigint NOT NULL AUTO_INCREMENT,
  `social_credit_code` varchar(64) COLLATE utf8mb4_general_ci NOT NULL,
  `shareholder_seq` int DEFAULT NULL,
  `shareholder_name` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `shareholder_type` varchar(100) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `shareholder_credit_code` varchar(64) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `stock_percent` decimal(12,6) DEFAULT NULL,
  `subscribed_capital` decimal(20,6) DEFAULT NULL,
  `capital_unit` varchar(32) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `cache_info_id` bigint DEFAULT NULL,
  `sync_time` datetime DEFAULT NULL,
  `create_by` varchar(64) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `create_time` datetime DEFAULT NULL,
  `update_by` varchar(64) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `update_time` datetime DEFAULT NULL,
  PRIMARY KEY (`shareholder_id`),
  KEY `idx_enterprise_shareholder_credit` (`social_credit_code`),
  KEY `idx_enterprise_shareholder_cache` (`cache_info_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='实体库企业完整股东';

CREATE TABLE IF NOT EXISTS `ccdi_project_counterparty_enterprise` (
  `counterparty_enterprise_id` bigint NOT NULL AUTO_INCREMENT,
  `project_id` bigint NOT NULL,
  `counterparty_name` varchar(255) COLLATE utf8mb4_general_ci NOT NULL,
  `social_credit_code` varchar(64) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `enterprise_name` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `registered_capital` decimal(20,6) DEFAULT NULL,
  `registered_capital_unit` varchar(32) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `register_date` date DEFAULT NULL,
  `establish_date` date DEFAULT NULL,
  `industry_code` varchar(100) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `industry_name` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `organization_type_code` varchar(100) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `organization_type_name` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `region_code` varchar(100) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `region_name` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `register_address` varchar(500) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `employee_count` int DEFAULT NULL,
  `legal_representative` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `cache_info_id` bigint DEFAULT NULL,
  `sync_time` datetime DEFAULT NULL,
  `create_by` varchar(64) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `create_time` datetime DEFAULT NULL,
  `update_by` varchar(64) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `update_time` datetime DEFAULT NULL,
  PRIMARY KEY (`counterparty_enterprise_id`),
  UNIQUE KEY `uk_project_counterparty_name` (`project_id`,`counterparty_name`),
  KEY `idx_project_counterparty_credit` (`social_credit_code`),
  KEY `idx_project_counterparty_cache` (`cache_info_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='项目对手方工商信息';

CREATE TABLE IF NOT EXISTS `ccdi_project_counterparty_shareholder` (
  `shareholder_id` bigint NOT NULL AUTO_INCREMENT,
  `counterparty_enterprise_id` bigint NOT NULL,
  `shareholder_seq` int NOT NULL,
  `shareholder_name` varchar(255) COLLATE utf8mb4_general_ci NOT NULL,
  `shareholder_type` varchar(100) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `shareholder_credit_code` varchar(64) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `stock_percent` decimal(12,6) DEFAULT NULL,
  `subscribed_capital` decimal(20,6) DEFAULT NULL,
  `capital_unit` varchar(32) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `create_by` varchar(64) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `create_time` datetime DEFAULT NULL,
  `update_by` varchar(64) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `update_time` datetime DEFAULT NULL,
  PRIMARY KEY (`shareholder_id`),
  KEY `idx_project_counterparty_shareholder_enterprise` (`counterparty_enterprise_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='项目对手方完整股东';

CREATE TABLE IF NOT EXISTS `sys_api_log` (
  `log_id` bigint NOT NULL AUTO_INCREMENT,
  `caller_user_id` bigint DEFAULT NULL,
  `caller_username` varchar(64) COLLATE utf8mb4_general_ci NOT NULL,
  `api_url` text COLLATE utf8mb4_general_ci NOT NULL,
  `http_method` varchar(10) COLLATE utf8mb4_general_ci NOT NULL,
  `content_type` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `request_headers` longtext COLLATE utf8mb4_general_ci,
  `request_params` longtext COLLATE utf8mb4_general_ci,
  `response_status` int DEFAULT NULL,
  `response_headers` longtext COLLATE utf8mb4_general_ci,
  `response_body` longtext COLLATE utf8mb4_general_ci,
  `call_status` char(1) COLLATE utf8mb4_general_ci NOT NULL,
  `error_msg` longtext COLLATE utf8mb4_general_ci,
  `cost_time` bigint NOT NULL,
  `call_time` datetime NOT NULL,
  PRIMARY KEY (`log_id`),
  KEY `idx_api_log_call_time` (`call_time`),
  KEY `idx_api_log_caller` (`caller_user_id`),
  KEY `idx_api_log_status` (`call_status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='外部接口调用日志';

ALTER TABLE `ccdi_enterprise_shareholder`
  CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
ALTER TABLE `ccdi_project_counterparty_enterprise`
  CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
ALTER TABLE `ccdi_project_counterparty_shareholder`
  CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
ALTER TABLE `sys_api_log`
  MODIFY `caller_username` VARCHAR(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  MODIFY `api_url` TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  MODIFY `http_method` VARCHAR(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  MODIFY `call_status` CHAR(1) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  MODIFY `cost_time` BIGINT NOT NULL,
  CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

-- 企业实体扩展字段（MySQL 5.7 不支持 ADD COLUMN IF NOT EXISTS，使用 information_schema 判定）。
SET @schema_name = DATABASE();

SET @ddl = IF(EXISTS(SELECT 1 FROM information_schema.columns WHERE table_schema=@schema_name AND table_name='ccdi_enterprise_base_info' AND column_name='registered_capital'), 'SELECT 1', 'ALTER TABLE ccdi_enterprise_base_info ADD COLUMN registered_capital DECIMAL(20,6) NULL COMMENT ''注册资本'' AFTER legal_representative');
PREPARE stmt FROM @ddl; EXECUTE stmt; DEALLOCATE PREPARE stmt;
SET @ddl = IF(EXISTS(SELECT 1 FROM information_schema.columns WHERE table_schema=@schema_name AND table_name='ccdi_enterprise_base_info' AND column_name='registered_capital_unit'), 'SELECT 1', 'ALTER TABLE ccdi_enterprise_base_info ADD COLUMN registered_capital_unit VARCHAR(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL COMMENT ''注册资本单位'' AFTER registered_capital');
PREPARE stmt FROM @ddl; EXECUTE stmt; DEALLOCATE PREPARE stmt;
SET @ddl = IF(EXISTS(SELECT 1 FROM information_schema.columns WHERE table_schema=@schema_name AND table_name='ccdi_enterprise_base_info' AND column_name='register_date'), 'SELECT 1', 'ALTER TABLE ccdi_enterprise_base_info ADD COLUMN register_date DATE NULL COMMENT ''注册日期'' AFTER registered_capital_unit');
PREPARE stmt FROM @ddl; EXECUTE stmt; DEALLOCATE PREPARE stmt;
SET @ddl = IF(EXISTS(SELECT 1 FROM information_schema.columns WHERE table_schema=@schema_name AND table_name='ccdi_enterprise_base_info' AND column_name='region_code'), 'SELECT 1', 'ALTER TABLE ccdi_enterprise_base_info ADD COLUMN region_code VARCHAR(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL COMMENT ''区域编码'' AFTER register_date');
PREPARE stmt FROM @ddl; EXECUTE stmt; DEALLOCATE PREPARE stmt;
SET @ddl = IF(EXISTS(SELECT 1 FROM information_schema.columns WHERE table_schema=@schema_name AND table_name='ccdi_enterprise_base_info' AND column_name='region_name'), 'SELECT 1', 'ALTER TABLE ccdi_enterprise_base_info ADD COLUMN region_name VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL COMMENT ''区域名称'' AFTER region_code');
PREPARE stmt FROM @ddl; EXECUTE stmt; DEALLOCATE PREPARE stmt;
SET @ddl = IF(EXISTS(SELECT 1 FROM information_schema.columns WHERE table_schema=@schema_name AND table_name='ccdi_enterprise_base_info' AND column_name='employee_count'), 'SELECT 1', 'ALTER TABLE ccdi_enterprise_base_info ADD COLUMN employee_count INT NULL COMMENT ''从业人数'' AFTER region_name');
PREPARE stmt FROM @ddl; EXECUTE stmt; DEALLOCATE PREPARE stmt;
SET @ddl = IF(EXISTS(SELECT 1 FROM information_schema.columns WHERE table_schema=@schema_name AND table_name='ccdi_enterprise_base_info' AND column_name='cache_info_id'), 'SELECT 1', 'ALTER TABLE ccdi_enterprise_base_info ADD COLUMN cache_info_id BIGINT NULL COMMENT ''工商缓存ID'' AFTER employee_count');
PREPARE stmt FROM @ddl; EXECUTE stmt; DEALLOCATE PREPARE stmt;
SET @ddl = IF(EXISTS(SELECT 1 FROM information_schema.columns WHERE table_schema=@schema_name AND table_name='ccdi_enterprise_base_info' AND column_name='enterprise_sync_time'), 'SELECT 1', 'ALTER TABLE ccdi_enterprise_base_info ADD COLUMN enterprise_sync_time DATETIME NULL COMMENT ''工商同步时间'' AFTER cache_info_id');
PREPARE stmt FROM @ddl; EXECUTE stmt; DEALLOCATE PREPARE stmt;
ALTER TABLE `ccdi_enterprise_base_info` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

-- 只读接口日志菜单。
SET @log_parent_id = (SELECT menu_id FROM sys_menu WHERE menu_type='M' AND menu_name='日志管理' ORDER BY menu_id LIMIT 1);
SET @api_log_menu_id = (SELECT menu_id FROM sys_menu WHERE perms='monitor:apilog:list' ORDER BY menu_id LIMIT 1);
SET @next_menu_id = (SELECT next_id FROM (SELECT COALESCE(MAX(menu_id), 0) + 1 AS next_id FROM sys_menu) t);
SET @api_log_menu_id = COALESCE(@api_log_menu_id, @next_menu_id);
INSERT INTO sys_menu (menu_id, menu_name, parent_id, order_num, path, component, query, route_name, is_frame, is_cache, menu_type, visible, status, perms, icon, create_by, create_time, update_by, update_time, remark)
SELECT @api_log_menu_id, '接口日志', @log_parent_id, 3, 'apilog', 'monitor/apilog/index', '', '', 1, 0, 'C', '0', '0', 'monitor:apilog:list', 'form', 'admin', NOW(), '', NULL, '只读外部接口调用日志'
WHERE @log_parent_id IS NOT NULL AND NOT EXISTS (SELECT 1 FROM sys_menu WHERE perms='monitor:apilog:list');

-- 工商刷新和历史补全权限。
SET @enterprise_menu_id = (SELECT menu_id FROM sys_menu WHERE perms='ccdi:enterpriseBaseInfo:list' ORDER BY menu_id LIMIT 1);
SET @project_menu_id = (SELECT menu_id FROM sys_menu WHERE perms='ccdi:project:list' ORDER BY menu_id LIMIT 1);
SET @next_menu_id = (SELECT next_id FROM (SELECT COALESCE(MAX(menu_id), 0) + 1 AS next_id FROM sys_menu) t);
INSERT INTO sys_menu (menu_id, menu_name, parent_id, order_num, path, component, query, route_name, is_frame, is_cache, menu_type, visible, status, perms, icon, create_by, create_time, update_by, update_time, remark)
SELECT @next_menu_id, '实体工商刷新', @enterprise_menu_id, 6, '', NULL, '', '', 1, 0, 'F', '0', '0', 'ccdi:enterpriseBaseInfo:refresh', '#', 'admin', NOW(), '', NULL, ''
WHERE @enterprise_menu_id IS NOT NULL AND NOT EXISTS (SELECT 1 FROM sys_menu WHERE perms='ccdi:enterpriseBaseInfo:refresh');
SET @next_menu_id = (SELECT next_id FROM (SELECT COALESCE(MAX(menu_id), 0) + 1 AS next_id FROM sys_menu) t);
INSERT INTO sys_menu (menu_id, menu_name, parent_id, order_num, path, component, query, route_name, is_frame, is_cache, menu_type, visible, status, perms, icon, create_by, create_time, update_by, update_time, remark)
SELECT @next_menu_id, '实体工商补全', @enterprise_menu_id, 7, '', NULL, '', '', 1, 0, 'F', '0', '0', 'ccdi:enterpriseBaseInfo:backfill', '#', 'admin', NOW(), '', NULL, ''
WHERE @enterprise_menu_id IS NOT NULL AND NOT EXISTS (SELECT 1 FROM sys_menu WHERE perms='ccdi:enterpriseBaseInfo:backfill');
SET @next_menu_id = (SELECT next_id FROM (SELECT COALESCE(MAX(menu_id), 0) + 1 AS next_id FROM sys_menu) t);
INSERT INTO sys_menu (menu_id, menu_name, parent_id, order_num, path, component, query, route_name, is_frame, is_cache, menu_type, visible, status, perms, icon, create_by, create_time, update_by, update_time, remark)
SELECT @next_menu_id, '对手方工商刷新', @project_menu_id, 6, '', NULL, '', '', 1, 0, 'F', '0', '0', 'ccdi:project:counterpartyEnterprise:refresh', '#', 'admin', NOW(), '', NULL, ''
WHERE @project_menu_id IS NOT NULL AND NOT EXISTS (SELECT 1 FROM sys_menu WHERE perms='ccdi:project:counterpartyEnterprise:refresh');
SET @next_menu_id = (SELECT next_id FROM (SELECT COALESCE(MAX(menu_id), 0) + 1 AS next_id FROM sys_menu) t);
INSERT INTO sys_menu (menu_id, menu_name, parent_id, order_num, path, component, query, route_name, is_frame, is_cache, menu_type, visible, status, perms, icon, create_by, create_time, update_by, update_time, remark)
SELECT @next_menu_id, '对手方工商补全', @project_menu_id, 7, '', NULL, '', '', 1, 0, 'F', '0', '0', 'ccdi:project:counterpartyEnterprise:backfill', '#', 'admin', NOW(), '', NULL, ''
WHERE @project_menu_id IS NOT NULL AND NOT EXISTS (SELECT 1 FROM sys_menu WHERE perms='ccdi:project:counterpartyEnterprise:backfill');

INSERT IGNORE INTO sys_role_menu (role_id, menu_id)
SELECT 1, menu_id FROM sys_menu
WHERE perms IN ('monitor:apilog:list', 'ccdi:enterpriseBaseInfo:refresh', 'ccdi:enterpriseBaseInfo:backfill',
                'ccdi:project:counterpartyEnterprise:refresh', 'ccdi:project:counterpartyEnterprise:backfill');