import { MySqlDialect } from 'drizzle-orm/mysql-core';
import { CaseReportRepository, rowsOf } from './case-report.repository';

//* 가짜 execute 는 실제 mysql2 처럼 [rows, fields] 튜플을 돌려준다(행 배열만 돌려주는 모의 금지 — 명세 3.3 절)
const dialect = new MySqlDialect();
function repoWith(responder: (sql: string, params: unknown[]) => unknown[]) {
  const calls: { sql: string; params: unknown[] }[] = [];
  const drizzle = {
    execute: jest.fn(async (q: any) => {
      const { sql, params } = dialect.sqlToQuery(q);
      calls.push({ sql, params });
      return [responder(sql, params), []];
    }),
  };
  const repo = new CaseReportRepository(
    drizzle as any,
    { get: () => 'spec-aes' } as any,
  );
  return { repo, calls };
}

describe('심평원·국세청 조회 어댑터', () => {
  it('rowsOf 는 튜플의 첫 원소(행 배열)를 꺼낸다', () => {
    expect(rowsOf([[{ a: 1 }], []])).toEqual([{ a: 1 }]);
  });

  it('목록은 소유 사슬(구독 회원)·삭제 안 됨·최근 60일·최신순 100건, 이름 검색은 복호화 값 LIKE(와일드카드 이스케이프)', async () => {
    const { repo, calls } = repoWith(() => [
      {
        odd_id: 9001,
        odd_transaction_id: 'a1',
        odd_created_at: '2026-10-20 10:00:00',
        odd_state: 'ODD006',
        usr_id: 9001,
        usr_name: '김합성',
        usr_birth: '19800101',
        basic_count: 12,
        rows_created_at: '2026-10-20 10:00:05',
        remaining_days: '23',
        delete_on: '2026-11-20',
        detail_count: 6,
        prescribe_count: 4,
      },
      {
        odd_id: 9003,
        odd_transaction_id: null,
        odd_created_at: '2026-10-19 10:00:00',
        odd_state: 'ODD006',
        usr_id: 9001,
        usr_name: '김합성',
        usr_birth: '19800101',
        basic_count: 0,
        rows_created_at: null,
        remaining_days: null,
        delete_on: null,
        detail_count: 0,
        prescribe_count: 0,
      },
      {
        odd_id: 9004,
        odd_transaction_id: null,
        odd_created_at: '2026-10-18 10:00:00',
        odd_state: 'ODD001',
        usr_id: 9001,
        usr_name: '김합성',
        usr_birth: '19800101',
        basic_count: 0,
        rows_created_at: null,
        remaining_days: null,
        delete_on: null,
        detail_count: 0,
        prescribe_count: 0,
      },
    ]);
    const items = await repo.listHiraOrders(9001, '김%_');
    const { sql, params } = calls[0];
    expect(sql).toContain('JOIN tn_subscribe s ON s.sub_id = p.sub_id');
    expect(sql).toContain('s.mem_id = ?');
    expect(sql).toContain('d.odd_deletion = 0');
    expect(sql).toContain('INTERVAL 60 DAY');
    expect(sql).toMatch(/ORDER BY d.odd_created_at DESC LIMIT 100$/);
    expect(sql).not.toMatch(/SELECT \*/);
    expect(params).toEqual([
      'spec-aes',
      'spec-aes',
      9001,
      'spec-aes',
      '%김\\%\\_%',
    ]);
    expect(items[0]).toEqual({
      oddId: 9001,
      transactionId: 'a1',
      createdAt: '2026-10-20 10:00:00',
      customer: { usrId: 9001, name: '김합성', birth: '19800101' },
      rowCounts: { basic: 12, detail: 6, prescribe: 4 },
      rowsCreatedAt: '2026-10-20 10:00:05',
      remainingDays: 23,
      available: true,
      deleteOn: '2026-11-20',
      state: 'READY',
    });
    //* 자료를 받은 건(분석완료)인데 행이 0개면 삭제 배치가 지운 것, 인증 중인 건은 아직 조회 전
    expect(items[1]).toMatchObject({
      available: false,
      remainingDays: 0,
      rowsCreatedAt: null,
      deleteOn: null,
      state: 'EXPIRED',
    });
    expect(items[2]).toMatchObject({ available: false, state: 'PENDING' });
  });

  it('지워지는 날은 첫 행 + 30일, 06:00 배치 뒤면 다음 날(DB 에서 YYYY-MM-DD 로 만든다)', async () => {
    const { repo, calls } = repoWith(() => []);
    await repo.listHiraOrders(9001);
    expect(calls[0].sql).toContain(
      "DATE_FORMAT(MIN(b.trb_created_at) + INTERVAL 30 DAY + INTERVAL 18 HOUR, '%Y-%m-%d')",
    );
    expect(calls[0].sql).toContain('d.odd_state');
  });

  it('고객 목록은 로그인 회원의 tn_user 만, 이름·생년월일 복호화, 500명 상한, 이름 검색은 와일드카드 이스케이프', async () => {
    const { repo, calls } = repoWith(() => [
      { usr_id: 9004, usr_name: '이합성', usr_birth: '19850505' },
      { usr_id: '9001', usr_name: null, usr_birth: null },
    ]);
    expect(await repo.listCustomers(9001)).toEqual([
      { usrId: 9004, name: '이합성', birth: '19850505' },
      { usrId: 9001, name: null, birth: null },
    ]);
    const { sql, params } = calls[0];
    expect(sql).toContain('FROM tn_user u WHERE u.mem_id = ?');
    expect(sql).toMatch(/LIMIT 500$/);
    expect(sql).not.toMatch(/SELECT \*/);
    expect(params).toEqual(['spec-aes', 'spec-aes', 9001]);
    await repo.listCustomers(9001, '이%');
    expect(calls[1].params).toEqual([
      'spec-aes',
      'spec-aes',
      9001,
      'spec-aes',
      '%이\\%%',
    ]);
  });

  it('소유하지 않은 분석번호는 null', async () => {
    const { repo, calls } = repoWith(() => []);
    expect(await repo.findOwnedOrder(9001, 9002)).toBeNull();
    expect(calls[0].sql).toContain('d.odd_id = ?');
    expect(calls[0].params.slice(2)).toEqual([9001, 9002]);
  });

  it('스냅샷 표 다섯은 칸 이름을 적어 그 분석번호만 고른다', async () => {
    const { repo, calls } = repoWith(() => [{ x: 1 }]);
    const tables = await repo.hiraTables(9001);
    expect(Object.keys(tables)).toEqual([
      'tt_treat_basic',
      'tt_treat_detail',
      'tt_treat_prescribe',
      'tn_analysis_basic',
      'tn_analysis_detail',
    ]);
    expect(calls).toHaveLength(5);
    for (const c of calls) {
      expect(c.sql).not.toMatch(/SELECT \*/);
      expect(c.sql).toContain('WHERE odd_id = ?');
      expect(c.params).toEqual([9001]);
    }
    expect(
      calls.find((c) => c.sql.includes('tn_analysis_detail'))?.sql,
    ).toContain('asd_in_operation');
  });

  it('국세청 테이블 감지: n=0 이면 없음, 목록은 완료·결과 있는 내 건만, JSON 칸은 문자열이어도 푼다', async () => {
    const absent = repoWith(() => [{ n: 0 }]);
    expect(await absent.repo.ntsTablePresent()).toBe(false);
    expect(absent.calls[0].sql).toContain("table_name = 'tn_loss_analysis'");

    const present = repoWith((sql) =>
      sql.includes('information_schema')
        ? [{ n: 1 }]
        : [
            {
              lsa_id: 31,
              usr_id: 9001,
              usr_name: '김합성',
              lsa_completed_at: '2026-10-21 09:00:00',
              lsa_available_years: '[2025]',
              lsa_total_rows: 2,
              lsa_result: '{"startYear":2021}',
            },
          ],
    );
    expect(await present.repo.ntsTablePresent()).toBe(true);
    const list = await present.repo.listNts(9001);
    expect(list).toEqual([
      {
        lsaId: 31,
        usrId: 9001,
        customerName: '김합성',
        completedAt: '2026-10-21 09:00:00',
        years: [2025],
        totalRows: 2,
      },
    ]);
    const listSql = present.calls[1].sql;
    expect(listSql).toContain('l.mem_id = ?');
    expect(listSql).toContain("l.lsa_state = 'COMPLETED'");
    expect(listSql).toContain('l.lsa_result IS NOT NULL');
    expect(listSql).not.toContain('l.lsa_result,');
    const one = await present.repo.findNts(9001, 31);
    expect(one).toEqual({
      lsa_id: 31,
      usr_id: 9001,
      completed_at: '2026-10-21 09:00:00',
      lsa_result: { startYear: 2021 },
    });
    expect(present.calls[2].params).toEqual(['spec-aes', 9001, 31]);
    await present.repo.listNts(9001, 7); // 같은 고객 건만(국세청 자동 연결)
    expect(present.calls[3].sql).toContain('l.usr_id = ?');
    expect(present.calls[3].params).toEqual(['spec-aes', 9001, 7]);
    expect(listSql).not.toContain('l.usr_id = ?');
  });
});