from pathlib import Path
from openpyxl import Workbook

out_dir = Path(r"d:/Projects/Ai-DOP/SourceCode/ai-dop-platform/docs/db")
out_dir.mkdir(parents=True, exist_ok=True)

tables = [
("ado_intg_system","外部系统注册"),("ado_intg_connector","连接器配置"),("ado_intg_field_mapping","字段映射"),("ado_intg_code_mapping","码值映射"),("ado_intg_sync_checkpoint","同步游标"),("ado_intg_event_raw","原始事件落地"),
("ado_md_customer","客户主数据"),("ado_md_supplier","供应商主数据"),("ado_md_item","物料主数据"),("ado_md_work_center","工作中心"),("ado_md_process_route","工艺路线"),("ado_md_process_route_step","工艺步骤"),("ado_md_calendar","工作日历"),
("ado_s1_sales_order","销售订单头"),("ado_s1_sales_order_line","销售订单行"),("ado_s1_delivery_promise","交付承诺"),("ado_s1_delivery_change","交付变更"),
("ado_s2_mps_plan","主生产计划"),("ado_s2_work_order","工单"),("ado_s2_work_order_op","工单工序执行"),("ado_s2_capacity_snapshot","产能快照"),
("ado_s3_mrp_result","MRP结果"),("ado_s3_material_kit_status","物料齐套状态"),("ado_s3_supplier_delivery_perf","供应商准交绩效"),
("ado_s4_purchase_order","采购订单头"),("ado_s4_purchase_order_line","采购订单行"),("ado_s4_receipt","收货记录"),("ado_s4_return","退货记录"),
("ado_s5_inventory_balance","库存余额"),("ado_s5_inventory_txn","库存流水"),("ado_s5_iqc_result","来料检验"),
("ado_s6_report_work","报工记录"),("ado_s6_ipqc_result","过程检验"),("ado_s6_equipment_oee","设备OEE快照"),
("ado_s7_fg_inbound","成品入库"),("ado_s7_fg_outbound","成品出库"),("ado_s7_oqc_result","成品检验"),("ado_s7_trace_serial","序列号追溯"),
("ado_s8_rule_model","S8监控规则模型"),("ado_s8_rule_condition","S8规则条件"),("ado_s8_rule_scope","S8规则范围"),("ado_s8_alert_model","S8预警模型"),("ado_s8_alert_channel","S8预警通道"),("ado_s8_alert_record","S8预警记录"),
("ado_s8_incident","S8异常单"),("ado_s8_incident_dispatch","S8异常分派"),("ado_s8_incident_action","S8异常处置"),("ado_s8_incident_verify","S8异常验证关闭"),("ado_s8_rule_eval_log","S8规则评估日志"),("ado_s8_rule_hit_fact","S8规则命中事实"),
("ado_s9_kpi_def","KPI定义"),("ado_s9_kpi_relation","KPI父子关系"),("ado_s9_kpi_page_binding","KPI页面绑定"),("ado_s9_kpi_formula","KPI公式版本"),("ado_s9_kpi_value_l1_day","KPI一级日值"),("ado_s9_kpi_value_l2_day","KPI二级日值"),("ado_s9_kpi_value_l3_day","KPI三级日值"),
("ado_s9_dashboard_snapshot","管理看板快照"),("ado_s9_drilldown_cache","下钻缓存"),("ado_s9_calc_task","KPI计算任务"),("ado_s9_calc_log","KPI计算日志"),("ado_s9_chatbi_semantic","ChatBI语义模型"),("ado_s9_chatbi_query_log","ChatBI查询日志"),
("ado_mobile_kpi_snapshot","移动端KPI快照"),("ado_mobile_sync_cursor","移动端同步游标"),("ado_mobile_alert_ack","移动端预警签收"),("ado_mobile_incident_action","移动端异常操作轨迹"),
]

base_cols = [
("id","bigint","否","","主键"),("tenant_id","bigint","否","","租户ID"),("org_id","bigint","是","","集团ID"),("company_id","bigint","是","","公司ID"),("factory_id","bigint","否","","工厂ID"),
("status","varchar(32)","是","","状态"),("biz_date","date","是","","业务日期"),("create_time","datetime(3)","否","current_timestamp(3)","创建时间"),("update_time","datetime(3)","是","","更新时间"),("is_deleted","tinyint","否","0","删除标识"),("is_active","tinyint","否","1","有效标识"),
]

sql_path = out_dir / "V1__aidopdev_all_tables.sql"
with sql_path.open("w", encoding="utf-8") as f:
    f.write("create database if not exists aidopdev default character set utf8mb4 collate utf8mb4_general_ci;\n")
    f.write("use aidopdev;\n\n")
    for t, desc in tables:
        f.write(f"drop table if exists {t};\n")
        f.write(f"create table {t} (\n")
        lines=[]
        for n,tp,nullable,default,comment in base_cols:
            nn = "not null" if nullable=="否" else "null"
            if default:
                d = f" default {default}" if default.startswith("current_timestamp") else f" default '{default}'"
            else:
                d = ""
            lines.append(f"  {n} {tp} {nn}{d} comment '{comment}'")
        lines.append("  primary key (id)")
        lines.append("  ,key idx_tfbd (tenant_id, factory_id, biz_date)")
        f.write(",\n".join(lines))
        f.write(f"\n) engine=innodb comment='{desc}';\n\n")

xlsx_path = out_dir / "aidopdev_表字段说明.xlsx"
wb = Workbook()
ws = wb.active
ws.title = "表清单"
ws.append(["序号","表名","表说明","字段数"])
for i, (t,d) in enumerate(tables,1):
    ws.append([i,t,d,len(base_cols)])

ws2 = wb.create_sheet("字段说明")
ws2.append(["表名","表说明","字段名","类型","可空","默认值","字段说明"])
for t,d in tables:
    for c in base_cols:
        ws2.append([t,d,c[0],c[1],c[2],c[3],c[4]])

wb.save(xlsx_path)
print(str(sql_path))
print(str(xlsx_path))
print(len(tables))