import { expect } from 'chai';
import request from 'supertest';

import { parseProp, UITypes } from 'nocodb-sdk';
import { Model } from '../../../src/models';
import Base from '../../../src/models/Base';
import { createProject } from '../factory/base';
import {
  createColumn,
  createLookupColumn,
  createLtarColumn2,
  createRollupColumn,
  customColumns,
} from '../factory/column';
import { createBulkRows } from '../factory/row';
import { createTable } from '../factory/table';
import init from '../init';

export interface ITestContext {
  context: Awaited<ReturnType<typeof init>>;
  ctx: {
    workspace_id: any;
    base_id: any;
  };
  base: Base;
  tables: {
    table1: Model;
    table2: Model;
    table3: Model;
    table4: Model;
  };
}
async function ncAxiosLinkAdd({
  context,
  urlParams,
  body = {},
  status = 200,
  msg,
}: {
  context: { context: any; base: any };
  urlParams: { tableId: string; linkId: string; rowId: string };
  body?: any;
  status?: number;
  msg?: string;
}) {
  const url = `/api/v3/data/${context.base.id}/${urlParams.tableId}/links/${urlParams.linkId}/${urlParams.rowId}`;
  const response = await request(context.context.app)
    .post(url)
    .set('xc-auth', context.context.token)
    .send(body);

  expect(response.status).to.equal(status);
  if (msg) {
    expect(response.body.message || response.body.msg).to.equal(msg);
  }
  return response;
}

const getRows = (tableName: string) => {
  return Array.from({ length: 40 }).map((v, i) => {
    return {
      Title: `${tableName}_${(i + 1).toString().padStart(3, '0')}`,
    };
  });
};

const getDuplicatedRows = (tableName: string) => {
  return Array.from({ length: 40 }).map((v, i) => {
    return {
      Title: `${tableName}_${((i + 1) % 5).toString().padStart(3, '0')}`,
    };
  });
};

/**
 * Sets up the test environment with base, table, view, and BaseModelSql instance
 * Used by: tests/unit/formula/tests/formula-lookup-ltar.test.ts
 */
