import 'server-only';
import { db } from './db';
import {
  ADS,
  AD_CATS,
  LISTINGS,
  CATS,
  AMEN,
  HOURS,
  type Ad,
  type Listing,
} from './data';

export const DEMO_SELLER_EMAIL = 'demo-seller@darsouk.local';
const AVATAR_COLORS = ['#0E7C66', '#F83332', '#E0922F', '#6C5CE7', '#2C6B5C', '#B97E3C'];
export const avatarColor = (id: number) => AVATAR_COLORS[Math.abs(id) % AVATAR_COLORS.length];

export function getDemoSellerId(): number {
  const row = db.prepare('SELECT id FROM users WHERE email = ?').get(DEMO_SELLER_EMAIL) as
    | { id: number }
    | undefined;
  return row?.id ?? 0;
}

/** Returns the owner user id for an ad: a real user for DB ads, the demo seller for the seed catalogue. */
export function getAdOwnerId(adId: number): number {
  if (adId < 1000) return getDemoSellerId();
  const row = db.prepare('SELECT user_id FROM ads WHERE id = ?').get(adId) as
    | { user_id: number }
    | undefined;
  return row?.user_id ?? 0;
}

export type ReservationRow = {
  id: number;
  user_id: number;
  biz_id: number;
  day_num: number;
  month: string;
  time: string;
  covers: number;
  status: string;
  ref: string;
  created_at: number;
};

export type AdRow = {
  id: number;
  user_id: number;
  title: string;
  cat: string;
  price: string;
  city: string;
  area: string;
  cond: string;
  nego: number;
  phone: string;
  descr: string;
  photos: string;
  status: 'active' | 'paused' | 'sold';
  created_at: number;
  first?: string;
  last?: string;
  account?: string;
  business_name?: string;
};

/* ---------- Businesses (services / reservations catalogue) ---------- */
export const BIZ_ID_BASE = 2000;

export type BusinessRow = {
  id: number;
  owner_id: number;
  slug: string;
  name: string;
  cat: string;
  cat_name: string;
  city: string;
  area: string;
  address: string;
  phone: string;
  descr: string;
  price: string;
  unit: string;
  price_level: string;
  photos: string;
  amenities: string;
  status: string;
  rating: number;
  reviews: number;
  created_at: number;
};

function parseJsonArray(raw: string | null | undefined): string[] {
  if (!raw) return [];
  try {
    const v = JSON.parse(raw);
    return Array.isArray(v) ? v : [];
  } catch {
    return [];
  }
}

function businessRowToListing(row: BusinessRow): Listing {
  const amenityKeys = parseJsonArray(row.amenities);
  return {
    id: row.id,
    slug: row.slug || String(row.id),
    name: row.name,
    cat: row.cat,
    catName: row.cat_name || CATS.find((c) => c.key === row.cat)?.name || 'Commerce',
    area: row.area || row.city,
    city: row.city,
    rating: row.rating || 0,
    reviews: row.reviews || 0,
    price: row.price || '—',
    unit: row.unit || '',
    priceLevel: row.price_level || '€€',
    status: row.status === 'soon' ? 'soon' : 'open',
    tags: AMEN(amenityKeys).map((a) => a.label).slice(0, 3),
    desc: row.descr || 'Nouveau commerce sur Darsouk.',
    address: row.address,
    phone: row.phone,
    hours: HOURS,
    amenities: AMEN(amenityKeys.length ? amenityKeys : ['resa', 'card']),
    verified: true,
    photos: parseJsonArray(row.photos),
    ownerId: row.owner_id,
  };
}

/** All bookable businesses: every pro's live business first, then the seed catalogue. */
export function getAllBusinesses(): Listing[] {
  const rows = db
    .prepare('SELECT * FROM businesses ORDER BY created_at DESC')
    .all() as BusinessRow[];
  return [...rows.map(businessRowToListing), ...LISTINGS];
}

