import pandas as pd
from docx import Document
import os
from datetime import datetime

def auto_populate_chambers_form(excel_file, template_file, output_file):
    """
    Auto-populate Chambers submission form (Word document) with data from Excel file
    
    Parameters:
    excel_file (str): Path to the Excel file containing matter data
    template_file (str): Path to the Word document template (Chambers.docx)
    output_file (str): Path where the populated document will be saved
    """
    print(f"Reading data from Excel file: {excel_file}")
    
    # Read Excel data
    df = pd.read_excel(excel_file)
    
    # Load the Word document template
    doc = Document(template_file)
    
    # Count how many publishable and confidential matters we have
    publishable_matters = df[df['Confidential/Publishable'].str.lower() == 'publishable']
    confidential_matters = df[df['Confidential/Publishable'].str.lower() == 'confidential']
    
    print(f"Found {len(publishable_matters)} publishable matters and {len(confidential_matters)} confidential matters")
    
    # Populate publishable matters (Section D)
    for i, matter in publishable_matters.iterrows():
        if i >= 10:  # Max 10 publishable matters allowed
            print("Warning: More than 10 publishable matters found in Excel. Only the first 10 will be used.")
            break
            
        matter_num = i + 1
        section_name = f"Publishable Matter {matter_num}"
        
        # Find the section in the document
        found_section = False
        for para in doc.paragraphs:
            if section_name in para.text:
                found_section = True
                break
        
        if not found_section:
            print(f"Warning: Could not find section '{section_name}' in the template document.")
            continue
        
        # Populate fields for this matter
        populate_matter_fields(
            doc, 
            section_name, 
            "D", 
            matter['Client Name'],
            matter['Matter/Case Overview'],
            matter['Value'],
            matter.get('Cross-border', 'N/A'),
            matter['Lead partners'],
            matter['Team Members'],
            matter.get('Other Firms/counsels Involved', ''),
            matter.get('Current Status', ''),
            matter.get('Additional Info', '')
        )
    
    # Populate confidential matters (Section E)
    for i, matter in confidential_matters.iterrows():
        if i >= 10:  # Max 10 confidential matters allowed
            print("Warning: More than 10 confidential matters found in Excel. Only the first 10 will be used.")
            break
            
        matter_num = i + 1
        section_name = f"Confidential Matter {matter_num}"
        
        # Find the section in the document
        found_section = False
        for para in doc.paragraphs:
            if section_name in para.text:
                found_section = True
                break
        
        if not found_section:
            print(f"Warning: Could not find section '{section_name}' in the template document.")
            continue
        
        # Populate fields for this matter
        populate_matter_fields(
            doc, 
            section_name, 
            "E", 
            matter['Client Name'],
            matter['Matter/Case Overview'],
            matter['Value'],
            matter.get('Cross-border', 'N/A'),
            matter['Lead partners'],
            matter['Team Members'],
            matter.get('Other Firms/counsels Involved', ''),
            matter.get('Current Status', ''),
            matter.get('Additional Info', '')
        )
    
    # Populate client lists
    populate_client_lists(doc, publishable_matters, confidential_matters)
    
    # Save the populated document
    doc.save(output_file)
    print(f"Document successfully populated and saved as: {output_file}")


def populate_matter_fields(doc, section_name, section_prefix, 
                          client_name, matter_overview, value, cross_border,
                          lead_partner, team_members, other_firms,
                          status, additional_info):
    """
    Populate the fields for a specific matter in the document
    """
    # Combine matter overview with role, significance, etc. from Excel
    # (Adjust as needed based on your Excel structure)
    
    # Find and replace fields
    for i, para in enumerate(doc.paragraphs):
        # Client name field
        if f"{section_prefix}1 Name of client" in para.text:
            # Find the paragraph after this one and set its text
            if i+1 < len(doc.paragraphs):
                doc.paragraphs[i+1].text = client_name
        
        # Matter summary field
        elif f"{section_prefix}2 Summary of matter" in para.text:
            if i+1 < len(doc.paragraphs):
                doc.paragraphs[i+1].text = matter_overview
        
        # Matter value field
        elif f"{section_prefix}3 Matter value" in para.text:
            if i+1 < len(doc.paragraphs):
                doc.paragraphs[i+1].text = str(value)
        
        # Cross-border field
        elif f"{section_prefix}4 Is this a cross-border matter" in para.text:
            if i+1 < len(doc.paragraphs):
                doc.paragraphs[i+1].text = cross_border
        
        # Lead partner field
        elif f"{section_prefix}5 Lead partner" in para.text:
            if i+1 < len(doc.paragraphs):
                doc.paragraphs[i+1].text = lead_partner
        
        # Team members field
        elif f"{section_prefix}6 Other team members" in para.text:
            if i+1 < len(doc.paragraphs):
                doc.paragraphs[i+1].text = team_members
        
        # Other firms field
        elif f"{section_prefix}7 Other firms advising" in para.text:
            if i+1 < len(doc.paragraphs):
                doc.paragraphs[i+1].text = other_firms
        
        # Status field
        elif f"{section_prefix}8 Date of completion" in para.text:
            if i+1 < len(doc.paragraphs):
                doc.paragraphs[i+1].text = status
        
        # Additional info field
        elif f"{section_prefix}9 Other information" in para.text:
            if i+1 < len(doc.paragraphs):
                doc.paragraphs[i+1].text = additional_info


