نظام CRM صممته لوكالة سيارات، وكيف أغلقت ثغراته.

// One customer, many leads, and every touch recorded as an activity, so
// response times and performance are measured, not guessed.
export type Department = 'telesales' | 'testdrive' | 'aftersales' | 'callcenter' | 'fleet' | 'marketing';
export type Source = 'website' | 'social' | 'sms' | 'email' | 'outdoor' | 'event' | 'callcenter';

export interface Customer {
  id: string;
  phone: string;               // +9665XXXXXXXX: the one key that detects duplicates
  name: string;
  consent: { marketingSms: boolean; marketingWhatsApp: boolean; at: string };
  optedOutAt?: string;
}

export interface Lead {
  id: string;
  customerId: string;
  department: Department;
  inquiryType: string;         // chosen in the form, never guessed when present
  source: Source;
  utm?: { campaign: string; medium: string; content?: string };
  status: 'new' | 'assigned' | 'contacting' | 'qualified' | 'won' | 'lost' | 'nurture';
  temperature?: 'hot' | 'warm' | 'cold' | 'lost';
  ownerId?: string;
  attempts: number;
  lostReason?: string;
  createdAt: string;
  firstContactAt?: string;     // speed to lead is measured from createdAt to here
}

export interface Activity {
  leadId: string;
  agentId: string;
  kind: 'call' | 'whatsapp' | 'sms' | 'visit' | 'test_drive' | 'note';
  outcome: 'answered' | 'no_answer' | 'interested' | 'not_interested' | 'booked';
  at: string;
}

// The same person arrives as 0551234567, 966551234567, +966 55 123 4567 or
// 551234567. One normalised number means one customer record, not four.
export function normalizeSaudiMobile(raw: string): string | null {
  const d = raw.replace(/\D/g, '');
  const local = d.startsWith('966') ? d.slice(3) : d.startsWith('0') ? d.slice(1) : d;
  return /^5\d{8}$/.test(local) ? `+966${local}` : null;
}

export async function findOrCreateCustomer(db: Db, input: { phone: string; name: string }) {
  const phone = normalizeSaudiMobile(input.phone);
  if (!phone) throw new Error('invalid_mobile');
  const existing = await db.customers.findByPhone(phone);
  if (existing) return { customer: existing, isNew: false };
  const customer = await db.customers.insert({ phone, name: input.name.trim() });
  return { customer, isNew: true };
}

def transfer_text_based_on_keywords(input_file, keywords_to_files):
    # Read the contents of the original file
    with open(input_file, 'r', encoding='utf-8') as file:
        text = file.read()

    # Check each set of keywords and transfer the text to the corresponding file if the keywords are found
    for keywords, output_file in keywords_to_files.items():
        if all(keyword in text for keyword in keywords):
            with open(output_file, 'w', encoding='utf-8') as file:
                file.write(text)
            print(f"The text has been successfully transferred to {output_file}!")

# Define the files and the corresponding keywords
keywords_to_files = {
    'TeleSales.txt': ('buy', 'cost', 'value'),
    'AfterSales.txt': ('part', 'maintenance', 'inspection', 'periodic'),
    'Fleet.txt': ('company', 'institution'),
}

# Use the function to transfer texts
transfer_text_based_on_keywords('path/to/input_file.txt', keywords_to_files)

import json
import re
from pathlib import Path

# The form's own "inquiry type" decides. Keywords only suggest a department
# for free text, in Arabic and English, and anything unclear goes to triage.
RULES = {
    "telesales": {"buy", "price", "cost", "offer", "شراء", "سعر", "عرض", "تقسيط"},
    "aftersales": {"part", "parts", "maintenance", "service", "قطع", "صيانة", "فحص"},
    "fleet": {"company", "fleet", "institution", "شركة", "أسطول", "مؤسسة"},
}

def suggest_department(text: str) -> str:
    words = set(re.findall(r"\w+", text.lower()))
    hits = {dept: len(words & keys) for dept, keys in RULES.items()}
    best = max(hits, key=hits.get)
    tied = list(hits.values()).count(hits[best]) > 1
    return "triage" if hits[best] == 0 or tied else best

def route(lead: dict, queue_dir: Path) -> str:
    dept = lead.get("inquiry_type") or suggest_department(lead.get("message", ""))
    # Append, never overwrite: every lead stays in its queue until handled.
    with open(queue_dir / f"{dept}.jsonl", "a", encoding="utf-8") as f:
        f.write(json.dumps(lead, ensure_ascii=False) + "\n")
    return dept

// The form field decides where a lead goes. Free text can only suggest, and a
// lead no rule can place goes to a triage queue that a person checks, so
// nothing is dropped and nothing is overwritten.
const BY_INQUIRY: Record<string, Department> = {
  quote: 'telesales',
  offer: 'telesales',
  test_drive: 'testdrive',
  service_booking: 'aftersales',
  spare_parts: 'aftersales',
  fleet: 'fleet',
  complaint: 'callcenter',
  general: 'callcenter',
};

export function routeLead(inquiryType: string, message = ''): Department | 'triage' {
  return BY_INQUIRY[inquiryType] ?? suggestFromText(message) ?? 'triage';
}

// One rule, with no gaps and no overlaps: the purchase timeframe decides the
// temperature, a confirmed budget or financing separates hot from warm, and a
// lead nobody reached after three attempts is nurtured, not thrown away.
export function classifyLead(timeframe: Timeframe, budgetConfirmed: boolean, answered: boolean): Temperature {
  if (timeframe === 'no') return 'lost';
  if (!answered) return 'cold';
  if (timeframe === 'd30') return budgetConfirmed ? 'hot' : 'warm';
  if (timeframe === 'm3') return 'warm';
  return 'cold';
}

// Days until the next follow-up for each temperature.
export const FOLLOW_UP_DAYS: Record<Temperature, number | null> = {
  hot: 2,     // and the same agent keeps it for 15 days before it is reassessed
  warm: 30,
  cold: 90,
  lost: null, // the reason is recorded; only marketing may re-engage, with consent
};

-- What the design is judged by: how fast a lead is answered, how many are
-- reached, and which source really sells, so the budget follows sales.
SELECT source,
       COUNT(*) AS leads,
       AVG(CASE WHEN first_contact_at IS NOT NULL THEN 1.0 ELSE 0 END) AS contact_rate,
       AVG((julianday(first_contact_at) - julianday(created_at)) * 1440) AS minutes_to_first_contact,
       AVG(CASE WHEN status = 'won' THEN 1.0 ELSE 0 END) AS conversion
FROM leads
WHERE created_at >= date('now', '-30 days')
GROUP BY source
ORDER BY conversion DESC;