import os
import base64
from datetime import datetime
from odoo import models, fields, api
from weasyprint import HTML
import calendar

class ReportGenerator(models.Model):
    _name = "sos_report_generator"
    _description = "Monthly Report Generation"

    report_file = fields.Binary("Report File")
    report_filename = fields.Char("Report Filename")

    @api.model
    def generate_and_save_pdf(self):
        sales_group = self.env.ref("sos_inventory.sos_sales_user", raise_if_not_found=False)
        management_group = self.env.ref("sos_inventory.sos_management_user", raise_if_not_found=False)

        # Get users from both groups
        sales_users = self.env['res.users'].search([('groups_id', 'in', [sales_group.id])]) if sales_group else []
        management_users = self.env['res.users'].search([('groups_id', 'in', [management_group.id])]) if management_group else []

        # Get current month and year
        current_year = datetime.now().year
        current_month = datetime.now().month
        current_month_alpha = datetime.now().strftime("%B")
        from_date = f"01/{current_month:02d}/{current_year}"
        to_date = f"{calendar.monthrange(current_year, current_month)[1]}/{current_month:02d}/{current_year}"

        # Generate reports for sales users individually
        for sales_user in sales_users:
            title = f"SPENCO REPORT {current_month_alpha} {current_year}"
            filename = f"spenco_report_{current_month}_{current_year}_{sales_user.name}.pdf"
            domain = [('status', '=', 'open'), ('sales_person', '=', sales_user.id)]
            domain += [
                ('line_ids.status_changed_on', '>=', from_date),
                ('line_ids.status_changed_on', '<=', to_date),
                ('spenco_status', '!=', 'Suspects'),
            ]
            email_cc=''
            self._generate_pdf_for_users(domain, filename,title,sales_user.login,email_cc)

        # Generate a single report for all management users
        if management_users:
            title = f"SPENCO REPORT {current_month_alpha} {current_year}"
            filename = f"spenco_report_{current_month}_{current_year}_top_management.pdf"
            domain = [('status', '=', 'open')]
            domain += [
                ('line_ids.status_changed_on', '>=', from_date),
                ('line_ids.status_changed_on', '<=', to_date),
                ('spenco_status', '!=', 'Suspects'),
            ]

            email_to = 'ramachandran.r@sosaley.com'
            email_cc = 'sethuraman.n@sosaley.com,anitha.a@sosaley.com'
            self._generate_pdf_for_users(domain, filename,title,email_to,email_cc)

        return True

    def _generate_pdf_for_users(self, domain, filename,title,email_to,email_cc):
        """Common function to generate reports for given domain and filename."""
        records = self.env['sos_case_diary'].search(domain)
        if not records:
            return

        case_diary_data = []
        totals = {
            'Prospects': 0,
            'Engaged': 0,
            'Negotiation': 0,
            'Commercial Order': 0,
            'Expected Order Value': 0,
        }

        for record in records:
            product_or_project = record.project_name if record.interested_in == "projects" else record.products
            latest_record = self.env['sos_case_diary_line'].search(
                [('ref_id', '=', record.id)], order="id desc", limit=1
            )

            current_state_value = round(latest_record.current_state_value, 2)
            spenco_status = latest_record.spenco_status

            # Update totals based on spenco_status
            if spenco_status == 'Prospects':
                totals['Prospects'] += current_state_value
                totals['Expected Order Value'] += round(current_state_value * 0.1, 2)
            elif spenco_status == 'Engaged':
                totals['Engaged'] += current_state_value
                totals['Expected Order Value'] += round(current_state_value * 0.3, 2)
            elif spenco_status == 'Negotiation':
                totals['Negotiation'] += current_state_value
                totals['Expected Order Value'] += round(current_state_value * 0.5, 2)
            elif spenco_status == 'Commercial Order':
                totals['Commercial Order'] += current_state_value
                totals['Expected Order Value'] += round(current_state_value, 2)

            case_diary_data.append({
                'customer_name': record.customer_name.customer_name,
                'customer_city': record.customer_city,
                'lead_generated_by': record.lead_generated_by,
                'products': product_or_project,
                'quantity': record.quantity,
                'sales_person': record.sales_person.name,
                'account_start_date': record.account_start_date,
                'status_changed_on': latest_record.status_changed_on,
                'spenco_status': latest_record.spenco_status,
                'current_state_value': latest_record.current_state_value,
                'status_values': {
                    'Prospects': current_state_value if spenco_status == 'Prospects' else '',
                    'Engaged': current_state_value if spenco_status == 'Engaged' else '',
                    'Negotiation': current_state_value if spenco_status == 'Negotiation' else '',
                    'Commercial Order': current_state_value if spenco_status == 'Commercial Order' else '',
                },
                'expected_order_value': round(
                    current_state_value * (
                        0.1 if spenco_status == 'Prospects' else
                        0.3 if spenco_status == 'Engaged' else
                        0.5 if spenco_status == 'Negotiation' else
                        1.0 if spenco_status == 'Commercial Order' else 0
                    ), 2
                ),
                'totals': totals,
            })

        self.generate_html_report(case_diary_data, filename,title,email_to,email_cc)


    def generate_html_report(self, data,filename,title,email_to, email_cc):
        """Converts the given data list into an HTML table format and generates a PDF."""
        
        # Define the table header
        html = f"""
        <html>
        <head>
            <style>
                @page {{
                size: A4 landscape;
                margin: 20mm;
            }}
            body {{
                font-family: Arial, sans-serif;
                margin: 10px;
                font-size: 11px;
            }}
            table {{
                width: 100%;
                border-collapse: collapse;
                table-layout: fixed;
                word-wrap: break-word;
            }}
            th, td {{
                border: 1px solid #ddd;
                padding: 5px;
                text-align: left;
                white-space: normal;
            }}
            th {{
                background-color: #f2f2f2;
            }}
            tr:nth-child(even) {{
                background-color: #f9f9f9;
            }}
            </style>
        </head>
        <body>
            <h2 style="text-align: center;">{title}</h2>
            <table>
                <thead>
                    <tr>
                        <th rowspan="2">S.No</th>
                        <th rowspan="2">Customer</th>
          
          
                        <th rowspan="2">Sales Person</th>
                        <th rowspan="2">Status Changed On</th>
                        <th rowspan="2">Project/Product</th>
                        <th rowspan="2">Quantity</th>
                        <th colspan="4">Order Booking (In Lakhs)</th>
                        <th rowspan="2">Expected Order Value (In Lakhs)</th>
                    </tr>
                    <tr>
                        <th>Prospects</th>
                        <th>Engaged</th>
                        <th>Negotiation</th>
                        <th>Commercial Order</th>
                    </tr>
                </thead>
                <tbody>
        """
        # Populate table rows
        for index, record in enumerate(data, start=1):
            current_state_value = record.get("current_state_value", 0)
            spenco_status = record.get("spenco_status", "")
            html += """
                <tr>
                    <td>{}</td>
                    <td>{}</td>
                    <td>{}</td>
                    <td>{}</td>
                    <td>{}</td>
                    <td>{}</td>
                    <td>{}</td>
                    <td>{}</td>
                    <td>{}</td>
                    <td>{}</td>
                    <td>{}</td>
                </tr>
            """.format(
                index,
                record.get('customer_name', '-'),
                record.get('sales_person', '-'),
                record.get('status_changed_on', '-'),
                record.get('products', '-'),
                record.get('quantity', '-'),
                f"₹ {current_state_value:.2f}" if spenco_status == "Prospects" else "-",
                f"₹ {current_state_value:.2f}" if spenco_status == "Engaged" else "-",
                f"₹ {current_state_value:.2f}" if spenco_status == "Negotiation" else "-",
                f"₹ {current_state_value:.2f}" if spenco_status == "Commercial Order" else "-",

                f"₹ {record.get('expected_order_value', 0):.2f}"
            )

        # Add totals row
        totals = data[0].get('totals', {})
        total_prospects = f"₹ {totals.get('Prospects', 0):.2f}"
        total_engaged = f"₹ {totals.get('Engaged', 0):.2f}"
        total_negotiation = f"₹ {totals.get('Negotiation', 0):.2f}"
        total_commercial_order = f"₹ {totals.get('Commercial Order', 0):.2f}"
        total_expected_value = f"₹ {totals.get('Expected Order Value', 0):.2f}"

        html += """
            <tr>
                <td colspan="6"><b>Total</b></td>
                <td><b>{}</b></td>
                <td><b>{}</b></td>
                <td><b>{}</b></td>
                <td><b>{}</b></td>
                <td><b>{}</b></td>
            </tr>
        """.format(
            total_prospects, total_engaged, total_negotiation, total_commercial_order, total_expected_value
        )

        # Close the table and body
        html += """
            </tbody>
            </table>
        </body>
        </html>
        """

        # Define the output directory and file path
        current_year = datetime.now().year
        current_month = datetime.now().month
        current_month_alpha = datetime.now().strftime("%B")
        output_dir = f"/var/www/html/slink_reports/spenco_reports/{current_year}/{current_month_alpha}"
        os.makedirs(output_dir, exist_ok=True)
        new_output_dir = f"spenco_reports/{current_year}/{current_month_alpha}"

        output_path = os.path.join(output_dir, filename)
        new_path = os.path.join(new_output_dir, filename)
        # Generate PDF
        HTML(string=html).write_pdf(output_path)
        
        # Email part
        url = f"https://slinkreport.sosaley.in/{new_path}"
        message = f"""
        <p>This Month consolidated <b>SPENCO REPORT</b> is ready</p>
        """
        body_html = f"""
        <p>Hello User,</p>
        {message}
        <p>Click the button below to Download:</p>
        <table width="100%" cellspacing="0" cellpadding="0">
            <tr>
                <td>
                    <table cellspacing="0" cellpadding="0">
                        <tr>
                            <td style="border-radius: 5px;" bgcolor="#71639e">
                                <a href="{url}" target="_blank" style="padding: 4px 8px; border: 1px solid #71639e;border-radius: 5px;font-family: Helvetica, Arial, sans-serif;font-size: 14px; color: #ffffff;text-decoration: none;font-weight:bold;display: inline-block;">
                                    Click Here
                                </a>
                            </td>
                        </tr>
                    </table>
                </td>
            </tr>
        </table>
        """
        subject = f"Monthly SPENCO Report"
        mail_values = {
                'subject': subject,
                'body_html': body_html,
                'email_to': email_to,
                'email_cc': email_cc if email_cc else False,
                'email_from': "slink <alert@sosaley.in>",
            }
        mail = self.env['mail.mail'].sudo().create(mail_values)
        mail.sudo().send()
        # Email part ends
        
        return output_path


