-- 从旧系统同步发票信息到新系统的SQL脚本
-- 同步旧系统的发票数据到新系统，避免重复同步

-- 方案1: 使用INSERT IGNORE插入新数据
INSERT IGNORE 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
);

-- 方案2: 使用LEFT JOIN确保只插入不存在的记录
-- 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,
--     i.ServiceOrderIDPK,
--     CASE 
--         WHEN i.InvoiceType = 1 THEN 1
--         WHEN i.InvoiceType = 2 THEN 2
--         ELSE 1
--     END,
--     i.InvoiceName,
--     CAST(i.InvoiceMoney AS DECIMAL(10,2)),
--     i.InvoiceMakeout,
--     i.InvoiceCompanyAdd,
--     i.InvoiceCompanyBank,
--     i.InvoiceCompanyBankNo,
--     i.InvoiceZipCode,
--     i.Invoice_strAdd,
--     i.Invoice_strName,
--     i.Invoice_strPhone,
--     i.Invoice_strEmail,
--     CASE 
--         WHEN i.AuditStatus = 1 THEN 1
--         WHEN i.AuditStatus = 2 THEN 2
--         ELSE 0
--     END,
--     i.InvoiceNo,
--     COALESCE(i.InvoiceURL, i.EleCloud_PDF),
--     i.ApplicationTime,
--     i.AuditTime,
--     i.AuditMakeout,
--     1
-- FROM InvoiceData i
-- LEFT JOIN sys_invoice si ON i.InvoiceID = si.legacy_invoice_id
-- WHERE si.legacy_invoice_id IS NULL;

-- 更新统计信息
ANALYZE TABLE sys_invoice;