import { dbInstance, poolInstance } from "./db";
import {
  users,
  guests,
  settings,
  whatsappAccounts,
  ipBans,
  auditLogs,
  hallBranches,
  type User,
  type InsertUser,
  type Guest,
  type InsertGuest,
  type UpdateGuestRequest,
  type WhatsappAccount,
  type InsertWhatsappAccount,
  type SendQueueEntry,
  type InsertSendQueueEntry,
  type IpBan,
  type AuditLog,
  type HallBranch,
} from "@shared/schema";
import { eq, and, sql, lt, isNull, inArray, desc } from "drizzle-orm";
import { toInviteSlug } from "./slug-utils";

export interface IStorage {
  getUser(id: number): Promise<User | undefined>;
  getUserByCode(code: string): Promise<User | undefined>;
  getUserByPhone(phone: string): Promise<User | undefined>;
  getUsersByPhone(phone: string): Promise<User[]>;
  createUser(user: InsertUser): Promise<User>;
  updateUser(id: number, updates: Partial<Omit<User, 'id' | 'createdAt'>>): Promise<User>;
  deleteUser(id: number): Promise<void>;
  getAllUsers(): Promise<User[]>;
  getAdminUser(): Promise<User | undefined>;
  incrementMessagesSent(userId: number): Promise<void>;
  addMessageQuota(userId: number, amount: number): Promise<void>;

  getGuests(userId: number): Promise<Guest[]>;
  getGuest(id: number): Promise<Guest | undefined>;
  getGuestBySlug(slug: string): Promise<Guest | undefined>;
  createGuest(guest: InsertGuest): Promise<Guest>;
  updateGuest(id: number, updates: UpdateGuestRequest): Promise<Guest>;
  deleteGuest(id: number): Promise<void>;
  bulkDeleteGuests(ids: number[], userId: number): Promise<void>;
  deleteGuestsByUser(userId: number): Promise<void>;

  getSetting(userId: number, key: string): Promise<string | undefined>;
  setSetting(userId: number, key: string, value: string): Promise<void>;
  deleteSetting(userId: number, key: string): Promise<void>;

  getWhatsappAccounts(userId: number): Promise<WhatsappAccount[]>;
  getAllWhatsappAccounts(): Promise<WhatsappAccount[]>;
  getWhatsappAccount(id: number): Promise<WhatsappAccount | undefined>;
  createWhatsappAccount(account: InsertWhatsappAccount): Promise<WhatsappAccount>;
  deleteWhatsappAccount(id: number): Promise<void>;
  deleteWhatsappAccountsByUser(userId: number): Promise<void>;

  cleanupExpiredSessions(): Promise<number>;
  cleanupOldMedia(userId: number): Promise<void>;
  markGuestSent(guestId: number): Promise<void>;
  getEventDetails(userId: number): Promise<Record<string, string>>;
  setEventDetails(userId: number, details: Record<string, string>): Promise<void>;
  getExpiredUsers(daysAfterMonth: number): Promise<User[]>;
  deleteAllUserData(userId: number): Promise<void>;
  getGuestsExpiredAfterMonth(): Promise<{ userId: number; guestIds: number[] }[]>;
  getGuestByQrToken(token: string): Promise<Guest | undefined>;
  checkInGuestAtomic(token: string): Promise<{ guest: Guest; alreadyCheckedIn: boolean } | null>;
  checkInGuest(guestId: number): Promise<Guest>;
  getCheckedInStats(userId: number): Promise<{ total: number; checkedIn: number }>;
  getCheckedInGuests(userId: number): Promise<Guest[]>;
  updateGuestName(guestId: number, name: string, userId: number): Promise<Guest | undefined>;
  updateGuestPhone(guestId: number, phone: string, userId: number): Promise<Guest | undefined>;

  getAllHalls(): Promise<User[]>;
  getHallClients(hallId: number): Promise<User[]>;
  getHallArchivedClients(hallId: number): Promise<User[]>;
  getHallStats(hallId: number): Promise<{ totalGuests: number; guestsThisWeek: number; guestsThisMonth: number; confirmedGuests: number }>;
  getHallAllGuests(hallId: number): Promise<(Guest & { clientName: string })[]>;
  getHallScanners(hallId: number): Promise<User[]>;
  getScannersForUser(userId: number): Promise<User[]>;
  suspendUser(id: number, suspended: boolean): Promise<User>;
  cascadeSuspendHallClients(hallId: number, suspend: boolean): Promise<void>;
  adjustHallAllocated(hallId: number, delta: number): Promise<void>;
  adjustClientMonthQuota(clientId: number, delta: number): Promise<void>;
  confirmHallPayment(hallId: number, subscriptionType?: string): Promise<User>;
  resetHallBalance(hallId: number): Promise<void>;
  getUsersWithActiveBulkSend(): Promise<number[]>;

