//! Subscription queries: tier CRUD, subscription lifecycle, and access control.

use chrono::{DateTime, Utc};
use sqlx::PgPool;

use super::enums::SubscriptionStatus;
use super::models::*;
use super::{PriceCents, ProjectId, SubscriptionId, SubscriptionTierId, UserId};
use crate::error::Result;

// ── Tier CRUD ──

/// Create a new subscription tier for a project.
#[tracing::instrument(skip_all)]
pub async fn create_subscription_tier(
    pool: &PgPool,
    project_id: ProjectId,
    name: &str,
    description: Option<&str>,
    price_cents: PriceCents,
) -> Result<DbSubscriptionTier> {
    let tier = sqlx::query_as::<_, DbSubscriptionTier>(
        r#"
        INSERT INTO subscription_tiers (project_id, name, description, price_cents)
        VALUES ($1, $2, $3, $4)
        RETURNING *
        "#,
    )
    .bind(project_id)
    .bind(name)
    .bind(description)
    .bind(price_cents.as_i32())
    .fetch_one(pool)
    .await?;

    Ok(tier)
}

/// Get a subscription tier by ID.
#[tracing::instrument(skip_all)]
pub async fn get_subscription_tier_by_id(
    pool: &PgPool,
    id: SubscriptionTierId,
) -> Result<Option<DbSubscriptionTier>> {
    let tier = sqlx::query_as::<_, DbSubscriptionTier>(
        "SELECT * FROM subscription_tiers WHERE id = $1",
    )
    .bind(id)
    .fetch_optional(pool)
    .await?;

    Ok(tier)
}

/// Get all active tiers for a project, ordered by sort_order.
#[tracing::instrument(skip_all)]
pub async fn get_active_tiers_by_project(
    pool: &PgPool,
    project_id: ProjectId,
) -> Result<Vec<DbSubscriptionTier>> {
    let tiers = sqlx::query_as::<_, DbSubscriptionTier>(
        "SELECT * FROM subscription_tiers WHERE project_id = $1 AND is_active = true ORDER BY sort_order, created_at",
    )
    .bind(project_id)
    .fetch_all(pool)
    .await?;

    Ok(tiers)
}

/// Get all tiers for a project (active and inactive), for dashboard management.
#[tracing::instrument(skip_all)]
pub async fn get_all_tiers_by_project(
    pool: &PgPool,
    project_id: ProjectId,
) -> Result<Vec<DbSubscriptionTier>> {
    let tiers = sqlx::query_as::<_, DbSubscriptionTier>(
        "SELECT * FROM subscription_tiers WHERE project_id = $1 ORDER BY sort_order, created_at",
    )
    .bind(project_id)
    .fetch_all(pool)
    .await?;

    Ok(tiers)
}

/// Update a subscription tier's name, description, and active status.
#[tracing::instrument(skip_all)]
pub async fn update_subscription_tier(
    pool: &PgPool,
    id: SubscriptionTierId,
    name: &str,
    description: Option<&str>,
    is_active: bool,
) -> Result<DbSubscriptionTier> {
    let tier = sqlx::query_as::<_, DbSubscriptionTier>(
        r#"
        UPDATE subscription_tiers
        SET name = $2, description = $3, is_active = $4
        WHERE id = $1
        RETURNING *
        "#,
    )
    .bind(id)
    .bind(name)
    .bind(description)
    .bind(is_active)
    .fetch_one(pool)
    .await?;

    Ok(tier)
}

/// Store Stripe product and price IDs on a tier after creating them on connected account.
#[tracing::instrument(skip_all)]
pub async fn update_tier_stripe_ids(
    pool: &PgPool,
    tier_id: SubscriptionTierId,
    product_id: &str,
    price_id: &str,
) -> Result<()> {
    sqlx::query(
        r#"
        UPDATE subscription_tiers
        SET stripe_product_id = $2, stripe_price_id = $3
        WHERE id = $1
        "#,
    )
    .bind(tier_id)
    .bind(product_id)
    .bind(price_id)
    .execute(pool)
    .await?;

    Ok(())
}

