import pool from '../config/database';
import logger from '../utils/logger';
import {
  VeranstaltungKategorie,
  Veranstaltung,
  VeranstaltungListItem,
  WiederholungConfig,
  CreateKategorieData,
  UpdateKategorieData,
  CreateVeranstaltungData,
  UpdateVeranstaltungData,
} from '../models/events.model';

// ---------------------------------------------------------------------------
// Internal helpers
// ---------------------------------------------------------------------------

/** Map a raw DB row to a VeranstaltungListItem */
function rowToListItem(row: any): VeranstaltungListItem {
  return {
    id: row.id,
    titel: row.titel,
    ort: row.ort ?? null,
    kategorie_id: row.kategorie_id ?? null,
    kategorie_name: row.kategorie_name ?? null,
    kategorie_farbe: row.kategorie_farbe ?? null,
    kategorie_icon: row.kategorie_icon ?? null,
    datum_von: new Date(row.datum_von),
    datum_bis: new Date(row.datum_bis),
    ganztaegig: row.ganztaegig,
    alle_gruppen: row.alle_gruppen,
    zielgruppen: row.zielgruppen ?? [],
    abgesagt: row.abgesagt,
    anmeldung_erforderlich: row.anmeldung_erforderlich,
    wiederholung: row.wiederholung ?? null,
    wiederholung_parent_id: row.wiederholung_parent_id ?? null,
  };
}

/** Map a raw DB row to a full Veranstaltung */
function rowToVeranstaltung(row: any): Veranstaltung {
  return {
    id: row.id,
    titel: row.titel,
    beschreibung: row.beschreibung ?? null,
    ort: row.ort ?? null,
    ort_url: row.ort_url ?? null,
    kategorie_id: row.kategorie_id ?? null,
    datum_von: new Date(row.datum_von),
    datum_bis: new Date(row.datum_bis),
    ganztaegig: row.ganztaegig,
    zielgruppen: row.zielgruppen ?? [],
    alle_gruppen: row.alle_gruppen,
    max_teilnehmer: row.max_teilnehmer ?? null,
    anmeldung_erforderlich: row.anmeldung_erforderlich,
    anmeldung_bis: row.anmeldung_bis ? new Date(row.anmeldung_bis) : null,
    erstellt_von: row.erstellt_von,
    abgesagt: row.abgesagt,
    abgesagt_grund: row.abgesagt_grund ?? null,
    abgesagt_am: row.abgesagt_am ? new Date(row.abgesagt_am) : null,
    erstellt_am: new Date(row.erstellt_am),
    aktualisiert_am: new Date(row.aktualisiert_am),
    // Joined fields
    kategorie_name: row.kategorie_name ?? null,
    kategorie_farbe: row.kategorie_farbe ?? null,
    kategorie_icon: row.kategorie_icon ?? null,
    erstellt_von_name: row.erstellt_von_name ?? null,
    // Recurrence fields
    wiederholung: row.wiederholung ?? null,
    wiederholung_parent_id: row.wiederholung_parent_id ?? null,
  };
}

/** Format a Date as YYYYMMDDTHHMMSSZ (UTC) for iCal output */
function formatIcalDate(date: Date): string {
  return date.toISOString().replace(/[-:]/g, '').replace(/\.\d{3}/, '');
}

/** Fold long iCal lines at 75 octets (RFC 5545 §3.1) */
function icalFold(line: string): string {
  if (Buffer.byteLength(line, 'utf-8') <= 75) return line;
  let folded = '';
  let currentLine = '';
  let currentBytes = 0;

  for (const char of line) {
    const charBytes = Buffer.byteLength(char, 'utf-8');
    if (currentBytes + charBytes > 75) {
      folded += currentLine + '\r\n ';
      currentLine = char;
      currentBytes = 1 + charBytes; // continuation line leading space = 1 byte
    } else {
      currentLine += char;
      currentBytes += charBytes;
    }
  }
  folded += currentLine;
  return folded;
}

/** Escape special characters in iCal text values (RFC 5545 §3.3.11) */
function icalEscape(value: string | null | undefined): string {
  if (!value) return '';
  return value
    .replace(/\\/g, '\\\\')
    .replace(/;/g, '\\;')
    .replace(/,/g, '\\,')
    .replace(/\n/g, '\\n');
}

