import { NextRequest, NextResponse } from 'next/server'
import { getSession } from '@/lib/auth'
import { queryJobPanelDb } from '@/lib/job-panel-db'
import { checkPanelAccess } from '@/lib/services/job-panel-permissions'
import { v4 as uuidv4 } from 'uuid'
import type { RowDataPacket } from 'mysql2'

// Auto-create table if it doesn't exist
async function ensureAnnouncementsTable() {
  try {
    await queryJobPanelDb(`
      CREATE TABLE IF NOT EXISTS job_panel_announcements (
        id VARCHAR(36) PRIMARY KEY,
        panel_id VARCHAR(36) NOT NULL,
        title VARCHAR(255) NOT NULL,
        content TEXT NOT NULL,
        priority ENUM('low', 'normal', 'high', 'urgent') DEFAULT 'normal',
        is_pinned TINYINT(1) DEFAULT 0,
        min_rank INT DEFAULT 0 COMMENT 'Minimum rank required to see this announcement (0 = all)',
        visible_from DATETIME DEFAULT NULL COMMENT 'When the announcement becomes visible (NULL = immediately)',
        visible_until DATETIME DEFAULT NULL COMMENT 'When the announcement expires (NULL = never)',
        created_by VARCHAR(36) NOT NULL,
        created_by_name VARCHAR(255) NOT NULL,
        created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
        updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        INDEX idx_panel_id (panel_id),
        INDEX idx_visibility (panel_id, visible_from, visible_until, min_rank)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
    `)
    return true
  } catch (error) {
    console.error('[Announcements] Failed to create table:', error)
    return false
  }
}

// GET - List announcements
export async function GET(
  request: NextRequest,
  { params }: { params: Promise<{ id: string }> }
) {
  try {
    const session = await getSession()
    if (!session) {
      return NextResponse.json({ error: 'Unauthorized' }, { status: 401 })
    }

    const { id } = await params
    const { searchParams } = new URL(request.url)
    const userRank = parseInt(searchParams.get('rank') || '0')

    // Check access
    const access = await checkPanelAccess(id)
    if (!access?.canAccess) {
      return NextResponse.json({ error: 'Access denied' }, { status: 403 })
    }

    // Get announcements that are currently visible and match user's rank
    // For leaders/superadmins, show all announcements
    const isLeader = access.isLeader || access.isCoLeader || access.isSuperadmin

    let query = `
      SELECT * FROM job_panel_announcements 
      WHERE panel_id = ?
      AND (visible_from IS NULL OR visible_from <= NOW())
      AND (visible_until IS NULL OR visible_until >= NOW())
    `
    const queryParams: (string | number)[] = [id]

    // Non-leaders only see announcements for their rank or lower
    if (!isLeader) {
      query += ' AND min_rank <= ?'
      queryParams.push(userRank)
    }

    query += ' ORDER BY is_pinned DESC, priority DESC, created_at DESC'

    const announcements = await queryJobPanelDb<RowDataPacket[]>(query, queryParams)

    return NextResponse.json({
      announcements: announcements.map(a => ({
        id: a.id,
        title: a.title,
        content: a.content,
        priority: a.priority,
        isPinned: a.is_pinned === 1,
        minRank: a.min_rank,
        visibleFrom: a.visible_from,
        visibleUntil: a.visible_until,
        createdBy: a.created_by,
        createdByName: a.created_by_name,
        createdAt: a.created_at,
      })),
    })
  } catch (error) {
    // If table doesn't exist, create it and return empty array
    const errorMessage = error instanceof Error ? error.message : String(error)
    if (errorMessage.includes("doesn't exist") || errorMessage.includes('Table') || errorMessage.includes('ER_NO_SUCH_TABLE')) {
      await ensureAnnouncementsTable()
      return NextResponse.json({ announcements: [], tableCreated: true })
    }
    console.error('[Announcements] GET error:', error)
    return NextResponse.json({ error: 'Failed to fetch announcements' }, { status: 500 })
  }
}

