import type { Env, SiteSettings } from '../env';
import { all } from '../db/queries';
import { mediaUrl } from '../utils/image';
import { nowSeconds } from '../utils/time';
import { ulid } from '../utils/id';

export interface LinkView {
  id: string;
  name: string;
  url: string;
  avatar: string | null;
  description: string;
  category: string;
  visible?: number;
  sort_order?: number;
}

export async function listPublicLinks(env: Env['Bindings'], settings: SiteSettings): Promise<LinkView[]> {
  const rows = await all<{ id: string; name: string; url: string; avatar: string | null; description: string | null; category: string | null }>(
    env.DB.prepare(`SELECT id, name, url, avatar, description, category FROM wm_links WHERE visible = 1 ORDER BY category ASC, sort_order ASC, created_at DESC`)
  );
  return rows.map((row) => ({
    id: row.id,
    name: row.name,
    url: row.url,
    avatar: row.avatar ? mediaUrl(settings.cdn_base_url, row.avatar, row.avatar) : null,
    description: row.description || '',
    category: row.category || 'default'
  }));
}

export async function listAdminLinks(env: Env['Bindings']): Promise<LinkView[]> {
  const rows = await all<{ id: string; name: string; url: string; avatar: string | null; description: string | null; category: string | null; visible: number; sort_order: number }>(
    env.DB.prepare(`SELECT id, name, url, avatar, description, category, visible, sort_order FROM wm_links ORDER BY category ASC, sort_order ASC, created_at DESC`)
  );
  return rows.map((row) => ({
    id: row.id,
    name: row.name,
    url: row.url,
    avatar: row.avatar,
    description: row.description || '',
    category: row.category || 'default',
    visible: row.visible,
    sort_order: row.sort_order
  }));
}

export async function createLink(env: Env['Bindings'], input: {
  name: string;
  url: string;
  avatar?: string | null;
  description?: string;
  category?: string;
  visible?: boolean;
  sortOrder?: number;
}): Promise<string> {
  const id = ulid();
  await env.DB.prepare(
    `INSERT INTO wm_links(id, name, url, avatar, description, category, visible, sort_order, created_at)
     VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)`
  )
    .bind(id, input.name, input.url, input.avatar || null, input.description || '', input.category || 'default', input.visible === false ? 0 : 1, input.sortOrder || 0, nowSeconds())
    .run();
  return id;
}

export async function updateLink(env: Env['Bindings'], id: string, input: Partial<{
  name: string;
  url: string;
  avatar: string | null;
  description: string;
  category: string;
  visible: boolean;
  sortOrder: number;
}>): Promise<void> {
  await env.DB.prepare(
    `UPDATE wm_links SET
      name = COALESCE(?, name),
      url = COALESCE(?, url),
      avatar = COALESCE(?, avatar),
      description = COALESCE(?, description),
      category = COALESCE(?, category),
      visible = COALESCE(?, visible),
      sort_order = COALESCE(?, sort_order)
     WHERE id = ?`
  )
    .bind(
      input.name ?? null,
      input.url ?? null,
      input.avatar ?? null,
      input.description ?? null,
      input.category ?? null,
      input.visible === undefined ? null : input.visible ? 1 : 0,
      input.sortOrder ?? null,
      id
    )
    .run();
}

export async function deleteLink(env: Env['Bindings'], id: string): Promise<void> {
  await env.DB.prepare('DELETE FROM wm_links WHERE id = ?').bind(id).run();
}
