master_tree_database.py

#!/usr/bin/env python3
"""
Master Tree Database for TreeTrack
Pre-loaded species data for auto-suggestions and completions
"""

import sqlite3
import json
from pathlib import Path
from datetime import datetime
import logging

logger = logging.getLogger(__name__)

# Master tree species data from Tezpur research team
MASTER_TREE_DATA = [
    {"id": 1, "local_name": "", "scientific_name": "Abroma augusta", "fruiting_season": "", "tree_code": "AA"},
    {"id": 2, "local_name": "", "scientific_name": "Abrus precatorius", "fruiting_season": "", "tree_code": "AP"},
    {"id": 3, "local_name": "Khair", "scientific_name": "Acacia catechu", "fruiting_season": "", "tree_code": "AC"},
    {"id": 4, "local_name": "Jungli chandan", "scientific_name": "Adenanthera pavonina", "fruiting_season": "March to august", "tree_code": "AP2"},
    {"id": 5, "local_name": "", "scientific_name": "Aeschynomene americanum", "fruiting_season": "", "tree_code": "AA2"},
    {"id": 6, "local_name": "", "scientific_name": "Ageratum conyzoides", "fruiting_season": "", "tree_code": "AC2"},
    {"id": 7, "local_name": "Amari", "scientific_name": "Aglaia spectabilis", "fruiting_season": "May to august", "tree_code": "AS"},
    {"id": 8, "local_name": "Borpat", "scientific_name": "Ailanthus integrifolia", "fruiting_season": "October to april", "tree_code": "AI"},
    {"id": 9, "local_name": "Sika moroliya", "scientific_name": "Alangium chinense", "fruiting_season": "July to november", "tree_code": "AC3"},
    {"id": 10, "local_name": "Siris", "scientific_name": "Albizia", "fruiting_season": "", "tree_code": "AA3"},
    {"id": 11, "local_name": "Koroi", "scientific_name": "Albizia procera", "fruiting_season": "December to may", "tree_code": "AP3"},
    {"id": 12, "local_name": "Boga siris", "scientific_name": "Albizia procera", "fruiting_season": "", "tree_code": "AP4"},
    {"id": 13, "local_name": "", "scientific_name": "Albizzia odoratissima", "fruiting_season": "", "tree_code": "AO"},
    {"id": 14, "local_name": "Chatiana", "scientific_name": "Alstonia scholaris", "fruiting_season": "January to February", "tree_code": "AS2"},
    {"id": 15, "local_name": "Kali gos", "scientific_name": "Altingia excelsa", "fruiting_season": "", "tree_code": "AE"},
    {"id": 16, "local_name": "Agar", "scientific_name": "Aquilaria malaccensis", "fruiting_season": "April to september", "tree_code": "AM"},
    {"id": 17, "local_name": "Bachahu", "scientific_name": "Archidendron bigeminum", "fruiting_season": "April to May", "tree_code": "AB"},
    {"id": 18, "local_name": "Shamkathal", "scientific_name": "Artocarpus chaplasha", "fruiting_season": "March to July", "tree_code": "AC4"},
    {"id": 19, "local_name": "Neem", "scientific_name": "Azadirachta indica", "fruiting_season": "", "tree_code": "AI2"},
    {"id": 20, "local_name": "Leteku", "scientific_name": "Baccaurea ramiflora", "fruiting_season": "May to August", "tree_code": "BR"},
    {"id": 21, "local_name": "Kanchan", "scientific_name": "Bauhinia purpurea", "fruiting_season": "January to March", "tree_code": "BP"},
    {"id": 22, "local_name": "No local name in use", "scientific_name": "Beilschmiedia assamica", "fruiting_season": "", "tree_code": "BA"},
    {"id": 23, "local_name": "", "scientific_name": "Bidens sp.", "fruiting_season": "", "tree_code": "BS"},
    {"id": 24, "local_name": "Mebu/Uriam", "scientific_name": "Bischofia javanica", "fruiting_season": "September to January", "tree_code": "BJ"},
    {"id": 25, "local_name": "Semal", "scientific_name": "Bombax ceiba", "fruiting_season": "", "tree_code": "BC"},
    {"id": 26, "local_name": "Kuhir", "scientific_name": "Bridelia retusa", "fruiting_season": "October to January", "tree_code": "BR2"},
    {"id": 27, "local_name": "", "scientific_name": "Bridelia tomentosa", "fruiting_season": "", "tree_code": "BT"},
    {"id": 28, "local_name": "Paper Mulberry", "scientific_name": "Broussonetia papyrifera", "fruiting_season": "", "tree_code": "BP2"},
    {"id": 29, "local_name": "", "scientific_name": "Caesalpinia mimosoides", "fruiting_season": "", "tree_code": "CM"},
    {"id": 30, "local_name": "Dhuna", "scientific_name": "Canarium strictum", "fruiting_season": "October to February", "tree_code": "CS"},
    {"id": 31, "local_name": "Sonaru", "scientific_name": "Cassia fistula", "fruiting_season": "", "tree_code": "CF"},
    {"id": 32, "local_name": "Hingori", "scientific_name": "Castanopsis indica", "fruiting_season": "August to November", "tree_code": "CI"},
    {"id": 33, "local_name": "", "scientific_name": "Cayratia sp.", "fruiting_season": "", "tree_code": "CS2"},
    {"id": 34, "local_name": "Banderdima", "scientific_name": "Chisocheton cumingianus/Dysoxylum gotadhora", "fruiting_season": "March to July", "tree_code": "CC"},
    {"id": 35, "local_name": "Lepchipoma", "scientific_name": "Choerospondias axillaris", "fruiting_season": "July to October", "tree_code": "CA"},
    {"id": 36, "local_name": "Bogipoma", "scientific_name": "Chukrasia tabularis", "fruiting_season": "October to February", "tree_code": "CT"},
    {"id": 37, "local_name": "Gonsorai", "scientific_name": "Cinnamomum glaucescens", "fruiting_season": "", "tree_code": "CG"},
    {"id": 38, "local_name": "Dalchini", "scientific_name": "Cinnamomum zeylanicum", "fruiting_season": "", "tree_code": "CZ"},
    {"id": 39, "local_name": "Apung?", "scientific_name": "Clerodendrum", "fruiting_season": "", "tree_code": "CC2"},
    {"id": 40, "local_name": "", "scientific_name": "Corchorus capsularis", "fruiting_season": "", "tree_code": "CC3"},
    {"id": 41, "local_name": "Barun", "scientific_name": "Crataeva religiosa", "fruiting_season": "November to December", "tree_code": "CR"},
    {"id": 42, "local_name": "Ranlung-damdawi", "scientific_name": "Croton caudatus", "fruiting_season": "May to march", "tree_code": "CC4"},
    {"id": 43, "local_name": "", "scientific_name": "Croton sp.", "fruiting_season": "", "tree_code": "CS3"},
    {"id": 44, "local_name": "Sissoo", "scientific_name": "Dalbergia sissoo", "fruiting_season": "", "tree_code": "DS"},
    {"id": 45, "local_name": "", "scientific_name": "Derris scandens", "fruiting_season": "", "tree_code": "DS2"},
    {"id": 46, "local_name": "", "scientific_name": "Derris sp.", "fruiting_season": "", "tree_code": "DS3"},
    {"id": 47, "local_name": "", "scientific_name": "Desmodium triangulare", "fruiting_season": "", "tree_code": "DT"},
    {"id": 48, "local_name": "", "scientific_name": "Desmodium triflorum", "fruiting_season": "", "tree_code": "DT2"},
    {"id": 49, "local_name": "Outenga", "scientific_name": "Dillenia indica", "fruiting_season": "November to April", "tree_code": "DI"},
    {"id": 50, "local_name": "", "scientific_name": "Diospyros sp.", "fruiting_season": "", "tree_code": "DS4"},
    {"id": 51, "local_name": "Khokun", "scientific_name": "Duabanga grandiflora", "fruiting_season": "April to May", "tree_code": "DG"},
    {"id": 52, "local_name": "Banderdima", "scientific_name": "Dysoxylum gotadhora", "fruiting_season": "", "tree_code": "DG2"},
    {"id": 53, "local_name": "Gandhelipoma", "scientific_name": "Dysoxylum mollissimum", "fruiting_season": "January to March", "tree_code": "DM"},
    {"id": 54, "local_name": "Amselleng", "scientific_name": "Dysoxylum procerum", "fruiting_season": "November to April", "tree_code": "DP"},
    {"id": 55, "local_name": "Rudraksh", "scientific_name": "Elaeocarpus sphaericus", "fruiting_season": "October to November", "tree_code": "ES"},
    {"id": 56, "local_name": "Amloki", "scientific_name": "Embelica officinalis", "fruiting_season": "", "tree_code": "EO"},
    {"id": 57, "local_name": "Phulgamari", "scientific_name": "Endospermum chinense", "fruiting_season": "August to November", "tree_code": "EC"},
    {"id": 58, "local_name": "", "scientific_name": "Eupatorium odoratum", "fruiting_season": "", "tree_code": "EO2"},
    {"id": 59, "local_name": "", "scientific_name": "Evolvulus numularias", "fruiting_season": "", "tree_code": "EN"},
    {"id": 60, "local_name": "Borgos", "scientific_name": "Ficus benghalensis", "fruiting_season": "", "tree_code": "FB"},
    {"id": 61, "local_name": "Dabor", "scientific_name": "Ficus drupacea/ Ficus curtipes", "fruiting_season": "August and january", "tree_code": "FD"},
    {"id": 62, "local_name": "Dimaru", "scientific_name": "Ficus glomerata", "fruiting_season": "", "tree_code": "FG"},
    {"id": 63, "local_name": "", "scientific_name": "Ficus hispida", "fruiting_season": "", "tree_code": "FH"},
    {"id": 64, "local_name": "Chepani-dimoru", "scientific_name": "Ficus Nervosa", "fruiting_season": "March to April", "tree_code": "FN"},
    {"id": 65, "local_name": "Mota-bokol-bih", "scientific_name": "Friesodielsia fornicata", "fruiting_season": "October to December", "tree_code": "FF"},
    {"id": 66, "local_name": "Bor thekera", "scientific_name": "Garcinia pedunculata", "fruiting_season": "", "tree_code": "GP"},
    {"id": 67, "local_name": "Kechkechipoma", "scientific_name": "Garuga floribunda", "fruiting_season": "", "tree_code": "GF"},
    {"id": 68, "local_name": "", "scientific_name": "Gliricidia sepium", "fruiting_season": "", "tree_code": "GS"},
    {"id": 69, "local_name": "", "scientific_name": "Glochidion assamensis", "fruiting_season": "", "tree_code": "GA"},
    {"id": 70, "local_name": "Gamari", "scientific_name": "Gmelina arborea", "fruiting_season": "May to September", "tree_code": "GA2"},
    {"id": 71, "local_name": "", "scientific_name": "Grewia sp.", "fruiting_season": "", "tree_code": "GS2"},
    {"id": 72, "local_name": "Gaikhure/Korondo/Keseru", "scientific_name": "Heteropanax fragrans", "fruiting_season": "January to March", "tree_code": "HF"},
    {"id": 73, "local_name": "", "scientific_name": "Hibiscus subdariffa", "fruiting_season": "", "tree_code": "HS"},
    {"id": 74, "local_name": "", "scientific_name": "Hyptis suaveolens", "fruiting_season": "", "tree_code": "HS2"},
    {"id": 75, "local_name": "Pisola", "scientific_name": "Kydia calycina", "fruiting_season": "December to January", "tree_code": "KC"},
    {"id": 76, "local_name": "Ajar", "scientific_name": "Lagerstroemia speciosa", "fruiting_season": "", "tree_code": "LS"},
    {"id": 77, "local_name": "Jia poma", "scientific_name": "Lannea coromandelica", "fruiting_season": "", "tree_code": "LC"},
    {"id": 78, "local_name": "", "scientific_name": "Lantana camara", "fruiting_season": "", "tree_code": "LC2"},
    {"id": 79, "local_name": "Bon gudhi", "scientific_name": "Lepisanthes senegalensis", "fruiting_season": "February to May", "tree_code": "LS2"},
    {"id": 80, "local_name": "Jutuli", "scientific_name": "Liquidambar excelsa", "fruiting_season": "August to November", "tree_code": "LE"},
    {"id": 81, "local_name": "Kaunlo", "scientific_name": "Litsea glutinosa", "fruiting_season": "September to October", "tree_code": "LG"},
    {"id": 82, "local_name": "Baghnala", "scientific_name": "Litsea glutinosa", "fruiting_season": "", "tree_code": "LG2"},
    {"id": 83, "local_name": "Muga", "scientific_name": "Litsea monopetala", "fruiting_season": "June to August", "tree_code": "LM"},
    {"id": 84, "local_name": "", "scientific_name": "Litsea sp.", "fruiting_season": "", "tree_code": "LS3"},
    {"id": 85, "local_name": "Tokko", "scientific_name": "Livistona jenkinsiana", "fruiting_season": "September to February", "tree_code": "LJ"},
    {"id": 86, "local_name": "", "scientific_name": "Macaranga peltata", "fruiting_season": "", "tree_code": "MP"},
    {"id": 87, "local_name": "", "scientific_name": "Macclura cochinchinensis", "fruiting_season": "", "tree_code": "MC"},
    {"id": 88, "local_name": "", "scientific_name": "Maesa indica", "fruiting_season": "", "tree_code": "MI"},
    {"id": 89, "local_name": "Titachampa", "scientific_name": "Magnolia champaca", "fruiting_season": "Throughout the  year", "tree_code": "MC2"},
    {"id": 90, "local_name": "Boromthuri", "scientific_name": "Magnolia hodgsonii", "fruiting_season": "May to November", "tree_code": "MH"},
    {"id": 91, "local_name": "Borumthuri", "scientific_name": "Magnolia hodgsonii", "fruiting_season": "May to November", "tree_code": "MH2"},
    {"id": 92, "local_name": "Rohini phal", "scientific_name": "Mallotus philippensis", "fruiting_season": "November to May( Ripening )", "tree_code": "MP2"},
    {"id": 93, "local_name": "Ghora neem", "scientific_name": "Melia azederach", "fruiting_season": "", "tree_code": "MA"},
    {"id": 94, "local_name": "", "scientific_name": "Melia dubia", "fruiting_season": "", "tree_code": "MD"},
    {"id": 95, "local_name": "Sia nahar", "scientific_name": "Mesua assamica", "fruiting_season": "", "tree_code": "MA2"},
    {"id": 96, "local_name": "Nahar", "scientific_name": "Mesua ferrea", "fruiting_season": "March to October", "tree_code": "MF"},
    {"id": 97, "local_name": "Kotkora", "scientific_name": "Meyna spinosa", "fruiting_season": "November to December", "tree_code": "MS"},
    {"id": 98, "local_name": "Sutum tanyi", "scientific_name": "Micromelum", "fruiting_season": "July to September", "tree_code": "MM"},
    {"id": 99, "local_name": "Kari", "scientific_name": "Monoon simiarum", "fruiting_season": "Main fruiting peak- May to July; Minor peak-Dec to Feb", "tree_code": "MS2"},
    {"id": 100, "local_name": "", "scientific_name": "Morinda", "fruiting_season": "", "tree_code": "MM2"},
    {"id": 101, "local_name": "Kamini", "scientific_name": "Murraya paniculata", "fruiting_season": "August to January", "tree_code": "MP3"},
    {"id": 102, "local_name": "", "scientific_name": "Oroxylum indica", "fruiting_season": "", "tree_code": "OI"},
    {"id": 103, "local_name": "Totola", "scientific_name": "Oroxylum indicum", "fruiting_season": "October to December", "tree_code": "OI2"},
    {"id": 104, "local_name": "Manipuri sim", "scientific_name": "Parkia roxburghii", "fruiting_season": "", "tree_code": "PR"},
    {"id": 105, "local_name": "Bonsum", "scientific_name": "Phoebe sp.", "fruiting_season": "July to August", "tree_code": "PS"},
    {"id": 106, "local_name": "Kalakari", "scientific_name": "Picrasma javanica", "fruiting_season": "May to December", "tree_code": "PJ"},
    {"id": 107, "local_name": "", "scientific_name": "Premna benghalensis", "fruiting_season": "", "tree_code": "PB"},
    {"id": 108, "local_name": "Hatipaila", "scientific_name": "Pterospermum acerifolium", "fruiting_season": "May to November", "tree_code": "PA"},
    {"id": 109, "local_name": "Karibadam", "scientific_name": "Pterygota alata", "fruiting_season": "September to January", "tree_code": "PA2"},
    {"id": 110, "local_name": "", "scientific_name": "Rhynchostylis sp.", "fruiting_season": "", "tree_code": "RS"},
    {"id": 111, "local_name": "Agla bel/Biswal", "scientific_name": "Senegalia pennata", "fruiting_season": "October to January", "tree_code": "SP"},
    {"id": 112, "local_name": "", "scientific_name": "Senna hirsuta", "fruiting_season": "", "tree_code": "SH"},
    {"id": 113, "local_name": "", "scientific_name": "Senna tora", "fruiting_season": "", "tree_code": "ST"},
    {"id": 114, "local_name": "Sal", "scientific_name": "Shorea robusta", "fruiting_season": "", "tree_code": "SR"},
    {"id": 115, "local_name": "", "scientific_name": "Sida mysorensis", "fruiting_season": "", "tree_code": "SM"},
    {"id": 116, "local_name": "", "scientific_name": "Sida rhombifolia", "fruiting_season": "", "tree_code": "SR2"},
    {"id": 117, "local_name": "", "scientific_name": "Smilax sp.", "fruiting_season": "", "tree_code": "SS"},
    {"id": 118, "local_name": "Amora tenga", "scientific_name": "Spondias pinnata", "fruiting_season": "November to February", "tree_code": "SP2"},
    {"id": 119, "local_name": "", "scientific_name": "Stephania hernandifolia", "fruiting_season": "", "tree_code": "SH2"},
    {"id": 120, "local_name": "Udal", "scientific_name": "Sterculia villosa", "fruiting_season": "March to May", "tree_code": "SV"},
    {"id": 121, "local_name": "", "scientific_name": "Streblus asper", "fruiting_season": "", "tree_code": "SA"},
    {"id": 122, "local_name": "Jamun", "scientific_name": "Syzygium cumini", "fruiting_season": "May to July", "tree_code": "SC"},
    {"id": 123, "local_name": "Lohajam", "scientific_name": "Syzygium formosum", "fruiting_season": "May to July", "tree_code": "SF"},
    {"id": 124, "local_name": "Panijamun", "scientific_name": "Syzygium syzygioides", "fruiting_season": "", "tree_code": "SS2"},
    {"id": 125, "local_name": "Arjun", "scientific_name": "Terminalia arjuna", "fruiting_season": "", "tree_code": "TA"},
    {"id": 126, "local_name": "Baheda/Behera", "scientific_name": "Terminalia bellirica", "fruiting_season": "", "tree_code": "TB"},
    {"id": 127, "local_name": "Bhomora", "scientific_name": "Terminalia bellirica", "fruiting_season": "", "tree_code": "TB2"},
    {"id": 128, "local_name": "Hilika", "scientific_name": "Terminalia chebula", "fruiting_season": "November to March", "tree_code": "TC"},
    {"id": 129, "local_name": "Hollock", "scientific_name": "Terminalia myriocarpa", "fruiting_season": "October to January", "tree_code": "TM"},
    {"id": 130, "local_name": "Bhelu", "scientific_name": "Tetrameles nudiflora", "fruiting_season": "April to May", "tree_code": "TN"},
    {"id": 131, "local_name": "", "scientific_name": "Tetrastigma sp.", "fruiting_season": "", "tree_code": "TS"},
    {"id": 132, "local_name": "Kauri lota/kukua loti", "scientific_name": "Thunbergia grandiflora", "fruiting_season": "Cold Winter", "tree_code": "TG"},
    {"id": 133, "local_name": "Chikan/Jiban", "scientific_name": "Trema orientalis", "fruiting_season": "July to September", "tree_code": "TO"},
    {"id": 134, "local_name": "", "scientific_name": "Vigna sp.", "fruiting_season": "", "tree_code": "VS"},
    {"id": 135, "local_name": "Panchpatti/Khungsuman/Khong-sman-bol", "scientific_name": "Vitex quinata", "fruiting_season": "Ausgust to September", "tree_code": "VQ"},
    {"id": 136, "local_name": "Panchpatti", "scientific_name": "Vitex sp", "fruiting_season": "", "tree_code": "VS2"},
    {"id": 137, "local_name": "", "scientific_name": "Xylosoma longifolia", "fruiting_season": "", "tree_code": "XL"},
    {"id": 138, "local_name": "Bajrang chota jat", "scientific_name": "Zanthoxylum oxyphyllum", "fruiting_season": "", "tree_code": "ZO"},
    {"id": 139, "local_name": "Bajrang", "scientific_name": "Zanthoxylum rhetsa", "fruiting_season": "March to May", "tree_code": "ZR"},
    {"id": 140, "local_name": "", "scientific_name": "Ziziphus mauritiana (Most probably)", "fruiting_season": "", "tree_code": "ZM"},
    {"id": 141, "local_name": "Unk sapling", "scientific_name": "", "fruiting_season": "", "tree_code": ""},
    {"id": 142, "local_name": "Tengapat", "scientific_name": "", "fruiting_season": "", "tree_code": ""},
    {"id": 143, "local_name": "Maikikori", "scientific_name": "", "fruiting_season": "", "tree_code": ""},
    {"id": 144, "local_name": "Doot gos", "scientific_name": "", "fruiting_season": "", "tree_code": ""},
    {"id": 145, "local_name": "Lali poma", "scientific_name": "", "fruiting_season": "", "tree_code": ""},
    {"id": 146, "local_name": "Tinpatti", "scientific_name": "", "fruiting_season": "", "tree_code": ""},
]

