from DepositAPI.model.BaseModel import BaseModel
from DepositAPI.libs.db.RDbSql import DbMySql
from DepositAPI.common import Utility

class ProductQuote(BaseModel):
    """Model for many-to-many relationship between Product and Quote"""
    
    def __init__(self):
        self.table = "tbl_product_quote"
        self.primary_key = None  # Composite key
        self.fields = []
    
    def get_quotes_by_product(self, product_id):
        """Get all quotes linked to a specific product"""
        db = DbMySql()
        query_sql = """
            SELECT q.* 
            FROM tbl_quote q
            INNER JOIN tbl_product_quote pq ON q.ID = pq.QuoteID
            WHERE pq.ProductID = :product_id
            ORDER BY pq.CreatedDate DESC
        """
        rows = db.query(query=query_sql, params={'product_id': product_id})
        return self.process_in_pandas(rows)
    
    def get_products_by_quote(self, quote_id):
        """Get all products linked to a specific quote"""
        db = DbMySql()
        query_sql = """
            SELECT p.* 
            FROM tbl_product p
            INNER JOIN tbl_product_quote pq ON p.ID = pq.ProductID
            WHERE pq.QuoteID = :quote_id
            ORDER BY pq.CreatedDate DESC
        """
        rows = db.query(query=query_sql, params={'quote_id': quote_id})
        return self.process_in_pandas(rows)
    
    def link_product_quote(self, product_id, quote_ids, created_by=None):
        """
        Link a product with multiple quotes
        Args:
            product_id: ID of the product
            quote_ids: List of quote IDs to link
            created_by: Username of the creator
        """
        if not quote_ids or not isinstance(quote_ids, list):
            return {'success': False, 'message': 'Quote IDs must be a non-empty list'}
        
        db = DbMySql()
        
        # Delete existing links for this product
        delete_sql = "DELETE FROM tbl_product_quote WHERE ProductID = :product_id"
        db.query(query=delete_sql, params={'product_id': product_id})
        
        # Insert new links
        insert_sql = """
            INSERT INTO tbl_product_quote (ProductID, QuoteID, CreatedBy)
            VALUES (:product_id, :quote_id, :created_by)
        """
        
        for quote_id in quote_ids:
            try:
                db.query(
                    query=insert_sql, 
                    params={
                        'product_id': product_id,
                        'quote_id': quote_id,
                        'created_by': created_by
                    }
                )
            except Exception as e:
                print(f"Error linking product {product_id} to quote {quote_id}: {e}")
        
        return {'success': True, 'message': 'Product-Quote links updated successfully'}
    
    def unlink_product_quote(self, product_id, quote_id):
        """Remove a specific product-quote link"""
        db = DbMySql()
        delete_sql = """
            DELETE FROM tbl_product_quote 
            WHERE ProductID = :product_id AND QuoteID = :quote_id
        """
        db.query(query=delete_sql, params={
            'product_id': product_id,
            'quote_id': quote_id
        })
        return {'success': True, 'message': 'Product-Quote link removed'}
    
    def get_quote_ids_by_product(self, product_id):
        """Get list of quote IDs for a product (returns simple list of IDs)"""
        db = DbMySql()
        query_sql = """
            SELECT QuoteID 
            FROM tbl_product_quote
            WHERE ProductID = :product_id
            ORDER BY CreatedDate DESC
        """
        rows = db.query(query=query_sql, params={'product_id': product_id})
        return [row['QuoteID'] for row in rows] if rows else []