import { randomUUID } from 'node:crypto';
import path from 'node:path';

import { getConnection } from '@/modules/database/connection.js';
import type { CreateProjectPathResult, ProjectRepositoryRow } from '@/shared/types.js';
import { normalizeProjectPath } from '@/shared/utils.js';

function normalizeProjectDisplayName(projectPath: string, customProjectName: string | null): string {
    const trimmedCustomName = typeof customProjectName === 'string' ? customProjectName.trim() : '';
    if (trimmedCustomName.length > 0) {
        return trimmedCustomName;
    }

    const directoryName = path.basename(projectPath);
    return directoryName || projectPath;
}

function normalizePreviewPort(port: number | null | undefined): number | null {
  if (port === null || port === undefined) {
    return null;
  }

  if (!Number.isFinite(port)) {
    return null;
  }

  const normalizedPort = Math.floor(port);
  if (normalizedPort < 1 || normalizedPort > 65535) {
    return null;
  }

  return normalizedPort;
}

export const projectsDb = {
    createProjectPath(
        userId: number,
        projectPath: string,
        customProjectName: string | null = null,
        previewProdPort: number | null = null,
        previewDevPort: number | null = null,
    ): CreateProjectPathResult {
        const db = getConnection();
        const normalizedProjectPath = normalizeProjectPath(projectPath);
        const normalizedProjectName = normalizeProjectDisplayName(normalizedProjectPath, customProjectName);
        const attemptedId = randomUUID();
        const row = db.prepare(`
            INSERT INTO projects (
            project_id,
            user_id,
            project_path,
            custom_project_name,
            project_host_alias,
            preview_prod_port,
            preview_dev_port,
            isArchived
        )
            VALUES (?, ?, ?, ?, ?, ?, ?, 0)
            ON CONFLICT(user_id, project_path) DO UPDATE SET
            isArchived = 0,
            project_host_alias = COALESCE(excluded.project_host_alias, projects.project_host_alias),
            preview_prod_port = COALESCE(excluded.preview_prod_port, projects.preview_prod_port),
            preview_dev_port = COALESCE(excluded.preview_dev_port, projects.preview_dev_port)
            WHERE projects.isArchived = 1
            RETURNING project_id, project_path, custom_project_name, project_host_alias, preview_prod_port, preview_dev_port, isStarred, isArchived
        `).get(
            attemptedId,
            userId,
            normalizedProjectPath,
            normalizedProjectName,
            null,
            normalizePreviewPort(previewProdPort),
            normalizePreviewPort(previewDevPort),
        ) as ProjectRepositoryRow | undefined;

        if (row) {
            return {
                outcome: row.project_id === attemptedId ? 'created' : 'reactivated_archived',
                project: row,
            };
        }

        const existingProject = projectsDb.getProjectPath(userId, normalizedProjectPath);
        return {
            outcome: 'active_conflict',
            project: existingProject,
        };
    },

    getProjectPath(userId: number, projectPath: string): ProjectRepositoryRow | null {
        const db = getConnection();
        const normalizedProjectPath = normalizeProjectPath(projectPath);
        const row = db.prepare(`
            SELECT project_id, project_path, custom_project_name, project_host_alias, preview_prod_port, preview_dev_port, isStarred, isArchived
            FROM projects
            WHERE user_id = ? AND project_path = ?
        `).get(userId, normalizedProjectPath) as ProjectRepositoryRow | undefined;

        return row ?? null;
    },

    getProjectById(userId: number, projectId: string): ProjectRepositoryRow | null {
        const db = getConnection();
        const row = db.prepare(`
            SELECT project_id, project_path, custom_project_name, project_host_alias, preview_prod_port, preview_dev_port, isStarred, isArchived
            FROM projects
            WHERE user_id = ? AND project_id = ?
        `).get(userId, projectId) as ProjectRepositoryRow | undefined;

        return row ?? null;
    },

    /**
     * Resolve the absolute project directory from a database project_id.
     *
     * This is the canonical lookup used after the projectName → projectId migration:
     * API routes receive the DB-assigned `projectId` and must resolve the real folder
     * path through this helper before touching the filesystem. Returns `null` when the
     * project row does not exist so callers can respond with a 404.
     */
    getProjectPathById(userId: number, projectId: string): string | null {
        const db = getConnection();
        const row = db.prepare(`
            SELECT project_path
            FROM projects
            WHERE user_id = ? AND project_id = ?
        `).get(userId, projectId) as Pick<ProjectRepositoryRow, 'project_path'> | undefined;

        return row?.project_path ?? null;
    },

    getProjectPaths(userId: number): ProjectRepositoryRow[] {
        const db = getConnection();
        return db.prepare(`
            SELECT project_id, project_path, custom_project_name, project_host_alias, preview_prod_port, preview_dev_port, isStarred, isArchived
            FROM projects
            WHERE user_id = ? AND isArchived = 0
        `).all(userId) as ProjectRepositoryRow[];
    },

    getAllProjectPaths(): ProjectRepositoryRow[] {
        const db = getConnection();
        return db.prepare(`
            SELECT project_id, project_path, custom_project_name, project_host_alias, preview_prod_port, preview_dev_port, isStarred, isArchived
            FROM projects
        `).all() as ProjectRepositoryRow[];
    },

    getAllActiveProjectPaths(): ProjectRepositoryRow[] {
        const db = getConnection();
        return db.prepare(`
            SELECT project_id, project_path, custom_project_name, project_host_alias, preview_prod_port, preview_dev_port, isStarred, isArchived
            FROM projects
            WHERE isArchived = 0
        `).all() as ProjectRepositoryRow[];
    },

    /**
     * Archived rows are queried separately so archive-focused UIs can present
     * hidden workspaces without reintroducing them into the active sidebar list.
     */
    getArchivedProjectPaths(userId: number): ProjectRepositoryRow[] {
        const db = getConnection();
        return db.prepare(`
            SELECT project_id, project_path, custom_project_name, project_host_alias, preview_prod_port, preview_dev_port, isStarred, isArchived
            FROM projects
            WHERE user_id = ? AND isArchived = 1
        `).all(userId) as ProjectRepositoryRow[];
    },

    getCustomProjectName(userId: number, projectPath: string): string | null {
        const db = getConnection();
        const normalizedProjectPath = normalizeProjectPath(projectPath);
        const row = db.prepare(`
            SELECT custom_project_name
            FROM projects
            WHERE user_id = ? AND project_path = ?
        `).get(userId, normalizedProjectPath) as Pick<ProjectRepositoryRow, 'custom_project_name'> | undefined;

        return row?.custom_project_name ?? null;
    },

    updateCustomProjectName(userId: number, projectPath: string, customProjectName: string | null): void {
        const db = getConnection();
        const normalizedProjectPath = normalizeProjectPath(projectPath);
        db.prepare(`
            INSERT INTO projects (project_id, user_id, project_path, custom_project_name)
            VALUES (?, ?, ?, ?)
            ON CONFLICT(user_id, project_path) DO UPDATE SET custom_project_name = excluded.custom_project_name
        `).run(randomUUID(), userId, normalizedProjectPath, customProjectName);
    },

    updateCustomProjectNameById(userId: number, projectId: string, customProjectName: string | null): void {
        const db = getConnection();
        db.prepare(`
            UPDATE projects
            SET custom_project_name = ?
            WHERE user_id = ? AND project_id = ?
        `).run(customProjectName, userId, projectId);
    },

    updateProjectPreviewPortsById(userId: number, projectId: string, previewProdPort: number | null, previewDevPort: number | null): void {
        const db = getConnection();
        db.prepare(`
            UPDATE projects
            SET preview_prod_port = ?, preview_dev_port = ?
            WHERE user_id = ? AND project_id = ?
        `).run(normalizePreviewPort(previewProdPort), normalizePreviewPort(previewDevPort), userId, projectId);
    },

    updateProjectRoutingById(
        userId: number,
        projectId: string,
        projectHostAlias: string | null,
        previewProdPort: number | null,
        previewDevPort: number | null,
    ): void {
        const db = getConnection();
        db.prepare(`
            UPDATE projects
            SET project_host_alias = ?, preview_prod_port = ?, preview_dev_port = ?
            WHERE user_id = ? AND project_id = ?
        `).run(
            projectHostAlias,
            normalizePreviewPort(previewProdPort),
            normalizePreviewPort(previewDevPort),
            userId,
            projectId,
        );
    },

    updateProjectIsStarred(userId: number, projectPath: string, isStarred: boolean): void {
        const db = getConnection();
        const normalizedProjectPath = normalizeProjectPath(projectPath);
        db.prepare(`
            UPDATE projects
            SET isStarred = ?
            WHERE user_id = ? AND project_path = ?
        `).run(isStarred ? 1 : 0, userId, normalizedProjectPath);
    },

    updateProjectIsStarredById(userId: number, projectId: string, isStarred: boolean): void {
        const db = getConnection();
        db.prepare(`
            UPDATE projects
            SET isStarred = ?
            WHERE user_id = ? AND project_id = ?
        `).run(isStarred ? 1 : 0, userId, projectId);
    },

    updateProjectIsArchived(userId: number, projectPath: string, isArchived: boolean): void {
        const db = getConnection();
        const normalizedProjectPath = normalizeProjectPath(projectPath);
        db.prepare(`
            UPDATE projects
            SET isArchived = ?
            WHERE user_id = ? AND project_path = ?
        `).run(isArchived ? 1 : 0, userId, normalizedProjectPath);
    },

    updateProjectIsArchivedById(userId: number, projectId: string, isArchived: boolean): void {
        const db = getConnection();
        db.prepare(`
            UPDATE projects
            SET isArchived = ?
            WHERE user_id = ? AND project_id = ?
        `).run(isArchived ? 1 : 0, userId, projectId);
    },

    deleteProjectPath(userId: number, projectPath: string): void {
        const db = getConnection();
        const normalizedProjectPath = normalizeProjectPath(projectPath);
        db.prepare(`
            DELETE FROM projects
            WHERE user_id = ? AND project_path = ?
        `).run(userId, normalizedProjectPath);
    },

    deleteProjectById(userId: number, projectId: string): void {
        const db = getConnection();
        db.prepare(`
            DELETE FROM projects
            WHERE user_id = ? AND project_id = ?
        `).run(userId, projectId);
    },

    isProjectPathUsedByOthers(userId: number, projectPath: string): { used: boolean; usernames: string[] } {
        const db = getConnection();
        const normalizedProjectPath = normalizeProjectPath(projectPath);
        const rows = db.prepare(`
            SELECT u.username FROM projects p
            JOIN users u ON u.id = p.user_id
            WHERE p.project_path = ? AND p.user_id != ? AND p.isArchived = 0 AND u.is_active = 1
        `).all(normalizedProjectPath, userId) as Array<{ username: string }>;
        return {
            used: rows.length > 0,
            usernames: rows.map(r => r.username),
        };
    },
};