import { BadRequestException, ForbiddenException, Injectable, NotFoundException } from "@nestjs/common";
import { and, desc, eq, gt, inArray, isNull, sql } from "drizzle-orm";
import { getDb, schema, withTenant, withUser } from "@slackwsh/data";
import { can } from "@slackwsh/core";
import type { Role } from "@slackwsh/contracts";
import { hashToken, generateToken } from "../auth/token.util";
import { EmailService } from "../auth/email.service";
import { StatusService } from "./status.service";

const INVITE_ACCEPT_WITH_NO_EMAIL_MATCH = "invite email does not match the signed-in account";

function normalizeEmail(email: string) {
  return email.trim().toLowerCase();
}

@Injectable()
export class WorkspacesService {
  constructor(private readonly email: EmailService, private readonly statuses: StatusService) {}

  async createWorkspace(ownerUserId: number, input: { name: string; slug: string }) {
    return withUser(ownerUserId, async (tx) => {
      const existingSlug = await tx.select().from(schema.workspaces).where(eq(schema.workspaces.slug, input.slug)).limit(1);
      if (existingSlug.length > 0) throw new BadRequestException("slug already taken");

      const [org] = await tx.insert(schema.organizations).values({ name: input.name }).returning();
      if (!org) throw new Error("organization insert returned no row");

      const [{ workspaceId }] = await tx.execute<{ workspaceId: number }>(
        sql`select nextval(pg_get_serial_sequence('workspaces', 'id'))::integer as "workspaceId"`,
      );
      await tx.execute(sql`select set_config('app.current_workspace_id', ${String(workspaceId)}, true)`);

      const [workspace] = await tx
        .insert(schema.workspaces)
        .values({ id: workspaceId, orgId: org.id, slug: input.slug, name: input.name })
        .returning();
      if (!workspace) throw new Error("workspace insert returned no row");

      // Membership insert is tenant-scoped — set workspace GUC now that we
      // know the identity-assigned id.
      await tx.insert(schema.workspaceMembers).values({
        workspaceId: workspace.id,
        userId: ownerUserId,
        role: "owner",
      });

      const defaults = await tx
        .insert(schema.channels)
        .values([
          { workspaceId: workspace.id, type: "public", name: "general", createdBy: ownerUserId, memberCount: 1 },
          { workspaceId: workspace.id, type: "public", name: "random", createdBy: ownerUserId, memberCount: 1 },
        ])
        .returning({ id: schema.channels.id });
      await tx.insert(schema.channelSeq).values(defaults.map((channel) => ({ channelId: channel.id, lastSeq: 0 })));
      await tx.insert(schema.channelMembers).values(
        defaults.map((channel) => ({ channelId: channel.id, userId: ownerUserId, role: "owner" })),
      );

      return workspace;
    });
  }

  async listMyWorkspaces(userId: number) {
    return withUser(userId, async (tx) => {
      const rows = await tx
        .select({ workspace: schema.workspaces, role: schema.workspaceMembers.role })
        .from(schema.workspaceMembers)
        .innerJoin(schema.workspaces, eq(schema.workspaces.id, schema.workspaceMembers.workspaceId))
        .where(eq(schema.workspaceMembers.userId, userId));

      return rows.map((r) => ({ ...r.workspace, role: r.role }));
    });
  }

  async getMembership(workspaceId: number, userId: number) {
    return withTenant({ workspaceId, userId }, async (tx) => {
      const [row] = await tx
        .select()
        .from(schema.workspaceMembers)
        .where(and(eq(schema.workspaceMembers.workspaceId, workspaceId), eq(schema.workspaceMembers.userId, userId)))
        .limit(1);
      return row ?? null;
    });
  }

  async listMembers(workspaceId: number, actorUserId: number) {
    await this.requireMembership(workspaceId, actorUserId);
    return withTenant({ workspaceId, userId: actorUserId }, async (tx) => {
      const rows = await tx
        .select({ member: schema.workspaceMembers, user: schema.users })
        .from(schema.workspaceMembers)
        .innerJoin(schema.users, eq(schema.users.id, schema.workspaceMembers.userId))
        .where(eq(schema.workspaceMembers.workspaceId, workspaceId));
      return this.statuses.resolveMemberRows(workspaceId, actorUserId, rows);
    });
  }