def create_master_tree_database():
    """Initialize the master tree species database"""
    db_path = Path("data/master_trees.db")
    
    try:
        conn = sqlite3.connect(db_path)
        cursor = conn.cursor()
        
        # Create master species table
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS master_species (
                id INTEGER PRIMARY KEY,
                local_name TEXT,
                scientific_name TEXT,
                fruiting_season TEXT,
                tree_code TEXT UNIQUE,
                created_at DATETIME DEFAULT CURRENT_TIMESTAMP
            )
        ''')
        
        # Create indexes for fast searches
        cursor.execute('CREATE INDEX IF NOT EXISTS idx_local_name ON master_species(local_name)')
        cursor.execute('CREATE INDEX IF NOT EXISTS idx_scientific_name ON master_species(scientific_name)')
        cursor.execute('CREATE INDEX IF NOT EXISTS idx_tree_code ON master_species(tree_code)')
        
        # Insert master data (only if table is empty)
        cursor.execute("SELECT COUNT(*) FROM master_species")
        if cursor.fetchone()[0] == 0:
            insert_query = '''
                INSERT OR IGNORE INTO master_species (local_name, scientific_name, fruiting_season, tree_code)
                VALUES (?, ?, ?, ?)
            '''
            
            inserted_count = 0
            for tree in MASTER_TREE_DATA:
                # Skip entries with empty tree codes to avoid conflicts
                tree_code = tree.get('tree_code', '').strip()
                if not tree_code:  # Skip empty codes
                    tree_code = None
                    
                cursor.execute(insert_query, (
                    tree['local_name'],
                    tree['scientific_name'], 
                    tree['fruiting_season'],
                    tree_code
                ))
                if cursor.rowcount > 0:
                    inserted_count += 1
            
            logger.info(f"Master tree database created with {inserted_count} species")
        
        conn.commit()
        conn.close()
        return True
        
    except Exception as e:
        logger.error(f"Failed to create master tree database: {e}")
        return False

def get_tree_suggestions(query: str, limit: int = 10):
    """Get auto-suggestions for tree names based on query"""
    db_path = Path("data/master_trees.db")
    
    if not db_path.exists():
        create_master_tree_database()
    
    try:
        conn = sqlite3.connect(db_path)
        cursor = conn.cursor()
        
        # Search in both local names and scientific names
        search_query = f"%{query.lower()}%"
        
        cursor.execute('''
            SELECT DISTINCT local_name, scientific_name, tree_code, fruiting_season
            FROM master_species 
            WHERE LOWER(local_name) LIKE ? OR LOWER(scientific_name) LIKE ? OR LOWER(tree_code) LIKE ?
            ORDER BY 
                CASE 
                    WHEN LOWER(local_name) = ? THEN 1
                    WHEN LOWER(scientific_name) = ? THEN 2
                    WHEN LOWER(tree_code) = ? THEN 3
                    WHEN LOWER(local_name) LIKE ? THEN 4
                    WHEN LOWER(scientific_name) LIKE ? THEN 5
                    ELSE 6
                END
            LIMIT ?
        ''', (search_query, search_query, search_query, 
              query.lower(), query.lower(), query.lower(),
              f"{query.lower()}%", f"{query.lower()}%", limit))
        
        results = cursor.fetchall()
        conn.close()
        
        suggestions = []
        for row in results:
            suggestions.append({
                'local_name': row[0] or '',
                'scientific_name': row[1] or '',
                'tree_code': row[2] or '',
                'fruiting_season': row[3] or ''
            })
        
        return suggestions
        
    except Exception as e:
        logger.error(f"Error getting tree suggestions: {e}")
        return []

def get_all_tree_codes():
    """Get all available tree codes for validation"""
    db_path = Path("data/master_trees.db")
    
    if not db_path.exists():
        create_master_tree_database()
    
    try:
        conn = sqlite3.connect(db_path)
        cursor = conn.cursor()
        
        cursor.execute('SELECT DISTINCT tree_code FROM master_species WHERE tree_code != "" ORDER BY tree_code')
        results = cursor.fetchall()
        conn.close()
        
        return [row[0] for row in results]
        
    except Exception as e:
        logger.error(f"Error getting tree codes: {e}")
        return []

if __name__ == "__main__":
    create_master_tree_database()