import { createHash, randomBytes, timingSafeEqual } from 'node:crypto';
import { nanoid } from 'nanoid';
import { getDb } from '../db/index.js';
import type {
  AttachmentMeta,
  Decision,
  IgnoredSender,
  IngestInput,
  IntakeItem,
  IntakeListFilters,
  SourceType,
  Status,
} from '../types.js';
import { config } from '../config.js';

interface Row {
  id: string;
  source_type: string;
  source_account: string | null;
  source_external_id: string;
  source_url: string | null;
  sender_id: string | null;
  sender_name: string | null;
  sender_email: string | null;
  client_project: string | null;
  title: string;
  body: string;
  metadata: string;
  attachments: string;
  parse_warnings: string;
  raw_payload: string | null;
  received_at: string;
  ingested_at: string;
  decision: string;
  status: string;
  maj_wp_ticket_id: string | null;
  remote_status: string | null;
  remote_message: string | null;
  last_sync_at: string | null;
  error: string | null;
  claim_token: string | null;
  claim_expires_at: string | null;
  claimed_by: string | null;
  idempotency_key: string;
}

function rowToItem(row: Row): IntakeItem {
  return {
    ...row,
    source_type: row.source_type as SourceType,
    decision: row.decision as Decision,
    status: row.status as Status,
    metadata: JSON.parse(row.metadata || '{}'),
    attachments: JSON.parse(row.attachments || '[]') as AttachmentMeta[],
    parse_warnings: JSON.parse(row.parse_warnings || '[]') as string[],
    raw_payload: row.raw_payload ? JSON.parse(row.raw_payload) : null,
  };
}

function buildIdempotencyKey(input: IngestInput): string {
  const account = input.source_account ?? '';
  // Monday API et extension Chrome partagent la même clé pour un même item
  if (input.source_type === 'monday_api' || input.source_type === 'monday_chrome') {
    return createHash('sha256').update(`monday:${input.source_external_id}`).digest('hex');
  }
  return createHash('sha256')
    .update(`${input.source_type}:${account}:${input.source_external_id}`)
    .digest('hex');
}

function normalizeSenderKey(email?: string | null, senderId?: string | null): string | null {
  if (email) return email.toLowerCase().trim();
  if (senderId) return `monday:${senderId}`;
  return null;
}

