import pandas as pd
from docx import Document


def populate_chambers(excel_path, doc_path, output_path):
    try:
        print("Loading Excel data...")
        df = pd.read_excel(excel_path)
        print(f"Excel data loaded: {len(df)} rows")
        
        print("Loading Word document...")
        print("Available columns:", df.columns.tolist())

        doc = Document(doc_path)
        tables = doc.tables
        print(f"Total tables found in document: {len(tables)}")
        
        if not tables:
            print("Error: No tables found in document.")
            return
        
        publishable_index = 0
        confidential_index = 0
        
        # Populate Publishable Matters
        for i, row in df.iterrows():
            if i < len(tables):
                table = tables[i]  # Assume publishable tables appear first
                if len(table.rows) >= 18:
                    try:
                       
                        table.cell(2, 0).text = f" {row['Client Name ']}"
                        table.cell(3, 0).text = f" {row['Description  (in 500 words)']}"
                        table.cell(4, 0).text = f" {row['Value ']}"
                        table.cell(6, 0).text = f" {row['Lead partners']}"
                        table.cell(7, 0).text = f" {row['Team Members']}"
                        
                        table.cell(9, 0).text = f" {row['Current Status (ongoing / completed )']}"
                        
                       
                        #table.cell(0, 0).text = f" {row['Description  (in 500 words)']}"
                        # table.cell(4, 0).text = f" {row['Value ']}"
                        # table.cell(10, 0).text = f" {row['Lead partners']}"
                        # table.cell(12, 0).text = f" {row['Team Members']}"
                        print(f"Matter {i} populated successfully.")
                    except IndexError as e:
                        print(f"Error: Table {i} has fewer rows than expected. Skipping entry. {e}")
                else:
                    print(f"Warning: Table {i} has only {len(table.rows)} rows. Expected at least 6.")
                publishable_index += 1
        
        # Populate Confidential Matters
        # for i, row in df.iterrows():
        #     if row.get('Confidential', 'No') == "Yes":  # Check confidentiality column
        #         table_index = confidential_index + publishable_index
        #         if table_index < len(tables):
        #             table = tables[table_index]
        #             if len(table.rows) >= 6:
        #                 try:
        #                     table.cell(1, 0).text = f"E1 Name of client: {row['Client Name']}"
        #                     table.cell(2, 0).text = f"E2 Summary: {row['Description (in500 words)']}"
        #                     table.cell(3, 0).text = f"E3 Matter value: {row['Value']}"
        #                     print(f"Confidential matter {i} populated successfully.")
        #                 except IndexError as e:
        #                     print(f"Error: Confidential Table {table_index} has fewer rows than expected. Skipping entry. {e}")
        #             else:
        #                 print(f"Warning: Confidential Table {table_index} has only {len(table.rows)} rows. Expected at least 6.")
        #             confidential_index += 1
        
        doc.save(output_path)
        print("Document updated successfully!")
    except Exception as e:
        print(f"Unexpected error: {e}")


# File paths
excel_path = r"C:\Users\Administrator\Documents\Submissions- Arbitration (version 1).xlsb.xlsx" 
doc_path = r"C:\Users\Administrator\Desktop\Submission Form Templates\Templates\Chambers.docx" 
output_path = r"C:\Users\Administrator\Desktop\confif.docx" 
 

populate_chambers(excel_path, doc_path, output_path)
    in this code in some forms in the pdf is not going to correct cells ...so now i will two one one page doc one for publishable mattters and other for confidential matters ....now make one one row enetry ...for each row fill the same one page doc and at the end cmbine all the docs ....like if excel have 10publisbale and 10 confidential rows ..in end combine all thwse 20 forms ....and deelte the temproray saved single single upadetd doc