// ---------------------------------------------------------------------------
// Events Service
// ---------------------------------------------------------------------------

class EventsService {
  // -------------------------------------------------------------------------
  // KATEGORIEN
  // -------------------------------------------------------------------------

  /** Returns all event categories ordered by name. */
  async getKategorien(): Promise<VeranstaltungKategorie[]> {
    const result = await pool.query(`
      SELECT id, name, beschreibung, farbe, icon, zielgruppen, alle_gruppen, erstellt_von, erstellt_am, aktualisiert_am
      FROM veranstaltung_kategorien
      ORDER BY name ASC
    `);
    return result.rows.map((row) => ({
      id: row.id,
      name: row.name,
      beschreibung: row.beschreibung ?? null,
      farbe: row.farbe ?? null,
      icon: row.icon ?? null,
      zielgruppen: row.zielgruppen ?? [],
      alle_gruppen: row.alle_gruppen ?? false,
      erstellt_von: row.erstellt_von ?? null,
      erstellt_am: new Date(row.erstellt_am),
      aktualisiert_am: new Date(row.aktualisiert_am),
    }));
  }

  /** Creates a new event category. */
  async createKategorie(data: CreateKategorieData, userId: string): Promise<VeranstaltungKategorie> {
    const result = await pool.query(
      `INSERT INTO veranstaltung_kategorien (name, beschreibung, farbe, icon, zielgruppen, alle_gruppen, erstellt_von)
       VALUES ($1, $2, $3, $4, $5, $6, $7)
       RETURNING id, name, beschreibung, farbe, icon, zielgruppen, alle_gruppen, erstellt_von, erstellt_am, aktualisiert_am`,
      [data.name, data.beschreibung ?? null, data.farbe ?? null, data.icon ?? null, data.zielgruppen ?? [], data.alle_gruppen ?? false, userId]
    );
    const row = result.rows[0];
    return {
      id: row.id,
      name: row.name,
      beschreibung: row.beschreibung ?? null,
      farbe: row.farbe ?? null,
      icon: row.icon ?? null,
      zielgruppen: row.zielgruppen ?? [],
      alle_gruppen: row.alle_gruppen ?? false,
      erstellt_von: row.erstellt_von ?? null,
      erstellt_am: new Date(row.erstellt_am),
      aktualisiert_am: new Date(row.aktualisiert_am),
    };
  }

  /** Updates an existing event category. Returns null if not found. */
  async updateKategorie(id: string, data: UpdateKategorieData): Promise<VeranstaltungKategorie | null> {
    const fields: string[] = [];
    const values: any[] = [];
    let idx = 1;

    if (data.name !== undefined) { fields.push(`name = $${idx++}`); values.push(data.name); }
    if (data.beschreibung !== undefined) { fields.push(`beschreibung = $${idx++}`); values.push(data.beschreibung); }
    if (data.farbe !== undefined) { fields.push(`farbe = $${idx++}`); values.push(data.farbe); }
    if (data.icon !== undefined) { fields.push(`icon = $${idx++}`); values.push(data.icon); }
    if (data.zielgruppen !== undefined) { fields.push(`zielgruppen = $${idx++}`); values.push(data.zielgruppen); }
    if (data.alle_gruppen !== undefined) { fields.push(`alle_gruppen = $${idx++}`); values.push(data.alle_gruppen); }

    if (fields.length === 0) {
      // Nothing to update — return the existing record
      const existing = await pool.query(
        `SELECT id, name, beschreibung, farbe, icon, zielgruppen, alle_gruppen, erstellt_von, erstellt_am, aktualisiert_am
         FROM veranstaltung_kategorien WHERE id = $1`,
        [id]
      );
      if (existing.rows.length === 0) return null;
      const row = existing.rows[0];
      return {
        id: row.id, name: row.name, beschreibung: row.beschreibung ?? null,
        farbe: row.farbe ?? null, icon: row.icon ?? null, zielgruppen: row.zielgruppen ?? [],
        alle_gruppen: row.alle_gruppen ?? false,
        erstellt_von: row.erstellt_von ?? null,
        erstellt_am: new Date(row.erstellt_am), aktualisiert_am: new Date(row.aktualisiert_am),
      };
    }

    fields.push(`aktualisiert_am = NOW()`);
    values.push(id);

    const result = await pool.query(
      `UPDATE veranstaltung_kategorien SET ${fields.join(', ')}
       WHERE id = $${idx}
       RETURNING id, name, beschreibung, farbe, icon, zielgruppen, alle_gruppen, erstellt_von, erstellt_am, aktualisiert_am`,
      values
    );
    if (result.rows.length === 0) return null;
    const row = result.rows[0];
    return {
      id: row.id, name: row.name, beschreibung: row.beschreibung ?? null,
      farbe: row.farbe ?? null, icon: row.icon ?? null, zielgruppen: row.zielgruppen ?? [],
      alle_gruppen: row.alle_gruppen ?? false,
      erstellt_von: row.erstellt_von ?? null,
      erstellt_am: new Date(row.erstellt_am), aktualisiert_am: new Date(row.aktualisiert_am),
    };
  }

