import { PostgresJsDatabase } from 'drizzle-orm/postgres-js';
import { eq, and, sql, inArray } from 'drizzle-orm';
import { randomUUID } from 'crypto';
import { IFollowsRepository } from '../../domain/repositories/IFollowsRepository';
import { Follow } from '../../domain/Follow';
import { DID } from '../../domain/value-objects/DID';
import {
  FollowTargetType,
  FollowTargetTypeEnum,
} from '../../domain/value-objects/FollowTargetType';
import { follows } from './schema/follows.sql';
import { publishedRecords } from '../../../cards/infrastructure/repositories/schema/publishedRecord.sql';
import { PublishedRecordId } from '../../../cards/domain/value-objects/PublishedRecordId';
import { UniqueEntityID } from 'src/shared/domain/UniqueEntityID';
import { err, ok, Result } from 'src/shared/core/Result';

export class DrizzleFollowsRepository implements IFollowsRepository {
  constructor(private db: PostgresJsDatabase) {}

  async getFollowers(
    targetId: string,
    targetType: FollowTargetType,
  ): Promise<Result<Follow[]>> {
    try {
      const results = await this.db
        .select()
        .from(follows)
        .where(
          and(
            eq(follows.targetId, targetId),
            eq(follows.targetType, targetType.value),
          ),
        )
        .orderBy(sql`${follows.createdAt} DESC`);

      const followEntities: Follow[] = [];

      for (const row of results) {
        const followerDIDResult = DID.create(row.followerId);
        if (followerDIDResult.isErr()) {
          console.error(
            `Invalid follower DID: ${row.followerId}`,
            followerDIDResult.error,
          );
          continue;
        }

        const targetTypeResult = FollowTargetType.create(
          row.targetType as FollowTargetTypeEnum,
        );
        if (targetTypeResult.isErr()) {
          console.error(
            `Invalid target type: ${row.targetType}`,
            targetTypeResult.error,
          );
          continue;
        }

        const followResult = Follow.create(
          {
            followerId: followerDIDResult.value,
            targetId: row.targetId,
            targetType: targetTypeResult.value,
            createdAt: row.createdAt,
          },
          new UniqueEntityID(
            `${row.followerId}:${row.targetId}:${row.targetType}`,
          ),
        );

        if (followResult.isOk()) {
          followEntities.push(followResult.value);
        } else {
          console.error('Failed to create Follow entity:', followResult.error);
        }
      }

      return ok(followEntities);
    } catch (error: any) {
      return err(error);
    }
  }

  async getFollowersOfCollections(
    collectionIds: string[],
  ): Promise<Result<Follow[]>> {
    try {
      if (collectionIds.length === 0) {
        return ok([]);
      }

      const results = await this.db
        .select()
        .from(follows)
        .where(
          and(
            inArray(follows.targetId, collectionIds),
            eq(follows.targetType, FollowTargetTypeEnum.COLLECTION),
          ),
        )
        .orderBy(sql`${follows.createdAt} DESC`);

      const followEntities: Follow[] = [];

      for (const row of results) {
        const followerDIDResult = DID.create(row.followerId);
        if (followerDIDResult.isErr()) {
          console.error(
            `Invalid follower DID: ${row.followerId}`,
            followerDIDResult.error,
          );
          continue;
        }

        const targetTypeResult = FollowTargetType.create(
          row.targetType as FollowTargetTypeEnum,
        );
        if (targetTypeResult.isErr()) {
          console.error(
            `Invalid target type: ${row.targetType}`,
            targetTypeResult.error,
          );
          continue;
        }

        const followResult = Follow.create(
          {
            followerId: followerDIDResult.value,
            targetId: row.targetId,
            targetType: targetTypeResult.value,
            createdAt: row.createdAt,
          },
          new UniqueEntityID(
            `${row.followerId}:${row.targetId}:${row.targetType}`,
          ),
        );

        if (followResult.isOk()) {
          followEntities.push(followResult.value);
        } else {
          console.error('Failed to create Follow entity:', followResult.error);
        }
      }

      return ok(followEntities);
    } catch (error: any) {
      return err(error);
    }
  }