  createSendQueueBatch(entries: InsertSendQueueEntry[]): Promise<void>;
  getDueSendQueueEntries(): Promise<SendQueueEntry[]>;
  markSendQueueEntry(id: number, status: 'sent' | 'failed', error?: string): Promise<void>;
  rescheduleSendQueueEntry(id: number, scheduledAt: Date, attempts: number): Promise<void>;
  clearUserSendQueue(userId: number): Promise<void>;
  cancelUserSendQueue(userId: number): Promise<void>;
  pauseUserSendQueue(userId: number): Promise<void>;
  resumeUserSendQueue(userId: number): Promise<{ guestIds: number[]; intervalMs: number; templateId: string; accountId: number | null; useSystem: boolean; warmupActive: boolean; baseUrl: string; settingsUserId: number | null }>;
  getSendQueueStats(userId: number): Promise<{ pending: number; sent: number; failed: number; paused: number }>;
  deleteOldSendQueueEntries(): Promise<void>;

  saveMediaFile(userId: number, data: Buffer, mimeType: string, fileType: string, size: number): Promise<void>;
  getMediaFile(userId: number): Promise<{ data: Buffer; mimeType: string; fileType: string } | null>;
  deleteMediaFile(userId: number): Promise<void>;

  getSystemMessageEnabled(key: string): Promise<boolean>;
  setSystemMessageEnabled(key: string, enabled: boolean): Promise<void>;
  getAllSystemMessageSettings(): Promise<Record<string, boolean>>;

  // IP ban management
  isIpBanned(ip: string): Promise<boolean>;
  banIp(ip: string, reason?: string, manual?: boolean): Promise<void>;
  unbanIp(ip: string): Promise<void>;
  getAllIpBans(): Promise<IpBan[]>;

  // Audit log
  addAuditLog(entry: { actorId?: number; action: string; details?: string; ip?: string }): Promise<void>;
  getAuditLogs(limit?: number): Promise<AuditLog[]>;

  // Account sharing (primary/secondary linked users)
  getEffectiveUserId(userId: number): Promise<number>;
  getLinkedUsers(primaryUserId: number): Promise<User[]>;
  createLinkedUser(primaryUserId: number, name: string, phoneNumber: string): Promise<User>;
  revokeLinkedUser(linkedUserId: number, primaryUserId: number): Promise<void>;

  // Hall branches
  getHallBranches(hallId: number): Promise<HallBranch[]>;
  getHallBranch(branchId: number): Promise<HallBranch | undefined>;
  createHallBranch(hallId: number, name: string, googleMapsUrl?: string): Promise<HallBranch>;
  updateHallBranch(branchId: number, name: string, googleMapsUrl?: string): Promise<HallBranch>;
  deleteHallBranch(branchId: number): Promise<void>;
}

export class DatabaseStorage implements IStorage {
  private get db() {
    return dbInstance();
  }

  async getUser(id: number): Promise<User | undefined> {
    const [user] = await this.db.select().from(users).where(eq(users.id, id));
    return user;
  }

  async getUserByCode(code: string): Promise<User | undefined> {
    const [user] = await this.db.select().from(users).where(eq(users.accessCode, code));
    return user;
  }

  async getUserByPhone(phone: string): Promise<User | undefined> {
    const [user] = await this.db.select().from(users).where(eq(users.phoneNumber, phone));
    return user;
  }

  async getUsersByPhone(phone: string): Promise<User[]> {
    return await this.db.select().from(users).where(eq(users.phoneNumber, phone));
  }

  async createUser(insertUser: InsertUser): Promise<User> {
    const [user] = await this.db.insert(users).values(insertUser).returning();
    return user;
  }

  async updateUser(id: number, updates: Partial<Omit<User, 'id' | 'createdAt'>>): Promise<User> {
    const [updated] = await this.db.update(users).set(updates).where(eq(users.id, id)).returning();
    return updated;
  }

  async deleteUser(id: number): Promise<void> {
    await this.db.delete(users).where(eq(users.id, id));
  }

  async getAllUsers(): Promise<User[]> {
    return await this.db.select().from(users).orderBy(users.createdAt);
  }

  async getAdminUser(): Promise<User | undefined> {
    const [admin] = await this.db.select().from(users).where(eq(users.isAdmin, true)).limit(1);
    return admin;
  }

  async incrementMessagesSent(userId: number): Promise<void> {
    await this.db.update(users).set({ messagesSent: sql`${users.messagesSent} + 1` }).where(eq(users.id, userId));
  }

  async addMessageQuota(userId: number, amount: number): Promise<void> {
    await this.db.update(users).set({ messageQuota: sql`${users.messageQuota} + ${amount}` }).where(eq(users.id, userId));
  }

  async getGuests(userId: number): Promise<Guest[]> {
    return await this.db.select().from(guests).where(eq(guests.userId, userId)).orderBy(guests.createdAt);
  }

  async getGuest(id: number): Promise<Guest | undefined> {
    const [guest] = await this.db.select().from(guests).where(eq(guests.id, id));
    return guest;
  }

  async getGuestBySlug(slug: string): Promise<Guest | undefined> {
    const [guest] = await this.db.select().from(guests).where(eq(guests.inviteSlug, slug));
    return guest;
  }

