import { and, asc, count, desc, eq, inArray, isNull } from "drizzle-orm";
import { drizzle } from "drizzle-orm/mysql2";
import { appNotifications, appSettings, contentEntries, contractTemplateSections, customerDeviceTokens, customerLocations, customers, fieldTasks, InsertCustomerLocation, InsertPhoneOtpChallenge, InsertServiceOrder, InsertUser, inventoryItems, orderContracts, orderInvoices, orderPayments, orderQuotes, orderRatings, phoneOtpChallenges, promotions, quoteItems, serviceCatalog, serviceCategories, serviceOrders, supportConversations, supportKnowledgeArticles, supportMessages, supportQuickPrompts, taskActivity, taskAssignments, taskChecklistItems, taskCompletionReviews, taskEvidence, taskMessages, taskPartRequests, taskParts, taskTimeEntries, technicianProfiles, technicianTeamMembers, technicianTeams, users } from "../drizzle/schema";
import { ENV } from "./_core/env";
import { storagePut } from "./storage";
import { canMoveCustomerOrderStatus, type CustomerOrderStatus } from "../shared/order-status";

let _db: ReturnType<typeof drizzle> | null = null;

// Lazily create the drizzle instance so local tooling can run without a DB.
export async function getDb() {
  if (!_db && process.env.DATABASE_URL) {
    try {
      _db = drizzle(process.env.DATABASE_URL);
    } catch (error) {
      console.warn("[Database] Failed to connect:", error);
      _db = null;
    }
  }
  return _db;
}

export type SmsProviderConfig = {
  endpoint: string;
  senderId: string;
  tokenSecretKey: string;
  isActive: boolean;
};

const SMS_PROVIDER_SETTING_KEYS = ["smsjo.endpoint", "smsjo.sender_id", "smsjo.token_secret_key", "smsjo.enabled"] as const;

/**
 * Reads non-sensitive SMS provider settings from the database. The API token is
 * deliberately never stored in this table: it remains a protected server secret.
 */
export async function getSmsProviderConfig(): Promise<SmsProviderConfig | null> {
  const db = await getDb();
  if (!db) return null;
  const rows = await db
    .select({ settingKey: appSettings.settingKey, value: appSettings.value })
    .from(appSettings)
    .where(inArray(appSettings.settingKey, [...SMS_PROVIDER_SETTING_KEYS]));
  const values = new Map(rows.map((row) => [row.settingKey, row.value?.trim() ?? ""]));
  const endpoint = values.get("smsjo.endpoint");
  const senderId = values.get("smsjo.sender_id");
  const tokenSecretKey = values.get("smsjo.token_secret_key");
  if (!endpoint || !senderId || !tokenSecretKey) return null;
  return { endpoint, senderId, tokenSecretKey, isActive: values.get("smsjo.enabled") !== "false" };
}

export async function getSmsOtpMessageTemplate(): Promise<string | null> {
  const db = await getDb();
  if (!db) return null;
  const rows = await db
    .select({ valueAr: contentEntries.valueAr })
    .from(contentEntries)
    .where(eq(contentEntries.contentKey, "sms.otp.message"))
    .limit(1);
  const template = rows[0]?.valueAr.trim();
  return template && template.includes("{{code}}") ? template : null;
}

export async function upsertUser(user: InsertUser): Promise<void> {
  if (!user.openId) {
    throw new Error("User openId is required for upsert");
  }

  const db = await getDb();
  if (!db) {
    console.warn("[Database] Cannot upsert user: database not available");
    return;
  }

  try {
    const values: InsertUser = {
      openId: user.openId,
    };
    const updateSet: Record<string, unknown> = {};

    const textFields = ["name", "email", "loginMethod"] as const;
    type TextField = (typeof textFields)[number];

    const assignNullable = (field: TextField) => {
      const value = user[field];
      if (value === undefined) return;
      const normalized = value ?? null;
      values[field] = normalized;
      updateSet[field] = normalized;
    };

    textFields.forEach(assignNullable);

    if (user.lastSignedIn !== undefined) {
      values.lastSignedIn = user.lastSignedIn;
      updateSet.lastSignedIn = user.lastSignedIn;
    }
    if (user.role !== undefined) {
      values.role = user.role;
      updateSet.role = user.role;
    } else if (user.openId === ENV.ownerOpenId) {
      values.role = "admin";
      updateSet.role = "admin";
    }

    if (!values.lastSignedIn) {
      values.lastSignedIn = new Date();
    }

    if (Object.keys(updateSet).length === 0) {
      updateSet.lastSignedIn = new Date();
    }

    await db.insert(users).values(values).onDuplicateKeyUpdate({
      set: updateSet,
    });
  } catch (error) {
    console.error("[Database] Failed to upsert user:", error);
    throw error;
  }
}

export async function getUserByOpenId(openId: string) {
  const db = await getDb();
  if (!db) {
    console.warn("[Database] Cannot get user: database not available");
    return undefined;
  }

  const result = await db.select().from(users).where(eq(users.openId, openId)).limit(1);

  return result.length > 0 ? result[0] : undefined;
}

export async function createPhoneOtpChallenge(challenge: InsertPhoneOtpChallenge) {
  const db = await getDb();
  if (!db) return null;
  const result = await db.insert(phoneOtpChallenges).values(challenge);
  return Number(result[0].insertId);
}

export async function getLatestPhoneOtpChallenge(phone: string) {
  const db = await getDb();
  if (!db) return null;
  const result = await db
    .select()
    .from(phoneOtpChallenges)
    .where(and(eq(phoneOtpChallenges.phone, phone), isNull(phoneOtpChallenges.consumedAt)))
    .orderBy(desc(phoneOtpChallenges.createdAt))
    .limit(1);
  return result[0] ?? null;
}

export async function consumePhoneOtpChallenge(id: number) {
  const db = await getDb();
  if (!db) return;
  await db.update(phoneOtpChallenges).set({ consumedAt: new Date() }).where(eq(phoneOtpChallenges.id, id));
}

export async function ensureCustomer(phone: string) {
  const db = await getDb();
  if (!db) return null;
  await db.insert(customers).values({ phone, isPhoneVerified: true }).onDuplicateKeyUpdate({ set: { isPhoneVerified: true } });
  const result = await db.select().from(customers).where(eq(customers.phone, phone)).limit(1);
  return result[0] ?? null;
}

export async function updateCustomerProfile(phone: string, profile: { fullName?: string; email?: string; avatarUrl?: string }) {
  const db = await getDb();
  if (!db) return null;
  await ensureCustomer(phone);
  await db.update(customers).set(profile).where(eq(customers.phone, phone));
  const result = await db.select().from(customers).where(eq(customers.phone, phone)).limit(1);
  return result[0] ?? null;
}

const avatarMimeTypes = ["image/jpeg", "image/png", "image/webp"] as const;
const maxAvatarBytes = 1_500_000;

function isAvatarPayloadValid(bytes: Buffer, mimeType: typeof avatarMimeTypes[number]) {
  if (mimeType === "image/jpeg") return bytes.length > 3 && bytes[0] === 0xff && bytes[1] === 0xd8 && bytes[2] === 0xff;
  if (mimeType === "image/png") return bytes.length > 8 && bytes.subarray(0, 8).equals(Buffer.from([0x89, 0x50, 0x4e, 0x47, 0x0d, 0x0a, 0x1a, 0x0a]));
  return bytes.length > 12 && bytes.subarray(0, 4).toString("ascii") === "RIFF" && bytes.subarray(8, 12).toString("ascii") === "WEBP";
}

