import type { RequestContext } from '../../core/http.js';
import { HttpError } from '../../core/http.js';
import { intParam, stringParam, tenantIdFrom } from '../../core/request.js';
import { query, queryOne } from '../../core/db.js';

const SCORELINE_FIELD_QUERY_PREFIX = 'field.';
const SCORELINE_FIELD_MIN_QUERY_PREFIX = 'min.';
const SCORELINE_FIELD_MAX_QUERY_PREFIX = 'max.';
const SCORELINE_FIELD_KEY_PATTERN = /^[A-Za-z][A-Za-z0-9_]{0,63}$/;

interface ScorelineFilterField {
  fieldKey: string;
  fieldType?: string | null;
}

interface ScorelineDynamicFilter {
  operator: 'eq' | 'min' | 'max';
  fieldKey: string;
  value: string;
}

function normalizeFieldType(value?: string | null) {
  return (value || 'text').trim().toLowerCase();
}

function quoteJsonPathKey(key: string) {
  return `'${key.replace(/'/g, "''")}'`;
}

function collectDynamicFilters(ctx: RequestContext) {
  const filters: ScorelineDynamicFilter[] = [];
  for (const [rawKey, rawValue] of ctx.url.searchParams.entries()) {
    const value = rawValue.trim();
    if (!value) continue;

    if (rawKey.startsWith(SCORELINE_FIELD_QUERY_PREFIX)) {
      filters.push({ operator: 'eq', fieldKey: rawKey.slice(SCORELINE_FIELD_QUERY_PREFIX.length), value });
    } else if (rawKey.startsWith(SCORELINE_FIELD_MIN_QUERY_PREFIX)) {
      filters.push({ operator: 'min', fieldKey: rawKey.slice(SCORELINE_FIELD_MIN_QUERY_PREFIX.length), value });
    } else if (rawKey.startsWith(SCORELINE_FIELD_MAX_QUERY_PREFIX)) {
      filters.push({ operator: 'max', fieldKey: rawKey.slice(SCORELINE_FIELD_MAX_QUERY_PREFIX.length), value });
    }
  }
  return filters;
}

async function loadFilterableScorelineFields(tenantId: string, regionId: string) {
  return query<ScorelineFilterField>(
    `
    select "fieldKey", "fieldType"
    from (
      select distinct on (field_key)
             field_key as "fieldKey", field_type as "fieldType",
             sort_order as "sortOrder"
      from public.scoreline_fields
      where tenant_id = $1
        and ($2::uuid is null or region_id = $2::uuid or region_id is null)
        and is_filter = true
      order by field_key asc,
               case
                 when $2::uuid is not null and region_id = $2::uuid then 0
                 when region_id is null then 1
                 else 2
               end asc,
               sort_order asc
    ) fields
    order by "sortOrder" asc, "fieldKey" asc
    `,
    [tenantId, regionId || null],
  );
}

