-- 发票同步定时任务配置
-- 用于从旧系统同步发票信息到新系统

-- 1. 创建发票同步日志表
CREATE TABLE IF NOT EXISTS `sys_invoice_sync_log` (
  `log_id` BIGINT(20) NOT NULL AUTO_INCREMENT COMMENT '同步日志ID',
  `sync_type` VARCHAR(50) NOT NULL COMMENT '同步类型(invoice_info-发票信息,invoice_status-发票状态)',
  `sync_start_time` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '同步开始时间',
  `sync_end_time` DATETIME DEFAULT NULL COMMENT '同步结束时间',
  `records_processed` INT(11) DEFAULT 0 COMMENT '处理记录数',
  `records_success` INT(11) DEFAULT 0 COMMENT '成功记录数',
  `records_failed` INT(11) DEFAULT 0 COMMENT '失败记录数',
  `error_message` TEXT DEFAULT NULL COMMENT '错误信息',
  `status` TINYINT(1) DEFAULT 0 COMMENT '状态(0-处理中,1-成功,2-失败)',
  PRIMARY KEY (`log_id`),
  INDEX `idx_sync_type` (`sync_type`),
  INDEX `idx_sync_start_time` (`sync_start_time`)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COMMENT='发票同步日志表';

-- 2. 插入发票同步定时任务配置
DELETE FROM sys_job WHERE job_name = 'InvoiceSyncJob';
INSERT INTO sys_job (
    job_name, 
    job_group, 
    invoke_target, 
    cron_expression, 
    misfire_policy, 
    concurrent, 
    status, 
    create_by, 
    create_time
) VALUES (
    'InvoiceSyncJob',
    'SYSTEM',
    'sysInvoiceTask.syncInvoiceFromLegacySystem',
    '0 0/30 * * * ?',  -- 每30分钟执行一次
    '3',
    '1',
    '0',
    'admin',
    NOW()
);

-- 3. 同步发票信息的存储过程
DELIMITER $$

DROP PROCEDURE IF EXISTS sync_invoice_from_legacy$$

CREATE PROCEDURE sync_invoice_from_legacy()
BEGIN
    DECLARE v_start_time DATETIME DEFAULT NOW();
    DECLARE v_error_msg TEXT DEFAULT '';
    DECLARE v_processed_count INT DEFAULT 0;
    DECLARE v_success_count INT DEFAULT 0;
    DECLARE v_failed_count INT DEFAULT 0;
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        GET DIAGNOSTICS CONDITION 1
            v_error_msg = MESSAGE_TEXT;
        ROLLBACK;
    END;

    START TRANSACTION;
    
    -- 记录开始同步
    INSERT INTO sys_invoice_sync_log (sync_type, sync_start_time, status)
    VALUES ('invoice_info', v_start_time, 0);
    
    SET @log_id = LAST_INSERT_ID();
    
    -- 同步新的发票记录
    INSERT INTO sys_invoice (
        legacy_invoice_id,
        legacy_service_order_id,
        invoice_type,
        invoice_name,
        invoice_money,
        invoice_remarks,
        company_address,
        company_bank,
        company_bank_no,
        zip_code,
        mail_address,
        contact_name,
        contact_phone,
        contact_email,
        status,
        invoice_no,
        invoice_url,
        apply_time,
        audit_time,
        audit_remarks,
        sync_status
    )
    SELECT 
        i.InvoiceID as legacy_invoice_id,
        i.ServiceOrderIDPK as legacy_service_order_id,
        CASE 
            WHEN i.InvoiceType = 1 THEN 1  -- 个人发票
            WHEN i.InvoiceType = 2 THEN 2  -- 企业发票
            ELSE 1  -- 默认个人发票
        END as invoice_type,
        i.InvoiceName as invoice_name,
        CAST(i.InvoiceMoney AS DECIMAL(10,2)) as invoice_money,
        i.InvoiceMakeout as invoice_remarks,
        i.InvoiceCompanyAdd as company_address,
        i.InvoiceCompanyBank as company_bank,
        i.InvoiceCompanyBankNo as company_bank_no,
        i.InvoiceZipCode as zip_code,
        i.Invoice_strAdd as mail_address,
        i.Invoice_strName as contact_name,
        i.Invoice_strPhone as contact_phone,
        i.Invoice_strEmail as contact_email,
        CASE 
            WHEN i.AuditStatus = 1 THEN 1  -- 已通过
            WHEN i.AuditStatus = 2 THEN 2  -- 已驳回
            ELSE 0  -- 待审核
        END as status,
        i.InvoiceNo as invoice_no,
        COALESCE(i.InvoiceURL, i.EleCloud_PDF) as invoice_url,
        i.ApplicationTime as apply_time,
        i.AuditTime as audit_time,
        i.AuditMakeout as audit_remarks,
        1 as sync_status  -- 标记为已同步
    FROM InvoiceData i
    WHERE i.InvoiceID NOT IN (
        SELECT legacy_invoice_id 
        FROM sys_invoice 
        WHERE legacy_invoice_id IS NOT NULL
    );
    
    SET v_processed_count = ROW_COUNT();
    SET v_success_count = v_processed_count;
    
    -- 更新同步日志
    UPDATE sys_invoice_sync_log 
    SET 
        sync_end_time = NOW(),
        records_processed = v_processed_count,
        records_success = v_success_count,
        records_failed = v_failed_count,
        error_message = v_error_msg,
        status = IF(v_error_msg = '', 1, 2)
    WHERE log_id = @log_id;
    
    COMMIT;
END$$

DELIMITER ;

-- 4. 同步发票状态的存储过程
DELIMITER $$

DROP PROCEDURE IF EXISTS sync_invoice_status_from_legacy$$

CREATE PROCEDURE sync_invoice_status_from_legacy()
BEGIN
    DECLARE v_start_time DATETIME DEFAULT NOW();
    DECLARE v_error_msg TEXT DEFAULT '';
    DECLARE v_processed_count INT DEFAULT 0;
    DECLARE v_success_count INT DEFAULT 0;
    DECLARE v_failed_count INT DEFAULT 0;
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        GET DIAGNOSTICS CONDITION 1
            v_error_msg = MESSAGE_TEXT;
        ROLLBACK;
    END;

    START TRANSACTION;
    
    -- 记录开始同步
    INSERT INTO sys_invoice_sync_log (sync_type, sync_start_time, status)
    VALUES ('invoice_status', v_start_time, 0);
    
    SET @log_id = LAST_INSERT_ID();
    
    -- 更新现有发票的状态信息
    UPDATE sys_invoice si
    INNER JOIN InvoiceData i ON si.legacy_invoice_id = i.InvoiceID
    SET 
        si.status = CASE 
            WHEN i.AuditStatus = 1 THEN 1  -- 已通过
            WHEN i.AuditStatus = 2 THEN 2  -- 已驳回
            ELSE 0  -- 待审核
        END,
        si.invoice_no = COALESCE(si.invoice_no, i.InvoiceNo),
        si.invoice_url = COALESCE(si.invoice_url, i.InvoiceURL, i.EleCloud_PDF),
        si.audit_time = i.AuditTime,
        si.audit_remarks = i.AuditMakeout,
        si.sync_status = 1
    WHERE si.legacy_invoice_id IS NOT NULL
      AND (
          si.status != CASE 
              WHEN i.AuditStatus = 1 THEN 1
              WHEN i.AuditStatus = 2 THEN 2
              ELSE 0
          END
          OR si.invoice_no IS NULL
          OR si.invoice_url IS NULL
      );
    
    SET v_processed_count = ROW_COUNT();
    SET v_success_count = v_processed_count;
    
    -- 更新同步日志
    UPDATE sys_invoice_sync_log 
    SET 
        sync_end_time = NOW(),
        records_processed = v_processed_count,
        records_success = v_success_count,
        records_failed = v_failed_count,
        error_message = v_error_msg,
        status = IF(v_error_msg = '', 1, 2)
    WHERE log_id = @log_id;
    
    COMMIT;
END$$

DELIMITER ;

-- 5. 查询发票同步日志的视图
CREATE OR REPLACE VIEW v_invoice_sync_log AS
SELECT 
    l.log_id,
    l.sync_type,
    l.sync_start_time,
    l.sync_end_time,
    l.records_processed,
    l.records_success,
    l.records_failed,
    l.error_message,
    l.status,
    CASE l.status 
        WHEN 0 THEN '处理中' 
        WHEN 1 THEN '成功' 
        WHEN 2 THEN '失败' 
        ELSE '未知' 
    END as status_name,
    TIMESTAMPDIFF(SECOND, l.sync_start_time, l.sync_end_time) as duration_seconds
FROM sys_invoice_sync_log l
ORDER BY l.sync_start_time DESC;

-- 6. 手动执行发票信息同步
-- CALL sync_invoice_from_legacy();

-- 7. 手动执行发票状态同步
-- CALL sync_invoice_status_from_legacy();