  /**
   * Invite by email. If that account already exists, create an in-app
   * notification so they can accept from their workspace picker — they are
   * not added as a member until they accept.
   */
  async createInvite(
    workspaceId: number,
    actorUserId: number,
    input: { email: string; role: Role; expiresInHours: number; maxUses: number },
  ) {
    const actor = await this.requireMembership(workspaceId, actorUserId);
    if (!can({ userId: actorUserId, role: actor.role }, "member:invite")) {
      throw new ForbiddenException("insufficient role to invite members");
    }

    const email = normalizeEmail(input.email);
    const db = getDb();
    const [existingUser] = await db
      .select()
      .from(schema.users)
      .where(sql`lower(${schema.users.email}) = ${email}`)
      .limit(1);

    const created = await withTenant({ workspaceId, userId: actorUserId }, async (tx) => {
      const [workspace] = await tx.select().from(schema.workspaces).where(eq(schema.workspaces.id, workspaceId)).limit(1);
      if (!workspace) throw new NotFoundException("workspace not found");

      const [actorUser] = await tx.select().from(schema.users).where(eq(schema.users.id, actorUserId)).limit(1);

      if (existingUser) {
        const [membership] = await tx
          .select()
          .from(schema.workspaceMembers)
          .where(
            and(
              eq(schema.workspaceMembers.workspaceId, workspaceId),
              eq(schema.workspaceMembers.userId, existingUser.id),
              isNull(schema.workspaceMembers.deactivatedAt),
            ),
          )
          .limit(1);
        if (membership) {
          throw new BadRequestException("that person is already a member of this workspace");
        }
      }

      const [openInvite] = await tx
        .select()
        .from(schema.invites)
        .where(
          and(
            eq(schema.invites.workspaceId, workspaceId),
            eq(schema.invites.email, email),
            isNull(schema.invites.usedAt),
            gt(schema.invites.expiresAt, new Date()),
            sql`${schema.invites.useCount} < ${schema.invites.maxUses}`,
          ),
        )
        .limit(1);

      let invite = openInvite;
      let token: string | undefined;

      if (!invite) {
        token = generateToken();
        const [created] = await tx
          .insert(schema.invites)
          .values({
            workspaceId,
            email,
            invitedByUserId: actorUserId,
            tokenHash: hashToken(token),
            role: input.role,
            expiresAt: new Date(Date.now() + input.expiresInHours * 3600 * 1000),
            maxUses: input.maxUses,
          })
          .returning();
        if (!created) throw new Error("invite insert returned no row");
        invite = created;
      }

      await this.email.sendInviteEmail(email, workspace.name, token ?? `(pending invite ${invite.id})`);

      return {
        id: invite.id,
        token,
        role: invite.role,
        expiresAt: invite.expiresAt,
        maxUses: invite.maxUses,
        userExists: Boolean(existingUser),
        notified: Boolean(existingUser),
        existingUserId: existingUser?.id ?? null,
        workspaceName: workspace.name,
        workspaceSlug: workspace.slug,
        invitedByName: actorUser?.name ?? null,
      };
    });

    if (created.existingUserId) {
      await withUser(created.existingUserId, async (tx) => {
        await tx.insert(schema.notifications).values({
          userId: created.existingUserId!,
          type: "workspace_invite",
          title: `Join ${created.workspaceName}`,
          body: `${created.invitedByName ?? "Someone"} invited you to join ${created.workspaceName} on Voxi.`,
          payload: {
            inviteId: created.id,
            workspaceId,
            workspaceName: created.workspaceName,
            workspaceSlug: created.workspaceSlug,
            invitedByUserId: actorUserId,
            invitedByName: created.invitedByName,
          },
        });
      });
    }

    return {
      id: created.id,
      token: created.token,
      role: created.role,
      expiresAt: created.expiresAt,
      maxUses: created.maxUses,
      userExists: created.userExists,
      notified: created.notified,
    };
  }