export class IntakeService {
  ingest(input: IngestInput): { item: IntakeItem; created: boolean; duplicate: boolean } {
    const db = getDb();
    const idempotencyKey = buildIdempotencyKey(input);
    const now = new Date().toISOString();

    const existing = db
      .prepare('SELECT * FROM intake_items WHERE idempotency_key = ?')
      .get(idempotencyKey) as Row | undefined;

    if (existing) {
      return { item: rowToItem(existing), created: false, duplicate: true };
    }

    const senderKey = normalizeSenderKey(input.sender_email, input.sender_id);
    let decision: Decision = 'pending';
    const parseWarnings = [...(input.parse_warnings ?? [])];

    if (senderKey) {
      const blocked = db
        .prepare(
          `SELECT * FROM ignored_senders WHERE active = 1 AND (
            (sender_type = 'email' AND sender_key = ?) OR
            (sender_type = 'monday_user' AND sender_key = ?)
          )`,
        )
        .get(
          input.sender_email?.toLowerCase().trim() ?? '',
          senderKey,
        ) as IgnoredSender | undefined;

      if (blocked) {
        decision = 'auto_ignored';
        parseWarnings.push(`Expéditeur blacklisté: ${blocked.sender_label ?? blocked.sender_key}`);
      }
    }

    const id = nanoid();
    const item: IntakeItem = {
      id,
      source_type: input.source_type,
      source_account: input.source_account ?? null,
      source_external_id: input.source_external_id,
      source_url: input.source_url ?? null,
      sender_id: input.sender_id ?? null,
      sender_name: input.sender_name ?? null,
      sender_email: input.sender_email ?? null,
      client_project: input.client_project ?? null,
      title: input.title,
      body: input.body ?? '',
      metadata: input.metadata ?? {},
      attachments: input.attachments ?? [],
      parse_warnings: parseWarnings,
      raw_payload: input.raw_payload ?? null,
      received_at: input.received_at ?? now,
      ingested_at: now,
      decision,
      status: 'none',
      maj_wp_ticket_id: null,
      remote_status: null,
      remote_message: null,
      last_sync_at: null,
      error: null,
      claim_token: null,
      claim_expires_at: null,
      claimed_by: null,
      idempotency_key: idempotencyKey,
    };

    try {
      db.prepare(
        `INSERT INTO intake_items (
          id, source_type, source_account, source_external_id, source_url,
          sender_id, sender_name, sender_email, client_project, title, body,
          metadata, attachments, parse_warnings, raw_payload, received_at, ingested_at,
          decision, status, idempotency_key
        ) VALUES (
          @id, @source_type, @source_account, @source_external_id, @source_url,
          @sender_id, @sender_name, @sender_email, @client_project, @title, @body,
          @metadata, @attachments, @parse_warnings, @raw_payload, @received_at, @ingested_at,
          @decision, @status, @idempotency_key
        )`,
      ).run({
        ...item,
        metadata: JSON.stringify(item.metadata),
        attachments: JSON.stringify(item.attachments),
        parse_warnings: JSON.stringify(item.parse_warnings),
        raw_payload: item.raw_payload ? JSON.stringify(item.raw_payload) : null,
      });
    } catch (err: unknown) {
      const message = err instanceof Error ? err.message : String(err);
      if (message.includes('UNIQUE constraint')) {
        const dup = db
          .prepare('SELECT * FROM intake_items WHERE idempotency_key = ?')
          .get(idempotencyKey) as Row;
        return { item: rowToItem(dup), created: false, duplicate: true };
      }
      throw err;
    }

    return { item, created: true, duplicate: false };
  }

  getById(id: string): IntakeItem | null {
    const row = getDb().prepare('SELECT * FROM intake_items WHERE id = ?').get(id) as Row | undefined;
    return row ? rowToItem(row) : null;
  }

  list(filters: IntakeListFilters = {}): IntakeItem[] {
    const conditions: string[] = [];
    const params: unknown[] = [];

    if (filters.decision) {
      const decisions = Array.isArray(filters.decision) ? filters.decision : [filters.decision];
      conditions.push(`decision IN (${decisions.map(() => '?').join(',')})`);
      params.push(...decisions);
    }
    if (filters.status) {
      const statuses = Array.isArray(filters.status) ? filters.status : [filters.status];
      conditions.push(`status IN (${statuses.map(() => '?').join(',')})`);
      params.push(...statuses);
    }
    if (filters.source_type) {
      conditions.push('source_type = ?');
      params.push(filters.source_type);
    }

    const where = conditions.length ? `WHERE ${conditions.join(' AND ')}` : '';
    const limit = filters.limit ?? 100;
    const offset = filters.offset ?? 0;

    const rows = getDb()
      .prepare(
        `SELECT * FROM intake_items ${where} ORDER BY ingested_at DESC LIMIT ? OFFSET ?`,
      )
      .all(...params, limit, offset) as Row[];

    return rows.map(rowToItem);
  }

  approve(id: string): IntakeItem | null {
    const db = getDb();
    const item = this.getById(id);
    if (!item || item.decision !== 'pending') return null;

    db.prepare(
      `UPDATE intake_items SET decision = 'approved', status = 'approved', error = NULL WHERE id = ? AND decision = 'pending'`,
    ).run(id);

    return this.getById(id);
  }

  ignore(id: string): IntakeItem | null {
    const db = getDb();
    const item = this.getById(id);
    if (!item || item.decision !== 'pending') return null;

    db.prepare(`UPDATE intake_items SET decision = 'ignored', status = 'none' WHERE id = ? AND decision = 'pending'`).run(id);
    return this.getById(id);
  }

