/**
 * اسکیمای پایگاه داده «شهرنما»
 * ---------------------------------------------------------------------------
 * این فایل مدل دامنه‌ی کل پلتفرم را تعریف می‌کند: کاربران و نقش‌ها، سازمان‌ها و
 * ادارات، اصناف و کسب‌وکارها، محتوای خبری و نسخه‌ها، گزارش‌های مردمی و پرونده‌ها،
 * آمار و مجموعه‌داده‌ها، ارتباطات سازمانی، اعلان‌ها و لاگ حسابرسی.
 *
 * قواعد کلی:
 *  - نام جدول‌ها و ستون‌ها انگلیسی و snake_case هستند.
 *  - هر داده‌ی حساس یا سازمانی دارای ستون سطح دسترسی (visibility) است.
 *  - هیچ حذف فیزیکی برای محتوای منتشرشده انجام نمی‌شود (soft delete + audit).
 */
import {
  pgTable,
  text,
  uuid,
  timestamp,
  integer,
  boolean,
  jsonb,
  doublePrecision,
  primaryKey,
  index,
  uniqueIndex,
} from 'drizzle-orm/pg-core';
import { sql } from 'drizzle-orm';

const id = () => uuid('id').primaryKey().defaultRandom();
const createdAt = () => timestamp('created_at', { withTimezone: true }).notNull().defaultNow();
const updatedAt = () => timestamp('updated_at', { withTimezone: true }).notNull().defaultNow();

/* ========================================================================== */
/* هویت، نقش و دسترسی                                                          */
/* ========================================================================== */

export const users = pgTable(
  'users',
  {
    id: id(),
    email: text('email').notNull(),
    phone: text('phone'),
    passwordHash: text('password_hash').notNull(),
    displayName: text('display_name').notNull(),
    // نام مستعار عمومی؛ برای گزارش‌های ناشناس هرگز نمایش داده نمی‌شود
    handle: text('handle'),
    avatarUrl: text('avatar_url'),
    bio: text('bio'),
    status: text('status').notNull().default('active'), // active | suspended | pending
    isSystem: boolean('is_system').notNull().default(false),
    failedLoginCount: integer('failed_login_count').notNull().default(0),
    lockedUntil: timestamp('locked_until', { withTimezone: true }),
    lastLoginAt: timestamp('last_login_at', { withTimezone: true }),
    createdAt: createdAt(),
    updatedAt: updatedAt(),
  },
  (t) => ({
    emailIdx: uniqueIndex('users_email_uidx').on(t.email),
  }),
);

export const roles = pgTable(
  'roles',
  {
    id: id(),
    key: text('key').notNull(), // super_admin, editor_in_chief, ...
    nameFa: text('name_fa').notNull(),
    description: text('description'),
    // scope مشخص می‌کند نقش در چه فضایی معنا دارد: global | organization | business
    scope: text('scope').notNull().default('global'),
    isBuiltin: boolean('is_builtin').notNull().default(true),
    createdAt: createdAt(),
  },
  (t) => ({ keyIdx: uniqueIndex('roles_key_uidx').on(t.key) }),
);

export const permissions = pgTable(
  'permissions',
  {
    id: id(),
    key: text('key').notNull(), // article.publish, report.route, ...
    nameFa: text('name_fa').notNull(),
    group: text('group').notNull(),
    createdAt: createdAt(),
  },
  (t) => ({ keyIdx: uniqueIndex('permissions_key_uidx').on(t.key) }),
);

export const rolePermissions = pgTable(
  'role_permissions',
  {
    roleId: uuid('role_id').notNull().references(() => roles.id, { onDelete: 'cascade' }),
    permissionId: uuid('permission_id').notNull().references(() => permissions.id, { onDelete: 'cascade' }),
  },
  (t) => ({ pk: primaryKey({ columns: [t.roleId, t.permissionId] }) }),
);

/**
 * انتساب نقش به کاربر.
 * scopeType/scopeId اجازه می‌دهد یک نقش فقط داخل یک سازمان یا کسب‌وکار معتبر باشد
 * (پایه‌ی جداسازی چند-مستاجری در لایه‌ی سرور).
 */
export const userRoles = pgTable(
  'user_roles',
  {
    id: id(),
    userId: uuid('user_id').notNull().references(() => users.id, { onDelete: 'cascade' }),
    roleId: uuid('role_id').notNull().references(() => roles.id, { onDelete: 'cascade' }),
    scopeType: text('scope_type').notNull().default('global'), // global | organization | department | business
    scopeId: uuid('scope_id'),
    grantedBy: uuid('granted_by'),
    createdAt: createdAt(),
  },
  (t) => ({
    userIdx: index('user_roles_user_idx').on(t.userId),
    scopeIdx: index('user_roles_scope_idx').on(t.scopeType, t.scopeId),
  }),
);

