import {
  Injectable,
  BadRequestException,
  ConflictException,
  HttpException,
  HttpStatus,
} from '@nestjs/common';
import { Kysely, sql } from 'kysely';
import { DB } from '@/database/database.type';
import { Logger } from 'pino-nestjs';
import crypto from 'crypto';
import { RewardEngineService } from './reward-engine.service';
import { NotificationsService } from '../notifications/notifications.service';

type ReferralStatus = 'PENDING' | 'VALID' | 'REWARDED' | 'REJECTED' | 'FRAUD';
type ShareStatus = 'PENDING' | 'VALID' | 'REWARDED' | 'REJECTED' | 'FRAUD';

type ReferralContext = { ip?: string | null; userAgent?: string | null; deviceId?: string | null };
type ShareLogContext = {
  ip?: string | null;
  userAgent?: string | null;
  deviceId?: string | null;
  shareIntentToken?: string | null;
  shareDurationMs?: number | null;
};
type ReferralRiskResult = { score: number; reasons: string[]; sameDevice: boolean };

const REFERRAL_WAIT_HOURS = 24;
const REFERRAL_MIN_ACCOUNT_AGE_HOURS = 24;
const REFERRAL_SUSPICIOUS_THRESHOLD = 30;
const REFERRAL_FRAUD_THRESHOLD = 60;
const REFERRAL_EXPIRY_DAYS = 14;

const SHARE_TARGET = 20;
const SHARE_COOLDOWN_SECONDS = 45;
const SHARE_SUSPICIOUS_THRESHOLD = 30;
const SHARE_FRAUD_THRESHOLD = 60;
const SHARE_PENDING_REVIEW_HOURS = 2;

const MAX_REWARDED_ACCOUNTS_PER_DEVICE = 2;

const REFERRAL_MILESTONES = [
  { count: 1, days: 3 },
  { count: 3, days: 7 },
  { count: 5, days: 14 },
  { count: 10, days: 30 },
] as const;

const VALID_PLATFORMS = ['instagram', 'facebook', 'tiktok'] as const;

const UUID_REGEX = /^[0-9a-f]{8}-[0-9a-f]{4}-[1-5][0-9a-f]{3}-[89ab][0-9a-f]{3}-[0-9a-f]{12}$/i;

function normalizeDeviceId(value?: string | null): string | null {
  if (!value) return null;
  const trimmed = String(value).trim();
  if (!trimmed || !UUID_REGEX.test(trimmed)) return null;
  return trimmed;
}

function normalizeIp(rawIp?: string | null): string | null {
  if (!rawIp) return null;
  const trimmed = String(rawIp).trim();
  if (!trimmed) return null;
  return trimmed.replace('::ffff:', '');
}

function maskEmail(email?: string | null): string {
  const value = String(email || '')
    .trim()
    .toLowerCase();
  const [local, domain] = value.split('@');
  if (!local || !domain) return '';
  const safeLocal = local.length <= 2 ? `${local[0] || '*'}*` : `${local.slice(0, 2)}***`;
  return `${safeLocal}@${domain}`;
}

function hashIp(ip?: string | null): string | null {
  const normalized = normalizeIp(ip);
  if (!normalized) return null;
  const salt = process.env.REFERRAL_IP_SALT || 'fur-finder-referral-salt';
  return crypto.createHash('sha256').update(`${salt}:${normalized}`).digest('hex');
}

function monthKeyUtc(date: Date): string {
  const y = date.getUTCFullYear();
  const m = String(date.getUTCMonth() + 1).padStart(2, '0');
  return `${y}-${m}`;
}

function getUtcMonthStart(date: Date): Date {
  return new Date(Date.UTC(date.getUTCFullYear(), date.getUTCMonth(), 1));
}

function toHoursAgo(timestamp: Date): number {
  return (Date.now() - timestamp.getTime()) / (1000 * 60 * 60);
}

@Injectable()
export class ReferralsService {
  constructor(
    private readonly db: Kysely<DB>,
    private readonly logger: Logger,
    private readonly rewardEngine: RewardEngineService,
    private readonly notificationsService: NotificationsService,
  ) {}

