from odoo import api, fields, models, _
from odoo.exceptions import UserError, ValidationError
import logging

_logger = logging.getLogger(__name__)


class StockInventoryBackdate(models.Model):
    _name = 'stock.inventory.backdate'
    _description = 'Backdated Inventory Adjustment'
    _inherit = ['mail.thread', 'mail.activity.mixin']
    _order = 'backdate_datetime desc, id desc'

    name = fields.Char(string='Reference', required=True, default='New', readonly=True, tracking=True)
    backdate_datetime = fields.Datetime(
        string='Adjustment Date & Time',
        required=True,
        default=fields.Datetime.now,
        help="The date and time for the backdated inventory adjustment",
        tracking=True
    )
    location_id = fields.Many2one(
        'stock.location',
        string='Location',
        required=True,
        domain="[('usage', '=', 'internal')]",
        tracking=True
    )
    company_id = fields.Many2one(
        'res.company',
        string='Company',
        required=True,
        default=lambda self: self.env.company
    )
    state = fields.Selection([
        ('draft', 'Draft'),
        ('done', 'Done'),
        ('cancel', 'Cancelled')
    ], string='Status', default='draft', readonly=True, tracking=True)
    line_ids = fields.One2many(
        'stock.inventory.backdate.line',
        'inventory_id',
        string='Inventory Lines'
    )
    notes = fields.Text(string='Notes')

    @api.model
    def create(self, vals):
        if vals.get('name', 'New') == 'New':
            vals['name'] = self.env['ir.sequence'].next_by_code('stock.inventory.backdate') or 'New'
        return super(StockInventoryBackdate, self).create(vals)

    def action_load_products(self):
        """Load products with current inventory at the location"""
        self.ensure_one()
        if self.state != 'draft':
            raise UserError(_('You can only load products in draft state.'))
        
        if not self.location_id:
            raise UserError(_('Please select a location first.'))
        
        # Get all quants at this location
        quants = self.env['stock.quant'].search([
            ('location_id', '=', self.location_id.id),
            ('company_id', '=', self.company_id.id),
            ('quantity', '!=', 0)
        ])
        
        if not quants:
            raise UserError(_('No products found at this location. You can add products manually.'))
        
        # Get existing product IDs to avoid duplicates
        existing_product_ids = self.line_ids.mapped('product_id').ids
        
        lines = []
        for quant in quants:
            # Skip if already in lines
            if quant.product_id.id in existing_product_ids:
                continue
                
            # Get inventory position at the backdate
            historical_qty = self._get_historical_quantity(
                quant.product_id,
                quant.location_id,
                quant.lot_id,
                quant.package_id,
                quant.owner_id,
                self.backdate_datetime
            )
            
            lines.append((0, 0, {
                'product_id': quant.product_id.id,
                'lot_id': quant.lot_id.id,
                'package_id': quant.package_id.id,
                'owner_id': quant.owner_id.id,
                'theoretical_qty': historical_qty,
                'counted_qty': historical_qty,
                'difference_qty': 0.0,
            }))
        
        if lines:
            self.line_ids = [(0, 0, line[2]) for line in lines]
            return {
                'type': 'ir.actions.client',
                'tag': 'display_notification',
                'params': {
                    'title': _('Products Loaded'),
                    'message': _('%s product(s) loaded successfully.') % len(lines),
                    'type': 'success',
                    'sticky': False,
                }
            }
        else:
            return {
                'type': 'ir.actions.client',
                'tag': 'display_notification',
                'params': {
                    'title': _('No New Products'),
                    'message': _('All products are already in the list.'),
                    'type': 'info',
                    'sticky': False,
                }
            }

    def _get_historical_quantity(self, product, location, lot, package, owner, date):
        """Calculate inventory quantity at a specific date"""
        base_domain = [
            ('product_id', '=', product.id),
            ('state', '=', 'done'),
            ('date', '<=', date),
        ]
        if lot:
            base_domain.append(('lot_id', '=', lot.id))
        if package:
            base_domain.append(('package_id', '=', package.id))
        if owner:
            base_domain.append(('owner_id', '=', owner.id))
        
        # Get all incoming moves (destination = our location)
        domain_in = base_domain + [('location_dest_id', '=', location.id)]
        moves_in = self.env['stock.move.line'].search(domain_in)
        
        # Get all outgoing moves (source = our location)
        domain_out = base_domain + [('location_id', '=', location.id)]
        moves_out = self.env['stock.move.line'].search(domain_out)
        
        qty_in = sum(moves_in.mapped('quantity'))
        qty_out = sum(moves_out.mapped('quantity'))
        
        return qty_in - qty_out

    def action_validate(self):
        """Validate and create backdated stock moves"""
        self.ensure_one()
        if self.state != 'draft':
            raise UserError(_('Only draft adjustments can be validated.'))
        
        if not self.line_ids:
            raise UserError(_('Please add at least one inventory line.'))
        
        # Create stock moves for each line with differences
        for line in self.line_ids:
            if line.difference_qty != 0:
                line._create_stock_move()
        
        self.write({'state': 'done'})
        return True

    def action_cancel(self):
        """Cancel the adjustment"""
        self.ensure_one()
        if self.state == 'done':
            raise UserError(_('Cannot cancel a validated adjustment.'))
        self.write({'state': 'cancel'})
        return True

    def action_draft(self):
        """Reset to draft"""
        self.ensure_one()
        self.write({'state': 'draft'})
        return True


