// Mock puro de better-sqlite3 para testes unitários.
// Implementa apenas a API utilizada por db/index.ts e CardRepository.
// Usa arrays em memória para simular tabelas de forma determinística.
//
// Limitações aceitas:
//   - Suporta apenas as queries geradas pelo código de produção (preparadas)
//   - Não valida SQL — parseia apenas os padrões conhecidos
//   - PRAGMA user_version, journal_mode e foreign_keys são aceitos sem efeito

class Statement {
  constructor(db, sql) {
    this._db = db
    this._sql = sql.replace(/\s+/g, ' ').trim()
  }

  run(params) {
    return this._db._run(this._sql, params)
  }

  get(...args) {
    return this._db._get(this._sql, args.flat())
  }

  all(...args) {
    return this._db._all(this._sql, args.flat())
  }
}

class Database {
  constructor() {
    // Tabelas em memória
    this._tables = {
      cards: [],
      card_stages: [],
    }
    this._nextId = { card_stages: 1 }
    this._userVersion = 0
  }

  exec(sql) {
    const stmts = sql.split(';').map(s => s.trim()).filter(Boolean)
    for (const stmt of stmts) {
      this._run(stmt, {})
    }
    return this
  }

  prepare(sql) {
    return new Statement(this, sql)
  }

  pragma(pragma, opts) {
    const p = pragma.trim()
    if (p === 'user_version') {
      if (opts && opts.simple) return this._userVersion
      return [{ user_version: this._userVersion }]
    }
    if (p === 'journal_mode') {
      if (opts && opts.simple) return 'memory'
      return [{ journal_mode: 'memory' }]
    }
    // Para SET pragmas (foreign_keys = ON, etc.) ignoramos silenciosamente
    return null
  }

  close() {}

  // ── Motor interno ────────────────────────────────────────────────────────────

  _resolveParams(sql, params) {
    // Resolve @param → valor do objeto params
    if (params && typeof params === 'object' && !Array.isArray(params)) {
      return sql.replace(/@(\w+)/g, (_, k) => {
        const v = params[k]
        return v === null || v === undefined ? 'NULL' : `'${String(v).replace(/'/g, "''")}'`
      })
    }
    return sql
  }

  _run(sql, params) {
    const resolved = this._resolveParams(sql, params || {})

    // PRAGMA SET
    if (/^PRAGMA\s+\w+\s*=/.test(resolved)) return { changes: 0 }

    // PRAGMA user_version = N
    const uvMatch = resolved.match(/^PRAGMA\s+user_version\s*=\s*(\d+)$/i)
    if (uvMatch) { this._userVersion = parseInt(uvMatch[1]); return { changes: 0 } }

    // CREATE TABLE / CREATE INDEX / CREATE TRIGGER / ALTER TABLE — aceitar e ignorar
    if (/^CREATE\s+(TABLE|INDEX|TRIGGER|UNIQUE)/i.test(resolved)) return { changes: 0 }
    if (/^ALTER\s+TABLE/i.test(resolved)) return { changes: 0 }
    if (/^BEGIN|^COMMIT|^ROLLBACK/i.test(resolved)) return { changes: 0 }

    // INSERT OR IGNORE INTO cards
    const insCardsMatch = resolved.match(
      /^INSERT OR IGNORE INTO cards\s*\([^)]+\)\s*VALUES\s*\((.+)\)$/is
    )
    if (insCardsMatch) {
      return this._insertCardOrIgnore(params)
    }