export const sessions = pgTable(
  'sessions',
  {
    id: id(),
    userId: uuid('user_id').notNull().references(() => users.id, { onDelete: 'cascade' }),
    tokenHash: text('token_hash').notNull(),
    // چرخش توکن: هر نشست به نشست قبلی زنجیر می‌شود تا سرقت توکن قابل تشخیص باشد
    rotatedFrom: uuid('rotated_from'),
    userAgent: text('user_agent'),
    ip: text('ip'),
    expiresAt: timestamp('expires_at', { withTimezone: true }).notNull(),
    revokedAt: timestamp('revoked_at', { withTimezone: true }),
    createdAt: createdAt(),
  },
  (t) => ({ tokenIdx: uniqueIndex('sessions_token_uidx').on(t.tokenHash) }),
);

/* ========================================================================== */
/* جغرافیا و طبقه‌بندی                                                          */
/* ========================================================================== */

export const regions = pgTable(
  'regions',
  {
    id: id(),
    nameFa: text('name_fa').notNull(),
    slug: text('slug').notNull(),
    kind: text('kind').notNull(), // province | city | district | neighborhood
    parentId: uuid('parent_id'),
    lat: doublePrecision('lat'),
    lng: doublePrecision('lng'),
    createdAt: createdAt(),
  },
  (t) => ({ slugIdx: uniqueIndex('regions_slug_uidx').on(t.slug) }),
);

/** طبقه‌بندی‌های قابل تنظیم از پنل مدیریت (نوع سازمان، دسته خبر، موضوع، صنف و ...) */
export const taxonomies = pgTable(
  'taxonomies',
  {
    id: id(),
    kind: text('kind').notNull(), // organization_type | article_category | topic | tag | service | report_category | business_category
    key: text('key').notNull(),
    nameFa: text('name_fa').notNull(),
    parentId: uuid('parent_id'),
    icon: text('icon'),
    color: text('color'),
    orderIndex: integer('order_index').notNull().default(0),
    isActive: boolean('is_active').notNull().default(true),
    metadata: jsonb('metadata').$type<Record<string, unknown>>().default({}),
    createdAt: createdAt(),
  },
  (t) => ({ kindKeyIdx: uniqueIndex('taxonomies_kind_key_uidx').on(t.kind, t.key) }),
);

/* ========================================================================== */
/* سازمان‌ها                                                                    */
/* ========================================================================== */

export const organizations = pgTable(
  'organizations',
  {
    id: id(),
    slug: text('slug').notNull(),
    nameFa: text('name_fa').notNull(),
    shortName: text('short_name'),
    typeKey: text('type_key').notNull(), // ارجاع منطقی به taxonomies(kind='organization_type')
    parentId: uuid('parent_id'),
    regionId: uuid('region_id').references(() => regions.id),
    description: text('description'),
    logoUrl: text('logo_url'),
    coverUrl: text('cover_url'),
    address: text('address'),
    lat: doublePrecision('lat'),
    lng: doublePrecision('lng'),
    phone: text('phone'),
    website: text('website'),
    email: text('email'),
    // وضعیت تأیید فقط توسط مدیر سیستم و با مدرک قابل تغییر است
    verificationStatus: text('verification_status').notNull().default('unverified'),
    verifiedAt: timestamp('verified_at', { withTimezone: true }),
    verifiedBy: uuid('verified_by'),
    isActive: boolean('is_active').notNull().default(true),
    // پیکربندی SLA برای همین سازمان (ساعت)
    slaConfig: jsonb('sla_config').$type<{ critical: number; high: number; normal: number; low: number }>()
      .default({ critical: 4, high: 24, normal: 72, low: 168 }),
    escalationPolicy: jsonb('escalation_policy').$type<{ steps: { afterHours: number; to: string }[] }>()
      .default({ steps: [] }),
    dataSource: text('data_source').notNull().default('demo'), // demo | official | user_submitted
    createdAt: createdAt(),
    updatedAt: updatedAt(),
  },
  (t) => ({
    slugIdx: uniqueIndex('organizations_slug_uidx').on(t.slug),
    typeIdx: index('organizations_type_idx').on(t.typeKey),
  }),
);

export const departments = pgTable(
  'departments',
  {
    id: id(),
    organizationId: uuid('organization_id').notNull().references(() => organizations.id, { onDelete: 'cascade' }),
    parentId: uuid('parent_id'),
    nameFa: text('name_fa').notNull(),
    slug: text('slug').notNull(),
    kind: text('kind').notNull().default('unit'), // deputy | office | unit
    regionId: uuid('region_id').references(() => regions.id),
    phone: text('phone'),
    address: text('address'),
    lat: doublePrecision('lat'),
    lng: doublePrecision('lng'),
    createdAt: createdAt(),
  },
  (t) => ({ orgIdx: index('departments_org_idx').on(t.organizationId) }),
);