  async createGuest(insertGuest: InsertGuest): Promise<Guest> {
    const qrToken = this.generateQrToken();
    let inviteSlug: string | undefined;
    for (let attempt = 0; attempt < 5; attempt++) {
      const candidate = toInviteSlug(insertGuest.name);
      const existing = await this.getGuestBySlug(candidate);
      if (!existing) { inviteSlug = candidate; break; }
    }
    const [guest] = await this.db.insert(guests).values({ ...insertGuest, qrToken, inviteSlug }).returning();
    return guest;
  }

  private generateQrToken(): string {
    const chars = 'abcdefghijklmnopqrstuvwxyz0123456789';
    let token = '';
    for (let i = 0; i < 48; i++) {
      token += chars[Math.floor(Math.random() * chars.length)];
    }
    return token + Date.now().toString(36);
  }

  async updateGuest(id: number, updates: UpdateGuestRequest): Promise<Guest> {
    const [updated] = await this.db.update(guests).set(updates).where(eq(guests.id, id)).returning();
    return updated;
  }

  async deleteGuest(id: number): Promise<void> {
    await this.db.delete(guests).where(eq(guests.id, id));
  }

  async bulkDeleteGuests(ids: number[], userId: number): Promise<void> {
    if (!ids.length) return;
    await this.db.delete(guests).where(and(inArray(guests.id, ids), eq(guests.userId, userId)));
  }

  async deleteGuestsByUser(userId: number): Promise<void> {
    await this.db.delete(guests).where(eq(guests.userId, userId));
  }

  async getSetting(userId: number, key: string): Promise<string | undefined> {
    const [setting] = await this.db.select().from(settings).where(and(eq(settings.userId, userId), eq(settings.key, key)));
    return setting?.value || undefined;
  }

  async setSetting(userId: number, key: string, value: string): Promise<void> {
    await this.db.insert(settings)
      .values({ userId, key, value })
      .onConflictDoUpdate({
        target: [settings.userId, settings.key],
        set: { value },
      });
  }

  async deleteSetting(userId: number, key: string): Promise<void> {
    await this.db.delete(settings).where(and(eq(settings.userId, userId), eq(settings.key, key)));
  }

  async getWhatsappAccounts(userId: number): Promise<WhatsappAccount[]> {
    return await this.db.select().from(whatsappAccounts).where(eq(whatsappAccounts.userId, userId)).orderBy(whatsappAccounts.createdAt);
  }

  async getAllWhatsappAccounts(): Promise<WhatsappAccount[]> {
    return await this.db.select().from(whatsappAccounts).orderBy(whatsappAccounts.createdAt);
  }

  async getWhatsappAccount(id: number): Promise<WhatsappAccount | undefined> {
    const [account] = await this.db.select().from(whatsappAccounts).where(eq(whatsappAccounts.id, id));
    return account;
  }

  async createWhatsappAccount(account: InsertWhatsappAccount): Promise<WhatsappAccount> {
    const [created] = await this.db.insert(whatsappAccounts).values(account).returning();
    return created;
  }

  async deleteWhatsappAccount(id: number): Promise<void> {
    await this.db.delete(whatsappAccounts).where(eq(whatsappAccounts.id, id));
  }

  async deleteWhatsappAccountsByUser(userId: number): Promise<void> {
    await this.db.delete(whatsappAccounts).where(eq(whatsappAccounts.userId, userId));
  }

  async cleanupExpiredSessions(): Promise<number> {
    return 0;
  }

  async cleanupOldMedia(userId: number): Promise<void> {
    await this.db.delete(settings).where(
      and(
        eq(settings.userId, userId),
        sql`${settings.key} IN ('global_media_url', 'global_media_type')`
      )
    );
  }

  async markGuestSent(guestId: number): Promise<void> {
    await this.db.update(guests).set({ sentAt: new Date() }).where(eq(guests.id, guestId));
  }

  async getEventDetails(userId: number): Promise<Record<string, string>> {
    const rows = await this.db.select().from(settings).where(
      and(
        eq(settings.userId, userId),
        sql`(${settings.key} LIKE 'event_%' OR ${settings.key} = 'include_qr')`
      )
    );
    const details: Record<string, string> = {};
    for (const row of rows) {
      if (row.value) details[row.key] = row.value;
    }
    return details;
  }

  async setEventDetails(userId: number, details: Record<string, string>): Promise<void> {
    for (const [key, value] of Object.entries(details)) {
      if (value) await this.setSetting(userId, key, value);
    }
  }

  async getExpiredUsers(daysAfterMonth: number): Promise<User[]> {
    const cutoff = new Date();
    cutoff.setDate(cutoff.getDate() - 30 - daysAfterMonth);
    const allUsers = await this.db.select().from(users).where(
      and(
        eq(users.isAdmin, false),
        lt(users.createdAt, cutoff)
      )
    );
    return allUsers;
  }