  private async evaluateReferralRisk(params: {
    referrerUserId: string;
    signupDeviceId: string | null;
    signupIpHash: string | null;
  }): Promise<ReferralRiskResult> {
    let score = 0;
    const reasons: string[] = [];
    let sameDevice = false;

    if (params.signupDeviceId) {
      const deviceOwner = await sql<{ user_id: string }>`
        SELECT user_id FROM devices WHERE device_id = ${params.signupDeviceId} AND deleted_at IS NULL LIMIT 1
      `.execute(this.db);

      if (
        deviceOwner.rows.length > 0 &&
        String(deviceOwner.rows[0].user_id) === params.referrerUserId
      ) {
        score += 50;
        reasons.push('same_device');
        sameDevice = true;
      }

      const rewardedByDevice = await sql<{ count: number }>`
        SELECT COUNT(*)::int AS count FROM referral_attributions
        WHERE signup_device_id = ${params.signupDeviceId} AND status IN ('VALID', 'REWARDED') AND deleted_at IS NULL
      `.execute(this.db);
      if (Number(rewardedByDevice.rows[0]?.count || 0) >= MAX_REWARDED_ACCOUNTS_PER_DEVICE) {
        score += 30;
        reasons.push('multiple_rewarded_accounts_device');
      }

      const todayCount = await sql<{ count: number }>`
        SELECT COUNT(*)::int AS count FROM referral_attributions
        WHERE signup_device_id = ${params.signupDeviceId} AND created_at >= date_trunc('day', NOW()) AND deleted_at IS NULL
      `.execute(this.db);
      if (Number(todayCount.rows[0]?.count || 0) >= 3) {
        score += 30;
        reasons.push('multiple_accounts_device_day');
      }
    }

    if (params.signupIpHash) {
      const sameIpCount = await sql<{ count: number }>`
        SELECT COUNT(*)::int AS count FROM referral_attributions
        WHERE signup_ip_hash = ${params.signupIpHash} AND created_at >= NOW() - INTERVAL '24 hours' AND deleted_at IS NULL
      `.execute(this.db);
      if (Number(sameIpCount.rows[0]?.count || 0) >= 3) {
        score += 20;
        reasons.push('suspicious_ip_cluster');
      }
    }

    return { score, reasons, sameDevice };
  }

  private async finalizeReferralStatus(
    referralId: string,
    status: ReferralStatus,
    input: {
      fraudScore?: number;
      fraudReasons?: string[];
      validationReason: string;
      meaningfulActionAt?: Date | null;
      markValidated?: boolean;
      markRewarded?: boolean;
    },
  ) {
    await sql`
      UPDATE referral_attributions
      SET status = ${status},
          fraud_score = ${input.fraudScore ?? 0},
          fraud_reasons = ${JSON.stringify(input.fraudReasons || [])}::jsonb,
          validation_reason = ${input.validationReason},
          meaningful_action_at = COALESCE(${input.meaningfulActionAt || null}, meaningful_action_at),
          validated_at = CASE WHEN ${!!input.markValidated} THEN NOW() ELSE validated_at END,
          rewarded_at = CASE WHEN ${!!input.markRewarded} THEN NOW() ELSE rewarded_at END,
          last_checked_at = NOW(),
          updated_at = NOW()
      WHERE id = ${referralId} AND deleted_at IS NULL
    `.execute(this.db);
  }

  private async countValidReferrals(referrerUserId: string): Promise<number> {
    const result = await sql<{ count: number }>`
      SELECT COUNT(*)::int AS count FROM referral_attributions
      WHERE referrer_user_id = ${referrerUserId} AND status IN ('VALID', 'REWARDED') AND deleted_at IS NULL
    `.execute(this.db);
    return Number(result.rows[0]?.count || 0);
  }

