from odoo import models, fields, api
from odoo.exceptions import UserError
from datetime import date,datetime
from odoo.exceptions import ValidationError
import base64
import io
import xlsxwriter
class NCMR_Model(models.Model):
    _name = 'sos_ncmr'
    _description = 'Non-Conforming Material Report'
    _rec_name="ncmr_no"
    _order = 'id desc'

    ncmr_no = fields.Char(string="NCMR No",default=lambda self: self._generate_id(),readonly= True, required= True)
    ncmr_date = fields.Date(string="NCMR Date",default=lambda self: fields.Date.today())
    fg_name = fields.Many2one('sos_fg', string='FG Name')
    fg_category = fields.Many2one('sos_testing_parameters',string="FG Category", compute="_compute_fg_category", store=True)
    batch_no = fields.Char(string="Batch No")
    department = fields.Char(string="Department")
    status = fields.Selection([ ('open', 'Open'),('closed', 'Closed')], default='open' , string="Status")
    material_name = fields.Many2one('sos_material', string="Material Name")
    material_code = fields.Char(string="Material Code")
    material_category = fields.Many2one('sos_material_configuration',string="Material Category")
    rca_required = fields.Selection([('yes', 'Yes'),('no', 'No')], string="RCA Required",default="yes")
    rca_notes_text=fields.Text(string="Notes")
    sfg_name = fields.Many2one('sos_sfg', string="SFG Name")
    sfg_code = fields.Char(string="SFG Code",related="sfg_name.sfg_code")
    sfg_category = fields.Many2one('sos_sfg_configuration',string="SFG Category")
    rejected_qty = fields.Integer(string="Rejected Quantity")
    material_option = fields.Boolean('Materials', default=True)
    sfg_option = fields.Boolean('Semi-Finished Goods')
    fg_option = fields.Boolean('Finished Goods')
    ncmr_type = fields.Selection([ ('new', 'New'),('return', 'Return')], default='new' , string="NCMR Type")
    incoming_doc_ref = fields.Many2one('sos_iqi',string="IQI Ref No",readonly=True)
    return_incoming_doc_ref = fields.Many2one('sos_return_iqi',string="IQI Ref No",readonly=True)
    fir_incoming_doc_ref = fields.Many2one('sos_fir',string="FIR ref",readonly=True)
    fg_incoming_doc_ref = fields.Many2one('sos_fir_brr',string="FIR ref",readonly=True)
    return_fg_incoming_doc_ref = fields.Many2one('sos_return_fir',string="Return FIR ref",readonly=True)
    incoming_responsibility = fields.Text(string="Incoming Responsibility")
    qa_action = fields.Selection([ ('scrap', 'Scrap'),('rework', 'Rework')], string="Action")
    dispensed_doc_ref = fields.Char(string="Dispensed Doc Ref")
    dispensed_responsibility = fields.Text(string="Dispensed Responsibility")
    return_incoming_resposibility = fields.Char(string="Approved Doc Ref")
    approved_doc_ref = fields.Char(string="Approved Doc Ref")
    approved_responsibility = fields.Text(string="Approved Responsibility")
    customer_complaint_doc_ref = fields.Char(string="Customer Complaint Doc Ref")
    customer_complaint_responsibility = fields.Text(string="Customer Complaint Responsibility")
    approved_fg_doc_ref = fields.Char(string="Approved FG Doc Ref")
    approved_fg_responsibility = fields.Text(string="Approved FG Responsibility")
    returned_fg_doc_ref = fields.Char(string="Approved FG Doc Ref")
    returned_fg_responsibility = fields.Text(string="Approved FG Responsibility")
    finished_fg_assy  = fields.Char() 
    finished_fg_assy_responsibility = fields.Text(string="Production Assy FG Responsibility")
    description_of_nc = fields.Html(string="Description of Non-Conformities")
    root_cause_of_nc = fields.Html(string="Root Cause of Non-Conformities")
    containment_action_of_nc = fields.Html(string="Containment Action to close  the Non-Conformities")
    comments_on_capa = fields.Html(string="Comments on Corrective / Preventive Action ")
    qa_comments=fields.Text(string="QA Comments")
    qa_done_on = fields.Date(string="Verified On")
    rework_responsible_rd_user = fields.Many2one('res.users',string='R&D User')
    rd_user = fields.Many2many('res.users',string='R&D User', relation='sos_ncmr_rd_user_rel')
    qa_by = fields.Many2one('res.users',string='QC In-Charge',readonly=True)
    qa_sign = fields.Image(related="qa_by.signature_image",string='QC In-Charge',readonly=True)
    qa_tested_on = fields.Datetime(string="Approved On")
    line_ids = fields.One2many('sos_ncmr_line', 'ncmr_id', string="Line Ids",copy=True)
    capa_line_ids = fields.One2many('sos_ncmr_capa_line', 'ncmr_id', string="CAPA Line Ids",copy=True)
    line_count = fields.Integer(string='Number of Defects', compute='_compute_line_count', store=True)
    unique_defect_count = fields.Integer(string='Required NCMR', compute='_compute_line_count', store=True)
    material_line_count = fields.Integer(string='Number of Defects', compute='_compute_material_line_count', store=True)
    fg_line_count = fields.Integer(string='Number of Defects', compute='_compute_fg_line_count', store=True)
    material_unique_defect_count = fields.Integer(string='Required NCMR', compute='_compute_material_line_count', store=True)
    fg_unique_defect_count = fields.Integer(string='Required NCMR', compute='_compute_fg_line_count', store=True)
    defective_status_ids = fields.One2many('sos_ncmr_defective_status', 'ncmr_id', string='Defectives with Status')
    opened_issues = fields.Integer(string="Issues Open", compute="_calculate_status")
    closed_issues = fields.Integer(string="Issues Closed", compute="_calculate_status")
    action_group = fields.Selection(
        [('production', 'Production Team'), ('scg', 'SCG Team'), ('rd', 'R&D Team')],
        string="Action By",
        default="scg"
    )
    forward_on = fields.Datetime(string="Forwarded On")
    forward_by = fields.Many2one('res.users',string='Forward By',readonly=True)
    forward_sign = fields.Image(related="forward_by.signature_image",string='Sign',readonly=True)
    forward_to = fields.Many2many('res.users', string='Forward To')
    comments=fields.Text(string="Comments")
    scg_approval_by = fields.Many2one('res.users',string='SCG Approved By',readonly=True)
    scg_approval_sign = fields.Image(related="scg_approval_by.signature_image",string='SCG In-Charge',readonly=True)
    scg_approval_on = fields.Datetime(string="Approved On")
    scg_comments=fields.Text(string="Comments")
    production_approval_by = fields.Many2one('res.users',string='Production In-Charge',readonly=True)
    production_approval_sign = fields.Image(related="production_approval_by.signature_image",string='Production In-Charge',readonly=True)
    production_approval_on = fields.Datetime(string="Approved On")
    production_comments=fields.Text(string="Comments")
    rework_action = fields.Selection(
        [('inhouse', 'In-House'), ('outsourcing_vendor', 'Outsourcing Vendor'), ('supplier_replacement', 'Supplier Replacement')],
        string="Rework Action",
        default="inhouse"
    )
    rework_action_by_qc = fields.Boolean(string="Rework")
    rd_comments=fields.Text(string="Comments")
    rd_approval_by = fields.Many2one('res.users',string='R&D In-Charge',readonly=True)
    rd_approval_sign = fields.Image(related="rd_approval_by.signature_image",string='R&D In-Charge',readonly=True)
    rd_approval_on = fields.Datetime(string="Approved On")
    rework_rd_approval_by = fields.Many2one('res.users',string='Rework - R&D In-Charge',readonly=True)
    rework_rd_approval_sign = fields.Image(related="rework_rd_approval_by.signature_image",string='Rework - R&D In-Charge',readonly=True)
    rework_rd_approval_on = fields.Datetime(string="Approved On")
    
    combined_incoming_doc_ref = fields.Reference(
        selection=[
            ('sos_iqi', 'IQI Ref No'),
            ('sos_return_iqi', 'Return IQI Ref No'),
            ('sos_fir_brr', 'BRR Ref'),
            ('sos_return_fir', 'Return BRR FIR Ref'),
            ('sos_fir', 'FIR Ref')
        ],
        string="Document Reference",
        compute="_compute_combined_incoming_doc_ref",
        store=False
    )
    supplier_name = fields.Many2one('sos_suppliers',string="Supplier Name",compute="_get_supplier_name")
    service_provider_name = fields.Many2one('sos_service_providers',string="Service Provider Name",compute="_get_service_provider_name")
    customer_name = fields.Many2one('sos_inventory_customers', string="Customer Name",compute="_get_customer_name")

    outsourcing_return_ref_no = fields.Many2one('sos_sfg_outsourcing_return_register',string="Outsourcing Return Ref No")
    #Excel Write
    def action_ncmr_report_orm_btn(self, from_date, to_date,force_download=True):
        output = io.BytesIO()
        workbook = xlsxwriter.Workbook(output, {'in_memory': True})
        
        headers = {
            'S No':'S No',
            'NCMRNo' : 'NCMR No',
            'Product': 'Product',
            'Date': 'Date',
            'Issue Location': 'Issue Location',
            'Customer Name/Supplier Name/Service Provider Name': 'Customer Name/Supplier Name/Service Provider Name',
            'Customer/Supplier/Service':'Customer/Supplier/Service',
            'Category':'Category',
            'Defective': 'Problem Definition',
            'Defect Qty': 'Defect Qty',
            'Required NCMR': 'Required NCMR',
            'Closed Complaint': 'Closed Complaint',
            'Pending Complaint': 'Pending Complaint',
            'Closure Rate %': 'Closure Rate (%)',
            'PPM':'PPM',
            'Status':'Status',
            'Reason for delay': 'Reason for delay',
            'Responsible': 'Responsible',
        }

        if from_date and to_date:

            ncmr_lines = self.env['sos_ncmr'].search([
                 ('ncmr_date', '>=', from_date),
                 ('ncmr_date', '<=', to_date)
            ])

            result = []
            

            for line in ncmr_lines:
                
                ncmrid  = line.id
                ncmr_no = line.ncmr_no
                
                mat_name = line.material_category.material_name if line.material_category.material_name else None
                sfg_name = line.sfg_category.sfg_name if line.sfg_category.sfg_name else None 
                fg_name  = line.fg_name.display_name if line.fg_name.display_name else None
                product = mat_name or sfg_name or fg_name

                if line.incoming_responsibility:
                    parts = line.incoming_responsibility.split(",")
                    incom_resp_dep = parts[0].strip() + "-" + parts[2].strip() if len(parts) > 2 else ''
                else:
                    incom_resp_dep = ''

                if line.dispensed_responsibility:
                    parts = line.dispensed_responsibility.split(",")
                    dispe_resp_dep = parts[0].strip() + "-" + parts[2].strip() if len(parts) > 2 else ''
                else:
                    dispe_resp_dep = ''

                if line.approved_responsibility:
                    parts = line.approved_responsibility.split(",")
                    appvd_resp_dep = parts[0].strip() + "-" + parts[2].strip() if len(parts) > 2 else ''
                else:
                    appvd_resp_dep = ''

                if line.customer_complaint_responsibility:
                    parts = line.customer_complaint_responsibility.split(",")
                    cusmr_resp_dep = parts[0].strip() + "-" + parts[2].strip() if len(parts) > 2 else ''
                else:
                    cusmr_resp_dep = ''

                if line.approved_fg_responsibility:
                    parts = line.approved_fg_responsibility.split(",")
                    appfg_resp_dep = parts[0].strip() + "-" + parts[2].strip() if len(parts) > 2 else ''
                else:
                    appfg_resp_dep = ''

                if line.returned_fg_responsibility:
                    parts = line.returned_fg_responsibility.split(",")
                    rtnfg_resp_dep = parts[0].strip() + "-" + parts[2].strip() if len(parts) > 2 else ''
                else:
                    rtnfg_resp_dep = ''

                if line.finished_fg_assy_responsibility:
                    parts = line.finished_fg_assy_responsibility.split(",")
                    ffgas_resp_dep = parts[0].strip() + "-" + parts[2].strip() if len(parts) > 2 else ''
                else:
                    ffgas_resp_dep = ''

                responsibility = incom_resp_dep or dispe_resp_dep or appvd_resp_dep or cusmr_resp_dep or appfg_resp_dep or rtnfg_resp_dep or ffgas_resp_dep
                
                custmer_name = None
                suppler_name = None
                serprdr_name = None
                customer = None
                supplier = None
                serviceprovider = None

                if line.customer_name and  line.customer_name.customer_name:
                    custmer_name = line.customer_name.customer_name
                    customer     = 'Customer'

                if line.supplier_name and line.supplier_name.supplier_name:
                    suppler_name = line.supplier_name.supplier_name
                    supplier     = 'Supplier'

                if line.service_provider_name.service_provider_name:
                    serprdr_name = line.service_provider_name.service_provider_name
                    serviceprovider = 'Vendor'

                cust_supp_serprdr_name = custmer_name or suppler_name or serprdr_name
                cust_supp_serprdr      = customer or supplier or serviceprovider

                mat_type   = line.material_name.material_type_id.name if line.material_name.material_type_id.name else None
                fg_type    = line.fg_name.fg_type  if line.fg_name.fg_type else None
                sfg_catgry = line.sfg_name.category  if line.sfg_name.category else None
                category   = mat_type or fg_type or sfg_catgry

                defect_qty = line.rejected_qty
                status = line.status if line.status else 'Open'

                required_ncmr = (
                        line.unique_defect_count if line.unique_defect_count not in (None, 0)
                        else line.fg_unique_defect_count if line.fg_unique_defect_count not in (None, 0)
                        else line.material_unique_defect_count if line.material_unique_defect_count not in (None, 0)
                        else 0
                )
                
                open_count = sum(1 for rec in line.defective_status_ids if rec.status == 'open')
                closed_count = sum(1 for rec in line.defective_status_ids if rec.status == 'closed')
                closure_rate = (closed_count / required_ncmr) * 100 if required_ncmr else 0.0

                
                ppm = None

                recevd_qty = line.combined_incoming_doc_ref

                if not recevd_qty:
                    ppm = None
                else:
                    received_qty = None
                    rejected_qty = None

                    if recevd_qty and hasattr(recevd_qty,'received_qty'):
                        received_qty = recevd_qty.received_qty
                    elif recevd_qty and hasattr(recevd_qty,'batch_size'):
                        received_qty = recevd_qty.batch_size
                    
                    if recevd_qty and hasattr(recevd_qty,'rejected_qty'):
                        rejected_qty = recevd_qty.rejected_qty

                    if received_qty and rejected_qty is not None:
                        ppm = (rejected_qty / received_qty) * 10**6  #issue at PPM bcoz of diff name 
                    else:
                        ppm = None

                issue_location = ''
                
                if line.incoming_doc_ref:
                    issue_location = 'IQI'
                elif line.return_incoming_doc_ref:
                    issue_location = 'Return-IQI'
                elif line.fir_incoming_doc_ref:
                    issue_location = 'FIR'
                elif line.fg_incoming_doc_ref:
                    issue_location = 'FIR BRR'
                elif line.return_fg_incoming_doc_ref:
                    issue_location = 'Return BRR'
                
                if line.defective_status_ids: 
                    
                    defective_names = []

                    for fgname in line.defective_status_ids:

                        if fgname.defective_id:
                            defective_names.append(fgname.defective_id.name)

                        if fgname.material_defective_id:
                            defective_names.append(fgname.material_defective_id.name)

                        if fgname.fg_defective_id:
                            defective_names.append(fgname.fg_defective_id.name)

                        defective_names = list(set(defective_names))
                else:
                    
                    query = """
                            SELECT 
                                sfg_defect.name AS Problem 
                            FROM                              
                                sos_ncmr_line nl
                            JOIN                                  
                                sos_ncmr n ON nl.ncmr_id = n.id
                                                            
                            LEFT JOIN sos_ncmr_defective_status ds ON ds.ncmr_id = nl.ncmr_id  
                                                                                            
                            LEFT JOIN sos_ncmr_line_sos_sfg_defective_line_rel sfg_rel ON nl.id = sfg_rel.sos_ncmr_line_id
                            LEFT JOIN sos_sfg_defective_line sfg_defect ON sfg_rel.sos_sfg_defective_line_id = sfg_defect.id
                                                                                                                            
                            WHERE     
                                n.id =  %s ;

                        """

                    self.env.cr.execute(query,(ncmrid,))
                    record_list = self.env.cr.fetchall()
                    defective_names = [r[0] for r in record_list if r[0]]

                result.append({
                    'S No':'',
                    'NCMRNo': line.ncmr_no,
                    'Product': product,
                    'Date': line.ncmr_date.strftime('%Y-%m-%d'),
                    'Issue Location': issue_location,
                    'Customer Name/Supplier Name/Service Provider Name':cust_supp_serprdr_name,
                    'Customer/Supplier/Service':cust_supp_serprdr,
                    'Category':category,
                    'Defective': defective_names,
                    'Defect Qty': defect_qty,
                    'Required NCMR': required_ncmr,
                    'Closed Complaint':closed_count,
                    'Pending Complaint':open_count,
                    'Closure Rate %':closure_rate,
                    'PPM':ppm,
                    'Status': status,
                    'Reason for delay':'',
                    'Responsible':responsibility
                })

            self._write_sheet(workbook, 'NMCR', result, headers)
            workbook.close()
            output.seek(0)
            excel_data = output.read()

            attachment = self.env['ir.attachment'].create({
                'name': f'NCMR_Export.xlsx',
                'type': 'binary',
                'datas': base64.b64encode(excel_data),
                'res_model': self._name,
                'res_id': self.id,
                'mimetype': 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
            })

            return {
                'type': 'ir.actions.act_url',
                'url': f'/web/content/{attachment.id}?download=true',
                'target': 'self',
            }
    def _write_sheet(self, workbook, sheet_name, result, headers):
        """Write data to each sheet based on specified fields and custom headers."""
        worksheet = workbook.add_worksheet(sheet_name)

        for col, header in enumerate(headers.values()):
            worksheet.write(0, col, header)

        # Write data rows
        for row_num, row in enumerate(result, start=1):
            worksheet.write(row_num, 0, row_num)
            for col_num, key in enumerate(headers.keys()):
                value = row.get(key, '')
                if isinstance(value, list):
                    value = ', '.join(str(v) for v in value)
                worksheet.write(row_num, col_num, str(value))  
                
    @api.depends('incoming_doc_ref', 'return_incoming_doc_ref', 'fg_incoming_doc_ref','return_fg_incoming_doc_ref','fir_incoming_doc_ref')
    def _compute_combined_incoming_doc_ref(self):
        for record in self:
            if record.incoming_doc_ref:
                record.combined_incoming_doc_ref = f'sos_iqi,{record.incoming_doc_ref.id}'
            elif record.return_incoming_doc_ref:
                record.combined_incoming_doc_ref = f'sos_return_iqi,{record.return_incoming_doc_ref.id}'
            elif record.fg_incoming_doc_ref:
                record.combined_incoming_doc_ref = f'sos_fir_brr,{record.fg_incoming_doc_ref.id}'
            elif record.return_fg_incoming_doc_ref:
                record.combined_incoming_doc_ref = f'sos_return_fir,{record.return_fg_incoming_doc_ref.id}'
            elif record.fir_incoming_doc_ref:
                record.combined_incoming_doc_ref = f'sos_fir,{record.fir_incoming_doc_ref.id}'
            else:
                record.combined_incoming_doc_ref = False
    @api.depends('combined_incoming_doc_ref')
    def _get_supplier_name(self):
        for record in self:
            supplier = record.combined_incoming_doc_ref
            if supplier and hasattr(supplier, 'supplier_name'):
                record.supplier_name = supplier.supplier_name
            else:
                record.supplier_name = False  # or '' if it's a Char field
    @api.depends('combined_incoming_doc_ref')
    def _get_service_provider_name(self):
        for record in self:
            service_provider = record.combined_incoming_doc_ref
            if service_provider and hasattr(service_provider, 'service_provider_name'):
                record.service_provider_name = service_provider.service_provider_name
            else:
                record.service_provider_name = False  # or '' if it's a Char field
    @api.depends('combined_incoming_doc_ref')
    def _get_customer_name(self):
        for record in self:
            customer = record.combined_incoming_doc_ref
            if customer and hasattr(customer, 'customer_name'):
                record.customer_name = customer.customer_name
            else:
                record.customer_name = False  # or '' if it's a Char field
    
    @api.depends('fg_name')
    def _compute_fg_category(self):
        for record in self:
            if record.fg_name:
                record.fg_category = self.env['sos_testing_parameters'].search([('fg_name', '=', record.fg_name.id)], limit=1)
            else:
                record.fg_category = False
    @api.onchange('material_option')
    def _onchange_material_option(self):
        if self.material_option:
            self.sfg_option = False
            self.fg_option = False

    @api.onchange('sfg_option')
    def _onchange_sfg_option(self):
        if self.sfg_option:
            self.material_option = False
            self.fg_option = False
    @api.onchange('fg_option')
    def _onchange_fg_option(self):
        if self.fg_option:
            self.material_option = False
            self.sfg_option = False
    @api.depends('defective_status_ids.status')
    def _calculate_status(self):
        for record in self:
            if record.sfg_option:
                opened_issues_count = self.env['sos_ncmr_defective_status'].search_count([
                    ('ncmr_id', '=', record.id),
                    ('defective_id','!=',False),
                    ('status', '=', 'open')
                ])
                # Count the number of closed issues
                closed_issues_count = self.env['sos_ncmr_defective_status'].search_count([
                    ('ncmr_id', '=', record.id),
                    ('defective_id','!=',False),
                    ('status', '=', 'closed')
                ])
            elif record.material_option:
                
                opened_issues_count = self.env['sos_ncmr_defective_status'].search_count([
                    ('ncmr_id', '=', record.id),
                    ('material_defective_id','!=',False),
                    ('status', '=', 'open')
                ])
                
                # Count the number of closed issues
                closed_issues_count = self.env['sos_ncmr_defective_status'].search_count([
                    ('ncmr_id', '=', record.id),
                    ('material_defective_id','!=',False),
                    ('status', '=', 'closed')
                ])
            else:
                
                opened_issues_count = self.env['sos_ncmr_defective_status'].search_count([
                    ('ncmr_id', '=', record.id),
                    ('fg_defective_id','!=',False),
                    ('status', '=', 'open')
                ])
                
                # Count the number of closed issues
                closed_issues_count = self.env['sos_ncmr_defective_status'].search_count([
                    ('ncmr_id', '=', record.id),
                    ('fg_defective_id','!=',False),
                    ('status', '=', 'closed')
                ])
            if opened_issues_count == 0 and closed_issues_count > 0:
                record.status = "closed"
            record.opened_issues = opened_issues_count
            record.closed_issues = closed_issues_count

    @api.depends('line_ids', 'line_ids.defectives')
    def _compute_line_count(self):
        for record in self:
            record.line_count = len(record.line_ids)
            unique_defects = set()
            for line in record.line_ids:
                unique_defects.update(line.defectives.ids)
            record.unique_defect_count = len(unique_defects)
    @api.depends('line_ids', 'line_ids.material_defectives')
    def _compute_material_line_count(self):
        for record in self:
            record.material_line_count = len(record.line_ids)
            material_unique_defects = set()
            for line in record.line_ids:
                material_unique_defects.update(line.material_defectives.ids)
            record.material_unique_defect_count = len(material_unique_defects)
    @api.depends('line_ids', 'line_ids.fg_defectives')
    def _compute_fg_line_count(self):
        for record in self:
            record.fg_line_count = len(record.line_ids)
            fg_unique_defects = set()
            for line in record.line_ids:
                fg_unique_defects.update(line.fg_defectives.ids)
            record.fg_unique_defect_count = len(fg_unique_defects)            
    def _generate_id(self):
        self.qa_done_on = date.today()
        sequence_util = self.env['sos_common_scripts']
        return sequence_util.generate_sequence('sos_ncmr','NCMR', 'ncmr_no')
    def action_forward_esign_btn(self):
        send_email = self.env['sos_common_scripts']
        all_closed = True
        for record in self.defective_status_ids:
            if record.status != 'closed':
                all_closed = False
                if not record.aodr_no:
                    raise UserError("All Status Should Be Closed")
                    return
        if self.qa_action == "scrap":
            if self.material_option:
                material_type = "material"
                column = "material_name"
            elif self.sfg_option:
                material_type = "sfg"
                column = "sfg_name"
            else:
                material_type = "fg"
                column = "fg_name"

            # Creating the disposal register record
            disposal_register = self.env['sos_disposal_register'].create({
                'dr_no': send_email.generate_sequence('sos_disposal_register', 'DR', 'dr_no'),
                'ncmr_no': self.id,
                'goods_type': material_type,
                column: getattr(self, column).id if getattr(self, column) else False # Dynamically assign value based on column name
            })
            return send_email.action_assign_signature(
                    self, 
                    'forward_by',
                    'forward_on',
                    'sos_inventory.sos_qa_user'
                )
        else:

            if all_closed or record.aodr_no:
                self.status = 'closed'
                if self.rework_action == "inhouse":
                    iqi_record = self.env['sos_iqi'].create({
                            'iqi_no': send_email.generate_sequence('sos_iqi', 'R-IQI', 'iqi_no'),
                            'material_option':self.material_option,
                            'sfg_option':self.sfg_option,
                            'received_qty': self.rejected_qty,
                            'iqi_type':'rework',
                            'material_name': self.material_name,
                            'material_code': self.material_name.material_code,
                            'sfg_name': self.sfg_name.id,
                            'sfg_code': self.sfg_name.sfg_code,
                            'supplier_name':self.incoming_doc_ref.supplier_name.id,
                            'service_provider_name':self.incoming_doc_ref.service_provider_name.id,
                            'old_iqi_ref': self.incoming_doc_ref.id,
                            'ir_id_unique_id':self.incoming_doc_ref.ir_id_unique_id.id,
                            'in_tact':self.incoming_doc_ref.in_tact
                            # 'test_report':self.incoming_doc_ref.test_report,
                            # 'test_report_no':self.incoming_doc_ref.test_report_no,
                            # 'test_report_doc':self.incoming_doc_ref.test_report_doc,
                            # 'test_report_filename':self.incoming_doc_ref.test_report_filename
                            

                        })
                
                    # Rework Iteration starts
                    sos_record = self.env['sos_iqi'].search([('id', '=', self.incoming_doc_ref.id)], limit=1)
                    if sos_record:
                        rework_iteration = sos_record.reworked_count + 1
                        
                        sos_record.write({
                            'reworked_count':rework_iteration
                        })
                    # Rework Iteration ends
                return send_email.action_assign_signature(
                    self, 
                    'forward_by',
                    'forward_on',
                    'sos_inventory.sos_qa_user'
                )
            else:
                raise UserError("All Status Should Be Closed")

    def action_report_ncmr_btn(self):
        try:
            action = self.env.ref("sos_inventory.action_report_ncmr").report_action(self)
            return action
        except ValueError as e:
            print(f"Failed to find report action: {e}")
    def action_scg_esign_btn(self):
        sequence_util = self.env['sos_common_scripts']
        orr_no = sequence_util.generate_sequence('sos_sfg_outsourcing_return_register','ORR', 'orr_no')
            
        send_email = self.env['sos_common_scripts']
        send_email.action_assign_signature(
            self, 
            'scg_approval_by',
            'scg_approval_on',
            'sos_inventory.sos_scg_group_user'
        )
        if self.rework_action == "inhouse":
            # Email part
            body_html = f"""
            <p>Below <b>NCMR</b> is waiting for your Action</p>
            """
            send_email = self.env['sos_common_scripts']
            send_email.send_group_email(self.env,'sos_ncmr',self.id,"deenalaura.m@sosaley.in","NCMR Action Request",body_html,"sos_inventory.sos_production_user")
        elif self.rework_action == "supplier_replacement":
            orr_record = self.env['sos_sfg_outsourcing_return_register'].create({
                                'orr_no': orr_no,
                                'iqi_no':self.incoming_doc_ref.id,
                                'iqi_date':self.incoming_doc_ref.iqi_date,
                                'material_name':self.incoming_doc_ref.material_name,
                                'supplier_name':self.incoming_doc_ref.supplier_name.id,
                                'goods_type':'Materials',
                                'returned_qty':self.incoming_doc_ref.rejected_qty
                            })
        else:
            if self.material_option:
                orr_record = self.env['sos_sfg_outsourcing_return_register'].create({
                                'orr_no': orr_no,
                                'iqi_no':self.incoming_doc_ref.id,
                                'iqi_date':self.incoming_doc_ref.iqi_date,
                                'material_name':self.incoming_doc_ref.material_name,
                                'supplier_name':self.incoming_doc_ref.supplier_name.id,
                                'goods_type':'Materials',
                                'returned_qty':self.incoming_doc_ref.rejected_qty
                            })
            else:
                orr_record = self.env['sos_sfg_outsourcing_return_register'].create({
                                'orr_no': orr_no,
                                'iqi_no':self.incoming_doc_ref.id,
                                'iqi_date':self.incoming_doc_ref.iqi_date,
                                'service_provider_name':self.incoming_doc_ref.service_provider_name,
                                'sfg_name':self.incoming_doc_ref.sfg_name,
                                'goods_type':'SFG',
                                'returned_qty':self.incoming_doc_ref.rejected_qty
                            })
            self.outsourcing_return_ref_no = orr_record.id
        
    def action_production_esign_btn(self):
        send_email = self.env['sos_common_scripts']
        send_email.action_assign_signature(
            self, 
            'production_approval_by',
            'production_approval_on',
            'sos_inventory.sos_production_user'
        )
    def action_rd_esign_btn(self):
        send_email = self.env['sos_common_scripts']
        send_email.action_assign_signature(
            self, 
            'rd_approval_by',
            'rd_approval_on'
        )
    def action_rework_rd_esign_btn(self):
        send_email = self.env['sos_common_scripts']
        send_email.action_assign_signature(
            self, 
            'rework_rd_approval_by',
            'rework_rd_approval_on'
        )
    
    def action_qa_esign_btn(self):
        if self.action_group:
            sequence_util = self.env['sos_common_scripts']
            sequence_util.action_assign_signature(
            self, 
            'qa_by',
            'qa_tested_on',
            'sos_inventory.sos_qc_user'
            )
            if self.rework_action_by_qc:
                # Email part
                body_html = f"""
                <p>Below <b>NCMR</b> is waiting for your Action</p>
                """
                send_email = self.env['sos_common_scripts']
                send_email.send_direct_email(
                    self.env,
                    "sos_ncmr",
                    self.id,
                    self.rework_responsible_rd_user.login,
                    "NCMR Rework Action Request",
                    body_html
                )
            if self.action_group == "rd":
                # Email part
                body_html = f"""
                <p>Below <b>NCMR</b> is waiting for your Action</p>
                """
                send_email = self.env['sos_common_scripts']
                for user in self.rd_user:
                    if user.login:
                        send_email.send_direct_email(
                            self.env,
                            "sos_ncmr",
                            self.id,
                            user.login,
                            "NCMR Action Request",
                            body_html
                        )
               
            else:
                if self.action_group == "production":
                    group="sos_inventory.sos_production_user"
                else:
                    group="sos_inventory.sos_scg_group_user"
                # Email part
                body_html = f"""
                <p>Below <b>NCMR</b> is waiting for your Action</p>
                """
                send_email = self.env['sos_common_scripts']
                send_email.send_group_email(self.env,'sos_ncmr',self.id,"deenalaura.m@sosaley.in","NCMR Action Request",body_html,group)
                # Email part ends
            
        else:
            return {
                    'type': 'ir.actions.client',
                    'tag': 'display_notification',
                    'params': {
                        'message': "Select Action to be taken by",
                        'type': 'danger',
                        'sticky': False
                    }
                }
