#!/usr/bin/env python3
"""
판매 관리 시스템 Phase 1 - compounds 테이블에 판매 관련 컬럼 추가
"""

import sqlite3
from datetime import datetime

def add_sales_columns():
    """compounds 테이블에 판매 관련 컬럼 추가"""
    conn = sqlite3.connect('database/kdrug.db')
    cursor = conn.cursor()

    try:
        # 1. payment_method 컬럼 추가
        cursor.execute("""
            ALTER TABLE compounds
            ADD COLUMN payment_method TEXT
        """)
        print("✓ payment_method 컬럼 추가 완료")
    except sqlite3.OperationalError as e:
        if "duplicate column name" in str(e):
            print("- payment_method 컬럼 이미 존재")
        else:
            raise

    try:
        # 2. payment_date 컬럼 추가
        cursor.execute("""
            ALTER TABLE compounds
            ADD COLUMN payment_date DATETIME
        """)
        print("✓ payment_date 컬럼 추가 완료")
    except sqlite3.OperationalError as e:
        if "duplicate column name" in str(e):
            print("- payment_date 컬럼 이미 존재")
        else:
            raise

    try:
        # 3. discount_rate 컬럼 추가
        cursor.execute("""
            ALTER TABLE compounds
            ADD COLUMN discount_rate REAL DEFAULT 0
        """)
        print("✓ discount_rate 컬럼 추가 완료")
    except sqlite3.OperationalError as e:
        if "duplicate column name" in str(e):
            print("- discount_rate 컬럼 이미 존재")
        else:
            raise

    try:
        # 4. discount_reason 컬럼 추가
        cursor.execute("""
            ALTER TABLE compounds
            ADD COLUMN discount_reason TEXT
        """)
        print("✓ discount_reason 컬럼 추가 완료")
    except sqlite3.OperationalError as e:
        if "duplicate column name" in str(e):
            print("- discount_reason 컬럼 이미 존재")
        else:
            raise

    try:
        # 5. delivery_method 컬럼 추가
        cursor.execute("""
            ALTER TABLE compounds
            ADD COLUMN delivery_method TEXT
        """)
        print("✓ delivery_method 컬럼 추가 완료")
    except sqlite3.OperationalError as e:
        if "duplicate column name" in str(e):
            print("- delivery_method 컬럼 이미 존재")
        else:
            raise

    try:
        # 6. delivery_date 컬럼 추가
        cursor.execute("""
            ALTER TABLE compounds
            ADD COLUMN delivery_date DATETIME
        """)
        print("✓ delivery_date 컬럼 추가 완료")
    except sqlite3.OperationalError as e:
        if "duplicate column name" in str(e):
            print("- delivery_date 컬럼 이미 존재")
        else:
            raise

    try:
        # 7. invoice_number 컬럼 추가
        cursor.execute("""
            ALTER TABLE compounds
            ADD COLUMN invoice_number TEXT
        """)
        print("✓ invoice_number 컬럼 추가 완료")
    except sqlite3.OperationalError as e:
        if "duplicate column name" in str(e):
            print("- invoice_number 컬럼 이미 존재")
        else:
            raise

    try:
        # 8. actual_payment_amount 컬럼 추가 (실제 결제 금액)
        cursor.execute("""
            ALTER TABLE compounds
            ADD COLUMN actual_payment_amount REAL
        """)
        print("✓ actual_payment_amount 컬럼 추가 완료")
    except sqlite3.OperationalError as e:
        if "duplicate column name" in str(e):
            print("- actual_payment_amount 컬럼 이미 존재")
        else:
            raise

    conn.commit()
    print("\n판매 관련 컬럼 추가 완료!")

    # 현재 compounds 테이블 구조 확인
    cursor.execute("PRAGMA table_info(compounds)")
    columns = cursor.fetchall()
    print("\n현재 compounds 테이블 구조:")
    for col in columns:
        print(f"  - {col[1]}: {col[2]}")

    conn.close()

if __name__ == "__main__":
    add_sales_columns()