import { getDbClient } from '@/storage/database/local-db';
import { createEmptyCanvasState, type CanvasProject, type CanvasProjectState } from '@/lib/canvas-types';
import crypto from 'crypto';

function isRecord(value: unknown): value is Record<string, unknown> {
  return !!value && typeof value === 'object' && !Array.isArray(value);
}

export function normalizeCanvasState(value: unknown): CanvasProjectState {
  if (!isRecord(value)) return createEmptyCanvasState();
  const fallback = createEmptyCanvasState();
  const viewport = isRecord(value.viewport) ? value.viewport : fallback.viewport;
  return {
    nodes: Array.isArray(value.nodes) ? value.nodes as CanvasProjectState['nodes'] : [],
    connections: Array.isArray(value.connections) ? value.connections as CanvasProjectState['connections'] : [],
    groups: Array.isArray(value.groups) ? value.groups as CanvasProjectState['groups'] : [],
    assets: Array.isArray(value.assets) ? value.assets as CanvasProjectState['assets'] : [],
    viewport: {
      x: typeof viewport.x === 'number' ? viewport.x : 0,
      y: typeof viewport.y === 'number' ? viewport.y : 0,
      zoom: typeof viewport.zoom === 'number' ? Math.min(3, Math.max(0.2, viewport.zoom)) : 1,
    },
  };
}

export async function ensureCanvasSchema() {
  const client = await getDbClient();
  try {
    await client.query(`
      CREATE TABLE IF NOT EXISTS canvas_projects (
        id uuid PRIMARY KEY,
        user_id uuid NOT NULL REFERENCES profiles(id) ON DELETE CASCADE,
        title text NOT NULL DEFAULT '未命名画布',
        state jsonb NOT NULL DEFAULT '{"nodes":[],"connections":[],"groups":[],"assets":[],"viewport":{"x":0,"y":0,"zoom":1}}'::jsonb,
        created_at timestamptz NOT NULL DEFAULT now(),
        updated_at timestamptz NOT NULL DEFAULT now()
      )
    `);
    await client.query('CREATE INDEX IF NOT EXISTS idx_canvas_projects_user_updated ON canvas_projects(user_id, updated_at DESC)');
  } finally {
    client.release();
  }
}

function mapProject(row: Record<string, unknown>): CanvasProject {
  return {
    id: String(row.id || ''),
    userId: String(row.user_id || ''),
    title: String(row.title || '未命名画布'),
    state: normalizeCanvasState(row.state),
    createdAt: row.created_at instanceof Date ? row.created_at.toISOString() : String(row.created_at || ''),
    updatedAt: row.updated_at instanceof Date ? row.updated_at.toISOString() : String(row.updated_at || ''),
  };
}

export async function listCanvasProjects(userId: string): Promise<CanvasProject[]> {
  await ensureCanvasSchema();
  const client = await getDbClient();
  try {
    const result = await client.query(
      `SELECT id, user_id, title, state, created_at, updated_at
       FROM canvas_projects
       WHERE user_id = $1
       ORDER BY updated_at DESC
       LIMIT 50`,
      [userId],
    );
    return result.rows.map(mapProject);
  } finally {
    client.release();
  }
}

export async function createCanvasProject(userId: string, title = '未命名画布'): Promise<CanvasProject> {
  await ensureCanvasSchema();
  const client = await getDbClient();
  try {
    const id = crypto.randomUUID();
    const result = await client.query(
      `INSERT INTO canvas_projects (id, user_id, title, state)
       VALUES ($1, $2, $3, $4::jsonb)
       RETURNING id, user_id, title, state, created_at, updated_at`,
      [id, userId, title.trim() || '未命名画布', JSON.stringify(createEmptyCanvasState())],
    );
    return mapProject(result.rows[0]);
  } finally {
    client.release();
  }
}

export async function getCanvasProject(userId: string, id: string): Promise<CanvasProject | null> {
  await ensureCanvasSchema();
  const client = await getDbClient();
  try {
    const result = await client.query(
      `SELECT id, user_id, title, state, created_at, updated_at
       FROM canvas_projects
       WHERE id = $1 AND user_id = $2
       LIMIT 1`,
      [id, userId],
    );
    return result.rows.length > 0 ? mapProject(result.rows[0]) : null;
  } finally {
    client.release();
  }
}

export async function updateCanvasProject(
  userId: string,
  id: string,
  values: { title?: string; state?: CanvasProjectState },
): Promise<CanvasProject | null> {
  await ensureCanvasSchema();
  const client = await getDbClient();
  try {
    const current = await client.query(
      'SELECT title, state FROM canvas_projects WHERE id = $1 AND user_id = $2 LIMIT 1',
      [id, userId],
    );
    if (current.rows.length === 0) return null;

    const title = typeof values.title === 'string' && values.title.trim()
      ? values.title.trim()
      : current.rows[0].title;
    const state = values.state ? normalizeCanvasState(values.state) : normalizeCanvasState(current.rows[0].state);
    const result = await client.query(
      `UPDATE canvas_projects
       SET title = $3, state = $4::jsonb, updated_at = now()
       WHERE id = $1 AND user_id = $2
       RETURNING id, user_id, title, state, created_at, updated_at`,
      [id, userId, title, JSON.stringify(state)],
    );
    return result.rows.length > 0 ? mapProject(result.rows[0]) : null;
  } finally {
    client.release();
  }
}

export async function deleteCanvasProject(userId: string, id: string): Promise<boolean> {
  await ensureCanvasSchema();
  const client = await getDbClient();
  try {
    const result = await client.query(
      'DELETE FROM canvas_projects WHERE id = $1 AND user_id = $2',
      [id, userId],
    );
    return (result.rowCount || 0) > 0;
  } finally {
    client.release();
  }
}