  private async applyReferralRewards(referral: {
    id: string;
    referrer_user_id: string;
    referee_user_id: string;
  }) {
    await this.rewardEngine.grantReward({
      userId: referral.referee_user_id,
      rewardKey: `referral-referee:${referral.id}`,
      rewardType: 'referral_referee_bonus',
      days: 3,
      reason: 'Valid referral signup reward',
      metadata: { referralId: referral.id, role: 'referee' },
      sourceEventId: referral.id,
    });

    const validCount = (await this.countValidReferrals(referral.referrer_user_id)) + 1;
    for (const milestone of REFERRAL_MILESTONES) {
      if (validCount >= milestone.count) {
        await this.rewardEngine.grantReward({
          userId: referral.referrer_user_id,
          rewardKey: `referral-milestone:${referral.referrer_user_id}:${milestone.count}`,
          rewardType: 'referral_milestone',
          days: milestone.days,
          reason: `Referral milestone reached: ${milestone.count} valid referrals`,
          metadata: { referralId: referral.id, milestone: milestone.count },
          sourceEventId: referral.id,
        });
      }
    }
  }

  private async processSingleReferral(referralId: string): Promise<void> {
    let referralRow: any = null;

    // Lock + early-exit checks inside a short transaction (mirrors Express lock client).
    await this.db.transaction().execute(async (trx) => {
      const locked = await sql<any>`
        SELECT ra.id, ra.referrer_user_id, ra.referee_user_id, ra.status, ra.wait_until, ra.created_at,
               ra.signup_device_id, ra.signup_ip_hash,
               referee.email_verified_at, referee.created_at AS referee_created_at,
               referee.deleted_at AS referee_deleted_at, referee.role AS referee_role,
               referrer.deleted_at AS referrer_deleted_at
        FROM referral_attributions ra
        JOIN users referee ON referee.id = ra.referee_user_id
        JOIN users referrer ON referrer.id = ra.referrer_user_id
        WHERE ra.id = ${referralId} AND ra.deleted_at IS NULL
        FOR UPDATE
      `.execute(trx);

      if (locked.rows.length === 0) return;

      const row = locked.rows[0];
      if (row.status !== 'PENDING') return;

      const waitUntil = new Date(row.wait_until);
      if (waitUntil.getTime() > Date.now()) {
        await sql`UPDATE referral_attributions SET last_checked_at = NOW(), updated_at = NOW() WHERE id = ${referralId}`.execute(
          trx,
        );
        return;
      }

      referralRow = row;
    });

    if (!referralRow) return;

    if (
      referralRow.referrer_deleted_at ||
      referralRow.referee_deleted_at ||
      referralRow.referee_role === 'banned'
    ) {
      await this.finalizeReferralStatus(referralId, 'REJECTED', {
        validationReason: 'Referrer/referee is unavailable or banned',
      });
      return;
    }

    const refereeCreatedAt = new Date(referralRow.referee_created_at);
    const accountAgeHours = toHoursAgo(refereeCreatedAt);
    const emailVerified = !!referralRow.email_verified_at;

    const risk = await this.evaluateReferralRisk({
      referrerUserId: referralRow.referrer_user_id,
      signupDeviceId: referralRow.signup_device_id || null,
      signupIpHash: referralRow.signup_ip_hash || null,
    });

    if (risk.score >= REFERRAL_FRAUD_THRESHOLD) {
      await this.finalizeReferralStatus(referralId, 'FRAUD', {
        fraudScore: risk.score,
        fraudReasons: risk.reasons,
        validationReason: 'Fraud score threshold exceeded',
      });
      return;
    }

    const missingChecks: string[] = [];
    if (!emailVerified) missingChecks.push('email_not_verified');
    if (accountAgeHours < REFERRAL_MIN_ACCOUNT_AGE_HOURS) missingChecks.push('account_age_under_24h');
    if (risk.sameDevice) missingChecks.push('same_device_referrer_referee');

    if (missingChecks.length > 0 || risk.score >= REFERRAL_SUSPICIOUS_THRESHOLD) {
      const referralAgeDays =
        (Date.now() - new Date(referralRow.created_at).getTime()) / (1000 * 60 * 60 * 24);
      const shouldReject =
        referralAgeDays >= REFERRAL_EXPIRY_DAYS &&
        (missingChecks.includes('email_not_verified') || risk.score >= REFERRAL_SUSPICIOUS_THRESHOLD);

      const pendingReason = missingChecks.includes('email_not_verified')
        ? 'PENDING_EMAIL_VERIFICATION'
        : missingChecks.includes('account_age_under_24h')
          ? 'PENDING_ACCOUNT_24H'
          : `Awaiting validation: ${[...missingChecks, ...risk.reasons].join(',') || 'pending checks'}`;

      await this.finalizeReferralStatus(referralId, shouldReject ? 'REJECTED' : 'PENDING', {
        fraudScore: risk.score,
        fraudReasons: risk.reasons,
        validationReason: shouldReject
          ? `Referral expired before validation: ${missingChecks.join(',') || 'suspicious'}`
          : pendingReason,
      });
      return;
    }

    await this.finalizeReferralStatus(referralId, 'VALID', {
      fraudScore: risk.score,
      fraudReasons: risk.reasons,
      validationReason: 'Referral passed validation checks',
      markValidated: true,
    });

    await this.applyReferralRewards({
      id: referralRow.id,
      referrer_user_id: referralRow.referrer_user_id,
      referee_user_id: referralRow.referee_user_id,
    });

    await this.finalizeReferralStatus(referralId, 'REWARDED', {
      fraudScore: risk.score,
      fraudReasons: risk.reasons,
      validationReason: 'Referral validated and rewards processed',
      markValidated: true,
      markRewarded: true,
    });
  }