def populate_client_lists(doc, publishable_matters, confidential_matters):
    """
    Populate the publishable and confidential client lists
    """
    # Find the client list tables
    tables = doc.tables
    publishable_clients_table = None
    confidential_clients_table = None
    
    # Locate the tables by checking their content
    for table in tables:
        for row in table.rows:
            for cell in row.cells:
                if "D -- [PUBLISHABLE] CLIENTS" in cell.text:
                    publishable_clients_table = table
                elif "E0 -- [CONFIDENTIAL] CLIENTS" in cell.text:
                    confidential_clients_table = table
    
    # Populate publishable clients table
    if publishable_clients_table:
        for i, matter in publishable_matters.iterrows():
            if i >= len(publishable_clients_table.rows) - 1:  # -1 for header row
                break
                
            # Row index (skipping header row)
            row_idx = i + 1
            
            # Set client name
            if len(publishable_clients_table.rows[row_idx].cells) >= 2:
                publishable_clients_table.rows[row_idx].cells[1].text = matter['Client Name']
                
                # Set 'New Client' field if available in Excel
                if 'New Client' in matter and len(publishable_clients_table.rows[row_idx].cells) >= 3:
                    publishable_clients_table.rows[row_idx].cells[2].text = matter.get('New Client', '')
    
    # Populate confidential clients table
    if confidential_clients_table:
        for i, matter in confidential_matters.iterrows():
            if i >= len(confidential_clients_table.rows) - 1:  # -1 for header row
                break
                
            # Row index (skipping header row)
            row_idx = i + 1
            
            # Set client name
            if len(confidential_clients_table.rows[row_idx].cells) >= 2:
                confidential_clients_table.rows[row_idx].cells[1].text = matter['Client Name']
                
                # Set 'New Client' field if available in Excel
                if 'New Client' in matter and len(confidential_clients_table.rows[row_idx].cells) >= 3:
                    confidential_clients_table.rows[row_idx].cells[2].text = matter.get('New Client', '')


if __name__ == "__main__":
    # File paths - adjust these to your actual file locations
    excel_file = r"C:\Users\Administrator\Documents\Submissions- Arbitration (version 1).xlsb.xlsx"  # Your Excel file with matter data
    template_file = r"C:\Users\Administrator\Desktop\Submission Form Templates\Templates\Chambers.docx"# Original Chambers form template
    
    # Create output file with timestamp
    timestamp = datetime.now().strftime("%Y%m%d_%H%M%S")
    output_file = f"Chambers_Populated_{timestamp}.docx"
    
    # Run the auto-population
    auto_populate_chambers_form(excel_file, template_file, output_file) this code is giving  PS C:\Users\Administrator\Desktop\Bd team> python app.py
Reading data from Excel file: C:\Users\Administrator\Documents\Submissions- Arbitration (version 1).xlsb.xlsx
Traceback (most recent call last):
  File "C:\Users\Administrator\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\indexes\base.py", line 3805, in get_loc
    return self._engine.get_loc(casted_key)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "index.pyx", line 167, in pandas._libs.index.IndexEngine.get_loc
  File "index.pyx", line 196, in pandas._libs.index.IndexEngine.get_loc
  File "pandas\\_libs\\hashtable_class_helper.pxi", line 7081, in pandas._libs.hashtable.PyObjectHashTable.get_item
  File "pandas\\_libs\\hashtable_class_helper.pxi", line 7089, in pandas._libs.hashtable.PyObjectHashTable.get_item
KeyError: 'Confidential/Publishable'

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "C:\Users\Administrator\Desktop\Bd team\app.py", line 231, in <module>
    auto_populate_chambers_form(excel_file, template_file, output_file)
  File "C:\Users\Administrator\Desktop\Bd team\app.py", line 24, in auto_populate_chambers_form
    publishable_matters = df[df['Confidential/Publishable'].str.lower() == 'publishable']
                             ~~^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:\Users\Administrator\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\frame.py", line 4102, in __getitem__
    indexer = self.columns.get_loc(key)
              ^^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:\Users\Administrator\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\indexes\base.py", line 3812, in get_loc
    raise KeyError(key) from err
KeyError: 'Confidential/Publishable'
PS C:\Users\Administrator\Desktop\Bd team>    this error