  /**
   * Deletes an event category.
   * The DB schema uses ON DELETE SET NULL, so related events will have
   * their kategorie_id set to NULL automatically.
   */
  async deleteKategorie(id: string): Promise<void> {
    const result = await pool.query(
      `DELETE FROM veranstaltung_kategorien WHERE id = $1`,
      [id]
    );
    if (result.rowCount === 0) {
      throw new Error('Kategorie nicht gefunden');
    }
  }

  // -------------------------------------------------------------------------
  // EVENTS — queries
  // -------------------------------------------------------------------------

  /**
   * Returns events within [from, to] visible to the requesting user.
   * Visibility: alle_gruppen=TRUE OR zielgruppen overlap with userGroups
   * OR user is dashboard_admin.
   */
  async getEventsByDateRange(
    from: Date,
    to: Date,
    userGroups: string[]
  ): Promise<VeranstaltungListItem[]> {
    const result = await pool.query(
      `SELECT
         v.id, v.titel, v.ort, v.kategorie_id,
         k.name  AS kategorie_name,
         k.farbe AS kategorie_farbe,
         k.icon  AS kategorie_icon,
         v.datum_von, v.datum_bis, v.ganztaegig,
         v.alle_gruppen, v.zielgruppen, v.abgesagt, v.anmeldung_erforderlich,
         v.wiederholung, v.wiederholung_parent_id
       FROM veranstaltungen v
       LEFT JOIN veranstaltung_kategorien k ON k.id = v.kategorie_id
       WHERE (v.datum_von BETWEEN $1 AND $2 OR v.datum_bis BETWEEN $1 AND $2 OR (v.datum_von <= $1 AND v.datum_bis >= $2))
         AND (
           v.alle_gruppen = TRUE
           OR v.zielgruppen && $3
           OR 'dashboard_admin' = ANY($3)
         )
       ORDER BY v.datum_von ASC`,
      [from, to, userGroups]
    );
    return result.rows.map(rowToListItem);
  }

  /**
   * Returns the next N upcoming events (datum_von > NOW) visible to the user.
   */
  async getUpcomingEvents(limit: number, userGroups: string[]): Promise<VeranstaltungListItem[]> {
    const result = await pool.query(
      `SELECT
         v.id, v.titel, v.ort, v.kategorie_id,
         k.name  AS kategorie_name,
         k.farbe AS kategorie_farbe,
         k.icon  AS kategorie_icon,
         v.datum_von, v.datum_bis, v.ganztaegig,
         v.alle_gruppen, v.zielgruppen, v.abgesagt, v.anmeldung_erforderlich,
         v.wiederholung, v.wiederholung_parent_id
       FROM veranstaltungen v
       LEFT JOIN veranstaltung_kategorien k ON k.id = v.kategorie_id
       WHERE v.datum_von > NOW()
         AND (
           v.alle_gruppen = TRUE
           OR v.zielgruppen && $2
           OR 'dashboard_admin' = ANY($2)
         )
       ORDER BY v.datum_von ASC
       LIMIT $1`,
      [limit, userGroups]
    );
    return result.rows.map(rowToListItem);
  }

