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

function stringArrayParam(ctx: RequestContext, name: string, max = 200) {
  const repeated = ctx.url.searchParams.getAll(name);
  const csv = ctx.url.searchParams.get(name);
  const values = [...repeated, ...(csv ? csv.split(',') : [])]
    .map(value => value.trim())
    .filter(Boolean);
  return [...new Set(values)].slice(0, max);
}

function dateOnly(value: unknown) {
  if (!value) return null;
  if (value instanceof Date && !Number.isNaN(value.getTime())) return value.toISOString().slice(0, 10);
  if (typeof value === 'string' && value.trim()) return value.trim().slice(0, 10);
  return null;
}

function daysUntil(dateValue: unknown, today: Date) {
  const dateText = dateOnly(dateValue);
  if (!dateText) return null;
  const target = new Date(`${dateText}T00:00:00.000Z`);
  if (Number.isNaN(target.getTime())) return null;
  const current = new Date(Date.UTC(today.getUTCFullYear(), today.getUTCMonth(), today.getUTCDate()));
  return Math.ceil((target.getTime() - current.getTime()) / 86_400_000);
}

export async function regionsRoute(ctx: RequestContext) {
  const tenantId = await tenantIdFrom(ctx);

  const items = await query(
    `
    select id, legacy_id as "legacyId", name, code, short_name as "shortName",
           full_name as "fullName", icon, pinyin, is_hot as "isHot",
           is_active as "isActive", sort_order as "order"
    from public.regions
    where tenant_id = $1 and is_active = true
    order by sort_order asc, created_at asc
    `,
    [tenantId],
  );

  return { items };
}

export async function regionModulesRoute(ctx: RequestContext) {
  const tenantId = await tenantIdFrom(ctx);
  const regionId = ctx.url.searchParams.get('regionId');

  const params: unknown[] = [tenantId];
  const filters = ['tenant_id = $1', 'is_active = true'];
  if (regionId) {
    params.push(regionId);
    filters.push(`region_id = $${params.length}`);
  }

  const items = await query(
    `
    select id, legacy_id as "legacyId", region_id as "regionId", name, type,
           icon, color, text_color as "textColor", description, route,
           is_primary_school_module as "isPrimarySchoolModule",
           is_active as "isActive", sort_order as "order"
    from public.region_modules
    where ${filters.join(' and ')}
    order by sort_order asc, created_at asc
    `,
    params,
  );

  return { items };
}

export async function moduleNodesRoute(ctx: RequestContext) {
  const tenantId = await tenantIdFrom(ctx);
  const regionId = ctx.url.searchParams.get('regionId');
  const moduleId = ctx.url.searchParams.get('moduleId');
  const parentId = ctx.url.searchParams.get('parentId');

  const params: unknown[] = [tenantId];
  const filters = ['tenant_id = $1', 'is_active = true'];
  if (regionId) {
    params.push(regionId);
    filters.push(`region_id = $${params.length}`);
  }
  if (moduleId) {
    params.push(moduleId);
    filters.push(`module_id = $${params.length}`);
  }
  if (parentId) {
    params.push(parentId === 'root' ? null : parentId);
    filters.push(parentId === 'root' ? 'parent_id is null' : `parent_id = $${params.length}`);
  }

  const items = await query(
    `
    select id, legacy_id as "legacyId", module_id as "moduleId",
           parent_id as "parentId", legacy_parent_id as "legacyParentId",
           region_id as "regionId", type, name, path, sort_order as "order",
           is_active as "isActive", metadata
    from public.module_nodes
    where ${filters.join(' and ')}
    order by sort_order asc, created_at asc
    `,
    params,
  );

  return { items };
}

export async function schoolsRoute(ctx: RequestContext) {
  const tenantId = await tenantIdFrom(ctx);
  const regionId = ctx.url.searchParams.get('regionId');
  const params: unknown[] = [tenantId];
  const filters = ['tenant_id = $1'];
  if (regionId) {
    params.push(regionId);
    filters.push(`region_id = $${params.length}`);
  }

  const items = await query(
    `
    select id, legacy_id as "legacyId", region_id as "regionId",
           module_id as "moduleId", name,
           professional_exam_date as "professionalExamDate",
           metadata, created_at as "createdAt", updated_at as "updatedAt"
    from public.schools
    where ${filters.join(' and ')}
    order by name asc, created_at asc
    `,
    params,
  );

  return { items };
}