/// Delete a subscription tier. Soft-deletes (sets is_active=false) if any
/// subscriptions reference it; hard-deletes otherwise.
///
/// Uses a transaction with FOR UPDATE to prevent a TOCTOU race where a
/// subscription could be created between the existence check and the delete.
#[tracing::instrument(skip_all)]
pub async fn delete_subscription_tier(pool: &PgPool, id: SubscriptionTierId) -> Result<()> {
    let mut tx = pool.begin().await?;

    // Lock the tier row to serialize against concurrent subscription creation
    sqlx::query("SELECT id FROM subscription_tiers WHERE id = $1 FOR UPDATE")
        .bind(id)
        .fetch_optional(&mut *tx)
        .await?
        .ok_or(sqlx::Error::RowNotFound)?;

    let has_subscriptions: bool = sqlx::query_scalar(
        "SELECT EXISTS(SELECT 1 FROM subscriptions WHERE tier_id = $1)",
    )
    .bind(id)
    .fetch_one(&mut *tx)
    .await?;

    if has_subscriptions {
        sqlx::query("UPDATE subscription_tiers SET is_active = false WHERE id = $1")
            .bind(id)
            .execute(&mut *tx)
            .await?;
    } else {
        sqlx::query("DELETE FROM subscription_tiers WHERE id = $1")
            .bind(id)
            .execute(&mut *tx)
            .await?;
    }

    tx.commit().await?;
    Ok(())
}

// ── Subscription lifecycle ──

/// Create a new subscription record after successful checkout.
///
/// Returns `None` if the subscription already exists (duplicate webhook
/// or concurrent active subscription for the same user+project).
/// The partial UNIQUE index on `(subscriber_id, project_id) WHERE status = 'active'`
/// prevents multiple active subscriptions at the DB level.
#[tracing::instrument(skip_all)]
pub async fn create_subscription<'e>(
    executor: impl sqlx::PgExecutor<'e>,
    subscriber_id: UserId,
    tier_id: SubscriptionTierId,
    project_id: ProjectId,
    stripe_subscription_id: &str,
    stripe_customer_id: &str,
) -> Result<Option<DbSubscription>> {
    let sub = sqlx::query_as::<_, DbSubscription>(
        r#"
        INSERT INTO subscriptions (subscriber_id, tier_id, project_id, stripe_subscription_id, stripe_customer_id)
        VALUES ($1, $2, $3, $4, $5)
        ON CONFLICT DO NOTHING
        RETURNING *
        "#,
    )
    .bind(subscriber_id)
    .bind(tier_id)
    .bind(project_id)
    .bind(stripe_subscription_id)
    .bind(stripe_customer_id)
    .fetch_optional(executor)
    .await?;

    Ok(sub)
}

/// Look up a subscription by its Stripe subscription ID.
#[tracing::instrument(skip_all)]
pub async fn get_subscription_by_stripe_id(
    pool: &PgPool,
    stripe_sub_id: &str,
) -> Result<Option<DbSubscription>> {
    let sub = sqlx::query_as::<_, DbSubscription>(
        "SELECT * FROM subscriptions WHERE stripe_subscription_id = $1",
    )
    .bind(stripe_sub_id)
    .fetch_optional(pool)
    .await?;

    Ok(sub)
}

/// Update subscription status (active, past_due, canceled, unpaid).
/// Sets canceled_at when transitioning to canceled, preserving existing value.
/// Returns the updated record, or None if not found.
#[tracing::instrument(skip_all)]
pub async fn update_subscription_status<'e>(
    executor: impl sqlx::PgExecutor<'e>,
    stripe_sub_id: &str,
    status: SubscriptionStatus,
) -> Result<Option<DbSubscription>> {
    let sub = sqlx::query_as::<_, DbSubscription>(
        r#"
        UPDATE subscriptions
        SET status = $2,
            canceled_at = CASE
                WHEN $2 = 'canceled' THEN COALESCE(canceled_at, NOW())
                ELSE canceled_at
            END
        WHERE stripe_subscription_id = $1
        RETURNING *
        "#,
    )
    .bind(stripe_sub_id)
    .bind(status)
    .fetch_optional(executor)
    .await?;

    Ok(sub)
}

/// Update the billing period timestamps for a subscription.
#[tracing::instrument(skip_all)]
pub async fn update_subscription_period<'e>(
    executor: impl sqlx::PgExecutor<'e>,
    stripe_sub_id: &str,
    period_start: DateTime<Utc>,
    period_end: DateTime<Utc>,
) -> Result<()> {
    sqlx::query(
        r#"
        UPDATE subscriptions
        SET current_period_start = $2, current_period_end = $3
        WHERE stripe_subscription_id = $1
        "#,
    )
    .bind(stripe_sub_id)
    .bind(period_start)
    .bind(period_end)
    .execute(executor)
    .await?;

    Ok(())
}

/// Mark a subscription as canceled.
#[tracing::instrument(skip_all)]
pub async fn cancel_subscription(
    pool: &PgPool,
    stripe_sub_id: &str,
) -> Result<Option<DbSubscription>> {
    let sub = sqlx::query_as::<_, DbSubscription>(
        r#"
        UPDATE subscriptions
        SET status = 'canceled', canceled_at = COALESCE(canceled_at, NOW())
        WHERE stripe_subscription_id = $1
        RETURNING *
        "#,
    )
    .bind(stripe_sub_id)
    .fetch_optional(pool)
    .await?;

    Ok(sub)
}

