//! Shared utilities for SQLite repository implementations.

use chrono::{DateTime, Utc};
use goingson_core::CoreError;
use uuid::Uuid;

/// SQLite datetime format string.
const SQLITE_DATETIME_FORMAT: &str = "%Y-%m-%d %H:%M:%S";

/// Format a `DateTime<Utc>` for SQLite storage.
///
/// Uses the standard SQLite datetime format: `YYYY-MM-DD HH:MM:SS`
#[inline]
#[tracing::instrument(skip_all)]
pub fn format_datetime(dt: &DateTime<Utc>) -> String {
    dt.format(SQLITE_DATETIME_FORMAT).to_string()
}

/// Format a `DateTime<Utc>` for SQLite storage, returning the current time if `None`.
#[inline]
#[tracing::instrument(skip_all)]
pub fn format_datetime_now() -> String {
    format_datetime(&Utc::now())
}

/// Format an optional `DateTime<Utc>` for SQLite storage.
#[inline]
#[tracing::instrument(skip_all)]
pub fn format_datetime_opt(dt: Option<DateTime<Utc>>) -> Option<String> {
    dt.map(|d| format_datetime(&d))
}

/// Parse a datetime string from SQLite.
/// Supports RFC3339, SQLite datetime, and date-only formats.
#[tracing::instrument(skip_all)]
pub fn parse_datetime(s: &str) -> Result<DateTime<Utc>, CoreError> {
    chrono::DateTime::parse_from_rfc3339(s)
        .map(|dt| dt.with_timezone(&Utc))
        .or_else(|_| {
            chrono::NaiveDateTime::parse_from_str(s, "%Y-%m-%d %H:%M:%S")
                .map(|dt| dt.and_utc())
        })
        .or_else(|_| {
            chrono::NaiveDate::parse_from_str(s, "%Y-%m-%d")
                .map(|d| d.and_hms_opt(0, 0, 0).unwrap().and_utc())
        })
        .map_err(|e| CoreError::database_msg(format!("Invalid date: {}", e)))
}

/// Parse a JSON array string into a `Vec<String>`.
/// Returns empty vec on parse failure.
#[tracing::instrument(skip_all)]
pub fn parse_tags(s: &str) -> Vec<String> {
    serde_json::from_str(s).unwrap_or_default()
}

/// Parse a UUID string, converting parse errors to CoreError.
#[tracing::instrument(skip_all)]
pub fn parse_uuid(s: &str) -> Result<Uuid, CoreError> {
    Uuid::parse_str(s).map_err(|e| CoreError::database_msg(format!("Invalid UUID: {}", e)))
}

/// Parse an optional UUID string.
#[tracing::instrument(skip_all)]
pub fn parse_uuid_opt(s: Option<&str>) -> Result<Option<Uuid>, CoreError> {
    s.map(parse_uuid).transpose()
}

/// Escape LIKE wildcards in a value to prevent unintended pattern matching.
#[inline]
pub fn escape_like(value: &str) -> String {
    value.replace('\\', "\\\\").replace('%', "\\%").replace('_', "\\_")
}