  /** Returns a single event with joined kategorie and creator info. Returns null if not found. */
  async getById(id: string): Promise<Veranstaltung | null> {
    const result = await pool.query(
      `SELECT
         v.*,
         k.name  AS kategorie_name,
         k.farbe AS kategorie_farbe,
         k.icon  AS kategorie_icon,
         u.name  AS erstellt_von_name
       FROM veranstaltungen v
       LEFT JOIN veranstaltung_kategorien k ON k.id = v.kategorie_id
       LEFT JOIN users u ON u.id = v.erstellt_von
       WHERE v.id = $1`,
      [id]
    );
    if (result.rows.length === 0) return null;
    return rowToVeranstaltung(result.rows[0]);
  }

  // -------------------------------------------------------------------------
  // EVENTS — mutations
  // -------------------------------------------------------------------------

  /** Creates a new event and returns the full record. */
  async createEvent(data: CreateVeranstaltungData, userId: string): Promise<Veranstaltung> {
    const result = await pool.query(
      `INSERT INTO veranstaltungen (
         titel, beschreibung, ort, ort_url, kategorie_id,
         datum_von, datum_bis, ganztaegig,
         zielgruppen, alle_gruppen,
         max_teilnehmer, anmeldung_erforderlich, anmeldung_bis,
         erstellt_von, wiederholung
       ) VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,$15)
       RETURNING *`,
      [
        data.titel,
        data.beschreibung ?? null,
        data.ort ?? null,
        data.ort_url ?? null,
        data.kategorie_id ?? null,
        data.datum_von,
        data.datum_bis,
        data.ganztaegig,
        data.zielgruppen,
        data.alle_gruppen,
        data.max_teilnehmer ?? null,
        data.anmeldung_erforderlich,
        data.anmeldung_bis ?? null,
        userId,
        data.wiederholung ?? null,
      ]
    );

    // Generate recurrence instances if wiederholung is specified
    if (data.wiederholung) {
      const occurrenceDates = this.generateRecurrenceDates(data.datum_von, data.datum_bis, data.wiederholung);
      if (occurrenceDates.length > 0) {
        const duration = data.datum_bis.getTime() - data.datum_von.getTime();
        const instanceParams: any[][] = [];
        for (const occDate of occurrenceDates) {
          const occBis = new Date(occDate.getTime() + duration);
          instanceParams.push([
            result.rows[0].id, // wiederholung_parent_id
            data.titel,
            data.beschreibung ?? null,
            data.ort ?? null,
            data.ort_url ?? null,
            data.kategorie_id ?? null,
            occDate,
            occBis,
            data.ganztaegig,
            data.zielgruppen,
            data.alle_gruppen,
            data.max_teilnehmer ?? null,
            data.anmeldung_erforderlich,
            data.anmeldung_bis ?? null,
            userId,
          ]);
        }
        // Insert instances in a loop (simpler than building dynamic bulk insert)
        for (const params of instanceParams) {
          await pool.query(
            `INSERT INTO veranstaltungen (
               wiederholung_parent_id, titel, beschreibung, ort, ort_url, kategorie_id,
               datum_von, datum_bis, ganztaegig, zielgruppen, alle_gruppen,
               max_teilnehmer, anmeldung_erforderlich, anmeldung_bis, erstellt_von
             ) VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,$15)`,
            params
          );
        }
        logger.info(`Created ${instanceParams.length} recurrence instances for event ${result.rows[0].id}`);
      }
    }

    return rowToVeranstaltung(result.rows[0]);
  }

