import mysql.connector
from database.connection import get_db_connection
import logging

# 물성 정보 추가
def add_material_property(data):
    try:
        with get_db_connection() as conn:
            with conn.cursor(dictionary=True) as cursor:
                sql = """
                    INSERT INTO material_properties (material_code, material_name, material_type, density, remarks)
                    VALUES (%s, %s, %s, %s, %s)
                """
                cursor.execute(sql, (
                    data.get('material_code'),
                    data.get('material_name'),
                    data.get('material_type'),
                    data.get('density'),
                    data.get('remarks')
                ))
                conn.commit()
                return cursor.lastrowid
    except mysql.connector.Error as err:
        logging.error(f"Error adding material property: {err}")
        return None

# 물성 정보 수정
def update_material_property(data):
    try:
        with get_db_connection() as conn:
            with conn.cursor(dictionary=True) as cursor:
                sql = """
                    UPDATE material_properties SET
                        material_code = %s,
                        material_name = %s,
                        material_type = %s,
                        density = %s,
                        remarks = %s
                    WHERE id = %s
                """
                cursor.execute(sql, (
                    data.get('material_code'),
                    data.get('material_name'),
                    data.get('material_type'),
                    data.get('density'),
                    data.get('remarks'),
                    data.get('id')
                ))
                conn.commit()
                return True
    except mysql.connector.Error as err:
        logging.error(f"Error updating material property: {err}")
        return False

# 물성 정보 삭제
def delete_material_property(material_id):
    try:
        with get_db_connection() as conn:
            with conn.cursor(dictionary=True) as cursor:
                cursor.execute("SELECT COUNT(*) as count FROM inventory WHERE material_id = %s", (material_id,))
                if cursor.fetchone()['count'] > 0:
                    logging.warning(f"Cannot delete material property {material_id} as it is in use by inventory.")
                    return False
                cursor.execute("DELETE FROM material_properties WHERE id = %s", (material_id,))
                conn.commit()
                return True
    except mysql.connector.Error as err:
        logging.error(f"Error deleting material property: {err}")
        return False

# 모든 물성 정보 조회
def get_all_material_properties():
    try:
        with get_db_connection() as conn:
            with conn.cursor(dictionary=True) as cursor:
                cursor.execute("SELECT * FROM material_properties")
                return cursor.fetchall()
    except mysql.connector.Error as err:
        logging.error(f"Error getting all material properties: {err}")
        return []

# ID로 특정 물성 정보 조회
def get_material_property_by_id(material_id):
    try:
        with get_db_connection() as conn:
            with conn.cursor(dictionary=True) as cursor:
                cursor.execute("SELECT * FROM material_properties WHERE id = %s", (material_id,))
                return cursor.fetchone()
    except mysql.connector.Error as err:
        logging.error(f"Error getting material property by id: {err}")
        return None

# 재고 입고 (추가)
def add_inventory_to_db(data):
    try:
        with get_db_connection() as conn:
            with conn.cursor(dictionary=True) as cursor:
                stock = data.get('receipt_quantity', 0) - data.get('usage_quantity', 0)
                sql = """
                    INSERT INTO inventory (material_id, receipt_date, receipt_quantity, usage_quantity, stock, unit_cost, remarks, is_depleted, supplier_id, item_name, item_number)
                    VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
                """
                cursor.execute(sql, (
                    data.get('material_id'),
                    data.get('receipt_date'),
                    data.get('receipt_quantity'),
                    data.get('usage_quantity', 0),
                    stock,
                    data.get('unit_cost'),
                    data.get('remarks'),
                    1 if stock <= 0 else 0,
                    data.get('supplier_id'),
                    data.get('item_name'),
                    data.get('item_number')
                ))
                conn.commit()
                return cursor.lastrowid
    except mysql.connector.Error as err:
        logging.error(f"Error adding inventory: {err}")
        return None

