from odoo import models, fields, api, _

class HazardInventoryHistoryWizard(models.TransientModel):
    _name = "hazard.inventory.history.wizard"
    _description = "Asistente para Reporte Histórico de Inventario"

    date = fields.Date(string="Fecha del Reporte", required=True, default=fields.Date.context_today)

    def action_generate_report(self):
        # 1. Clear previous data for this wizard instance just in case
        self.env['hazard.inventory.history.data'].search([('wizard_id', '=', self.id)]).unlink()

        # 2. Run the SQL query to get stock at date
        query = """
            INSERT INTO hazard_inventory_history_data (
                wizard_id, location_id, product_id, product_tmpl_id, categ_id,
                quantity, standard_price, list_price, total_cost, total_sale, create_uid, create_date, write_uid, write_date
            )
            SELECT 
                %s AS wizard_id,
                loc.id AS location_id,
                prod.id AS product_id,
                tmpl.id AS product_tmpl_id,
                tmpl.categ_id AS categ_id,
                SUM(sml.qty) AS quantity,
                COALESCE(
                    (SELECT value_float 
                     FROM ir_property 
                     WHERE name = 'standard_price' 
                       AND (res_id = 'product.product,' || prod.id OR res_id = 'product.template,' || tmpl.id) 
                     ORDER BY res_id DESC, id DESC LIMIT 1),
                    0
                ) AS standard_price,
                COALESCE(tmpl.list_price, 0) AS list_price,
                
                (SUM(sml.qty) * COALESCE(
                    (SELECT value_float 
                     FROM ir_property 
                     WHERE name = 'standard_price' 
                       AND (res_id = 'product.product,' || prod.id OR res_id = 'product.template,' || tmpl.id) 
                     ORDER BY res_id DESC, id DESC LIMIT 1),
                    0
                )) AS total_cost,
                
                (SUM(sml.qty) * COALESCE(tmpl.list_price, 0)) AS total_sale,
                %s as create_uid,
                now() at time zone 'UTC' as create_date,
                %s as write_uid,
                now() at time zone 'UTC' as write_date
                
            FROM (
                SELECT product_id, location_dest_id as location_id, qty_done as qty 
                FROM stock_move_line 
                WHERE state = 'done' AND date <= %s
                
                UNION ALL
                
                SELECT product_id, location_id as location_id, -qty_done as qty 
                FROM stock_move_line 
                WHERE state = 'done' AND date <= %s
            ) sml
            JOIN stock_location loc ON sml.location_id = loc.id
            JOIN product_product prod ON sml.product_id = prod.id
            JOIN product_template tmpl ON prod.product_tmpl_id = tmpl.id
            WHERE loc.usage = 'internal'
            GROUP BY loc.id, prod.id, tmpl.id, tmpl.categ_id
            HAVING SUM(sml.qty) != 0
        """
        
        target_datetime = str(self.date) + ' 23:59:59'
        self.env.cr.execute(query, (self.id, self.env.uid, self.env.uid, target_datetime, target_datetime))
        
        return {
            'name': _('Inventario Histórico al %s' % self.date.strftime('%d/%m/%Y')),
            'type': 'ir.actions.act_window',
            'res_model': 'hazard.inventory.history.data',
            'view_mode': 'pivot,tree',
            'domain': [('wizard_id', '=', self.id)],
            'context': {
                'search_default_location_id': 1, # This will group by location if we add search view, else ignored
            }
        }