export const organizationMembers = pgTable(
  'organization_members',
  {
    id: id(),
    organizationId: uuid('organization_id').notNull().references(() => organizations.id, { onDelete: 'cascade' }),
    departmentId: uuid('department_id').references(() => departments.id),
    userId: uuid('user_id').notNull().references(() => users.id, { onDelete: 'cascade' }),
    position: text('position'), // سمت
    isOfficial: boolean('is_official').notNull().default(false), // مقام رسمی
    isPublic: boolean('is_public').notNull().default(false),
    createdAt: createdAt(),
  },
  (t) => ({
    orgUserIdx: uniqueIndex('org_members_uidx').on(t.organizationId, t.userId),
  }),
);

export const organizationServices = pgTable('organization_services', {
  id: id(),
  organizationId: uuid('organization_id').notNull().references(() => organizations.id, { onDelete: 'cascade' }),
  departmentId: uuid('department_id').references(() => departments.id),
  nameFa: text('name_fa').notNull(),
  description: text('description'),
  serviceKey: text('service_key'),
  createdAt: createdAt(),
});

/** کانال‌های ارتباطی سازمان (تلگرام، ایتا، روابط عمومی) */
export const communicationChannels = pgTable(
  'communication_channels',
  {
    id: id(),
    organizationId: uuid('organization_id').references(() => organizations.id, { onDelete: 'cascade' }),
    businessId: uuid('business_id'),
    departmentId: uuid('department_id').references(() => departments.id),
    provider: text('provider').notNull(), // telegram | eitaa | email | phone | web
    handle: text('handle').notNull(), // @username یا شناسه
    externalId: text('external_id'),
    ownerName: text('owner_name'), // مسئول ارتباط
    priority: integer('priority').notNull().default(100),
    verificationStatus: text('verification_status').notNull().default('unverified'),
    isActive: boolean('is_active').notNull().default(true),
    createdAt: createdAt(),
  },
  (t) => ({ orgIdx: index('comm_channels_org_idx').on(t.organizationId) }),
);

/** صف تحویل پیام‌های خروجی؛ وضعیت واقعی تحویل هرگز جعل نمی‌شود */
export const outboundMessages = pgTable('outbound_messages', {
  id: id(),
  channelId: uuid('channel_id').notNull().references(() => communicationChannels.id, { onDelete: 'cascade' }),
  provider: text('provider').notNull(),
  payload: jsonb('payload').$type<Record<string, unknown>>().notNull(),
  // queued | sending | delivered | failed | unavailable (اعتبارنامه/سرویس در دسترس نیست)
  state: text('state').notNull().default('queued'),
  deepLink: text('deep_link'),
  attempts: integer('attempts').notNull().default(0),
  lastError: text('last_error'),
  createdBy: uuid('created_by'),
  createdAt: createdAt(),
  updatedAt: updatedAt(),
});

/** پیام رسمی بین دو سازمان */
export const organizationMessages = pgTable('organization_messages', {
  id: id(),
  fromOrganizationId: uuid('from_organization_id').notNull().references(() => organizations.id),
  toOrganizationId: uuid('to_organization_id').notNull().references(() => organizations.id),
  fromDepartmentId: uuid('from_department_id'),
  toDepartmentId: uuid('to_department_id'),
  subject: text('subject').notNull(),
  body: text('body').notNull(),
  priority: text('priority').notNull().default('normal'), // critical | high | normal | low
  deadline: timestamp('deadline', { withTimezone: true }),
  actionRequested: boolean('action_requested').notNull().default(false),
  relatedCaseId: uuid('related_case_id'),
  attachments: jsonb('attachments').$type<string[]>().default([]),
  status: text('status').notNull().default('sent'), // sent | read | acknowledged | closed
  createdBy: uuid('created_by').notNull(),
  createdAt: createdAt(),
});

/* ========================================================================== */
/* اصناف و کسب‌وکارها                                                           */
/* ========================================================================== */

export const guilds = pgTable(
  'guilds',
  {
    id: id(),
    slug: text('slug').notNull(),
    nameFa: text('name_fa').notNull(),
    kind: text('kind').notNull().default('guild'), // union (اتحادیه) | guild (صنف)
    parentId: uuid('parent_id'),
    regionId: uuid('region_id').references(() => regions.id),
    organizationId: uuid('organization_id').references(() => organizations.id), // نهاد ناظر
    description: text('description'),
    createdAt: createdAt(),
  },
  (t) => ({ slugIdx: uniqueIndex('guilds_slug_uidx').on(t.slug) }),
);