export async function uploadCustomerAvatar(phone: string, input: { base64: string; mimeType: typeof avatarMimeTypes[number] }) {
  const db = await getDb();
  const customer = await ensureCustomer(phone);
  if (!db || !customer) throw new Error("تعذر الوصول إلى حساب العميل.");
  const bytes = Buffer.from(input.base64, "base64");
  if (!bytes.length || bytes.length > maxAvatarBytes || !isAvatarPayloadValid(bytes, input.mimeType)) {
    throw new Error("صورة الملف الشخصي غير صالحة أو يتجاوز حجمها الحد المسموح.");
  }
  const extension = input.mimeType === "image/png" ? "png" : input.mimeType === "image/webp" ? "webp" : "jpg";
  const stored = await storagePut(`customer-avatars/${customer.id}/avatar.${extension}`, bytes, input.mimeType);
  return updateCustomerProfile(phone, { avatarUrl: stored.url });
}

export async function listCustomerLocations(phone: string) {
  const db = await getDb();
  const customer = await ensureCustomer(phone);
  if (!db || !customer) return [];
  return db.select().from(customerLocations).where(eq(customerLocations.customerId, customer.id)).orderBy(desc(customerLocations.createdAt));
}

export async function createCustomerLocation(phone: string, location: Omit<InsertCustomerLocation, "customerId">) {
  const db = await getDb();
  const customer = await ensureCustomer(phone);
  if (!db || !customer) return null;
  const result = await db.insert(customerLocations).values({ ...location, customerId: customer.id });
  const id = Number(result[0].insertId);
  const rows = await db.select().from(customerLocations).where(eq(customerLocations.id, id)).limit(1);
  return rows[0] ?? null;
}

export async function listCustomerOrders(phone: string) {
  const db = await getDb();
  const customer = await ensureCustomer(phone);
  if (!db || !customer) return [];
  return db.select().from(serviceOrders).where(eq(serviceOrders.customerId, customer.id)).orderBy(desc(serviceOrders.createdAt));
}

export async function listAdminOrders(status?: CustomerOrderStatus) {
  const db = await getDb();
  if (!db) return [];
  return status
    ? db.select().from(serviceOrders).where(eq(serviceOrders.status, status)).orderBy(desc(serviceOrders.createdAt))
    : db.select().from(serviceOrders).orderBy(desc(serviceOrders.createdAt));
}

export async function updateAdminOrderStatus(input: { orderId: number; adminId: number; status: CustomerOrderStatus }) {
  const db = await getDb();
  if (!db) throw new Error("قاعدة البيانات غير متاحة حالياً.");
  const order = (await db.select().from(serviceOrders).where(eq(serviceOrders.id, input.orderId)).limit(1))[0] ?? null;
  if (!order) throw new Error("الطلب المطلوب غير موجود.");
  const currentStatus = order.status as CustomerOrderStatus;
  if (!canMoveCustomerOrderStatus(currentStatus, input.status)) {
    throw new Error("لا يمكن تجاوز مراحل الطلب. استخدم المرحلة التالية بالترتيب.");
  }
  await db.update(serviceOrders).set({ status: input.status }).where(eq(serviceOrders.id, input.orderId));
  const statusCopy: Record<CustomerOrderStatus, { titleAr: string; titleEn: string; bodyAr: string; bodyEn: string }> = {
    received: { titleAr: "تم استلام طلبك", titleEn: "Your request was received", bodyAr: "تم استلام الطلب وهو بانتظار مراجعة الإدارة.", bodyEn: "Your request was received and is awaiting administration review." },
    confirmed: { titleAr: "تم تأكيد طلبك", titleEn: "Your request was confirmed", bodyAr: "راجعت الإدارة الطلب وتم تأكيده. سنطلعك على الخطوة التالية.", bodyEn: "Administration reviewed and confirmed your request. We will keep you updated on the next step." },
    on_the_way: { titleAr: "فريق التنفيذ في الطريق", titleEn: "The service team is on the way", bodyAr: "حدّثت الإدارة حالة الطلب إلى: الفريق في الطريق إلى موقع الخدمة.", bodyEn: "Administration updated your request: the service team is on the way to the service location." },
    in_progress: { titleAr: "بدأ تنفيذ الخدمة", titleEn: "Service work has started", bodyAr: "حدّثت الإدارة حالة الطلب إلى: جاري التنفيذ.", bodyEn: "Administration updated your request: service work is in progress." },
    completed: { titleAr: "اكتملت الخدمة", titleEn: "Service completed", bodyAr: "تمت مراجعة الخدمة وتحديث طلبك إلى مكتمل.", bodyEn: "The service was reviewed and your request is now marked completed." },
    cancelled: { titleAr: "تم إلغاء الطلب", titleEn: "Request cancelled", bodyAr: "تم تحديث حالة الطلب إلى ملغى. يمكن لفريق الإدارة توضيح التفاصيل عند الحاجة.", bodyEn: "Your request was marked cancelled. The administration team can clarify the details if needed." },
  };
  const copy = statusCopy[input.status];
  await createCustomerNotification({ customerId: order.customerId, category: "orders", ...copy, icon: input.status === "completed" ? "verified" : "assignment", deepLink: `/request/${order.id}` });
  return (await db.select().from(serviceOrders).where(eq(serviceOrders.id, input.orderId)).limit(1))[0] ?? null;
}

export async function createCustomerOrder(phone: string, order: Omit<InsertServiceOrder, "customerId">) {
  const db = await getDb();
  const customer = await ensureCustomer(phone);
  if (!db || !customer) return null;
  const result = await db.insert(serviceOrders).values({ ...order, customerId: customer.id });
  const id = Number(result[0].insertId);
  await createCustomerNotification({
    customerId: customer.id,
    category: "orders",
    titleAr: "تم استلام طلبك",
    titleEn: "Your request was received",
    bodyAr: `تم استلام طلب ${order.serviceTitle} بنجاح. سنراجع التفاصيل ونحدّثك بالخطوة التالية.`,
    bodyEn: `Your ${order.serviceTitle} request was received successfully. We will review the details and update you with the next step.`,
    icon: "assignment",
    deepLink: `/request/${id}`,
  });
  const rows = await db.select().from(serviceOrders).where(eq(serviceOrders.id, id)).limit(1);
  return rows[0] ?? null;
}

async function getCustomerForPhone(phone: string) {
  const customer = await ensureCustomer(phone);
  if (!customer) throw new Error("تعذر الوصول إلى بيانات العميل.");
  return customer;
}

async function assertCustomerOrder(phone: string, orderId: number) {
  const db = await getDb();
  const customer = await getCustomerForPhone(phone);
  if (!db) return { db: null, customer, order: null };
  const rows = await db.select().from(serviceOrders).where(and(eq(serviceOrders.id, orderId), eq(serviceOrders.customerId, customer.id))).limit(1);
  return { db, customer, order: rows[0] ?? null };
}