  restore(id: string): IntakeItem | null {
    const db = getDb();
    const item = this.getById(id);
    if (!item || (item.decision !== 'ignored' && item.decision !== 'auto_ignored')) return null;

    db.prepare(
      `UPDATE intake_items SET decision = 'pending', status = 'none', error = NULL WHERE id = ?`,
    ).run(id);

    return this.getById(id);
  }

  claim(id: string, consumerId: string): { ok: true; item: IntakeItem; claim_token: string } | { ok: false; reason: string; item?: IntakeItem } {
    const db = getDb();
    const now = new Date();
    const item = this.getById(id);

    if (!item) return { ok: false, reason: 'not_found' };
    if (item.decision !== 'approved') return { ok: false, reason: 'not_approved', item };

    const expiredClaim =
      item.status === 'claimed' &&
      item.claim_expires_at &&
      new Date(item.claim_expires_at) < now;

    const claimable =
      item.status === 'none' ||
      item.status === 'approved' ||
      expiredClaim;

    if (!claimable) {
      return { ok: false, reason: 'already_claimed', item };
    }

    const claimToken = randomBytes(32).toString('hex');
    const expiresAt = new Date(now.getTime() + config.claimLeaseSeconds * 1000).toISOString();

    const result = db
      .prepare(
        `UPDATE intake_items SET
          status = 'claimed',
          claim_token = ?,
          claim_expires_at = ?,
          claimed_by = ?,
          last_sync_at = ?
        WHERE id = ? AND decision = 'approved' AND (
          status IN ('none', 'approved') OR
          (status = 'claimed' AND claim_expires_at < ?)
        )`,
      )
      .run(claimToken, expiresAt, consumerId, now.toISOString(), id, now.toISOString());

    if (result.changes === 0) {
      const current = this.getById(id);
      return { ok: false, reason: 'claim_conflict', item: current ?? undefined };
    }

    this.logSync(id, 'claim', { consumerId, claimToken });
    const updated = this.getById(id)!;
    return { ok: true, item: updated, claim_token: claimToken };
  }

  claimNextReady(consumerId: string): { ok: true; item: IntakeItem; claim_token: string } | { ok: false; reason: string } {
    const db = getDb();
    const now = new Date().toISOString();

    const candidate = db
      .prepare(
        `SELECT id FROM intake_items
         WHERE decision = 'approved' AND (
           status IN ('none', 'approved') OR
           (status = 'claimed' AND claim_expires_at < ?)
         )
         ORDER BY ingested_at ASC LIMIT 1`,
      )
      .get(now) as { id: string } | undefined;

    if (!candidate) return { ok: false, reason: 'none_ready' };
    const result = this.claim(candidate.id, consumerId);
    if (!result.ok) return result;
    return result;
  }

  acknowledgeCreated(
    id: string,
    payload: {
      claim_token?: string;
      maj_wp_ticket_id: string;
      remote_status?: string;
      remote_message?: string;
    },
  ): IntakeItem | null {
    const db = getDb();
    const item = this.getById(id);
    if (!item) return null;

    if (item.maj_wp_ticket_id === payload.maj_wp_ticket_id && item.status === 'created') {
      return item;
    }

    if (payload.claim_token && item.claim_token && payload.claim_token !== item.claim_token) {
      if (item.status !== 'created') return null;
    }

    const now = new Date().toISOString();
    db.prepare(
      `UPDATE intake_items SET
        status = 'created',
        maj_wp_ticket_id = ?,
        remote_status = ?,
        remote_message = ?,
        last_sync_at = ?,
        error = NULL,
        claim_token = NULL,
        claim_expires_at = NULL
      WHERE id = ?`,
    ).run(
      payload.maj_wp_ticket_id,
      payload.remote_status ?? null,
      payload.remote_message ?? null,
      now,
      id,
    );

    this.logSync(id, 'created', payload);
    return this.getById(id);
  }