  async processReferralValidationForUser(userId: string): Promise<void> {
    const pending = await sql<{ id: string }>`
      SELECT id FROM referral_attributions
      WHERE deleted_at IS NULL AND status = 'PENDING' AND wait_until <= NOW()
        AND (referrer_user_id = ${userId} OR referee_user_id = ${userId})
      ORDER BY created_at ASC LIMIT 50
    `.execute(this.db);

    for (const row of pending.rows) {
      await this.processSingleReferral(String(row.id));
    }
  }

  async createPendingReferralFromSignup(input: {
    referrerUserId: string;
    refereeUserId: string;
    referralCodeUsed?: string | null;
    context?: ReferralContext;
  }): Promise<void> {
    if (!input.referrerUserId || !input.refereeUserId) return;
    if (input.referrerUserId === input.refereeUserId) return;

    const exists = await sql<{ id: string }>`
      SELECT id FROM referral_attributions WHERE referee_user_id = ${input.refereeUserId} AND deleted_at IS NULL LIMIT 1
    `.execute(this.db);
    if (exists.rows.length > 0) return;

    const waitUntil = new Date(Date.now() + REFERRAL_WAIT_HOURS * 60 * 60 * 1000);
    const inserted = await sql<{ id: string }>`
      INSERT INTO referral_attributions
        (referrer_user_id, referee_user_id, referral_code_used, status, wait_until, signup_ip_hash, signup_device_id, signup_user_agent, created_at, updated_at)
      VALUES (${input.referrerUserId}, ${input.refereeUserId}, ${(input.referralCodeUsed || '').toUpperCase() || null}, 'PENDING', ${waitUntil.toISOString()}, ${hashIp(input.context?.ip)}, ${normalizeDeviceId(input.context?.deviceId)}, ${input.context?.userAgent || null}, NOW(), NOW())
      RETURNING id
    `.execute(this.db);

    const referralId = String(inserted.rows[0]?.id || '');
    if (referralId) {
      try {
        await this.notificationsService.createReferralSignupNotification({
          referrerUserId: input.referrerUserId,
          referralId,
        });
      } catch (e) {
        this.logger.error('Failed to create referral signup notification', e);
      }
    }
  }

  private async processPendingSharesForUser(userId: string): Promise<void> {
    const pending = await sql<any>`
      SELECT id, fraud_score, share_intent_token, share_duration_ms, created_at
      FROM social_shares
      WHERE user_id = ${userId} AND status = 'PENDING' AND deleted_at IS NULL
        AND shared_date >= ${getUtcMonthStart(new Date()).toISOString()}
      ORDER BY created_at ASC LIMIT 50
    `.execute(this.db);

    for (const share of pending.rows) {
      const createdAt = new Date(share.created_at);
      const ageHours = toHoursAgo(createdAt);
      if (ageHours < SHARE_PENDING_REVIEW_HOURS) continue;

      const fraudScore = Number(share.fraud_score || 0);
      const hasIntent = !!String(share.share_intent_token || '').trim();
      const duration = Number(share.share_duration_ms || 0);

      if (fraudScore >= SHARE_FRAUD_THRESHOLD) {
        await sql`UPDATE social_shares SET status = 'FRAUD', validation_reason = 'Fraud threshold exceeded during delayed review', validated_at = NOW() WHERE id = ${share.id}`.execute(
          this.db,
        );
        continue;
      }

      if (hasIntent && duration >= 2000) {
        await sql`UPDATE social_shares SET status = 'VALID', validation_reason = 'Delayed validation passed', validated_at = NOW() WHERE id = ${share.id}`.execute(
          this.db,
        );
        continue;
      }

      await sql`UPDATE social_shares SET status = 'REJECTED', validation_reason = 'Insufficient evidence after delayed review', validated_at = NOW() WHERE id = ${share.id}`.execute(
        this.db,
      );
    }
  }

