//! Slice-related queries and statistics.
//!
//! This module handles database operations for slice metadata, including
//! collection statistics, actor counts, lexicon counts, and slice discovery.

use super::client::Database;
use crate::errors::DatabaseError;
use crate::models::CollectionStats;

impl Database {
    /// Gets collection statistics for a slice.
    ///
    /// Returns record counts and unique actor counts per collection
    /// (excluding lexicons marked as excludedFromSync).
    pub async fn get_slice_collection_stats(
        &self,
        slice_uri: &str,
    ) -> Result<Vec<CollectionStats>, DatabaseError> {
        let stats = sqlx::query!(
            r#"
            WITH slice_collections AS (
                SELECT DISTINCT
                    json->>'nsid' as collection_nsid
                FROM record
                WHERE collection = 'network.slices.lexicon'
                    AND json->>'slice' = $1
                    AND json->>'nsid' IS NOT NULL
                    AND (json->>'definitions')::jsonb->'main'->>'type' = 'record'
                    AND (json->>'excludedFromSync' IS NULL OR json->>'excludedFromSync' != 'true')
            )
            SELECT
                r.collection,
                COUNT(*) as record_count,
                COUNT(DISTINCT r.did) as unique_actors
            FROM record r
            INNER JOIN slice_collections sc ON r.collection = sc.collection_nsid
            WHERE r.slice_uri = $1
            GROUP BY r.collection
            ORDER BY r.collection
            "#,
            slice_uri
        )
        .fetch_all(&self.pool)
        .await?;

        Ok(stats
            .into_iter()
            .map(|row| CollectionStats {
                collection: row.collection,
                record_count: row.record_count.unwrap_or(0),
                unique_actors: row.unique_actors.unwrap_or(0),
            })
            .collect())
    }

    /// Gets the list of collection NSIDs defined for a slice.
    ///
    /// Only includes lexicons with type 'record' that are not excluded from sync.
    pub async fn get_slice_collections_list(
        &self,
        slice_uri: &str,
    ) -> Result<Vec<String>, DatabaseError> {
        let rows = sqlx::query!(
            r#"
            SELECT DISTINCT json->>'nsid' as collection_nsid
            FROM record
            WHERE collection = 'network.slices.lexicon'
                AND json->>'slice' = $1
                AND json->>'nsid' IS NOT NULL
                AND (json->>'definitions')::jsonb->'main'->>'type' = 'record'
                AND (json->>'excludedFromSync' IS NULL OR json->>'excludedFromSync' != 'true')
            ORDER BY json->>'nsid'
            "#,
            slice_uri
        )
        .fetch_all(&self.pool)
        .await?;

        Ok(rows
            .into_iter()
            .filter_map(|row| row.collection_nsid)
            .collect())
    }

    /// Counts total records across all collections in a slice.
    ///
    /// Excludes lexicons marked as excludedFromSync.
    pub async fn get_slice_total_records(&self, slice_uri: &str) -> Result<i64, DatabaseError> {
        let count = sqlx::query!(
            r#"
            WITH slice_collections AS (
                SELECT DISTINCT
                    json->>'nsid' as collection_nsid
                FROM record
                WHERE collection = 'network.slices.lexicon'
                    AND json->>'slice' = $1
                    AND json->>'nsid' IS NOT NULL
                    AND (json->>'definitions')::jsonb->'main'->>'type' = 'record'
                    AND (json->>'excludedFromSync' IS NULL OR json->>'excludedFromSync' != 'true')
            )
            SELECT COUNT(*) as count
            FROM record r
            INNER JOIN slice_collections sc ON r.collection = sc.collection_nsid
            WHERE r.slice_uri = $1
            "#,
            slice_uri
        )
        .fetch_one(&self.pool)
        .await?;

        Ok(count.count.unwrap_or(0))
    }

