import os
import csv
from fillpdf import fillpdfs
import tkinter as tk
from tkinter import filedialog

# --- CONFIGURATION ---
PDF_TEMPLATE_PATH = "Automated_Run_Sheet_Template.pdf" 
OUTPUT_PDF_BASE_NAME = "Filled_Run_Sheet" 

# PDF Field counts
MAX_CONVEYANCE_ROWS = 30
MAX_MORTGAGE_ROWS = 30
OTHER_TYPE_MAX_ROWS = {
    "ftl_info": 4, "cl_info": 4, "ucc_info": 4,
    "bc_info": 4, "judgmnt_info": 4
}

# --- Document Type Categorization Rules ---
CONVEYANCE_TYPES = {
    "AGREEMENT", "ASSIGN/RENTS", "COURT ORDER", "DECLARATION/RESTRICT",
    "DECLARATION/TAKING", "DEED", "EASEMENT", "FINAL JUDGMENT", 
    "MUNICIPALITY - DEED", "POWER OF ATTORNEY", "RIGHT OF WAY"
}
MORTGAGE_LIEN_TYPES = {
    "ASSUMPTION/MORTGAGE", "CONDOMINIUM LIEN", "EXTENSION/MORTGAGE",
    "MORTGAGE", "MUNICIPALITY - MORT.", "POSTPONEMENT/MORTGAG", 
    "SUBORDINATION AGREE", "TAX SALE CERTIFICATE", "LIEN"
}
OTHER_TYPE_MAP = { # Checked FIRST
    "FEDERAL TAX LIEN": "ftl_info", "CONSTRUCTION LIEN": "cl_info",
    "UCC": "ucc_info", "BANKRUPTCY": "bc_info", "JUDGMENT": "judgmnt_info",
}
IGNORE_TYPES = { # Checked FIRST
    "LIS PENDENS", "NOTICE/SETTLEMENT", "LIS PENDENS/RECORDED",
    "LIS PENDENS/FORCLOS", "N/SETTLEMENT PARTIES", "DISCHARGE/MORTGAGE",
    "RELEASE/FED TAX LIEN", "CANCELLATION OF MTG", "DISCHARGE/LIS PENDEN",
    "RELEASE", "DISCHARGE", "CANCELLATION", "BILL OF SALE",
    "MISC RECORDED DOC", "ASSIGNMENT/MORTGAGE", "INHERITANCE TAX WVR",
}

# --- FUNCTION TO READ CSV AND EXTRACT DATA ---
def read_csv_data(csv_filepath):
    extracted_records = []
    if not os.path.exists(csv_filepath):
        print(f"Error: Input CSV file '{csv_filepath}' not found."); return extracted_records
    print(f"Reading CSV file: {csv_filepath}")
    try:
        with open(csv_filepath, 'r', encoding='utf-8', newline='') as f:
            reader = csv.reader(f)
            try: header = next(reader); print(f"CSV Header read: {header}") 
            except StopIteration: print("Error: CSV file is empty."); return extracted_records
            # Indices based on user's manually created CSV structure
            idx_book = 3; idx_page = 4; idx_type_desc = 5; idx_party_code = 7
            max_index_needed = 7 
            row_count = 0; processed_count = 0
            for row in reader:
                row_count += 1
                if len(row) <= max_index_needed or not any(field.strip() for field in row): continue 
                try:
                    book_text_raw = row[idx_book].strip()
                    page_text_raw = row[idx_page].strip()
                    type_desc_text = row[idx_type_desc].strip().upper()
                    party_code_text = row[idx_party_code].strip().upper() # Read party code

                    if not type_desc_text: continue
                    book_text = book_text_raw.lstrip('0'); book_text = book_text if book_text else '0'
                    page_text = page_text_raw.lstrip('0'); page_text = page_text if page_text else '0'          
                    record = { 'book': book_text, 'page': page_text, 'type_desc': type_desc_text,
                               'party_code': party_code_text, # Store party code
                               'book_num': float('inf'), 'page_num': float('inf') }
                    try: record['book_num'] = int(book_text) 
                    except ValueError: pass
                    try: record['page_num'] = int(page_text)
                    except ValueError: pass
                    extracted_records.append(record); processed_count += 1
                except IndexError: print(f"Skipping row {row_count+1} due to IndexError (needs {max_index_needed+1} columns).")
                except Exception as e: print(f"Error processing CSV row {row_count+1}: {e}")        
    except Exception as e: print(f"An error occurred reading or parsing '{csv_filepath}': {e}")
    print(f"CSV Parsing finished. Found {processed_count} potentially valid records from {row_count} data rows.")
    return extracted_records