  async deleteAllUserData(userId: number): Promise<void> {
    await this.db.delete(guests).where(eq(guests.userId, userId));
    await this.db.delete(settings).where(eq(settings.userId, userId));
    await this.db.delete(whatsappAccounts).where(eq(whatsappAccounts.userId, userId));
    await poolInstance().query('DELETE FROM media_files WHERE user_id = $1', [userId]);
    await this.db.delete(users).where(eq(users.id, userId));
  }

  async getGuestsExpiredAfterMonth(): Promise<{ userId: number; guestIds: number[] }[]> {
    const cutoff = new Date();
    cutoff.setDate(cutoff.getDate() - 30);
    const expiredGuests = await this.db.select().from(guests).where(
      and(
        sql`${guests.sentAt} IS NOT NULL`,
        lt(guests.sentAt, cutoff)
      )
    );
    const grouped = new Map<number, number[]>();
    for (const g of expiredGuests) {
      if (!grouped.has(g.userId)) grouped.set(g.userId, []);
      grouped.get(g.userId)!.push(g.id);
    }
    return Array.from(grouped.entries()).map(([userId, guestIds]) => ({ userId, guestIds }));
  }

  async getGuestByQrToken(token: string): Promise<Guest | undefined> {
    const [guest] = await this.db.select().from(guests).where(eq(guests.qrToken, token));
    return guest;
  }

  async checkInGuestAtomic(token: string): Promise<{ guest: Guest; alreadyCheckedIn: boolean } | null> {
    const now = new Date();
    const [updated] = await this.db.update(guests)
      .set({ checkedIn: true, checkedInAt: now })
      .where(and(eq(guests.qrToken, token), eq(guests.checkedIn, false)))
      .returning();
    if (updated) return { guest: updated, alreadyCheckedIn: false };
    const [existing] = await this.db.select().from(guests).where(eq(guests.qrToken, token));
    if (existing) return { guest: existing, alreadyCheckedIn: true };
    return null;
  }

  async checkInGuest(guestId: number): Promise<Guest> {
    const [updated] = await this.db.update(guests)
      .set({ checkedIn: true, checkedInAt: new Date() })
      .where(eq(guests.id, guestId))
      .returning();
    return updated;
  }

  async updateGuestName(guestId: number, name: string, userId: number): Promise<Guest | undefined> {
    const [updated] = await this.db.update(guests)
      .set({ name })
      .where(and(eq(guests.id, guestId), eq(guests.userId, userId)))
      .returning();
    return updated;
  }

  async updateGuestPhone(guestId: number, phone: string, userId: number): Promise<Guest | undefined> {
    const [updated] = await this.db.update(guests)
      .set({ phoneNumber: phone })
      .where(and(eq(guests.id, guestId), eq(guests.userId, userId)))
      .returning();
    return updated;
  }

  async getCheckedInStats(userId: number): Promise<{ total: number; checkedIn: number }> {
    const allGuests = await this.db.select().from(guests).where(eq(guests.userId, userId));
    return {
      total: allGuests.length,
      checkedIn: allGuests.filter(g => g.checkedIn).length,
    };
  }

  async getCheckedInGuests(userId: number): Promise<Guest[]> {
    return await this.db.select().from(guests)
      .where(and(eq(guests.userId, userId), eq(guests.checkedIn, true)))
      .orderBy(guests.checkedInAt);
  }

  async getAllHalls(): Promise<User[]> {
    return await this.db.select().from(users)
      .where(eq(users.role, "hall"))
      .orderBy(users.createdAt);
  }

  async getHallStats(hallId: number): Promise<{ totalGuests: number; guestsThisWeek: number; guestsThisMonth: number; confirmedGuests: number }> {
    const clients = await this.db.select({ id: users.id }).from(users).where(eq(users.parentHallId, hallId));
    if (clients.length === 0) return { totalGuests: 0, guestsThisWeek: 0, guestsThisMonth: 0, confirmedGuests: 0 };
    const clientIds = clients.map(c => c.id);
    const now = new Date();
    const weekAgo = new Date(now.getTime() - 7 * 24 * 60 * 60 * 1000);
    const monthAgo = new Date(now.getTime() - 30 * 24 * 60 * 60 * 1000);
    const allGuests = await this.db.select().from(guests).where(
      sql`${guests.userId} = ANY(${clientIds})`
    );
    return {
      totalGuests: allGuests.length,
      guestsThisWeek: allGuests.filter(g => g.createdAt && g.createdAt >= weekAgo).length,
      guestsThisMonth: allGuests.filter(g => g.createdAt && g.createdAt >= monthAgo).length,
      confirmedGuests: allGuests.filter(g => g.status === 'confirmed').length,
    };
  }

  async getHallClients(hallId: number): Promise<User[]> {
    return await this.db.select().from(users)
      .where(and(eq(users.parentHallId, hallId), eq(users.role, "user"), eq(users.isArchived, false)))
      .orderBy(users.createdAt);
  }