export async function getCustomerOrderFinancials(phone: string, orderId: number) {
  const { db, customer, order } = await assertCustomerOrder(phone, orderId);
  if (!db || !order) return null;
  const quotes = await db.select().from(orderQuotes).where(eq(orderQuotes.orderId, orderId)).orderBy(desc(orderQuotes.createdAt));
  const quote = quotes[0] ?? null;
  const [items, contracts] = quote ? await Promise.all([
    db.select().from(quoteItems).where(eq(quoteItems.quoteId, quote.id)),
    db.select().from(orderContracts).where(eq(orderContracts.quoteId, quote.id)).orderBy(desc(orderContracts.createdAt)),
  ]) : [[], []];
  const contract = contracts[0] ?? null;
  const location = (await db.select().from(customerLocations).where(and(eq(customerLocations.id, order.locationId), eq(customerLocations.customerId, customer.id))).limit(1))[0] ?? null;
  const contractSections = await db.select().from(contractTemplateSections).where(eq(contractTemplateSections.isActive, true)).orderBy(asc(contractTemplateSections.sortOrder));
  const payments = await db.select().from(orderPayments).where(eq(orderPayments.orderId, orderId)).orderBy(desc(orderPayments.createdAt));
  const invoices = await db.select().from(orderInvoices).where(eq(orderInvoices.orderId, orderId)).orderBy(desc(orderInvoices.createdAt));
  return { order, customer, location, quote, contract, contractSections, items, payments, invoices };
}

export async function acceptCustomerQuote(phone: string, input: { orderId: number; quoteId: number }) {
  const { db, customer, order } = await assertCustomerOrder(phone, input.orderId);
  if (!db || !order) throw new Error("الطلب غير موجود أو لا يخص هذا العميل.");
  const quote = (await db.select().from(orderQuotes).where(and(eq(orderQuotes.id, input.quoteId), eq(orderQuotes.orderId, input.orderId))).limit(1))[0] ?? null;
  if (!quote || quote.status !== "sent") throw new Error("لا يوجد عرض سعر معتمد متاح للقبول حالياً.");
  if (quote.expiresAt && quote.expiresAt.getTime() < Date.now()) throw new Error("انتهت صلاحية عرض السعر. تواصل مع الإدارة لتحديثه.");
  const contract = (await db.select().from(orderContracts).where(and(eq(orderContracts.quoteId, quote.id), eq(orderContracts.orderId, input.orderId))).limit(1))[0] ?? null;
  if (!contract || contract.status !== "pending") throw new Error("العقد غير متاح للقبول حالياً. تواصل مع الإدارة.");
  if (!contract.documentUrl) throw new Error("لم تُرفق الإدارة وثيقة العقد بعد. لا يمكن قبول العرض قبل مراجعتها.");
  const now = new Date();
  await db.update(orderQuotes).set({ status: "accepted", acceptedAt: now }).where(eq(orderQuotes.id, quote.id));
  await db.update(orderContracts).set({ status: "accepted", acceptedAt: now }).where(eq(orderContracts.id, contract.id));
  const existingDeposit = (await db.select({ id: orderPayments.id }).from(orderPayments).where(and(eq(orderPayments.orderId, input.orderId), eq(orderPayments.kind, "deposit"))).limit(1))[0] ?? null;
  if (!existingDeposit && quote.depositAmountFils > 0) {
    await db.insert(orderPayments).values({ orderId: input.orderId, kind: "deposit", status: "pending", method: "transfer", amountFils: quote.depositAmountFils });
  }
  await createCustomerNotification({ customerId: customer.id, category: "payments", titleAr: "تم قبول عرض السعر", titleEn: "Quote accepted", bodyAr: "تم حفظ موافقتك على عرض السعر والعقد. ستظهر لك تعليمات الدفعة عند إعدادها.", bodyEn: "Your acceptance of the quote and contract was saved. Payment instructions will appear once they are prepared.", icon: "description", deepLink: `/request/${input.orderId}/payment` });
  return getCustomerOrderFinancials(phone, input.orderId);
}

export async function createAdminQuote(input: {
  orderId: number;
  adminId: number;
  totalAmountFils: number;
  depositAmountFils: number;
  notes?: string;
  expiresAt?: Date;
  contractDocumentUrl?: string;
  items: Array<{ titleAr: string; titleEn: string; description?: string; quantity: number; unitAmountFils: number }>;
}) {
  const db = await getDb();
  if (!db) throw new Error("قاعدة البيانات غير متاحة حالياً.");
  const order = (await db.select().from(serviceOrders).where(eq(serviceOrders.id, input.orderId)).limit(1))[0] ?? null;
  if (!order) throw new Error("الطلب المطلوب غير موجود.");
  if (order.status !== "received" && order.status !== "confirmed") throw new Error("يمكن إعداد عرض السعر للطلبات المستلمة أو المؤكدة فقط.");
  if (!input.items.length) throw new Error("أضف بنداً واحداً على الأقل إلى عرض السعر.");
  if (input.totalAmountFils <= 0 || input.depositAmountFils < 0 || input.depositAmountFils > input.totalAmountFils) throw new Error("تحقق من إجمالي العرض وقيمة الدفعة الأولى.");
  const computedAmountFils = input.items.reduce((total, item) => total + item.quantity * item.unitAmountFils, 0);
  if (computedAmountFils !== input.totalAmountFils) throw new Error("يجب أن يطابق إجمالي العرض مجموع بنوده الفعلية.");
  const existing = await db.select({ id: orderQuotes.id }).from(orderQuotes).where(and(eq(orderQuotes.orderId, input.orderId), inArray(orderQuotes.status, ["draft", "sent", "accepted"]))).limit(1);
  if (existing[0]) throw new Error("يوجد عرض سعر نشط لهذا الطلب. ألغِه أو أنشئ نسخة جديدة وفق سياسة الإدارة أولاً.");
  const result = await db.insert(orderQuotes).values({ orderId: input.orderId, status: "sent", totalAmountFils: input.totalAmountFils, depositAmountFils: input.depositAmountFils, payableAmountFils: input.totalAmountFils, discountFils: 0, notes: input.notes, expiresAt: input.expiresAt });
  const quoteId = Number(result[0].insertId);
  await db.insert(quoteItems).values(input.items.map((item) => ({ quoteId, title: item.titleAr, titleAr: item.titleAr, titleEn: item.titleEn, description: item.description, quantity: item.quantity, unitAmountFils: item.unitAmountFils })));
  await db.insert(orderContracts).values({ orderId: input.orderId, quoteId, status: "pending", documentUrl: input.contractDocumentUrl || "internal://etqan-service-contract-v1" });
  await createCustomerNotification({ customerId: order.customerId, category: "payments", titleAr: "وصل عرض سعر جديد", titleEn: "A new quote is available", bodyAr: "أرسلت الإدارة عرض سعر وعقداً لطلبك. راجعهما قبل قبول العرض.", bodyEn: "Administration sent a quote and contract for your request. Review both before accepting the quote.", icon: "description", deepLink: `/request/${input.orderId}/quote` });
  return getAdminOrderQuote(input.orderId);
}

