import sqlite3
from datetime import datetime

DB_PATH = "auction.db"

def get_connection():
    return sqlite3.connect(DB_PATH)

def save_target(item_data):
    """
    관심 물건 저장 (items 테이블 + saved_targets 테이블)
    """
    conn = get_connection()
    cursor = conn.cursor()
    
    try:
        # 1. items 테이블에 기본 정보 저장 (없으면 넣고, 있으면 무시)
        cursor.execute('''
            INSERT OR IGNORE INTO items (auction_id, link, first_seen_at)
            VALUES (?, ?, ?)
        ''', (item_data['auction_id'], item_data['link'], datetime.now()))
        
        # 2. saved_targets 테이블에 추가
        cursor.execute('''
            INSERT INTO saved_targets (auction_id, link, status, created_at)
            VALUES (?, ?, ?, ?)
        ''', (item_data['auction_id'], item_data['link'], '관심등록', datetime.now()))
        
        conn.commit()
        return True
    except Exception as e:
        print(f"DB 저장 에러: {e}")
        return False
    finally:
        conn.close()

def get_saved_list():
    """
    저장된 물건 리스트 가져오기
    """
    conn = get_connection()
    cursor = conn.cursor()
    cursor.execute("SELECT id, auction_id, link, status, created_at FROM saved_targets ORDER BY id DESC")
    rows = cursor.fetchall()
    conn.close()
    return rows