  async getHallArchivedClients(hallId: number): Promise<User[]> {
    return await this.db.select().from(users)
      .where(and(eq(users.parentHallId, hallId), eq(users.role, "user"), eq(users.isArchived, true)))
      .orderBy(users.archivedAt);
  }

  async getHallScanners(hallId: number): Promise<User[]> {
    return await this.db.select().from(users)
      .where(and(eq(users.role, "scanner"), eq(users.parentHallId, hallId)));
  }

  async getScannersForUser(userId: number): Promise<User[]> {
    const allScanners = await this.db.select().from(users).where(eq(users.role, "scanner"));
    const result: User[] = [];
    for (const s of allScanners) {
      const linked = await this.getSetting(s.id, "linked_user_id");
      if (linked === String(userId)) result.push(s);
    }
    return result;
  }

  async getHallAllGuests(hallId: number): Promise<(Guest & { clientName: string })[]> {
    const clients = await this.db.select({ id: users.id, name: users.name }).from(users)
      .where(and(eq(users.parentHallId, hallId), eq(users.role, "user")));
    if (clients.length === 0) return [];
    const clientIds = clients.map(c => c.id);
    const clientMap = new Map(clients.map(c => [c.id, c.name]));
    const allGuests = await this.db.select().from(guests)
      .where(sql`${guests.userId} = ANY(${clientIds})`)
      .orderBy(guests.createdAt);
    return allGuests.map(g => ({ ...g, clientName: clientMap.get(g.userId) || "" }));
  }

  async suspendUser(id: number, suspended: boolean): Promise<User> {
    const [updated] = await this.db.update(users)
      .set({ isSuspended: suspended })
      .where(eq(users.id, id))
      .returning();
    return updated;
  }

  async cascadeSuspendHallClients(hallId: number, suspend: boolean): Promise<void> {
    if (suspend) {
      // Suspend all non-suspended, non-archived clients and mark them as suspended-by-hall
      await this.db.update(users)
        .set({ isSuspended: true, suspendedByHall: true })
        .where(and(
          eq(users.parentHallId, hallId),
          eq(users.role, "user"),
          eq(users.isSuspended, false),
          eq(users.isArchived, false),
        ));
    } else {
      // Restore only clients that were suspended by the hall cascade; manually suspended clients stay
      await this.db.update(users)
        .set({ isSuspended: false, suspendedByHall: false })
        .where(and(
          eq(users.parentHallId, hallId),
          eq(users.role, "user"),
          eq(users.suspendedByHall, true),
        ));
    }
  }

  async adjustHallAllocated(hallId: number, delta: number): Promise<void> {
    if (delta < 0) {
      await this.db.update(users)
        .set({ messagesSent: sql`GREATEST(0, ${users.messagesSent} + ${delta})` })
        .where(eq(users.id, hallId));
    } else {
      await this.db.update(users)
        .set({ messagesSent: sql`${users.messagesSent} + ${delta}` })
        .where(eq(users.id, hallId));
    }
  }

  async adjustClientMonthQuota(clientId: number, delta: number): Promise<void> {
    if (delta < 0) {
      await this.db.update(users)
        .set({ currentMonthQuota: sql`GREATEST(0, ${users.currentMonthQuota} + ${delta})` })
        .where(eq(users.id, clientId));
    } else {
      await this.db.update(users)
        .set({ currentMonthQuota: sql`${users.currentMonthQuota} + ${delta}` })
        .where(eq(users.id, clientId));
    }
  }

  async confirmHallPayment(hallId: number, subscriptionType: string = "monthly"): Promise<User> {
    const now = new Date();

    // If the hall still has an active subscription, extend from the current expiry
    // so early payment preserves the remaining days (e.g. paying on Apr 25 for an
    // Apr 30 expiry gives May 30, not May 25).
    const [current] = await this.db.select({ subscriptionExpiresAt: users.subscriptionExpiresAt })
      .from(users).where(eq(users.id, hallId));
    const base = (current?.subscriptionExpiresAt && new Date(current.subscriptionExpiresAt) > now)
      ? new Date(current.subscriptionExpiresAt)
      : now;

    const nextExpiry = new Date(base);
    if (subscriptionType === "annual") {
      nextExpiry.setFullYear(nextExpiry.getFullYear() + 1);
    } else {
      nextExpiry.setMonth(nextExpiry.getMonth() + 1);
    }
    const [updated] = await this.db.update(users)
      .set({
        subscriptionExpiresAt: nextExpiry,
        subscriptionStartedAt: now,
        subscriptionType,
        billingStatus: "paid",
        messagesSent: 0,
        balanceResetAt: now,
        paymentWarningAt: null,
        hallNotifiedAt: null,
        adminWarning7DaysAt: null,
        adminWarningDayZeroAt: null,
        adminWarning1DayAt: null,
        adminWarning3DaysAfterAt: null,
        isSuspended: false,
      } as any)
      .where(eq(users.id, hallId))
      .returning();
    // Clear all subscription reminder tracking keys
    for (const key of [
      "mf_payment_failed_at", "mf_grace_reminder_1da", "mf_grace_reminder_3da",
      "sub_hall_reminder_7d", "sub_hall_reminder_3d", "sub_hall_reminder_1d",
      "sub_hall_reminder_0d", "sub_hall_reminder_1da", "sub_hall_reminder_3da",
    ]) {
      await this.deleteSetting(hallId, key);
    }
    // Restore clients that were suspended by the hall billing cascade
    await this.cascadeSuspendHallClients(hallId, false);
    // Reset messagesSent for active clients, reset currentMonthQuota for ALL hall clients (including archived)
    const activeClients = await this.getHallClients(hallId);
    for (const client of activeClients) {
      await this.updateUser(client.id, { messagesSent: 0 });
    }
    await this.db.update(users)
      .set({ currentMonthQuota: 0 })
      .where(and(eq(users.parentHallId, hallId), eq(users.role, "user")));
    return updated;
  }