# 재고 정보 수정
def update_inventory_in_db(data):
    try:
        with get_db_connection() as conn:
            with conn.cursor(dictionary=True) as cursor:
                stock = data.get('receipt_quantity', 0) - data.get('usage_quantity', 0)
                sql = """
                    UPDATE inventory SET
                        material_id = %s,
                        receipt_date = %s,
                        receipt_quantity = %s,
                        usage_quantity = %s,
                        stock = %s,
                        unit_cost = %s,
                        remarks = %s,
                        is_depleted = %s,
                        supplier_id = %s,
                        item_name = %s,
                        item_number = %s
                    WHERE id = %s
                """
                cursor.execute(sql, (
                    data.get('material_id'),
                    data.get('receipt_date'),
                    data.get('receipt_quantity'),
                    data.get('usage_quantity'),
                    stock,
                    data.get('unit_cost'),
                    data.get('remarks'),
                    1 if stock <= 0 else 0,
                    data.get('supplier_id'),
                    data.get('item_name'),
                    data.get('item_number'),
                    data.get('id')
                ))
                conn.commit()
                return True
    except mysql.connector.Error as err:
        logging.error(f"Error updating inventory: {err}")
        return False

# 재고 소진 처리
def deplete_inventory_in_db(inventory_id):
    try:
        with get_db_connection() as conn:
            with conn.cursor(dictionary=True) as cursor:
                sql = "UPDATE inventory SET is_depleted = 1, stock = 0 WHERE id = %s"
                cursor.execute(sql, (inventory_id,))
                conn.commit()
                return True
    except mysql.connector.Error as err:
        logging.error(f"Error depleting inventory: {err}")
        return False

# 모든 재고 목록 조회
def get_all_inventory_from_db():
    try:
        with get_db_connection() as conn:
            with conn.cursor(dictionary=True) as cursor:
                sql = """
                    SELECT
                        i.id, i.material_id, i.receipt_date, i.receipt_quantity, i.usage_quantity,
                        i.stock, i.unit_cost, i.remarks, i.is_depleted, i.supplier_id,
                        i.item_name, i.item_number,
                        mp.material_code, mp.material_name, mp.material_type, mp.density,
                        s.name_kr as supplier
                    FROM inventory i
                    LEFT JOIN material_properties mp ON i.material_id = mp.id
                    LEFT JOIN suppliers s ON i.supplier_id = s.id
                    ORDER BY i.receipt_date DESC
                """
                cursor.execute(sql)
                return cursor.fetchall()
    except mysql.connector.Error as err:
        logging.error(f"Error getting all inventory: {err}")
        return []

# 특정 재고 상세 정보 조회
def get_inventory_details_from_db(inventory_id):
    try:
        with get_db_connection() as conn:
            with conn.cursor(dictionary=True) as cursor:
                sql = """
                    SELECT
                        i.*,
                        mp.material_code, mp.material_name, mp.material_type, mp.density,
                        s.name_kr as supplier_name
                    FROM inventory i
                    LEFT JOIN material_properties mp ON i.material_id = mp.id
                    LEFT JOIN suppliers s ON i.supplier_id = s.id
                    WHERE i.id = %s
                """
                cursor.execute(sql, (inventory_id,))
                inventory_data = cursor.fetchone()
                if not inventory_data:
                    return None
        
        return {
            "inventory": inventory_data
        }
    except mysql.connector.Error as err:
        logging.error(f"Error getting inventory details: {err}")
        return None

# 특정 자재의 총 재고량 조회
def get_total_stock_for_material(material_id):
    try:
        with get_db_connection() as conn:
            with conn.cursor(dictionary=True) as cursor:
                sql = "SELECT SUM(stock) as total_stock FROM inventory WHERE material_id = %s AND is_depleted = 0"
                cursor.execute(sql, (material_id,))
                result = cursor.fetchone()
                return result['total_stock'] if result and result['total_stock'] is not None else 0
    except mysql.connector.Error as err:
        logging.error(f"Error getting total stock for material: {err}")
        return 0