# --- Helper function for sorting ---
def sort_key(record): return (record.get('book_num', float('inf')), record.get('page_num', float('inf')))

# --- Helper function for deduplication ---
def deduplicate_list(records_list, category_name):
    seen_book_page = set(); deduplicated_list = []; print(f"  Deduplicating {category_name} ({len(records_list)} records)...")
    for record in records_list:
        book_page_key = f"B{record.get('book', '')}P{record.get('page', '')}"; 
        if book_page_key not in seen_book_page: seen_book_page.add(book_page_key); deduplicated_list.append(record)
    print(f"  {category_name} size after deduplication: {len(deduplicated_list)}")
    return deduplicated_list

# --- MAIN FUNCTION ---
def main():
    if not os.path.exists(PDF_TEMPLATE_PATH): print(f"Error: Template PDF '{PDF_TEMPLATE_PATH}' not found."); return
    # --- Get CSV Filename using File Dialog ---
    print("Opening file dialog to select CSV input file...")
    root = tk.Tk(); root.withdraw() 
    csv_input_filepath = filedialog.askopenfilename( title="Select the CSV file containing land records", initialdir=os.getcwd(), filetypes=(("CSV files", "*.csv"), ("All files", "*.*")))
    root.destroy()
    if not csv_input_filepath: print("No file selected. Exiting."); return
    print(f"User selected file: {csv_input_filepath}")
    base_input_name = os.path.splitext(os.path.basename(csv_input_filepath))[0]
    output_pdf_path = f"{OUTPUT_PDF_BASE_NAME}_{base_input_name}.pdf"

    data_to_fill = {}; print("\nStarting data processing...")
    all_records = read_csv_data(csv_input_filepath); print(f"\nData processing started. Read {len(all_records)} records from CSV.")
    if not all_records: print("No records parsed."); return
    
    # --- Categorize Records (Reordered Checks + GT Deed Filter) ---
    conveyances_list = []; mortgages_list = []; other_lists = { prefix: [] for prefix in OTHER_TYPE_MAX_ROWS.keys() }
    print("\nCategorizing records...")
    ignored_count = 0; uncategorized_count = 0; other_counts = { prefix: 0 for prefix in OTHER_TYPE_MAX_ROWS.keys() }; gt_deed_skipped_count = 0
    for record in all_records:
        type_desc = record.get('type_desc', ''); party_code = record.get('party_code', ''); category_assigned = False
        # 1. Ignore specific types
        if any(ignore_type in type_desc for ignore_type in IGNORE_TYPES): ignored_count += 1; continue 
        # 2. Check Specific Other Types FIRST
        for other_type_key, prefix in OTHER_TYPE_MAP.items():
            if other_type_key in type_desc:
                 if prefix in other_lists: other_lists[prefix].append(record); other_counts[prefix] += 1; category_assigned = True; break 
        if category_assigned: continue 
        # 3. Check Conveyance Types (AND Filter GT Deeds)
        is_conveyance_type = any(conv_type in type_desc for conv_type in CONVEYANCE_TYPES)
        if is_conveyance_type:
            if "DEED" in type_desc and party_code == 'GT': # Check specifically for DEED and GT
                print(f"  Skipping GT Deed: {type_desc} - Bk {record.get('book')} Pg {record.get('page')}")
                gt_deed_skipped_count += 1
            else:
                conveyances_list.append(record) # Add other conveyances or non-GT Deeds
            category_assigned = True # Mark assigned even if skipped as GT Deed to prevent further checks
            continue 
        # 4. Check Mortgage/Lien Types
        if any(m_type in type_desc for m_type in MORTGAGE_LIEN_TYPES):
             mortgages_list.append(record); category_assigned = True; continue
        # 5. Handle records not categorized
        if not category_assigned: uncategorized_count += 1; print(f"  -> Uncategorized Type (Skipped): '{type_desc}'")
    print(f"Categorization Summary: Conveyances={len(conveyances_list)}, Mortgages/Liens={len(mortgages_list)}, Other={sum(other_counts.values())}, Ignored={ignored_count}, GT Deeds Skipped={gt_deed_skipped_count}, Uncategorized={uncategorized_count}")

    # --- Deduplicate ---
    print("\nDeduplicating Conveyances and Mortgages based on Book/Page..."); 
    conveyances_list = deduplicate_list(conveyances_list, "Conveyances")
    mortgages_list = deduplicate_list(mortgages_list, "Mortgages/Liens")

    # --- Sort ---
    print("\nSorting Conveyances and Mortgages by Book/Page number..."); conveyances_list.sort(key=sort_key); mortgages_list.sort(key=sort_key)

    # --- Prepare PDF data ---
    print("\nPreparing data for PDF fields...")
    # Conveyances
    print(f"  Populating Conveyances (Max: {MAX_CONVEYANCE_ROWS})..."); idx_c = 0
    for record in conveyances_list:
        if idx_c < MAX_CONVEYANCE_ROWS: data_to_fill[f'conv_book_{idx_c}'] = record.get('book', ''); data_to_fill[f'conv_page_{idx_c}'] = record.get('page', ''); idx_c += 1
        else: print(f"  Warning: More conveyances ({len(conveyances_list)}) than PDF rows."); break
    # Mortgages/Liens
    print(f"  Populating Mortgages/Liens (Max: {MAX_MORTGAGE_ROWS})..."); idx_m = 0
    for record in mortgages_list:
         if idx_m < MAX_MORTGAGE_ROWS: data_to_fill[f'mtg_book_{idx_m}'] = record.get('book', ''); data_to_fill[f'mtg_page_{idx_m}'] = record.get('page', ''); idx_m += 1
         else: print(f"  Warning: More mortgages/liens ({len(mortgages_list)}) than PDF rows."); break
    # Other Specific Types (With FTL Formatting Correction)
    other_type_row_indices = { prefix: 0 for prefix in OTHER_TYPE_MAX_ROWS.keys() }
    for prefix, records in other_lists.items():
        max_rows_for_type = OTHER_TYPE_MAX_ROWS.get(prefix, 0); print(f"  Populating {prefix} fields (Max: {max_rows_for_type})..."); records.sort(key=sort_key) 
        for record in records:
             current_idx = other_type_row_indices.get(prefix, 0)
             if current_idx < max_rows_for_type:
                 field_name = f'{prefix}_{current_idx}'; book = record.get('book', ''); page = record.get('page', ''); type_desc = record.get('type_desc', '')
                 # --- Start Formatting info_text ---
                 info_text = "" ; has_book = book and book != '0'; has_page = page and page != '0'
                 # --- Specific formatting for FTL ---
                 if prefix == 'ftl_info':
                     if has_book and has_page: info_text = f"Bk {book} Pg {page}"
                     elif has_book: info_text = f"Bk {book}"
                     elif has_page: info_text = f"Pg {page}"
                     else: info_text = type_desc # Fallback for FTL
                 # --- Standard formatting for other types ---
                 else:
                      if has_book and has_page: info_text = f"{type_desc} - Bk {book} Pg {page}"
                      elif has_book: info_text = f"{type_desc} - Bk {book}"
                      elif has_page: info_text = f"{type_desc} - Pg {page}"
                      else: info_text = type_desc
                 # --- End Formatting info_text ---        
                 data_to_fill[field_name] = info_text; other_type_row_indices[prefix] = current_idx + 1
             else: print(f"  Warning: More records for {prefix} ({len(records)}) than PDF rows."); break

    # --- Fill the PDF ---
    if not data_to_fill: print("\nNo data was prepared to fill PDF fields."); return
    try:
        fillpdfs.write_fillable_pdf(PDF_TEMPLATE_PATH, output_pdf_path, data_to_fill, flatten=False)
        print(f"\nSuccessfully created '{output_pdf_path}'")
        print("Manual Steps Remaining: Fill Header, Mortgage Statuses/Dates, Ignored Types.")
    except Exception as e: print(f"\nAn error occurred during PDF filling: {e}")

# --- Run main function ---
if __name__ == "__main__":
    main()