  /** Returns all future occurrence dates for a recurring event (excluding the base occurrence).
   *  Capped at 100 instances and 2 years from the start date. */
  private generateRecurrenceDates(startDate: Date, _endDate: Date, config: WiederholungConfig): Date[] {
    const dates: Date[] = [];
    const defaultLimit = new Date(startDate);
    defaultLimit.setUTCFullYear(defaultLimit.getUTCFullYear() + 2);
    const limitDate = config.bis ? new Date(config.bis + 'T23:59:59Z') : defaultLimit;
    const interval = config.intervall ?? 1;
    // Cap at 100 instances max, and 2 years
    const maxDate = new Date(startDate);
    maxDate.setUTCFullYear(maxDate.getUTCFullYear() + 2);
    const effectiveLimit = limitDate < maxDate ? limitDate : maxDate;

    // Work in UTC to avoid timezone shifts
    let currentMs = startDate.getTime();
    const originalDay = startDate.getUTCDate();
    const startHours = startDate.getUTCHours();
    const startMinutes = startDate.getUTCMinutes();

    while (dates.length < 100) {
      let current = new Date(currentMs);
      // Advance to next occurrence
      switch (config.typ) {
        case 'wöchentlich':
          current.setUTCDate(current.getUTCDate() + 7 * interval);
          break;
        case 'zweiwöchentlich':
          current.setUTCDate(current.getUTCDate() + 14);
          break;
        case 'monatlich_datum': {
          const targetMonth = current.getUTCMonth() + interval;
          current.setUTCDate(1);
          current.setUTCMonth(targetMonth);
          const lastDay = new Date(Date.UTC(current.getUTCFullYear(), current.getUTCMonth() + 1, 0)).getUTCDate();
          current.setUTCDate(Math.min(originalDay, lastDay));
          current.setUTCHours(startHours, startMinutes, 0, 0);
          break;
        }
        case 'monatlich_erster_wochentag': {
          const targetWeekday = config.wochentag ?? 0; // 0=Mon
          current.setUTCMonth(current.getUTCMonth() + 1);
          current.setUTCDate(1);
          // Convert JS Sunday=0 to Monday=0: (getDay()+6)%7
          while ((current.getUTCDay() + 6) % 7 !== targetWeekday) {
            current.setUTCDate(current.getUTCDate() + 1);
          }
          current.setUTCHours(startHours, startMinutes, 0, 0);
          break;
        }
        case 'monatlich_letzter_wochentag': {
          const targetWeekday = config.wochentag ?? 0;
          // Go to last day of next month
          current.setUTCMonth(current.getUTCMonth() + 2);
          current.setUTCDate(0);
          while ((current.getUTCDay() + 6) % 7 !== targetWeekday) {
            current.setUTCDate(current.getUTCDate() - 1);
          }
          current.setUTCHours(startHours, startMinutes, 0, 0);
          break;
        }
      }
      if (current > effectiveLimit) break;
      currentMs = current.getTime();
      dates.push(new Date(current));
    }
    return dates;
  }

