import java.sql.*;
import java.util.*;

/**
 * Verify effective lobster execution policy for a tenant + company.
 * Mirrors LobsterExecutionPolicyService.resolve merge logic.
 */
public class VerifyLobsterExecutionPolicy {
    private static final String HOST = "cq-cdb-8fjmemkb.sql.tencentcdb.com";
    private static final int PORT = 27220;
    private static final String MASTER_USER = "root";
    private static final String MASTER_PWD = "Ylrz_1q2w3e4r5t6y";
    private static final String MASTER = "ylrz_saas";

    private static final long TENANT_ID = 33L;
    private static final long COMPANY_ID = 338L;

    public static void main(String[] args) throws Exception {
        long tenantId = args.length > 0 ? Long.parseLong(args[0]) : TENANT_ID;
        long companyId = args.length > 1 ? Long.parseLong(args[1]) : COMPANY_ID;

        TenantDb tenant = resolveTenantDb(tenantId);
        System.out.println("=== Tenant " + tenantId + " db=" + tenant.database + " companyId=" + companyId + " ===\n");

        try (Connection c = connect(tenant.database, tenant.user, tenant.password)) {
            printGlobalConfig(c);
            printTaskCopies(c, companyId);
            printTagBindings(c, companyId);
            printEffectivePolicies(c, companyId);
            printSupplementary(c, companyId);
        }
    }

    private static void printSupplementary(Connection c, long companyId) throws SQLException {
        System.out.println("--- Supplementary stats ---");
        runQuery(c, "tag rel by template_id",
                "SELECT template_id, COUNT(*) cnt FROM company_lobster_tag_user_rel "
                        + "WHERE company_id=? AND del_flag=0 GROUP BY template_id ORDER BY cnt DESC LIMIT 10", companyId);
        runQuery(c, "instance by status",
                "SELECT status, COUNT(*) cnt FROM lobster_workflow_instance "
                        + "WHERE company_id=? GROUP BY status", companyId);
        runQuery(c, "tag-template bindings",
                "SELECT id, tag_code, task_template_id, status FROM company_tag_template_binding "
                        + "WHERE company_id=? AND del_flag=0 ORDER BY id DESC LIMIT 8", companyId);
    }

    private static void runQuery(Connection c, String label, String sql, long companyId) throws SQLException {
        System.out.println("[" + label + "]");
        if (!tableExists(c, sql.contains("company_lobster_tag_user_rel") ? "company_lobster_tag_user_rel"
                : sql.contains("lobster_workflow_instance") ? "lobster_workflow_instance"
                : "company_tag_template_binding")) {
            System.out.println("  (table missing)");
            return;
        }
        try (PreparedStatement ps = c.prepareStatement(sql)) {
            ps.setLong(1, companyId);
            try (ResultSet rs = ps.executeQuery()) {
                int n = 0;
                while (rs.next()) {
                    n++;
                    printRow(rs);
                }
                if (n == 0) System.out.println("  (empty)");
            }
        }
        System.out.println();
    }

    private static void printGlobalConfig(Connection c) throws SQLException {
        System.out.println("--- lobster_execution_config (tenant-global, company_id=0) ---");
        if (!tableExists(c, "lobster_execution_config")) {
            System.out.println("(table missing -> code defaults: HYBRID, autoStart=true)\n");
            return;
        }
        String sql = "SELECT id, company_id, default_execution_mode, auto_start_instance_on_tag, "
                + "quality_gate_enabled, quality_pass_score_percent, message_dedup_enabled, update_time "
                + "FROM lobster_execution_config WHERE company_id=0 LIMIT 1";
        try (Statement st = c.createStatement(); ResultSet rs = st.executeQuery(sql)) {
            if (!rs.next()) {
                System.out.println("(no row -> code defaults: HYBRID, autoStart=true)\n");
                return;
            }
            printRow(rs);
        }
        System.out.println();
    }

