#!/usr/bin/env python3
"""
Generate SQL script to bulk add UPC lists to product contract terms.
Supports multiple contract terms with deduplication.

Usage:
    Note: You should use the shell script wrapper `bulk-add-upc-lists-to-product-terms.sh` instead
    ./bulk-add-upc-lists-to-product-terms.py -i contracts.json --dev-username abcd --dev-identity-id uuid
    ./bulk-add-upc-lists-to-product-terms.py --help
"""

import argparse
import json
import re
import sys
from pathlib import Path
from typing import List, Dict


def load_data_from_json(json_path: str) -> tuple[str, List[Dict]]:
    with open(json_path, 'r') as f:
        obj = json.load(f)
    ticket_id = obj.get('ticket_id')
    contracts = obj.get('data', [])
    assert isinstance(ticket_id, str) and re.match(r'^[A-Z]+-\d+$', ticket_id), f"ticket_id must be a string matching format (e.g., ACC-9468), got: {ticket_id}"
    for i, contract in enumerate(contracts, 1):
        for field in ['account_id', 'contract_id', 'contract_term_id']:
            v = contract.get(field)
            assert v and str(v).isdigit(), f"Contract term {i} has invalid {field}: {v}"
        upcs = contract.get('upcs')
        assert isinstance(upcs, list) and upcs, f"Contract term {i} must have a non-empty 'upcs' array"
        for j, upc in enumerate(upcs):
            assert isinstance(upc, str) and upc.strip(), f"Contract term {i} UPC at index {j} must be a non-empty string"
            assert 11 <= len(upc) <= 16 and upc.isdigit(), f"Contract term {i} UPC at index {j} must be between 11 and 16 digits, got '{upc}'"
    return ticket_id, contracts


def generate_upc_union_statements(upcs: List[str]) -> str:
    return '\n'.join([f"            SELECT '{upc}' AS upc" if i == 0 else f"            UNION SELECT '{upc}'" for i, upc in enumerate(upcs)])


def substitute_template(template: str, context: dict) -> str:
    for k, v in context.items():
        template = template.replace(k, v)
    return template


def generate_sql(contracts: List[Dict], ticket_id: str, dev_user: str, dev_id: str, template_path: Path) -> str:
    with open(template_path, 'r') as f:
        template = f.read()

    # Split template into rollback table section and update section
    rollback_table_template = template.split('-- CHANGESET %changeset_index_update:')[0]
    update_template = '-- CHANGESET %changeset_index_update:' + template.split('-- CHANGESET %changeset_index_update:')[1]

    header = f"--liquibase formatted sql\n\n-- This script bulk adds UPC lists to product contract terms with deduplication.\n-- Generated for ticket: {ticket_id}\n-- Total contract terms to update: {len(contracts)}\n\n"

    # Generate contract_term_id list for rollback table
    contract_term_ids = [str(c['contract_term_id']) for c in contracts]
    contract_term_id_list = ', '.join(contract_term_ids)

    # Generate changeset 0: rollback table
    rollback_ctx = {
        '%dev_username': dev_user,
        '%ticket_id': ticket_id,
        '%contract_term_id_list': contract_term_id_list,
        '%dev_identity_id': dev_id
    }
    rollback_table_section = substitute_template(rollback_table_template, rollback_ctx)

    # Generate update changesets for each contract term
    changesets = []
    for i, contract in enumerate(contracts, 1):
        upc_sql = generate_upc_union_statements(contract['upcs'])
        ctx = {
            '%changeset_index_update': str(i),
            '%contract_index': str(i),
            '%dev_username': dev_user,
            '%ticket_id': ticket_id,
            '%account_id': str(contract['account_id']),
            '%contract_id': str(contract['contract_id']),
            '%contract_term_id': str(contract['contract_term_id']),
            '%dev_identity_id': dev_id,
            '%upc_statements': upc_sql
        }
        changeset = substitute_template(update_template, ctx)
        changesets.append(changeset)
        if i < len(contracts):
            changesets.append("\n-- " + "="*76 + "\n\n")

    sql = header + rollback_table_section + ''.join(changesets)
    return sql


def generate_output_filepath(ticket_id: str, repo_root: Path) -> Path:
    return repo_root / 'royalty_accounting' / 'build' / 'changelog' / 'dml' / f"{ticket_id}-bulk-add-upcs.sql"


def main():
    parser = argparse.ArgumentParser(description='Generate SQL script to bulk add UPC lists to product contract terms with deduplication.', formatter_class=argparse.RawDescriptionHelpFormatter)
    parser.add_argument('-i', '--input', dest='input_file', required=True)
    parser.add_argument('--dev-username', dest='dev_username', required=True)
    parser.add_argument('--dev-identity-id', dest='dev_identity_id', required=True)
    args = parser.parse_args()

    input_file, dev_user, dev_id = args.input_file, args.dev_username, args.dev_identity_id

    if not Path(input_file).exists() or not input_file.endswith('.json'):
        print(f"Error: Input file not found or not a .json file: {input_file}", file=sys.stderr)
        sys.exit(1)
    try:
        ticket_id, contracts = load_data_from_json(input_file)
    except Exception as e:
        print(f"Error loading JSON file: {e}", file=sys.stderr)
        sys.exit(1)

    if not contracts:
        print("Error: No contracts found in input file", file=sys.stderr)
        sys.exit(1)

    print(f"Loaded {len(contracts)} contract term(s) from {input_file}", file=sys.stderr)
    script_dir = Path(__file__).parent
    template_path = script_dir.parent / 'templates' / 'bulk-add-upc-changeset.template.sql'

    if not template_path.exists():
        print(f"Error: Template file not found: {template_path}", file=sys.stderr)
        sys.exit(1)

    sql = generate_sql(contracts, ticket_id, dev_user, dev_id, template_path)
    repo_root = script_dir.parent.parent
    output_file = generate_output_filepath(ticket_id, repo_root)
    output_file.parent.mkdir(parents=True, exist_ok=True)

    try:
        with open(output_file, 'w') as f:
            f.write(sql)
        print(f"SQL file generated successfully: {output_file}", file=sys.stderr)
    except Exception as e:
        print(f"Error writing output file: {e}", file=sys.stderr)
        sys.exit(1)


if __name__ == '__main__':
    main()