import { db } from '../db/db_index';
import { coupons, reservedCoupons, users } from '../db/schema';
import { eq, and, like, or, inArray, lt, desc, asc } from 'drizzle-orm';

export interface Coupon {
  id: number;
  couponCode: string;
  isUserBased: boolean;
  discountPercent: string | null;
  flatDiscount: string | null;
  minOrder: string | null;
  productIds: number[] | null;
  maxValue: string | null;
  isApplyForAll: boolean;
  validTill: Date | null;
  maxLimitForUser: number | null;
  exclusiveApply: boolean;
  isInvalidated: boolean;
  createdAt: Date;
  createdBy: number;
}

export async function getAllCoupons(
  cursor?: number,
  limit: number = 50,
  search?: string
): Promise<{ coupons: any[]; hasMore: boolean }> {
  let whereCondition = undefined;
  const conditions = [];
  
  if (cursor) {
    conditions.push(lt(coupons.id, cursor));
  }
  
  if (search && search.trim()) {
    conditions.push(like(coupons.couponCode, `%${search}%`));
  }
  
  if (conditions.length > 0) {
    whereCondition = and(...conditions);
  }
  
  const result = await db.query.coupons.findMany({
    where: whereCondition,
    with: {
      creator: true,
      applicableUsers: {
        with: {
          user: true,
        },
      },
      applicableProducts: {
        with: {
          product: true,
        },
      },
    },
    orderBy: (couponsTable: typeof coupons) => [desc(couponsTable.createdAt)],
    limit: limit + 1,
  });
  
  const hasMore = result.length > limit;
  const couponsList = hasMore ? result.slice(0, limit) : result;
  
  return { coupons: couponsList, hasMore };
}

export async function getCouponById(id: number): Promise<any | null> {
  const result = await db.query.coupons.findFirst({
    where: eq(coupons.id, id),
    with: {
      creator: true,
      applicableUsers: {
        with: {
          user: true,
        },
      },
      applicableProducts: {
        with: {
          product: true,
        },
      },
    },
  });

  return result || null;
}

export async function invalidateCoupon(id: number): Promise<Coupon> {
  const result = await db.update(coupons)
    .set({ isInvalidated: true })
    .where(eq(coupons.id, id))
    .returning();

  return result[0];
}

export interface CouponValidationResult {
  valid: boolean;
  message?: string;
  discountAmount?: number;
  coupon?: Partial<Coupon>;
}

export async function validateCoupon(
  code: string,
  userId: number,
  orderAmount: number
): Promise<CouponValidationResult> {
  const coupon = await db.query.coupons.findFirst({
    where: and(
      eq(coupons.couponCode, code.toUpperCase()),
      eq(coupons.isInvalidated, false)
    ),
  });

  if (!coupon) {
    return { valid: false, message: "Coupon not found or invalidated" };
  }

  // Check expiry date
  if (coupon.validTill && new Date(coupon.validTill) < new Date()) {
    return { valid: false, message: "Coupon has expired" };
  }

  // Check if coupon applies to all users or specific user
  if (!coupon.isApplyForAll && !coupon.isUserBased) {
    return { valid: false, message: "Coupon is not available for use" };
  }

  // Check minimum order amount
  const minOrderValue = coupon.minOrder ? parseFloat(coupon.minOrder) : 0;
  if (minOrderValue > 0 && orderAmount < minOrderValue) {
    return { valid: false, message: `Minimum order amount is ${minOrderValue}` };
  }

  // Calculate discount
  let discountAmount = 0;
  if (coupon.discountPercent) {
    const percent = parseFloat(coupon.discountPercent);
    discountAmount = (orderAmount * percent) / 100;
  } else if (coupon.flatDiscount) {
    discountAmount = parseFloat(coupon.flatDiscount);
  }

  // Apply max value limit
  const maxValueLimit = coupon.maxValue ? parseFloat(coupon.maxValue) : 0;
  if (maxValueLimit > 0 && discountAmount > maxValueLimit) {
    discountAmount = maxValueLimit;
  }

  return {
    valid: true,
    discountAmount,
    coupon: {
      id: coupon.id,
      discountPercent: coupon.discountPercent,
      flatDiscount: coupon.flatDiscount,
      maxValue: coupon.maxValue,
    }
  };
}

