import csv
import json
from typing import Iterable
from itertools import islice
from textwrap import dedent

service_tiers = [
    {
        "id": 1,
        "name": "diy-tier-1",
        "uuid": "5f2bd4fc-df94-4f35-97d3-ef23f8573279",
        "display_name": "Basic",
        "date_updated": "2024-08-21 11:41:45",
    },
    {
        "id": 2,
        "name": "managed-basic",
        "uuid": "2304f272-7a12-40fc-ba48-959fff435223",
        "display_name": "Managed Basic",
        "date_updated": "2024-08-21 11:41:45",
    },
    {
        "id": 3,
        "name": "tier-1",
        "uuid": "1ed7aac0-ceb6-4c09-9166-afda8f349316",
        "display_name": "Tier 1",
        "date_updated": "2024-08-21 11:41:45",
    },
    {
        "id": 4,
        "name": "tier-2",
        "uuid": "7410e51c-90da-4092-99da-b5489f364fa8",
        "display_name": "Tier 2",
        "date_updated": "2024-08-21 11:41:45",
    },
    {
        "id": 5,
        "name": "tier-3",
        "uuid": "9363881e-1120-4495-ae09-657e13609e11",
        "display_name": "Tier 3",
        "date_updated": "2024-08-21 11:41:45",
    },
    {
        "id": 6,
        "name": "premium-services",
        "uuid": "1dd92c83-25a3-4034-9a3d-f7c3f434f4ce",
        "display_name": "Premium Services",
        "date_updated": "2024-08-21 11:41:45",
    },
    {
        "id": 7,
        "name": "untiered",
        "uuid": "c069ab15-9370-4bd6-8ad1-895a64ae2ad8",
        "display_name": "Untiered",
        "date_updated": "2024-08-21 11:41:45",
    },
]

UPDATE_VENDOR = """
UPDATE vendor
SET
    assigned_to               = {assigned_to},
    assigned_reviewer         = {assigned_reviewer},
    support_contact_email     = '{support_contact_email}',
    quarterback_label_manager = {quarterback_label_manager},
    last_modified_by          = 1788
WHERE
    vendor_id = {vendor_id};
"""

UPDATE_VENDOR_ROLLBACK = """
UPDATE vendor v
INNER JOIN ROLLBACK_PLATFORM_4475_backfill_vendor rb USING(vendor_id)
SET
    v.assigned_to                = rb.assigned_to,
    v.assigned_reviewer          = rb.assigned_reviewer,
    v.support_contact_email      = rb.support_contact_email,
    v.quarterback_label_manager  = rb.quarterback_label_manager,
    v.last_modified_by           = rb.last_modified_by
WHERE
    v.vendor_id = {vendor_id}; 
"""

UPDATE_VENDOR_PRODUCT_MANAGER = """
INSERT INTO product_manager_mapping_vendor 
    (vendor_id, product_manager_id, updated_at)
VALUES ({vendor_id}, {product_manager}, NOW())
ON DUPLICATE KEY UPDATE
    product_manager_id = VALUES(product_manager_id),
    updated_at = VALUES(updated_at);
"""

UPDATE_VENDOR_PRODUCT_MANAGER_ROLLABACK = """
UPDATE product_manager_mapping_vendor AS p
INNER JOIN ROLLBACK_PLATFORM_4475_backfill_pm_vendor AS rb USING(vendor_id)
SET
    p.product_manager_id = rb.product_manager_id,
    p.updated_at = rb.updated_at
WHERE
    p.vendor_id = {vendor_id};
"""

UPDATE_VENDOR_ROLLBACK_TABLE = """
DROP TABLE IF EXISTS ROLLBACK_PLATFORM_4475_backfill_vendor;
CREATE TABLE ROLLBACK_PLATFORM_4475_backfill_vendor
    SELECT vendor_id, assigned_to, assigned_reviewer,
           support_contact_email, quarterback_label_manager, last_modified_by
    FROM vendor WHERE vendor_id in (
{vendor_ids}
    );
"""

UPDATE_VENDOR_PM_ROLLBACK_TABLE = """
DROP TABLE IF EXISTS ROLLBACK_PLATFORM_4475_backfill_pm_vendor;   
CREATE TABLE ROLLBACK_PLATFORM_4475_backfill_pm_vendor
    SELECT vendor_id, product_manager_id, updated_at
    FROM product_manager_mapping_vendor WHERE vendor_id in (
{vendor_ids}
    );
"""

