package com.emergencyrepair.bootstrap;

import lombok.RequiredArgsConstructor;
import org.springframework.boot.CommandLineRunner;
import org.springframework.core.annotation.Order;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Component;

import java.util.LinkedHashMap;
import java.util.Map;

@Component
@Order(1)
@RequiredArgsConstructor
public class DatabaseBootstrap implements CommandLineRunner {
    private final JdbcTemplate jdbcTemplate;

    @Override
    public void run(String... args) {
        ensureSchema();
        for (Map.Entry<String, String> entry : tableDdl().entrySet()) {
            if (!tableExists(entry.getKey())) {
                jdbcTemplate.execute(entry.getValue());
            }
        }
        renameColumnIfNeeded("T_FAULT", "FAULT_DESCRIPTION", "ENGINEERING_NAME_DESC", "ALTER TABLE LX.T_FAULT RENAME COLUMN FAULT_DESCRIPTION TO ENGINEERING_NAME_DESC");
        ensureColumn("T_FAULT", "EQUIPMENT_IDS", "ALTER TABLE LX.T_FAULT ADD EQUIPMENT_IDS VARCHAR(2000)");
        ensureColumn("T_COMPLETION_INFO", "CONSUMABLE_COUNT", "ALTER TABLE LX.T_COMPLETION_INFO ADD CONSUMABLE_COUNT INT");
        ensureColumn("T_COMPLETION_INFO", "SOLUTIONS_JSON", "ALTER TABLE LX.T_COMPLETION_INFO ADD SOLUTIONS_JSON CLOB");
        ensureIndex("UX_T_FAULT_FAULT_CODE", "CREATE UNIQUE INDEX LX.UX_T_FAULT_FAULT_CODE ON LX.T_FAULT(FAULT_CODE)");
        ensureIndex("UX_T_WORK_ORDER_NO", "CREATE UNIQUE INDEX LX.UX_T_WORK_ORDER_NO ON LX.T_WORK_ORDER(WORK_ORDER_NO)");
        ensureIndex("IX_T_WORK_ORDER_FAULT_ID", "CREATE INDEX LX.IX_T_WORK_ORDER_FAULT_ID ON LX.T_WORK_ORDER(FAULT_ID)");
        ensureIndex("IX_T_COMPLETION_WORK_ORDER", "CREATE INDEX LX.IX_T_COMPLETION_WORK_ORDER ON LX.T_COMPLETION_INFO(WORK_ORDER_ID)");
    }

    private void ensureSchema() {
        Integer count = jdbcTemplate.queryForObject(
                "SELECT COUNT(*) FROM ALL_USERS WHERE USERNAME = ?", Integer.class, "LX");
        if (count == null || count == 0) {
            jdbcTemplate.execute("CREATE SCHEMA LX AUTHORIZATION SYSDBA");
        }
        try {
            jdbcTemplate.execute("CREATE OR REPLACE SYNONYM LX.SYS_USER FOR CSICZB_ZB.SYS_USER");
            jdbcTemplate.execute("CREATE OR REPLACE SYNONYM LX.SYS_DEPT FOR CSICZB_ZB.SYS_DEPT");
        } catch (Exception ignored) {
            // Synonyms are optional for the first version. Biz tables remain fully usable.
        }
    }

    private boolean tableExists(String tableName) {
        Integer count = jdbcTemplate.queryForObject(
                "SELECT COUNT(*) FROM ALL_TABLES WHERE OWNER = 'LX' AND TABLE_NAME = ?",
                Integer.class, tableName);
        return count != null && count > 0;
    }

    private void ensureColumn(String tableName, String columnName, String ddl) {
        if (!columnExists(tableName, columnName)) {
            jdbcTemplate.execute(ddl);
        }
    }

    private void renameColumnIfNeeded(String tableName, String oldColumnName, String newColumnName, String ddl) {
        if (!columnExists(tableName, newColumnName) && columnExists(tableName, oldColumnName)) {
            jdbcTemplate.execute(ddl);
        }
    }

    private boolean columnExists(String tableName, String columnName) {
        Integer count = jdbcTemplate.queryForObject(
                "SELECT COUNT(*) FROM ALL_TAB_COLUMNS WHERE OWNER = ? AND TABLE_NAME = ? AND COLUMN_NAME = ?",
                Integer.class, "LX", tableName, columnName);
        return count != null && count > 0;
    }

    private void ensureIndex(String indexName, String ddl) {
        Integer count = jdbcTemplate.queryForObject(
                "SELECT COUNT(*) FROM ALL_INDEXES WHERE OWNER = 'LX' AND INDEX_NAME = ?",
                Integer.class, indexName);
        if (count == null || count == 0) {
            jdbcTemplate.execute(ddl);
        }
    }