export async function getAdminOrderQuote(orderId: number) {
  const db = await getDb();
  if (!db) return null;
  const order = (await db.select().from(serviceOrders).where(eq(serviceOrders.id, orderId)).limit(1))[0] ?? null;
  if (!order) return null;
  const quote = (await db.select().from(orderQuotes).where(eq(orderQuotes.orderId, orderId)).orderBy(desc(orderQuotes.createdAt)).limit(1))[0] ?? null;
  const items = quote ? await db.select().from(quoteItems).where(eq(quoteItems.quoteId, quote.id)) : [];
  const contract = quote ? (await db.select().from(orderContracts).where(eq(orderContracts.quoteId, quote.id)).limit(1))[0] ?? null : null;
  return { order, quote, items, contract };
}

export async function saveOrderRating(phone: string, input: { orderId: number; stars: number; comment?: string }) {
  const { db, customer, order } = await assertCustomerOrder(phone, input.orderId);
  if (!db || !order) throw new Error("الطلب غير موجود أو لا يخص هذا العميل.");
  const exists = await db.select({ id: orderRatings.id }).from(orderRatings).where(eq(orderRatings.orderId, input.orderId)).limit(1);
  if (exists[0]) throw new Error("تم تسجيل تقييم لهذا الطلب مسبقاً.");
  const result = await db.insert(orderRatings).values({ orderId: input.orderId, customerId: customer.id, stars: input.stars, comment: input.comment });
  const rows = await db.select().from(orderRatings).where(eq(orderRatings.id, Number(result[0].insertId))).limit(1);
  return rows[0] ?? null;
}

export async function getOrCreateSupportConversation(phone: string) {
  const db = await getDb();
  const customer = await getCustomerForPhone(phone);
  if (!db) return null;
  const existing = await db.select().from(supportConversations).where(and(eq(supportConversations.customerId, customer.id), eq(supportConversations.status, "ai_active"))).orderBy(desc(supportConversations.updatedAt)).limit(1);
  if (existing[0]) return existing[0];
  const humanOpen = await db.select().from(supportConversations).where(and(eq(supportConversations.customerId, customer.id), eq(supportConversations.status, "human_active"))).orderBy(desc(supportConversations.updatedAt)).limit(1);
  if (humanOpen[0]) return humanOpen[0];
  const result = await db.insert(supportConversations).values({ customerId: customer.id, status: "ai_active", subject: "دعم بوابة الإتقان" });
  const rows = await db.select().from(supportConversations).where(eq(supportConversations.id, Number(result[0].insertId))).limit(1);
  return rows[0] ?? null;
}

export async function getSupportConversation(phone: string, conversationId?: number) {
  const db = await getDb();
  const customer = await getCustomerForPhone(phone);
  if (!db) return null;
  const conversation = conversationId
    ? (await db.select().from(supportConversations).where(and(eq(supportConversations.id, conversationId), eq(supportConversations.customerId, customer.id))).limit(1))[0] ?? null
    : await getOrCreateSupportConversation(phone);
  if (!conversation) return null;
  const messages = await db.select().from(supportMessages).where(eq(supportMessages.conversationId, conversation.id)).orderBy(supportMessages.createdAt);
  return { conversation, messages };
}

export async function listSupportQuickPrompts() {
  const db = await getDb();
  if (!db) return [];
  return db.select().from(supportQuickPrompts).where(eq(supportQuickPrompts.isActive, true)).orderBy(supportQuickPrompts.sortOrder);
}

export async function getSupportAgentContext(phone: string, conversationId: number) {
  const db = await getDb();
  const customer = await getCustomerForPhone(phone);
  if (!db) return null;
  const conversation = (await db.select({ id: supportConversations.id, status: supportConversations.status }).from(supportConversations).where(and(eq(supportConversations.id, conversationId), eq(supportConversations.customerId, customer.id))).limit(1))[0] ?? null;
  if (!conversation || conversation.status !== "ai_active") return null;
  const [knowledge, orders, messages] = await Promise.all([
    db.select().from(supportKnowledgeArticles).where(eq(supportKnowledgeArticles.isActive, true)).orderBy(supportKnowledgeArticles.sortOrder).limit(24),
    db.select({ id: serviceOrders.id, serviceCode: serviceOrders.serviceCode, serviceTitle: serviceOrders.serviceTitle, status: serviceOrders.status, preferredDate: serviceOrders.preferredDate, createdAt: serviceOrders.createdAt }).from(serviceOrders).where(eq(serviceOrders.customerId, customer.id)).orderBy(desc(serviceOrders.createdAt)).limit(6),
    db.select({ author: supportMessages.author, body: supportMessages.body, createdAt: supportMessages.createdAt }).from(supportMessages).where(eq(supportMessages.conversationId, conversationId)).orderBy(desc(supportMessages.createdAt)).limit(8),
  ]);
  return { knowledge, orders, recentMessages: messages.reverse() };
}

export async function createSupportMessage(phone: string, body: string) {
  const db = await getDb();
  const conversation = await getOrCreateSupportConversation(phone);
  if (!db || !conversation) return null;
  const result = await db.insert(supportMessages).values({ conversationId: conversation.id, author: "customer", body });
  await db.update(supportConversations).set({ updatedAt: new Date() }).where(eq(supportConversations.id, conversation.id));
  const rows = await db.select().from(supportMessages).where(eq(supportMessages.id, Number(result[0].insertId))).limit(1);
  return { conversation, message: rows[0] ?? null };
}

export async function createAssistantSupportMessage(conversationId: number, body: string) {
  const db = await getDb();
  if (!db) return null;
  const conversation = (await db.select().from(supportConversations).where(eq(supportConversations.id, conversationId)).limit(1))[0] ?? null;
  if (!conversation || conversation.status !== "ai_active") return null;
  const result = await db.insert(supportMessages).values({ conversationId, author: "assistant", body });
  await db.update(supportConversations).set({ updatedAt: new Date() }).where(eq(supportConversations.id, conversationId));
  const rows = await db.select().from(supportMessages).where(eq(supportMessages.id, Number(result[0].insertId))).limit(1);
  return rows[0] ?? null;
}

export async function takeOverSupportConversation(conversationId: number, adminId?: number) {
  const db = await getDb();
  if (!db) return null;
  await db.update(supportConversations).set({ status: "human_active", assignedAdminId: adminId ?? null, updatedAt: new Date() }).where(eq(supportConversations.id, conversationId));
  await db.insert(supportMessages).values({ conversationId, author: "system", body: "تولى فريق الإدارة المحادثة. سيتوقف الرد التلقائي الآن." });
  const rows = await db.select().from(supportConversations).where(eq(supportConversations.id, conversationId)).limit(1);
  return rows[0] ?? null;
}

export async function getAppContent(section?: string) {
  const db = await getDb();
  if (!db) return [];
  return section
    ? db.select().from(contentEntries).where(eq(contentEntries.section, section)).orderBy(contentEntries.contentKey)
    : db.select().from(contentEntries).orderBy(contentEntries.section, contentEntries.contentKey);
}

export async function getActiveCategories() {
  const db = await getDb();
  if (!db) return [];
  return db.select().from(serviceCategories).where(eq(serviceCategories.isActive, true)).orderBy(serviceCategories.sortOrder);
}

