#!/usr/bin/env python3
# -*- coding: utf-8 -*-

import sqlite3

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

print("=== purchase_receipts 테이블 구조 ===")
cursor.execute("PRAGMA table_info(purchase_receipts)")
columns = cursor.fetchall()
for col in columns:
    print(f"  {col[1]}: {col[2]}")

print("\n=== purchase_receipt_lines 테이블 구조 ===")
cursor.execute("PRAGMA table_info(purchase_receipt_lines)")
columns = cursor.fetchall()
for col in columns:
    print(f"  {col[1]}: {col[2]}")

print("\n=== 입고장 데이터 샘플 ===")
cursor.execute("""
    SELECT pr.receipt_id, pr.receipt_number, pr.receipt_date,
           COUNT(prl.line_id) as line_count,
           SUM(prl.quantity_g) as total_quantity,
           SUM(prl.total_price) as total_amount
    FROM purchase_receipts pr
    LEFT JOIN purchase_receipt_lines prl ON pr.receipt_id = prl.receipt_id
    GROUP BY pr.receipt_id
    LIMIT 5
""")
rows = cursor.fetchall()
for row in rows:
    print(f"  입고장 {row[0]}: {row[1]} ({row[2]})")
    print(f"    - 항목수: {row[3]}개, 총량: {row[4]}g, 총액: ₩{row[5]:,.0f}")

print("\n=== inventory_lots의 receipt_line_id 연결 확인 ===")
cursor.execute("""
    SELECT
        COUNT(*) as total_lots,
        SUM(CASE WHEN receipt_line_id IS NOT NULL THEN 1 ELSE 0 END) as matched_lots,
        SUM(CASE WHEN receipt_line_id IS NULL THEN 1 ELSE 0 END) as unmatched_lots
    FROM inventory_lots
    WHERE is_depleted = 0
""")
result = cursor.fetchone()
print(f"  전체 LOT: {result[0]}개")
print(f"  입고장 연결된 LOT: {result[1]}개")
print(f"  입고장 연결 안된 LOT: {result[2]}개")

conn.close()