export const businesses = pgTable(
  'businesses',
  {
    id: id(),
    slug: text('slug').notNull(),
    nameFa: text('name_fa').notNull(),
    categoryKey: text('category_key').notNull(),
    guildId: uuid('guild_id').references(() => guilds.id),
    ownerUserId: uuid('owner_user_id').references(() => users.id),
    ownerNamePublic: text('owner_name_public'), // فقط اگر مالک اجازه داده باشد
    managerUserId: uuid('manager_user_id').references(() => users.id),
    regionId: uuid('region_id').references(() => regions.id),
    description: text('description'),
    logoUrl: text('logo_url'),
    website: text('website'),
    phone: text('phone'),
    socials: jsonb('socials').$type<Record<string, string>>().default({}),
    verificationStatus: text('verification_status').notNull().default('unverified'),
    locationVerified: boolean('location_verified').notNull().default(false),
    identityVerified: boolean('identity_verified').notNull().default(false),
    isActive: boolean('is_active').notNull().default(true),
    dataSource: text('data_source').notNull().default('demo'),
    createdAt: createdAt(),
    updatedAt: updatedAt(),
  },
  (t) => ({ slugIdx: uniqueIndex('businesses_slug_uidx').on(t.slug) }),
);

export const shops = pgTable('shops', {
  id: id(),
  businessId: uuid('business_id').notNull().references(() => businesses.id, { onDelete: 'cascade' }),
  nameFa: text('name_fa').notNull(),
  isMainBranch: boolean('is_main_branch').notNull().default(false),
  regionId: uuid('region_id').references(() => regions.id),
  address: text('address'),
  lat: doublePrecision('lat'),
  lng: doublePrecision('lng'),
  phone: text('phone'),
  openingHours: jsonb('opening_hours').$type<Record<string, string>>().default({}),
  images: jsonb('images').$type<string[]>().default([]),
  createdAt: createdAt(),
});

export const businessOfferings = pgTable('business_offerings', {
  id: id(),
  businessId: uuid('business_id').notNull().references(() => businesses.id, { onDelete: 'cascade' }),
  kind: text('kind').notNull().default('service'), // service | product
  nameFa: text('name_fa').notNull(),
  description: text('description'),
  priceLabel: text('price_label'),
  createdAt: createdAt(),
});

/* ========================================================================== */
/* محتوای خبری                                                                  */
/* ========================================================================== */

export const articles = pgTable(
  'articles',
  {
    id: id(),
    slug: text('slug').notNull(),
    title: text('title').notNull(),
    summary: text('summary'),
    body: text('body').notNull(),
    contentType: text('content_type').notNull().default('news'), // breaking | news | report | analysis | interview | investigation | announcement | statement | data | infographic | audio | video
    categoryKey: text('category_key'),
    topicKeys: jsonb('topic_keys').$type<string[]>().default([]),
    tagKeys: jsonb('tag_keys').$type<string[]>().default([]),
    coverUrl: text('cover_url'),
    coverAlt: text('cover_alt'),
    audioUrl: text('audio_url'),
    audioDurationSec: integer('audio_duration_sec'),
    videoUrl: text('video_url'),
    regionId: uuid('region_id').references(() => regions.id),
    organizationId: uuid('organization_id').references(() => organizations.id),
    businessId: uuid('business_id').references(() => businesses.id),
    datasetIds: jsonb('dataset_ids').$type<string[]>().default([]),
    sourceRefs: jsonb('source_refs').$type<{ title: string; url?: string; publisher?: string; date?: string }[]>().default([]),
    authorId: uuid('author_id').notNull().references(() => users.id),
    // وضعیت گردش کار تحریریه
    status: text('status').notNull().default('draft'), // draft | submitted | in_review | changes_requested | approved | scheduled | published | archived
    scheduledFor: timestamp('scheduled_for', { withTimezone: true }),
    publishedAt: timestamp('published_at', { withTimezone: true }),
    publishedBy: uuid('published_by'),
    /**
     * قانون ۱۰ روزه: از لحظه انتشار، تا editable_until ویرایش عادی مجاز است.
     * پس از آن رکورد قفل می‌شود (locked=true) و فقط «اصلاحیه» با ثبت دلیل و نسخه
     * جدید امکان‌پذیر است. اجرای این قانون در لایه سرویس/دیتابیس انجام می‌شود.
     */
    editableUntil: timestamp('editable_until', { withTimezone: true }),
    isLocked: boolean('is_locked').notNull().default(false),
    currentRevision: integer('current_revision').notNull().default(1),
    viewCount: integer('view_count').notNull().default(0),
    discussionCount: integer('discussion_count').notNull().default(0),
    saveCount: integer('save_count').notNull().default(0),
    isBreaking: boolean('is_breaking').notNull().default(false),
    isFeatured: boolean('is_featured').notNull().default(false),
    dataSource: text('data_source').notNull().default('demo'),
    deletedAt: timestamp('deleted_at', { withTimezone: true }),
    createdAt: createdAt(),
    updatedAt: updatedAt(),
  },
  (t) => ({
    slugIdx: uniqueIndex('articles_slug_uidx').on(t.slug),
    statusIdx: index('articles_status_idx').on(t.status, t.publishedAt),
  }),
);