export async function getActiveServices(featuredOnly = false) {
  const db = await getDb();
  if (!db) return [];
  return featuredOnly
    ? db.select().from(serviceCatalog).where(and(eq(serviceCatalog.isActive, true), eq(serviceCatalog.isFeatured, true))).orderBy(serviceCatalog.sortOrder)
    : db.select().from(serviceCatalog).where(eq(serviceCatalog.isActive, true)).orderBy(serviceCatalog.sortOrder);
}

export async function getActivePromotions() {
  const db = await getDb();
  if (!db) return [];
  return db.select().from(promotions).where(eq(promotions.isActive, true)).orderBy(promotions.sortOrder);
}

export async function getCustomerNotifications(phone: string) {
  const db = await getDb();
  const customer = await ensureCustomer(phone);
  if (!db || !customer) return [];
  return db.select().from(appNotifications).where(eq(appNotifications.customerId, customer.id)).orderBy(desc(appNotifications.createdAt));
}

export async function markCustomerNotificationsRead(phone: string) {
  const db = await getDb();
  const customer = await ensureCustomer(phone);
  if (!db || !customer) return;
  await db.update(appNotifications).set({ isRead: true }).where(eq(appNotifications.customerId, customer.id));
}

export async function markCustomerNotificationRead(phone: string, notificationId: number) {
  const db = await getDb();
  const customer = await ensureCustomer(phone);
  if (!db || !customer) return false;
  const result = await db.update(appNotifications).set({ isRead: true }).where(and(eq(appNotifications.id, notificationId), eq(appNotifications.customerId, customer.id)));
  return Number(result[0].affectedRows) > 0;
}

export async function getCustomerUnreadNotificationCount(phone: string) {
  const db = await getDb();
  const customer = await ensureCustomer(phone);
  if (!db || !customer) return 0;
  const result = await db.select({ total: count() }).from(appNotifications).where(and(eq(appNotifications.customerId, customer.id), eq(appNotifications.isRead, false)));
  return Number(result[0]?.total ?? 0);
}

export async function registerCustomerDeviceToken(phone: string, input: { expoPushToken: string; platform: "ios" | "android"; preferredLanguage: "ar" | "en"; permissionStatus: string }) {
  const db = await getDb();
  const customer = await ensureCustomer(phone);
  if (!db || !customer) return null;
  await db.insert(customerDeviceTokens).values({ customerId: customer.id, ...input }).onDuplicateKeyUpdate({
    set: { customerId: customer.id, platform: input.platform, preferredLanguage: input.preferredLanguage, permissionStatus: input.permissionStatus, lastSeenAt: new Date() },
  });
  const rows = await db.select().from(customerDeviceTokens).where(eq(customerDeviceTokens.expoPushToken, input.expoPushToken)).limit(1);
  return rows[0] ?? null;
}

export async function getCustomerDevices(phone: string) {
  const db = await getDb();
  const customer = await ensureCustomer(phone);
  if (!db || !customer) return [];
  return db.select({ id: customerDeviceTokens.id, platform: customerDeviceTokens.platform, preferredLanguage: customerDeviceTokens.preferredLanguage, permissionStatus: customerDeviceTokens.permissionStatus, lastSeenAt: customerDeviceTokens.lastSeenAt, createdAt: customerDeviceTokens.createdAt }).from(customerDeviceTokens).where(eq(customerDeviceTokens.customerId, customer.id));
}

export async function revokeCustomerDevice(phone: string, deviceId: number) {
  const db = await getDb();
  const customer = await ensureCustomer(phone);
  if (!db || !customer) return false;
  await db.delete(customerDeviceTokens).where(and(eq(customerDeviceTokens.id, deviceId), eq(customerDeviceTokens.customerId, customer.id)));
  return true;
}

type CustomerNotificationPayload = {
  customerId: number;
  category: string;
  titleAr: string;
  titleEn: string;
  bodyAr: string;
  bodyEn: string;
  icon?: string | null;
  imageUrl?: string | null;
  deepLink?: string | null;
};

async function sendExpoPushForNotification(notification: { id: number; customerId: number | null } & Omit<CustomerNotificationPayload, "customerId">) {
  const db = await getDb();
  if (!db || !notification.customerId) return { attempted: 0, accepted: 0, skipped: true };
  const devices = await db.select().from(customerDeviceTokens).where(and(eq(customerDeviceTokens.customerId, notification.customerId), eq(customerDeviceTokens.permissionStatus, "granted")));
  const validDevices = devices.filter((device) => /^(Expo|Exponent)PushToken\[[^\]]+\]$/.test(device.expoPushToken));
  if (!validDevices.length) return { attempted: 0, accepted: 0, skipped: true };
  let accepted = 0;
  for (let start = 0; start < validDevices.length; start += 100) {
    const group = validDevices.slice(start, start + 100);
    const response = await fetch("https://exp.host/--/api/v2/push/send", {
      method: "POST",
      headers: { Accept: "application/json", "Content-Type": "application/json", ...(process.env.EXPO_ACCESS_TOKEN ? { Authorization: `Bearer ${process.env.EXPO_ACCESS_TOKEN}` } : {}) },
      body: JSON.stringify(group.map((device) => ({
        to: device.expoPushToken,
        title: device.preferredLanguage === "en" ? notification.titleEn : notification.titleAr,
        body: device.preferredLanguage === "en" ? notification.bodyEn : notification.bodyAr,
        data: { url: notification.deepLink, notificationId: notification.id },
        channelId: "etqan_updates",
        priority: "high",
        sound: "default",
      }))),
    });
    if (!response.ok) continue;
    const result = await response.json() as { data?: Array<{ status?: string }> };
    accepted += result.data?.filter((ticket) => ticket.status === "ok").length ?? 0;
  }
  return { attempted: validDevices.length, accepted, skipped: false };
}

export async function createCustomerNotification(input: CustomerNotificationPayload) {
  const db = await getDb();
  if (!db) return { notification: null, delivery: { attempted: 0, accepted: 0, skipped: true } };
  const result = await db.insert(appNotifications).values(input);
  const rows = await db.select().from(appNotifications).where(eq(appNotifications.id, Number(result[0].insertId))).limit(1);
  const notification = rows[0] ?? null;
  if (!notification) return { notification: null, delivery: { attempted: 0, accepted: 0, skipped: true } };
  try {
    return { notification, delivery: await sendExpoPushForNotification(notification) };
  } catch {
    return { notification, delivery: { attempted: 0, accepted: 0, skipped: false } };
  }
}

export async function getCustomerNotification(phone: string, notificationId: number) {
  const db = await getDb();
  const customer = await ensureCustomer(phone);
  if (!db || !customer) return null;
  const rows = await db.select().from(appNotifications).where(and(eq(appNotifications.id, notificationId), eq(appNotifications.customerId, customer.id))).limit(1);
  return rows[0] ?? null;
}

export async function listActiveTechnicians() {
  const db = await getDb();
  if (!db) return [];
  return db.select().from(technicianProfiles).where(eq(technicianProfiles.isActive, true)).orderBy(asc(technicianProfiles.fullNameAr));
}

export async function getTechnicianByUserId(userId: number) {
  const db = await getDb();
  if (!db) return null;
  const rows = await db.select().from(technicianProfiles).where(and(eq(technicianProfiles.userId, userId), eq(technicianProfiles.isActive, true))).limit(1);
  return rows[0] ?? null;
}