  async save(follow: Follow): Promise<Result<void>> {
    try {
      // Handle publishedRecordId persistence (similar to Card repository)
      let publishedRecordIdUuid: string | undefined;
      if (follow.publishedRecordId) {
        const publishedRecordId = follow.publishedRecordId.getValue();

        // Insert or get existing published record
        const existingRecord = await this.db
          .select()
          .from(publishedRecords)
          .where(
            and(
              eq(publishedRecords.uri, publishedRecordId.uri),
              eq(publishedRecords.cid, publishedRecordId.cid),
            ),
          )
          .limit(1);

        if (existingRecord.length > 0) {
          publishedRecordIdUuid = existingRecord[0]!.id;
        } else {
          const insertResult = await this.db
            .insert(publishedRecords)
            .values({
              id: randomUUID(),
              uri: publishedRecordId.uri,
              cid: publishedRecordId.cid,
            })
            .returning({ id: publishedRecords.id });
          publishedRecordIdUuid = insertResult[0]!.id;
        }
      }

      await this.db
        .insert(follows)
        .values({
          followerId: follow.followerId.value,
          targetId: follow.targetId,
          targetType: follow.targetType.value,
          publishedRecordId: publishedRecordIdUuid,
          createdAt: follow.createdAt,
        })
        .onConflictDoUpdate({
          target: [follows.followerId, follows.targetId, follows.targetType],
          set: {
            publishedRecordId: publishedRecordIdUuid,
          },
        });

      return ok(undefined);
    } catch (error) {
      return err(error as Error);
    }
  }

  async delete(
    followerId: string,
    targetId: string,
    targetType: FollowTargetType,
  ): Promise<Result<void>> {
    try {
      await this.db
        .delete(follows)
        .where(
          and(
            eq(follows.followerId, followerId),
            eq(follows.targetId, targetId),
            eq(follows.targetType, targetType.value),
          ),
        );

      return ok(undefined);
    } catch (error) {
      return err(error as Error);
    }
  }

  async findByFollowerAndTarget(
    followerId: string,
    targetId: string,
    targetType: FollowTargetType,
  ): Promise<Result<Follow | null>> {
    try {
      const results = await this.db
        .select({
          follow: follows,
          publishedRecord: publishedRecords,
        })
        .from(follows)
        .leftJoin(
          publishedRecords,
          eq(follows.publishedRecordId, publishedRecords.id),
        )
        .where(
          and(
            eq(follows.followerId, followerId),
            eq(follows.targetId, targetId),
            eq(follows.targetType, targetType.value),
          ),
        )
        .limit(1);

      if (results.length === 0) {
        return ok(null);
      }

      const row = results[0]!;
      const followerDid = DID.create(row.follow.followerId);
      if (followerDid.isErr()) {
        return err(followerDid.error);
      }

      const targetTypeVO = FollowTargetType.create(
        row.follow.targetType as any,
      );
      if (targetTypeVO.isErr()) {
        return err(targetTypeVO.error);
      }

      // Reconstruct publishedRecordId if present
      let publishedRecordId: PublishedRecordId | undefined;
      if (row.publishedRecord) {
        publishedRecordId = PublishedRecordId.create({
          uri: row.publishedRecord.uri,
          cid: row.publishedRecord.cid,
        });
      }

      return Follow.create(
        {
          followerId: followerDid.value,
          targetId: row.follow.targetId,
          targetType: targetTypeVO.value,
          publishedRecordId,
          createdAt: row.follow.createdAt,
        },
        new UniqueEntityID(
          `${row.follow.followerId}:${row.follow.targetId}:${row.follow.targetType}`,
        ),
      );
    } catch (error) {
      return err(error as Error);
    }
  }

