"""
Supabase client configuration and database operations for TreeTrack
"""

import os
import logging
from typing import Dict, List, Optional, Any
from supabase import create_client, Client
from pathlib import Path

logger = logging.getLogger(__name__)

# Supabase configuration - all must be provided via environment variables
SUPABASE_URL = os.getenv("SUPABASE_URL")
SUPABASE_ANON_KEY = os.getenv("SUPABASE_ANON_KEY")
SUPABASE_SERVICE_ROLE_KEY = os.getenv("SUPABASE_SERVICE_ROLE_KEY")

# Validate required environment variables (defer validation to runtime)
if not SUPABASE_URL:
    logger.warning("SUPABASE_URL not found - will cause connection failures")
if not SUPABASE_ANON_KEY:
    logger.warning("SUPABASE_ANON_KEY not found - will cause connection failures")
if not SUPABASE_SERVICE_ROLE_KEY:
    logger.warning("SUPABASE_SERVICE_ROLE_KEY not provided - admin functions will be limited")

# Initialize Supabase client
supabase: Optional[Client] = None

def get_supabase_client() -> Client:
    """Get Supabase client instance with better error handling"""
    global supabase
    
    if supabase is None:
        if not SUPABASE_URL:
            raise ValueError("SUPABASE_URL environment variable is required")
        if not SUPABASE_ANON_KEY:
            raise ValueError("SUPABASE_ANON_KEY environment variable is required")
        
        try:
            supabase = create_client(SUPABASE_URL, SUPABASE_ANON_KEY)
            logger.info("Supabase client initialized successfully")
        except Exception as e:
            logger.error(f"Failed to initialize Supabase client: {e}")
            raise ValueError(f"Supabase client initialization failed: {e}")
    
    return supabase

def get_service_client() -> Client:
    """Get Supabase client with service role (admin) permissions"""
    if not SUPABASE_SERVICE_ROLE_KEY:
        raise ValueError("SUPABASE_SERVICE_ROLE_KEY environment variable is required")
    
    return create_client(SUPABASE_URL, SUPABASE_SERVICE_ROLE_KEY)


# Initialize database table (run once)
def create_trees_table():
    """Create the trees table in Supabase"""
    client = get_service_client()
    
    # SQL to create trees table
    sql = """
    CREATE TABLE IF NOT EXISTS trees (
        id BIGSERIAL PRIMARY KEY,
        latitude DECIMAL(10, 8) NOT NULL CHECK (latitude >= -90 AND latitude <= 90),
        longitude DECIMAL(11, 8) NOT NULL CHECK (longitude >= -180 AND longitude <= 180),
        location_name VARCHAR(200),
        local_name VARCHAR(200),
        scientific_name VARCHAR(200),
        common_name VARCHAR(200),
        tree_code VARCHAR(20),
        height DECIMAL(6, 2) CHECK (height > 0 AND height <= 1000), -- feet
        width DECIMAL(6, 2) CHECK (width > 0 AND width <= 200),   -- feet (girth/DBH)
        utility JSONB,
        storytelling_text TEXT CHECK (LENGTH(storytelling_text) <= 5000),
        storytelling_audio VARCHAR(500),
        phenology_stages JSONB,
        photographs JSONB,
        notes TEXT CHECK (LENGTH(notes) <= 2000),
        created_at TIMESTAMPTZ DEFAULT NOW(),
        updated_at TIMESTAMPTZ DEFAULT NOW(),
        created_by VARCHAR(100) DEFAULT 'system'
    );
    
    -- Create indexes for better performance
    CREATE INDEX IF NOT EXISTS idx_trees_location ON trees(latitude, longitude);
    CREATE INDEX IF NOT EXISTS idx_trees_scientific_name ON trees(scientific_name);
    CREATE INDEX IF NOT EXISTS idx_trees_local_name ON trees(local_name);
    CREATE INDEX IF NOT EXISTS idx_trees_location_name ON trees(location_name);
    CREATE INDEX IF NOT EXISTS idx_trees_tree_code ON trees(tree_code);
    CREATE INDEX IF NOT EXISTS idx_trees_created_at ON trees(created_at);
    
    -- Create updated_at trigger
    CREATE OR REPLACE FUNCTION update_updated_at_column()
    RETURNS TRIGGER AS $$
    BEGIN
        NEW.updated_at = NOW();
        RETURN NEW;
    END;
    $$ language 'plpgsql';
    
    DROP TRIGGER IF EXISTS update_trees_updated_at ON trees;
    CREATE TRIGGER update_trees_updated_at
        BEFORE UPDATE ON trees
        FOR EACH ROW
        EXECUTE FUNCTION update_updated_at_column();
    """
    
    try:
        client.rpc('exec_sql', {'sql': sql}).execute()
        logger.info("Trees table created successfully")
        return True
    except Exception as e:
        logger.error(f"Error creating trees table: {e}")
        return False

# Storage buckets setup
def create_storage_buckets():
    """Create storage buckets for files"""
    client = get_service_client()
    
    buckets = [
        {"name": "tree-images", "public": False},
        {"name": "tree-audios", "public": False}
    ]
    
    for bucket in buckets:
        try:
            result = client.storage.create_bucket(bucket["name"], {"public": bucket["public"]})
            logger.info(f"Created storage bucket: {bucket['name']}")
        except Exception as e:
            logger.info(f"Bucket {bucket['name']} might already exist: {e}")
    
    return True

# Test connection
def test_supabase_connection() -> bool:
    """Test Supabase connection"""
    try:
        logger.info("Testing Supabase connection...")
        client = get_supabase_client()
        result = client.table('trees').select("id").limit(1).execute()
        logger.info("Supabase connection successful")
        return True
        
    except Exception as e:
        logger.error(f"Supabase connection failed: {type(e).__name__}: {e}")
        return False