/** مسیرهای عمومی: صفحه اصلی، اخبار، سازمان‌ها، کسب‌وکارها، آمار، نقشه، خط زمانی */
import type { FastifyInstance } from 'fastify';
import { and, desc, eq, inArray, sql, isNotNull } from 'drizzle-orm';
import { getDb } from '../../database/client.js';
import {
  articles, organizations, departments, organizationServices, businesses, shops, businessOfferings,
  guilds, datasets, datasetPoints, cases, reports, caseEvents, officialResponses, regions, taxonomies,
  communicationChannels, users, analyticsEvents, comments,
} from '../../database/schema/index.js';
import { getArticleBySlug, incrementView } from '../../modules/articles/service.js';
import { getPublicCase, organizationScorecard } from '../../modules/reports/service.js';
import { Errors } from '../../common/errors.js';

const publishedArticle = () => and(eq(articles.status, 'published'), sql`${articles.deletedAt} IS NULL`);

export default async function publicRoutes(app: FastifyInstance) {
  const db = () => getDb();

  /** بسته‌ی کامل صفحه اصلی — یک درخواست، همه بخش‌ها */
  app.get('/home', async () => {
    const d = db();
    const [spotlight, breaking, latest, publicCases, orgs, biz, stats, audio, topics] = await Promise.all([
      d.select().from(articles).where(and(publishedArticle(), eq(articles.isFeatured, true))).orderBy(desc(articles.publishedAt)).limit(6),
      d.select().from(articles).where(and(publishedArticle(), eq(articles.isBreaking, true))).orderBy(desc(articles.publishedAt)).limit(6),
      d.select().from(articles).where(publishedArticle()).orderBy(desc(articles.publishedAt)).limit(12),
      d.select({ c: cases, r: reports }).from(cases).innerJoin(reports, eq(reports.id, cases.reportId))
        .where(eq(cases.visibility, 'public')).orderBy(desc(cases.createdAt)).limit(8),
      d.select().from(organizations).where(eq(organizations.isActive, true)).limit(12),
      d.select().from(businesses).where(eq(businesses.isActive, true)).limit(12),
      d.select().from(datasets).where(eq(datasets.status, 'published')).orderBy(desc(datasets.lastUpdatedAt)).limit(6),
      d.select().from(articles).where(and(publishedArticle(), isNotNull(articles.audioUrl))).orderBy(desc(articles.publishedAt)).limit(6),
      d.select().from(taxonomies).where(and(eq(taxonomies.kind, 'topic'), eq(taxonomies.isActive, true))).limit(14),
    ]);

    const statsWithPoints = await Promise.all(
      stats.map(async (ds) => ({
        dataset: ds,
        points: await d.select().from(datasetPoints).where(eq(datasetPoints.datasetId, ds.id)).orderBy(datasetPoints.periodLabel),
      })),
    );

    const mostDiscussed = await d.select().from(articles).where(publishedArticle()).orderBy(desc(articles.discussionCount)).limit(6);
    const mostReported = await d.execute<{ category_key: string; c: number }>(sql`
      SELECT category_key, COUNT(*)::int AS c FROM reports
      WHERE status NOT IN ('rejected','draft') GROUP BY category_key ORDER BY c DESC LIMIT 8`);

    return {
      spotlight: spotlight.length ? spotlight : latest.slice(0, 5),
      breaking, latest,
      publicCases: publicCases.map(({ c, r }) => ({
        ...c,
        category: r.categoryKey, kind: r.kind, severity: r.severity,
        excerpt: r.body.slice(0, 160),
      })),
      organizations: orgs, businesses: biz, statistics: statsWithPoints, audio, topics,
      mostDiscussed,
      mostReported: (mostReported.rows ?? []).map((r) => ({ categoryKey: r.category_key, count: r.c })),
    };
  });

  /* ---------------------------------- اخبار --------------------------------- */
  app.get('/news', async (req) => {
    const q = req.query as Record<string, string | undefined>;
    const page = Math.max(1, Number(q.page ?? 1));
    const size = Math.min(48, Number(q.pageSize ?? 12));
    const conditions = [publishedArticle()];
    if (q.category) conditions.push(eq(articles.categoryKey, q.category));
    if (q.contentType) conditions.push(eq(articles.contentType, q.contentType));
    if (q.organizationId) conditions.push(eq(articles.organizationId, q.organizationId));
    const rows = await db().select().from(articles).where(and(...conditions))
      .orderBy(desc(articles.publishedAt)).limit(size).offset((page - 1) * size);
    const [{ count }] = await db().select({ count: sql<number>`count(*)::int` }).from(articles).where(and(...conditions));
    return { items: rows, page, pageSize: size, total: count };
  });

  app.get('/news/:slug', async (req) => {
    const { slug } = req.params as { slug: string };
    const result = await getArticleBySlug(slug);
    if (!result || result.article.status !== 'published') throw Errors.notFound('خبر یافت نشد.');
    await incrementView(result.article.id);
    await db().insert(analyticsEvents).values({ kind: 'article_view', entityType: 'article', entityId: result.article.id });

    const author = (await db().select({ id: users.id, name: users.displayName }).from(users).where(eq(users.id, result.article.authorId)))[0];
    const related = await db().select().from(articles)
      .where(and(publishedArticle(), eq(articles.categoryKey, result.article.categoryKey ?? '')))
      .orderBy(desc(articles.publishedAt)).limit(4);
    const linkedDatasets = (result.article.datasetIds ?? []).length
      ? await db().select().from(datasets).where(inArray(datasets.id, result.article.datasetIds as string[]))
      : [];
    const approvedComments = await db().select().from(comments)
      .where(and(eq(comments.articleId, result.article.id), eq(comments.status, 'approved')))
      .orderBy(desc(comments.createdAt));

    return {
      ...result,
      author,
      related: related.filter((r) => r.id !== result.article.id),
      datasets: linkedDatasets,
      comments: approvedComments,
      // وضعیت قفل ۱۰ روزه برای نمایش شفاف به خواننده
      lockState: {
        isLocked: result.article.isLocked,
        editableUntil: result.article.editableUntil,
        revisionCount: result.revisions.length,
        hasCorrections: result.corrections.length > 0,
      },
    };
  });

  /* -------------------------------- سازمان‌ها ------------------------------- */
  app.get('/organizations', async (req) => {
    const q = req.query as Record<string, string | undefined>;
    const conditions = [eq(organizations.isActive, true)];
    if (q.type) conditions.push(eq(organizations.typeKey, q.type));
    if (q.regionId) conditions.push(eq(organizations.regionId, q.regionId));
    if (q.verified === 'true') conditions.push(eq(organizations.verificationStatus, 'verified'));
    return { items: await db().select().from(organizations).where(and(...conditions)).limit(200) };
  });

  app.get('/organizations/:slug', async (req) => {
    const { slug } = req.params as { slug: string };
    const [org] = await db().select().from(organizations).where(eq(organizations.slug, slug));
    if (!org) throw Errors.notFound('سازمان یافت نشد.');
    const [depts, services, channels, orgArticles, orgCases, scorecard, children] = await Promise.all([
      db().select().from(departments).where(eq(departments.organizationId, org.id)),
      db().select().from(organizationServices).where(eq(organizationServices.organizationId, org.id)),
      db().select({
        id: communicationChannels.id, provider: communicationChannels.provider,
        handle: communicationChannels.handle, ownerName: communicationChannels.ownerName,
        verificationStatus: communicationChannels.verificationStatus,
      }).from(communicationChannels).where(and(eq(communicationChannels.organizationId, org.id), eq(communicationChannels.isActive, true))),
      db().select().from(articles).where(and(publishedArticle(), eq(articles.organizationId, org.id))).orderBy(desc(articles.publishedAt)).limit(8),
      db().select({ c: cases, r: reports }).from(cases).innerJoin(reports, eq(reports.id, cases.reportId))
        .where(and(eq(cases.organizationId, org.id), eq(cases.visibility, 'public'))).orderBy(desc(cases.createdAt)).limit(10),
      organizationScorecard(org.id),
      db().select().from(organizations).where(eq(organizations.parentId, org.id)),
    ]);
    const orgDatasets = await db().select().from(datasets).where(and(eq(datasets.organizationId, org.id), eq(datasets.status, 'published')));
    const responses = await db().select().from(officialResponses).where(eq(officialResponses.organizationId, org.id)).orderBy(desc(officialResponses.createdAt)).limit(6);
    return {
      organization: org, departments: depts, services, channels, children,
      articles: orgArticles,
      cases: orgCases.map(({ c, r }) => ({ ...c, category: r.categoryKey, excerpt: r.body.slice(0, 140) })),
      datasets: orgDatasets, responses, scorecard,
    };
  });

  /* -------------------------- کسب‌وکارها و اصناف ---------------------------- */
  app.get('/businesses', async (req) => {
    const q = req.query as Record<string, string | undefined>;
    const conditions = [eq(businesses.isActive, true)];
    if (q.category) conditions.push(eq(businesses.categoryKey, q.category));
    if (q.guildId) conditions.push(eq(businesses.guildId, q.guildId));
    if (q.regionId) conditions.push(eq(businesses.regionId, q.regionId));
    return { items: await db().select().from(businesses).where(and(...conditions)).limit(200) };
  });

  app.get('/businesses/:slug', async (req) => {
    const { slug } = req.params as { slug: string };
    const [biz] = await db().select().from(businesses).where(eq(businesses.slug, slug));
    if (!biz) throw Errors.notFound('کسب‌وکار یافت نشد.');
    const [branches, offerings, guild, publicCases] = await Promise.all([
      db().select().from(shops).where(eq(shops.businessId, biz.id)),
      db().select().from(businessOfferings).where(eq(businessOfferings.businessId, biz.id)),
      biz.guildId ? db().select().from(guilds).where(eq(guilds.id, biz.guildId)) : Promise.resolve([]),
      db().select({ c: cases, r: reports }).from(cases).innerJoin(reports, eq(reports.id, cases.reportId))
        .where(and(eq(cases.businessId, biz.id), eq(cases.visibility, 'public'))).limit(10),
    ]);
    const responses = await db().select().from(officialResponses).where(eq(officialResponses.businessId, biz.id)).limit(10);
    const resolved = publicCases.filter(({ c }) => c.status === 'resolved' || c.status === 'closed').length;
    return {
      business: biz, branches, offerings, guild: guild[0] ?? null,
      cases: publicCases.map(({ c, r }) => ({ ...c, category: r.categoryKey, excerpt: r.body.slice(0, 140) })),
      responses,
      // شاخص‌های اعتماد فقط از داده واقعی همین سامانه محاسبه می‌شوند
      trust: {
        verified: biz.verificationStatus === 'verified',
        locationVerified: biz.locationVerified,
        identityVerified: biz.identityVerified,
        publicCases: publicCases.length,
        resolvedCases: resolved,
        responseCount: responses.length,
        source: 'سامانه شهرنما',
      },
    };
  });

  app.get('/guilds', async () => ({ items: await db().select().from(guilds) }));

  app.get('/guilds/:slug', async (req) => {
    const { slug } = req.params as { slug: string };
    const [guild] = await db().select().from(guilds).where(eq(guilds.slug, slug));
    if (!guild) throw Errors.notFound('صنف یافت نشد.');
    const members = await db().select().from(businesses).where(eq(businesses.guildId, guild.id));
    const children = await db().select().from(guilds).where(eq(guilds.parentId, guild.id));
    return { guild, members, children };
  });

  /* ----------------------------------- آمار --------------------------------- */
  app.get('/statistics', async () => ({
    items: await db().select().from(datasets).where(eq(datasets.status, 'published')).orderBy(desc(datasets.lastUpdatedAt)),
  }));

  app.get('/statistics/:slug', async (req) => {
    const { slug } = req.params as { slug: string };
    const [ds] = await db().select().from(datasets).where(eq(datasets.slug, slug));
    if (!ds || ds.status !== 'published') throw Errors.notFound('مجموعه داده یافت نشد.');
    const points = await db().select().from(datasetPoints).where(eq(datasetPoints.datasetId, ds.id));
    const org = ds.organizationId ? (await db().select().from(organizations).where(eq(organizations.id, ds.organizationId)))[0] : null;
    return { dataset: ds, points, organization: org };
  });

  /** مقایسه آماری دو سری یا دو مجموعه‌داده */
  app.get('/statistics/:slug/compare', async (req) => {
    const { slug } = req.params as { slug: string };
    const { a, b } = req.query as { a?: string; b?: string };
    const [ds] = await db().select().from(datasets).where(eq(datasets.slug, slug));
    if (!ds) throw Errors.notFound('مجموعه داده یافت نشد.');
    if (!a || !b) throw Errors.validation('برای مقایسه باید دو سری (a و b) مشخص شود.');
    const points = await db().select().from(datasetPoints).where(eq(datasetPoints.datasetId, ds.id));
    const seriesA = points.filter((p) => p.seriesKey === a);
    const seriesB = points.filter((p) => p.seriesKey === b);
    const sum = (xs: typeof points) => xs.reduce((acc, p) => acc + p.value, 0);
    const sa = sum(seriesA); const sb = sum(seriesB);
    return {
      dataset: ds, seriesA, seriesB,
      comparison: {
        absoluteDifference: Number((sa - sb).toFixed(3)),
        percentageDifference: sb === 0 ? null : Number((((sa - sb) / Math.abs(sb)) * 100).toFixed(2)),
        trendA: trend(seriesA.map((p) => p.value)),
        trendB: trend(seriesB.map((p) => p.value)),
        source: ds.sourceTitle, publisher: ds.publisher, methodology: ds.methodology,
      },
    };
  });

  /* ------------------------------ گزارش عمومی ------------------------------- */
  app.get('/reports/:caseCode', async (req) => {
    const { caseCode } = req.params as { caseCode: string };
    return getPublicCase(caseCode, req.principal);
  });

  app.get('/reports', async (req) => {
    const q = req.query as Record<string, string | undefined>;
    const conditions = [eq(cases.visibility, 'public')];
    if (q.status) conditions.push(eq(cases.status, q.status));
    if (q.organizationId) conditions.push(eq(cases.organizationId, q.organizationId));
    const rows = await db().select({ c: cases, r: reports }).from(cases)
      .innerJoin(reports, eq(reports.id, cases.reportId))
      .where(and(...conditions)).orderBy(desc(cases.createdAt)).limit(60);
    return {
      items: rows.map(({ c, r }) => ({
        ...c, category: r.categoryKey, kind: r.kind, severity: r.severity,
        excerpt: r.body.slice(0, 180), lat: r.privacy === 'private_to_org' ? null : r.lat,
        lng: r.privacy === 'private_to_org' ? null : r.lng,
      })),
    };
  });

  /* --------------------------- نقشه و لایه‌های آن ---------------------------- */
  app.get('/map', async (req) => {
    const q = req.query as Record<string, string | undefined>;
    const layers = (q.layers ?? 'organizations,businesses,cases').split(',');
    const out: Record<string, unknown[]> = {};
    if (layers.includes('organizations')) {
      out.organizations = (await db().select().from(organizations).where(isNotNull(organizations.lat)))
        .map((o) => ({ id: o.id, name: o.nameFa, slug: o.slug, lat: o.lat, lng: o.lng, type: o.typeKey, verified: o.verificationStatus === 'verified' }));
    }
    if (layers.includes('departments')) {
      out.departments = (await db().select().from(departments).where(isNotNull(departments.lat)))
        .map((d) => ({ id: d.id, name: d.nameFa, lat: d.lat, lng: d.lng, organizationId: d.organizationId }));
    }
    if (layers.includes('businesses')) {
      out.shops = (await db().select({ s: shops, b: businesses }).from(shops).innerJoin(businesses, eq(businesses.id, shops.businessId)))
        .filter(({ s }) => s.lat !== null)
        .map(({ s, b }) => ({ id: s.id, name: s.nameFa, lat: s.lat, lng: s.lng, businessSlug: b.slug, category: b.categoryKey }));
    }
    if (layers.includes('cases')) {
      out.cases = (await db().select({ c: cases, r: reports }).from(cases).innerJoin(reports, eq(reports.id, cases.reportId))
        .where(and(eq(cases.visibility, 'public'), isNotNull(reports.lat))))
        .map(({ c, r }) => ({ id: c.id, code: c.caseCode, title: c.title, lat: r.lat, lng: r.lng, category: r.categoryKey, status: c.status, severity: r.severity }));
    }
    return out;
  });

  /* ------------------------------- خط زمانی --------------------------------- */
  app.get('/timeline', async (req) => {
    const q = req.query as Record<string, string | undefined>;
    const items: { id: string; date: string; title: string; kind: string; url?: string; summary?: string | null }[] = [];

    if (q.organizationId) {
      const orgArticles = await db().select().from(articles)
        .where(and(publishedArticle(), eq(articles.organizationId, q.organizationId))).limit(60);
      for (const a of orgArticles) items.push({ id: a.id, date: (a.publishedAt ?? a.createdAt).toISOString(), title: a.title, kind: 'article', url: `/news/${a.slug}`, summary: a.summary });
      const orgCases = await db().select().from(cases).where(and(eq(cases.organizationId, q.organizationId), eq(cases.visibility, 'public'))).limit(60);
      for (const c of orgCases) items.push({ id: c.id, date: c.createdAt.toISOString(), title: c.title, kind: 'case', url: `/reports/${c.caseCode}` });
    } else if (q.caseId) {
      const events = await db().select().from(caseEvents).where(and(eq(caseEvents.caseId, q.caseId), eq(caseEvents.visibility, 'public')));
      for (const e of events) items.push({ id: e.id, date: e.createdAt.toISOString(), title: e.note ?? e.kind, kind: e.kind });
    } else if (q.topic) {
      const rows = await db().select().from(articles)
        .where(and(publishedArticle(), sql`${articles.topicKeys} @> ${JSON.stringify([q.topic])}::jsonb`)).limit(80);
      for (const a of rows) items.push({ id: a.id, date: (a.publishedAt ?? a.createdAt).toISOString(), title: a.title, kind: a.contentType, url: `/news/${a.slug}`, summary: a.summary });
    } else {
      const rows = await db().select().from(articles).where(publishedArticle()).orderBy(desc(articles.publishedAt)).limit(60);
      for (const a of rows) items.push({ id: a.id, date: (a.publishedAt ?? a.createdAt).toISOString(), title: a.title, kind: a.contentType, url: `/news/${a.slug}`, summary: a.summary });
    }
    items.sort((x, y) => new Date(x.date).getTime() - new Date(y.date).getTime());
    return { items };
  });

  /** حالت کاوش: ساختار درختی سازمان یا شهر برای صفحه Drag-to-Look */
  app.get('/explore/organization/:slug', async (req) => {
    const { slug } = req.params as { slug: string };
    const [org] = await db().select().from(organizations).where(eq(organizations.slug, slug));
    if (!org) throw Errors.notFound('سازمان یافت نشد.');
    const depts = await db().select().from(departments).where(eq(departments.organizationId, org.id));
    const services = await db().select().from(organizationServices).where(eq(organizationServices.organizationId, org.id));
    const staff = await db().execute<{ id: string; name: string; position: string | null; department_id: string | null }>(sql`
      SELECT u.id, u.display_name AS name, m.position, m.department_id
      FROM organization_members m JOIN users u ON u.id = m.user_id
      WHERE m.organization_id = ${org.id} AND m.is_public = true`);
    const channels = await db().select().from(communicationChannels).where(eq(communicationChannels.organizationId, org.id));
    return { organization: org, departments: depts, services, officials: staff.rows ?? [], channels };
  });

  app.get('/explore/city/:slug', async (req) => {
    const { slug } = req.params as { slug: string };
    const [region] = await db().select().from(regions).where(eq(regions.slug, slug));
    if (!region) throw Errors.notFound('منطقه یافت نشد.');
    const districts = await db().select().from(regions).where(eq(regions.parentId, region.id));
    const ids = [region.id, ...districts.map((d) => d.id)];
    const [orgs, biz, openCases] = await Promise.all([
      db().select().from(organizations).where(inArray(organizations.regionId, ids)),
      db().select().from(businesses).where(inArray(businesses.regionId, ids)),
      db().select({ c: cases, r: reports }).from(cases).innerJoin(reports, eq(reports.id, cases.reportId))
        .where(and(eq(cases.visibility, 'public'), inArray(reports.regionId, ids))).limit(40),
    ]);
    return {
      region, districts, organizations: orgs, businesses: biz,
      cases: openCases.map(({ c, r }) => ({ ...c, category: r.categoryKey, lat: r.lat, lng: r.lng })),
    };
  });

  /* --------------------------- داده‌های مرجع (پایه) -------------------------- */
  app.get('/taxonomies', async (req) => {
    const { kind } = req.query as { kind?: string };
    const rows = kind
      ? await db().select().from(taxonomies).where(and(eq(taxonomies.kind, kind), eq(taxonomies.isActive, true)))
      : await db().select().from(taxonomies).where(eq(taxonomies.isActive, true));
    return { items: rows };
  });

  app.get('/regions', async () => ({ items: await db().select().from(regions) }));
}

/** روند ساده‌ی یک سری عددی (برای مقایسه آماری) */
function trend(values: number[]): 'up' | 'down' | 'flat' | 'unknown' {
  if (values.length < 2) return 'unknown';
  const diff = values[values.length - 1] - values[0];
  const scale = Math.abs(values[0]) || 1;
  if (Math.abs(diff) / scale < 0.02) return 'flat';
  return diff > 0 ? 'up' : 'down';
}