/**
 * نسخه‌های خبر. هر نسخه پس از ثبت تغییرناپذیر است (append-only) و پایه‌ی
 * آرشیو غیرقابل‌تغییر و سیستم اصلاحیه محسوب می‌شود.
 */
export const articleRevisions = pgTable(
  'article_revisions',
  {
    id: id(),
    articleId: uuid('article_id').notNull().references(() => articles.id, { onDelete: 'cascade' }),
    revision: integer('revision').notNull(),
    title: text('title').notNull(),
    summary: text('summary'),
    body: text('body').notNull(),
    changeKind: text('change_kind').notNull().default('edit'), // create | edit | publish | correction
    reason: text('reason'),
    contentHash: text('content_hash').notNull(),
    editorId: uuid('editor_id').notNull(),
    createdAt: createdAt(),
  },
  (t) => ({ artRevIdx: uniqueIndex('article_revisions_uidx').on(t.articleId, t.revision) }),
);

/** اصلاحیه‌ی رسمی پس از پایان پنجره ویرایش */
export const articleCorrections = pgTable('article_corrections', {
  id: id(),
  articleId: uuid('article_id').notNull().references(() => articles.id, { onDelete: 'cascade' }),
  revisionId: uuid('revision_id').notNull().references(() => articleRevisions.id),
  reason: text('reason').notNull(),
  note: text('note').notNull(),
  responsibleUserId: uuid('responsible_user_id').notNull(),
  createdAt: createdAt(),
});

export const articleWorkflowEvents = pgTable('article_workflow_events', {
  id: id(),
  articleId: uuid('article_id').notNull().references(() => articles.id, { onDelete: 'cascade' }),
  fromStatus: text('from_status'),
  toStatus: text('to_status').notNull(),
  actorId: uuid('actor_id').notNull(),
  note: text('note'),
  createdAt: createdAt(),
});

export const comments = pgTable('comments', {
  id: id(),
  articleId: uuid('article_id').references(() => articles.id, { onDelete: 'cascade' }),
  caseId: uuid('case_id'),
  parentId: uuid('parent_id'),
  userId: uuid('user_id').references(() => users.id),
  body: text('body').notNull(),
  status: text('status').notNull().default('pending'), // pending | approved | rejected
  moderatedBy: uuid('moderated_by'),
  createdAt: createdAt(),
});

/* ========================================================================== */
/* آمار و داده                                                                  */
/* ========================================================================== */

export const datasets = pgTable(
  'datasets',
  {
    id: id(),
    slug: text('slug').notNull(),
    titleFa: text('title_fa').notNull(),
    description: text('description'),
    unit: text('unit').notNull(),
    // منشأ داده (Data Provenance) — نمایش عمومی الزامی است
    sourceTitle: text('source_title').notNull(),
    sourceUrl: text('source_url'),
    publisher: text('publisher').notNull(),
    methodology: text('methodology'),
    geographicScope: text('geographic_scope'),
    organizationId: uuid('organization_id').references(() => organizations.id),
    frequency: text('frequency'), // daily | monthly | yearly
    chartHint: text('chart_hint').notNull().default('line'), // line|bar|area|scatter|heatmap|ranking|geo
    status: text('status').notNull().default('draft'), // draft | published
    dataSource: text('data_source').notNull().default('demo'),
    lastUpdatedAt: timestamp('last_updated_at', { withTimezone: true }),
    createdBy: uuid('created_by'),
    createdAt: createdAt(),
  },
  (t) => ({ slugIdx: uniqueIndex('datasets_slug_uidx').on(t.slug) }),
);

export const datasetPoints = pgTable(
  'dataset_points',
  {
    id: id(),
    datasetId: uuid('dataset_id').notNull().references(() => datasets.id, { onDelete: 'cascade' }),
    seriesKey: text('series_key').notNull().default('default'), // مثلاً نام استان برای مقایسه
    periodLabel: text('period_label').notNull(), // مثلاً «۱۴۰۴/۰۳»
    periodStart: timestamp('period_start', { withTimezone: true }),
    value: doublePrecision('value').notNull(),
    regionId: uuid('region_id').references(() => regions.id),
    note: text('note'),
  },
  (t) => ({ dsIdx: index('dataset_points_ds_idx').on(t.datasetId, t.seriesKey) }),
);