CYPHER_TEMPLATE = """
MATCH (v:Vendor {{ vendorId: {vendor_id} }})
MATCH (newTier:ServiceTier {{ uuid: '{service_tier_uuid}' }})
OPTIONAL MATCH (v)-[oldRel:IN_SERVICE_TIER]->(oldTier:ServiceTier)
WITH v, newTier, oldRel, oldTier
CALL apoc.do.when(
  oldRel IS NULL OR oldTier.uuid <> newTier.uuid,
  '
    CALL apoc.refactor.setType(oldRel, "DELETED_IN_SERVICE_TIER")
    YIELD input, output AS deletedRel
    SET deletedRel.updatedAt = datetime(),
        deletedRel.updatedBy = "database/PLATFORM-4475"
    MERGE (v)-[newRel:IN_SERVICE_TIER]->(newTier)
    ON CREATE
    SET newRel.createdAt = datetime(),
        newRel.createdBy = "database/PLATFORM-4475"
  ',
  '',
  {{oldRel:oldRel, v:v, newTier:newTier}}
) YIELD value
RETURN v.vendorId;
"""
def read_csv(csv_file_path: str) -> list[dict] | None:
    try:
        with open(csv_file_path, mode="r", encoding="utf-8-sig") as csv_file:
            csv_reader = csv.DictReader(csv_file)
            return [row for row in csv_reader]
    except FileNotFoundError:
        print(f"Error: Input CSV file '{csv_file_path}' not found")
        return None


def find_user_id_by_name(name: str, users_data: list[dict]) -> dict | None:
    clean_name = name.strip()
    if not clean_name:
        return None

    try:
        f_name, l_name = clean_name.split(" ")
    except ValueError:
        print(
            f"Error: Name '{clean_name}' is not in the expected format 'First Last'"
        )
        raise

    for user in users_data:
        if user.get("f_name") == f_name and user.get("l_name") == l_name:
            return user

    return None


def find_service_tier_id_by_name(name: str) -> str | None:
    tier_name = name.strip()
    for tier in service_tiers:
        if tier["display_name"] == tier_name:
            return tier["uuid"]
    return None


def format_chunked_sql(
    template: str,
    ids: Iterable[int],
    chunk_size: int = 5,
    indent: int = 12
) -> str:
    # Dedent the template once
    tpl = dedent(template)

    # Helper to split IDs into chunks
    def chunked(it: Iterable[int], size: int) -> list[list[int]]:
        iterator = iter(it)
        return list(iter(lambda: list(islice(iterator, size)), []))

    # Build the indented, comma-separated lines
    chunks = chunked(ids, chunk_size)
    lines = [
        " " * indent + ", ".join(str(i) for i in chunk)
        for chunk in chunks
    ]
    vendor_ids_formatted = ",\n".join(lines)

    # Substitute into template
    return tpl.format(vendor_ids=vendor_ids_formatted)