  /**
   * Updates an existing event.
   * If the event is a recurrence parent and wiederholung is provided,
   * it deletes all future instances and regenerates them.
   * Returns the updated record or null if not found.
   */
  async updateEvent(id: string, data: UpdateVeranstaltungData): Promise<Veranstaltung | null> {
    const fields: string[] = [];
    const values: any[] = [];
    let idx = 1;

    const fieldMap: Record<string, any> = {
      titel:                 data.titel,
      beschreibung:          data.beschreibung,
      ort:                   data.ort,
      ort_url:               data.ort_url,
      kategorie_id:          data.kategorie_id,
      datum_von:             data.datum_von,
      datum_bis:             data.datum_bis,
      ganztaegig:            data.ganztaegig,
      zielgruppen:           data.zielgruppen,
      alle_gruppen:          data.alle_gruppen,
      max_teilnehmer:        data.max_teilnehmer,
      anmeldung_erforderlich: data.anmeldung_erforderlich,
      anmeldung_bis:         data.anmeldung_bis,
      wiederholung:          data.wiederholung,
    };

    for (const [col, val] of Object.entries(fieldMap)) {
      if (val !== undefined) {
        fields.push(`${col} = $${idx++}`);
        values.push(val);
      }
    }

    if (fields.length === 0) return this.getById(id);

    fields.push(`aktualisiert_am = NOW()`);
    values.push(id);

    const result = await pool.query(
      `UPDATE veranstaltungen SET ${fields.join(', ')} WHERE id = $${idx} RETURNING *`,
      values
    );
    if (result.rows.length === 0) return null;

    const updated = result.rows[0];

    // If this is a recurrence parent and wiederholung was updated, regenerate instances
    if (data.wiederholung !== undefined && updated.wiederholung_parent_id === null) {
      // Delete all existing children of this parent
      await pool.query(
        `DELETE FROM veranstaltungen WHERE wiederholung_parent_id = $1`,
        [id]
      );

      if (data.wiederholung) {
        // Regenerate instances from the (possibly new) dates and config
        const datumVon = data.datum_von ? new Date(data.datum_von) : new Date(updated.datum_von);
        const datumBis = data.datum_bis ? new Date(data.datum_bis) : new Date(updated.datum_bis);
        const occurrenceDates = this.generateRecurrenceDates(datumVon, datumBis, data.wiederholung);
        if (occurrenceDates.length > 0) {
          const duration = datumBis.getTime() - datumVon.getTime();
          for (const occDate of occurrenceDates) {
            const occBis = new Date(occDate.getTime() + duration);
            await pool.query(
              `INSERT INTO veranstaltungen (
                 wiederholung_parent_id, titel, beschreibung, ort, ort_url, kategorie_id,
                 datum_von, datum_bis, ganztaegig, zielgruppen, alle_gruppen,
                 max_teilnehmer, anmeldung_erforderlich, erstellt_von
               ) VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14)`,
              [
                id,
                updated.titel,
                updated.beschreibung ?? null,
                updated.ort ?? null,
                updated.ort_url ?? null,
                updated.kategorie_id ?? null,
                occDate,
                occBis,
                updated.ganztaegig,
                updated.zielgruppen,
                updated.alle_gruppen,
                updated.max_teilnehmer ?? null,
                updated.anmeldung_erforderlich,
                updated.erstellt_von,
              ]
            );
          }
          logger.info(`Regenerated ${occurrenceDates.length} recurrence instances for event ${id}`);
        }
      } else {
        logger.info(`Removed recurrence from event ${id}, all instances deleted`);
      }
    }

    return rowToVeranstaltung(updated);
  }

  /**
   * Soft-cancels an event by setting abgesagt=TRUE, recording the reason
   * and the timestamp.
   */
  async cancelEvent(id: string, grund: string, userId: string): Promise<void> {
    logger.info('Cancelling event', { id, userId });
    const result = await pool.query(
      `UPDATE veranstaltungen
       SET abgesagt = TRUE, abgesagt_grund = $2, abgesagt_am = NOW(), aktualisiert_am = NOW()
       WHERE id = $1`,
      [id, grund]
    );
    if (result.rowCount === 0) {
      throw new Error('Event not found');
    }
  }

  /**
   * Hard-deletes an event (and any recurrence children) from the database.
   * Returns true if the event was found and deleted, false if not found.
   */
  async deleteEvent(id: string, mode: 'all' | 'single' | 'future' = 'all'): Promise<boolean> {
    logger.info('Hard-deleting event', { id, mode });

    if (mode === 'single') {
      // Delete only this single instance
      const result = await pool.query(
        `DELETE FROM veranstaltungen WHERE id = $1`,
        [id]
      );
      return (result.rowCount ?? 0) > 0;
    }

    if (mode === 'future') {
      // Delete this instance and all later instances in the same series
      const event = await pool.query(
        `SELECT id, datum_von, wiederholung_parent_id FROM veranstaltungen WHERE id = $1`,
        [id]
      );
      if (event.rows.length === 0) return false;
      const row = event.rows[0];
      const parentId = row.wiederholung_parent_id ?? row.id;
      const datumVon = new Date(row.datum_von);

      // Delete this instance and all siblings/children with datum_von >= this one
      await pool.query(
        `DELETE FROM veranstaltungen
         WHERE (wiederholung_parent_id = $1 OR id = $1)
           AND datum_von >= $2
           AND id != $1`,
        [parentId, datumVon]
      );
      // Also delete the selected instance itself
      await pool.query(
        `DELETE FROM veranstaltungen WHERE id = $1`,
        [id]
      );
      return true;
    }

    // mode === 'all': Delete parent + all children (original behavior)
    // First check if this is a child instance — find the parent
    const event = await pool.query(
      `SELECT id, wiederholung_parent_id FROM veranstaltungen WHERE id = $1`,
      [id]
    );
    if (event.rows.length === 0) return false;
    const parentId = event.rows[0].wiederholung_parent_id ?? id;

    // Delete all children of the parent
    await pool.query(
      `DELETE FROM veranstaltungen WHERE wiederholung_parent_id = $1`,
      [parentId]
    );
    // Delete the parent itself
    const result = await pool.query(
      `DELETE FROM veranstaltungen WHERE id = $1`,
      [parentId]
    );
    return (result.rowCount ?? 0) > 0;
  }