export async function listTechnicianTeams() {
  const db = await getDb();
  if (!db) return [];
  const teams = await db.select().from(technicianTeams).where(eq(technicianTeams.isActive, true)).orderBy(asc(technicianTeams.titleAr));
  if (!teams.length) return [];
  const memberships = await db.select().from(technicianTeamMembers).where(inArray(technicianTeamMembers.teamId, teams.map((team) => team.id)));
  return teams.map((team) => ({ ...team, memberIds: memberships.filter((member) => member.teamId === team.id).map((member) => member.technicianId) }));
}

async function getTaskWithOrder(taskId: number) {
  const db = await getDb();
  if (!db) return { db: null, task: null, order: null, customer: null, location: null };
  const task = (await db.select().from(fieldTasks).where(eq(fieldTasks.id, taskId)).limit(1))[0] ?? null;
  if (!task) return { db, task: null, order: null, customer: null, location: null };
  const order = (await db.select().from(serviceOrders).where(eq(serviceOrders.id, task.orderId)).limit(1))[0] ?? null;
  const customer = order ? (await db.select().from(customers).where(eq(customers.id, order.customerId)).limit(1))[0] ?? null : null;
  const location = order ? (await db.select().from(customerLocations).where(eq(customerLocations.id, order.locationId)).limit(1))[0] ?? null : null;
  return { db, task, order, customer, location };
}

async function addTaskActivity(taskId: number, eventType: string, body: { ar: string; en: string }, actor?: { userId?: number; technicianId?: number }) {
  const db = await getDb();
  if (!db) return;
  await db.insert(taskActivity).values({ taskId, eventType, bodyAr: body.ar, bodyEn: body.en, actorUserId: actor?.userId, actorTechnicianId: actor?.technicianId });
}

async function notifyTaskCustomer(taskId: number, copy: { titleAr: string; titleEn: string; bodyAr: string; bodyEn: string }, deepLink: string) {
  const { db, order } = await getTaskWithOrder(taskId);
  if (!db || !order) return;
  await createCustomerNotification({ customerId: order.customerId, category: "task", ...copy, icon: "engineering", deepLink });
}

export async function createAdminTask(input: {
  orderId: number; adminId: number; titleAr: string; titleEn: string; instructionsAr?: string; instructionsEn?: string; priority: "low" | "normal" | "high" | "urgent"; scheduledStartAt?: Date; scheduledEndAt?: Date; technicianIds: number[]; primaryTechnicianId?: number;
}) {
  const db = await getDb();
  if (!db) throw new Error("قاعدة البيانات غير متاحة حالياً.");
  const order = (await db.select().from(serviceOrders).where(eq(serviceOrders.id, input.orderId)).limit(1))[0];
  if (!order) throw new Error("الطلب المطلوب غير موجود.");
  if (order.status !== "confirmed") throw new Error("يجب أن تؤكد الإدارة الطلب أولاً قبل تعيين فريق التنفيذ.");
  if (!input.technicianIds.length) throw new Error("اختر فنيًا واحدًا على الأقل لتعيين المهمة.");
  const existing = await db.select({ id: fieldTasks.id }).from(fieldTasks).where(eq(fieldTasks.orderId, input.orderId)).limit(1);
  if (existing[0]) throw new Error("يوجد سجل مهمة مرتبط بهذا الطلب بالفعل.");
  const activeTechnicians = await db.select({ id: technicianProfiles.id }).from(technicianProfiles).where(and(eq(technicianProfiles.isActive, true), inArray(technicianProfiles.id, input.technicianIds)));
  if (activeTechnicians.length !== input.technicianIds.length) throw new Error("أحد الفنيين المختارين غير متاح للتعيين.");
  const result = await db.insert(fieldTasks).values({
    orderId: input.orderId,
    titleAr: input.titleAr,
    titleEn: input.titleEn,
    instructionsAr: input.instructionsAr,
    instructionsEn: input.instructionsEn,
    priority: input.priority,
    status: "offered",
    scheduledStartAt: input.scheduledStartAt,
    scheduledEndAt: input.scheduledEndAt,
    assignedByAdminId: input.adminId,
  });
  const taskId = Number(result[0].insertId);
  await db.insert(taskAssignments).values(input.technicianIds.map((technicianId) => ({ taskId, technicianId, assignedByAdminId: input.adminId, status: "offered" as const, isPrimary: technicianId === (input.primaryTechnicianId ?? input.technicianIds[0]) })));
  await addTaskActivity(taskId, "task_assigned", { ar: "قامت الإدارة بتعيين فريق التنفيذ للمهمة.", en: "The administration assigned an execution team to this task." }, { userId: input.adminId });
  await notifyTaskCustomer(taskId, { titleAr: "تم تعيين فريق لمشروعك", titleEn: "A team was assigned to your project", bodyAr: "تمت مراجعة طلبك وتعيين فريق مختص للتنفيذ.", bodyEn: "Your request was reviewed and a specialist team has been assigned." }, `/request/${input.orderId}`);
  return getAdminTaskDetail(taskId);
}

export async function listAdminTasks(status?: string) {
  const db = await getDb();
  if (!db) return [];
  const tasks = status ? await db.select().from(fieldTasks).where(eq(fieldTasks.status, status as typeof fieldTasks.$inferSelect.status)).orderBy(desc(fieldTasks.createdAt)) : await db.select().from(fieldTasks).orderBy(desc(fieldTasks.createdAt));
  const orderIds = tasks.map((task) => task.orderId);
  const orders = orderIds.length ? await db.select().from(serviceOrders).where(inArray(serviceOrders.id, orderIds)) : [];
  return tasks.map((task) => ({ ...task, order: orders.find((order) => order.id === task.orderId) ?? null }));
}

export async function listAssignableOrders() {
  const db = await getDb();
  if (!db) return [];
  const [orders, assigned] = await Promise.all([
    db.select().from(serviceOrders).where(eq(serviceOrders.status, "confirmed")).orderBy(desc(serviceOrders.createdAt)),
    db.select({ orderId: fieldTasks.orderId }).from(fieldTasks),
  ]);
  const assignedOrderIds = new Set(assigned.map((task) => task.orderId));
  return orders.filter((order) => !assignedOrderIds.has(order.id));
}

