import pandas as pd
from docx import Document
from docx.shared import RGBColor
import os
from docx.oxml.ns import qn

def populate_chambers(excel_path, doc_path_yes, doc_path_no, output_path):
    try:
        print("Loading Excel data...")
        df = pd.read_excel(excel_path)
        print(f"Excel data loaded: {len(df)} rows")
        
        # Create directories for temporary files if they don't exist
        temp_dir = os.path.join(os.path.dirname(output_path), "temp")
        os.makedirs(temp_dir, exist_ok=True)
        
        yes_files = []
        no_files = []
        
        # Iterate through each row of the DataFrame
        for i, row in df.iterrows():
            # Determine which document to use based on the Confidential/Publishable column
            confidential_val = str(row.get('Confidential / Publishable ', '')).strip()
            
            if confidential_val == 'Yes':
                # Create a new document from the template for each row
                doc = Document(doc_path_yes)
                if doc.tables:
                    table = doc.tables[0]  # Use the first table in the template
                    fill_table_with_data(row, table, True)
                    
                    # Save as temporary file
                    temp_file = os.path.join(temp_dir, f"yes_{i}.docx")
                    doc.save(temp_file)
                    yes_files.append(temp_file)
                    print(f"Created 'Yes' document for row {i+1}")
                else:
                    print(f"Warning: No tables found in the 'Yes' template.")
                    
            elif confidential_val == 'No':
                # Create a new document from the template for each row
                doc = Document(doc_path_no)
                if doc.tables:
                    table = doc.tables[0]  # Use the first table in the template
                    fill_table_with_data(row, table, False)
                    
                    # Save as temporary file
                    temp_file = os.path.join(temp_dir, f"no_{i}.docx")
                    doc.save(temp_file)
                    no_files.append(temp_file)
                    print(f"Created 'No' document for row {i+1}")
                else:
                    print(f"Warning: No tables found in the 'No' template.")
            else:
                print(f"Warning: Unexpected value in the 'Confidential / Publishable ' column: {confidential_val}.")
        
        # Combine all documents: 'No' files followed by 'Yes' files
        combine_word_documents(no_files + yes_files, output_path)
        print(f"All documents combined successfully to {output_path}!")
        
        # Clean up temporary files
        for file in no_files + yes_files:
            try:
                os.remove(file)
            except Exception as e:
                print(f"Warning: Could not delete temporary file {file}: {e}")
        
    except Exception as e:
        print(f"Unexpected error: {e}")

def fill_table_with_data(row, table, is_confidential):
    try:
        # Using the exact same cell references as your original code
        if len(table.rows) >= 25:  # Make sure the table has enough rows
            # Fill the cells from the row in the DataFrame using your original cell references
            fill_cell(table.cell(4, 0), str(row['Client Name ']), is_confidential)  # Client Name
            fill_cell(table.cell(24, 0), str(row['Description  (in 500 words)']), is_confidential)  # Description
            fill_cell(table.cell(14, 0), str(row['Value ']), is_confidential)  # Matter/Case Overview
            fill_cell(table.cell(20, 0), str(row['Lead partners']), is_confidential)  # Lead Partner
            fill_cell(table.cell(22, 0), str(row['Other Firms/counsels Involved']), is_confidential)  # Other Involved Firms

            print(f"Table filled with {'Yes' if is_confidential else 'No'} form data.")
        else:
            print(f"Warning: Table has only {len(table.rows)} rows. Expected at least 25.")
    except IndexError as e:
        print(f"Error: Unable to fill the table. {e}")

def fill_cell(cell, text, is_confidential):
    # Clear existing content by deleting each paragraph
    for paragraph in cell.paragraphs:
        cell._element.remove(paragraph._element)

    # Adding a new paragraph to the cell
    p = cell.add_paragraph()
    run = p.add_run(text)

    if is_confidential:
        run.font.color.rgb = RGBColor(255, 0, 0)  # Set the font color to red

def combine_word_documents(files, output_path):
    """
    Combines multiple Word documents into one.
    First file becomes the base document, and each subsequent file's content is added to it.
    """
    if not files:
        print("No files to combine.")
        return
    
    print(f"Combining {len(files)} documents...")
    
    # Create a new document
    merged_document = Document()
    
    # Remove the blank paragraph that's created by default
    if merged_document.paragraphs:
        p = merged_document.paragraphs[0]._element
        p.getparent().remove(p)
        p._p = p._element = None
    
    for i, file in enumerate(files):
        print(f"Adding content from {file}")
        try:
            sub_doc = Document(file)
            
            # Add a page break before adding content, except for the first document
            if i > 0:
                merged_document.add_page_break()
            
            # Copy all elements from the subdocument
            for element in sub_doc.element.body:
                # Don't copy the section break
                if element.tag != qn('w:sectPr'):
                    merged_document.element.body.append(element)
            
        except Exception as e:
            print(f"Warning: Could not add content from {file}: {e}")
    
    # Save the combined document
    merged_document.save(output_path)
    print(f"Combined document saved to {output_path}")

# File paths
excel_path = r"C:\Users\Administrator\Documents\Submissions- Arbitration (version 1).xlsb.xlsx" 
doc_path_yes = r"C:\Users\Administrator\Desktop\Iflr_Yes.docx"
doc_path_no = r"C:\Users\Administrator\Desktop\Iflr_No.docx"
output_path = r"C:\Users\Administrator\Desktop\output.docx" 
    
populate_chambers(excel_path, doc_path_yes, doc_path_no, output_path)   in this in the strating im getting some emoty pages so plz help me not to get those white empty pages