#!/usr/bin/env python3
"""
판매 관리 시스템 - 신규 테이블 생성
"""

import sqlite3
from datetime import datetime

def create_sales_tables():
    """판매 관련 신규 테이블 생성"""
    conn = sqlite3.connect('database/kdrug.db')
    cursor = conn.cursor()

    # 1. sales_transactions 테이블 생성
    cursor.execute("""
        CREATE TABLE IF NOT EXISTS sales_transactions (
            transaction_id INTEGER PRIMARY KEY AUTOINCREMENT,
            compound_id INTEGER REFERENCES compounds(compound_id),
            transaction_date DATETIME NOT NULL,
            transaction_type TEXT NOT NULL, -- SALE, REFUND, CANCEL
            amount REAL NOT NULL,
            payment_method TEXT,
            payment_status TEXT, -- PENDING, COMPLETED, FAILED
            notes TEXT,
            created_by TEXT,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP
        )
    """)
    print("✓ sales_transactions 테이블 생성 완료")

    # 2. price_policies 테이블 생성
    cursor.execute("""
        CREATE TABLE IF NOT EXISTS price_policies (
            policy_id INTEGER PRIMARY KEY AUTOINCREMENT,
            formula_id INTEGER REFERENCES formulas(formula_id),
            base_price REAL NOT NULL,
            dispensing_fee REAL DEFAULT 0,
            is_active BOOLEAN DEFAULT 1,
            effective_date DATE,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
        )
    """)
    print("✓ price_policies 테이블 생성 완료")

    # 3. sales_status_history 테이블 생성
    cursor.execute("""
        CREATE TABLE IF NOT EXISTS sales_status_history (
            history_id INTEGER PRIMARY KEY AUTOINCREMENT,
            compound_id INTEGER REFERENCES compounds(compound_id),
            old_status TEXT,
            new_status TEXT NOT NULL,
            changed_by TEXT,
            change_reason TEXT,
            changed_at DATETIME DEFAULT CURRENT_TIMESTAMP
        )
    """)
    print("✓ sales_status_history 테이블 생성 완료")

    # 인덱스 생성
    cursor.execute("""
        CREATE INDEX IF NOT EXISTS idx_sales_transactions_compound
        ON sales_transactions(compound_id)
    """)

    cursor.execute("""
        CREATE INDEX IF NOT EXISTS idx_price_policies_formula
        ON price_policies(formula_id)
    """)

    cursor.execute("""
        CREATE INDEX IF NOT EXISTS idx_sales_status_history_compound
        ON sales_status_history(compound_id)
    """)
    print("✓ 인덱스 생성 완료")

    conn.commit()

    # 생성된 테이블 확인
    cursor.execute("""
        SELECT name FROM sqlite_master
        WHERE type='table'
        AND name IN ('sales_transactions', 'price_policies', 'sales_status_history')
    """)
    tables = cursor.fetchall()
    print("\n생성된 테이블:")
    for table in tables:
        print(f"  - {table[0]}")

    conn.close()
    print("\n판매 관련 테이블 생성 완료!")

if __name__ == "__main__":
    create_sales_tables()