export async function majorsRoute(ctx: RequestContext) {
  const tenantId = await tenantIdFrom(ctx);
  const regionId = ctx.url.searchParams.get('regionId');
  const schoolId = ctx.url.searchParams.get('schoolId');
  const params: unknown[] = [tenantId];
  const filters = ['tenant_id = $1', 'is_active = true'];
  if (regionId) {
    params.push(regionId);
    filters.push(`region_id = $${params.length}`);
  }
  if (schoolId) {
    params.push(schoolId);
    filters.push(`school_id = $${params.length}`);
  }

  const items = await query(
    `
    select id, legacy_id as "legacyId", region_id as "regionId",
           school_id as "schoolId", name, description, study_tips as "studyTips",
           sort_order as "order", is_active as "isActive",
           created_at as "createdAt", updated_at as "updatedAt"
    from public.majors
    where ${filters.join(' and ')}
    order by sort_order asc, name asc
    `,
    params,
  );

  return { items };
}

export async function subjectsRoute(ctx: RequestContext) {
  const tenantId = await tenantIdFrom(ctx);
  const regionId = ctx.url.searchParams.get('regionId');
  const schoolId = ctx.url.searchParams.get('schoolId');
  const majorId = ctx.url.searchParams.get('majorId');
  const moduleId = ctx.url.searchParams.get('moduleId');
  const type = ctx.url.searchParams.get('type');
  const params: unknown[] = [tenantId];
  const filters = ['tenant_id = $1', 'is_active = true'];
  if (regionId) {
    params.push(regionId);
    filters.push(`region_id = $${params.length}`);
  }
  if (schoolId) {
    params.push(schoolId);
    filters.push(`school_id = $${params.length}`);
  }
  if (majorId) {
    params.push(majorId);
    filters.push(`major_id = $${params.length}`);
  }
  if (moduleId) {
    params.push(moduleId);
    filters.push(`module_id = $${params.length}`);
  }
  if (type) {
    params.push(type);
    filters.push(`type = $${params.length}`);
  }

  const items = await query(
    `
    select id, legacy_id as "legacyId", region_id as "regionId",
           module_id as "moduleId", school_id as "schoolId",
           major_id as "majorId", node_id as "nodeId", name, type,
           major_legacy_ids as "majorLegacyIds", icon, description, stats,
           sort_order as "order", is_active as "isActive",
           created_at as "createdAt", updated_at as "updatedAt"
    from public.subjects
    where ${filters.join(' and ')}
    order by sort_order asc, created_at asc
    `,
    params,
  );

  return { items };
}

export async function categoriesRoute(ctx: RequestContext) {
  const tenantId = await tenantIdFrom(ctx);
  const subjectId = ctx.url.searchParams.get('subjectId');
  const nodeId = ctx.url.searchParams.get('nodeId');
  const params: unknown[] = [tenantId];
  const filters = ['tenant_id = $1', 'is_active = true'];
  if (subjectId) {
    params.push(subjectId);
    filters.push(`subject_id = $${params.length}`);
  }
  if (nodeId) {
    params.push(nodeId);
    filters.push(`node_id = $${params.length}`);
  }

  const items = await query(
    `
    select id, legacy_id as "legacyId", subject_id as "subjectId",
           node_id as "nodeId", name, category_type as "categoryType",
           sort_order as "order", svip_question_limit as "svipQuestionLimit",
           is_active as "isActive", created_at as "createdAt",
           updated_at as "updatedAt"
    from public.categories
    where ${filters.join(' and ')}
    order by sort_order asc, created_at asc
    `,
    params,
  );

  return { items };
}

