import pandas as pd
import src.etl.extract as ex


def get_product_list():
    demand = ex.read_demand_data()
    print(demand["Material Number"].unique())
    return demand["Material Number"].unique()


def get_employee_list():
    employee = ex.read_employee_data()
    employee = employee["Description"]

    return employee["Employee_Type"].unique()


def get_released_product_list(start_date=None):
    
    released_orders = ex.read_orders_data(
        start_date=start_date, 
        # end_date=end_date
    )
    
    
    product_list = released_orders["Material Number"].unique().tolist()
    print(f"Released products for date range {start_date}: {len(product_list)} products")
    return product_list


def get_available_dates():
    """
    COOIS_Released_Prod_Orders.csv에서 사용 가능한 모든 날짜를 가져옵니다.
    
    Returns:
        tuple: (start_dates, end_dates) - 고유한 시작 날짜와 종료 날짜 리스트
    """
    # 모든 데이터를 가져옴 (날짜 필터링 없이)
    released_orders = ex.read_orders_data()
    
    # 날짜 컬럼을 datetime으로 변환 (이미 extract.py에서 변환되지만 확실히 하기 위해)
    released_orders["Basic start date"] = pd.to_datetime(released_orders["Basic start date"])
    released_orders["Basic finish date"] = pd.to_datetime(released_orders["Basic finish date"])
    
    # 고유한 시작 날짜와 종료 날짜 추출
    start_dates = sorted(released_orders["Basic start date"].dt.date.unique())
    end_dates = sorted(released_orders["Basic finish date"].dt.date.unique())
    
    # 모든 고유한 날짜들 (시작날짜 + 종료날짜)
    all_dates = sorted(set(start_dates + end_dates))
    
    return all_dates, start_dates, end_dates


def get_date_ranges():
    """
    COOIS_Released_Prod_Orders.csv에서 날짜 범위 조합을 가져옵니다.
    
    Returns:
        list: 사용 가능한 (start_date, end_date) 조합 리스트
    """
    released_orders = ex.read_orders_data()
    
    # 날짜 컬럼을 datetime으로 변환
    released_orders["Basic start date"] = pd.to_datetime(released_orders["Basic start date"])
    released_orders["Basic finish date"] = pd.to_datetime(released_orders["Basic finish date"])
    
    # 고유한 날짜 범위 조합 추출
    date_ranges = released_orders[["Basic start date", "Basic finish date"]].drop_duplicates()
    date_ranges["start_date"] = date_ranges["Basic start date"].dt.date
    date_ranges["end_date"] = date_ranges["Basic finish date"].dt.date
    
    # (start_date, end_date) 튜플 리스트로 반환
    ranges = [(row["start_date"], row["end_date"]) for _, row in date_ranges.iterrows()]
    ranges = sorted(set(ranges))  # 중복 제거 및 정렬
    
    return ranges