/* ========================================================================== */
/* گزارش مردمی و پرونده                                                         */
/* ========================================================================== */

export const reports = pgTable(
  'reports',
  {
    id: id(),
    publicCode: text('public_code').notNull(), // کد رهگیری قابل نمایش
    kind: text('kind').notNull(), // report | complaint | suggestion | request | question | alert
    categoryKey: text('category_key').notNull(), // water | power | transport | health | environment | municipal | business | guild | other
    title: text('title').notNull(),
    body: text('body').notNull(),
    severity: text('severity').notNull().default('normal'), // critical | high | normal | low
    regionId: uuid('region_id').references(() => regions.id),
    lat: doublePrecision('lat'),
    lng: doublePrecision('lng'),
    address: text('address'),
    reporterUserId: uuid('reporter_user_id').references(() => users.id),
    /**
     * سطح حریم خصوصی گزارش:
     * public | public_after_review | private_to_org | anonymous_public | anonymous_to_org
     * هویت گزارش‌دهنده در حالت‌های anonymous هرگز در پاسخ‌های عمومی API قرار نمی‌گیرد.
     */
    privacy: text('privacy').notNull().default('public_after_review'),
    targetOrganizationId: uuid('target_organization_id').references(() => organizations.id),
    targetBusinessId: uuid('target_business_id').references(() => businesses.id),
    status: text('status').notNull().default('draft'),
    moderationNote: text('moderation_note'),
    moderatedBy: uuid('moderated_by'),
    duplicateOfId: uuid('duplicate_of_id'),
    createdAt: createdAt(),
    updatedAt: updatedAt(),
  },
  (t) => ({
    codeIdx: uniqueIndex('reports_code_uidx').on(t.publicCode),
    statusIdx: index('reports_status_idx').on(t.status),
  }),
);

export const reportEvidence = pgTable('report_evidence', {
  id: id(),
  reportId: uuid('report_id').notNull().references(() => reports.id, { onDelete: 'cascade' }),
  mediaId: uuid('media_id'),
  kind: text('kind').notNull(), // image | video | document | link | location
  url: text('url'),
  caption: text('caption'),
  createdAt: createdAt(),
});

export const cases = pgTable(
  'cases',
  {
    id: id(),
    caseCode: text('case_code').notNull(),
    reportId: uuid('report_id').notNull().references(() => reports.id, { onDelete: 'cascade' }),
    title: text('title').notNull(),
    organizationId: uuid('organization_id').references(() => organizations.id),
    departmentId: uuid('department_id').references(() => departments.id),
    businessId: uuid('business_id').references(() => businesses.id),
    assigneeUserId: uuid('assignee_user_id').references(() => users.id),
    priority: text('priority').notNull().default('normal'),
    /** وضعیت پرونده مطابق چرخه عمر تعریف‌شده در مستندات */
    status: text('status').notNull().default('accepted'),
    slaDueAt: timestamp('sla_due_at', { withTimezone: true }),
    slaBreachedAt: timestamp('sla_breached_at', { withTimezone: true }),
    escalationLevel: integer('escalation_level').notNull().default(0),
    resolutionNote: text('resolution_note'),
    closedAt: timestamp('closed_at', { withTimezone: true }),
    reopenCount: integer('reopen_count').notNull().default(0),
    visibility: text('visibility').notNull().default('public'), // public | organization_internal | private
    createdAt: createdAt(),
    updatedAt: updatedAt(),
  },
  (t) => ({
    codeIdx: uniqueIndex('cases_code_uidx').on(t.caseCode),
    orgIdx: index('cases_org_idx').on(t.organizationId, t.status),
  }),
);

export const caseEvents = pgTable('case_events', {
  id: id(),
  caseId: uuid('case_id').notNull().references(() => cases.id, { onDelete: 'cascade' }),
  kind: text('kind').notNull(), // status_change | assignment | routing | note | response | escalation | sla_breach | reopen
  fromValue: text('from_value'),
  toValue: text('to_value'),
  note: text('note'),
  actorId: uuid('actor_id'),
  visibility: text('visibility').notNull().default('public'),
  createdAt: createdAt(),
});

/** پاسخ رسمی سازمان یا کسب‌وکار */
export const officialResponses = pgTable('official_responses', {
  id: id(),
  caseId: uuid('case_id').notNull().references(() => cases.id, { onDelete: 'cascade' }),
  organizationId: uuid('organization_id').references(() => organizations.id),
  businessId: uuid('business_id').references(() => businesses.id),
  body: text('body').notNull(),
  attachments: jsonb('attachments').$type<string[]>().default([]),
  sourceRef: text('source_ref'),
  responderUserId: uuid('responder_user_id').notNull(),
  responderTitle: text('responder_title'),
  status: text('status').notNull().default('published'), // draft | published | retracted
  createdAt: createdAt(),
});