    private static void printTaskCopies(Connection c, long companyId) throws SQLException {
        System.out.println("--- company_workflow_lobster task_copy (company_id=" + companyId + ") ---");
        if (!tableExists(c, "company_workflow_lobster")) {
            System.out.println("(table missing)\n");
            return;
        }
        String sql = "SELECT id, template_name, template_kind, status, execution_mode, "
                + "enable_content_personalization, enable_flow_personalization, "
                + "strict_fixed_workflow, auto_start_instance_on_tag, source_template_id, update_time "
                + "FROM company_workflow_lobster "
                + "WHERE company_id=? AND del_flag=0 AND template_kind='task_copy' "
                + "ORDER BY update_time DESC LIMIT 20";
        try (PreparedStatement ps = c.prepareStatement(sql)) {
            ps.setLong(1, companyId);
            try (ResultSet rs = ps.executeQuery()) {
                int n = 0;
                while (rs.next()) {
                    n++;
                    System.out.println("#" + n + " id=" + rs.getLong("id")
                            + " name=" + rs.getString("template_name")
                            + " status=" + rs.getInt("status"));
                    System.out.println("    execution_mode=" + nv(rs.getString("execution_mode"))
                            + " contentPers=" + nv(rs.getObject("enable_content_personalization"))
                            + " flowPers=" + nv(rs.getObject("enable_flow_personalization"))
                            + " strict=" + nv(rs.getObject("strict_fixed_workflow"))
                            + " autoStart=" + nv(rs.getObject("auto_start_instance_on_tag")));
                    System.out.println("    source_template_id=" + nv(rs.getObject("source_template_id"))
                            + " updated=" + rs.getTimestamp("update_time"));
                }
                if (n == 0) System.out.println("(no task_copy rows)");
            }
        }
        System.out.println();
    }

    private static void printTagBindings(Connection c, long companyId) throws SQLException {
        System.out.println("--- company_lobster_tag_user_rel (sample bindings, company_id=" + companyId + ") ---");
        if (!tableExists(c, "company_lobster_tag_user_rel")) {
            System.out.println("(table missing)\n");
            return;
        }
        String sql = "SELECT rel.id, rel.template_id, rel.binding_id, rel.tag_code, rel.channel_type, "
                + "rel.channel_contact_id, wf.template_name, wf.execution_mode, wf.strict_fixed_workflow "
                + "FROM company_lobster_tag_user_rel rel "
                + "LEFT JOIN company_workflow_lobster wf ON wf.id=rel.template_id AND wf.company_id=rel.company_id "
                + "WHERE rel.company_id=? AND rel.del_flag=0 "
                + "ORDER BY rel.id DESC LIMIT 10";
        try (PreparedStatement ps = c.prepareStatement(sql)) {
            ps.setLong(1, companyId);
            try (ResultSet rs = ps.executeQuery()) {
                int n = 0;
                while (rs.next()) {
                    n++;
                    System.out.println("rel#" + rs.getLong("id")
                            + " template_id=" + rs.getLong("template_id")
                            + " binding_id=" + nv(rs.getObject("binding_id"))
                            + " tag=" + rs.getString("tag_code")
                            + " contact=" + rs.getLong("channel_contact_id"));
                    System.out.println("    wf=" + nv(rs.getString("template_name"))
                            + " wf.execution_mode=" + nv(rs.getString("execution_mode"))
                            + " strict=" + nv(rs.getObject("strict_fixed_workflow")));
                }
                if (n == 0) System.out.println("(no tag-user rel rows)");
            }
        }
        System.out.println();
    }

