from odoo import models, fields, api
import base64
import io
import xlsxwriter
from odoo.tools.misc import xlsxwriter
from odoo.exceptions import UserError
from datetime import date

class SOSMaterialQueryWizard(models.TransientModel):
    _name = 'sos_material_stock_report_wizard'
    _description = 'SOS Material Query Wizard'

    stock_report_date = fields.Date(string="Stock Status On")
    result_ids = fields.One2many('sos_material_stock_report_result', 'wizard_id', string="Query Results")
    excel_file = fields.Binary(string="Download Excel")
    excel_file_name = fields.Char(string="File Name")



    def run_query(self):
        """Run the query to calculate material stock status between April 1, 2025, and stock_report_date."""
        if not self.stock_report_date:
            raise UserError("Please select a Stock Report Date.")
        
        # Validate stock_report_date is not before April 1, 2025
        start_date = date(2025, 4, 1)
        if self.stock_report_date < start_date:
            raise UserError("Stock Report Date cannot be before April 1, 2025.")

        # Clear existing results
        self.result_ids = [(5, 0, 0)]

        # Fetch all materials
        materials = self.env['sos_material'].search([])
        if not materials:
            raise UserError("No materials found in the system.")

        results = []
        for material in materials:
            # Filter and sum "IN" transactions between April 1, 2025, and stock_report_date
            total_in_qty = sum(material.line_ids_in.filtered(
                lambda line: start_date <= line.date.date() <= self.stock_report_date
            ).mapped('quantity'))

            # Filter and sum "OUT" transactions between April 1, 2025, and stock_report_date
            total_out_qty = sum(material.line_ids_out.filtered(
                lambda line: start_date <= line.date.date() <= self.stock_report_date
            ).mapped('quantity'))

            # Calculate on-date in-hand stock quantity
            ondate_inhand_stock_qty = material.opening_bal_qty + total_in_qty - total_out_qty

            # Ensure non-negative stock quantity (optional, depending on business rules)
            if ondate_inhand_stock_qty < 0:
                ondate_inhand_stock_qty = 0  # Or raise a warning, depending on requirements

            # Append the calculated data to the results
            results.append({
                'wizard_id': self.id,
                'part_no': material.part_no,
                'material_code': material.material_code,
                'inhand_stock_qty': ondate_inhand_stock_qty,
                'inhand_stock_val': ondate_inhand_stock_qty * material.unit_price,
            })

        if not results:
            raise UserError("No stock transactions found for the specified date range.")

        # Bulk create the records for better performance
        self.env['sos_material_stock_report_result'].create(results)

        # Reopen the wizard to display results
        return {
            'type': 'ir.actions.act_window',
            'name': 'Material Stock Status',
            'res_model': self._name,
            'view_mode': 'form',
            'view_id': self.env.ref('sos_inventory.view_sos_material_query_wizard_form').id,
            'target': 'new',
            'res_id': self.id,
        }

    def export_to_excel(self):
        """Export results to Excel."""
        output = io.BytesIO()
        workbook = io.BytesIO()
        workbook = xlsxwriter.Workbook(output)
        worksheet = workbook.add_worksheet('Materials')

        # Write header
        headers = ['Name', 'Material Code', 'In-Hand Stock Qty', 'In-Hand Stock Value']
        for col, header in enumerate(headers):
            worksheet.write(0, col, header)

        # Write data
        row = 1
        for result in self.result_ids:
            worksheet.write(row, 0, result.part_no)
            worksheet.write(row, 1, result.material_code)
            worksheet.write(row, 2, result.inhand_stock_qty)
            worksheet.write(row, 3, result.inhand_stock_val)
            row += 1

        workbook.close()
        output.seek(0)

        self.excel_file = base64.b64encode(output.read())
        self.excel_file_name = 'materials.xlsx'

        return {
            'type': 'ir.actions.act_url',
            'url': f'/web/content/?model={self._name}&id={self.id}&field=excel_file&filename_field=excel_file_name&download=true',
            'target': 'self',
        }
    def open_previous_year_wizard(self):
        return {
            'type': 'ir.actions.act_window',
            'name': 'Export Backup',
            'res_model': 'material_backup_export',
            'view_mode': 'form',
            'target': 'new',
        }