async function buildScorelineDynamicWhere(
  ctx: RequestContext,
  tenantId: string,
  regionId: string,
  params: unknown[],
) {
  const filters = collectDynamicFilters(ctx);
  if (!filters.length) return '';

  const filterableFields = await loadFilterableScorelineFields(tenantId, regionId);
  const fieldMap = new Map(filterableFields.map(item => [item.fieldKey, item]));
  const clauses: string[] = [];

  for (const filter of filters) {
    if (!SCORELINE_FIELD_KEY_PATTERN.test(filter.fieldKey)) {
      throw new HttpError(400, 'Scoreline field filter key is invalid', 'SCORELINE_FIELD_FILTER_KEY_INVALID');
    }
    const field = fieldMap.get(filter.fieldKey);
    if (!field) {
      throw new HttpError(400, 'Scoreline field filter is not enabled', 'SCORELINE_FIELD_FILTER_NOT_ALLOWED');
    }

    const type = normalizeFieldType(field.fieldType);
    const jsonKey = quoteJsonPathKey(filter.fieldKey);
    params.push(filter.value);
    const paramIndex = params.length;

    if (filter.operator === 'min' || filter.operator === 'max') {
      if (!['number', 'integer', 'decimal', 'float'].includes(type)) {
        throw new HttpError(400, 'Scoreline range filter requires a numeric field', 'SCORELINE_FIELD_RANGE_TYPE_INVALID');
      }
      const numericValue = Number(filter.value);
      if (!Number.isFinite(numericValue)) {
        throw new HttpError(400, 'Scoreline range filter value must be numeric', 'SCORELINE_FIELD_RANGE_VALUE_INVALID');
      }
      clauses.push(`
        case
          when jsonb_typeof(field_values -> ${jsonKey}) = 'number' then (field_values ->> ${jsonKey})::numeric
          when field_values ->> ${jsonKey} ~ '^-?[0-9]+(\\.[0-9]+)?$' then (field_values ->> ${jsonKey})::numeric
          else null
        end ${filter.operator === 'min' ? '>=' : '<='} $${paramIndex}::numeric
      `);
      continue;
    }

    if (['number', 'integer', 'decimal', 'float'].includes(type)) {
      const numericValue = Number(filter.value);
      if (!Number.isFinite(numericValue)) {
        throw new HttpError(400, 'Scoreline numeric filter value must be numeric', 'SCORELINE_FIELD_VALUE_INVALID');
      }
      clauses.push(`
        case
          when jsonb_typeof(field_values -> ${jsonKey}) = 'number' then (field_values ->> ${jsonKey})::numeric
          when field_values ->> ${jsonKey} ~ '^-?[0-9]+(\\.[0-9]+)?$' then (field_values ->> ${jsonKey})::numeric
          else null
        end = $${paramIndex}::numeric
      `);
    } else if (['text', 'textarea', 'string'].includes(type)) {
      clauses.push(`field_values ->> ${jsonKey} ilike '%' || $${paramIndex}::text || '%'`);
    } else if (['boolean', 'bool'].includes(type)) {
      clauses.push(`lower(field_values ->> ${jsonKey}) = lower($${paramIndex}::text)`);
    } else {
      clauses.push(`field_values ->> ${jsonKey} = $${paramIndex}::text`);
    }
  }

  return clauses.length ? ` and ${clauses.join(' and ')}` : '';
}

export async function scorelineFieldsRoute(ctx: RequestContext) {
  const tenantId = await tenantIdFrom(ctx);
  const regionId = stringParam(ctx, 'regionId');

  const items = await query(
    `
    select *
    from (
      select distinct on (field_key)
             id, legacy_id as "legacyId", region_id as "regionId",
             field_key as "fieldKey", field_name as "fieldName",
             field_type as "fieldType", unit, is_filter as "isFilter",
             is_required as "isRequired", is_visible as "isVisible",
             is_trend as "isTrend", options, placeholder, description,
             sort_order as "sortOrder", created_at as "createdAt",
             updated_at as "updatedAt"
      from public.scoreline_fields
      where tenant_id = $1 and ($2::uuid is null or region_id = $2::uuid or region_id is null)
      order by field_key asc,
               case
                 when $2::uuid is not null and region_id = $2::uuid then 0
                 when region_id is null then 1
                 else 2
               end asc,
               sort_order asc,
               field_name asc
    ) fields
    order by "sortOrder" asc, "fieldName" asc
    `,
    [tenantId, regionId || null],
  );

  return { items };
}

export async function scorelineSchoolsRoute(ctx: RequestContext) {
  const tenantId = await tenantIdFrom(ctx);
  const regionId = stringParam(ctx, 'regionId');
  const q = stringParam(ctx, 'q');
  const limit = intParam(ctx, 'limit', 200, 1000);

  const items = await query(
    `
    select id, legacy_id as "legacyId", region_id as "regionId",
           name, short_name as "shortName", type, is_hot as "isHot",
           sort_order as "order", created_at as "createdAt",
           updated_at as "updatedAt"
    from public.scoreline_schools
    where tenant_id = $1
      and ($2::uuid is null or region_id = $2::uuid)
      and ($3::text = '' or name ilike '%' || $3 || '%' or short_name ilike '%' || $3 || '%')
    order by is_hot desc, sort_order asc, name asc, type asc nulls last
    limit $4
    `,
    [tenantId, regionId || null, q, limit],
  );

  return { items };
}