    /// Counts total actors tracked in a slice.
    pub async fn get_slice_total_actors(&self, slice_uri: &str) -> Result<i64, DatabaseError> {
        let count = sqlx::query!(
            r#"
            SELECT COUNT(*) as count
            FROM actor
            WHERE slice_uri = $1
            "#,
            slice_uri
        )
        .fetch_one(&self.pool)
        .await?;

        Ok(count.count.unwrap_or(0))
    }

    /// Counts lexicon definitions for a slice.
    ///
    /// Only includes record-type lexicons.
    pub async fn get_slice_lexicon_count(&self, slice_uri: &str) -> Result<i64, DatabaseError> {
        let count = sqlx::query!(
            r#"
            SELECT COUNT(*) as count
            FROM record
            WHERE collection = 'network.slices.lexicon'
                AND json->>'slice' = $1
                AND (json->>'definitions')::jsonb->'main'->>'type' = 'record'
            "#,
            slice_uri
        )
        .fetch_one(&self.pool)
        .await?;

        Ok(count.count.unwrap_or(0))
    }

    /// Gets all slice URIs from network.slices.slice records.
    ///
    /// Returns all slices that exist in the system
    pub async fn get_all_slices(&self) -> Result<Vec<String>, DatabaseError> {
        let rows: Vec<(String,)> = sqlx::query_as(
            r#"
            SELECT DISTINCT uri as slice_uri
            FROM record
            WHERE collection = 'network.slices.slice'
        "#,
        )
        .fetch_all(&self.pool)
        .await?;

        Ok(rows.into_iter().map(|(uri,)| uri).collect())
    }

    /// Gets the domain associated with a slice.
    ///
    /// Looks up the network.slices.slice record and extracts the domain field.
    ///
    /// # Returns
    /// Some(domain) if the slice exists and has a domain, None otherwise
    pub async fn get_slice_domain(&self, slice_uri: &str) -> Result<Option<String>, DatabaseError> {
        let row = sqlx::query!(
            r#"
            SELECT json->>'domain' as domain
            FROM record
            WHERE collection = 'network.slices.slice'
            AND uri = $1
            "#,
            slice_uri
        )
        .fetch_optional(&self.pool)
        .await?;

        Ok(row.and_then(|r| r.domain))
    }

    /// Looks up a slice URI by actor handle and rkey.
    ///
    /// This method resolves the DID from the actor handle via the actors table,
    /// then constructs the expected slice URI pattern and finds the matching slice.
    ///
    /// # Arguments
    /// * `actor_handle` - The handle of the slice owner (e.g., "alice.bsky.social")
    /// * `rkey` - The record key portion of the slice URI
    ///
    /// # Returns
    /// Some(slice_uri) if found, None otherwise
    pub async fn get_slice_uri_by_handle_and_rkey(
        &self,
        actor_handle: &str,
        rkey: &str,
    ) -> Result<Option<String>, DatabaseError> {
        // First, try to get the DID from the actor table
        let did_row = sqlx::query!(
            r#"
            SELECT DISTINCT did
            FROM actor
            WHERE handle = $1
            LIMIT 1
            "#,
            actor_handle
        )
        .fetch_optional(&self.pool)
        .await?;

        if let Some(row) = did_row {
            // We have a DID, construct the expected URI pattern and look it up
            let expected_uri_pattern = format!("at://{}/%/{}", row.did, rkey);

            let slice_row = sqlx::query!(
                r#"
                SELECT uri
                FROM record
                WHERE collection = 'network.slices.slice'
                AND uri LIKE $1
                LIMIT 1
                "#,
                expected_uri_pattern
            )
            .fetch_optional(&self.pool)
            .await?;

            Ok(slice_row.map(|r| r.uri))
        } else {
            // No DID found in actors table, try to look up directly by actorHandle in the slice record
            let slice_row = sqlx::query!(
                r#"
                SELECT uri
                FROM record
                WHERE collection = 'network.slices.slice'
                AND json->>'actorHandle' = $1
                AND uri LIKE '%' || $2 || '%'
                LIMIT 1
                "#,
                actor_handle,
                rkey
            )
            .fetch_optional(&self.pool)
            .await?;

            Ok(slice_row.map(|r| r.uri))
        }
    }
}