// ── Suspension pause/resume ──

/// Get all active subscriptions to a creator's projects (for pausing on suspension).
#[tracing::instrument(skip_all)]
pub async fn get_active_subscriptions_by_creator(
    pool: &PgPool,
    creator_id: UserId,
) -> Result<Vec<DbSubscription>> {
    let subs = sqlx::query_as::<_, DbSubscription>(
        r#"
        SELECT s.* FROM subscriptions s
        WHERE s.project_id IN (SELECT id FROM projects WHERE user_id = $1)
          AND s.status = 'active'
          AND s.paused_at IS NULL
        "#,
    )
    .bind(creator_id)
    .fetch_all(pool)
    .await?;

    Ok(subs)
}

/// Mark all active subscriptions to a creator's projects as paused.
#[tracing::instrument(skip_all)]
pub async fn pause_subscriptions_for_creator(
    pool: &PgPool,
    creator_id: UserId,
) -> Result<u64> {
    let result = sqlx::query(
        r#"
        UPDATE subscriptions SET paused_at = NOW()
        WHERE project_id IN (SELECT id FROM projects WHERE user_id = $1)
          AND status = 'active'
          AND paused_at IS NULL
        "#,
    )
    .bind(creator_id)
    .execute(pool)
    .await?;

    Ok(result.rows_affected())
}

/// Get all paused subscriptions to a creator's projects (for cancelling on termination).
#[tracing::instrument(skip_all)]
pub async fn get_paused_subscriptions_by_creator(
    pool: &PgPool,
    creator_id: UserId,
) -> Result<Vec<DbSubscription>> {
    let subs = sqlx::query_as::<_, DbSubscription>(
        r#"
        SELECT s.* FROM subscriptions s
        WHERE s.project_id IN (SELECT id FROM projects WHERE user_id = $1)
          AND s.status = 'active'
          AND s.paused_at IS NOT NULL
        "#,
    )
    .bind(creator_id)
    .fetch_all(pool)
    .await?;

    Ok(subs)
}

/// Resume all paused subscriptions for a creator's projects.
#[tracing::instrument(skip_all)]
pub async fn resume_subscriptions_for_creator(
    pool: &PgPool,
    creator_id: UserId,
) -> Result<Vec<DbSubscription>> {
    let subs = sqlx::query_as::<_, DbSubscription>(
        r#"
        UPDATE subscriptions SET paused_at = NULL
        WHERE project_id IN (SELECT id FROM projects WHERE user_id = $1)
          AND status = 'active'
          AND paused_at IS NOT NULL
        RETURNING *
        "#,
    )
    .bind(creator_id)
    .fetch_all(pool)
    .await?;

    Ok(subs)
}

// ── Access control ──

/// Check if a user has an active subscription to a project.
#[tracing::instrument(skip_all)]
pub async fn has_active_subscription_to_project(
    pool: &PgPool,
    user_id: UserId,
    project_id: ProjectId,
) -> Result<bool> {
    // Defense-in-depth on a missed/delayed `customer.subscription.deleted`
    // webhook: also reject when the current period has ended. status='active'
    // alone trusts Stripe to push the cancellation event promptly.
    let count: i64 = sqlx::query_scalar(
        "SELECT COUNT(*) FROM subscriptions \
         WHERE subscriber_id = $1 AND project_id = $2 AND status = 'active' AND paused_at IS NULL \
         AND (current_period_end IS NULL OR current_period_end > NOW())",
    )
    .bind(user_id)
    .bind(project_id)
    .fetch_one(pool)
    .await?;

    Ok(count > 0)
}

/// Get user subscriptions joined with project and tier data (for library display).
#[tracing::instrument(skip_all)]
pub async fn get_user_subscriptions_with_details(
    pool: &PgPool,
    user_id: UserId,
) -> Result<Vec<DbUserSubscriptionRow>> {
    let rows = sqlx::query_as::<_, DbUserSubscriptionRow>(
        "SELECT s.id, s.project_id, p.title AS project_title, p.slug AS project_slug,
                t.name AS tier_name, t.price_cents, s.status,
                s.current_period_end, s.stripe_subscription_id
         FROM subscriptions s
         JOIN projects p ON p.id = s.project_id
         JOIN subscription_tiers t ON t.id = s.tier_id
         WHERE s.subscriber_id = $1
         ORDER BY s.created_at DESC
         LIMIT 1000",
    )
    .bind(user_id)
    .fetch_all(pool)
    .await?;

    Ok(rows)
}

