import { db } from '@/lib/db';
import { scripts, scriptVersions, ratings } from '@/lib/db/schema';
import { eq, desc, asc, and, or, like, count, sql } from 'drizzle-orm';
import { generateId, ApiError } from './index';

export interface CreateScriptData {
  name: string;
  description: string;
  content: string;
  compatibleOs: string[];
  categories: string[];
  tags?: string[];
  gitRepositoryUrl?: string;
  authorId: string;
  authorName: string;
  version?: string;
}

export interface UpdateScriptData {
  name?: string;
  description?: string;
  content?: string;
  compatibleOs?: string[];
  categories?: string[];
  tags?: string[];
  gitRepositoryUrl?: string;
  version?: string;
}

export interface ScriptFilters {
  categories?: string[];
  compatibleOs?: string[];
  search?: string;
  authorId?: string;
  isApproved?: boolean;
  sortBy?: 'newest' | 'oldest' | 'popular' | 'rating';
  limit?: number;
  offset?: number;
}

// Create a new script
export async function createScript(data: CreateScriptData) {
  try {
    const scriptId = generateId();
    const now = new Date();

    await db.insert(scripts).values({
      id: scriptId,
      name: data.name,
      description: data.description,
      content: data.content,
      compatibleOs: data.compatibleOs,
      categories: data.categories,
      tags: data.tags || [],
      gitRepositoryUrl: data.gitRepositoryUrl,
      authorId: data.authorId,
      authorName: data.authorName,
      version: data.version || '1.0.0',
      isApproved: false,
      isPublic: true,
      viewCount: 0,
      downloadCount: 0,
      rating: 0,
      ratingCount: 0,
      createdAt: now,
      updatedAt: now,
    });

    const script = {
      id: scriptId,
      name: data.name,
      description: data.description,
      content: data.content,
      compatibleOs: data.compatibleOs,
      categories: data.categories,
      tags: data.tags || [],
      gitRepositoryUrl: data.gitRepositoryUrl,
      authorId: data.authorId,
      authorName: data.authorName,
      version: data.version || '1.0.0',
      isApproved: false,
      isPublic: true,
      viewCount: 0,
      downloadCount: 0,
      rating: 0,
      ratingCount: 0,
      createdAt: now,
      updatedAt: now,
    };

    // Create initial version
    await db.insert(scriptVersions).values({
      id: generateId(),
      scriptId: scriptId,
      version: data.version || '1.0.0',
      content: data.content,
      changelog: 'Initial version',
      createdAt: now,
      createdBy: data.authorId,
    });

    return script;
  } catch (error) {
    throw new ApiError(`Failed to create script: ${error}`, 500);
  }
}

// Get script by ID
export async function getScriptById(id: string) {
  try {
    const script = await db.query.scripts.findFirst({
      where: eq(scripts.id, id),
      with: {
        author: true,
        versions: {
          orderBy: desc(scriptVersions.createdAt),
        },
        ratings: true,
      },
    });

    if (!script) {
      throw new ApiError('Script not found', 404);
    }

    return script;
  } catch (error) {
    if (error instanceof ApiError) throw error;
    throw new ApiError(`Failed to get script: ${error}`, 500);
  }
}

// Get scripts with filters
export async function getScripts(filters: ScriptFilters = {}) {
  try {
    const {
      categories,
      compatibleOs,
      search,
      authorId,
      isApproved = true,
      sortBy = 'newest',
      limit = 20,
      offset = 0,
    } = filters;

    let query = db.select().from(scripts);
    let conditions: any[] = [];

    // Apply filters
    if (isApproved !== undefined) {
      conditions.push(eq(scripts.isApproved, isApproved));
    }

    if (authorId) {
      conditions.push(eq(scripts.authorId, authorId));
    }

    if (search) {
      conditions.push(
        or(
          like(scripts.name, `%${search}%`),
          like(scripts.description, `%${search}%`)
        )
      );
    }

    if (categories && categories.length > 0) {
      conditions.push(
        sql`JSON_OVERLAPS(${scripts.categories}, ${JSON.stringify(categories)})`
      );
    }

    if (compatibleOs && compatibleOs.length > 0) {
      conditions.push(
        sql`JSON_OVERLAPS(${scripts.compatibleOs}, ${JSON.stringify(compatibleOs)})`
      );
    }

    if (conditions.length > 0) {
      query = query.where(and(...conditions)) as any;
    }

    // Apply sorting
    switch (sortBy) {
      case 'newest':
        query = query.orderBy(desc(scripts.createdAt)) as any;
        break;
      case 'oldest':
        query = query.orderBy(asc(scripts.createdAt)) as any;
        break;
      case 'popular':
        query = query.orderBy(desc(scripts.viewCount)) as any;
        break;
      case 'rating':
        query = query.orderBy(desc(scripts.rating)) as any;
        break;
    }

    // Apply pagination
    query = query.limit(limit).offset(offset) as any;

    const results = await query;
    
    // Get total count for pagination
    const [{ total }] = await db
      .select({ total: count() })
      .from(scripts)
      .where(conditions.length > 0 ? and(...conditions) : undefined);

    return {
      scripts: results,
      total,
      hasMore: offset + limit < total,
    };
  } catch (error) {
    throw new ApiError(`Failed to get scripts: ${error}`, 500);
  }
}