  async listPendingInvitesForEmail(userId: number, userEmail: string) {
    const email = normalizeEmail(userEmail);
    return withUser(userId, async (tx) => {
      const rows = await tx
        .select({
          invite: schema.invites,
          workspace: schema.workspaces,
          invitedBy: schema.users,
        })
        .from(schema.invites)
        .innerJoin(schema.workspaces, eq(schema.workspaces.id, schema.invites.workspaceId))
        .leftJoin(schema.users, eq(schema.users.id, schema.invites.invitedByUserId))
        .where(
          and(
            eq(schema.invites.email, email),
            isNull(schema.invites.usedAt),
            gt(schema.invites.expiresAt, new Date()),
            sql`${schema.invites.useCount} < ${schema.invites.maxUses}`,
          ),
        );

      return rows.map((row) => ({
        id: row.invite.id,
        workspaceId: row.workspace.id,
        workspaceName: row.workspace.name,
        workspaceSlug: row.workspace.slug,
        role: row.invite.role,
        invitedByName: row.invitedBy?.name ?? null,
        expiresAt: row.invite.expiresAt,
        createdAt: row.invite.createdAt,
      }));
    });
  }

  async listNotifications(userId: number) {
    return withUser(userId, async (tx) => {
      return tx
        .select()
        .from(schema.notifications)
        .where(eq(schema.notifications.userId, userId))
        .orderBy(desc(schema.notifications.createdAt))
        .limit(50);
    });
  }

  async markNotificationRead(userId: number, notificationId: number) {
    return withUser(userId, async (tx) => {
      await tx
        .update(schema.notifications)
        .set({ readAt: new Date() })
        .where(and(eq(schema.notifications.id, notificationId), eq(schema.notifications.userId, userId)));
      return { read: true };
    });
  }

  async acceptInvite(token: string, userId: number, userEmail: string) {
    const tokenHash = hashToken(token);
    const invite = await withUser(userId, async (tx) => {
      const [row] = await tx.select().from(schema.invites).where(eq(schema.invites.tokenHash, tokenHash)).limit(1);
      return row ?? null;
    });
    if (!invite) throw new NotFoundException("invite not found");
    return this.consumeInvite(invite, userId, userEmail);
  }

  async acceptInviteById(inviteId: number, userId: number, userEmail: string) {
    const invite = await withUser(userId, async (tx) => {
      const [row] = await tx.select().from(schema.invites).where(eq(schema.invites.id, inviteId)).limit(1);
      return row ?? null;
    });
    if (!invite) throw new NotFoundException("invite not found");
    return this.consumeInvite(invite, userId, userEmail);
  }

  async declineInviteById(inviteId: number, userId: number, userEmail: string) {
    return withUser(userId, async (tx) => {
      const [invite] = await tx.select().from(schema.invites).where(eq(schema.invites.id, inviteId)).limit(1);
      if (!invite) throw new NotFoundException("invite not found");
      if (!invite.email || normalizeEmail(invite.email) !== normalizeEmail(userEmail)) {
        throw new ForbiddenException(INVITE_ACCEPT_WITH_NO_EMAIL_MATCH);
      }
      if (invite.usedAt || invite.useCount >= invite.maxUses) {
        throw new BadRequestException("invite is no longer pending");
      }

      await tx
        .update(schema.invites)
        .set({ usedAt: new Date(), useCount: invite.maxUses })
        .where(eq(schema.invites.id, invite.id));

      await tx
        .update(schema.notifications)
        .set({ readAt: new Date() })
        .where(
          and(
            eq(schema.notifications.userId, userId),
            eq(schema.notifications.type, "workspace_invite"),
            sql`${schema.notifications.payload}->>'inviteId' = ${String(invite.id)}`,
            isNull(schema.notifications.readAt),
          ),
        );
      return { declined: true };
    });
  }