export async function getAdminTaskDetail(taskId: number) {
  const { db, task, order, customer, location } = await getTaskWithOrder(taskId);
  if (!db || !task) return null;
  const [assignments, checklist, evidence, parts, partRequests, timeEntries, messages, completionReview, activity] = await Promise.all([
    db.select().from(taskAssignments).where(eq(taskAssignments.taskId, taskId)).orderBy(desc(taskAssignments.isPrimary), asc(taskAssignments.createdAt)),
    db.select().from(taskChecklistItems).where(eq(taskChecklistItems.taskId, taskId)).orderBy(asc(taskChecklistItems.sortOrder)),
    db.select().from(taskEvidence).where(eq(taskEvidence.taskId, taskId)).orderBy(desc(taskEvidence.createdAt)),
    db.select().from(taskParts).where(eq(taskParts.taskId, taskId)).orderBy(desc(taskParts.createdAt)),
    db.select().from(taskPartRequests).where(eq(taskPartRequests.taskId, taskId)).orderBy(desc(taskPartRequests.createdAt)),
    db.select().from(taskTimeEntries).where(eq(taskTimeEntries.taskId, taskId)).orderBy(desc(taskTimeEntries.startedAt)),
    db.select().from(taskMessages).where(eq(taskMessages.taskId, taskId)).orderBy(asc(taskMessages.createdAt)),
    db.select().from(taskCompletionReviews).where(eq(taskCompletionReviews.taskId, taskId)).limit(1),
    db.select().from(taskActivity).where(eq(taskActivity.taskId, taskId)).orderBy(desc(taskActivity.createdAt)),
  ]);
  const technicianIds = assignments.map((assignment) => assignment.technicianId);
  const technicians = technicianIds.length ? await db.select().from(technicianProfiles).where(inArray(technicianProfiles.id, technicianIds)) : [];
  return { task, order, customer, location, assignments: assignments.map((assignment) => ({ ...assignment, technician: technicians.find((technician) => technician.id === assignment.technicianId) ?? null })), checklist, evidence, parts, partRequests, timeEntries, messages, completionReview: completionReview[0] ?? null, activity };
}

async function assertTechnicianTask(userId: number, taskId: number) {
  const db = await getDb();
  const technician = await getTechnicianByUserId(userId);
  if (!db || !technician) throw new Error("لا يوجد ملف فني نشط لهذا الحساب.");
  const assignment = (await db.select().from(taskAssignments).where(and(eq(taskAssignments.taskId, taskId), eq(taskAssignments.technicianId, technician.id))).limit(1))[0] ?? null;
  if (!assignment || assignment.status === "declined" || assignment.status === "revoked") throw new Error("هذه المهمة غير مخصصة لك.");
  return { db, technician, assignment };
}

export async function listTechnicianTasks(userId: number) {
  const db = await getDb();
  const technician = await getTechnicianByUserId(userId);
  if (!db || !technician) return { technician: null, tasks: [] };
  const assignments = await db.select().from(taskAssignments).where(eq(taskAssignments.technicianId, technician.id)).orderBy(desc(taskAssignments.createdAt));
  const taskIds = assignments.filter((assignment) => assignment.status !== "declined" && assignment.status !== "revoked").map((assignment) => assignment.taskId);
  const tasks = taskIds.length ? await db.select().from(fieldTasks).where(inArray(fieldTasks.id, taskIds)).orderBy(asc(fieldTasks.scheduledStartAt)) : [];
  const orderIds = tasks.map((task) => task.orderId);
  const orders = orderIds.length ? await db.select().from(serviceOrders).where(inArray(serviceOrders.id, orderIds)) : [];
  const customerIds = orders.map((order) => order.customerId);
  const locationIds = orders.map((order) => order.locationId);
  const taskCustomers = customerIds.length ? await db.select().from(customers).where(inArray(customers.id, customerIds)) : [];
  const locations = locationIds.length ? await db.select().from(customerLocations).where(inArray(customerLocations.id, locationIds)) : [];
  return { technician, tasks: tasks.map((task) => {
    const order = orders.find((candidate) => candidate.id === task.orderId) ?? null;
    return { ...task, assignment: assignments.find((assignment) => assignment.taskId === task.id) ?? null, order, customer: order ? taskCustomers.find((customer) => customer.id === order.customerId) ?? null : null, location: order ? locations.find((location) => location.id === order.locationId) ?? null : null };
  }) };
}

export async function getTechnicianTaskDetail(userId: number, taskId: number) {
  await assertTechnicianTask(userId, taskId);
  return getAdminTaskDetail(taskId);
}

export async function respondToTaskAssignment(userId: number, input: { taskId: number; accept: boolean }) {
  const { db, technician, assignment } = await assertTechnicianTask(userId, input.taskId);
  if (assignment.status !== "offered") throw new Error("تمت معالجة هذا التعيين مسبقاً.");
  const now = new Date();
  if (!input.accept) {
    await db.update(taskAssignments).set({ status: "declined", declinedAt: now }).where(eq(taskAssignments.id, assignment.id));
    await addTaskActivity(input.taskId, "assignment_declined", { ar: "اعتذر الفني عن تنفيذ المهمة.", en: "The technician declined this task." }, { technicianId: technician.id });
    return getAdminTaskDetail(input.taskId);
  }
  await db.update(taskAssignments).set({ status: "accepted", acceptedAt: now }).where(eq(taskAssignments.id, assignment.id));
  await db.update(fieldTasks).set({ status: "accepted", acceptedAt: now }).where(eq(fieldTasks.id, input.taskId));
  await addTaskActivity(input.taskId, "assignment_accepted", { ar: "قبل الفني المهمة وبدأ الاستعداد للتنفيذ.", en: "The technician accepted the task and is preparing for execution." }, { technicianId: technician.id });
  return getAdminTaskDetail(input.taskId);
}

export async function updateTechnicianTaskStatus(userId: number, input: { taskId: number; status: "en_route" | "on_site" | "in_progress" }) {
  const { db, technician } = await assertTechnicianTask(userId, input.taskId);
  const now = new Date();
  await db.update(fieldTasks).set({ status: input.status, ...(input.status === "in_progress" ? { startedAt: now } : {}) }).where(eq(fieldTasks.id, input.taskId));
  const copy = {
    en_route: { ar: "الفني في الطريق إلى موقع الخدمة.", en: "The technician is on the way to your service location." },
    on_site: { ar: "وصل الفني إلى موقع الخدمة.", en: "The technician has arrived at the service location." },
    in_progress: { ar: "بدأ الفني تنفيذ الخدمة.", en: "The technician has started the service." },
  }[input.status];
  await addTaskActivity(input.taskId, input.status, copy, { technicianId: technician.id });
  return getAdminTaskDetail(input.taskId);
}

export async function toggleTaskChecklistItem(userId: number, input: { taskId: number; itemId: number; completed: boolean }) {
  const { db, technician } = await assertTechnicianTask(userId, input.taskId);
  const item = (await db.select().from(taskChecklistItems).where(and(eq(taskChecklistItems.id, input.itemId), eq(taskChecklistItems.taskId, input.taskId))).limit(1))[0];
  if (!item) throw new Error("عنصر قائمة التحقق غير موجود.");
  await db.update(taskChecklistItems).set({ completedByTechnicianId: input.completed ? technician.id : null, completedAt: input.completed ? new Date() : null }).where(eq(taskChecklistItems.id, input.itemId));
  return getTechnicianTaskDetail(userId, input.taskId);
}

export async function startTaskTimer(userId: number, taskId: number) {
  const { db, technician } = await assertTechnicianTask(userId, taskId);
  const active = (await db.select().from(taskTimeEntries).where(and(eq(taskTimeEntries.taskId, taskId), eq(taskTimeEntries.technicianId, technician.id), isNull(taskTimeEntries.endedAt))).limit(1))[0];
  if (active) return active;
  const result = await db.insert(taskTimeEntries).values({ taskId, technicianId: technician.id, startedAt: new Date() });
  await addTaskActivity(taskId, "timer_started", { ar: "بدأ الفني تسجيل وقت العمل.", en: "The technician started the work timer." }, { technicianId: technician.id });
  return (await db.select().from(taskTimeEntries).where(eq(taskTimeEntries.id, Number(result[0].insertId))).limit(1))[0] ?? null;
}