// Update script
export async function updateScript(id: string, data: UpdateScriptData, userId: string) {
  try {
    // Check if user owns the script or is admin
    const script = await getScriptById(id);
    if (script.authorId !== userId) {
      throw new ApiError('Unauthorized to update this script', 403);
    }

    const updateData = {
      ...data,
      updatedAt: new Date(),
    };

    await db
      .update(scripts)
      .set(updateData)
      .where(eq(scripts.id, id));

    const updatedScript = { ...script, ...updateData };

    // If content changed, create new version
    if (data.content && data.version) {
      await db.insert(scriptVersions).values({
        id: generateId(),
        scriptId: id,
        version: data.version,
        content: data.content,
        changelog: 'Updated script content',
        createdAt: new Date(),
        createdBy: userId,
      });
    }

    return updatedScript;
  } catch (error) {
    if (error instanceof ApiError) throw error;
    throw new ApiError(`Failed to update script: ${error}`, 500);
  }
}

// Delete script
export async function deleteScript(id: string, userId: string) {
  try {
    const script = await getScriptById(id);
    if (script.authorId !== userId) {
      throw new ApiError('Unauthorized to delete this script', 403);
    }

    // Delete all related data
    await db.delete(scriptVersions).where(eq(scriptVersions.scriptId, id));
    await db.delete(ratings).where(eq(ratings.scriptId, id));
    await db.delete(scripts).where(eq(scripts.id, id));

    return { success: true };
  } catch (error) {
    if (error instanceof ApiError) throw error;
    throw new ApiError(`Failed to delete script: ${error}`, 500);
  }
}

// Approve/reject script (admin only)
export async function moderateScript(id: string, isApproved: boolean, _moderatorId: string) {
  try {
    const script = await getScriptById(id);
    if (!script) {
      throw new ApiError('Script not found', 404);
    }

    await db
      .update(scripts)
      .set({
        isApproved,
        updatedAt: new Date(),
      })
      .where(eq(scripts.id, id));

    const moderatedScript = { ...script, isApproved, updatedAt: new Date() };
    return moderatedScript;
  } catch (error) {
    throw new ApiError(`Failed to moderate script: ${error}`, 500);
  }
}

// Increment view count
export async function incrementViewCount(id: string) {
  try {
    await db
      .update(scripts)
      .set({
        viewCount: sql`${scripts.viewCount} + 1`,
      })
      .where(eq(scripts.id, id));

    return { success: true };
  } catch (error) {
    throw new ApiError(`Failed to increment view count: ${error}`, 500);
  }
}

// Increment download count
export async function incrementDownloadCount(id: string) {
  try {
    await db
      .update(scripts)
      .set({
        downloadCount: sql`${scripts.downloadCount} + 1`,
      })
      .where(eq(scripts.id, id));

    return { success: true };
  } catch (error) {
    throw new ApiError(`Failed to increment download count: ${error}`, 500);
  }
}

// Get popular scripts
export async function getPopularScripts(limit: number = 10) {
  try {
    const popularScripts = await db
      .select()
      .from(scripts)
      .where(eq(scripts.isApproved, true))
      .orderBy(desc(scripts.viewCount))
      .limit(limit);

    return popularScripts;
  } catch (error) {
    throw new ApiError(`Failed to get popular scripts: ${error}`, 500);
  }
}

// Get recent scripts
export async function getRecentScripts(limit: number = 10) {
  try {
    const recentScripts = await db
      .select()
      .from(scripts)
      .where(eq(scripts.isApproved, true))
      .orderBy(desc(scripts.createdAt))
      .limit(limit);

    return recentScripts;
  } catch (error) {
    throw new ApiError(`Failed to get recent scripts: ${error}`, 500);
  }
}