import streamlit as st
import pandas as pd
from google.cloud import bigquery
from google.oauth2 import service_account
from io import BytesIO
from html2image import Html2Image
from PIL import Image

# Sidebar for the app
with st.sidebar:
    st.title("BigQuery Data Fetcher and Invoice Generator")

def fetch_data_from_bigquery(query, credentials, job_config=None):
    """Fetch data from BigQuery using the provided SQL query and credentials."""
    client = bigquery.Client(credentials=credentials)
    query_job = client.query(query, job_config=job_config)
    results = query_job.result()  # Wait for the job to complete.
    return results.to_dataframe()

def generate_invoice_html(data):
    """Generate HTML content for the invoice based on the provided data."""
    try:
        required_columns = [
            'final_rule_id', 'created_at', 'Company_Name', 'Company_ID', 
            'Plan_Name', 'Bundle_by', 'Business_Head', 'Application_ID', 
            'Commercial_value', 'Usage_limit', 'Threshold_value', 'Threshold_option', 
            'Expected_Billing', 'Plan_Validity', 'Net_total_billing', 
            'Product_lines', 'Fulfilling_Location', 'Fee_Type', 'Variable_Type', 
            'Chargeable_on', 'Fee_Nature', 'Fee_reversal', 'Reversal_%'
        ]
        
        missing_columns = [col for col in required_columns if col not in data.columns]
        if missing_columns:
            raise ValueError(f"Missing columns in data: {', '.join(missing_columns)}")

        invoice_css = """
        body {
            font-family: 'Arial', sans-serif;
            background: #f4f4f4;
        }
        .invoice {
            max-width: 900px;
            margin: 50px auto;
            padding: 20px;
            background: #fff;
            border-radius: 8px;
            box-shadow: 0 0 15px rgba(0, 0, 0, 0.1);
        }
        .header-content {
            display: flex;
            justify-content: space-between;
            border-bottom: 2px solid #ddd;
            padding-bottom: 20px;
            margin-bottom: 20px;
        }
        .title-section {
            text-align: left;
        }
        .logo-section {
            display: flex;
            flex-direction: column;
            align-items: flex-end;
        }
        .logo {
            max-width: 300px;
            margin-bottom: 10px;
        }
        .company-info {
            list-style: none;
            padding: 0;
            margin: 0;
        }
        .pageTitle {
            color: #8a2be2;
            font-size: 2.5rem;
            margin: 0;
        }
        .ruleID {
            color: #555;
            display: block;
            margin-top: 5px;
            font-size: 1.2rem;
        }
        .invDetails {
            margin-top: 10px;
            font-size: 1.2rem;
            color: #333;
        }
        .card-container {
            display: flex;
            gap: 20px;
            margin-top: 20px;
        }
        .info-card {
            flex: 1;
            padding: 15px;
            background: #f9f9f9;
            border-radius: 8px;
            box-shadow: 0 0 10px rgba(0, 0, 0, 0.1);
            position: relative;
        }
        .card-header {
            font-weight: bold;
            margin-bottom: 10px;
            color: #a689f6;
            font-size: 20px;
            background-image: -webkit-linear-gradient(0deg, #a689f6 3%, #272191 33%, #413bb9 61%);
            background-clip: text;
            -webkit-background-clip: text;
            text-fill-color: transparent;
            -webkit-text-fill-color: transparent;
            border-bottom: 1px solid #ddd;
            padding-bottom: 10px;
        }
        .net-billing {
            font-size: 1.5rem;
            font-weight: bold;
            color: #a689f6;
            font-size: 20px;
            background-image: -webkit-linear-gradient(0deg, #a689f6 3%, #272191 33%, #413bb9 61%);
            background-clip: text;
            -webkit-background-clip: text;
            text-fill-color: transparent;
            -webkit-text-fill-color: transparent;
        }
        .table-container {
            margin-top: 30px;
            margin-bottom: 30px;
        }
        .styled-table {
            width: 100%;
            border-collapse: collapse;
            box-shadow: 0 0 10px rgba(0, 0, 0, 0.1);
            border-radius: 10px;
            overflow: hidden;
        }
        .styled-table thead {
            background: -webkit-linear-gradient(90deg, #a689f6, #272191, #413bb9);
        }
        .styled-table th {
            color: white;
            padding: 8px;
            border: 1px solid #ddd;
            text-align: left;
        }
        .styled-table tbody tr:nth-child(even) {
            background-color: #f9f9f9;
        }
        .styled-table td {
            padding: 8px;
            border: 1px solid #ddd;
            text-align: left;
        }
        """

        invoice_html = f"""
        <html>
        <head>
            <style>
                {invoice_css}
            </style>
        </head>
        <body>
            <div class="invoice">
                <div class="header-content">
                    <div class="title-section">
                        <h1 class="pageTitle">Plan Maker</h1>
                        <small class="ruleID">Rule ID: {data['final_rule_id'].iloc[0]}</small>
                        <h4 class="invDetails">Date: {data['created_at'].iloc[0]}</h4>
                    </div>
                    <div class="logo-section">
                        <img class="logo" src="https://www.medianews4u.com/wp-content/uploads/2021/02/Fynd-Platform-encourages-SMEs-to-start-an-online-business-with-a-new-campaign.jpg" alt="Fynd Image"/>
                        <ul class="company-info">
                            <li><strong>Fynd</strong></li>
                            <li>Ninad Mandavkar</li>
                            <li>ninadmandavkar@gofynd.com</li>
                        </ul>
                    </div>
                </div>
                
                <div class="invoice-body">
                    <div class="card-container">
                        <div class="info-card">
                            <div class="card-header">Company Details</div>
                            <div class="card-content">
                                <p><strong>Name:</strong> {data['Company_Name'].iloc[0]}</p>
                                <p><strong>ID:</strong> {data['Company_ID'].iloc[0]}</p>
                            </div>
                        </div>
                        <div class="info-card">
                            <div class="card-header">Plan Details</div>
                            <div class="card-content">
                                <p><strong>Name:</strong> {data['Plan_Name'].iloc[0]}</p>
                                <p><strong>Bundle by:</strong> {data['Bundle_by'].iloc[0]}</p>
                                <p><strong>Business Head:</strong> {data['Business_Head'].iloc[0]}</p>
                                <p><strong>Application ID:</strong> {data['Application_ID'].iloc[0]}</p>
                            </div>
                        </div>
                    </div>
                    
                    <div class="table-container">
                        <table class="styled-table">
                            <thead>
                                <tr>
                                    <th>Commercial Value</th>
                                    <th>Usage</th>
                                    <th>Threshold Value</th>
                                    <th>Threshold Option</th>
                                    <th>Expected Billing</th>
                                    <th>Plan Validity</th>
                                </tr>
                            </thead>
                            <tbody>
                                <tr>
                                    <td>{data['Commercial_value'].iloc[0]}</td>
                                    <td>{data['Usage_limit'].iloc[0]}</td>
                                    <td>{data['Threshold_value'].iloc[0]}</td>
                                    <td>{data['Threshold_option'].iloc[0]}</td>
                                    <td>{data['Expected_Billing'].iloc[0]}</td>
                                    <td>{data['Plan_Validity'].iloc[0]}</td>
                                </tr>
                            </tbody>
                        </table>
                    </div>
                    
                    <div class="card-container">
                        <div class="info-card">
                            <div class="card-header">Net Billing</div>
                            <div class="card-content net-billing">
                                <p>{data['Net_total_billing'].iloc[0]}</p>
                            </div>
                        </div>
                    </div>
                </div>
            </div>
        </body>
        </html>
        """
        return invoice_html

    except Exception as e:
        st.error(f"Error generating invoice: {e}")
        return None

def save_invoice_as_image(html_content, output_file):
    """Save the provided HTML content as an image."""
    try:
        hti = Html2Image()
        hti.screenshot(html_str=html_content, save_as=output_file)
    except Exception as e:
        st.error(f"Error saving invoice as image: {e}")

def main():
    st.header("Upload your data file")
    
    uploaded_file = st.file_uploader("Choose a file", type=["csv"])
    if uploaded_file:
        data = pd.read_csv(uploaded_file)
        st.write("Data preview:")
        st.write(data.head())
        
        # Define your BigQuery credentials and query here
        # Example credentials and query
        credentials = service_account.Credentials.from_service_account_file('path/to/credentials.json')
        query = """
        SELECT *
        FROM `your_project.your_dataset.your_table`
        WHERE condition = 'value'
        """
        data_from_bq = fetch_data_from_bigquery(query, credentials)
        st.write("Data fetched from BigQuery:")
        st.write(data_from_bq.head())
        
        # Generate and save invoice
        invoice_html = generate_invoice_html(data_from_bq)
        if invoice_html:
            output_file = "invoice.png"
            save_invoice_as_image(invoice_html, output_file)
            st.image(output_file)

if __name__ == "__main__":
    main()