export async function scorelineMajorsRoute(ctx: RequestContext) {
  const tenantId = await tenantIdFrom(ctx);
  const regionId = stringParam(ctx, 'regionId');
  const schoolId = stringParam(ctx, 'schoolId');
  const limit = intParam(ctx, 'limit', 500, 2000);

  const items = await query(
    `
    select id, legacy_id as "legacyId", region_id as "regionId",
           school_id as "schoolId", name, sort_order as "order",
           has_restriction as "hasRestriction", restriction_desc as "restrictionDesc",
           created_at as "createdAt", updated_at as "updatedAt"
    from public.scoreline_majors
    where tenant_id = $1
      and ($2::uuid is null or region_id = $2::uuid)
      and ($3::uuid is null or school_id = $3::uuid)
    order by sort_order asc, name asc
    limit $4
    `,
    [tenantId, regionId || null, schoolId || null, limit],
  );

  return { items };
}

export async function scorelineRecordsRoute(ctx: RequestContext) {
  const tenantId = await tenantIdFrom(ctx);
  const regionId = stringParam(ctx, 'regionId');
  const schoolId = stringParam(ctx, 'schoolId');
  const majorId = stringParam(ctx, 'majorId');
  const year = Number(ctx.url.searchParams.get('year') || 0);
  const page = intParam(ctx, 'page', 1, 10_000);
  const pageSize = intParam(ctx, 'pageSize', intParam(ctx, 'perPage', 20, 100), 100);
  const offset = (page - 1) * pageSize;

  const params = [tenantId, regionId || null, schoolId || null, majorId || null, Number.isFinite(year) ? Math.trunc(year) : 0];
  const dynamicWhere = await buildScorelineDynamicWhere(ctx, tenantId, regionId, params);
  const where = `
    tenant_id = $1
    and ($2::uuid is null or region_id = $2::uuid)
    and ($3::uuid is null or school_id = $3::uuid)
    and ($4::uuid is null or major_id = $4::uuid)
    and ($5::integer = 0 or year = $5::integer)
    ${dynamicWhere}
  `;
  const limitParamIndex = params.length + 1;
  const offsetParamIndex = params.length + 2;

  const [countRow, items] = await Promise.all([
    queryOne<{ total: string }>(`select count(*)::text as total from public.scoreline_records where ${where}`, params),
    query(
      `
      select id, legacy_id as "legacyId", region_id as "regionId",
             school_id as "schoolId", major_id as "majorId", year,
             school_name as "schoolName", major_name as "majorName",
             field_values as "fieldValues", created_at as "createdAt",
             updated_at as "updatedAt"
      from public.scoreline_records
      where ${where}
      order by year desc, school_name asc nulls last, major_name asc nulls last
      limit $${limitParamIndex} offset $${offsetParamIndex}
      `,
      [...params, pageSize, offset],
    ),
  ]);

  return {
    page,
    pageSize,
    total: Number(countRow?.total || 0),
    items,
  };
}

export async function scorelineTrendRoute(ctx: RequestContext) {
  const tenantId = await tenantIdFrom(ctx);
  const regionId = stringParam(ctx, 'regionId');
  const schoolId = stringParam(ctx, 'schoolId');
  const majorId = stringParam(ctx, 'majorId');
  const limit = intParam(ctx, 'limit', 20, 100);

  const items = await query(
    `
    select id, region_id as "regionId", school_id as "schoolId",
           major_id as "majorId", year, school_name as "schoolName",
           major_name as "majorName", field_values as "fieldValues"
    from public.scoreline_records
    where tenant_id = $1
      and ($2::uuid is null or region_id = $2::uuid)
      and ($3::uuid is null or school_id = $3::uuid)
      and ($4::uuid is null or major_id = $4::uuid)
    order by year asc, major_name asc nulls last
    limit $5
    `,
    [tenantId, regionId || null, schoolId || null, majorId || null, limit],
  );

  return { items };
}

export async function scorelineYearsRoute(ctx: RequestContext) {
  const tenantId = await tenantIdFrom(ctx);
  const regionId = stringParam(ctx, 'regionId');

  const items = await query<{ year: number }>(
    `
    select distinct year
    from public.scoreline_records
    where tenant_id = $1 and ($2::uuid is null or region_id = $2::uuid)
    order by year desc
    `,
    [tenantId, regionId || null],
  );

  return { items: items.map(item => item.year) };
}