export async function getReservedCoupons(
  cursor?: number,
  limit: number = 50,
  search?: string
): Promise<{ coupons: any[]; hasMore: boolean }> {
  let whereCondition = undefined;
  const conditions = [];
  
  if (cursor) {
    conditions.push(lt(reservedCoupons.id, cursor));
  }
  
  if (search && search.trim()) {
    conditions.push(or(
      like(reservedCoupons.secretCode, `%${search}%`),
      like(reservedCoupons.couponCode, `%${search}%`)
    ));
  }
  
  if (conditions.length > 0) {
    whereCondition = and(...conditions);
  }

  const result = await db.query.reservedCoupons.findMany({
    where: whereCondition,
    with: {
      redeemedUser: true,
      creator: true,
    },
    orderBy: (reservedCouponsTable: typeof reservedCoupons) => [desc(reservedCouponsTable.createdAt)],
    limit: limit + 1,
  });

  const hasMore = result.length > limit;
  const couponsList = hasMore ? result.slice(0, limit) : result;

  return { coupons: couponsList, hasMore };
}

export interface UserMiniInfo {
  id: number;
  name: string;
  mobile: string | null;
}

export async function getUsersForCoupon(
  search?: string,
  limit: number = 20,
  offset: number = 0
): Promise<{ users: UserMiniInfo[] }> {
  let whereCondition = undefined;
  if (search && search.trim()) {
    whereCondition = or(
      like(users.name, `%${search}%`),
      like(users.mobile, `%${search}%`)
    );
  }

  const userList = await db.query.users.findMany({
    where: whereCondition,
    columns: {
      id: true,
      name: true,
      mobile: true,
    },
    limit: limit,
    offset: offset,
    orderBy: (usersTable: typeof users) => [asc(usersTable.name)],
  });

  return {
    users: userList.map((user: typeof users.$inferSelect) => ({
      id: user.id,
      name: user.name || 'Unknown',
      mobile: user.mobile,
    }))
  };
}

// ============================================================================
// BATCH 2: Transaction Methods
// ============================================================================

import { couponApplicableUsers, couponApplicableProducts, orders, orderStatus } from '../db/schema';

export interface CreateCouponInput {
  couponCode: string;
  isUserBased: boolean;
  discountPercent?: string;
  flatDiscount?: string;
  minOrder?: string;
  productIds?: number[] | null;
  maxValue?: string;
  isApplyForAll: boolean;
  validTill?: Date;
  maxLimitForUser?: number;
  exclusiveApply: boolean;
  createdBy: number;
}

export async function createCouponWithRelations(
  input: CreateCouponInput,
  applicableUsers?: number[],
  applicableProducts?: number[]
): Promise<Coupon> {
  return await db.transaction(async (tx) => {
    // Create the coupon
    const [coupon] = await tx.insert(coupons).values({
      couponCode: input.couponCode,
      isUserBased: input.isUserBased,
      discountPercent: input.discountPercent,
      flatDiscount: input.flatDiscount,
      minOrder: input.minOrder,
      productIds: input.productIds,
      createdBy: input.createdBy,
      maxValue: input.maxValue,
      isApplyForAll: input.isApplyForAll,
      validTill: input.validTill,
      maxLimitForUser: input.maxLimitForUser,
      exclusiveApply: input.exclusiveApply,
    }).returning();

    // Insert applicable users
    if (applicableUsers && applicableUsers.length > 0) {
      await tx.insert(couponApplicableUsers).values(
        applicableUsers.map(userId => ({
          couponId: coupon.id,
          userId,
        }))
      );
    }

    // Insert applicable products
    if (applicableProducts && applicableProducts.length > 0) {
      await tx.insert(couponApplicableProducts).values(
        applicableProducts.map(productId => ({
          couponId: coupon.id,
          productId,
        }))
      );
    }

    return {
      id: coupon.id,
      couponCode: coupon.couponCode,
      isUserBased: coupon.isUserBased,
      discountPercent: coupon.discountPercent,
      flatDiscount: coupon.flatDiscount,
      minOrder: coupon.minOrder,
      productIds: coupon.productIds,
      maxValue: coupon.maxValue,
      isApplyForAll: coupon.isApplyForAll,
      validTill: coupon.validTill,
      maxLimitForUser: coupon.maxLimitForUser,
      exclusiveApply: coupon.exclusiveApply,
      isInvalidated: coupon.isInvalidated,
      createdAt: coupon.createdAt,
      createdBy: coupon.createdBy,
    };
  });
}

