import db from "@/db/db"
import { stageSubmissionView, StageSubmissionView } from "@/db/schema"
import { and, asc, desc, eq, or, ilike, isTrue, sql, isNotNull, count } from "drizzle-orm"
import { filterColumns } from "@/lib/filter-columns"
import { GetStageSubmissionsSchema } from "./validation"
import { getServerSession } from 'next-auth/next'
import { authOptions } from '@/app/api/auth/[...nextauth]/route'
import { redirect } from "next/navigation"

// Repository functions (동일)
async function selectStageSubmissions(
  tx: typeof db,
  params: {
    where?: any
    orderBy?: any
    offset?: number
    limit?: number
  }
) {
  const { where, orderBy = [desc(stageSubmissionView.isOverdue)], offset = 0, limit = 10 } = params
  
  const query = tx
    .select()
    .from(stageSubmissionView)
    .$dynamic()
  
  if (where) query.where(where)
  if (orderBy) query.orderBy(...(Array.isArray(orderBy) ? orderBy : [orderBy]))
  query.limit(limit).offset(offset)
  
  return await query
}

async function countStageSubmissions(tx: typeof db, where?: any) {
  const query = tx
    .select({   count: count() })
    .from(stageSubmissionView)
    .$dynamic()
  
  if (where) query.where(where)
  
  const result = await query
  return result[0]?.count ?? 0
}

// Service function with session check
export async function getStageSubmissions(input: GetStageSubmissionsSchema) {
  // Session 체크
  const session = await getServerSession(authOptions)
  if (!session?.user?.id) {
    return {
      success: false,
      error: '로그인이 필요합니다.'
    }
  }
  const vendorId = session.user.companyId // companyId가 vendorId

  try {
    const offset = (input.page - 1) * input.perPage

    // Advanced filters
    const advancedWhere = filterColumns({
      table: stageSubmissionView,
      filters: input.filters,
      joinOperator: input.joinOperator,
    })

    // Global search
    let globalWhere
    if (input.search) {
      const s = `%${input.search}%`
      globalWhere = or(
        ilike(stageSubmissionView.documentTitle, s),
        ilike(stageSubmissionView.docNumber, s),
        ilike(stageSubmissionView.vendorDocNumber, s),
        ilike(stageSubmissionView.stageName, s)
        // vendorName 검색 제거 (자기 회사만 보므로)
      )
    }

    // Status filters
    let statusWhere
    if (input.submissionStatus && input.submissionStatus !== "all") {
      switch (input.submissionStatus) {
        case "required":
          statusWhere = eq(stageSubmissionView.requiresSubmission, true)
          break
        case "submitted":
          statusWhere = eq(stageSubmissionView.latestSubmissionStatus, "SUBMITTED")
          break
        case "approved":
          statusWhere = eq(stageSubmissionView.latestReviewStatus, "APPROVED")
          break
        case "rejected":
          statusWhere = eq(stageSubmissionView.latestReviewStatus, "REJECTED")
          break
      }
    }

    // Sync status filter
    let syncWhere
    if (input.syncStatus && input.syncStatus !== "all") {
      if (input.syncStatus === "pending") {
        syncWhere = or(
          eq(stageSubmissionView.latestSyncStatus, "pending"),
          eq(stageSubmissionView.requiresSync, true)
        )
      } else {
        syncWhere = eq(stageSubmissionView.latestSyncStatus, input.syncStatus)
      }
    }

    // Project filter
    let projectWhere = input.projectId ? eq(stageSubmissionView.projectId, input.projectId) : undefined

    // ✅ 벤더 필터 - session의 companyId 사용
    const vendorWhere = eq(stageSubmissionView.vendorId, vendorId)

    const finalWhere = and(
      vendorWhere, // 항상 벤더 필터 적용
      advancedWhere,
      globalWhere,
      statusWhere,
      syncWhere,
      projectWhere
    )

    const orderBy =
      input.sort.length > 0
        ? input.sort.map((item) =>
            item.desc 
              ? desc(stageSubmissionView[item.id]) 
              : asc(stageSubmissionView[item.id])
          )
        : [desc(stageSubmissionView.isOverdue), asc(stageSubmissionView.daysUntilDue)]

    // Transaction
    const { data, total } = await db.transaction(async (tx) => {
      const data = await selectStageSubmissions(tx, {
        where: finalWhere,
        orderBy,
        offset,
        limit: input.perPage,
      })
      const total = await countStageSubmissions(tx, finalWhere)
      return { data, total }
    })

    const pageCount = Math.ceil(total / input.perPage)

    return { data, pageCount }
  } catch (err) {
    console.error("Error fetching stage submissions:", err)
    return { data: [], pageCount: 0 }
  }
}

// 프로젝트 목록 조회 - 벤더 필터 적용
export async function getProjects() {
    const session = await getServerSession(authOptions)
    if (!session?.user?.id) {
      return {
        success: false,
        error: '로그인이 필요합니다.'
      }
    }
  if (!session?.user?.companyId) {
    return []
  }
  
  const vendorId = session.user.companyId

  const projects = await db
    .selectDistinct({
      id: stageSubmissionView.projectId,
      code: stageSubmissionView.projectCode,
    })
    .from(stageSubmissionView)
    .where(
      and(
        eq(stageSubmissionView.vendorId, vendorId),
        isNotNull(stageSubmissionView.projectId)
      )
    )
    .orderBy(asc(stageSubmissionView.projectCode))

  return projects
}

// 통계 조회 - 벤더별
export async function getSubmissionStats() {
    const session = await getServerSession(authOptions)
    if (!session?.user?.id) {
      return {
        success: false,
        error: '로그인이 필요합니다.'
      }
    }


  if (!session?.user?.companyId) {
    return {
      pending: 0,
      overdue: 0,
      awaitingSync: 0,
      completed: 0,
    }
  }
  
  const vendorId = session.user.companyId

  const stats = await db
    .select({
      pending: sql<number>`count(*) filter (where ${stageSubmissionView.requiresSubmission} = true)::int`,
      overdue: sql<number>`count(*) filter (where ${stageSubmissionView.isOverdue} = true)::int`,
      awaitingSync: sql<number>`count(*) filter (where ${stageSubmissionView.requiresSync} = true)::int`,
      completed: sql<number>`count(*) filter (where ${stageSubmissionView.latestReviewStatus} = 'APPROVED')::int`,
    })
    .from(stageSubmissionView)
    .where(eq(stageSubmissionView.vendorId, vendorId))

  return stats[0] || {
    pending: 0,
    overdue: 0,
    awaitingSync: 0,
    completed: 0,
  }
}