#!/usr/bin/env python3
"""
조제(compound) 관련 테이블 스키마 확인
"""

import sqlite3

def check_compound_tables():
    conn = sqlite3.connect('database/kdrug.db')
    cursor = conn.cursor()

    # compounds 테이블 구조 확인
    print("=" * 80)
    print("1. COMPOUNDS 테이블 구조:")
    print("=" * 80)
    cursor.execute("PRAGMA table_info(compounds)")
    columns = cursor.fetchall()
    for col in columns:
        print(f"  {col[1]:25s} {col[2]:15s} {'NOT NULL' if col[3] else 'NULL':10s} {col[4] or ''}")

    # compound_items 테이블 구조 확인
    print("\n" + "=" * 80)
    print("2. COMPOUND_ITEMS 테이블 구조:")
    print("=" * 80)
    cursor.execute("PRAGMA table_info(compound_items)")
    columns = cursor.fetchall()
    for col in columns:
        print(f"  {col[1]:25s} {col[2]:15s} {'NOT NULL' if col[3] else 'NULL':10s} {col[4] or ''}")

    # 관련 테이블 확인
    print("\n" + "=" * 80)
    print("3. 관련 테이블 목록:")
    print("=" * 80)
    cursor.execute("""
        SELECT name FROM sqlite_master
        WHERE type='table' AND (name LIKE '%sale%' OR name LIKE '%payment%' OR name LIKE '%invoice%')
        ORDER BY name
    """)
    related_tables = cursor.fetchall()
    if related_tables:
        for table in related_tables:
            print(f"  - {table[0]}")
    else:
        print("  판매/결제 관련 테이블 없음")

    # 샘플 데이터 확인
    print("\n" + "=" * 80)
    print("4. 최근 조제 데이터 샘플:")
    print("=" * 80)
    cursor.execute("""
        SELECT c.compound_id, p.name, f.formula_name, c.compound_date, c.created_at, c.status
        FROM compounds c
        LEFT JOIN patients p ON c.patient_id = p.patient_id
        LEFT JOIN formulas f ON c.formula_id = f.formula_id
        ORDER BY c.created_at DESC
        LIMIT 5
    """)
    compounds = cursor.fetchall()
    for comp in compounds:
        print(f"  ID:{comp[0]} | 환자:{comp[1]} | 처방:{comp[2]} | 조제일:{comp[3]} | 상태:{comp[5]}")

    conn.close()

if __name__ == "__main__":
    check_compound_tables()