// SQL query builder for dynamic event filtering
//
// Constructs and executes SQL queries based on filter criteria,
// supporting PostGIS for location-based filtering.

use sqlx::{PgPool, QueryBuilder};
use tracing::{debug, instrument, trace};

use super::{EventFilterCriteria, EventSortField, FilterError, LocationFilter, SortOrder};
use crate::storage::event::model::Event;
use crate::atproto::lexicon::community::lexicon::calendar::event::{Mode, Status};

/// SQL query builder for event filtering
#[derive(Debug, Clone)]
pub struct EventQueryBuilder {
    pool: PgPool,
}

impl EventQueryBuilder {
    /// Create a new query builder
    pub fn new(pool: PgPool) -> Self {
        Self { pool }
    }
    
    /// Build and execute a query to get filtered events
    #[instrument(skip(self, criteria), fields(search_term = ?criteria.search_term, page = criteria.page))]
    pub async fn build_and_execute(
        &self,
        criteria: &EventFilterCriteria,
    ) -> Result<Vec<Event>, FilterError> {
        let mut query = QueryBuilder::new(
            "SELECT aturi, cid, did, lexicon, record, name, updated_at FROM events"
        );
        
        // Apply filters
        self.apply_filters(&mut query, criteria);
        
        // Apply sorting
        self.apply_sorting(&mut query, criteria);
        
        // Apply pagination
        self.apply_pagination(&mut query, criteria);
        
        trace!("Executing query: {}", query.sql());
        
        let events = query
            .build_query_as::<Event>()
            .fetch_all(&self.pool)
            .await?;
        
        Ok(events)
    }
    
    /// Build and execute a query to count total results
    #[instrument(skip(self, criteria))]
    pub async fn count_results(&self, criteria: &EventFilterCriteria) -> Result<i64, FilterError> {
        let mut query = QueryBuilder::new("SELECT COUNT(*) FROM events");
        
        // Apply the same filters but without sorting/pagination
        self.apply_where_clause(&mut query, criteria);
        
        let count: (i64,) = query
            .build_query_as()
            .fetch_one(&self.pool)
            .await?;
        
        Ok(count.0)
    }
    