    private static void printEffectivePolicies(Connection c, long companyId) throws SQLException {
        System.out.println("--- Effective policy (LobsterExecutionPolicyService.resolve logic) ---");

        Global g = loadGlobal(c);
        List<TaskCopy> copies = loadTaskCopies(c, companyId);

        System.out.println("Global baseline:");
        System.out.println("  executionMode=" + g.mode + " autoStartInstanceOnTag=" + g.autoStart
                + " qualityGate=" + g.qualityGate);

        System.out.println("\nPer task_copy (templateId -> merged effective):");
        if (copies.isEmpty()) {
            Policy p = merge(g, null);
            printPolicy("(no template / prompt-only contacts)", p);
        } else {
            for (TaskCopy tc : copies) {
                Policy p = merge(g, tc);
                printPolicy("templateId=" + tc.id + " \"" + tc.name + "\"", p);
            }
        }

        System.out.println("\nOrchestrator routing summary:");
        if (copies.isEmpty()) {
            Policy p = merge(g, null);
            System.out.println("  [unbound] preferInstance=" + p.preferInstance()
                    + " allowPromptFallback=" + p.allowPromptFallback()
                    + " allowFlow=" + p.allowFlow
                    + " allowContent=" + p.allowContent
                    + " -> " + describeRoute(p));
        } else {
            for (TaskCopy tc : copies) {
                Policy p = merge(g, tc);
                System.out.println("  [templateId=" + tc.id + "] preferInstance=" + p.preferInstance()
                        + " allowPromptFallback=" + p.allowPromptFallback()
                        + " allowFlow=" + p.allowFlow
                        + " allowContent=" + p.allowContent
                        + " -> " + describeRoute(p));
            }
            Policy unbound = merge(g, null);
            System.out.println("  [unbound/no tag rel] preferInstance=" + unbound.preferInstance()
                    + " allowPromptFallback=" + unbound.allowPromptFallback()
                    + " -> " + describeRoute(unbound));
        }
    }

    private static String describeRoute(Policy p) {
        if (p.strict) {
            return "INSTANCE-only (strict fixed, no prompt fallback, no flow personalization)";
        }
        switch (p.mode) {
            case "HYBRID":
                return "try instance first (autoStart=" + p.autoStart + "), else PROMPT chat fallback";
            case "INSTANCE":
                return "INSTANCE only; prompt fallback only if instance path fails";
            case "PROMPT":
                return "PROMPT chat only, no instance path";
            default:
                return p.mode;
        }
    }

    private static void printPolicy(String label, Policy p) {
        System.out.println("  " + label);
        System.out.println("    mode=" + p.mode + " strict=" + p.strict
                + " autoStart=" + p.autoStart
                + " contentPers=" + p.allowContent + " flowPers=" + p.allowFlow);
    }

    private static Global loadGlobal(Connection c) throws SQLException {
        Global g = new Global();
        g.mode = "HYBRID";
        g.autoStart = true;
        g.qualityGate = true;
        if (!tableExists(c, "lobster_execution_config")) return g;
        String sql = "SELECT default_execution_mode, auto_start_instance_on_tag, quality_gate_enabled "
                + "FROM lobster_execution_config WHERE company_id=0 LIMIT 1";
        try (Statement st = c.createStatement(); ResultSet rs = st.executeQuery(sql)) {
            if (rs.next()) {
                if (rs.getString("default_execution_mode") != null && !rs.getString("default_execution_mode").isBlank()) {
                    g.mode = rs.getString("default_execution_mode").trim().toUpperCase();
                }
                g.autoStart = intToBool(rs.getObject("auto_start_instance_on_tag"), true);
                g.qualityGate = intToBool(rs.getObject("quality_gate_enabled"), true);
            }
        }
        return g;
    }