  private async maybeGrantShareReward(
    userId: string,
  ): Promise<{ rewardEarned: boolean; validShares: number }> {
    const startOfMonth = getUtcMonthStart(new Date());
    const countResult = await sql<{ count: number }>`
      SELECT COUNT(*)::int AS count FROM social_shares
      WHERE user_id = ${userId} AND shared_date >= ${startOfMonth.toISOString()}
        AND status IN ('VALID', 'REWARDED') AND COALESCE(share_intent_token, '') <> ''
        AND COALESCE(share_duration_ms, 0) >= 1500 AND deleted_at IS NULL
    `.execute(this.db);

    const validShares = Number(countResult.rows[0]?.count || 0);
    let rewardEarned = false;

    if (validShares >= SHARE_TARGET) {
      const now = new Date();
      const result = await this.rewardEngine.grantReward({
        userId,
        rewardKey: `share-monthly:${userId}:${monthKeyUtc(now)}`,
        rewardType: 'ambassador',
        days: 7,
        reason: 'Social ambassador reward for 20 valid shares in one month',
        metadata: { month: monthKeyUtc(now), validShares },
      });

      rewardEarned = result.granted;

      if (result.granted || result.duplicate) {
        await sql`
          UPDATE social_shares SET status = CASE WHEN status = 'VALID' THEN 'REWARDED' ELSE status END
          WHERE id = (
            SELECT id FROM social_shares
            WHERE user_id = ${userId} AND shared_date >= ${startOfMonth.toISOString()}
              AND status IN ('VALID', 'REWARDED') AND COALESCE(share_intent_token, '') <> ''
              AND COALESCE(share_duration_ms, 0) >= 1500 AND deleted_at IS NULL
            ORDER BY created_at DESC LIMIT 1
          )
        `.execute(this.db);
      }
    }

    return { rewardEarned, validShares };
  }