/// Validate email address format (RFC 5321/5322 compliant).
///
/// Validates the basic structure of an email address:
/// - Local part: letters, digits, and allowed special chars (.!#$%&'*+/=?^_`{|}~-)
/// - Domain: valid hostname with at least one dot
/// - No consecutive dots, leading/trailing dots in local part
///
/// Note: Does not validate quoted strings or IP address literals for simplicity.
#[tracing::instrument(skip_all)]
pub fn is_valid_email(email: &str) -> bool {
    let trimmed = email.trim();
    if trimmed.is_empty() || trimmed.len() > 254 {
        return false;
    }

    let parts: Vec<&str> = trimmed.splitn(2, '@').collect();
    if parts.len() != 2 {
        return false;
    }

    let (local, domain) = (parts[0], parts[1]);

    // Validate local part
    if local.is_empty() || local.len() > 64 {
        return false;
    }
    if local.starts_with('.') || local.ends_with('.') || local.contains("..") {
        return false;
    }
    if !local.chars().all(|c| {
        c.is_ascii_alphanumeric() || ".!#$%&'*+/=?^_`{|}~-".contains(c)
    }) {
        return false;
    }

    // Validate domain
    if domain.is_empty() || domain.len() > 253 {
        return false;
    }
    if !domain.contains('.') {
        return false;
    }
    if domain.starts_with('.') || domain.ends_with('.') || domain.starts_with('-') {
        return false;
    }

    // Validate each domain label
    for label in domain.split('.') {
        if label.is_empty() || label.len() > 63 {
            return false;
        }
        if label.starts_with('-') || label.ends_with('-') {
            return false;
        }
        if !label.chars().all(|c| c.is_ascii_alphanumeric() || c == '-') {
            return false;
        }
    }

    true
}

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

    #[test]
    fn test_parse_datetime_rfc3339() {
        let result = parse_datetime("2024-01-15T10:30:00Z");
        assert!(result.is_ok());
    }

    #[test]
    fn test_parse_datetime_sqlite_format() {
        let result = parse_datetime("2024-01-15 10:30:00");
        assert!(result.is_ok());
    }

    #[test]
    fn test_parse_datetime_date_only() {
        let result = parse_datetime("2024-01-15");
        assert!(result.is_ok());
        let dt = result.unwrap();
        assert_eq!(dt.hour(), 0);
        assert_eq!(dt.minute(), 0);
    }

    #[test]
    fn test_parse_tags() {
        let tags = parse_tags(r#"["work", "urgent"]"#);
        assert_eq!(tags, vec!["work", "urgent"]);
    }

    #[test]
    fn test_parse_tags_empty() {
        let tags = parse_tags("[]");
        assert!(tags.is_empty());
    }

    #[test]
    fn test_parse_tags_invalid() {
        let tags = parse_tags("not json");
        assert!(tags.is_empty());
    }

    #[test]
    fn test_parse_uuid_valid() {
        let result = parse_uuid("550e8400-e29b-41d4-a716-446655440000");
        assert!(result.is_ok());
    }

    #[test]
    fn test_parse_uuid_invalid() {
        let result = parse_uuid("not-a-uuid");
        assert!(result.is_err());
    }

    #[test]
    fn test_parse_uuid_opt_some() {
        let result = parse_uuid_opt(Some("550e8400-e29b-41d4-a716-446655440000"));
        assert!(result.is_ok());
        assert!(result.unwrap().is_some());
    }

    #[test]
    fn test_parse_uuid_opt_none() {
        let result = parse_uuid_opt(None);
        assert!(result.is_ok());
        assert!(result.unwrap().is_none());
    }

    #[test]
    fn test_is_valid_email() {
        // Valid emails
        assert!(is_valid_email("test@example.com"));
        assert!(is_valid_email("  user@domain.org  ")); // Trimmed
        assert!(is_valid_email("user.name@domain.com"));
        assert!(is_valid_email("user+tag@domain.com"));
        assert!(is_valid_email("user_name@sub.domain.com"));
        assert!(is_valid_email("a@b.co"));

        // Invalid emails
        assert!(!is_valid_email(""));
        assert!(!is_valid_email("invalid"));
        assert!(!is_valid_email("@domain.com"));
        assert!(!is_valid_email("user@"));
        assert!(!is_valid_email("user@domain")); // No TLD
        assert!(!is_valid_email("user@.com"));
        assert!(!is_valid_email("user@domain."));
        assert!(!is_valid_email(".user@domain.com")); // Leading dot
        assert!(!is_valid_email("user.@domain.com")); // Trailing dot
        assert!(!is_valid_email("user..name@domain.com")); // Consecutive dots
        assert!(!is_valid_email("user@-domain.com")); // Domain starts with hyphen
        assert!(!is_valid_email("user@domain-.com")); // Label ends with hyphen
    }
}