    // INSERT OR REPLACE INTO card_stages
    const insStagesMatch = resolved.match(
      /^INSERT OR REPLACE INTO card_stages\s*\([^)]+\)\s*VALUES\s*\((.+)\)$/is
    )
    if (insStagesMatch) {
      return this._upsertStage(params)
    }

    // UPDATE cards SET status = @status WHERE card_id = @card_id
    if (/^UPDATE cards SET status/i.test(resolved)) {
      return this._updateCards('status', params)
    }

    // UPDATE cards SET texto_original = @texto_original WHERE card_id = @card_id
    if (/^UPDATE cards SET texto_original/i.test(resolved)) {
      return this._updateCards('texto_original', params)
    }

    // UPDATE cards SET deleted_at = ... (direto via exec/prepare genérico)
    if (/^UPDATE cards SET deleted_at/i.test(resolved)) {
      const m = resolved.match(/deleted_at\s*=\s*'([^']+)'\s+WHERE card_id\s*=\s*'([^']+)'/i)
      if (m) {
        const card = this._tables.cards.find(c => c.card_id === m[2])
        if (card) card.deleted_at = m[1]
      }
      return { changes: 1 }
    }

    // UPDATE cards SET atualizado_em (trigger — ignorar)
    if (/^UPDATE cards SET atualizado_em/i.test(resolved)) return { changes: 0 }

    // DELETE FROM
    if (/^DELETE FROM card_stages/i.test(resolved)) { this._tables.card_stages = []; return { changes: 0 } }
    if (/^DELETE FROM cards/i.test(resolved)) { this._tables.cards = []; return { changes: 0 } }

    return { changes: 0 }
  }

  _get(sql, positional) {
    const norm = sql.replace(/\s+/g, ' ').trim()

    // SELECT * FROM cards WHERE card_id = ?
    if (/SELECT \* FROM cards WHERE card_id = \?/i.test(norm)) {
      return this._tables.cards.find(c => c.card_id === positional[0]) ?? undefined
    }

    return undefined
  }

  _all(sql, positional) {
    const norm = sql.replace(/\s+/g, ' ').trim()

    // listByUser
    if (/SELECT card_id, titulo FROM cards WHERE slack_user_id/i.test(norm)) {
      const uid = positional[0]
      return this._tables.cards
        .filter(c => c.slack_user_id === uid && c.status !== 'aprovado' && c.deleted_at == null)
        .sort((a, b) => (b.criado_em || '').localeCompare(a.criado_em || ''))
        .map(c => ({ card_id: c.card_id, titulo: c.titulo }))
    }

    // getStages
    if (/SELECT \* FROM card_stages WHERE card_id = \? ORDER BY id ASC/i.test(norm)) {
      const cid = positional[0]
      return this._tables.card_stages.filter(s => s.card_id === cid).sort((a, b) => a.id - b.id)
    }

    return []
  }

  // ── Helpers de tabela ────────────────────────────────────────────────────────

  _insertCardOrIgnore(params) {
    const exists = this._tables.cards.find(c => c.card_id === params.card_id)
    if (exists) return { changes: 0 }
    const now = new Date().toISOString().replace(/\.\d{3}Z$/, 'Z')
    this._tables.cards.push({
      card_id: params.card_id,
      provider: params.provider ?? 'jira',
      external_url: params.external_url ?? null,
      slack_user_id: params.slack_user_id,
      slack_channel_id: params.slack_channel_id,
      titulo: params.titulo,
      texto_original: params.texto_original,
      status: params.status ?? 'em_progresso',
      criado_em: now,
      atualizado_em: now,
      deleted_at: null,
    })
    return { changes: 1 }
  }

  _upsertStage(params) {
    const idx = this._tables.card_stages.findIndex(
      s => s.card_id === params.card_id && s.stage === params.stage
    )
    if (idx >= 0) {
      this._tables.card_stages[idx].conteudo = params.conteudo
      this._tables.card_stages[idx].gerado_em = new Date().toISOString()
    } else {
      this._tables.card_stages.push({
        id: this._nextId.card_stages++,
        card_id: params.card_id,
        stage: params.stage,
        conteudo: params.conteudo,
        gerado_em: new Date().toISOString(),
      })
    }
    return { changes: 1 }
  }

  _updateCards(field, params) {
    const card = this._tables.cards.find(c => c.card_id === params.card_id)
    if (card) card[field] = params[field]
    return { changes: card ? 1 : 0 }
  }
}

module.exports = Database