  async resetHallBalance(hallId: number): Promise<void> {
    const now = new Date();
    await this.db.update(users).set({ messagesSent: 0, balanceResetAt: now } as any).where(eq(users.id, hallId));
    // Reset messagesSent for active clients, reset currentMonthQuota for ALL hall clients (including archived)
    const activeClients = await this.getHallClients(hallId);
    for (const client of activeClients) {
      await this.updateUser(client.id, { messagesSent: 0 });
    }
    await this.db.update(users)
      .set({ currentMonthQuota: 0 })
      .where(and(eq(users.parentHallId, hallId), eq(users.role, "user")));
  }

  async getUsersWithActiveBulkSend(): Promise<number[]> {
    const rows = await this.db
      .select({ userId: settings.userId })
      .from(settings)
      .where(and(eq(settings.key, "bulk_send_active"), eq(settings.value, "true")));
    return rows.map(r => r.userId);
  }

  async createSendQueueBatch(entries: InsertSendQueueEntry[]): Promise<void> {
    if (entries.length === 0) return;
    const cols = 'user_id,guest_id,scheduled_at,status,template_id,account_id,use_system,warmup_active,attempts,last_error,base_url,interval_ms,settings_user_id,created_at';
    const vals = entries.map((_, i) => {
      const b = i * 10;
      return `($${b+1},$${b+2},$${b+3},'pending',$${b+4},$${b+5},$${b+6},$${b+7},0,NULL,$${b+8},$${b+9},$${b+10},NOW())`;
    }).join(',');
    const params: any[] = [];
    for (const e of entries) {
      params.push(e.userId, e.guestId, e.scheduledAt, e.templateId ?? 'formal', e.accountId ?? null, e.useSystem ?? false, e.warmupActive ?? false, e.baseUrl, e.intervalMs ?? 60000, e.settingsUserId ?? null);
    }
    await poolInstance().query(`INSERT INTO send_queue (${cols}) VALUES ${vals}`, params);
  }

  async getDueSendQueueEntries(): Promise<SendQueueEntry[]> {
    // Fetch all overdue pending entries ordered by scheduled_at (oldest first).
    // LIMIT 200 prevents processing an unbounded backlog in one tick.
    // Application code further limits to 1 per user per tick to preserve cadence.
    const { rows } = await poolInstance().query(
      `SELECT id, user_id, guest_id, scheduled_at, status, template_id, account_id, use_system, warmup_active, attempts, last_error, base_url, interval_ms, settings_user_id, created_at
       FROM send_queue
       WHERE status = 'pending' AND scheduled_at <= NOW()
       ORDER BY scheduled_at ASC
       LIMIT 200`
    );
    return rows.map((r: any) => ({
      id: r.id, userId: r.user_id, guestId: r.guest_id,
      scheduledAt: r.scheduled_at, status: r.status,
      templateId: r.template_id, accountId: r.account_id,
      useSystem: r.use_system, warmupActive: r.warmup_active,
      attempts: r.attempts, lastError: r.last_error, baseUrl: r.base_url,
      intervalMs: r.interval_ms ?? 60000, settingsUserId: r.settings_user_id ?? null,
      createdAt: r.created_at,
    }));
  }

  async markSendQueueEntry(id: number, status: 'sent' | 'failed', error?: string): Promise<void> {
    await poolInstance().query(
      'UPDATE send_queue SET status=$1, last_error=$2 WHERE id=$3',
      [status, error ?? null, id]
    );
  }

  async rescheduleSendQueueEntry(id: number, scheduledAt: Date, attempts: number): Promise<void> {
    await poolInstance().query(
      'UPDATE send_queue SET scheduled_at=$1, attempts=$2 WHERE id=$3',
      [scheduledAt, attempts, id]
    );
  }

  async clearUserSendQueue(userId: number): Promise<void> {
    await poolInstance().query('DELETE FROM send_queue WHERE user_id=$1', [userId]);
  }

  async cancelUserSendQueue(userId: number): Promise<void> {
    await poolInstance().query(
      "UPDATE send_queue SET status='cancelled' WHERE user_id=$1 AND status IN ('pending','paused')",
      [userId]
    );
  }