export interface UpdateCouponInput {
  couponCode?: string;
  isUserBased?: boolean;
  discountPercent?: string;
  flatDiscount?: string;
  minOrder?: string;
  productIds?: number[] | null;
  maxValue?: string;
  isApplyForAll?: boolean;
  validTill?: Date | null;
  maxLimitForUser?: number;
  exclusiveApply?: boolean;
  isInvalidated?: boolean;
}

export async function updateCouponWithRelations(
  id: number,
  input: UpdateCouponInput,
  applicableUsers?: number[],
  applicableProducts?: number[]
): Promise<Coupon> {
  return await db.transaction(async (tx) => {
    // Update the coupon
    const [coupon] = await tx.update(coupons)
      .set({
        ...input,
      })
      .where(eq(coupons.id, id))
      .returning();

    // Update applicable users: delete existing and insert new
    if (applicableUsers !== undefined) {
      await tx.delete(couponApplicableUsers).where(eq(couponApplicableUsers.couponId, id));
      if (applicableUsers.length > 0) {
        await tx.insert(couponApplicableUsers).values(
          applicableUsers.map(userId => ({
            couponId: id,
            userId,
          }))
        );
      }
    }

    // Update applicable products: delete existing and insert new
    if (applicableProducts !== undefined) {
      await tx.delete(couponApplicableProducts).where(eq(couponApplicableProducts.couponId, id));
      if (applicableProducts.length > 0) {
        await tx.insert(couponApplicableProducts).values(
          applicableProducts.map(productId => ({
            couponId: id,
            productId,
          }))
        );
      }
    }

    return {
      id: coupon.id,
      couponCode: coupon.couponCode,
      isUserBased: coupon.isUserBased,
      discountPercent: coupon.discountPercent,
      flatDiscount: coupon.flatDiscount,
      minOrder: coupon.minOrder,
      productIds: coupon.productIds,
      maxValue: coupon.maxValue,
      isApplyForAll: coupon.isApplyForAll,
      validTill: coupon.validTill,
      maxLimitForUser: coupon.maxLimitForUser,
      exclusiveApply: coupon.exclusiveApply,
      isInvalidated: coupon.isInvalidated,
      createdAt: coupon.createdAt,
      createdBy: coupon.createdBy,
    };
  });
}

export async function generateCancellationCoupon(
  orderId: number,
  staffUserId: number,
  userId: number,
  orderAmount: number,
  couponCode: string
): Promise<Coupon> {
  return await db.transaction(async (tx) => {
    // Calculate expiry date (30 days from now)
    const expiryDate = new Date();
    expiryDate.setDate(expiryDate.getDate() + 30);

    // Create the coupon
    const [coupon] = await tx.insert(coupons).values({
      couponCode,
      isUserBased: true,
      flatDiscount: orderAmount.toString(),
      minOrder: orderAmount.toString(),
      maxValue: orderAmount.toString(),
      validTill: expiryDate,
      maxLimitForUser: 1,
      createdBy: staffUserId,
      isApplyForAll: false,
    }).returning();

    // Insert applicable users
    await tx.insert(couponApplicableUsers).values({
      couponId: coupon.id,
      userId,
    });

    // Update order_status with refund coupon ID
    await tx.update(orderStatus)
      .set({ refundCouponId: coupon.id })
      .where(eq(orderStatus.orderId, orderId));

    return {
      id: coupon.id,
      couponCode: coupon.couponCode,
      isUserBased: coupon.isUserBased,
      discountPercent: coupon.discountPercent,
      flatDiscount: coupon.flatDiscount,
      minOrder: coupon.minOrder,
      productIds: coupon.productIds,
      maxValue: coupon.maxValue,
      isApplyForAll: coupon.isApplyForAll,
      validTill: coupon.validTill,
      maxLimitForUser: coupon.maxLimitForUser,
      exclusiveApply: coupon.exclusiveApply,
      isInvalidated: coupon.isInvalidated,
      createdAt: coupon.createdAt,
      createdBy: coupon.createdBy,
    };
  });
}

export interface CreateReservedCouponInput {
  secretCode: string;
  couponCode: string;
  discountPercent?: string;
  flatDiscount?: string;
  minOrder?: string;
  productIds?: number[] | null;
  maxValue?: string;
  validTill?: Date;
  maxLimitForUser?: number;
  exclusiveApply: boolean;
  createdBy: number;
}

