/**
 * Items Service
 * Handles CRUD operations for game items.
 * Used by both bot commands and web dashboard.
 */

import { items } from "@db/schema";
import { eq, ilike, and, or, count, sql } from "drizzle-orm";
import { DrizzleClient } from "@shared/db/DrizzleClient";
import { withTransaction } from "@/lib/db";
import type { Transaction, ItemUsageData } from "@shared/lib/types";
import type { ItemType } from "@shared/lib/constants";

// --- DTOs ---

export interface CreateItemDTO {
    name: string;
    description?: string | null;
    rarity?: 'C' | 'R' | 'SR' | 'SSR';
    type: 'MATERIAL' | 'CONSUMABLE' | 'EQUIPMENT' | 'QUEST';
    price?: bigint | null;
    iconUrl: string;
    imageUrl: string;
    usageData?: ItemUsageData | null;
}

export interface UpdateItemDTO {
    name?: string;
    description?: string | null;
    rarity?: 'C' | 'R' | 'SR' | 'SSR';
    type?: 'MATERIAL' | 'CONSUMABLE' | 'EQUIPMENT' | 'QUEST';
    price?: bigint | null;
    iconUrl?: string;
    imageUrl?: string;
    usageData?: ItemUsageData | null;
}

export interface ItemFilters {
    search?: string;
    type?: string;
    rarity?: string;
    limit?: number;
    offset?: number;
}

// --- Service ---

export const itemsService = {
    /**
     * Get all items with optional filtering and pagination.
     */
    async getAllItems(filters: ItemFilters = {}) {
        const { search, type, rarity, limit = 100, offset = 0 } = filters;

        // Build conditions array
        const conditions = [];

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

        if (type) {
            conditions.push(eq(items.type, type));
        }

        if (rarity) {
            conditions.push(eq(items.rarity, rarity));
        }

        // Execute query with conditions
        const whereClause = conditions.length > 0 ? and(...conditions) : undefined;

        const [itemsList, totalResult] = await Promise.all([
            DrizzleClient
                .select()
                .from(items)
                .where(whereClause)
                .limit(limit)
                .offset(offset)
                .orderBy(items.id),
            DrizzleClient
                .select({ count: count() })
                .from(items)
                .where(whereClause)
        ]);

        return {
            items: itemsList,
            total: totalResult[0]?.count ?? 0
        };
    },

    /**
     * Get a single item by ID.
     */
    async getItemById(id: number) {
        return await DrizzleClient.query.items.findFirst({
            where: eq(items.id, id)
        });
    },

    /**
     * Get item by name (for uniqueness checks).
     */
    async getItemByName(name: string) {
        return await DrizzleClient.query.items.findFirst({
            where: eq(items.name, name)
        });
    },

    /**
     * Create a new item.
     */
    async createItem(data: CreateItemDTO, tx?: Transaction) {
        return await withTransaction(async (txFn) => {
            const [item] = await txFn.insert(items)
                .values({
                    name: data.name,
                    description: data.description ?? null,
                    rarity: data.rarity ?? 'C',
                    type: data.type,
                    price: data.price ?? null,
                    iconUrl: data.iconUrl,
                    imageUrl: data.imageUrl,
                    usageData: data.usageData ?? {},
                })
                .returning();

            return item;
        }, tx);
    },

    /**
     * Update an existing item.
     */
    async updateItem(id: number, data: UpdateItemDTO, tx?: Transaction) {
        return await withTransaction(async (txFn) => {
            // Build update object dynamically to support partial updates
            const updateData: Record<string, unknown> = {};

            if (data.name !== undefined) updateData.name = data.name;
            if (data.description !== undefined) updateData.description = data.description;
            if (data.rarity !== undefined) updateData.rarity = data.rarity;
            if (data.type !== undefined) updateData.type = data.type;
            if (data.price !== undefined) updateData.price = data.price;
            if (data.iconUrl !== undefined) updateData.iconUrl = data.iconUrl;
            if (data.imageUrl !== undefined) updateData.imageUrl = data.imageUrl;
            if (data.usageData !== undefined) updateData.usageData = data.usageData;

            if (Object.keys(updateData).length === 0) {
                // Nothing to update, just return the existing item
                return await txFn.query.items.findFirst({
                    where: eq(items.id, id)
                });
            }

            const [updatedItem] = await txFn
                .update(items)
                .set(updateData)
                .where(eq(items.id, id))
                .returning();

            return updatedItem;
        }, tx);
    },

    /**
     * Delete an item by ID.
     */
    async deleteItem(id: number, tx?: Transaction) {
        return await withTransaction(async (txFn) => {
            const [deletedItem] = await txFn
                .delete(items)
                .where(eq(items.id, id))
                .returning();

            return deletedItem;
        }, tx);
    },

    /**
     * Check if an item name is already taken (for validation).
     * Optionally exclude a specific ID (for updates).
     */
    async isNameTaken(name: string, excludeId?: number) {
        const existing = await DrizzleClient.query.items.findFirst({
            where: excludeId
                ? and(eq(items.name, name), sql`${items.id} != ${excludeId}`)
                : eq(items.name, name)
        });

        return !!existing;
    },

    /**
     * Get items for autocomplete (search by name, limited results).
     */
    async getItemsAutocomplete(query: string, limit: number = 25) {
        return await DrizzleClient
            .select({
                id: items.id,
                name: items.name,
                rarity: items.rarity,
                iconUrl: items.iconUrl,
            })
            .from(items)
            .where(ilike(items.name, `%${query}%`))
            .limit(limit);
    }
};