  async pauseUserSendQueue(userId: number): Promise<void> {
    await poolInstance().query(
      "UPDATE send_queue SET status='paused' WHERE user_id=$1 AND status='pending'",
      [userId]
    );
  }

  async resumeUserSendQueue(userId: number): Promise<{ guestIds: number[]; intervalMs: number; templateId: string; accountId: number | null; useSystem: boolean; warmupActive: boolean; baseUrl: string; settingsUserId: number | null }> {
    // Fetch paused entries (ordered oldest first) to get their config and guest list
    const { rows } = await poolInstance().query(
      `SELECT guest_id, interval_ms, template_id, account_id, use_system, warmup_active, base_url, settings_user_id
       FROM send_queue WHERE user_id=$1 AND status='paused'
       ORDER BY scheduled_at ASC`,
      [userId]
    );
    const guestIds = rows.map((r: any) => r.guest_id as number);
    const config = rows[0] ?? {};
    // Delete paused entries — new pending entries will be created by the resume endpoint
    await poolInstance().query("DELETE FROM send_queue WHERE user_id=$1 AND status='paused'", [userId]);
    return {
      guestIds,
      intervalMs: config.interval_ms ?? 60000,
      templateId: config.template_id ?? 'formal',
      accountId: config.account_id ?? null,
      useSystem: config.use_system ?? false,
      warmupActive: config.warmup_active ?? false,
      baseUrl: config.base_url ?? '',
      settingsUserId: config.settings_user_id ?? null,
    };
  }

  async getSendQueueStats(userId: number): Promise<{ pending: number; sent: number; failed: number; paused: number }> {
    // Use the persisted batch start time (set when a new batch is created, preserved on resume)
    // so sent/failed history is consistently counted regardless of how long ago entries were created.
    const batchStartStr = await this.getSetting(userId, "send_queue_batch_start");
    const batchStart = batchStartStr ? new Date(batchStartStr) : null;

    const { rows } = await poolInstance().query(
      `SELECT
         COUNT(*) FILTER (WHERE status = 'pending') AS pending,
         COUNT(*) FILTER (WHERE status = 'paused') AS paused,
         COUNT(*) FILTER (WHERE (status = 'sent' OR status = 'cancelled')
           AND ($2::timestamptz IS NULL OR created_at >= $2::timestamptz)) AS sent,
         COUNT(*) FILTER (WHERE status = 'failed'
           AND ($2::timestamptz IS NULL OR created_at >= $2::timestamptz)) AS failed
       FROM send_queue
       WHERE user_id = $1`,
      [userId, batchStart]
    );
    if (!rows[0]) return { pending: 0, paused: 0, sent: 0, failed: 0 };
    return {
      pending: parseInt(rows[0].pending || '0'),
      paused: parseInt(rows[0].paused || '0'),
      sent: parseInt(rows[0].sent || '0'),
      failed: parseInt(rows[0].failed || '0'),
    };
  }

  async deleteOldSendQueueEntries(): Promise<void> {
    await poolInstance().query(
      "DELETE FROM send_queue WHERE status IN ('sent','failed','cancelled') AND created_at < NOW() - interval '3 days'"
    );
  }

  async saveMediaFile(userId: number, data: Buffer, mimeType: string, fileType: string, size: number): Promise<void> {
    await poolInstance().query(
      `INSERT INTO media_files (user_id, data, mime_type, file_type, size, uploaded_at)
       VALUES ($1, $2, $3, $4, $5, NOW())
       ON CONFLICT (user_id) DO UPDATE SET data=$2, mime_type=$3, file_type=$4, size=$5, uploaded_at=NOW()`,
      [userId, data, mimeType, fileType, size]
    );
  }

  async getMediaFile(userId: number): Promise<{ data: Buffer; mimeType: string; fileType: string } | null> {
    const { rows } = await poolInstance().query(
      'SELECT data, mime_type, file_type FROM media_files WHERE user_id = $1',
      [userId]
    );
    if (!rows[0]) return null;
    return { data: rows[0].data, mimeType: rows[0].mime_type, fileType: rows[0].file_type };
  }

  async deleteMediaFile(userId: number): Promise<void> {
    await poolInstance().query('DELETE FROM media_files WHERE user_id = $1', [userId]);
  }

  // System message settings stored in settings table with userId=-1 (system-wide)
  private readonly SYSTEM_MSG_USER_ID = -1;

  async getSystemMessageEnabled(key: string): Promise<boolean> {
    const val = await this.getSetting(this.SYSTEM_MSG_USER_ID, `system_msg:${key}`);
    if (val === undefined) return true; // default enabled
    return val !== "false";
  }

  async setSystemMessageEnabled(key: string, enabled: boolean): Promise<void> {
    await this.setSetting(this.SYSTEM_MSG_USER_ID, `system_msg:${key}`, enabled ? "true" : "false");
  }

