import type { CreateProjectInput, Project, ProjectMember, ProjectRole, UpdateProjectInput } from "@vtit-agent-coding/shared";
import { type D1, newId, queryDb } from "./db";

export async function createProject(db: D1, ownerId: string, input: CreateProjectInput): Promise<Project> {
  const id = newId();
  const _now = new Date().toISOString();

  await queryDb(
    db,
    `INSERT INTO projects (
      id, owner_id, code, name, domain, github_url, gitlab_url, gitlab_project_id,
      gitlab_access_token, gitlab_webhook_secret, description, modules, created_at, updated_at
    ) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, NOW(), NOW())`,
    [
      id,
      ownerId,
      input.code,
      input.name,
      input.domain ?? null,
      input.github_url ?? null,
      input.gitlab_url ?? null,
      input.gitlab_project_id ?? null,
      input.gitlab_access_token ?? null,
      input.gitlab_webhook_secret ?? null,
      input.description ?? null,
      input.modules ? JSON.stringify(input.modules) : null,
    ],
  );

  // Auto-assign owner as MAINTAINER member
  const memberId = newId();
  await queryDb(
    db,
    `INSERT INTO project_members (id, project_id, user_id, role, created_at, updated_at)
     VALUES ($1, $2, $3, 'MAINTAINER', NOW(), NOW())`,
    [memberId, id, ownerId],
  );

  const project = await getProject(db, id, ownerId);
  if (!project) throw new Error("Failed to create project");
  return project;
}

export async function getProject(db: D1, id: string, userId: string): Promise<Project | null> {
  const res = await queryDb<Project>(
    db,
    `SELECT p.* FROM projects p
     LEFT JOIN project_members pm ON pm.project_id = p.id AND pm.user_id = $2
     WHERE p.id = $1 AND (p.owner_id = $2 OR pm.user_id IS NOT NULL)`,
    [id, userId],
  );
  return res.rows[0] || null;
}

export async function listProjects(db: D1, userId: string): Promise<(Project & { board_id?: string })[]> {
  const res = await queryDb<Project & { board_id?: string }>(
    db,
    `SELECT DISTINCT p.*, (SELECT id FROM boards WHERE project_id = p.id ORDER BY created_at ASC LIMIT 1) as board_id
     FROM projects p
     LEFT JOIN project_members pm ON pm.project_id = p.id AND pm.user_id = $1
     WHERE p.owner_id = $1 OR pm.user_id IS NOT NULL
     ORDER BY p.updated_at DESC`,
    [userId],
  );
  return res.rows;
}

export async function updateProject(db: D1, id: string, ownerId: string, input: UpdateProjectInput): Promise<Project | null> {
  const sets: string[] = ["updated_at = NOW()"];
  const params: unknown[] = [];
  let idx = 1;

  const fields = [
    "name",
    "code",
    "domain",
    "github_url",
    "gitlab_url",
    "gitlab_project_id",
    "gitlab_access_token",
    "gitlab_webhook_secret",
    "description",
    "modules",
  ] as const;

  for (const field of fields) {
    if (field in input && (input as any)[field] !== undefined) {
      sets.push(`${field} = $${idx++}`);
      let val = (input as any)[field];
      if (field === "modules" && val) val = JSON.stringify(val);
      params.push(val);
    }
  }

  params.push(id);
  params.push(ownerId);

  await queryDb(db, `UPDATE projects SET ${sets.join(", ")} WHERE id = $${idx++} AND owner_id = $${idx}`, params);

  return getProject(db, id, ownerId);
}

export async function deleteProject(db: D1, id: string, ownerId: string): Promise<boolean> {
  const res = await queryDb(db, "DELETE FROM projects WHERE id = $1 AND owner_id = $2", [id, ownerId]);
  return (res.rowCount ?? 0) > 0;
}