class StockInventoryBackdateLine(models.Model):
    _name = 'stock.inventory.backdate.line'
    _description = 'Backdated Inventory Adjustment Line'
    _sql_constraints = [
        ('unique_product_per_inventory',
         'unique(inventory_id, product_id, lot_id, package_id, owner_id)',
         'You cannot have duplicate products with the same lot/package/owner in the same adjustment!')
    ]

    inventory_id = fields.Many2one(
        'stock.inventory.backdate',
        string='Inventory Adjustment',
        required=True,
        ondelete='cascade'
    )
    product_id = fields.Many2one(
        'product.product',
        string='Product',
        required=True,
        domain="[('type', '=', 'product')]"
    )
    lot_id = fields.Many2one('stock.lot', string='Lot/Serial Number')
    package_id = fields.Many2one('stock.quant.package', string='Package')
    owner_id = fields.Many2one('res.partner', string='Owner')
    theoretical_qty = fields.Float(
        string='Theoretical Quantity',
        readonly=True,
        help="Quantity at the backdated time (can be negative if there was negative stock)"
    )
    counted_qty = fields.Float(
        string='Counted Quantity',
        required=True,
        default=0.0
    )
    difference_qty = fields.Float(
        string='Adjustment Qty (+/-)',
        default=0.0,
        help="Positive value adds stock, negative value removes stock."
    )
    product_uom_id = fields.Many2one(
        'uom.uom',
        string='Unit of Measure',
        related='product_id.uom_id',
        readonly=True
    )
    state = fields.Selection(related='inventory_id.state', string='Status')
    has_negative_theoretical = fields.Boolean(
        string='Has Negative Theoretical',
        compute='_compute_has_negative_theoretical',
        help="Indicates if theoretical quantity is negative"
    )

    @api.onchange('counted_qty')
    def _onchange_counted_qty(self):
        for line in self:
            line.difference_qty = line.counted_qty - line.theoretical_qty

    @api.onchange('difference_qty')
    def _onchange_difference_qty(self):
        for line in self:
            line.counted_qty = line.theoretical_qty + line.difference_qty
    
    @api.depends('theoretical_qty')
    def _compute_has_negative_theoretical(self):
        for line in self:
            line.has_negative_theoretical = line.theoretical_qty < 0
    
    @api.onchange('product_id', 'lot_id', 'package_id', 'owner_id')
    def _onchange_product_id(self):
        """Auto-calculate theoretical quantity when product is selected"""
        if self.product_id and self.inventory_id.location_id and self.inventory_id.backdate_datetime:
            self.theoretical_qty = self.inventory_id._get_historical_quantity(
                self.product_id,
                self.inventory_id.location_id,
                self.lot_id,
                self.package_id,
                self.owner_id,
                self.inventory_id.backdate_datetime
            )
            # Set counted_qty to theoretical_qty by default (Adjustment 0)
            if not self.counted_qty and not self.difference_qty:
                self.counted_qty = self.theoretical_qty
                self.difference_qty = 0.0

    def _create_stock_move(self):
        """Create backdated stock move for this line"""
        self.ensure_one()
        
        if self.difference_qty == 0:
            return
        
        # Find inventory adjustment location
        inventory_location = self.env['stock.location'].search([
            ('usage', '=', 'inventory'),
            ('company_id', 'in', [self.inventory_id.company_id.id, False])
        ], limit=1)
        
        if not inventory_location:
            raise UserError(_('Inventory adjustment location not found. Please check your stock configuration.'))
        
        # Determine source and destination based on difference
        if self.difference_qty > 0:
            # Increase inventory
            location_id = inventory_location.id
            location_dest_id = self.inventory_id.location_id.id
            qty = self.difference_qty
        else:
            # Decrease inventory
            location_id = self.inventory_id.location_id.id
            location_dest_id = inventory_location.id
            qty = abs(self.difference_qty)
        
        # Create stock move with backdated datetime
        backdate = self.inventory_id.backdate_datetime
        move_vals = {
            'name': _('Backdated Inventory Adjustment: %s') % self.inventory_id.name,
            'product_id': self.product_id.id,
            'product_uom': self.product_uom_id.id,
            'product_uom_qty': qty,
            'location_id': location_id,
            'location_dest_id': location_dest_id,
            'company_id': self.inventory_id.company_id.id,
            'is_inventory': True,
            'origin': self.inventory_id.name,
            'date': backdate,
        }
        
        move = self.env['stock.move'].create(move_vals)
        move._action_confirm()
        
        # Check if move line was already created by _action_confirm (e.g. reservation)
        move_line = move.move_line_ids.filtered(lambda ml: ml.product_id.id == self.product_id.id)
        
        move_line_vals = {
            'product_id': self.product_id.id,
            'product_uom_id': self.product_uom_id.id,
            'quantity': qty,
            'location_id': location_id,
            'location_dest_id': location_dest_id,
            'lot_id': self.lot_id.id if self.lot_id else False,
            'package_id': self.package_id.id if self.package_id else False,
            'owner_id': self.owner_id.id if self.owner_id else False,
            'date': backdate,
        }

        if move_line:
            # Update existing line
            move_line = move_line[0]
            move_line.write(move_line_vals)
            _logger.info(f"Updated existing move line {move_line.id} with quantity={qty}")
        else:
            # Create new line if none exists
            move_line_vals['move_id'] = move.id
            move_line = self.env['stock.move.line'].create(move_line_vals)
            _logger.info(f"Created new move line {move_line.id} with quantity={qty}")
        _logger.info(f"Created move line {move_line.id} with quantity_done={qty}")
        
        # Log product valuation settings
        product = self.product_id
        _logger.info(f"Product: {product.name}, Category: {product.categ_id.name}")
        _logger.info(f"Valuation: {product.categ_id.property_valuation}, Cost Method: {product.categ_id.property_cost_method}")
        _logger.info(f"Product Cost: {product.standard_price}")
        
        
        # Mark as picked (required for Odoo 17 _action_done)
        move.picked = True
        for ml in move.move_line_ids:
            ml.picked = True

        # Mark as done
        _logger.info(f"Move state before _action_done: {move.state}")
        result = move._action_done()
        _logger.info(f"Move state after _action_done: {move.state}")
        _logger.info(f"_action_done returned: {result}")
        
        # Refresh move to get latest data
        move = self.env['stock.move'].browse(move.id)
        _logger.info(f"Move state after refresh: {move.state}")
        
        # CRITICAL: Update dates via direct SQL after _action_done
        # The _action_done method overwrites dates, so we must update after
        
        _logger.info(f"Backdating move {move.id} to {backdate}")
        
        # Flush all pending ORM operations to DB before running raw SQL
        self.env.flush_all()

        # Update stock move
        self.env.cr.execute(
            "UPDATE stock_move SET date = %s WHERE id = %s",
            (backdate, move.id)
        )
        _logger.info(f"Updated stock_move {move.id}, rows affected: {self.env.cr.rowcount}")
        
        # Update stock move lines
        self.env.cr.execute(
            "UPDATE stock_move_line SET date = %s WHERE move_id = %s",
            (backdate, move.id)
        )
        _logger.info(f"Updated stock_move_line for move {move.id}, rows affected: {self.env.cr.rowcount}")
        
        # Update stock valuation layer
        # Check if valuation layer exists
        svl_count = self.env['stock.valuation.layer'].search_count([('stock_move_id', '=', move.id)])
        _logger.info(f"Found {svl_count} stock valuation layers for move {move.id}")
        
        if svl_count > 0:
            self.env.cr.execute(
                "UPDATE stock_valuation_layer SET create_date = %s WHERE stock_move_id = %s",
                (backdate, move.id)
            )
            _logger.info(f"Updated stock_valuation_layer for move {move.id}, rows affected: {self.env.cr.rowcount}")
        else:
            _logger.warning(f"No stock valuation layer found for move {move.id}. Product may not use real-time valuation or cost is zero.")
        
        # Update account moves if they exist
        # Refresh move to get account_move_ids
        move = self.env['stock.move'].browse(move.id)
        if move.account_move_ids:
            account_date = backdate.date()
            for account_move in move.account_move_ids:
                self.env.cr.execute(
                    "UPDATE account_move SET date = %s WHERE id = %s",
                    (account_date, account_move.id)
                )
                self.env.cr.execute(
                    "UPDATE account_move_line SET date = %s WHERE move_id = %s",
                    (account_date, account_move.id)
                )
                _logger.info(f"Updated account_move {account_move.id} and lines to {account_date}")

        # Invalidate cache to ensure ORM reloads data from DB
        self.env.invalidate_all()
        
        # Invalidate cache
        self.env.cache.invalidate()
        
        return move