  // -------------------------------------------------------------------------
  // ICAL TOKEN
  // -------------------------------------------------------------------------

  /**
   * Returns (or creates) the personal iCal subscription token for a user.
   *
   * The subscribeUrl is built from ICAL_BASE_URL (env) so it can be used
   * directly in calendar clients without any further transformation.
   */
  async getOrCreateIcalToken(userId: string): Promise<{ token: string; subscribeUrl: string }> {
    // Attempt to fetch an existing token first
    const existing = await pool.query(
      `SELECT token FROM veranstaltung_ical_tokens WHERE user_id = $1`,
      [userId]
    );

    let token: string;

    if (existing.rows.length > 0) {
      token = existing.rows[0].token;
    } else {
      // Insert a new row — the DEFAULT clause generates the token via gen_random_bytes
      const inserted = await pool.query(
        `INSERT INTO veranstaltung_ical_tokens (user_id)
         VALUES ($1)
         ON CONFLICT (user_id) DO UPDATE SET user_id = EXCLUDED.user_id
         RETURNING token`,
        [userId]
      );
      token = inserted.rows[0].token;
    }

    const baseUrl = (process.env.ICAL_BASE_URL || process.env.CORS_ORIGIN || '').replace(/\/$/, '');
    const subscribeUrl = `${baseUrl}/api/events/calendar.ics?token=${token}`;

    return { token, subscribeUrl };
  }

  // -------------------------------------------------------------------------
  // GROUPS
  // -------------------------------------------------------------------------

  /**
   * Returns distinct group slugs from active users as { id, label } pairs.
   * The label is derived from a known-translations map or humanized from the slug.
   */
  async getAvailableGroups(): Promise<Array<{ id: string; label: string }>> {
    const knownLabels: Record<string, string> = {
      'dashboard_admin':       'Administratoren',
      'dashboard_mitglied':    'Mitglieder',
      'dashboard_fahrmeister': 'Fahrmeister',
      'dashboard_zeugmeister': 'Zeugmeister',
      'dashboard_atemschutz':  'Atemschutzwart',
      'dashboard_jugend':      'Feuerwehrjugend',
      'dashboard_kommandant':  'Kommandanten',
      'dashboard_moderator':   'Moderatoren',
      'dashboard_chargen':     'Gruppenkommandanten',
      'feuerwehr-admin':       'Feuerwehr Admin',
      'feuerwehr-kommandant':  'Feuerwehr Kommandant',
    };

    const humanizeGroupName = (slug: string): string => {
      if (knownLabels[slug]) return knownLabels[slug];
      const stripped = slug.startsWith('dashboard_') ? slug.slice('dashboard_'.length) : slug;
      const spaced = stripped.replace(/-/g, ' ');
      return spaced.charAt(0).toUpperCase() + spaced.slice(1);
    };

    const result = await pool.query(
      `SELECT DISTINCT group_name
       FROM (
         SELECT unnest(authentik_groups) AS group_name
         FROM users
         WHERE authentik_groups IS NOT NULL
       ) g
       WHERE group_name LIKE 'dashboard_%'
         AND group_name != 'dashboard_admin'
       ORDER BY group_name`
    );

    return result.rows.map((row) => ({
      id:    row.group_name as string,
      label: humanizeGroupName(row.group_name as string),
    }));
  }

  // -------------------------------------------------------------------------
  // CONFLICT CHECK
  // -------------------------------------------------------------------------