export function getBusinessOwnerId(id: number): number {
  if (id < BIZ_ID_BASE) return 0;
  const r = db.prepare('SELECT owner_id FROM businesses WHERE id = ?').get(id) as
    | { owner_id: number }
    | undefined;
  return r?.owner_id ?? 0;
}

export function getBusinessById(id: number): Listing | undefined {
  if (id < BIZ_ID_BASE) return LISTINGS.find((l) => l.id === id) ?? undefined;
  const row = db.prepare('SELECT * FROM businesses WHERE id = ?').get(id) as BusinessRow | undefined;
  return row ? businessRowToListing(row) : undefined;
}

const PLACEHOLDER_BUSINESS: Listing = {
  id: 0,
  slug: '',
  name: 'Commerce indisponible',
  cat: 'restaurants',
  catName: 'Commerce',
  area: '',
  city: 'Alger',
  rating: 0,
  reviews: 0,
  price: '—',
  unit: '',
  priceLevel: '€€',
  status: 'open',
  tags: [],
  desc: 'Ce commerce n’est plus disponible.',
  hours: HOURS,
  amenities: [],
  verified: false,
  photos: [],
};

/** Resolve a business, never returning undefined (safe for lists referencing deleted businesses). */
export function getBusinessByIdOrFirst(id: number): Listing {
  return getBusinessById(id) ?? PLACEHOLDER_BUSINESS;
}

/* ---------- Notifications ---------- */
export type Notif = {
  id: number;
  type: string;
  title: string;
  body: string;
  link: string;
  read: boolean;
  when: string;
};

export function getNotifications(userId: number, limit = 20): Notif[] {
  const rows = db
    .prepare('SELECT * FROM notifications WHERE user_id = ? ORDER BY created_at DESC LIMIT ?')
    .all(userId, limit) as {
    id: number;
    type: string;
    title: string;
    body: string;
    link: string;
    read: number;
    created_at: number;
  }[];
  return rows.map((r) => ({
    id: r.id,
    type: r.type,
    title: r.title,
    body: r.body,
    link: r.link,
    read: !!r.read,
    when: relativeWhen(r.created_at),
  }));
}

export function getUnreadNotifCount(userId: number): number {
  const row = db
    .prepare('SELECT COUNT(*) AS n FROM notifications WHERE user_id = ? AND read = 0')
    .get(userId) as { n: number };
  return row.n;
}

/* ---------- Reviews ---------- */
const REVIEW_COLORS = ['#0E7C66', '#F83332', '#E0922F', '#6C5CE7', '#2C6B5C', '#B97E3C'];

export type Review = {
  id: number;
  author: string;
  rating: number;
  body: string;
  reply: string;
  when: string;
  color: string;
  mine: boolean;
};

export type ReviewStats = { avg: number; count: number; dist: [number, number][] };

export function getBusinessReviews(businessId: number, viewerId?: number): Review[] {
  const rows = db
    .prepare('SELECT * FROM reviews WHERE business_id = ? ORDER BY created_at DESC')
    .all(businessId) as {
    id: number;
    user_id: number;
    author: string;
    rating: number;
    body: string;
    reply: string;
    created_at: number;
  }[];
  return rows.map((r) => ({
    id: r.id,
    author: r.author || 'Membre Darsouk',
    rating: r.rating,
    body: r.body,
    reply: r.reply,
    when: relativeWhen(r.created_at),
    color: REVIEW_COLORS[r.user_id % REVIEW_COLORS.length],
    mine: viewerId === r.user_id,
  }));
}

export function getReviewStats(businessId: number): ReviewStats {
  const rows = db.prepare('SELECT rating FROM reviews WHERE business_id = ?').all(businessId) as {
    rating: number;
  }[];
  const count = rows.length;
  const dist: [number, number][] = [5, 4, 3, 2, 1].map((s) => [s, rows.filter((r) => r.rating === s).length]);
  const avg = count ? rows.reduce((s, r) => s + r.rating, 0) / count : 0;
  return { avg: Math.round(avg * 10) / 10, count, dist };
}

