import re
from pathlib import Path

import openpyxl


WORKBOOK = "/Users/ravindersingh/Downloads/List of Complaints 07.06.26 2.xlsx"
OUTPUT = "/private/tmp/complaint_hierarchy_migration.sql"


def clean(value):
    return re.sub(r"\s+", " ", str(value).strip())


def q(value):
    return "'" + str(value).replace("\\", "\\\\").replace("'", "''") + "'"


def add_path(parts, node_paths):
    path = tuple(clean(part) for part in parts if part is not None and clean(part))
    for index in range(1, len(path) + 1):
        if path[:index] not in node_paths:
            node_paths.append(path[:index])
    return path


def node_subquery(path):
    if len(path) == 1:
        return "SELECT id FROM complaint_nodes WHERE parent_id IS NULL AND name=%s LIMIT 1" % q(path[0])

    node_name = path[-1]
    parent_name = path[-2]
    return (
        "SELECT n.id FROM complaint_nodes n "
        "JOIN complaint_nodes p ON p.id=n.parent_id "
        "WHERE n.name=%s AND p.name=%s ORDER BY n.id DESC LIMIT 1"
    ) % (q(node_name), q(parent_name))


def main():
    workbook = openpyxl.load_workbook(WORKBOOK, data_only=True)
    node_paths = []
    complaints = []

    civil = workbook["Civil Works"]
    br_root = clean(civil.cell(1, 1).value).replace("COMPLAINTS OF INTERNAL B/R", "B/R")
    add_path([br_root, "Misc"], node_paths)
    section = None
    location = None
    item = None

    for row in civil.iter_rows(min_row=3, values_only=True):
        first, second, code, complaint = row[:4]

        if first and not second and not code and not complaint:
            section = clean(first)
            location = None
            item = None
            add_path([br_root, section], node_paths)
            continue

        if complaint and code:
            if first:
                location = clean(first)
            if second:
                item = clean(second)

            path = add_path([br_root, section, location, item], node_paths)
            complaints.append((path, clean(complaint), int(code), 3, 2, "BR"))

    em = workbook["Int E-M"]
    em_root = "E/M"
    add_path([em_root], node_paths)

    for row in em.iter_rows(min_row=3, values_only=True):
        code, complaint, priority, _remarks = row[:4]

        if code and isinstance(code, str) and "PRIORITY" in code.upper():
            match = re.search(r"0?([123])", code)
            priority_number = int(match.group(1)) if match else 3
            add_path([em_root, "Priority %d" % priority_number], node_paths)
            continue

        if complaint and priority:
            priority_number = int(priority)
            path = add_path([em_root, "Priority %d" % priority_number], node_paths)
            complaints.append((path, clean(complaint), int(code), priority_number, 1, "EM"))

    sql = [
        "SET FOREIGN_KEY_CHECKS=0;",
        (
            "CREATE TABLE IF NOT EXISTS complaint_nodes ("
            "id INT NOT NULL AUTO_INCREMENT,"
            "parent_id INT NULL,"
            "name VARCHAR(255) NOT NULL,"
            "sort_order INT NOT NULL DEFAULT 0,"
            "created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,"
            "PRIMARY KEY (id),"
            "KEY parent_id (parent_id),"
            "UNIQUE KEY uniq_parent_name (parent_id, name),"
            "CONSTRAINT complaint_nodes_parent_fk FOREIGN KEY (parent_id) "
            "REFERENCES complaint_nodes(id) ON DELETE CASCADE"
            ") ENGINE=InnoDB DEFAULT CHARSET=latin1;"
        ),
        "ALTER TABLE complaint_category MODIFY name VARCHAR(255) NOT NULL;",
        "ALTER TABLE complaint_subcategory MODIFY name VARCHAR(255) NOT NULL;",
        "ALTER TABLE complaint_list MODIFY code BIGINT DEFAULT NULL;",
        "ALTER TABLE complaint_list ADD COLUMN IF NOT EXISTS complaint_type VARCHAR(10) DEFAULT NULL AFTER cid;",
        "ALTER TABLE complaint_list ADD COLUMN IF NOT EXISTS node_id INT DEFAULT NULL AFTER complaint_type;",
        "ALTER TABLE complaint_list ADD INDEX IF NOT EXISTS idx_complaint_list_node_id (node_id);",
        "SET FOREIGN_KEY_CHECKS=1;",
        (
            "DELETE FROM complaint_list "
            "WHERE node_id IS NOT NULL "
            "AND cid NOT IN (SELECT c_list FROM users_complaint WHERE c_list IS NOT NULL);"
        ),
        "SET FOREIGN_KEY_CHECKS=0;",
        "DELETE FROM complaint_subcategory;",
        "DELETE FROM complaint_category;",
        "ALTER TABLE complaint_category AUTO_INCREMENT=1;",
        "ALTER TABLE complaint_subcategory AUTO_INCREMENT=1;",
        "SET FOREIGN_KEY_CHECKS=1;",
        (
            "INSERT INTO complaint_category (name,mcode,user_id,session_id,nwdate) "
            "VALUES ('B/R',1,0,0,NOW()),('E/M',2,0,0,NOW());"
        ),
    ]

    subcategories = []
    for path in node_paths:
        if len(path) == 2:
            subcategories.append((path[0], path[1]))

    for index, (parent, name) in enumerate(subcategories, start=1):
        sql.append(
            "INSERT INTO complaint_subcategory (cc_id,name,scode,user_id,session_id,nwdate) "
            "SELECT cc_id,%s,%d,0,0,NOW() FROM complaint_category WHERE name=%s;"
            % (q(name), index, q(parent))
        )

    for index, path in enumerate(node_paths, start=1):
        name = path[-1]
        if len(path) == 1:
            sql.append(
                "INSERT INTO complaint_nodes (parent_id,name,sort_order) "
                "SELECT NULL,%s,%d WHERE NOT EXISTS "
                "(SELECT 1 FROM complaint_nodes WHERE parent_id IS NULL AND name=%s);"
                % (q(name), index, q(name))
            )
            continue

        parent_path = path[:-1]
        parent_sql = node_subquery(parent_path)
        sql.append(
            "INSERT INTO complaint_nodes (parent_id,name,sort_order) "
            "SELECT (%s),%s,%d WHERE NOT EXISTS "
            "(SELECT 1 FROM complaint_nodes WHERE parent_id=(%s) AND name=%s);"
            % (parent_sql, q(name), index, parent_sql, q(name))
        )

    for path, details, code, priority, subdivision, complaint_type in complaints:
        category = path[0]
        subcategory = path[1] if len(path) > 1 else ""
        node_sql = node_subquery(path)
        sql.append(
            "INSERT INTO complaint_list "
            "(complaint_type,node_id,cc_id,cs_id,details,priority,subdivision,code) "
            "SELECT %s,(%s),cc.cc_id,cs.cs_id,%s,%d,%d,%d "
            "FROM complaint_category cc "
            "LEFT JOIN complaint_subcategory cs ON cs.cc_id=cc.cc_id AND cs.name=%s "
            "WHERE cc.name=%s "
            "AND NOT EXISTS ("
            "SELECT 1 FROM complaint_list cl "
            "WHERE cl.node_id=(%s) AND cl.code=%d AND cl.details=%s"
            ");"
            % (
                q(complaint_type),
                node_sql,
                q(details),
                priority,
                subdivision,
                code,
                q(subcategory),
                q(category),
                node_sql,
                code,
                q(details),
            )
        )

    Path(OUTPUT).write_text("\n".join(sql), encoding="utf-8")
    print("%d nodes, %d complaints -> %s" % (len(node_paths), len(complaints), OUTPUT))


if __name__ == "__main__":
    main()