    private static List<TaskCopy> loadTaskCopies(Connection c, long companyId) throws SQLException {
        List<TaskCopy> list = new ArrayList<>();
        if (!tableExists(c, "company_workflow_lobster")) return list;
        String sql = "SELECT id, template_name, execution_mode, enable_content_personalization, "
                + "enable_flow_personalization, strict_fixed_workflow, auto_start_instance_on_tag "
                + "FROM company_workflow_lobster "
                + "WHERE company_id=? AND del_flag=0 AND template_kind='task_copy' AND status=1 "
                + "ORDER BY update_time DESC";
        try (PreparedStatement ps = c.prepareStatement(sql)) {
            ps.setLong(1, companyId);
            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    TaskCopy t = new TaskCopy();
                    t.id = rs.getLong("id");
                    t.name = rs.getString("template_name");
                    t.executionMode = rs.getString("execution_mode");
                    t.contentPers = (Integer) rs.getObject("enable_content_personalization");
                    t.flowPers = (Integer) rs.getObject("enable_flow_personalization");
                    t.strict = (Integer) rs.getObject("strict_fixed_workflow");
                    t.autoStart = (Integer) rs.getObject("auto_start_instance_on_tag");
                    list.add(t);
                }
            }
        }
        return list;
    }

    private static Policy merge(Global g, TaskCopy tc) {
        Policy p = new Policy();
        p.mode = g.mode;
        p.autoStart = g.autoStart;
        p.allowContent = true;
        p.allowFlow = true;
        p.strict = false;

        if (tc != null) {
            if (tc.executionMode != null && !tc.executionMode.isBlank()) {
                p.mode = tc.executionMode.trim().toUpperCase();
            }
            if (tc.contentPers != null) p.allowContent = intToBool(tc.contentPers, true);
            if (tc.flowPers != null) p.allowFlow = intToBool(tc.flowPers, true);
            if (tc.strict != null) p.strict = intToBool(tc.strict, false);
            if (tc.autoStart != null) p.autoStart = intToBool(tc.autoStart, false);
        }

        if (p.strict) {
            p.allowFlow = false;
            if ("PROMPT".equals(p.mode)) p.mode = "INSTANCE";
        }
        return p;
    }

    private static boolean intToBool(Object v, boolean def) {
        if (v == null) return def;
        if (v instanceof Number) return ((Number) v).intValue() != 0;
        return Boolean.parseBoolean(v.toString());
    }

    private static String nv(Object o) {
        return o == null ? "null(inherit)" : String.valueOf(o);
    }

    private static void printRow(ResultSet rs) throws SQLException {
        ResultSetMetaData md = rs.getMetaData();
        for (int i = 1; i <= md.getColumnCount(); i++) {
            if (i > 1) System.out.print(" | ");
            System.out.print(md.getColumnLabel(i) + "=" + rs.getObject(i));
        }
        System.out.println();
    }

    private static boolean tableExists(Connection c, String table) throws SQLException {
        try (PreparedStatement ps = c.prepareStatement(
                "SELECT COUNT(*) FROM information_schema.TABLES WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME=?")) {
            ps.setString(1, table);
            try (ResultSet rs = ps.executeQuery()) {
                return rs.next() && rs.getInt(1) > 0;
            }
        }
    }

    private static TenantDb resolveTenantDb(long tenantId) throws SQLException {
        try (Connection master = connect(MASTER, MASTER_USER, MASTER_PWD);
             PreparedStatement ps = master.prepareStatement(
                     "SELECT db_url, db_account, db_pwd FROM tenant_info WHERE id=?")) {
            ps.setLong(1, tenantId);
            try (ResultSet rs = ps.executeQuery()) {
                if (!rs.next()) throw new IllegalStateException("tenant_info id=" + tenantId + " not found");
                return parseJdbcUrl(rs.getString("db_url"), rs.getString("db_account"), rs.getString("db_pwd"));
            }
        }
    }

    private static Connection connect(String db, String user, String pwd) throws SQLException {
        return DriverManager.getConnection(
                "jdbc:mysql://" + HOST + ":" + PORT + "/" + db
                        + "?useUnicode=true&characterEncoding=utf8&serverTimezone=GMT%2B8&allowMultiQueries=true",
                user, pwd);
    }

    private static TenantDb parseJdbcUrl(String jdbcUrl, String user, String password) {
        String body = jdbcUrl.substring("jdbc:mysql://".length());
        int slash = body.indexOf('/');
        int q = body.indexOf('?');
        String hostPort = body.substring(0, slash);
        String database = body.substring(slash + 1, q > 0 ? q : body.length());
        TenantDb t = new TenantDb();
        t.database = database;
        t.user = user;
        t.password = password;
        return t;
    }

    static class TenantDb {
        String database, user, password;
    }

    static class Global {
        String mode = "HYBRID";
        boolean autoStart = true;
        boolean qualityGate = true;
    }

    static class TaskCopy {
        long id;
        String name;
        String executionMode;
        Integer contentPers, flowPers, strict, autoStart;
    }

    static class Policy {
        String mode;
        boolean strict, autoStart, allowContent, allowFlow;

        boolean preferInstance() {
            if (strict) return true;
            return "INSTANCE".equals(mode) || "HYBRID".equals(mode);
        }

        boolean allowPromptFallback() {
            if (strict) return false;
            return "PROMPT".equals(mode) || "HYBRID".equals(mode);
        }
    }
}