    private Map<String, String> tableDdl() {
        Map<String, String> ddl = new LinkedHashMap<>();
        ddl.put("T_FAULT", "CREATE TABLE LX.T_FAULT (" +
                "ID BIGINT NOT NULL PRIMARY KEY, FAULT_CODE VARCHAR(80), X_NO VARCHAR(40), EQUIPMENT_ID BIGINT, EQUIPMENT_IDS VARCHAR(2000), EQUIPMENT_NAME VARCHAR(200), PARENT_SYSTEM VARCHAR(200), ENGINEERING_MAJOR VARCHAR(100), TEAM_MAJOR VARCHAR(100), REPORT_UNIT VARCHAR(200), REPORT_DATE DATE, OWNER VARCHAR(80), FAULT_IMPACT VARCHAR(100), FAULT_NATURE VARCHAR(100), MAJOR_FAULT_DESC VARCHAR(2000), REPAIR_REQUIREMENT VARCHAR(2000), ENGINEERING_NAME_DESC VARCHAR(2000), IS_SELF_REPAIR INT, IS_H_RELATED INT, IS_MONITOR_ITEM INT, MONITOR_ITEM VARCHAR(200), MONITOR_VALUE VARCHAR(200), REQUESTED_FINISH_DATE DATE, REMARK VARCHAR(2000), TASK_STATUS VARCHAR(40), CONTRACTOR_IDS VARCHAR(1000), CONTRACTOR_NAMES VARCHAR(2000), TRACKING_TEAM VARCHAR(100), ENGINEERING_NO VARCHAR(120), XCB_REVIEW_TIME TIMESTAMP, CMB_OPINION VARCHAR(2000), CMB_RESULT VARCHAR(20), CMB_TIME TIMESTAMP, JCC_ASSISTANT_OPINION VARCHAR(2000), JCC_ASSISTANT_RESULT VARCHAR(20), JCC_ASSISTANT_TIME TIMESTAMP, JCC_DIRECTOR_OPINION VARCHAR(2000), JCC_DIRECTOR_RESULT VARCHAR(20), JCC_DIRECTOR_TIME TIMESTAMP, DISPATCH_TIME TIMESTAMP, CANCEL_REASON VARCHAR(1000), CANCELLED_BY VARCHAR(80), CANCEL_TIME TIMESTAMP, CREATE_TIME TIMESTAMP, UPDATE_TIME TIMESTAMP)");
        ddl.put("T_ENGINEERING_MAJOR", "CREATE TABLE LX.T_ENGINEERING_MAJOR (ID BIGINT NOT NULL PRIMARY KEY, NAME VARCHAR(200), CREATE_TIME TIMESTAMP, UPDATE_TIME TIMESTAMP)");
        ddl.put("T_TEAM_MAJOR", "CREATE TABLE LX.T_TEAM_MAJOR (ID BIGINT NOT NULL PRIMARY KEY, NAME VARCHAR(200), CATEGORY VARCHAR(10), CREATE_TIME TIMESTAMP, UPDATE_TIME TIMESTAMP)");
        ddl.put("T_CONTRACTOR", "CREATE TABLE LX.T_CONTRACTOR (ID BIGINT NOT NULL PRIMARY KEY, NAME VARCHAR(200), SHORT_NAME VARCHAR(80), NATURE VARCHAR(100), SORT_NO INT, CONTACT VARCHAR(120), DISHONEST INT, REMARK VARCHAR(1000), CREATE_TIME TIMESTAMP, UPDATE_TIME TIMESTAMP)");
        ddl.put("T_BASE_EQUIPMENT", "CREATE TABLE LX.T_BASE_EQUIPMENT (ID BIGINT NOT NULL PRIMARY KEY, PARENT_ID BIGINT, NODE_LEVEL INT, NAME VARCHAR(200), CODE VARCHAR(80), SORT_NO INT, REMARK VARCHAR(1000), CREATE_TIME TIMESTAMP, UPDATE_TIME TIMESTAMP)");
        ddl.put("T_PRODUCT_TREE", "CREATE TABLE LX.T_PRODUCT_TREE (ID BIGINT NOT NULL PRIMARY KEY, PARENT_ID BIGINT, NODE_LEVEL INT, NODE_TYPE VARCHAR(40), NAME VARCHAR(200), CODE VARCHAR(80), BASE_EQUIPMENT_ID BIGINT, SORT_NO INT, REMARK VARCHAR(1000), CREATE_TIME TIMESTAMP, UPDATE_TIME TIMESTAMP)");
        ddl.put("T_WORK_ORDER", "CREATE TABLE LX.T_WORK_ORDER (ID BIGINT NOT NULL PRIMARY KEY, WORK_ORDER_NO VARCHAR(120), FAULT_ID BIGINT, FAULT_CODE VARCHAR(80), CONTRACTOR_ID BIGINT, CONTRACTOR_NAME VARCHAR(200), X_NO VARCHAR(40), EQUIPMENT_NAME VARCHAR(200), ENGINEERING_MAJOR VARCHAR(100), TEAM_MAJOR VARCHAR(100), PARENT_SYSTEM VARCHAR(200), REPORT_UNIT VARCHAR(200), REPORT_DATE DATE, REVIEW_TIME TIMESTAMP, DISPATCH_TIME TIMESTAMP, OWNER VARCHAR(80), REQUESTED_FINISH_DATE DATE, ENGINEERING_NAME_DESC VARCHAR(2000), REPAIR_REQUIREMENT VARCHAR(2000), FAULT_IMPACT VARCHAR(100), FAULT_NATURE VARCHAR(100), IS_SELF_REPAIR INT, IS_H_RELATED INT, IS_MONITOR_ITEM INT, MONITOR_ITEM VARCHAR(200), MONITOR_VALUE VARCHAR(200), REMARK VARCHAR(2000), COMPLETION_STATUS VARCHAR(40), SCORE INT, QUALITY_OPINION VARCHAR(2000), CREATE_TIME TIMESTAMP, UPDATE_TIME TIMESTAMP)");
        ddl.put("T_COMPLETION_INFO", "CREATE TABLE LX.T_COMPLETION_INFO (ID BIGINT NOT NULL PRIMARY KEY, WORK_ORDER_ID BIGINT, WORK_ORDER_NO VARCHAR(120), X_NO VARCHAR(40), CONTRACTOR_NAME VARCHAR(200), ENGINEERING_NO VARCHAR(120), START_DATE DATE, FINISH_DATE DATE, CONTRACTOR_PERSON VARCHAR(100), T_INTERNAL_CONSTRUCTION VARCHAR(200), RELATED_PROJECT VARCHAR(500), DURATION_DAYS INT, PERSON_COUNT INT, ACCEPTANCE_MODE VARCHAR(100), QUALITY_INFO VARCHAR(2000), REASON_ANALYSIS VARCHAR(2000), FAULT_DESCRIPTION VARCHAR(2000), ITEM_NAME VARCHAR(500), SOLUTION VARCHAR(2000), CONSUMABLES_JSON CLOB, CONSUMABLE_COUNT INT, SOLUTIONS_JSON CLOB, SUBMIT_TIME TIMESTAMP, CREATE_TIME TIMESTAMP, UPDATE_TIME TIMESTAMP)");
        ddl.put("T_ACCEPTANCE", "CREATE TABLE LX.T_ACCEPTANCE (ID BIGINT NOT NULL PRIMARY KEY, WORK_ORDER_ID BIGINT, WORK_ORDER_NO VARCHAR(120), X_NO VARCHAR(40), CONTRACTOR_NAME VARCHAR(200), STAGE VARCHAR(60), STATUS VARCHAR(60), APPLY_TIME TIMESTAMP, APPLY_REMARK VARCHAR(2000), INSPECTOR_OPINION VARCHAR(2000), INSPECTOR_RESULT VARCHAR(20), INSPECTOR_TIME TIMESTAMP, LEADER_OPINION VARCHAR(2000), LEADER_RESULT VARCHAR(20), LEADER_TIME TIMESTAMP, PRINT_TIME TIMESTAMP, RATING VARCHAR(20), RATING_REMARK VARCHAR(2000), RATING_TIME TIMESTAMP, CREATE_TIME TIMESTAMP, UPDATE_TIME TIMESTAMP)");
        ddl.put("T_SYS_MENU", "CREATE TABLE LX.T_SYS_MENU (ID BIGINT NOT NULL PRIMARY KEY, PID BIGINT, NAME VARCHAR(100), URL VARCHAR(200), ICON VARCHAR(80), MENU_TYPE INT, PERMISSIONS VARCHAR(200), SORT_NO INT, VISIBLE INT, CREATE_TIME TIMESTAMP, UPDATE_TIME TIMESTAMP)");
        ddl.put("T_SYS_ROLE", "CREATE TABLE LX.T_SYS_ROLE (ID BIGINT NOT NULL PRIMARY KEY, NAME VARCHAR(100), CODE VARCHAR(80), ROLE_TYPE VARCHAR(40), SORT_NO INT, REMARK VARCHAR(1000), CREATE_TIME TIMESTAMP, UPDATE_TIME TIMESTAMP)");
        ddl.put("T_SYS_ROLE_MENU", "CREATE TABLE LX.T_SYS_ROLE_MENU (ID BIGINT NOT NULL PRIMARY KEY, ROLE_ID BIGINT, MENU_ID BIGINT, CREATE_TIME TIMESTAMP)");
        ddl.put("T_SYS_TENANT_MENU", "CREATE TABLE LX.T_SYS_TENANT_MENU (ID BIGINT NOT NULL PRIMARY KEY, TENANT_ID BIGINT, MENU_ID BIGINT, CREATE_TIME TIMESTAMP)");
        ddl.put("T_SYS_ROLE_USER", "CREATE TABLE LX.T_SYS_ROLE_USER (ID BIGINT NOT NULL PRIMARY KEY, ROLE_ID BIGINT, USER_ID BIGINT, CREATE_TIME TIMESTAMP)");
        return ddl;
    }
}