import base64
from io import BytesIO
from reportlab.lib.pagesizes import letter
from reportlab.pdfgen import canvas
from odoo import models, fields, api
from datetime import datetime
import pytz
from odoo.exceptions import UserError
from odoo.tools import pdf
import os
import weasyprint

class ReportGenerator(models.Model):
    _name = 'sos_report_generator'
    _description = 'Report Generator'

    name = fields.Char('Name', required=True)
    report_type = fields.Selection([
        ('daily', 'Daily'),
        ('weekly', 'Weekly'),
        ('monthly', 'Monthly'),
    ], string='Report Type', required=True , default='daily')

    @api.model
    def generate_report(self):
        """
        Generate the report based on the report type.
        """
        # Execute the query for the material data
        self.env.cr.execute("""
           SELECT part_no,inhand_stock_qty,CASE WHEN inhand_stock_val IS NULL THEN '0.00' ELSE TO_CHAR(inhand_stock_val, 'FM999999999.00') END as formatted_inhand_stock_val FROM sos_material;
        """)
        materialdatas = self.env.cr.fetchall()

        # Execute the query for the fg data
        self.env.cr.execute("""
           SELECT name,inhand_stock_qty,CASE WHEN inhand_stock_val IS NULL THEN '0.00' ELSE TO_CHAR(inhand_stock_val, 'FM999999999.00') END as formatted_inhand_stock_val FROM sos_fg ;
        """)
        fgdatas = self.env.cr.fetchall()

        # Execute the query for the sfg data
        self.env.cr.execute("""
           SELECT name,inhand_stock_qty,CASE WHEN inhand_stock_val IS NULL THEN '0.00' ELSE TO_CHAR(inhand_stock_val, 'FM999999999.00') END as formatted_inhand_stock_val FROM sos_sfg;
        """)
        sfgdatas = self.env.cr.fetchall()

        # Prepare the HTML content
        report_html = self.generate_html_report(materialdatas, fgdatas, sfgdatas)
        # Generate PDF and send email
        attachment = self.create_pdf_from_html(report_html)
        
        self.send_email(attachment)

    def generate_html_report(self, materialdatas, fgdatas, sfgdatas):
        """
        Generate the HTML content for the report with tables.
        """
        report_data = []
        report_data.append("<html>")
        report_data.append("<head><title>Stock Report</title></head>")
        report_data.append("<body>")

        report_data.append("<h3>Material Stock Report</h3>")
        report_data.append(self.generate_html_table(materialdatas))

        report_data.append("<h3>FG Stock Report</h3>")
        report_data.append(self.generate_html_table(fgdatas))

        report_data.append("<h3>SFG Stock Report</h3>")
        report_data.append(self.generate_html_table(sfgdatas))

        report_data.append("</body>")
        report_data.append("</html>")

        return "\n".join(report_data)

    def generate_html_table(self, data):
        """
        Generate an HTML table for the provided data.
        """
        table_html = "<table style='border: solid 1px black; border-collapse: collapse;'>"
        table_html += '<tr><th style="border: solid 1px black;background-color: #808080; {font_style} ">ID</th><th style="border: solid 1px black;background-color: #808080; {font_style} ">Name</th><th style="border: solid 1px black;background-color: #808080; {font_style} ">Inhand Stock Qty</th><th style="border: solid 1px black;background-color: #808080; {font_style}">Inhand Stock Val</th></tr>'

        font_style = "font-family: Arial, sans-serif; font-size: 15px; color: #000000;"
    
        sno = 1
        for row in data:
            table_html += f'<tr><td style="border: solid 1px black; {font_style}">{sno}</td><td style="border: solid 1px black; {font_style}">{row[0]}</td><td style="border: solid 1px black; {font_style} ">{row[1]}</td><td style="border: solid 1px black; {font_style} ">{row[2]}</td></tr>'
            sno = sno+1

        table_html += "</table><br/><br/>"
        return table_html

    def send_email(self, attachment):
        
        email_values = {
            'subject': 'Scheduled Stock Report',
            'body_html': f'<p>Dear User,</p><p>Find the attached report.</p>',
            'email_from': "slink <alert@sosaley.in>",    # Sender's email
            'email_to': 'ashokkumar.s@sosaley.in',       # Recipient's email
            'attachment_ids': [(6, 0, [attachment.id])]  # Attach the report
        }

        mail = self.env['mail.mail'].create(email_values)
        mail.send()

    def create_pdf_from_html(self, html_content):
        """
        Convert HTML content to PDF using weasyprint.
        """
        pdf_content = weasyprint.HTML(string=html_content).write_pdf()

        # Encode the PDF content as base64
        pdf_base64 = base64.b64encode(pdf_content)

        # Create and return the PDF attachment
        attachment = self.env['ir.attachment'].create({
            'name': 'stock_report.pdf',
            'type': 'binary',
            'datas': pdf_base64,
            'mimetype': 'application/pdf',
        })
        return attachment