export async function listProjectMembers(db: D1, projectId: string): Promise<ProjectMember[]> {
  const res = await queryDb<ProjectMember>(
    db,
    `SELECT pm.*, u.name as user_name, u.email as user_email
     FROM project_members pm
     JOIN "user" u ON u.id = pm.user_id
     WHERE pm.project_id = $1
     ORDER BY pm.created_at ASC`,
    [projectId],
  );
  return res.rows;
}

export async function addProjectMember(db: D1, projectId: string, userId: string, role: ProjectRole): Promise<ProjectMember> {
  const id = newId();
  await queryDb(
    db,
    `INSERT INTO project_members (id, project_id, user_id, role, created_at, updated_at)
     VALUES ($1, $2, $3, $4, NOW(), NOW())
     ON CONFLICT (project_id, user_id) DO UPDATE SET role = $4, updated_at = NOW()`,
    [id, projectId, userId, role],
  );

  const res = await queryDb<ProjectMember>(
    db,
    `SELECT pm.*, u.name as user_name, u.email as user_email
     FROM project_members pm
     JOIN "user" u ON u.id = pm.user_id
     WHERE pm.project_id = $1 AND pm.user_id = $2`,
    [projectId, userId],
  );
  return res.rows[0];
}

export async function removeProjectMember(db: D1, projectId: string, userId: string): Promise<boolean> {
  const res = await queryDb(db, "DELETE FROM project_members WHERE project_id = $1 AND user_id = $2", [projectId, userId]);
  return (res.rowCount ?? 0) > 0;
}

export async function listProjectAgents(db: D1, projectId: string): Promise<any[]> {
  const res = await queryDb(
    db,
    `SELECT a.* FROM agents a
     JOIN project_agents pa ON pa.agent_id = a.id
     WHERE pa.project_id = $1
     ORDER BY a.name ASC`,
    [projectId],
  );
  return res.rows;
}

export async function addProjectAgent(db: D1, projectId: string, agentId: string): Promise<boolean> {
  await queryDb(
    db,
    `INSERT INTO project_agents (project_id, agent_id, created_at)
     VALUES ($1, $2, NOW())
     ON CONFLICT (project_id, agent_id) DO NOTHING`,
    [projectId, agentId],
  );
  return true;
}

export async function removeProjectAgent(db: D1, projectId: string, agentId: string): Promise<boolean> {
  const res = await queryDb(db, "DELETE FROM project_agents WHERE project_id = $1 AND agent_id = $2", [projectId, agentId]);
  return (res.rowCount ?? 0) > 0;
}

/**
 * Get user's role in a project. Returns 'OWNER' if the user owns the project,
 * the membership role if they are a member, or null if they have no access.
 */
export async function getProjectRole(db: D1, projectId: string, userId: string): Promise<ProjectRole | "OWNER" | null> {
  // Check if user is the project owner
  const projectRes = await queryDb<Project>(db, "SELECT * FROM projects WHERE id = $1 AND owner_id = $2", [projectId, userId]);
  if (projectRes.rows.length > 0) return "OWNER";

  // Check membership
  const memberRes = await queryDb<ProjectMember>(db, "SELECT * FROM project_members WHERE project_id = $1 AND user_id = $2", [projectId, userId]);
  if (memberRes.rows.length > 0) return memberRes.rows[0].role;

  return null;
}

/**
 * Search users by email or name (case-insensitive partial match).
 * Returns a limited set of user fields suitable for member invitation UI.
 */
export async function searchUsers(db: D1, query: string, limit = 20): Promise<{ id: string; name: string; email: string; image: string | null }[]> {
  const pattern = `%${query}%`;
  const res = await queryDb<{ id: string; name: string; email: string; image: string | null }>(
    db,
    `SELECT id, name, email, image FROM "user"
     WHERE LOWER(name) LIKE LOWER($1) OR LOWER(email) LIKE LOWER($2)
     ORDER BY name ASC
     LIMIT $3`,
    [pattern, pattern, limit],
  );
  return res.rows;
}