class NCMR_Defective_Status(models.Model):
    _name = 'sos_ncmr_defective_status'
    _description = 'NCMR Defective Status'

    ncmr_id = fields.Many2one('sos_ncmr', string="NCMR Reference", ondelete="cascade")
    defective_id = fields.Many2one('sos_sfg_defective_line', string="Defective")
    material_defective_id = fields.Many2one('sos_material_defective_line', string="Defective")
    fg_defective_id = fields.Many2one('sos_defectives', string="Defective")
    aodr_no = fields.Many2one('sos_aodr',string="AODR No(If Any)")
    status = fields.Selection([('open', 'Open'), ('closed', 'Closed')], string="Status", required=True, default='open')

    _sql_constraints = [
        ('ncmr_defective_unique', 'unique(ncmr_id, defective_id)', 'Each defective can only have one status per NCMR.')
    ]
class NCMR_Model_CAPA_Line(models.Model):
    _name = 'sos_ncmr_capa_line'
    _description = 'CAPA Lines'

    ncmr_id = fields.Many2one('sos_ncmr', string="NCMR Reference", ondelete="cascade")
    issue = fields.Char(string="Issue")
    corrective_action = fields.Html(string="Corrective Action")
    preventive_action = fields.Html(string="Preventive Action")

class NCMR_Model_Line(models.Model):
    _name = 'sos_ncmr_line'
    _description = 'Non-Conforming Material Report Defectives'

    s_no = fields.Char(string="Serial No")
    ncmr_id = fields.Many2one('sos_ncmr', string="NCMR Reference", ondelete="cascade")
    fg_name = fields.Many2one(related='ncmr_id.fg_name', string="FG Name", readonly=True, store=True)
    remarks=fields.Char(string="Specific Remarks")

    
    sfg_category = fields.Many2one(related='ncmr_id.sfg_category', string="SFG Category", readonly=True)
    defective_count = fields.Integer(string='Number of Defectives', compute='_compute_defective_count', store=True)
    defectives = fields.Many2many('sos_sfg_defective_line', string='Defectives')
    defectives_domain = fields.Many2many('sos_sfg_defective_line', compute='_compute_defective_domain')
    
    material_category = fields.Many2one(related='ncmr_id.material_category', string="Material Category", readonly=True)
    material_defectives = fields.Many2many('sos_material_defective_line', string='Defectives')
    material_defectives_domain = fields.Many2many('sos_material_defective_line', compute='_compute_material_defective_domain')
    material_defective_count = fields.Integer(string='Number of Defectives', compute='_compute_material_defective_count', store=True)
    
    fg_category = fields.Many2one(related='ncmr_id.fg_category', string="FG Category", readonly=True)
    fg_defective_count = fields.Integer(string='Number of Defectives', compute='_compute_fg_defective_count', store=True)
    fg_defectives = fields.Many2many('sos_defectives', string='Defectives')
    fg_defectives_domain = fields.Many2many('sos_defectives', compute='_compute_fg_defective_domain')
    @api.depends('ncmr_id', 'ncmr_id.fg_category')
    def _compute_fg_defective_domain(self):
        for record in self:
            if record.fg_category:
                sos_defectives_records = self.env['sos_defectives'].search([('defective_id', '=', record.fg_category.id)])

                defective_names = sos_defectives_records.mapped('id')
                matching_config_ids = self.env['sos_defectives'].search([
                    ('id', 'in', defective_names)
                ]).ids
                if matching_config_ids:
                    record.fg_defectives_domain = matching_config_ids if matching_config_ids else []  # Use [0] to prevent errors
                else:
                    record.fg_defectives_domain = []
            else:
                record.fg_defectives_domain = []
    @api.depends('ncmr_id', 'ncmr_id.material_category')
    def _compute_material_defective_domain(self):
        for record in self:
            if record.material_category:
                defective_names = record.material_category.defective_ids.mapped('id')

                matching_config_ids = self.env['sos_material_defective_line'].search([
                    ('id', 'in', defective_names)
                ]).ids
                if matching_config_ids:
                    record.material_defectives_domain = matching_config_ids if matching_config_ids else []  # Use [0] to prevent errors
                else:
                    record.material_defectives_domain = []
            else:
                record.material_defectives_domain = [] 
    @api.depends('ncmr_id', 'ncmr_id.sfg_category')
    def _compute_defective_domain(self):
        for record in self:
            if record.sfg_category:
                defective_names = record.sfg_category.defective_ids.mapped('id')
                matching_config_ids = self.env['sos_sfg_defective_line'].search([
                    ('id', 'in', defective_names)
                ]).ids
                if matching_config_ids:
                    record.defectives_domain = matching_config_ids if matching_config_ids else []  # Use [0] to prevent errors
                else:
                    record.defectives_domain = []
            else:
                record.defectives_domain = [] 
    @api.model
    def create(self, vals):
        record = super(NCMR_Model_Line, self).create(vals)
        record._update_defective_status_ids()
        return record

    def write(self, vals):
        result = super(NCMR_Model_Line, self).write(vals)
        self._update_defective_status_ids()
        return result

    def _update_defective_status_ids(self):
        for record in self:
            if record.ncmr_id:
                if record.ncmr_id.sfg_option:
                    existing_defective_ids = record.ncmr_id.defective_status_ids.mapped('defective_id.id')
                    new_lines = []
                    for defective in record.defectives:
                        if defective.id not in existing_defective_ids:
                            new_lines.append((0, 0, {
                                'defective_id': defective.id,
                                'ncmr_id': record.ncmr_id.id,
                            }))
                elif record.ncmr_id.material_option:
                    existing_defective_ids = record.ncmr_id.defective_status_ids.mapped('material_defective_id.id')
                    new_lines = []
                    for defective in record.material_defectives:
                        if defective.id not in existing_defective_ids:
                            new_lines.append((0, 0, {
                                'material_defective_id': defective.id,
                                'ncmr_id': record.ncmr_id.id,
                            }))
                else:
                    existing_defective_ids = record.ncmr_id.defective_status_ids.mapped('fg_defective_id.id')
                    new_lines = []
                    for defective in record.fg_defectives:
                        if defective.id not in existing_defective_ids:
                            new_lines.append((0, 0, {
                                'fg_defective_id': defective.id,
                                'ncmr_id': record.ncmr_id.id,
                            }))
                if new_lines:
                    record.ncmr_id.write({
                        'defective_status_ids': new_lines
                    })

    @api.depends('defectives')
    def _compute_defective_count(self):
        for record in self:
            record.defective_count = len(record.defectives)
    @api.depends('material_defectives')
    def _compute_material_defective_count(self):
        for record in self:
            record.material_defective_count = len(record.material_defectives)
    @api.depends('fg_defectives')
    def _compute_fg_defective_count(self):
        for record in self:
            record.fg_defective_count = len(record.fg_defectives)