/** Whether the viewer may post a review: booked there, isn't the owner, hasn't reviewed yet. */
export function canUserReview(userId: number, businessId: number): boolean {
  if (userId <= 0) return false;
  if (getAdOwnerId(businessId) === userId) return false; // not for ads, but harmless
  const owner = businessId >= BIZ_ID_BASE
    ? (db.prepare('SELECT owner_id FROM businesses WHERE id = ?').get(businessId) as { owner_id: number } | undefined)?.owner_id
    : 0;
  if (owner === userId) return false;
  const hasBooked = db
    .prepare('SELECT 1 FROM reservations WHERE user_id = ? AND biz_id = ? LIMIT 1')
    .get(userId, businessId);
  if (!hasBooked) return false;
  const already = db
    .prepare('SELECT 1 FROM reviews WHERE business_id = ? AND user_id = ? LIMIT 1')
    .get(businessId, userId);
  return !already;
}

export type OwnerBusiness = {
  id: number;
  name: string;
  cat: string;
  city: string;
  area: string;
  address: string;
  phone: string;
  descr: string;
  price: string;
  unit: string;
  status: string;
  amenities: string[];
  photos: string[];
};

export function getOwnerBusiness(ownerId: number): OwnerBusiness | null {
  const row = db.prepare('SELECT * FROM businesses WHERE owner_id = ?').get(ownerId) as
    | BusinessRow
    | undefined;
  if (!row) return null;
  return {
    id: row.id,
    name: row.name,
    cat: row.cat,
    city: row.city,
    area: row.area,
    address: row.address,
    phone: row.phone,
    descr: row.descr,
    price: row.price,
    unit: row.unit,
    status: row.status,
    amenities: parseJsonArray(row.amenities),
    photos: parseJsonArray(row.photos),
  };
}

export type ProReservation = {
  id: number;
  bizId: number;
  bizName: string;
  customer: string;
  covers: number;
  day_num: number;
  month: string;
  time: string;
  status: string;
  ref: string;
  created_at: number;
};

/** Reservations made on any business owned by this pro. */
export function getProReservations(ownerId: number): ProReservation[] {
  const rows = db
    .prepare(
      `SELECT r.*, b.name AS biz_name, u.first AS c_first, u.last AS c_last, u.email AS c_email
       FROM reservations r
       JOIN businesses b ON b.id = r.biz_id
       JOIN users u ON u.id = r.user_id
       WHERE b.owner_id = ?
       ORDER BY r.created_at DESC`
    )
    .all(ownerId) as (ReservationRow & {
    biz_name: string;
    c_first: string;
    c_last: string;
    c_email: string;
  })[];
  return rows.map((r) => ({
    id: r.id,
    bizId: r.biz_id,
    bizName: r.biz_name,
    customer: [r.c_first, r.c_last].filter(Boolean).join(' ') || r.c_email,
    covers: r.covers,
    day_num: r.day_num,
    month: r.month,
    time: r.time,
    status: r.status,
    ref: r.ref,
    created_at: r.created_at,
  }));
}

export function parsePhotos(raw: string | null | undefined): string[] {
  if (!raw) return [];
  try {
    const v = JSON.parse(raw);
    return Array.isArray(v) ? v : [];
  } catch {
    return [];
  }
}

export function relativeWhen(ms: number): string {
  const diff = Date.now() - ms;
  const h = Math.floor(diff / 3_600_000);
  if (h < 1) return 'À l’instant';
  if (h < 24) return `il y a ${h}h`;
  const d = Math.floor(h / 24);
  return `il y a ${d}j`;
}

export function getUserReservations(userId: number): ReservationRow[] {
  return db
    .prepare('SELECT * FROM reservations WHERE user_id = ? ORDER BY created_at DESC')
    .all(userId) as ReservationRow[];
}

export function getUserAds(userId: number): AdRow[] {
  return db
    .prepare('SELECT * FROM ads WHERE user_id = ? ORDER BY created_at DESC')
    .all(userId) as AdRow[];
}