/// Get the number of active subscribers to a project (for dashboard display).
#[tracing::instrument(skip_all)]
pub async fn get_project_subscriber_count(
    pool: &PgPool,
    project_id: ProjectId,
) -> Result<i64> {
    let count: i64 = sqlx::query_scalar(
        "SELECT COUNT(*) FROM subscriptions WHERE project_id = $1 AND status = 'active' AND paused_at IS NULL",
    )
    .bind(project_id)
    .fetch_one(pool)
    .await?;

    Ok(count)
}

// ── Item-level subscriptions ──

/// Check if a user has an active subscription to a specific item.
#[tracing::instrument(skip_all)]
pub async fn has_active_subscription_to_item(
    pool: &PgPool,
    user_id: UserId,
    item_id: super::ItemId,
) -> Result<bool> {
    let count: i64 = sqlx::query_scalar(
        "SELECT COUNT(*) FROM subscriptions WHERE subscriber_id = $1 AND item_id = $2 AND status = 'active' AND paused_at IS NULL",
    )
    .bind(user_id)
    .bind(item_id)
    .fetch_one(pool)
    .await?;

    Ok(count > 0)
}

/// Get all item IDs that a user has active subscriptions to (for batch access checks).
#[tracing::instrument(skip_all)]
pub async fn get_user_subscribed_item_ids(
    pool: &PgPool,
    user_id: UserId,
) -> Result<Vec<super::ItemId>> {
    let item_ids: Vec<super::ItemId> = sqlx::query_scalar(
        "SELECT DISTINCT item_id FROM subscriptions WHERE subscriber_id = $1 AND status = 'active' AND paused_at IS NULL AND item_id IS NOT NULL",
    )
    .bind(user_id)
    .fetch_all(pool)
    .await?;

    Ok(item_ids)
}

// ── Export ──

/// Export all subscribers across a creator's projects.
///
/// Returns username, display_name, tier name, subscription status, and when.
#[tracing::instrument(skip_all)]
pub async fn get_project_subscribers_for_export(
    pool: &PgPool,
    user_id: UserId,
) -> Result<Vec<SubscriberExportRow>> {
    let rows = sqlx::query_as::<_, SubscriberExportRow>(
        r#"
        SELECT u.username, u.display_name, t.name AS tier_name, s.status, s.created_at
        FROM subscriptions s
        JOIN users u ON u.id = s.subscriber_id
        JOIN subscription_tiers t ON t.id = s.tier_id
        WHERE s.project_id IN (SELECT id FROM projects WHERE user_id = $1)
        ORDER BY s.created_at DESC
        "#,
    )
    .bind(user_id)
    .fetch_all(pool)
    .await?;

    Ok(rows)
}

/// Export all subscriptions across a creator's projects with full detail.
///
/// Returns project name, tier name, price, subscriber username, status,
/// billing period dates, and cancellation date.
#[tracing::instrument(skip_all)]
pub async fn get_subscriptions_for_export(
    pool: &PgPool,
    user_id: UserId,
) -> Result<Vec<SubscriptionExportRow>> {
    let rows = sqlx::query_as::<_, SubscriptionExportRow>(
        r#"
        SELECT p.title AS project_title, t.name AS tier_name, t.price_cents,
               u.username, s.status,
               s.current_period_start, s.current_period_end,
               s.canceled_at, s.created_at
        FROM subscriptions s
        JOIN users u ON u.id = s.subscriber_id
        JOIN subscription_tiers t ON t.id = s.tier_id
        JOIN projects p ON p.id = s.project_id
        WHERE s.project_id IN (SELECT id FROM projects WHERE user_id = $1)
        ORDER BY s.created_at DESC
        "#,
    )
    .bind(user_id)
    .fetch_all(pool)
    .await?;

    Ok(rows)
}

// ── Event log ──

/// Log a subscription webhook event for debugging and idempotency.
/// The UNIQUE index on stripe_event_id makes duplicate events a no-op.
#[tracing::instrument(skip_all)]
pub async fn log_subscription_event(
    pool: &PgPool,
    subscription_id: Option<SubscriptionId>,
    stripe_event_id: &str,
    event_type: &str,
    payload: &serde_json::Value,
) -> Result<()> {
    sqlx::query(
        r#"
        INSERT INTO subscription_events (subscription_id, stripe_event_id, event_type, payload)
        VALUES ($1, $2, $3, $4)
        ON CONFLICT (stripe_event_id) DO NOTHING
        "#,
    )
    .bind(subscription_id)
    .bind(stripe_event_id)
    .bind(event_type)
    .bind(payload)
    .execute(pool)
    .await?;

    Ok(())
}