export async function createReservedCouponWithProducts(
  input: CreateReservedCouponInput,
  applicableProducts?: number[]
): Promise<any> {
  return await db.transaction(async (tx) => {
    const [coupon] = await tx.insert(reservedCoupons).values({
      secretCode: input.secretCode,
      couponCode: input.couponCode,
      discountPercent: input.discountPercent,
      flatDiscount: input.flatDiscount,
      minOrder: input.minOrder,
      productIds: input.productIds,
      maxValue: input.maxValue,
      validTill: input.validTill,
      maxLimitForUser: input.maxLimitForUser,
      exclusiveApply: input.exclusiveApply,
      createdBy: input.createdBy,
    }).returning();

    // Insert applicable products if provided
    if (applicableProducts && applicableProducts.length > 0) {
      await tx.insert(couponApplicableProducts).values(
        applicableProducts.map(productId => ({
          couponId: coupon.id,
          productId,
        }))
      );
    }

    return coupon;
  });
}

export async function getOrCreateUserByMobile(
  mobile: string
): Promise<{ id: number; mobile: string; name: string | null }> {
  return await db.transaction(async (tx) => {
    // Check if user exists
    let user = await tx.query.users.findFirst({
      where: eq(users.mobile, mobile),
    });

    if (!user) {
      // Create new user
      const [newUser] = await tx.insert(users).values({
        name: null,
        email: null,
        mobile,
      }).returning();
      user = newUser;
    }

    return {
      id: user.id,
      mobile: user.mobile,
      name: user.name,
    };
  });
}

export async function createCouponForUser(
  mobile: string,
  couponCode: string,
  staffUserId: number
): Promise<{ coupon: Coupon; user: { id: number; mobile: string; name: string | null } }> {
  return await db.transaction(async (tx) => {
    // Get or create user
    let user = await tx.query.users.findFirst({
      where: eq(users.mobile, mobile),
    });

    if (!user) {
      const [newUser] = await tx.insert(users).values({
        name: null,
        email: null,
        mobile,
      }).returning();
      user = newUser;
    }

    // Create the coupon
    const [coupon] = await tx.insert(coupons).values({
      couponCode,
      isUserBased: true,
      discountPercent: "20",
      minOrder: "1000",
      maxValue: "500",
      maxLimitForUser: 1,
      isApplyForAll: false,
      exclusiveApply: false,
      createdBy: staffUserId,
      validTill: new Date(Date.now() + 90 * 24 * 60 * 60 * 1000), // 90 days from now
    }).returning();

    // Associate coupon with user
    await tx.insert(couponApplicableUsers).values({
      couponId: coupon.id,
      userId: user.id,
    });

    return {
      coupon: {
        id: coupon.id,
        couponCode: coupon.couponCode,
        isUserBased: coupon.isUserBased,
        discountPercent: coupon.discountPercent,
        flatDiscount: coupon.flatDiscount,
        minOrder: coupon.minOrder,
        productIds: coupon.productIds,
        maxValue: coupon.maxValue,
        isApplyForAll: coupon.isApplyForAll,
        validTill: coupon.validTill,
        maxLimitForUser: coupon.maxLimitForUser,
        exclusiveApply: coupon.exclusiveApply,
        isInvalidated: coupon.isInvalidated,
        createdAt: coupon.createdAt,
        createdBy: coupon.createdBy,
      },
      user: {
        id: user.id,
        mobile: user.mobile,
        name: user.name,
      },
    };
  });
}

// ============================================================================
// Utility Functions
// ============================================================================

export async function checkUsersExist(userIds: number[]): Promise<boolean> {
  const existingUsers = await db.query.users.findMany({
    where: inArray(users.id, userIds),
    columns: { id: true },
  });
  return existingUsers.length === userIds.length;
}

export async function checkCouponExists(couponCode: string): Promise<boolean> {
  const existing = await db.query.coupons.findFirst({
    where: eq(coupons.couponCode, couponCode),
  });
  return !!existing;
}

export async function checkReservedCouponExists(secretCode: string): Promise<boolean> {
  const existing = await db.query.reservedCoupons.findFirst({
    where: eq(reservedCoupons.secretCode, secretCode),
  });
  return !!existing;
}

export async function getOrderWithUser(orderId: number): Promise<any | null> {
  return await db.query.orders.findFirst({
    where: eq(orders.id, orderId),
    with: {
      user: true,
    },
  });
}