  /**
   * Returns events that overlap with the given time range.
   * Used to warn users about scheduling conflicts before creating/updating events.
   */
  async checkConflicts(
    datumVon: Date,
    datumBis: Date,
    excludeId?: string
  ): Promise<Array<{ id: string; titel: string; datum_von: Date; datum_bis: Date; kategorie_name: string | null }>> {
    const params: any[] = [datumVon, datumBis];
    let excludeClause = '';
    if (excludeId) {
      excludeClause = ' AND v.id != $3';
      params.push(excludeId);
    }

    const result = await pool.query(
      `SELECT v.id, v.titel, v.datum_von, v.datum_bis,
              k.name AS kategorie_name
       FROM veranstaltungen v
       LEFT JOIN veranstaltung_kategorien k ON k.id = v.kategorie_id
       WHERE v.abgesagt = FALSE
         AND ($1::timestamptz, $2::timestamptz) OVERLAPS (v.datum_von, v.datum_bis)
         ${excludeClause}
       ORDER BY v.datum_von ASC
       LIMIT 10`,
      params
    );

    return result.rows.map((row) => ({
      id: row.id,
      titel: row.titel,
      datum_von: new Date(row.datum_von),
      datum_bis: new Date(row.datum_bis),
      kategorie_name: row.kategorie_name ?? null,
    }));
  }

  // -------------------------------------------------------------------------
  // ICAL EXPORT
  // -------------------------------------------------------------------------

  /**
   * Generates an iCal feed for a given token.
   *
   * Returns null if the token is invalid.
   */
  async getIcalExport(token: string): Promise<string | null> {
    // Validate token and update last-used timestamp
    const tokenResult = await pool.query(
      `UPDATE veranstaltung_ical_tokens
       SET zuletzt_verwendet_am = NOW()
       WHERE token = $1
       RETURNING user_id`,
      [token]
    );

    if (tokenResult.rows.length === 0) return null;

    const userId = tokenResult.rows[0].user_id;

    // Look up user's Authentik groups from DB for group-filtered event visibility
    const userResult = await pool.query(
      `SELECT authentik_groups FROM users WHERE id = $1`,
      [userId]
    );
    const userGroups: string[] = userResult.rows[0]?.authentik_groups ?? [];

    // Fetch events visible to this user: public events (alle_gruppen=TRUE) or events
    // targeting the user's Authentik groups. Includes upcoming events + last 30 days.
    const eventsResult = await pool.query(
      `SELECT v.id, v.titel, v.beschreibung, v.ort, v.datum_von, v.datum_bis, v.ganztaegig, v.abgesagt
       FROM veranstaltungen v
       WHERE (v.alle_gruppen = TRUE OR v.zielgruppen && $1::text[])
         AND v.datum_bis >= NOW() - INTERVAL '30 days'
       ORDER BY v.datum_von ASC`,
      [userGroups]
    );

    const now = new Date();
    const lines: string[] = [
      'BEGIN:VCALENDAR',
      'VERSION:2.0',
      'PRODID:-//Feuerwehr Dashboard//Veranstaltungen//DE',
      'X-WR-CALNAME:Feuerwehr Veranstaltungen',
      'X-WR-TIMEZONE:Europe/Berlin',
      'CALSCALE:GREGORIAN',
    ];

    for (const row of eventsResult.rows) {
      const datumVon = new Date(row.datum_von);
      const datumBis = new Date(row.datum_bis);

      lines.push('BEGIN:VEVENT');
      lines.push(icalFold(`UID:${row.id}@feuerwehr-veranstaltungen`));
      lines.push(`DTSTAMP:${formatIcalDate(now)}`);
      lines.push(`DTSTART:${formatIcalDate(datumVon)}`);
      lines.push(`DTEND:${formatIcalDate(datumBis)}`);
      lines.push(icalFold(`SUMMARY:${row.abgesagt ? '[ABGESAGT] ' : ''}${icalEscape(row.titel)}`));
      if (row.beschreibung) {
        lines.push(icalFold(`DESCRIPTION:${icalEscape(row.beschreibung)}`));
      }
      if (row.ort) {
        lines.push(icalFold(`LOCATION:${icalEscape(row.ort)}`));
      }
      if (row.abgesagt) {
        lines.push('STATUS:CANCELLED');
      }
      lines.push('END:VEVENT');
    }

    lines.push('END:VCALENDAR');

    // RFC 5545 requires CRLF line endings
    return lines.join('\r\n') + '\r\n';
  }
}

export default new EventsService();