export const tasks = pgTable('tasks', {
  id: id(),
  organizationId: uuid('organization_id').notNull().references(() => organizations.id, { onDelete: 'cascade' }),
  departmentId: uuid('department_id').references(() => departments.id),
  title: text('title').notNull(),
  description: text('description'),
  assigneeUserId: uuid('assignee_user_id').references(() => users.id),
  priority: text('priority').notNull().default('normal'),
  deadline: timestamp('deadline', { withTimezone: true }),
  status: text('status').notNull().default('open'), // open | in_progress | blocked | done | cancelled
  sourceKind: text('source_kind'), // case | message | manual
  sourceId: uuid('source_id'),
  relatedCaseId: uuid('related_case_id').references(() => cases.id),
  attachments: jsonb('attachments').$type<string[]>().default([]),
  createdBy: uuid('created_by').notNull(),
  createdAt: createdAt(),
  updatedAt: updatedAt(),
});

/** قواعد مسیریابی خودکار گزارش‌ها — کاملاً از پنل مدیریت قابل تنظیم */
export const routingRules = pgTable('routing_rules', {
  id: id(),
  nameFa: text('name_fa').notNull(),
  priority: integer('priority').notNull().default(100), // عدد کمتر = اولویت بالاتر
  isActive: boolean('is_active').notNull().default(true),
  // شرط‌ها: هر کدام اختیاری‌اند و در صورت وجود باید برقرار باشند (AND)
  matchCategoryKey: text('match_category_key'),
  matchKind: text('match_kind'),
  matchSeverity: text('match_severity'),
  matchRegionId: uuid('match_region_id'),
  matchOrganizationTypeKey: text('match_organization_type_key'),
  matchKeywords: jsonb('match_keywords').$type<string[]>().default([]),
  // نتیجه
  targetOrganizationId: uuid('target_organization_id').references(() => organizations.id),
  targetDepartmentId: uuid('target_department_id').references(() => departments.id),
  targetGuildId: uuid('target_guild_id').references(() => guilds.id),
  setPriority: text('set_priority'),
  createdAt: createdAt(),
});

/* ========================================================================== */
/* رسانه، جست‌وجو، اعلان، حسابرسی، تحلیل                                        */
/* ========================================================================== */

export const mediaAssets = pgTable('media_assets', {
  id: id(),
  kind: text('kind').notNull(), // image | video | audio | pdf | document
  storageKey: text('storage_key').notNull(),
  originalName: text('original_name').notNull(),
  mimeType: text('mime_type').notNull(),
  sizeBytes: integer('size_bytes').notNull(),
  checksumSha256: text('checksum_sha256').notNull(),
  width: integer('width'),
  height: integer('height'),
  durationSec: integer('duration_sec'),
  altText: text('alt_text'),
  caption: text('caption'),
  copyright: text('copyright'),
  folder: text('folder').notNull().default('/'),
  tags: jsonb('tags').$type<string[]>().default([]),
  visibility: text('visibility').notNull().default('public'),
  scanStatus: text('scan_status').notNull().default('pending'), // pending | clean | rejected
  usageCount: integer('usage_count').notNull().default(0),
  uploadedBy: uuid('uploaded_by'),
  createdAt: createdAt(),
});

/** ایندکس جست‌وجوی داخلی (fallback پستگرس وقتی OpenSearch در دسترس نیست) */
export const searchDocuments = pgTable(
  'search_documents',
  {
    id: id(),
    entityType: text('entity_type').notNull(), // article | organization | business | shop | report | case | dataset | guild
    entityId: uuid('entity_id').notNull(),
    title: text('title').notNull(),
    summary: text('summary'),
    // متن نرمال‌شده فارسی (ی/ک عربی، ارقام، نیم‌فاصله و ...) برای تطبیق پایدار
    normalizedText: text('normalized_text').notNull(),
    facets: jsonb('facets').$type<Record<string, unknown>>().notNull().default({}),
    url: text('url').notNull(),
    publishedAt: timestamp('published_at', { withTimezone: true }),
    popularity: integer('popularity').notNull().default(0),
    hasImage: boolean('has_image').notNull().default(false),
    hasVideo: boolean('has_video').notNull().default(false),
    hasDocument: boolean('has_document').notNull().default(false),
    updatedAt: updatedAt(),
  },
  (t) => ({
    entIdx: uniqueIndex('search_documents_uidx').on(t.entityType, t.entityId),
    typeIdx: index('search_documents_type_idx').on(t.entityType),
  }),
);