export async function initInitialModel() {
  const context = await init();
  const base = await createProject(context);

  const ctx = {
    workspace_id: base.fk_workspace_id,
    base_id: base.id,
  };

  const columns = [
    {
      title: 'Title',
      column_name: 'Title',
      uidt: UITypes.SingleLineText,
      pv: true,
    },
  ];
  const table1 = await createTable(context, base, {
    title: 'Table1',
    table_name: 'table1',
    columns: customColumns('custom', columns),
  });
  // insert records
  await createBulkRows(context, {
    base: base,
    table: table1,
    values: getRows('T1'),
  });
  const table2 = await createTable(context, base, {
    title: 'Table2',
    table_name: 'table2',
    columns: customColumns('custom', columns),
  });
  // insert records
  await createBulkRows(context, {
    base: base,
    table: table2,
    values: getRows('T2'),
  });
  const table3 = await createTable(context, base, {
    title: 'Table3',
    table_name: 'table3',
    columns: customColumns('custom', columns),
  });
  // insert records
  await createBulkRows(context, {
    base: base,
    table: table3,
    values: getRows('T3'),
  });
  const table4 = await createTable(context, base, {
    title: 'Table4',
    table_name: 'table4',
    columns: customColumns('custom', columns),
  });
  // insert records
  await createBulkRows(context, {
    base: base,
    table: table4,
    values: getDuplicatedRows('T4'),
  });

  // Create links
  const t3_HM_t2_Ltar = await createLtarColumn2(context, {
    title: 'T2s',
    parentTable: table3,
    childTable: table2,
    type: 'hm',
  });
  const t3_HM_t4_Ltar = await createLtarColumn2(context, {
    title: 'T4s',
    parentTable: table3,
    childTable: table4,
    type: 'hm',
  });
  const t2_HM_t1_Ltar = await createLtarColumn2(context, {
    title: 'T1s',
    parentTable: table2,
    childTable: table1,
    type: 'hm',
  });

  // self link
  const t1_HM_t1_Ltar = await createLtarColumn2(context, {
    title: 'Table1SelfList',
    parentTable: table1,
    childTable: table1,
    type: 'hm',
  });
  // oo
  const t3_OO_t1_Ltar = await createLtarColumn2(context, {
    title: 'T1_OO',
    parentTable: table3,
    childTable: table1,
    type: 'oo',
  });
  const t2_MM_t1_Ltar = await createLtarColumn2(context, {
    title: 'T1_MMs',
    parentTable: table2,
    childTable: table1,
    type: 'mm',
  });

  const source = (await Base.getByTitleOrId(ctx, base.id)).getSources();
  await createLookupColumn(context, {
    base,
    title: 'table1Name',
    table: await Model.getByIdOrName(ctx, {
      base_id: base.id,
      source_id: source.id!,
      id: table2.id,
    }),
    relatedTableName: table1.table_name,
    relatedTableColumnTitle: 'Title',
    relationColumnId: t2_HM_t1_Ltar.id,
  });
  await createLookupColumn(context, {
    base,
    title: 'table2Name',
    table: await Model.getByIdOrName(ctx, {
      base_id: base.id,
      source_id: source.id!,
      id: table3.id,
    }),
    relatedTableName: table2.table_name,
    relatedTableColumnTitle: 'Title',
    relationColumnId: t3_HM_t2_Ltar.id,
  });
  await createLookupColumn(context, {
    base,
    title: 'table2_table1s',
    table: await Model.getByIdOrName(ctx, {
      base_id: base.id,
      source_id: source.id!,
      id: table3.id,
    }),
    relatedTableName: table2.table_name,
    relatedTableColumnTitle: 'T1s',
    relationColumnId: t3_HM_t2_Ltar.id,
  });
  await createLookupColumn(context, {
    base,
    title: 'Table1SelfList_Titles',
    table: await Model.getByIdOrName(ctx, {
      base_id: base.id,
      source_id: source.id!,
      id: table1.id,
    }),
    relatedTableName: table1.table_name,
    relatedTableColumnTitle: 'Title',
    relationColumnId: t1_HM_t1_Ltar.id,
  });

  const linkTo_t2_HM_t1_Ltar = (rowId: string, body: any[]) => {
    ncAxiosLinkAdd({
      context: {
        context,
        base,
      },
      urlParams: {
        tableId: table2.id,
        linkId: t2_HM_t1_Ltar.id,
        rowId: rowId,
      },
      body: body,
      status: 200,
    });
  };
  const linkTo_t3_HM_t2_Ltar = (rowId: string, body: any[]) => {
    ncAxiosLinkAdd({
      context: {
        context,
        base,
      },
      urlParams: {
        tableId: table3.id,
        linkId: t3_HM_t2_Ltar.id,
        rowId: rowId,
      },
      body: body,
      status: 200,
    });
  };
  const linkTo_t3_HM_t4_Ltar = (rowId: string, body: any[]) => {
    ncAxiosLinkAdd({
      context: {
        context,
        base,
      },
      urlParams: {
        tableId: table3.id,
        linkId: t3_HM_t4_Ltar.id,
        rowId: rowId,
      },
      body: body,
      status: 200,
    });
  };
  const linkTo_t3_OO_t1_Ltar = (rowId: string, body: any[]) => {
    ncAxiosLinkAdd({
      context: {
        context,
        base,
      },
      urlParams: {
        tableId: table3.id,
        linkId: t3_OO_t1_Ltar.id,
        rowId: rowId,
      },
      body: body,
      status: 200,
    });
  };
  const linkTo_t2_MM_t1_Ltar = (rowId: string, body: any[]) => {
    ncAxiosLinkAdd({
      context: {
        context,
        base,
      },
      urlParams: {
        tableId: table2.id,
        linkId: t2_MM_t1_Ltar.id,
        rowId: rowId,
      },
      body: body,
      status: 200,
    });
  };

  await linkTo_t2_HM_t1_Ltar('1', [{ id: 1 }, { id: 2 }, { id: 3 }]);
  await linkTo_t2_HM_t1_Ltar('2', [{ id: 4 }, { id: 5 }, { id: 6 }]);
  await linkTo_t2_HM_t1_Ltar('3', [{ id: 7 }]);
  await linkTo_t3_HM_t2_Ltar('1', [{ id: 1 }, { id: 2 }]);
  await linkTo_t3_HM_t2_Ltar('2', [{ id: 3 }]);
  await linkTo_t3_HM_t4_Ltar(
    '1',
    Array.from({ length: 20 }).map((v, i) => ({ id: i + 1 })),
  );
  await linkTo_t3_OO_t1_Ltar('1', ['1']);
  await linkTo_t3_OO_t1_Ltar('2', ['2']);
  await linkTo_t3_OO_t1_Ltar('3', ['3']);
  await linkTo_t2_MM_t1_Ltar('1', ['1', '2']);

  return {
    context,
    ctx,
    base,
    tables: {
      table1,
      table2,
      table3,
      table4,
    },
  } as ITestContext;
}