  async logShare(user_id: string, platformRaw: string, context?: ShareLogContext) {
    const platform = String(platformRaw || '')
      .trim()
      .toLowerCase();
    if (!VALID_PLATFORMS.includes(platform as (typeof VALID_PLATFORMS)[number])) {
      throw new BadRequestException('Invalid platform');
    }

    const lastShare = await sql<{ created_at: string }>`
      SELECT created_at FROM social_shares WHERE user_id = ${user_id} AND deleted_at IS NULL ORDER BY created_at DESC LIMIT 1
    `.execute(this.db);

    if (lastShare.rows.length > 0) {
      const elapsedSec = (Date.now() - new Date(lastShare.rows[0].created_at).getTime()) / 1000;
      if (elapsedSec < SHARE_COOLDOWN_SECONDS) {
        throw new HttpException(
          {
            status: false,
            statusCode: 429,
            message: `Please wait ${Math.ceil(SHARE_COOLDOWN_SECONDS - elapsedSec)}s before logging another share`,
          },
          HttpStatus.TOO_MANY_REQUESTS,
        );
      }
    }

    const ipHash = hashIp(context?.ip);
    const deviceId = normalizeDeviceId(context?.deviceId);
    const shareIntentToken = String(context?.shareIntentToken || '').trim() || null;
    const shareDurationMs =
      context?.shareDurationMs == null
        ? null
        : Math.max(0, Math.floor(Number(context.shareDurationMs)));

    let score = 0;
    const reasons: string[] = [];
    let qualityScore = 0;

    if (!shareIntentToken) {
      score += 20;
      reasons.push('missing_share_intent_token');
    } else {
      qualityScore += 10;
    }

    if (shareDurationMs == null || shareDurationMs < 1500) {
      score += 20;
      reasons.push('share_duration_too_short');
    } else if (shareDurationMs >= 4000) {
      qualityScore += 10;
    }

    if (!deviceId) {
      score += 10;
      reasons.push('missing_device_id');
    } else {
      const deviceUsersToday = await sql<{ count: number }>`
        SELECT COUNT(DISTINCT user_id)::int AS count FROM social_shares
        WHERE device_id = ${deviceId} AND shared_date = CURRENT_DATE AND deleted_at IS NULL
      `.execute(this.db);
      if (Number(deviceUsersToday.rows[0]?.count || 0) >= 3) {
        score += 30;
        reasons.push('multi_account_device_sharing');
      }
    }

    if (ipHash) {
      const usersOnIpToday = await sql<{ count: number }>`
        SELECT COUNT(DISTINCT user_id)::int AS count FROM social_shares
        WHERE ip_hash = ${ipHash} AND shared_date = CURRENT_DATE AND deleted_at IS NULL
      `.execute(this.db);
      if (Number(usersOnIpToday.rows[0]?.count || 0) >= 5) {
        score += 20;
        reasons.push('suspicious_ip_cluster');
      }
    }

    const repeatedPattern = await sql<{ count: number }>`
      SELECT COUNT(*)::int AS count FROM social_shares
      WHERE user_id = ${user_id} AND platform = ${platform}
        AND created_at >= NOW() - INTERVAL '7 days'
        AND EXTRACT(HOUR FROM created_at) = EXTRACT(HOUR FROM NOW())
        AND EXTRACT(MINUTE FROM created_at) = EXTRACT(MINUTE FROM NOW())
        AND deleted_at IS NULL
    `.execute(this.db);
    if (Number(repeatedPattern.rows[0]?.count || 0) >= 5) {
      score += 15;
      reasons.push('repeated_time_pattern');
    }

    let status: ShareStatus = 'VALID';
    let validationReason = 'Share passed immediate validation';
    let validatedAt: string | null = new Date().toISOString();

    if (score >= SHARE_FRAUD_THRESHOLD) {
      status = 'FRAUD';
      validationReason = 'Fraud score threshold exceeded';
    } else if (score >= SHARE_SUSPICIOUS_THRESHOLD) {
      status = 'PENDING';
      validationReason = 'Share flagged for delayed validation';
      validatedAt = null;
    }

    try {
      await sql`
        INSERT INTO social_shares
          (user_id, platform, shared_date, created_at, status, fraud_score, fraud_reasons, validation_reason, validated_at, device_id, ip_hash, share_duration_ms, share_intent_token, activity_quality_score)
        VALUES (${user_id}, ${platform}, CURRENT_DATE, NOW(), ${status}, ${score}, ${JSON.stringify(reasons)}::jsonb, ${validationReason}, ${validatedAt}, ${deviceId}, ${ipHash}, ${shareDurationMs}, ${shareIntentToken}, ${qualityScore})
      `.execute(this.db);
    } catch (e: any) {
      if (e?.code === '23505') {
        throw new ConflictException('You already logged a share on this platform today');
      }
      throw e;
    }

    await this.processPendingSharesForUser(user_id);
    const reward = await this.maybeGrantShareReward(user_id);

    return {
      status: true,
      statusCode: 200,
      data: {
        message: 'Share logged',
        shareStatus: status,
        validationReason,
        fraudScore: score,
        sharesThisMonth: reward.validShares,
        rewardEarned: reward.rewardEarned,
      },
    };
  }