export async function questionsRoute(ctx: RequestContext) {
  const tenantId = await tenantIdFrom(ctx);
  const subjectId = ctx.url.searchParams.get('subjectId');
  const categoryId = ctx.url.searchParams.get('categoryId');
  const nodeId = ctx.url.searchParams.get('nodeId');
  const entryId = ctx.url.searchParams.get('entryId');
  const contentNodeId = ctx.url.searchParams.get('contentNodeId');
  const collectionId = ctx.url.searchParams.get('collectionId');
  const questionIds = stringArrayParam(ctx, 'questionIds', 300);
  const limit = intParam(ctx, 'limit', 200, 500);

  const params: unknown[] = [tenantId];
  const filters = ['q.tenant_id = $1', `q.status = 'published'`];
  if (questionIds.length) {
    params.push(questionIds);
    filters.push(`q.id = any($${params.length}::uuid[])`);
  }
  if (subjectId) {
    params.push(subjectId);
    filters.push(`q.subject_id = $${params.length}`);
  }
  if (categoryId) {
    params.push(categoryId);
    filters.push(`q.category_id = $${params.length}`);
  }
  if (nodeId) {
    params.push(nodeId);
    filters.push(`q.node_id = $${params.length}`);
  }
  if (entryId) {
    params.push(entryId);
    filters.push(`q.entry_id = $${params.length}`);
  }
  if (contentNodeId) {
    params.push(contentNodeId);
    filters.push(`q.content_node_id = $${params.length}`);
  }
  if (collectionId) {
    params.push(collectionId);
    filters.push(`(
      q.primary_collection_id = $${params.length}
      or exists (
        select 1
        from public.question_collection_items qci
        where qci.tenant_id = q.tenant_id
          and qci.question_id = q.id
          and qci.collection_id = $${params.length}
      )
    )`);
  }
  params.push(limit);

  const items = await query(
    `
    select q.id, q.legacy_id as "legacyId", q.entry_id as "entryId",
           q.content_node_id as "contentNodeId",
           q.primary_collection_id as "primaryCollectionId",
           q.subject_id as "subjectId", q.category_id as "categoryId", q.node_id as "nodeId",
           q.type, q.type_label as "typeLabel", q.difficulty, q.tags,
           q.exam_markers as "examMarkers",
           q.media_url as "mediaUrl", q.has_video_explanation as "hasVideoExplanation",
           v.id as "versionId", v.content, v.options,
           v.correct_option_index as "correctOptionIndex",
           v.correct_option_indices as "correctOptionIndices",
           v.answer_text as "answerText", v.explanation, v.sub_questions as "subQuestions",
           v.code_lang as "codeLang", v.code_template as "codeTemplate",
           q.created_at as "createdAt", q.updated_at as "updatedAt"
    from public.questions q
    left join public.question_versions v on v.id = q.current_version_id
    where ${filters.join(' and ')}
    order by q.created_at desc
    limit $${params.length}
    `,
    params,
  );

  return { items };
}

export async function vocabularyUnitsRoute(ctx: RequestContext) {
  const tenantId = await tenantIdFrom(ctx);
  const regionId = ctx.url.searchParams.get('regionId');
  const params: unknown[] = [tenantId];
  const filters = ['tenant_id = $1', 'is_active = true'];
  if (regionId) {
    params.push(regionId);
    filters.push(`region_id = $${params.length}`);
  }

  const items = await query(
    `
    select id, legacy_id as "legacyId", region_id as "regionId",
           entry_id as "entryId", content_node_id as "contentNodeId",
           name, description, word_count as "wordCount",
           sort_order as "order", is_active as "isActive",
           created_at as "createdAt", updated_at as "updatedAt"
    from public.vocabulary_units
    where ${filters.join(' and ')}
    order by sort_order asc, created_at asc
    `,
    params,
  );

  return { items };
}

export async function vocabularyWordsRoute(ctx: RequestContext) {
  const tenantId = await tenantIdFrom(ctx);
  const unitId = ctx.url.searchParams.get('unitId');
  const limit = intParam(ctx, 'limit', 1000, 2000);
  const params: unknown[] = [tenantId];
  const filters = ['tenant_id = $1', 'is_active = true'];
  if (unitId) {
    params.push(unitId);
    filters.push(`unit_id = $${params.length}`);
  }
  params.push(limit);

  const items = await query(
    `
    select id, legacy_id as "legacyId", unit_id as "unitId",
           entry_id as "entryId", content_node_id as "contentNodeId",
           word, phonetic, meaning, example,
           example_translation as "exampleTranslation",
           difficulty, tags, sort_order as "order",
           is_active as "isActive",
           created_at as "createdAt", updated_at as "updatedAt"
    from public.vocabulary_words
    where ${filters.join(' and ')}
    order by sort_order asc, word asc
    limit $${params.length}
    `,
    params,
  );

  return { items };
}