def main():
    vendor_input_file = 'USCA_RM_CHANGEFILE.csv'
    orchardadmin_users_file = 'orchardadmin_users.csv'
    json_output_file = 'mapped_vendors.json'
    sql_output_file = 'PLATFORM-4475_update_vendors.sql'
    cypher_output_file = 'PLATFORM-4475_update_vendor_service_tiers.cypher'

    # reads sreadsheet data
    vendor_data = read_csv(vendor_input_file) or (None, None)
    if not vendor_data:
        return
    # read OrchardAdmin users data
    orchardadmin_data = read_csv(orchardadmin_users_file) or (None, None)
    if not orchardadmin_data:
        return

    mapped = []
    ar_sql = []
    for row in vendor_data:
        # mandatory field: assigned_to
        assigned_to = find_user_id_by_name(
            row["RELATIONSHIP MANAGER"], orchardadmin_data
        )
        if not assigned_to:
            print(
                f"Error: Could not find user ID for assigned_to: "
                f"{row['RELATIONSHIP MANAGER']}"
            )
            return
        support_contact_email = assigned_to["email"]

        # mandatory field: assigned_reviewer
        assigned_reviewer = find_user_id_by_name(
            row["ASSIGNED REVIEWER"], orchardadmin_data
        )
        if not assigned_reviewer:
            print(
                f"Error: Could not find user ID for assigned_reviewer: "
                f"{row['ASSIGNED REVIEWER']}"
            )
            return

        # mandatory field: service_tier
        service_tier_uuid = find_service_tier_id_by_name(
            row["ACCOUNT SERVICE TIER"]
        )
        if not service_tier_uuid:
            print(
                f"Error: Could not find service tier ID "
                f"{row['ACCOUNT SERVICE TIER']}"
            )
            return

        # optional field: quarterback_label_manager
        qlm = find_user_id_by_name(
            row['SECONDARY RELATIONSHIP MANAGER'], orchardadmin_data
        )
        quarterback_label_manager = qlm['id'] if qlm else None

        # optional field: product_manager
        pm = find_user_id_by_name(row['PRODUCT MANAGER'], orchardadmin_data)
        product_manager = pm['id'] if pm else None

        # vendor object
        mapped_vendor = {
            "vendor_id": row["Label ID"],
            "assigned_to": assigned_to["id"],
            "assigned_reviewer": assigned_reviewer["id"],
            "support_contact_email": support_contact_email,
            "quarterback_label_manager": quarterback_label_manager,
            "service_tier": service_tier_uuid,
            "product_manager": product_manager,
        }
        mapped.append(mapped_vendor)

        # update vendor table and rollback
        update_sql = UPDATE_VENDOR.format(
            assigned_to=assigned_to['id'],
            assigned_reviewer=assigned_reviewer['id'],
            support_contact_email=support_contact_email,
            quarterback_label_manager=quarterback_label_manager if quarterback_label_manager else 'NULL',
            vendor_id=mapped_vendor['vendor_id'],
        )

        rollback_template = UPDATE_VENDOR_ROLLBACK.format(
            vendor_id=mapped_vendor['vendor_id']
        )
        # prefix each line with "--rollback "
        rollback_sql = "\n".join(
            f"--rollback {line}" if line.strip() else "--rollback"
            for line in rollback_template.strip().splitlines()
        )

        ar_sql.append(update_sql + '\n' + rollback_sql)

        if product_manager:
            # update product_manager_mapping_vendor table and rollback
            update_sql = UPDATE_VENDOR_PRODUCT_MANAGER.format(
                vendor_id=mapped_vendor['vendor_id'],
                product_manager=product_manager,
            )

            rollback_template = UPDATE_VENDOR_PRODUCT_MANAGER_ROLLABACK.format(
                vendor_id=mapped_vendor['vendor_id'],
            )
            # prefix each line with "--rollback "
            rollback_sql = "\n".join(
                f"--rollback {line}" if line.strip() else "--rollback"
                for line in rollback_template.strip().splitlines()
            )
            ar_sql.append(update_sql + '\n' + rollback_sql)

    # Write the mapped data to a JSON file
    with open(json_output_file, 'w', encoding='utf-8') as json_file:
        json.dump(mapped, json_file, indent=4)

    # Write the SQL statements to a text file
    vendor_ids = [v['vendor_id'] for v in mapped]
    with open(sql_output_file, 'w', encoding='utf-8') as sql_file:
        # Write the rollback tables creation SQL
        sql_file.write('--liquibase formatted sql\n\n')
        sql_file.write('--changeset ratoui:1 runAlways:true')
        sql_file.write(
            format_chunked_sql(UPDATE_VENDOR_ROLLBACK_TABLE, vendor_ids, chunk_size=12, indent=8)
        )
        sql_file.write('\n\n')

        sql_file.write('--changeset ratoui:2 runAlways:true')
        sql_file.write(
            format_chunked_sql(UPDATE_VENDOR_PM_ROLLBACK_TABLE, vendor_ids, chunk_size=12, indent=8)
        )
        sql_file.write('\n\n')

        # Write the update and rollback SQL for each vendor
        for i, sql in enumerate(ar_sql, start=3):
            sql_file.write(f"--changeset ratoui:{i} runAlways:true runOnChange:true")
            sql_file.write(sql + '\n\n')

    with open(cypher_output_file, 'w') as cypher_file:
        cypher_file.write('--liquibase formatted cypher\n\n')
        for i, v in enumerate(mapped, start=1):
            cypher_file.write(f"--changeset ratoui:{i} runAlways:true runOnChange:true")
            cypher_file.write(
                CYPHER_TEMPLATE.format(
                    vendor_id=v['vendor_id'],
                    service_tier_uuid=v['service_tier']
                )
            )
            cypher_file.write("\n")

if __name__ == '__main__':
    main()