export function getUserFavorites(userId: number): string[] {
  const rows = db
    .prepare('SELECT kind, item_id FROM favorites WHERE user_id = ?')
    .all(userId) as { kind: string; item_id: number }[];
  return rows.map((r) => `${r.kind}:${r.item_id}`);
}

function adRowToAd(row: AdRow): Ad {
  const icon = AD_CATS.find((c) => c.key === row.cat)?.icon;
  const sellerName =
    row.account === 'pro' && row.business_name
      ? row.business_name
      : [row.first, row.last].filter(Boolean).join(' ') || 'Membre Darsouk';
  return {
    id: row.id,
    title: row.title,
    cat: row.cat,
    icon,
    price: row.price,
    city: row.city,
    area: row.area || row.city,
    when: relativeWhen(row.created_at),
    cond: row.cond,
    nego: !!row.nego,
    seller: {
      n: sellerName,
      t: row.account === 'pro' ? 'pro' : 'particulier',
      r: 5.0,
      since: String(new Date(row.created_at).getFullYear()),
      ads: 1,
    },
    desc: row.descr,
    photos: parsePhotos(row.photos),
  };
}

export type SellerProfile = {
  name: string;
  type: 'pro' | 'particulier';
  rating: number;
  since: string;
  adsCount: number;
  ads: Ad[];
};

/** Public seller profile resolved from one of their ads (DB owner, or seed catalogue seller). */
export function getSellerProfile(adId: number): SellerProfile | null {
  const ad = getAdById(adId);
  if (!ad) return null;
  let ads: Ad[];
  if (adId >= 1000) {
    const ownerId = getAdOwnerId(adId);
    const rows = db
      .prepare(
        `SELECT a.*, u.first, u.last, u.account, u.business_name
         FROM ads a JOIN users u ON u.id = a.user_id
         WHERE a.user_id = ? AND a.status != 'sold'
         ORDER BY a.created_at DESC`
      )
      .all(ownerId) as AdRow[];
    ads = rows.map(adRowToAd);
  } else {
    ads = ADS.filter((a) => a.seller.n === ad.seller.n);
  }
  return {
    name: ad.seller.n,
    type: ad.seller.t,
    rating: ad.seller.r,
    since: ad.seller.since,
    adsCount: ads.length,
    ads,
  };
}

export type AdEdit = {
  id: number;
  title: string;
  cat: string;
  price: string;
  city: string;
  cond: string;
  nego: boolean;
  phone: string;
  descr: string;
  photos: string[];
};

/** Load an ad owned by the user, shaped for the edit form. */
export function getAdForEdit(id: number, userId: number): AdEdit | null {
  const row = db.prepare('SELECT * FROM ads WHERE id = ? AND user_id = ?').get(id, userId) as
    | AdRow
    | undefined;
  if (!row) return null;
  return {
    id: row.id,
    title: row.title,
    cat: row.cat,
    price: row.price,
    city: row.city,
    cond: row.cond,
    nego: !!row.nego,
    phone: row.phone,
    descr: row.descr,
    photos: parsePhotos(row.photos),
  };
}

/** Public marketplace: every user's live ads (newest first) followed by the seed catalogue. */
export function getAllAds(): Ad[] {
  const rows = db
    .prepare(
      `SELECT a.*, u.first, u.last, u.account, u.business_name
       FROM ads a JOIN users u ON u.id = a.user_id
       WHERE a.status != 'sold'
       ORDER BY a.created_at DESC`
    )
    .all() as AdRow[];
  return [...rows.map(adRowToAd), ...ADS];
}

export function getAdById(id: number): Ad | undefined {
  if (id < 1000) return ADS.find((a) => a.id === id);
  const row = db
    .prepare(
      `SELECT a.*, u.first, u.last, u.account, u.business_name
       FROM ads a JOIN users u ON u.id = a.user_id WHERE a.id = ?`
    )
    .get(id) as AdRow | undefined;
  return row ? adRowToAd(row) : undefined;
}