  updateRemoteStatus(
    id: string,
    payload: {
      maj_wp_ticket_id?: string;
      status?: Status;
      remote_status?: string;
      remote_message?: string;
      error?: string | null;
    },
  ): IntakeItem | null {
    const db = getDb();
    const item = this.getById(id);
    if (!item) return null;

    const now = new Date().toISOString();
    const newStatus = payload.status ?? item.status;

    db.prepare(
      `UPDATE intake_items SET
        status = ?,
        maj_wp_ticket_id = COALESCE(?, maj_wp_ticket_id),
        remote_status = COALESCE(?, remote_status),
        remote_message = COALESCE(?, remote_message),
        error = ?,
        last_sync_at = ?
      WHERE id = ?`,
    ).run(
      newStatus,
      payload.maj_wp_ticket_id ?? null,
      payload.remote_status ?? null,
      payload.remote_message ?? null,
      payload.error ?? null,
      now,
      id,
    );

    this.logSync(id, 'status_update', payload);
    return this.getById(id);
  }

  listReady(limit = 50): IntakeItem[] {
    const now = new Date().toISOString();
    const rows = getDb()
      .prepare(
        `SELECT * FROM intake_items
         WHERE decision = 'approved' AND (
           status IN ('none', 'approved') OR
           (status = 'claimed' AND claim_expires_at < ?)
         )
         ORDER BY ingested_at ASC LIMIT ?`,
      )
      .all(now, limit) as Row[];

    return rows.map(rowToItem);
  }

  listTickets(): IntakeItem[] {
    return this.list({
      decision: 'approved',
      limit: 200,
    }).filter((i) => i.status !== 'none' || i.decision === 'approved');
  }

  private logSync(intakeId: string, action: string, payload: unknown): void {
    getDb()
      .prepare('INSERT INTO sync_log (intake_id, action, payload, created_at) VALUES (?, ?, ?, ?)')
      .run(intakeId, action, JSON.stringify(payload), new Date().toISOString());
  }
}

export class BlacklistService {
  addSender(input: {
    sender_type: 'email' | 'monday_user';
    sender_key: string;
    sender_label?: string;
    reason?: string;
  }): IgnoredSender {
    const db = getDb();
    const key =
      input.sender_type === 'email' ? input.sender_key.toLowerCase().trim() : input.sender_key;
    const id = nanoid();
    const now = new Date().toISOString();

    db.prepare(
      `INSERT INTO ignored_senders (id, sender_type, sender_key, sender_label, reason, created_at, active)
       VALUES (?, ?, ?, ?, ?, ?, 1)
       ON CONFLICT(sender_type, sender_key) DO UPDATE SET active = 1, sender_label = excluded.sender_label, reason = excluded.reason`,
    ).run(id, input.sender_type, key, input.sender_label ?? null, input.reason ?? null, now);

    return db
      .prepare('SELECT * FROM ignored_senders WHERE sender_type = ? AND sender_key = ?')
      .get(input.sender_type, key) as IgnoredSender;
  }

  list(activeOnly = true): IgnoredSender[] {
    const sql = activeOnly
      ? 'SELECT * FROM ignored_senders WHERE active = 1 ORDER BY created_at DESC'
      : 'SELECT * FROM ignored_senders ORDER BY created_at DESC';
    const rows = getDb().prepare(sql).all() as Array<Omit<IgnoredSender, 'active'> & { active: number }>;
    return rows.map((r) => ({ ...r, active: Boolean(r.active) }));
  }

  reactivate(id: string): IgnoredSender | null {
    getDb().prepare('UPDATE ignored_senders SET active = 1 WHERE id = ?').run(id);
    const row = getDb().prepare('SELECT * FROM ignored_senders WHERE id = ?').get(id) as
      | (Omit<IgnoredSender, 'active'> & { active: number })
      | undefined;
    return row ? { ...row, active: Boolean(row.active) } : null;
  }

