catalog_data.py

import os
import sys
import sqlite3
import pandas as pd
import uuid
from datetime import datetime
sys.path.append(os.path.abspath(os.path.join(os.path.dirname(__file__), "..")))
from utils import resource_path, app_root, ensure_dir
from utils import logger

DB_FILE = os.path.join(app_root(), "data", "mycology.db")
ensure_dir(os.path.dirname(DB_FILE))
logger.info(f"[catalog_data] Using database: {DB_FILE}")


def init_db():
    conn = sqlite3.connect(DB_FILE)
    cursor = conn.cursor()

    # Ensure the table exists with the correct schema
    cursor.execute("""
        CREATE TABLE IF NOT EXISTS specimens (
            id TEXT PRIMARY KEY,
            date TEXT,
            state TEXT,
            county TEXT,
            city TEXT,
            site_name TEXT,
            species TEXT,
            field_photo TEXT,
            nearby_trees TEXT,
            substrate TEXT,
            habit TEXT,
            odor TEXT,
            taste TEXT,
            notes TEXT DEFAULT '',
            inat_id TEXT,
            collected_by TEXT
        )
    """)

       # If the table exists but is missing columns, try to add them
    cursor.execute("PRAGMA table_info(specimens)")
    columns = [row[1] for row in cursor.fetchall()]
    if "inat_id" not in columns:
        cursor.execute("ALTER TABLE specimens ADD COLUMN inat_id TEXT")
    if "collected_by" not in columns:
        cursor.execute("ALTER TABLE specimens ADD COLUMN collected_by TEXT")
    if "notes" not in columns:
        cursor.execute("ALTER TABLE specimens ADD COLUMN notes TEXT DEFAULT ''")

    conn.commit()
    conn.close()

def get_next_unique_id(prefix="NTMA"):
    """Generate a unique ID using UUID to prevent collisions"""
    # If prefix is empty or None, use default
    if not prefix:
        prefix = "NTMA"
    return f"{prefix}{uuid.uuid4().hex[:8].upper()}"

def insert_specimen(id, date, state, county, city, site_name,
                    species, field_photo, nearby_trees, substrate,
                    habit, odor, taste, notes="", inat_id="", collected_by=""):

    conn = sqlite3.connect(DB_FILE)
    cursor = conn.cursor()
    cursor.execute("""
        INSERT INTO specimens (
            id, date, state, county, city, site_name,
            species, field_photo, nearby_trees, substrate,
            habit, odor, taste, notes, inat_id, collected_by
        ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
    """, (
        id, date, state, county, city, site_name,
        species, field_photo, nearby_trees, substrate,
        habit, odor, taste, notes, inat_id, collected_by
    ))

    conn.commit()
    conn.close()

def export_to_excel():
    conn = sqlite3.connect(DB_FILE)
    df = pd.read_sql_query("SELECT * FROM specimens", conn)
    timestamp = datetime.now().strftime("%Y%m%d_%H%M%S")
    filename = os.path.join(app_root(), "data", f"specimens_export_{timestamp}.xlsx")
    df.to_excel(filename, index=False)
    conn.close()
    return filename