    /// Apply WHERE clause filters to the query
    fn apply_filters<'a>(
        &self,
        query: &mut QueryBuilder<'a, sqlx::Postgres>,
        criteria: &'a EventFilterCriteria,
    ) {
        self.apply_where_clause(query, criteria);
    }
    
    /// Apply WHERE clause conditions
    fn apply_where_clause<'a>(
        &self,
        query: &mut QueryBuilder<'a, sqlx::Postgres>,
        criteria: &'a EventFilterCriteria,
    ) {
        let mut has_where = false;
        
        // Text search in name and description
        if let Some(ref term) = criteria.search_term {
            if !term.trim().is_empty() {
                query.push(if has_where { " AND " } else { " WHERE " });
                query.push("(name ILIKE ");
                query.push_bind(format!("%{}%", term));
                query.push(" OR record->>'description' ILIKE ");
                query.push_bind(format!("%{}%", term));
                query.push(")");
                has_where = true;
            }
        }
        
        // Date filtering - using overlap logic for events within date range
        // An event overlaps with the filter range if:
        // event_start <= filter_end AND event_end >= filter_start
        if criteria.start_date.is_some() || criteria.end_date.is_some() {
            debug!("Applying date filters: start_date={:?}, end_date={:?}", criteria.start_date, criteria.end_date);
            
            query.push(if has_where { " AND " } else { " WHERE " });
            query.push("(");
            
            let mut condition_added = false;
            
            if let Some(filter_start) = criteria.start_date {
                // Event must end on or after filter start date (for overlap)
                // If no endsAt, use end of start day (startsAt date + 23:59:59)
                query.push("COALESCE((record->>'endsAt')::timestamptz, \
                           DATE_TRUNC('day', (record->>'startsAt')::timestamptz) + INTERVAL '1 day' - INTERVAL '1 second') >= ");
                query.push_bind(filter_start);
                condition_added = true;
            }
            
            if let Some(filter_end) = criteria.end_date {
                // Event must start on or before filter end date (for overlap)
                if condition_added {
                    query.push(" AND ");
                }
                query.push("(record->>'startsAt')::timestamptz <= ");
                query.push_bind(filter_end);
            }
            
            query.push(")");
            has_where = true;
        }
        
        // Creator filtering
        if let Some(ref creator_did) = criteria.creator_did {
            query.push(if has_where { " AND " } else { " WHERE " });
            query.push("did = ");
            query.push_bind(creator_did);
            has_where = true;
        }
        
        // Mode filtering
        if !criteria.modes.is_empty() {
            query.push(if has_where { " AND " } else { " WHERE " });
            query.push("(");
            for (i, mode) in criteria.modes.iter().enumerate() {
                if i > 0 {
                    query.push(" OR ");
                }
                // Convert enum to its serde representation for database comparison
                let mode_str = match mode {
                    Mode::InPerson => 
                        "community.lexicon.calendar.event#inperson",
                    Mode::Virtual => 
                        "community.lexicon.calendar.event#virtual",
                    Mode::Hybrid => 
                        "community.lexicon.calendar.event#hybrid",
                };
                query.push("record->>'mode' = ");
                query.push_bind(mode_str);
            }
            query.push(")");
            has_where = true;
        }
        
        // Status filtering
        if !criteria.statuses.is_empty() {
            query.push(if has_where { " AND " } else { " WHERE " });
            query.push("(");
            for (i, status) in criteria.statuses.iter().enumerate() {
                if i > 0 {
                    query.push(" OR ");
                }
                // Convert enum to its serde representation for database comparison
                let status_str = match status {
                    Status::Scheduled => 
                        "community.lexicon.calendar.event#scheduled",
                    Status::Rescheduled => 
                        "community.lexicon.calendar.event#rescheduled",
                    Status::Cancelled => 
                        "community.lexicon.calendar.event#cancelled",
                    Status::Postponed => 
                        "community.lexicon.calendar.event#postponed",
                    Status::Planned => 
                        "community.lexicon.calendar.event#planned",
                };
                query.push("record->>'status' = ");
                query.push_bind(status_str);
            }
            query.push(")");
            has_where = true;
        }
        
        // Location filtering using PostGIS (if PostGIS extension is available)
        if let Some(ref location) = criteria.location {
            self.apply_location_filter(query, location, has_where);
        }
    }
    
    /// Apply location-based filtering using PostGIS
    /// This method handles all EventLocation types from the ATproto lexicon:
    /// - Address: Physical addresses (must have coordinates or be geocoded)
    /// - Geo: Direct latitude/longitude coordinates 
    /// - Fsq: Foursquare place IDs (require coordinates)
    /// - Hthree: H3 geospatial cell identifiers (require coordinates)
    /// Virtual/hybrid events without location data are filtered out.
    fn apply_location_filter<'a>(
        &self,
        query: &mut QueryBuilder<'a, sqlx::Postgres>,
        location: &'a LocationFilter,
        has_where: bool,
    ) {
        query.push(if has_where { " AND " } else { " WHERE " });
        
        // First, ensure we only get events with actual location data
        // This filters out virtual/hybrid events without physical locations
        query.push("(");
        
        // Check if event has location data at all (not null and not empty array)
        query.push("record->'location' IS NOT NULL AND ");
        query.push("jsonb_array_length(CAST(record AS jsonb)->'location') > 0 AND ");
        
        // Build a comprehensive location check that handles all location types
        query.push("(");
        
        // Case 1: Direct Geo coordinates (community.lexicon.location.geo)
        query.push("EXISTS (");
        query.push("  SELECT 1 FROM jsonb_array_elements(CAST(record AS jsonb)->'location') AS loc ");
        query.push("  WHERE loc->>'$type' = 'community.lexicon.location.geo' ");
        query.push("  AND loc->>'latitude' IS NOT NULL ");
        query.push("  AND loc->>'longitude' IS NOT NULL ");
        query.push("  AND ST_DWithin(");
        query.push("    ST_MakePoint((loc->>'longitude')::float8, (loc->>'latitude')::float8)::geography, ");
        query.push("    ST_MakePoint(");
        query.push_bind(location.longitude);
        query.push(", ");
        query.push_bind(location.latitude);
        query.push(")::geography, ");
        query.push_bind(location.radius_km * 1000.0); // Convert km to meters
        query.push("  )");
        query.push(") OR ");
        
        // Case 2: Address with coordinates (community.lexicon.location.address)
        // Note: Addresses without coordinates would need geocoding (future enhancement)
        query.push("EXISTS (");
        query.push("  SELECT 1 FROM jsonb_array_elements(CAST(record AS jsonb)->'location') AS loc ");
        query.push("  WHERE loc->>'$type' = 'community.lexicon.location.address' ");
        query.push("  AND loc->>'latitude' IS NOT NULL ");
        query.push("  AND loc->>'longitude' IS NOT NULL ");
        query.push("  AND ST_DWithin(");
        query.push("    ST_MakePoint((loc->>'longitude')::float8, (loc->>'latitude')::float8)::geography, ");
        query.push("    ST_MakePoint(");
        query.push_bind(location.longitude);
        query.push(", ");
        query.push_bind(location.latitude);
        query.push(")::geography, ");
        query.push_bind(location.radius_km * 1000.0);
        query.push("  )");
        query.push(") OR ");
        
        // Case 3: Foursquare places with coordinates (community.lexicon.location.fsq)
        query.push("EXISTS (");
        query.push("  SELECT 1 FROM jsonb_array_elements(CAST(record AS jsonb)->'location') AS loc ");
        query.push("  WHERE loc->>'$type' = 'community.lexicon.location.fsq' ");
        query.push("  AND loc->>'latitude' IS NOT NULL ");
        query.push("  AND loc->>'longitude' IS NOT NULL ");
        query.push("  AND ST_DWithin(");
        query.push("    ST_MakePoint((loc->>'longitude')::float8, (loc->>'latitude')::float8)::geography, ");
        query.push("    ST_MakePoint(");
        query.push_bind(location.longitude);
        query.push(", ");
        query.push_bind(location.latitude);
        query.push(")::geography, ");
        query.push_bind(location.radius_km * 1000.0);
        query.push("  )");
        query.push(") OR ");
        
        // Case 4: H3 cells with coordinates (community.lexicon.location.hthree)
        query.push("EXISTS (");
        query.push("  SELECT 1 FROM jsonb_array_elements(CAST(record AS jsonb)->'location') AS loc ");
        query.push("  WHERE loc->>'$type' = 'community.lexicon.location.hthree' ");
        query.push("  AND loc->>'latitude' IS NOT NULL ");
        query.push("  AND loc->>'longitude' IS NOT NULL ");
        query.push("  AND ST_DWithin(");
        query.push("    ST_MakePoint((loc->>'longitude')::float8, (loc->>'latitude')::float8)::geography, ");
        query.push("    ST_MakePoint(");
        query.push_bind(location.longitude);
        query.push(", ");
        query.push_bind(location.latitude);
        query.push(")::geography, ");
        query.push_bind(location.radius_km * 1000.0);
        query.push("  )");
        query.push(")");
        
        // End the comprehensive location check
        query.push(")");
        
        // End the main location filter condition
        query.push(")");
    }
    
    /// Apply sorting to the query
    fn apply_sorting<'a>(
        &self,
        query: &mut QueryBuilder<'a, sqlx::Postgres>,
        criteria: &'a EventFilterCriteria,
    ) {
        query.push(" ORDER BY ");
        
        match criteria.sort_by {
            EventSortField::StartTime => {
                query.push("(record->>'startsAt')::timestamptz");
            }
            EventSortField::UpdatedAt => {
                query.push("updated_at");
            }
            EventSortField::Name => {
                query.push("name");
            }
            EventSortField::PopularityRsvp => {
                // This would require a more complex query with a subquery or join
                // For now, fall back to start time
                query.push("(record->>'startsAt')::timestamptz");
            }
        }
        
        match criteria.sort_order {
            SortOrder::Ascending => query.push(" ASC"),
            SortOrder::Descending => query.push(" DESC"),
        };
    }
    
    /// Apply pagination to the query
    fn apply_pagination<'a>(
        &self,
        query: &mut QueryBuilder<'a, sqlx::Postgres>,
        criteria: &'a EventFilterCriteria,
    ) {
        query.push(" LIMIT ");
        query.push_bind(criteria.page_size as i64);
        query.push(" OFFSET ");
        
        // Ensure page is at least 1 to avoid underflow
        let page = criteria.page.max(1);
        let offset = (page - 1) * criteria.page_size;
        query.push_bind(offset as i64);
    }
}

#[cfg(test)]
mod tests {
    use super::*;

    #[test]
    fn test_criteria_building() {
        let criteria = EventFilterCriteria::new()
            .with_search_term("conference")
            .with_pagination(0, 10);
        
        assert_eq!(criteria.search_term, Some("conference".to_string()));
        assert_eq!(criteria.page, 0);
        assert_eq!(criteria.page_size, 10);
    }
    
    #[test]
    fn test_has_filters() {
        let empty_criteria = EventFilterCriteria::new();
        assert!(!empty_criteria.has_filters());
        
        let filtered_criteria = EventFilterCriteria::new()
            .with_search_term("test");
        assert!(filtered_criteria.has_filters());
    }
}