  private async consumeInvite(
    invite: typeof schema.invites.$inferSelect,
    userId: number,
    userEmail: string,
  ) {
    if (invite.expiresAt.getTime() < Date.now()) throw new BadRequestException("invite expired");
    if (invite.useCount >= invite.maxUses || invite.usedAt) throw new BadRequestException("invite has no remaining uses");
    if (invite.email && normalizeEmail(invite.email) !== normalizeEmail(userEmail)) {
      throw new ForbiddenException(INVITE_ACCEPT_WITH_NO_EMAIL_MATCH);
    }

    const workspace = await withTenant({ workspaceId: invite.workspaceId, userId }, async (tx) => {
      const existing = await tx
        .select()
        .from(schema.workspaceMembers)
        .where(and(eq(schema.workspaceMembers.workspaceId, invite.workspaceId), eq(schema.workspaceMembers.userId, userId)))
        .limit(1);
      if (existing.length === 0) {
        await tx.insert(schema.workspaceMembers).values({
          workspaceId: invite.workspaceId,
          userId,
          role: invite.role,
        });
      } else if (existing[0]?.deactivatedAt) {
        await tx
          .update(schema.workspaceMembers)
          .set({ deactivatedAt: null, role: invite.role })
          .where(and(eq(schema.workspaceMembers.workspaceId, invite.workspaceId), eq(schema.workspaceMembers.userId, userId)));
      }
      const defaultChannels = await tx
        .select({ id: schema.channels.id })
        .from(schema.channels)
        .where(
          and(
            eq(schema.channels.workspaceId, invite.workspaceId),
            eq(schema.channels.type, "public"),
            inArray(schema.channels.name, ["general", "random"]),
          ),
        );
      if (defaultChannels.length > 0) {
        const existingDefaults = await tx
          .select({ channelId: schema.channelMembers.channelId })
          .from(schema.channelMembers)
          .where(
            and(
              eq(schema.channelMembers.userId, userId),
              inArray(schema.channelMembers.channelId, defaultChannels.map((channel) => channel.id)),
            ),
          );
        const existingIds = new Set(existingDefaults.map((row) => row.channelId));
        const missing = defaultChannels.filter((channel) => !existingIds.has(channel.id));
        if (missing.length > 0) {
          await tx.insert(schema.channelMembers).values(
            missing.map((channel) => ({ channelId: channel.id, userId, role: "member" })),
          );
          for (const channel of missing) {
            await tx
              .update(schema.channels)
              .set({ memberCount: sql`${schema.channels.memberCount} + 1` })
              .where(eq(schema.channels.id, channel.id));
          }
        }
      }
      await tx
        .update(schema.invites)
        .set({ useCount: invite.useCount + 1, usedAt: new Date() })
        .where(eq(schema.invites.id, invite.id));
      const [ws] = await tx.select().from(schema.workspaces).where(eq(schema.workspaces.id, invite.workspaceId)).limit(1);
      return ws;
    });

    await this.markInviteNotificationsRead(userId, invite.id);
    return workspace;
  }

  private async markInviteNotificationsRead(userId: number, inviteId: number) {
    await withUser(userId, async (tx) => {
      await tx
        .update(schema.notifications)
        .set({ readAt: new Date() })
        .where(
          and(
            eq(schema.notifications.userId, userId),
            eq(schema.notifications.type, "workspace_invite"),
            sql`${schema.notifications.payload}->>'inviteId' = ${String(inviteId)}`,
            isNull(schema.notifications.readAt),
          ),
        );
    });
  }

  async changeMemberRole(workspaceId: number, actorUserId: number, targetUserId: number, role: Role) {
    const actor = await this.requireMembership(workspaceId, actorUserId);
    if (!can({ userId: actorUserId, role: actor.role }, "member:change_role")) {
      throw new ForbiddenException("insufficient role to change member roles");
    }
    return withTenant({ workspaceId, userId: actorUserId }, async (tx) => {
      await tx
        .update(schema.workspaceMembers)
        .set({ role })
        .where(and(eq(schema.workspaceMembers.workspaceId, workspaceId), eq(schema.workspaceMembers.userId, targetUserId)));
    });
  }