export async function handbookSubjectsRoute(ctx: RequestContext) {
  const tenantId = await tenantIdFrom(ctx);
  const regionId = ctx.url.searchParams.get('regionId');
  const params: unknown[] = [tenantId];
  const filters = ['tenant_id = $1', 'is_active = true'];
  if (regionId) {
    params.push(regionId);
    filters.push(`region_id = $${params.length}`);
  }

  const items = await query(
    `
    select id, legacy_id as "legacyId", region_id as "regionId",
           entry_id as "entryId", content_node_id as "contentNodeId",
           name, type, icon, color, description, sort_order as "order",
           is_active as "isActive", metadata,
           created_at as "createdAt", updated_at as "updatedAt"
    from public.handbook_subjects
    where ${filters.join(' and ')}
    order by sort_order asc, created_at asc
    `,
    params,
  );

  return { items };
}

export async function handbookChaptersRoute(ctx: RequestContext) {
  const tenantId = await tenantIdFrom(ctx);
  const subjectId = ctx.url.searchParams.get('subjectId');
  const params: unknown[] = [tenantId];
  const filters = ['tenant_id = $1', 'is_active = true'];
  if (subjectId) {
    params.push(subjectId);
    filters.push(`subject_id = $${params.length}`);
  }

  const items = await query(
    `
    select id, legacy_id as "legacyId", subject_id as "subjectId",
           entry_id as "entryId", content_node_id as "contentNodeId",
           name, description, sort_order as "order",
           is_active as "isActive",
           created_at as "createdAt", updated_at as "updatedAt"
    from public.handbook_chapters
    where ${filters.join(' and ')}
    order by sort_order asc, created_at asc
    `,
    params,
  );

  return { items };
}

export async function handbookEntriesRoute(ctx: RequestContext) {
  const tenantId = await tenantIdFrom(ctx);
  const chapterId = ctx.url.searchParams.get('chapterId');
  const includeContent = ctx.url.searchParams.get('includeContent') === 'true';
  const params: unknown[] = [tenantId];
  const filters = ['tenant_id = $1', 'is_active = true'];
  if (chapterId) {
    params.push(chapterId);
    filters.push(`chapter_id = $${params.length}`);
  }

  const items = await query(
    `
    select id, legacy_id as "legacyId", chapter_id as "chapterId",
           entry_id as "entryId", content_node_id as "contentNodeId",
           title, summary, ${includeContent ? 'content' : 'null::text as content'},
           tags, sort_order as "order", is_active as "isActive",
           created_at as "createdAt", updated_at as "updatedAt"
    from public.handbook_entries
    where ${filters.join(' and ')}
    order by sort_order asc, created_at asc
    `,
    params,
  );

  return { items };
}

export async function bannersRoute(ctx: RequestContext) {
  const tenantId = await tenantIdFrom(ctx);
  const regionId = ctx.url.searchParams.get('regionId');
  const params: unknown[] = [tenantId];
  const filters = ['tenant_id = $1', 'is_active = true'];
  if (regionId) {
    params.push(regionId);
    filters.push(`region_id = $${params.length}`);
  }

  const items = await query(
    `
    select id, legacy_id as "legacyId", region_id as "regionId",
           title, subtitle, content, button_text as "buttonText",
           button_link as "buttonLink", bg_color as "bgColor",
           border_color as "borderColor", sort_order as "order",
           is_active as "isActive", created_at as "createdAt",
           updated_at as "updatedAt"
    from public.banners
    where ${filters.join(' and ')}
    order by sort_order asc, created_at asc
    `,
    params,
  );

  return { items };
}

export async function faqsRoute(ctx: RequestContext) {
  const tenantId = await tenantIdFrom(ctx);
  const regionId = ctx.url.searchParams.get('regionId');
  const params: unknown[] = [tenantId];
  const filters = ['tenant_id = $1', 'is_active = true'];
  if (regionId) {
    params.push(regionId);
    filters.push(`region_id = $${params.length}`);
  }

  const items = await query(
    `
    select id, legacy_id as "legacyId", region_id as "regionId",
           question, answer, sort_order as "order", is_active as "isActive",
           created_at as "createdAt", updated_at as "updatedAt"
    from public.faqs
    where ${filters.join(' and ')}
    order by sort_order asc, created_at asc
    `,
    params,
  );

  return { items };
}

export async function announcementsRoute(ctx: RequestContext) {
  const tenantId = await tenantIdFrom(ctx);
  const items = await query(
    `
    select id, legacy_id as "legacyId", content, link,
           bg_color as "bgColor", sort_order as "order",
           is_active as "isActive", created_at as "createdAt",
           updated_at as "updatedAt"
    from public.announcements
    where tenant_id = $1 and is_active = true
    order by sort_order asc, created_at asc
    `,
    [tenantId],
  );

  return { items };
}