// POST - Create announcement
export async function POST(
  request: NextRequest,
  { params }: { params: Promise<{ id: string }> }
) {
  try {
    const session = await getSession()
    if (!session) {
      return NextResponse.json({ error: 'Unauthorized' }, { status: 401 })
    }

    const { id } = await params

    // Check access - only leaders can create announcements
    const access = await checkPanelAccess(id)
    if (!access?.canAccess || (!access.isLeader && !access.isCoLeader && !access.isSuperadmin)) {
      return NextResponse.json({ error: 'Access denied' }, { status: 403 })
    }

    const body = await request.json()
    const { title, content, priority, isPinned, minRank, visibleFrom, visibleUntil } = body

    if (!title || !content) {
      return NextResponse.json({ error: 'Title and content are required' }, { status: 400 })
    }

    const announcementId = uuidv4()

    await queryJobPanelDb(
      `INSERT INTO job_panel_announcements 
       (id, panel_id, title, content, priority, is_pinned, min_rank, visible_from, visible_until, created_by, created_by_name)
       VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
      [
        announcementId,
        id,
        title,
        content,
        priority || 'normal',
        isPinned ? 1 : 0,
        minRank || 0,
        visibleFrom || null,
        visibleUntil || null,
        session.discordId,
        session.username || 'Unknown',
      ]
    )

    return NextResponse.json({ 
      success: true, 
      announcementId,
      message: 'Announcement created successfully' 
    })
  } catch (error) {
    const errorMessage = error instanceof Error ? error.message : String(error)
    if (errorMessage.includes("doesn't exist") || errorMessage.includes('ER_NO_SUCH_TABLE')) {
      // Try to create table and retry
      const created = await ensureAnnouncementsTable()
      if (created) {
        return NextResponse.json({ error: 'Table was just created. Please try again.' }, { status: 503 })
      }
      return NextResponse.json({ error: 'Failed to create announcements table' }, { status: 500 })
    }
    console.error('[Announcements] POST error:', error)
    return NextResponse.json({ error: 'Failed to create announcement' }, { status: 500 })
  }
}

// PUT - Update announcement
export async function PUT(
  request: NextRequest,
  { params }: { params: Promise<{ id: string }> }
) {
  try {
    const session = await getSession()
    if (!session) {
      return NextResponse.json({ error: 'Unauthorized' }, { status: 401 })
    }

    const { id } = await params

    // Check access - only leaders can update announcements
    const access = await checkPanelAccess(id)
    if (!access?.canAccess || (!access.isLeader && !access.isCoLeader && !access.isSuperadmin)) {
      return NextResponse.json({ error: 'Access denied' }, { status: 403 })
    }

    const body = await request.json()
    const { announcementId, title, content, priority, isPinned, minRank, visibleFrom, visibleUntil } = body

    if (!announcementId) {
      return NextResponse.json({ error: 'Announcement ID is required' }, { status: 400 })
    }

    await queryJobPanelDb(
      `UPDATE job_panel_announcements 
       SET title = ?, content = ?, priority = ?, is_pinned = ?, min_rank = ?, visible_from = ?, visible_until = ?
       WHERE id = ? AND panel_id = ?`,
      [
        title,
        content,
        priority || 'normal',
        isPinned ? 1 : 0,
        minRank || 0,
        visibleFrom || null,
        visibleUntil || null,
        announcementId,
        id,
      ]
    )

    return NextResponse.json({ success: true, message: 'Announcement updated successfully' })
  } catch (error) {
    console.error('[Announcements] PUT error:', error)
    return NextResponse.json({ error: 'Failed to update announcement' }, { status: 500 })
  }
}

// DELETE - Delete announcement
export async function DELETE(
  request: NextRequest,
  { params }: { params: Promise<{ id: string }> }
) {
  try {
    const session = await getSession()
    if (!session) {
      return NextResponse.json({ error: 'Unauthorized' }, { status: 401 })
    }

    const { id } = await params
    const { searchParams } = new URL(request.url)
    const announcementId = searchParams.get('announcementId')

    if (!announcementId) {
      return NextResponse.json({ error: 'Announcement ID is required' }, { status: 400 })
    }

    // Check access - only leaders can delete announcements
    const access = await checkPanelAccess(id)
    if (!access?.canAccess || (!access.isLeader && !access.isCoLeader && !access.isSuperadmin)) {
      return NextResponse.json({ error: 'Access denied' }, { status: 403 })
    }

    await queryJobPanelDb(
      'DELETE FROM job_panel_announcements WHERE id = ? AND panel_id = ?',
      [announcementId, id]
    )

    return NextResponse.json({ success: true, message: 'Announcement deleted successfully' })
  } catch (error) {
    console.error('[Announcements] DELETE error:', error)
    return NextResponse.json({ error: 'Failed to delete announcement' }, { status: 500 })
  }
}