  async setMemberActive(workspaceId: number, actorUserId: number, targetUserId: number, active: boolean) {
    const actor = await this.requireMembership(workspaceId, actorUserId);
    if (!can({ userId: actorUserId, role: actor.role }, "member:remove")) {
      throw new ForbiddenException("insufficient role to deactivate members");
    }
    return withTenant({ workspaceId, userId: actorUserId }, async (tx) => {
      await tx
        .update(schema.workspaceMembers)
        .set({ deactivatedAt: active ? null : new Date() })
        .where(and(eq(schema.workspaceMembers.workspaceId, workspaceId), eq(schema.workspaceMembers.userId, targetUserId)));
    });
  }

  async transferOwnership(workspaceId: number, actorUserId: number, newOwnerUserId: number) {
    const actor = await this.requireMembership(workspaceId, actorUserId);
    if (actor.role !== "owner") throw new ForbiddenException("only the current owner may transfer ownership");
    if (newOwnerUserId === actorUserId) return;

    return withTenant({ workspaceId, userId: actorUserId }, async (tx) => {
      const [target] = await tx
        .select()
        .from(schema.workspaceMembers)
        .where(and(eq(schema.workspaceMembers.workspaceId, workspaceId), eq(schema.workspaceMembers.userId, newOwnerUserId)))
        .limit(1);
      if (!target) throw new NotFoundException("target user is not a member of this workspace");

      await tx
        .update(schema.workspaceMembers)
        .set({ role: "member" })
        .where(and(eq(schema.workspaceMembers.workspaceId, workspaceId), eq(schema.workspaceMembers.userId, actorUserId)));
      await tx
        .update(schema.workspaceMembers)
        .set({ role: "owner" })
        .where(and(eq(schema.workspaceMembers.workspaceId, workspaceId), eq(schema.workspaceMembers.userId, newOwnerUserId)));
    });
  }

  async updateSettings(
    workspaceId: number,
    actorUserId: number,
    input: { name?: string; retentionDays?: number | null },
  ) {
    const actor = await this.requireMembership(workspaceId, actorUserId);
    if (!can({ userId: actorUserId, role: actor.role }, "workspace:update_settings")) {
      throw new ForbiddenException("insufficient role to update workspace settings");
    }
    return withTenant({ workspaceId, userId: actorUserId }, async (tx) => {
      const patch: Record<string, unknown> = {};
      if (input.name !== undefined) patch.name = input.name;
      if (input.retentionDays !== undefined) patch.retentionDays = input.retentionDays;
      await tx.update(schema.workspaces).set(patch).where(eq(schema.workspaces.id, workspaceId));
    });
  }

  async adminOverview(workspaceId: number, actorUserId: number) {
    const actor = await this.requireMembership(workspaceId, actorUserId);
    if (!can({ userId: actorUserId, role: actor.role }, "workspace:update_settings")) {
      throw new ForbiddenException("admin access required");
    }
    return withTenant({ workspaceId, userId: actorUserId }, async (tx) => {
      const [workspace] = await tx.select().from(schema.workspaces).where(eq(schema.workspaces.id, workspaceId)).limit(1);
      const members = await tx
        .select({ member: schema.workspaceMembers, user: schema.users })
        .from(schema.workspaceMembers)
        .innerJoin(schema.users, eq(schema.users.id, schema.workspaceMembers.userId))
        .where(eq(schema.workspaceMembers.workspaceId, workspaceId));
      const invites = await tx
        .select()
        .from(schema.invites)
        .where(and(eq(schema.invites.workspaceId, workspaceId), isNull(schema.invites.usedAt)));
      const channels = await tx.select().from(schema.channels).where(eq(schema.channels.workspaceId, workspaceId));
      return { workspace, members, invites, channels };
    });
  }

  private async requireMembership(workspaceId: number, userId: number) {
    const membership = await this.getMembership(workspaceId, userId);
    if (!membership || membership.deactivatedAt) {
      throw new ForbiddenException("not an active member of this workspace");
    }
    return membership;
  }
}