export async function initQrBarcodeColumns(context: ITestContext) {
  const table1 = context.tables.table1;
  const table2 = context.tables.table2;
  await createColumn(context.context, table1, {
    title: 'QRCode',
    column_name: 'qrcode',
    uidt: UITypes.QrCode,
    fk_qr_value_column_id: (
      await table1.getColumns(context.ctx)
    ).find((k) => k.title === 'Title').id,
  });
  await createColumn(context.context, table1, {
    title: 'Barcode',
    column_name: 'barcode',
    uidt: UITypes.Barcode,
    fk_barcode_value_column_id: (
      await table1.getColumns(context.ctx)
    ).find((k) => k.title === 'Title').id,
  });

  const t2_HM_t1_Ltar = (await table2.getColumns(context.ctx)).find(
    (col) => col.title === 'T1s',
  );
  const source = (await context.base.getSources())[0];
  await createLookupColumn(context.context, {
    base: context.base,
    title: 'table1Qr',
    table: await Model.getByIdOrName(context.ctx, {
      base_id: context.base.id,
      source_id: source.id!,
      id: context.tables.table2.id,
    }),
    relatedTableName: table1.table_name,
    relatedTableColumnTitle: 'QRCode',
    relationColumnId: t2_HM_t1_Ltar.id,
  });
  await createLookupColumn(context.context, {
    base: context.base,
    title: 'table1Barcode',
    table: await Model.getByIdOrName(context.ctx, {
      base_id: context.base.id,
      source_id: source.id!,
      id: context.tables.table2.id,
    }),
    relatedTableName: table1.table_name,
    relatedTableColumnTitle: 'Barcode',
    relationColumnId: t2_HM_t1_Ltar.id,
  });
}

