database.py

import sqlite3
import os

DATABASE_PATH = os.path.join(os.path.dirname(__file__), "contacts.db")

def get_db_connection():
    """Establishes a connection to the SQLite database with row factory enabled."""
    conn = sqlite3.connect(DATABASE_PATH)
    conn.row_factory = sqlite3.Row
    return conn

def init_db():
    """Initializes the database schema and seeds sample contacts if empty."""
    conn = get_db_connection()
    cursor = conn.cursor()
    
    # Create the contacts table
    cursor.execute("""
        CREATE TABLE IF NOT EXISTS contacts (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            name TEXT NOT NULL,
            email TEXT,
            phone TEXT,
            company TEXT,
            category TEXT,
            notes TEXT,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP
        )
    """)
    
    # Check if table is empty
    cursor.execute("SELECT COUNT(*) FROM contacts")
    count = cursor.fetchone()[0]
    
    if count == 0:
        # Seed premium dummy contacts
        sample_contacts = [
            ("Elon Musk", "elon@tesla.com", "+1-555-0199", "Tesla", "Work", "Enjoys rockets and electric vehicles."),
            ("Taylor Swift", "taylor@swift.music", "+1-555-1989", "Republic Records", "Personal", "Talented singer-songwriter."),
            ("Ada Lovelace", "ada@analyticalengine.org", "+44-1815-1210", "Analytical Inc.", "Favorites", "The world's first computer programmer."),
            ("Sherlock Holmes", "sherlock@bakerstreet.com", "+44-20-7946-0958", "Consulting Detective", "Favorites", "Elementary, my dear Watson."),
            ("Marie Curie", "marie.curie@sorbonne.fr", "+33-1-4046-2015", "Radium Institute", "Work", "Double Nobel laureate in Physics and Chemistry.")
        ]
        cursor.executemany("""
            INSERT INTO contacts (name, email, phone, company, category, notes)
            VALUES (?, ?, ?, ?, ?, ?)
        """, sample_contacts)
        conn.commit()
        
    conn.close()

def get_contacts(query=None, category=None):
    """Fetches contacts filtered by an optional search query and category."""
    conn = get_db_connection()
    cursor = conn.cursor()
    
    sql = "SELECT * FROM contacts WHERE 1=1"
    params = []
    
    if category and category.lower() != "all":
        sql += " AND LOWER(category) = ?"
        params.append(category.lower())
        
    if query:
        search_query = f"%{query}%"
        sql += """ AND (
            LOWER(name) LIKE ? OR 
            LOWER(email) LIKE ? OR 
            LOWER(phone) LIKE ? OR 
            LOWER(company) LIKE ? OR 
            LOWER(notes) LIKE ?
        )"""
        params.extend([search_query.lower()] * 5)
        
    sql += " ORDER BY name ASC"
    
    cursor.execute(sql, params)
    rows = cursor.fetchall()
    contacts = [dict(row) for row in rows]
    conn.close()
    return contacts

def get_contact(contact_id):
    """Fetches a single contact by its ID."""
    conn = get_db_connection()
    cursor = conn.cursor()
    cursor.execute("SELECT * FROM contacts WHERE id = ?", (contact_id,))
    row = cursor.fetchone()
    conn.close()
    return dict(row) if row else None

def create_contact(name, email, phone, company, category, notes):
    """Inserts a new contact into the database."""
    conn = get_db_connection()
    cursor = conn.cursor()
    cursor.execute("""
        INSERT INTO contacts (name, email, phone, company, category, notes)
        VALUES (?, ?, ?, ?, ?, ?)
    """, (name, email, phone, company, category, notes))
    conn.commit()
    new_id = cursor.lastrowid
    conn.close()
    return new_id

def update_contact(contact_id, name, email, phone, company, category, notes):
    """Updates an existing contact's details."""
    conn = get_db_connection()
    cursor = conn.cursor()
    cursor.execute("""
        UPDATE contacts
        SET name = ?, email = ?, phone = ?, company = ?, category = ?, notes = ?
        WHERE id = ?
    """, (name, email, phone, company, category, notes, contact_id))
    conn.commit()
    success = cursor.rowcount > 0
    conn.close()
    return success

def delete_contact(contact_id):
    """Deletes a contact from the database."""
    conn = get_db_connection()
    cursor = conn.cursor()
    cursor.execute("DELETE FROM contacts WHERE id = ?", (contact_id,))
    conn.commit()
    success = cursor.rowcount > 0
    conn.close()
    return success

def import_contacts_from_list(contacts_list):
    """Inserts multiple contacts in a single transaction."""
    if not contacts_list:
        return 0
    conn = get_db_connection()
    cursor = conn.cursor()
    try:
        cursor.executemany("""
            INSERT INTO contacts (name, email, phone, company, category, notes)
            VALUES (?, ?, ?, ?, ?, ?)
        """, [
            (c.get('name'), c.get('email'), c.get('phone'), c.get('company'), c.get('category', 'Personal'), c.get('notes'))
            for c in contacts_list
        ])
        conn.commit()
        count = cursor.rowcount
        conn.close()
        return count
    except Exception as e:
        conn.rollback()
        conn.close()
        raise e