  async getAllSystemMessageSettings(): Promise<Record<string, boolean>> {
    const rows = await this.db.select().from(settings).where(
      and(
        eq(settings.userId, this.SYSTEM_MSG_USER_ID),
        sql`${settings.key} LIKE 'system_msg:%'`
      )
    );
    const result: Record<string, boolean> = {};
    for (const row of rows) {
      const msgKey = row.key.replace("system_msg:", "");
      result[msgKey] = row.value !== "false";
    }
    return result;
  }

  async isIpBanned(ip: string): Promise<boolean> {
    const [row] = await this.db.select().from(ipBans).where(eq(ipBans.ip, ip));
    return !!row;
  }

  async banIp(ip: string, reason?: string, manual = false): Promise<void> {
    await this.db.insert(ipBans).values({ ip, reason, manual })
      .onConflictDoUpdate({ target: ipBans.ip, set: { bannedAt: sql`NOW()`, reason, manual } });
  }

  async unbanIp(ip: string): Promise<void> {
    await this.db.delete(ipBans).where(eq(ipBans.ip, ip));
  }

  async getAllIpBans(): Promise<IpBan[]> {
    return this.db.select().from(ipBans).orderBy(desc(ipBans.bannedAt));
  }

  async addAuditLog(entry: { actorId?: number; action: string; details?: string; ip?: string }): Promise<void> {
    await this.db.insert(auditLogs).values({
      actorId: entry.actorId ?? null,
      action: entry.action,
      details: entry.details ?? null,
      ip: entry.ip ?? null,
    });
  }

  async getAuditLogs(limit = 200): Promise<AuditLog[]> {
    return this.db.select().from(auditLogs).orderBy(desc(auditLogs.createdAt)).limit(limit);
  }

  async getEffectiveUserId(userId: number): Promise<number> {
    const [user] = await this.db.select({ primaryUserId: users.primaryUserId }).from(users).where(eq(users.id, userId));
    return user?.primaryUserId ?? userId;
  }

  async getLinkedUsers(primaryUserId: number): Promise<User[]> {
    return this.db.select().from(users).where(eq(users.primaryUserId, primaryUserId));
  }

  async createLinkedUser(primaryUserId: number, name: string, phoneNumber: string): Promise<User> {
    const { randomBytes } = await import("crypto");
    const accessCode = randomBytes(4).toString("hex").toUpperCase().slice(0, 8);
    const [user] = await this.db.insert(users).values({
      name,
      phoneNumber,
      accessCode,
      primaryUserId,
      messageQuota: 0,
      messagesSent: 0,
      isAdmin: false,
      isCoAdmin: false,
      role: "user",
      isSuspended: false,
      phoneVerified: true,
    }).returning();
    return user;
  }

  async revokeLinkedUser(linkedUserId: number, primaryUserId: number): Promise<void> {
    await this.db.delete(users).where(
      and(eq(users.id, linkedUserId), eq(users.primaryUserId, primaryUserId))
    );
  }

  async getHallBranches(hallId: number): Promise<HallBranch[]> {
    return await this.db.select().from(hallBranches)
      .where(eq(hallBranches.hallId, hallId))
      .orderBy(hallBranches.createdAt);
  }

  async getHallBranch(branchId: number): Promise<HallBranch | undefined> {
    const [branch] = await this.db.select().from(hallBranches).where(eq(hallBranches.id, branchId));
    return branch;
  }

  async createHallBranch(hallId: number, name: string, googleMapsUrl?: string): Promise<HallBranch> {
    const [branch] = await this.db.insert(hallBranches).values({
      hallId,
      name,
      googleMapsUrl: googleMapsUrl || null,
    }).returning();
    return branch;
  }

  async updateHallBranch(branchId: number, name: string, googleMapsUrl?: string): Promise<HallBranch> {
    const [branch] = await this.db.update(hallBranches)
      .set({ name, googleMapsUrl: googleMapsUrl || null })
      .where(eq(hallBranches.id, branchId))
      .returning();
    return branch;
  }

  async deleteHallBranch(branchId: number): Promise<void> {
    await this.db.delete(hallBranches).where(eq(hallBranches.id, branchId));
  }
}

export const storage = new DatabaseStorage();

export async function backfillInviteSlugs(): Promise<void> {
  try {
    const db = dbInstance();
    const nullSlugGuests = await db.select({ id: guests.id, name: guests.name })
      .from(guests)
      .where(isNull(guests.inviteSlug));
    for (const g of nullSlugGuests) {
      let inviteSlug: string | undefined;
      for (let attempt = 0; attempt < 5; attempt++) {
        const candidate = toInviteSlug(g.name);
        const [existing] = await db.select({ id: guests.id }).from(guests).where(eq(guests.inviteSlug, candidate));
        if (!existing) { inviteSlug = candidate; break; }
      }
      if (inviteSlug) {
        await db.update(guests).set({ inviteSlug }).where(eq(guests.id, g.id));
      }
    }
    if (nullSlugGuests.length > 0) {
      console.log(`[backfill] Generated invite slugs for ${nullSlugGuests.length} existing guests`);
    }
  } catch (err) {
    console.error("[backfill] Failed to backfill invite slugs:", err);
  }
}