export async function stopTaskTimer(userId: number, input: { taskId: number; noteAr?: string; noteEn?: string }) {
  const { db, technician } = await assertTechnicianTask(userId, input.taskId);
  const active = (await db.select().from(taskTimeEntries).where(and(eq(taskTimeEntries.taskId, input.taskId), eq(taskTimeEntries.technicianId, technician.id), isNull(taskTimeEntries.endedAt))).orderBy(desc(taskTimeEntries.startedAt)).limit(1))[0];
  if (!active) throw new Error("لا يوجد مؤقت عمل نشط لإيقافه.");
  await db.update(taskTimeEntries).set({ endedAt: new Date(), noteAr: input.noteAr, noteEn: input.noteEn }).where(eq(taskTimeEntries.id, active.id));
  await addTaskActivity(input.taskId, "timer_stopped", { ar: "أوقف الفني تسجيل وقت العمل.", en: "The technician stopped the work timer." }, { technicianId: technician.id });
  return getTechnicianTaskDetail(userId, input.taskId);
}

export async function createTaskEvidence(userId: number, input: { taskId: number; kind: "before" | "during" | "after" | "issue" | "receipt" | "signature"; url: string; captionAr?: string; captionEn?: string }) {
  const { db, technician } = await assertTechnicianTask(userId, input.taskId);
  const result = await db.insert(taskEvidence).values({ ...input, uploadedByTechnicianId: technician.id });
  await addTaskActivity(input.taskId, "evidence_added", { ar: "أضاف الفني توثيقاً للمهمة.", en: "The technician added task evidence." }, { technicianId: technician.id });
  return (await db.select().from(taskEvidence).where(eq(taskEvidence.id, Number(result[0].insertId))).limit(1))[0] ?? null;
}

export async function createTaskPartRequest(userId: number, input: { taskId: number; titleAr: string; titleEn: string; quantity: number; reasonAr?: string; reasonEn?: string; inventoryItemId?: number }) {
  const { db, technician } = await assertTechnicianTask(userId, input.taskId);
  const result = await db.insert(taskPartRequests).values({ ...input, requestedByTechnicianId: technician.id });
  await addTaskActivity(input.taskId, "part_requested", { ar: "طلب الفني قطعة إضافية للمهمة.", en: "The technician requested an additional task part." }, { technicianId: technician.id });
  return (await db.select().from(taskPartRequests).where(eq(taskPartRequests.id, Number(result[0].insertId))).limit(1))[0] ?? null;
}

export async function createTaskMessage(userId: number, input: { taskId: number; bodyAr: string; bodyEn: string; attachmentUrl?: string }) {
  const { db, technician } = await assertTechnicianTask(userId, input.taskId);
  const result = await db.insert(taskMessages).values({ ...input, authorRole: "technician", authorTechnicianId: technician.id, authorUserId: userId });
  return (await db.select().from(taskMessages).where(eq(taskMessages.id, Number(result[0].insertId))).limit(1))[0] ?? null;
}

export async function submitTaskCompletion(userId: number, input: { taskId: number; summaryAr: string; summaryEn: string; technicianSignatureUrl?: string; customerSignatureUrl?: string; customerSignedName?: string }) {
  const { db, technician } = await assertTechnicianTask(userId, input.taskId);
  const requiredItems = await db.select().from(taskChecklistItems).where(and(eq(taskChecklistItems.taskId, input.taskId), eq(taskChecklistItems.isRequired, true)));
  if (requiredItems.some((item) => !item.completedAt)) throw new Error("أكمل عناصر التحقق الإلزامية قبل إرسال المراجعة.");
  await db.insert(taskCompletionReviews).values({ ...input, submittedByTechnicianId: technician.id, status: "submitted" }).onDuplicateKeyUpdate({ set: { ...input, submittedByTechnicianId: technician.id, status: "submitted", reviewedByAdminId: null, reviewedAt: null, rejectionNoteAr: null, rejectionNoteEn: null } });
  await db.update(fieldTasks).set({ status: "pending_review", submittedAt: new Date() }).where(eq(fieldTasks.id, input.taskId));
  await addTaskActivity(input.taskId, "completion_submitted", { ar: "أرسل الفني المهمة إلى الإدارة للمراجعة.", en: "The technician submitted the task to administration for review." }, { technicianId: technician.id });
  await notifyTaskCustomer(input.taskId, { titleAr: "المهمة بانتظار المراجعة", titleEn: "Your task is pending review", bodyAr: "أكمل الفني التنفيذ وأرسل ملخص المهمة للمراجعة النهائية.", bodyEn: "The technician completed the work and submitted the task summary for final review." }, `/request/${(await getTaskWithOrder(input.taskId)).order?.id ?? ""}`);
  return getTechnicianTaskDetail(userId, input.taskId);
}

export async function reviewTaskCompletion(adminId: number, input: { taskId: number; approve: boolean; rejectionNoteAr?: string; rejectionNoteEn?: string }) {
  const { db, task, order } = await getTaskWithOrder(input.taskId);
  if (!db || !task || !order) throw new Error("المهمة غير موجودة.");
  const review = (await db.select().from(taskCompletionReviews).where(eq(taskCompletionReviews.taskId, input.taskId)).limit(1))[0];
  if (!review || review.status !== "submitted") throw new Error("لا توجد مراجعة مكتملة بانتظار اعتماد الإدارة.");
  const now = new Date();
  if (!input.approve) {
    await db.update(taskCompletionReviews).set({ status: "rejected", reviewedByAdminId: adminId, reviewedAt: now, rejectionNoteAr: input.rejectionNoteAr, rejectionNoteEn: input.rejectionNoteEn }).where(eq(taskCompletionReviews.id, review.id));
    await db.update(fieldTasks).set({ status: "in_progress" }).where(eq(fieldTasks.id, input.taskId));
    await addTaskActivity(input.taskId, "completion_rejected", { ar: "أعادت الإدارة المهمة لاستكمالها.", en: "Administration returned the task for further work." }, { userId: adminId });
    return getAdminTaskDetail(input.taskId);
  }
  await db.update(taskCompletionReviews).set({ status: "approved", reviewedByAdminId: adminId, reviewedAt: now }).where(eq(taskCompletionReviews.id, review.id));
  await db.update(fieldTasks).set({ status: "completed", completedAt: now }).where(eq(fieldTasks.id, input.taskId));
  await db.update(serviceOrders).set({ status: "completed" }).where(eq(serviceOrders.id, order.id));
  await db.update(taskAssignments).set({ status: "completed", completedAt: now }).where(eq(taskAssignments.taskId, input.taskId));
  await addTaskActivity(input.taskId, "completion_approved", { ar: "اعتمدت الإدارة إنهاء المهمة.", en: "Administration approved the completed task." }, { userId: adminId });
  await notifyTaskCustomer(input.taskId, { titleAr: "اكتملت خدمتك", titleEn: "Your service is complete", bodyAr: "اعتمدت الإدارة تنفيذ المهمة. نأمل أن نكون عند حسن ظنك.", bodyEn: "Administration approved the completed task. We hope the service met your expectations." }, `/request/${order.id}`);
  return getAdminTaskDetail(input.taskId);
}