import { desc, eq, sql } from "drizzle-orm";

import { listAuditLogs } from "@/modules/core/server/audit";
import { getDatabase } from "@/modules/core/server/db";
import {
  accounts,
  admissions,
  beds,
  elders,
  joinRequests,
  memberships,
  organizationInvitations,
  organizations,
  roles,
  rooms,
  sessions,
  systemSettings,
  systemIncidents,
} from "@/modules/core/server/schema";
import type {
  Account,
  Admission,
  AppData,
  FacilityBed,
  JoinRequest,
  Membership,
  Organization,
  OrganizationInvitation,
  Room,
  Session,
  SystemIncident,
} from "@/modules/core/types";
import type { Elder } from "@/modules/elders/types";

function iso(value: Date): string {
  return value.toISOString();
}

export async function readData(): Promise<AppData> {
  const database = getDatabase();
  const [
    organizationRows,
    accountRows,
    membershipRows,
    joinRequestRows,
    invitationRows,
    sessionRows,
    elderRows,
    roomRows,
    bedRows,
    admissionRows,
    auditLogRows,
    incidentRows,
    systemSettingsRows,
  ] = await Promise.all([
    database.select().from(organizations).orderBy(desc(organizations.createdAt)),
    database.select().from(accounts).orderBy(desc(accounts.createdAt)),
    database.select({ membership: memberships, role: roles }).from(memberships).innerJoin(roles, eq(roles.id, memberships.roleId)),
    database
      .select({
        request: joinRequests,
        accountName: accounts.name,
        accountEmail: accounts.email,
        organizationName: organizations.name,
      })
      .from(joinRequests)
      .innerJoin(accounts, eq(accounts.id, joinRequests.accountId))
      .innerJoin(organizations, eq(organizations.id, joinRequests.organizationId))
      .orderBy(desc(joinRequests.createdAt)),
    database
      .select({
        invitation: organizationInvitations,
        organizationName: organizations.name,
        roleLabel: roles.label,
      })
      .from(organizationInvitations)
      .innerJoin(organizations, eq(organizations.id, organizationInvitations.organizationId))
      .innerJoin(roles, eq(roles.id, organizationInvitations.roleId))
      .orderBy(desc(organizationInvitations.createdAt)),
    database.select().from(sessions).orderBy(desc(sessions.createdAt)),
    database.select().from(elders).orderBy(desc(elders.createdAt)),
    database.select().from(rooms).orderBy(desc(rooms.createdAt)),
    database
      .select({
        bed: beds,
        roomName: rooms.name,
        currentElderName: sql<string | null>`(
          select ${elders.name}
          from ${admissions}
          inner join ${elders} on ${elders.id} = ${admissions.elderId}
          where ${admissions.bedId} = ${beds.id} and ${admissions.status} = 'active'
          limit 1
        )`,
      })
      .from(beds)
      .innerJoin(rooms, eq(rooms.id, beds.roomId))
      .orderBy(desc(beds.createdAt)),
    database
      .select({
        admission: admissions,
        elderName: elders.name,
        bedCode: beds.code,
        roomName: rooms.name,
      })
      .from(admissions)
      .innerJoin(elders, eq(elders.id, admissions.elderId))
      .innerJoin(beds, eq(beds.id, admissions.bedId))
      .innerJoin(rooms, eq(rooms.id, beds.roomId))
      .orderBy(desc(admissions.admittedAt)),
    listAuditLogs({ limit: 100 }),
    database.select().from(systemIncidents).orderBy(desc(systemIncidents.createdAt)),
    database.select().from(systemSettings).limit(1),
  ]);

  const roleById = new Map(membershipRows.map((row) => [row.membership.id, row.role]));
  const activeAdmissionByElderId = new Map<string, { room: string; bed: string; bedId: string }>();
  for (const row of admissionRows) {
    if (row.admission.status !== "active") {
      continue;
    }

    activeAdmissionByElderId.set(row.admission.elderId, {
      room: row.roomName,
      bed: row.bedCode,
      bedId: row.admission.bedId,
    });
  }

  const bedCounts = new Map<string, { total: number; occupied: number }>();
  for (const row of bedRows) {
    const current = bedCounts.get(row.bed.roomId) ?? { total: 0, occupied: 0 };
    current.total += 1;
    if (row.bed.status === "occupied") {
      current.occupied += 1;
    }
    bedCounts.set(row.bed.roomId, current);
  }

  const organizationsData: Organization[] = organizationRows.map((row) => ({
    id: row.id,
    name: row.name,
    slug: row.slug,
    status: row.status,
    registrationEnabled: row.registrationEnabled,
    oidcEnabled: row.oidcEnabled,
    oidcIssuerUrl: row.oidcIssuerUrl,
    oidcClientId: row.oidcClientId,
    oidcHasClientSecret: row.oidcClientSecret.length > 0,
    oidcScopes: row.oidcScopes,
    oidcRedirectUri: row.oidcRedirectUri,
    oidcAvatarClaim: row.oidcAvatarClaim,
    oidcAutoProvision: row.oidcAutoProvision,
    createdAt: iso(row.createdAt),
    updatedAt: iso(row.updatedAt),
  }));

  const accountsData: Account[] = accountRows.map((row) => ({
    id: row.id,
    platformRoleId: row.platformRoleId ?? undefined,
    name: row.name,
    email: row.email,
    avatarUrl: row.avatarUrl,
    role: row.platformRoleId ?? "viewer",
    passwordHash: row.passwordHash,
    passwordSalt: row.passwordSalt,
    status: row.status,
    createdAt: iso(row.createdAt),
    updatedAt: iso(row.updatedAt),
  }));

  const membershipsData: Membership[] = membershipRows.map((row) => {
    const role = roleById.get(row.membership.id) ?? row.role;
    return {
      id: row.membership.id,
      accountId: row.membership.accountId,
      organizationId: row.membership.organizationId,
      roleId: row.membership.roleId,
      roleKey: role.key,
      roleLabel: role.label,
      status: row.membership.status,
      createdAt: iso(row.membership.createdAt),
      updatedAt: iso(row.membership.updatedAt),
    };
  });

  const joinRequestsData: JoinRequest[] = joinRequestRows.map((row) => ({
    id: row.request.id,
    accountId: row.request.accountId,
    accountName: row.accountName,
    accountEmail: row.accountEmail,
    organizationId: row.request.organizationId,
    organizationName: row.organizationName,
    status: row.request.status,
    reason: row.request.reason,
    reviewedByAccountId: row.request.reviewedByAccountId ?? undefined,
    reviewedAt: row.request.reviewedAt ? iso(row.request.reviewedAt) : undefined,
    createdAt: iso(row.request.createdAt),
  }));

  const organizationInvitationsData: OrganizationInvitation[] = invitationRows.map((row) => ({
    id: row.invitation.id,
    organizationId: row.invitation.organizationId,
    organizationName: row.organizationName,
    roleId: row.invitation.roleId,
    roleLabel: row.roleLabel,
    email: row.invitation.email,
    token: row.invitation.token,
    status: row.invitation.status as OrganizationInvitation["status"],
    createdByAccountId: row.invitation.createdByAccountId ?? undefined,
    acceptedByAccountId: row.invitation.acceptedByAccountId ?? undefined,
    acceptedAt: row.invitation.acceptedAt ? iso(row.invitation.acceptedAt) : undefined,
    expiresAt: iso(row.invitation.expiresAt),
    maxUses: row.invitation.maxUses,
    usedCount: row.invitation.usedCount,
    createdAt: iso(row.invitation.createdAt),
    updatedAt: iso(row.invitation.updatedAt),
  }));

  const sessionsData: Session[] = sessionRows.map((row) => ({
    id: row.id,
    accountId: row.accountId,
    activeOrganizationId: row.activeOrganizationId ?? undefined,
    createdAt: iso(row.createdAt),
    expiresAt: iso(row.expiresAt),
  }));

  const eldersData: Elder[] = elderRows.map((row) => {
    const activeAdmission = activeAdmissionByElderId.get(row.id);
    return {
      id: row.id,
      organizationId: row.organizationId,
      name: row.name,
      gender: row.gender,
      age: row.age,
      careLevel: row.careLevel,
      room: activeAdmission?.room ?? "",
      bed: activeAdmission?.bed ?? "",
      bedId: activeAdmission?.bedId,
      status: row.status,
      primaryContact: row.primaryContact,
      phone: row.phone,
      medicalNotes: row.medicalNotes,
      createdAt: iso(row.createdAt),
      updatedAt: iso(row.updatedAt),
    };
  });

  const roomsData: Room[] = roomRows.map((row) => {
    const counts = bedCounts.get(row.id) ?? { total: 0, occupied: 0 };
    return {
      id: row.id,
      organizationId: row.organizationId,
      name: row.name,
      code: row.code,
      capacity: row.capacity,
      status: row.status,
      bedCount: counts.total,
      occupiedBedCount: counts.occupied,
      createdAt: iso(row.createdAt),
      updatedAt: iso(row.updatedAt),
    };
  });

  const bedsData: FacilityBed[] = bedRows.map((row) => ({
    id: row.bed.id,
    organizationId: row.bed.organizationId,
    roomId: row.bed.roomId,
    roomName: row.roomName,
    code: row.bed.code,
    status: row.bed.status,
    notes: row.bed.notes,
    currentElderName: row.currentElderName ?? undefined,
    createdAt: iso(row.bed.createdAt),
    updatedAt: iso(row.bed.updatedAt),
  }));

  const admissionsData: Admission[] = admissionRows.map((row) => ({
    id: row.admission.id,
    organizationId: row.admission.organizationId,
    elderId: row.admission.elderId,
    elderName: row.elderName,
    bedId: row.admission.bedId,
    bedCode: row.bedCode,
    roomName: row.roomName,
    status: row.admission.status,
    admittedAt: iso(row.admission.admittedAt),
    dischargedAt: row.admission.dischargedAt ? iso(row.admission.dischargedAt) : undefined,
    notes: row.admission.notes,
  }));

  const incidentsData: SystemIncident[] = incidentRows.map((row) => ({
    id: row.id,
    organizationId: row.organizationId ?? undefined,
    severity: row.severity,
    status: row.status,
    title: row.title,
    description: row.description,
    source: row.source,
    createdAt: iso(row.createdAt),
    updatedAt: iso(row.updatedAt),
  }));

  const systemSettingsRow = systemSettingsRows[0];
  const systemSettingsData = systemSettingsRow
    ? {
        id: systemSettingsRow.id,
        registrationEnabled: systemSettingsRow.registrationEnabled,
        oidcEnabled: systemSettingsRow.oidcEnabled,
        oidcIssuerUrl: systemSettingsRow.oidcIssuerUrl,
        oidcClientId: systemSettingsRow.oidcClientId,
        oidcHasClientSecret: systemSettingsRow.oidcClientSecret.length > 0,
        oidcScopes: systemSettingsRow.oidcScopes,
        oidcRedirectUri: systemSettingsRow.oidcRedirectUri,
        oidcAvatarClaim: systemSettingsRow.oidcAvatarClaim,
        oidcAutoProvision: systemSettingsRow.oidcAutoProvision,
        updatedAt: iso(systemSettingsRow.updatedAt),
      }
    : {
        id: "global",
        registrationEnabled: true,
        oidcEnabled: false,
        oidcIssuerUrl: "",
        oidcClientId: "",
        oidcHasClientSecret: false,
        oidcScopes: "openid profile email",
        oidcRedirectUri: "",
        oidcAvatarClaim: "picture",
        oidcAutoProvision: false,
        updatedAt: new Date(0).toISOString(),
      };

  return {
    systemSettings: systemSettingsData,
    organizations: organizationsData,
    accounts: accountsData,
    memberships: membershipsData,
    joinRequests: joinRequestsData,
    organizationInvitations: organizationInvitationsData,
    sessions: sessionsData,
    elders: eldersData,
    rooms: roomsData,
    beds: bedsData,
    admissions: admissionsData,
    auditLogs: auditLogRows,
    incidents: incidentsData,
  };
}

export async function writeData(): Promise<void> {
  throw new Error("writeData is not available after PostgreSQL migration");
}

export async function updateData<T>(): Promise<T> {
  throw new Error("updateData is not available after PostgreSQL migration");
}