export async function productsRoute(ctx: RequestContext) {
  const tenantId = await tenantIdFrom(ctx);
  const regionId = ctx.url.searchParams.get('regionId');
  const params: unknown[] = [tenantId];
  const filters = ['tenant_id = $1', `status = 'active'`];
  if (regionId) {
    params.push(regionId);
    filters.push(`region_id = $${params.length}`);
  }

  const items = await query(
    `
    select id, legacy_id as "legacyId", region_id as "regionId",
           title, price_label as "price", link, type, tags, cover,
           preview_iframe as "previewIframe", detail_images as "detailImages",
           sort_order as "order", status, created_at as "createdAt",
           updated_at as "updatedAt"
    from public.products
    where ${filters.join(' and ')}
    order by sort_order asc, created_at desc
    `,
    params,
  );

  return { items };
}

export async function timelinesRoute(ctx: RequestContext) {
  const tenantId = await tenantIdFrom(ctx);
  const regionId = ctx.url.searchParams.get('regionId');
  const schoolId = ctx.url.searchParams.get('schoolId');
  const params: unknown[] = [tenantId];
  const filters = ['tenant_id = $1', 'is_active = true'];
  if (regionId) {
    params.push(regionId);
    filters.push(`region_id = $${params.length}`);
  }
  if (schoolId) {
    params.push(schoolId);
    filters.push(`school_id = $${params.length}`);
  }

  const items = await query(
    `
    select id, legacy_id as "legacyId", region_id as "regionId",
           school_id as "schoolId", type, title, description,
           event_date as "eventDate", link, sort_order as "order",
           is_active as "isActive", created_at as "createdAt",
           updated_at as "updatedAt"
    from public.timelines
    where ${filters.join(' and ')}
    order by event_date asc nulls last, sort_order asc
    `,
    params,
  );

  return { items };
}

export async function examDatesRoute(ctx: RequestContext) {
  const tenantId = await tenantIdFrom(ctx);
  const regionId = ctx.url.searchParams.get('regionId');
  const schoolId = ctx.url.searchParams.get('schoolId');
  const limit = intParam(ctx, 'limit', 50, 200);
  const params: unknown[] = [tenantId];
  const filters = ['tenant_id = $1', 'is_active = true'];
  if (regionId) {
    params.push(regionId);
    filters.push(`(region_id = $${params.length}::uuid or region_id is null)`);
  }
  if (schoolId) {
    params.push(schoolId);
    filters.push(`(school_id = $${params.length}::uuid or school_id is null)`);
  }
  params.push(limit);

  const today = new Date();
  const items = await query<{
    examDate: string | null;
  } & Record<string, unknown>>(
    `
    select id, legacy_id as "legacyId", region_id as "regionId", school_id as "schoolId",
           exam_name as "examName", exam_date::text as "examDate", exam_type as "examType",
           description, metadata, sort_order as "order", is_active as "isActive",
           created_at as "createdAt", updated_at as "updatedAt"
    from public.exam_dates
    where ${filters.join(' and ')}
    order by exam_date asc nulls last, sort_order asc
    limit $${params.length}
    `,
    params,
  );

  return {
    items: items.map(item => {
      return {
        ...item,
        daysLeft: daysUntil(item.examDate, today),
      };
    }),
  };
}

export async function svipPlansRoute(ctx: RequestContext) {
  const tenantId = await tenantIdFrom(ctx);
  const regionId = ctx.url.searchParams.get('regionId');
  const params: unknown[] = [tenantId];
  const filters = ['tenant_id = $1', 'is_active = true'];
  if (regionId) {
    params.push(regionId);
    filters.push(`(region_id = $${params.length} or region_id is null)`);
  }

  const items = await query(
    `
    select id, legacy_id as "legacyId", region_id as "regionId",
           name, price_cents as "priceCents",
           (price_cents::numeric / 100.0) as price,
           original_price_cents as "originalPriceCents",
           case when original_price_cents is null then null else (original_price_cents::numeric / 100.0) end as "originalPrice",
           days, description as "desc", per_day_label as "perDay",
           badge, recommended, coupon_only as "couponOnly",
           vp_product_id as "vpProductId", vp_enabled as "vpEnabled",
           sort_order as "order", is_active as "isActive",
           created_at as "createdAt", updated_at as "updatedAt"
    from public.svip_plans
    where ${filters.join(' and ')}
    order by sort_order asc, price_cents asc
    `,
    params,
  );

  return { items };
}
