use std::collections::{HashMap, HashSet};
use std::future::Future;
use std::pin::Pin;

use crate::error::Result;
use crate::types::DriverType;
use crate::values::Value;

pub mod mysql;
pub mod postgres;
pub mod sqlite;

// ============================================
// Core Driver Traits
// ============================================

/// Row returned from a database query.
/// Maps column names to values.
pub type Row = std::collections::HashMap<String, Value>;

/// Result of a query execution at the driver level.
#[derive(Debug)]
pub struct QueryResult {
    pub rows: Vec<Row>,
    pub rows_affected: u64,
}

/// Async database driver trait.
/// Each database backend (Postgres, MySQL, SQLite) implements this.
pub trait Driver: Send + Sync + 'static {
    fn driver_type(&self) -> DriverType;

    fn execute<'a>(
        &'a self,
        sql: &'a str,
        params: &'a [Value],
    ) -> Pin<Box<dyn Future<Output = Result<QueryResult>> + Send + 'a>>;

    fn execute_write<'a>(
        &'a self,
        sql: &'a str,
        params: &'a [Value],
    ) -> Pin<Box<dyn Future<Output = Result<QueryResult>> + Send + 'a>>;

    fn close<'a>(&'a self) -> Pin<Box<dyn Future<Output = Result<()>> + Send + 'a>>;
}

/// Database connection for use within transactions.
pub trait Connection: Send + Sync {
    fn query<'a>(
        &'a mut self,
        sql: &'a str,
        params: &'a [Value],
    ) -> Pin<Box<dyn Future<Output = Result<QueryResult>> + Send + 'a>>;
}

// ============================================
// ON CONFLICT Configuration
// ============================================

/// Action to take when an INSERT conflict occurs.
#[derive(Debug, Clone)]
pub enum OnConflictAction<'a> {
    DoNothing,
    DoUpdate(&'a [&'a str]),
}

/// ON CONFLICT clause configuration.
/// - Postgres: `ON CONFLICT (columns) DO NOTHING / DO UPDATE SET ...`
/// - MySQL: `INSERT IGNORE INTO ...` / `ON DUPLICATE KEY UPDATE ...`
/// - SQLite: `INSERT OR IGNORE INTO ...` / `ON CONFLICT (columns) DO UPDATE SET ...`
#[derive(Debug, Clone)]
pub struct OnConflict<'a> {
    pub columns: &'a [&'a str],
    pub action: OnConflictAction<'a>,
}

// ============================================
// SQL Builder Trait
// ============================================

/// Builds driver-specific SQL for bulk operations, RETURNING, etc.
pub trait SqlBuilder: Send + Sync {
    fn driver_type(&self) -> DriverType;

    fn supports_returning(&self) -> bool;

    /// Convert `?` placeholders to driver-specific format.
    /// Postgres: `$1, $2, ...`
    /// MySQL/SQLite: keep `?`
    fn rewrite_placeholders(&self, sql: &str) -> String;

    /// Build INSERT SQL for one or more rows.
    ///
    /// `column_types`: optional PostgreSQL type annotations (e.g., `["text", "int"]`).
    /// When provided for Postgres with `row_count > 1`, uses UNNEST-based insert
    /// (param count = columns.len(), each param is an array).
    fn build_insert(
        &self,
        table: &str,
        columns: &[&str],
        row_count: usize,
        on_conflict: Option<&OnConflict>,
        returning_columns: Option<&[&str]>,
        column_types: Option<&[&str]>,
    ) -> (String, usize);

    /// Build bulk UPDATE SQL.
    /// - Postgres: `UPDATE t SET ... FROM UNNEST(...) AS v(...) WHERE ...`
    /// - MySQL: `UPDATE t JOIN (VALUES ROW(...)) AS v(...) ON ... SET ...`
    /// - SQLite: `UPDATE t SET col = CASE WHEN ... END WHERE ...`
    ///
    /// `column_types`: optional PostgreSQL type annotations for key+value columns.
    /// `skip_map`: per-row skip flags. When row i skips column col, use existing value (t.col) instead of v.col.
    fn build_update_many(
        &self,
        table: &str,
        key_columns: &[&str],
        value_columns: &[&str],
        row_count: usize,
        returning_columns: Option<&[&str]>,
        column_types: Option<&[&str]>,
        skip_map: Option<&HashMap<usize, HashSet<String>>>,
    ) -> (String, usize);

    /// Build a SELECT to find records by primary key values.
    /// - Postgres single PK: `WHERE col = ANY($1::type[])`
    /// - Postgres composite: `WHERE (cols) IN (SELECT * FROM UNNEST(...))`
    /// - MySQL single PK: `WHERE col IN (?, ?, ...)`
    /// - MySQL composite: `JOIN (VALUES ROW(...)) AS v(...) ON ...`
    /// - SQLite single PK: `WHERE col IN (?, ?, ...)`
    /// - SQLite composite: `WITH v(...) AS (VALUES ...) ... JOIN v ON ...`
    fn build_find_by_pkeys(
        &self,
        table: &str,
        pkey_columns: &[&str],
        pkey_count: usize,
        select_column: &str,
        column_types: Option<&[&str]>,
    ) -> (String, usize);
}

// ============================================
// Type Cast Trait
// ============================================

/// Driver-specific type casting for serialization/deserialization.
pub trait TypeCast: Send + Sync {
    fn driver_type(&self) -> DriverType;

    fn serialize_array(&self, values: &[Value]) -> Value;
    fn deserialize_array(&self, value: &Value) -> Vec<Value>;

    fn serialize_json(&self, value: &serde_json::Value) -> Value;
    fn deserialize_json(&self, value: &Value) -> Option<serde_json::Value>;

    fn serialize_datetime(&self, value: &chrono::NaiveDateTime) -> Value;
    fn serialize_date(&self, value: &chrono::NaiveDate) -> Value;
}