  deactivate(id: string): IgnoredSender | null {
    getDb().prepare('UPDATE ignored_senders SET active = 0 WHERE id = ?').run(id);
    const row = getDb().prepare('SELECT * FROM ignored_senders WHERE id = ?').get(id) as
      | (Omit<IgnoredSender, 'active'> & { active: number })
      | undefined;
    return row ? { ...row, active: Boolean(row.active) } : null;
  }

  ignoreSenderFromItem(itemId: string, reason?: string): IgnoredSender | null {
    const intake = new IntakeService().getById(itemId);
    if (!intake) return null;

    if (intake.sender_email) {
      return this.addSender({
        sender_type: 'email',
        sender_key: intake.sender_email,
        sender_label: intake.sender_name ?? intake.sender_email,
        reason: reason ?? 'Ignoré depuis une demande',
      });
    }
    if (intake.sender_id) {
      return this.addSender({
        sender_type: 'monday_user',
        sender_key: intake.sender_id,
        sender_label: intake.sender_name ?? intake.sender_id,
        reason: reason ?? 'Ignoré depuis une demande Monday',
      });
    }
    return null;
  }
}

export function hashToken(token: string): string {
  return createHash('sha256').update(token).digest('hex');
}

export function verifyToken(token: string, hash: string): boolean {
  const a = Buffer.from(hashToken(token));
  const b = Buffer.from(hash);
  if (a.length !== b.length) return false;
  return timingSafeEqual(a, b);
}

export class ExtensionAuthService {
  createPairingCode(): { code: string; expires_at: string } {
    const db = getDb();
    const code = randomBytes(4).toString('hex').toUpperCase();
    const expiresAt = new Date(Date.now() + 10 * 60 * 1000).toISOString();
    db.prepare('INSERT INTO pairing_codes (code, expires_at, used) VALUES (?, ?, 0)').run(code, expiresAt);
    return { code, expires_at: expiresAt };
  }

  redeemPairingCode(code: string, label?: string): { token: string; id: string } | null {
    const db = getDb();
    const row = db
      .prepare('SELECT * FROM pairing_codes WHERE code = ? AND used = 0')
      .get(code.toUpperCase()) as { code: string; expires_at: string } | undefined;

    if (!row || new Date(row.expires_at) < new Date()) return null;

    const token = randomBytes(32).toString('hex');
    const id = nanoid();
    const now = new Date().toISOString();

    db.prepare('UPDATE pairing_codes SET used = 1 WHERE code = ?').run(code.toUpperCase());
    db.prepare(
      'INSERT INTO extension_tokens (id, label, token_hash, created_at, active) VALUES (?, ?, ?, ?, 1)',
    ).run(id, label ?? 'Extension Chrome', hashToken(token), now);

    return { token, id };
  }

  validateExtensionToken(token: string): boolean {
    const hash = hashToken(token);
    const row = getDb()
      .prepare('SELECT id FROM extension_tokens WHERE token_hash = ? AND active = 1')
      .get(hash) as { id: string } | undefined;

    if (row) {
      getDb()
        .prepare('UPDATE extension_tokens SET last_used_at = ? WHERE id = ?')
        .run(new Date().toISOString(), row.id);
      return true;
    }
    return false;
  }

  listTokens(): Array<{ id: string; label: string | null; created_at: string; last_used_at: string | null; active: boolean }> {
    const rows = getDb()
      .prepare('SELECT id, label, created_at, last_used_at, active FROM extension_tokens ORDER BY created_at DESC')
      .all() as Array<{ id: string; label: string | null; created_at: string; last_used_at: string | null; active: number }>;
    return rows.map((r) => ({ ...r, active: Boolean(r.active) }));
  }

  revokeToken(id: string): void {
    getDb().prepare('UPDATE extension_tokens SET active = 0 WHERE id = ?').run(id);
  }
}

export const intakeService = new IntakeService();
export const blacklistService = new BlacklistService();
export const extensionAuthService = new ExtensionAuthService();