/* ---------- Messaging ---------- */
export type ThreadMsg = { id: number; mine: boolean; body: string; at: string };
export type Thread = {
  id: number;
  adId: number;
  adRef: string;
  adTitle: string;
  adCat: string;
  adIcon: string;
  adPrice: string;
  otherName: string;
  otherInitial: string;
  otherColor: string;
  role: string;
  time: string;
  preview: string;
  unread: number;
  messages: ThreadMsg[];
};

type ConvRow = {
  id: number;
  ad_ref: string;
  ad_id: number;
  ad_title: string;
  ad_cat: string;
  ad_icon: string;
  ad_price: string;
  buyer_id: number;
  buyer_name: string;
  seller_id: number;
  seller_name: string;
  last_at: number;
  last_body: string | null;
  last_sender: number | null;
  my_read: number;
};

function clock(ms: number): string {
  const d = new Date(ms);
  const today = new Date();
  const sameDay = d.toDateString() === today.toDateString();
  const hm = d.toLocaleTimeString('fr-FR', { hour: '2-digit', minute: '2-digit' });
  if (sameDay) return hm;
  const yest = new Date(today);
  yest.setDate(today.getDate() - 1);
  if (d.toDateString() === yest.toDateString()) return 'Hier · ' + hm;
  return d.toLocaleDateString('fr-FR', { day: '2-digit', month: '2-digit' }) + ' · ' + hm;
}

/** Inbox for a user. role 'seller' restricts to threads where the user is the ad owner (pro view). */
export function getInbox(userId: number, role?: 'seller' | 'buyer'): Thread[] {
  const where =
    role === 'seller' ? 'c.seller_id = @uid' : role === 'buyer' ? 'c.buyer_id = @uid' : '(c.buyer_id = @uid OR c.seller_id = @uid)';
  const rows = db
    .prepare(
      `SELECT c.*,
         (SELECT body FROM messages m WHERE m.conv_id = c.id ORDER BY m.created_at DESC LIMIT 1) AS last_body,
         (SELECT sender_id FROM messages m WHERE m.conv_id = c.id ORDER BY m.created_at DESC LIMIT 1) AS last_sender,
         COALESCE((SELECT last_read_at FROM conv_reads r WHERE r.conv_id = c.id AND r.user_id = @uid), 0) AS my_read
       FROM conversations c
       WHERE ${where}
       ORDER BY c.last_at DESC`
    )
    .all({ uid: userId }) as ConvRow[];

  const msgStmt = db.prepare('SELECT * FROM messages WHERE conv_id = ? ORDER BY created_at ASC');

  return rows.map((c) => {
    const iAmBuyer = c.buyer_id === userId;
    const otherName = iAmBuyer ? c.seller_name : c.buyer_name;
    const otherId = iAmBuyer ? c.seller_id : c.buyer_id;
    const raw = msgStmt.all(c.id) as { id: number; sender_id: number; body: string; created_at: number }[];
    const unread = raw.filter((m) => m.sender_id !== userId && m.created_at > c.my_read).length;
    return {
      id: c.id,
      adId: c.ad_id,
      adRef: c.ad_ref,
      adTitle: c.ad_title,
      adCat: c.ad_cat,
      adIcon: c.ad_icon,
      adPrice: c.ad_price,
      otherName: otherName || 'Utilisateur',
      otherInitial: (otherName || 'U').charAt(0).toUpperCase(),
      otherColor: avatarColor(otherId),
      role: iAmBuyer ? 'Vendeur' : 'Acheteur',
      time: c.last_at ? clock(c.last_at) : '',
      preview: c.last_body ? (c.last_sender === userId ? 'Vous : ' : '') + c.last_body : 'Nouvelle conversation',
      unread,
      messages: raw.map((m) => ({ id: m.id, mine: m.sender_id === userId, body: m.body, at: clock(m.created_at) })),
    };
  });
}

export function getUnreadCount(userId: number, role?: 'seller' | 'buyer'): number {
  return getInbox(userId, role).reduce((s, t) => s + t.unread, 0);
}