export const searchSynonyms = pgTable('search_synonyms', {
  id: id(),
  term: text('term').notNull(),
  synonyms: jsonb('synonyms').$type<string[]>().notNull().default([]),
});

export const savedSearches = pgTable('saved_searches', {
  id: id(),
  userId: uuid('user_id').notNull().references(() => users.id, { onDelete: 'cascade' }),
  nameFa: text('name_fa').notNull(),
  query: jsonb('query').$type<Record<string, unknown>>().notNull(),
  notifyEnabled: boolean('notify_enabled').notNull().default(true),
  lastCheckedAt: timestamp('last_checked_at', { withTimezone: true }),
  createdAt: createdAt(),
});

export const searchQueries = pgTable('search_queries', {
  id: id(),
  userId: uuid('user_id'),
  rawQuery: text('raw_query').notNull(),
  normalizedQuery: text('normalized_query').notNull(),
  resultCount: integer('result_count').notNull().default(0),
  createdAt: createdAt(),
});

export const notifications = pgTable(
  'notifications',
  {
    id: id(),
    userId: uuid('user_id').notNull().references(() => users.id, { onDelete: 'cascade' }),
    kind: text('kind').notNull(),
    title: text('title').notNull(),
    body: text('body'),
    url: text('url'),
    entityType: text('entity_type'),
    entityId: uuid('entity_id'),
    readAt: timestamp('read_at', { withTimezone: true }),
    createdAt: createdAt(),
  },
  (t) => ({ userIdx: index('notifications_user_idx').on(t.userId, t.readAt) }),
);

export const follows = pgTable(
  'follows',
  {
    id: id(),
    userId: uuid('user_id').notNull().references(() => users.id, { onDelete: 'cascade' }),
    entityType: text('entity_type').notNull(), // organization | business | topic | region
    entityId: text('entity_id').notNull(),
    createdAt: createdAt(),
  },
  (t) => ({ uidx: uniqueIndex('follows_uidx').on(t.userId, t.entityType, t.entityId) }),
);

export const bookmarks = pgTable(
  'bookmarks',
  {
    id: id(),
    userId: uuid('user_id').notNull().references(() => users.id, { onDelete: 'cascade' }),
    articleId: uuid('article_id').notNull().references(() => articles.id, { onDelete: 'cascade' }),
    createdAt: createdAt(),
  },
  (t) => ({ uidx: uniqueIndex('bookmarks_uidx').on(t.userId, t.articleId) }),
);

export const readingHistory = pgTable('reading_history', {
  id: id(),
  userId: uuid('user_id').notNull().references(() => users.id, { onDelete: 'cascade' }),
  articleId: uuid('article_id').notNull().references(() => articles.id, { onDelete: 'cascade' }),
  progress: doublePrecision('progress').notNull().default(0),
  audioPositionSec: integer('audio_position_sec').notNull().default(0),
  createdAt: createdAt(),
});

/**
 * لاگ حسابرسی؛ فقط افزودنی است. هیچ مسیری در API اجازه حذف یا ویرایش آن را ندارد.
 */
export const auditLogs = pgTable(
  'audit_logs',
  {
    id: id(),
    action: text('action').notNull(),
    actorId: uuid('actor_id'),
    actorLabel: text('actor_label'),
    entityType: text('entity_type'),
    entityId: text('entity_id'),
    ip: text('ip'),
    userAgent: text('user_agent'),
    before: jsonb('before').$type<Record<string, unknown> | null>(),
    after: jsonb('after').$type<Record<string, unknown> | null>(),
    metadata: jsonb('metadata').$type<Record<string, unknown>>().default({}),
    createdAt: createdAt(),
  },
  (t) => ({
    actionIdx: index('audit_logs_action_idx').on(t.action, t.createdAt),
    entityIdx: index('audit_logs_entity_idx').on(t.entityType, t.entityId),
  }),
);

export const analyticsEvents = pgTable(
  'analytics_events',
  {
    id: id(),
    kind: text('kind').notNull(), // page_view | search | failed_search | article_view | report_submit | case_resolved
    entityType: text('entity_type'),
    entityId: text('entity_id'),
    userId: uuid('user_id'),
    metadata: jsonb('metadata').$type<Record<string, unknown>>().default({}),
    createdAt: createdAt(),
  },
  (t) => ({ kindIdx: index('analytics_events_kind_idx').on(t.kind, t.createdAt) }),
);

export const settings = pgTable('settings', {
  key: text('key').primaryKey(),
  value: jsonb('value').$type<unknown>().notNull(),
  updatedBy: uuid('updated_by'),
  updatedAt: updatedAt(),
});

export const schemaMeta = { version: 1, generatedFor: 'postgresql', sqlHelper: sql };