export async function initFormulaLookupColumns(context: ITestContext) {
  const formulaColumn = await createColumn(
    context.context,
    context.tables.table1,
    {
      title: 'FormulaTitle',
      uidt: UITypes.Formula,
      formula: 'CONCAT({Title}, "?")',
      formula_raw: 'CONCAT({Title}, "?")',
    },
  );
  await createColumn(context.context, context.tables.table2, {
    title: 'FormulaTitle',
    uidt: UITypes.Formula,
    formula: 'CONCAT({Title}, "?")',
    formula_raw: 'CONCAT({Title}, "?")',
  });
  const t2SupportingLookupColumns = [
    {
      title: 'T2CreatedTime',
      uidt: UITypes.CreatedTime,
    },
    {
      title: 'T2CreatedBy',
      uidt: UITypes.CreatedBy,
    },
    {
      title: 'T2LastModifiedTime',
      uidt: UITypes.LastModifiedTime,
    },
    {
      title: 'T2LastModifiedBy',
      uidt: UITypes.LastModifiedBy,
    },
  ];

  const t1SupportingLookupColumns = [
    {
      title: 'T1CreatedTime',
      uidt: UITypes.CreatedTime,
    },
    {
      title: 'T1CreatedBy',
      uidt: UITypes.CreatedBy,
    },
    {
      title: 'T1LastModifiedTime',
      uidt: UITypes.LastModifiedTime,
    },
    {
      title: 'T1LastModifiedBy',
      uidt: UITypes.LastModifiedBy,
    },
  ];
  const t3SupportingLookupColumns = [
    {
      title: 'T3CreatedTime',
      uidt: UITypes.CreatedTime,
    },
    {
      title: 'T3CreatedBy',
      uidt: UITypes.CreatedBy,
    },
    {
      title: 'T3LastModifiedTime',
      uidt: UITypes.LastModifiedTime,
    },
    {
      title: 'T3LastModifiedBy',
      uidt: UITypes.LastModifiedBy,
    },
  ];
  for (const colAttr of t1SupportingLookupColumns) {
    await createColumn(context.context, context.tables.table1, colAttr);
  }
  for (const colAttr of t3SupportingLookupColumns) {
    await createColumn(context.context, context.tables.table3, colAttr);
  }
  for (const colAttr of t2SupportingLookupColumns) {
    await createColumn(context.context, context.tables.table2, colAttr);
  }

  const t2_HM_t1_Ltar = (
    await context.tables.table2.getColumns(context.ctx)
  ).find((col) => col.title === 'T1s');
  const source = (await context.base.getSources())[0];

  await createLookupColumn(context.context, {
    base: context.base,
    title: 'table1FormulaTitle',
    table: await Model.getByIdOrName(context.ctx, {
      base_id: context.base.id,
      source_id: source.id!,
      id: context.tables.table2.id,
    }),
    relatedTableName: context.tables.table1.table_name,
    relatedTableColumnTitle: 'FormulaTitle',
    relationColumnId: t2_HM_t1_Ltar.id,
  });

  const t2FormulaColumn = await createColumn(
    context.context,
    context.tables.table2,
    {
      title: 'T2FormulaTitle',
      uidt: UITypes.Formula,
      formula: 'CONCAT({Title}, "?")',
      formula_raw: 'CONCAT({Title}, "?")',
    },
  );
  const t1_BT_t2_Ltar = (
    await context.tables.table1.getColumns(context.ctx)
  ).find((col) => col.title === 'Table2');
  const t1_OO_t3_Ltar = (
    await context.tables.table1.getColumns(context.ctx)
  ).find((col) => col.title === 'Table3');

  await createLookupColumn(context.context, {
    base: context.base,
    title: 'table2FormulaTitle',
    table: await Model.getByIdOrName(context.ctx, {
      base_id: context.base.id,
      source_id: source.id!,
      id: context.tables.table1.id,
    }),
    relatedTableName: context.tables.table2.table_name,
    relatedTableColumnTitle: 'T2FormulaTitle',
    relationColumnId: t1_BT_t2_Ltar.id,
  });

  // oo
  const t3_OO_t1_Ltar = (
    await context.tables.table3.getColumns(context.ctx)
  ).find((col) => col.title === 'T1_OO');

  await createLookupColumn(context.context, {
    base: context.base,
    title: 'table1FormulaTitle',
    table: await Model.getByIdOrName(context.ctx, {
      base_id: context.base.id,
      source_id: source.id!,
      id: context.tables.table3.id,
    }),
    relatedTableName: context.tables.table1.table_name,
    relatedTableColumnTitle: 'FormulaTitle',
    relationColumnId: t3_OO_t1_Ltar.id,
  });

  // referencing using formula field so it's populated in chunkList
  await createColumn(context.context, context.tables.table3, {
    title: 'table1FormulaTitleFormula',
    uidt: UITypes.Formula,
    formula: `{table1FormulaTitle}`,
    formula_raw: `{table1FormulaTitle}`,
  });

  for (const attr of t1SupportingLookupColumns) {
    await createLookupColumn(context.context, {
      base: context.base,
      title: `t2_hm_${attr.title}`,
      table: await Model.getByIdOrName(context.ctx, {
        base_id: context.base.id,
        source_id: source.id!,
        id: context.tables.table2.id,
      }),
      relatedTableName: context.tables.table1.table_name,
      relatedTableColumnTitle: attr.title,
      relationColumnId: t2_HM_t1_Ltar.id,
    });
  }

  for (const attr of t2SupportingLookupColumns) {
    await createLookupColumn(context.context, {
      base: context.base,
      title: `t1_bt_${attr.title}`,
      table: await Model.getByIdOrName(context.ctx, {
        base_id: context.base.id,
        source_id: source.id!,
        id: context.tables.table1.id,
      }),
      relatedTableName: context.tables.table2.table_name,
      relatedTableColumnTitle: attr.title,
      relationColumnId: t1_BT_t2_Ltar.id,
    });
  }
  for (const attr of t3SupportingLookupColumns) {
    await createLookupColumn(context.context, {
      base: context.base,
      title: `t1_oo_${attr.title}`,
      table: await Model.getByIdOrName(context.ctx, {
        base_id: context.base.id,
        source_id: source.id!,
        id: context.tables.table1.id,
      }),
      relatedTableName: context.tables.table3.table_name,
      relatedTableColumnTitle: attr.title,
      relationColumnId: t1_OO_t3_Ltar.id,
    });
  }

  const t2Fields = t1SupportingLookupColumns
    .map((col) => `{t2_hm_${col.title}}`)
    .join(',');
  const t1Fields = t2SupportingLookupColumns
    .map((col) => `{t1_bt_${col.title}}`)
    .join(',');
  // t2 formula for supporting lookup
  await createColumn(context.context, context.tables.table2, {
    title: 't2SupportingLookupFormula',
    uidt: UITypes.Formula,
    formula: `CONCAT(${t2Fields})`,
    formula_raw: `CONCAT(${t2Fields})`,
  });
  // t1 formula for supporting lookup
  await createColumn(context.context, context.tables.table1, {
    title: 't1SupportingLookupFormula',
    uidt: UITypes.Formula,
    formula: `CONCAT(${t1Fields})`,
    formula_raw: `CONCAT(${t1Fields})`,
  });
}

export async function initFormulaRollupColumns(context: ITestContext) {
  const formulaColumn = await createColumn(
    context.context,
    context.tables.table1,
    {
      title: 'FormulaTitle',
      uidt: UITypes.Formula,
      formula: 'CONCAT({Title}, "?")',
      formula_raw: 'CONCAT({Title}, "?")',
    },
  );
  const t2_HM_t1_Ltar = (
    await context.tables.table2.getColumns(context.ctx)
  ).find((col) => col.title === 'T1s');
  const source = (await context.base.getSources())[0];

  await createRollupColumn(context.context, {
    base: context.base,
    title: 'table1FormulaTitle',
    rollupFunction: 'countDistinct',
    table: await Model.getByIdOrName(context.ctx, {
      base_id: context.base.id,
      source_id: source.id!,
      id: context.tables.table2.id,
    }),
    relatedTableName: context.tables.table1.table_name,
    relatedTableColumnTitle: 'FormulaTitle',
    ltarColumnId: t2_HM_t1_Ltar.id,
  });
}