  async getFollowing(
    followerId: string,
    targetType: FollowTargetType,
    options: { page: number; limit: number },
  ): Promise<Result<{ follows: Follow[]; totalCount: number }>> {
    try {
      const offset = (options.page - 1) * options.limit;

      // Get total count
      const countResult = await this.db
        .select({ count: sql<number>`count(*)::int` })
        .from(follows)
        .where(
          and(
            eq(follows.followerId, followerId),
            eq(follows.targetType, targetType.value),
          ),
        );

      const totalCount = countResult[0]?.count ?? 0;

      // Get paginated results
      const results = await this.db
        .select()
        .from(follows)
        .where(
          and(
            eq(follows.followerId, followerId),
            eq(follows.targetType, targetType.value),
          ),
        )
        .orderBy(sql`${follows.createdAt} DESC`)
        .limit(options.limit)
        .offset(offset);

      const followEntities: Follow[] = [];

      for (const row of results) {
        const followerDIDResult = DID.create(row.followerId);
        if (followerDIDResult.isErr()) {
          console.error(
            `Invalid follower DID: ${row.followerId}`,
            followerDIDResult.error,
          );
          continue;
        }

        const targetTypeResult = FollowTargetType.create(
          row.targetType as FollowTargetTypeEnum,
        );
        if (targetTypeResult.isErr()) {
          console.error(
            `Invalid target type: ${row.targetType}`,
            targetTypeResult.error,
          );
          continue;
        }

        const followResult = Follow.create(
          {
            followerId: followerDIDResult.value,
            targetId: row.targetId,
            targetType: targetTypeResult.value,
            createdAt: row.createdAt,
          },
          new UniqueEntityID(
            `${row.followerId}:${row.targetId}:${row.targetType}`,
          ),
        );

        if (followResult.isOk()) {
          followEntities.push(followResult.value);
        } else {
          console.error('Failed to create Follow entity:', followResult.error);
        }
      }

      return ok({ follows: followEntities, totalCount });
    } catch (error: any) {
      return err(error);
    }
  }

  async getFollowingCount(
    followerId: string,
    targetType?: FollowTargetType,
  ): Promise<Result<number>> {
    try {
      const conditions = [eq(follows.followerId, followerId)];

      if (targetType) {
        conditions.push(eq(follows.targetType, targetType.value));
      }

      const result = await this.db
        .select({ count: sql<number>`count(*)::int` })
        .from(follows)
        .where(and(...conditions));

      return ok(result[0]?.count ?? 0);
    } catch (error: any) {
      return err(error);
    }
  }

  async getFollowersCount(
    targetId: string,
    targetType: FollowTargetType,
  ): Promise<Result<number>> {
    try {
      const result = await this.db
        .select({ count: sql<number>`count(*)::int` })
        .from(follows)
        .where(
          and(
            eq(follows.targetId, targetId),
            eq(follows.targetType, targetType.value),
          ),
        );

      return ok(result[0]?.count ?? 0);
    } catch (error: any) {
      return err(error);
    }
  }

  async checkFollowingMultiple(
    followerId: string,
    targetIds: string[],
    targetType: FollowTargetType,
  ): Promise<Result<Map<string, boolean>>> {
    try {
      if (targetIds.length === 0) {
        return ok(new Map());
      }

      // Get all follows for this follower and the target IDs in one query
      const results = await this.db
        .select({ targetId: follows.targetId })
        .from(follows)
        .where(
          and(
            eq(follows.followerId, followerId),
            inArray(follows.targetId, targetIds),
            eq(follows.targetType, targetType.value),
          ),
        );

      // Build map with all targetIds initialized to false
      const followMap = new Map<string, boolean>();
      targetIds.forEach((id) => followMap.set(id, false));

      // Set true for targetIds that were found
      results.forEach((row) => followMap.set(row.targetId, true));

      return ok(followMap);
    } catch (error: any) {
      return err(error);
    }
  }

  async getProfileFollowCounts(userId: string): Promise<
    Result<{
      followerCount: number;
      followingCount: number;
      followedCollectionsCount: number;
    }>
  > {
    try {
      // Get all three counts in a single query using subqueries
      const result = await this.db.execute(sql`
        SELECT
          (SELECT COUNT(*)::int FROM ${follows} WHERE ${follows.targetId} = ${userId} AND ${follows.targetType} = 'USER') as follower_count,
          (SELECT COUNT(*)::int FROM ${follows} WHERE ${follows.followerId} = ${userId} AND ${follows.targetType} = 'USER') as following_count,
          (SELECT COUNT(*)::int FROM ${follows} WHERE ${follows.followerId} = ${userId} AND ${follows.targetType} = 'COLLECTION') as followed_collections_count
      `);

      const row = result[0] as {
        follower_count: number;
        following_count: number;
        followed_collections_count: number;
      };

      return ok({
        followerCount: row.follower_count ?? 0,
        followingCount: row.following_count ?? 0,
        followedCollectionsCount: row.followed_collections_count ?? 0,
      });
    } catch (error: any) {
      return err(error);
    }
  }
}
