database.py

import sqlite3
import datetime
import os

# --- CONFIGURATION ---
# We use a NEW filename to avoid confusion with old files
DB_NAME = 'pcb_app_data.db' 

# Use absolute path so python always finds it
BASE_DIR = os.path.dirname(os.path.abspath(__file__))
DB_PATH = os.path.join(BASE_DIR, DB_NAME)

def get_db_connection():
    conn = sqlite3.connect(DB_PATH)
    conn.row_factory = sqlite3.Row
    return conn

def init_db():
    """Initializes the database if it doesn't exist."""
    print(f"🔌 Checking Database at: {DB_PATH}")
    
    conn = get_db_connection()
    cursor = conn.cursor()
    
    # Create the single table we need
    cursor.execute('''
        CREATE TABLE IF NOT EXISTS scans (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            filename TEXT NOT NULL,
            status TEXT NOT NULL,
            timestamp DATETIME DEFAULT CURRENT_TIMESTAMP,
            defects_data TEXT,
            confidence REAL,
            defects_count INTEGER
        )
    ''')
    conn.commit()
    conn.close()
    print("✅ Database initialized successfully.")

def save_scan(filename, status, defects_list, max_conf):
    """Saves a new scan result."""
    try:
        conn = get_db_connection()
        cursor = conn.cursor()
        
        defects_str = str(defects_list) 
        defects_count = len(defects_list) if defects_list else 0

        cursor.execute('''
            INSERT INTO scans (filename, status, defects_data, confidence, defects_count)
            VALUES (?, ?, ?, ?, ?)
        ''', (filename, status, defects_str, max_conf, defects_count))
        
        conn.commit()
        conn.close()
    except Exception as e:
        print(f"❌ Database Save Error: {e}")

def get_all_scans():
    """Fetches all history records."""
    try:
        conn = get_db_connection()
        cursor = conn.cursor()
        cursor.execute("SELECT * FROM scans ORDER BY id DESC")
        scans = cursor.fetchall()
        conn.close()
        return scans
    except Exception as e:
        print(f"❌ Database Read Error: {e}")
        return []

def clear_all_scans():
    """Wipes the database cleanly."""
    try:
        conn = get_db_connection()
        cursor = conn.cursor()
        cursor.execute("DELETE FROM scans")
        conn.commit()
        conn.close()
        print(f"🗑️ CLEARED database at {DB_PATH}")
        return True
    except Exception as e:
        print(f"❌ Database Delete Error: {e}")
        return False

# Run initialization immediately when imported
init_db()