  async getReferralData(user_id: string) {
    await this.processReferralValidationForUser(user_id);
    await this.processPendingSharesForUser(user_id);

    const userResult = await sql<{ referral_code: string; premium_until: string | null }>`
      SELECT referral_code, premium_until FROM users WHERE id = ${user_id} AND deleted_at IS NULL
    `.execute(this.db);
    const referralCode = userResult.rows[0]?.referral_code || '';
    const premiumUntil = userResult.rows[0]?.premium_until;

    const referralCount = await sql<{ count: number }>`
      SELECT COUNT(*)::int AS count FROM referral_attributions
      WHERE referrer_user_id = ${user_id} AND status IN ('VALID', 'REWARDED') AND deleted_at IS NULL
    `.execute(this.db);

    const pendingReferrals = await sql<{ count: number }>`
      SELECT COUNT(*)::int AS count FROM referral_attributions
      WHERE referrer_user_id = ${user_id} AND status = 'PENDING' AND deleted_at IS NULL
    `.execute(this.db);

    const referralMembers = await sql<any>`
      SELECT ra.id,
             COALESCE(NULLIF(TRIM(referee.display_name), ''), split_part(referee.email, '@', 1), 'User') AS name,
             referee.email AS email,
             referee.created_at AS "joinedAt",
             referee.email_verified_at AS "emailVerifiedAt",
             (referee.email_verified_at IS NOT NULL) AS "isEmailVerified",
             ra.status,
             ra.validation_reason AS "validationReason"
      FROM referral_attributions ra
      INNER JOIN users referee ON referee.id = ra.referee_user_id
      WHERE ra.referrer_user_id = ${user_id} AND ra.deleted_at IS NULL AND referee.deleted_at IS NULL
      ORDER BY referee.created_at DESC, ra.created_at DESC LIMIT 100
    `.execute(this.db);

    const rewardsResult = await sql<any>`
      SELECT CASE
               WHEN reward_type = 'ambassador' THEN 'ambassador'
               WHEN reward_type LIKE 'referral_%' THEN 'referral'
               ELSE reward_type
             END AS type,
             granted_days as "daysAwarded", reason, granted_at as "created_at"
      FROM reward_grants
      WHERE user_id = ${user_id} AND status = 'GRANTED' AND deleted_at IS NULL
      ORDER BY granted_at DESC
    `.execute(this.db);

    const startOfMonth = getUtcMonthStart(new Date());

    const validSharesResult = await sql<{ count: number }>`
      SELECT COUNT(*)::int AS count FROM social_shares
      WHERE user_id = ${user_id} AND shared_date >= ${startOfMonth.toISOString()}
        AND status IN ('VALID', 'REWARDED') AND COALESCE(share_intent_token, '') <> ''
        AND COALESCE(share_duration_ms, 0) >= 1500 AND deleted_at IS NULL
    `.execute(this.db);

    const loggedSharesResult = await sql<{ count: number }>`
      SELECT COUNT(*)::int AS count FROM social_shares
      WHERE user_id = ${user_id} AND shared_date >= ${startOfMonth.toISOString()}
        AND status IN ('PENDING', 'VALID', 'REWARDED') AND deleted_at IS NULL
    `.execute(this.db);

    const sharesResult = await sql<any>`
      SELECT platform, shared_date as "date", status, fraud_score as "fraudScore", validation_reason as "validationReason"
      FROM social_shares
      WHERE user_id = ${user_id} AND shared_date >= ${startOfMonth.toISOString()} AND status != 'REJECTED' AND deleted_at IS NULL
      ORDER BY shared_date DESC, created_at DESC
    `.execute(this.db);

    return {
      status: true,
      statusCode: 200,
      data: {
        referralCode,
        referralCount: Number(referralCount.rows[0]?.count || 0),
        pendingReferralCount: Number(pendingReferrals.rows[0]?.count || 0),
        premiumUntil: premiumUntil ? new Date(premiumUntil).toISOString() : null,
        rewards: rewardsResult.rows,
        referrals: referralMembers.rows.map((row: any) => ({
          id: row.id,
          name: row.name,
          emailMasked: maskEmail(row.email),
          joinedAt: row.joinedAt ? new Date(row.joinedAt).toISOString() : null,
          isEmailVerified: !!row.isEmailVerified,
          emailVerifiedAt: row.emailVerifiedAt ? new Date(row.emailVerifiedAt).toISOString() : null,
          status: row.status,
          validationReason: row.validationReason || null,
        })),
        sharesThisMonth: Number(validSharesResult.rows[0]?.count || 0),
        sharesLoggedThisMonth: Number(loggedSharesResult.rows[0]?.count || 0),
        shares: sharesResult.rows,
      },
    };
  }
}