import pool from '../config/db.js';

let tableChecked = false;

// Ensure admin_tasks table exists and has proper columns
async function ensureTasksTable() {
  if (tableChecked) return;
  try {
    await pool.query(`
      CREATE TABLE IF NOT EXISTS admin_tasks (
        id INT AUTO_INCREMENT PRIMARY KEY,
        title VARCHAR(255) NOT NULL,
        description TEXT NULL,
        listing_id INT NULL,
        status ENUM('pending', 'in_progress', 'completed') DEFAULT 'pending',
        priority ENUM('low', 'medium', 'high', 'urgent') DEFAULT 'medium',
        due_date DATE NULL,
        assigned_to INT NULL,
        created_by INT NULL,
        completed_at TIMESTAMP NULL,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        INDEX idx_admin_tasks_status (status),
        INDEX idx_admin_tasks_priority (priority),
        INDEX idx_admin_tasks_assigned_to (assigned_to),
        INDEX idx_admin_tasks_due_date (due_date),
        INDEX idx_admin_tasks_listing_id (listing_id)
      ) ENGINE=InnoDB;
    `);

    // Ensure listing_id column exists if table was previously created
    try {
      await pool.query(`ALTER TABLE admin_tasks ADD COLUMN IF NOT EXISTS listing_id INT NULL`);
      await pool.query(`CREATE INDEX IF NOT EXISTS idx_admin_tasks_listing_id ON admin_tasks(listing_id)`);
    } catch (e) {}

    tableChecked = true;
  } catch (err) {
    console.error("Failed to verify admin_tasks table:", err.message);
  }
}

// Helper to record administrative audit events in audit_logs table
async function logAuditEvent(adminUserId, eventType, description, severity = 'Info', ipAddress = null) {
  try {
    await pool.query(`
      CREATE TABLE IF NOT EXISTS audit_logs (
        id INT AUTO_INCREMENT PRIMARY KEY,
        admin_user_id INT,
        event_type VARCHAR(100) NOT NULL,
        description TEXT,
        severity VARCHAR(20) DEFAULT 'Info',
        ip_address VARCHAR(45),
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
      ) ENGINE=InnoDB;
    `);

    await pool.query(
      `INSERT INTO audit_logs (admin_user_id, event_type, description, severity, ip_address) 
       VALUES (?, ?, ?, ?, ?)`,
      [adminUserId, eventType, description, severity, ipAddress]
    );
  } catch (err) {
    console.error("Failed to write audit log:", err.message);
  }
}

// 1. Get List of Admins and Moderators for Task Assignment
export async function getAssignees(req, res) {
  try {
    // Select users with staff / admin roles
    const [rows] = await pool.query(`
      SELECT 
        u.id, 
        u.first_name, 
        u.last_name, 
        u.email, 
        COALESCE(r.name, 'Admin') as role_name
      FROM users u
      LEFT JOIN roles r ON r.id = u.role_id
      WHERE u.status = 'Active' OR u.status IS NULL
      ORDER BY u.first_name ASC
    `);

    return res.json({ assignees: rows });
  } catch (err) {
    console.error("getAssignees error:", err);
    return res.status(500).json({ error: "Failed to fetch staff assignees." });
  }
}

// 2. Get All Tasks with Filters and Summary Stats
export async function getTasks(req, res) {
  try {
    await ensureTasksTable();

    const { status, priority, assigned_to, search, limit } = req.query;
    let whereClauses = [];
    let queryParams = [];

    if (status && status !== 'all') {
      whereClauses.push('t.status = ?');
      queryParams.push(status);
    }

    if (priority && priority !== 'all') {
      whereClauses.push('t.priority = ?');
      queryParams.push(priority);
    }

    if (assigned_to) {
      if (assigned_to === 'me' && req.user?.id) {
        whereClauses.push('t.assigned_to = ?');
        queryParams.push(req.user.id);
      } else if (assigned_to !== 'all') {
        whereClauses.push('t.assigned_to = ?');
        queryParams.push(parseInt(assigned_to, 10));
      }
    }

    if (search && search.trim()) {
      whereClauses.push('(t.title LIKE ? OR t.description LIKE ? OR l.name LIKE ?)');
      const term = `%${search.trim()}%`;
      queryParams.push(term, term, term);
    }

    const whereSql = whereClauses.length > 0 ? `WHERE ${whereClauses.join(' AND ')}` : '';
    let limitSql = '';
    if (limit) {
      limitSql = `LIMIT ${parseInt(limit, 10)}`;
    }

    const [tasks] = await pool.query(`
      SELECT 
        t.id,
        t.title,
        t.description,
        t.listing_id,
        t.status,
        t.priority,
        t.due_date,
        t.assigned_to,
        t.created_by,
        t.completed_at,
        t.created_at,
        t.updated_at,
        -- Assigned staff user details
        u.first_name as assignee_first_name,
        u.last_name as assignee_last_name,
        u.email as assignee_email,
        -- Creator staff user details
        c.first_name as creator_first_name,
        c.last_name as creator_last_name,
        -- Linked business listing details
        l.name as listing_name,
        l.category as listing_category,
        l.status as listing_status
      FROM admin_tasks t
      LEFT JOIN users u ON u.id = t.assigned_to
      LEFT JOIN users c ON c.id = t.created_by
      LEFT JOIN listings l ON l.id = t.listing_id
      ${whereSql}
      ORDER BY 
        CASE WHEN t.status = 'pending' THEN 1 WHEN t.status = 'in_progress' THEN 2 ELSE 3 END ASC,
        CASE WHEN t.priority = 'urgent' THEN 1 WHEN t.priority = 'high' THEN 2 WHEN t.priority = 'medium' THEN 3 ELSE 4 END ASC,
        t.created_at DESC
      ${limitSql}
    `, queryParams);

    // Compute task counts / stats
    const [statsRow] = await pool.query(`
      SELECT 
        COUNT(*) as total,
        SUM(CASE WHEN status = 'pending' THEN 1 ELSE 0 END) as pending,
        SUM(CASE WHEN status = 'in_progress' THEN 1 ELSE 0 END) as in_progress,
        SUM(CASE WHEN status = 'completed' THEN 1 ELSE 0 END) as completed,
        SUM(CASE WHEN due_date < CURDATE() AND status != 'completed' THEN 1 ELSE 0 END) as overdue
      FROM admin_tasks
    `);

    const stats = {
      total: statsRow[0]?.total || 0,
      pending: statsRow[0]?.pending || 0,
      in_progress: statsRow[0]?.in_progress || 0,
      completed: statsRow[0]?.completed || 0,
      overdue: statsRow[0]?.overdue || 0
    };

    return res.json({ tasks, stats });
  } catch (err) {
    console.error("getTasks error:", err);
    return res.status(500).json({ error: "Failed to fetch tasks." });
  }
}

// 3. Create a New Administrative Task (Directly or from Listing Remarks)
export async function createTask(req, res) {
  try {
    await ensureTasksTable();

    const { title, description, listing_id, priority, due_date, assigned_to } = req.body;
    if (!title || !title.trim()) {
      return res.status(400).json({ error: "Task title is required." });
    }

    const adminUserId = req.user?.id || null;
    const adminEmail = req.user?.email || "Admin";
    const ip = req.headers['x-forwarded-for']?.split(',')[0].trim() || req.socket.remoteAddress || '127.0.0.1';
    const listingId = listing_id ? parseInt(listing_id, 10) : null;
    const assignedTo = assigned_to ? parseInt(assigned_to, 10) : null;
    const cleanPriority = ['low', 'medium', 'high', 'urgent'].includes(priority) ? priority : 'medium';
    const cleanDueDate = due_date ? due_date : null;

    const [result] = await pool.query(
      `INSERT INTO admin_tasks (title, description, listing_id, priority, due_date, assigned_to, created_by, status) 
       VALUES (?, ?, ?, ?, ?, ?, ?, 'pending')`,
      [title.trim(), description || null, listingId, cleanPriority, cleanDueDate, assignedTo, adminUserId]
    );

    const taskId = result.insertId;

    // Log the task creation event
    const eventType = listingId ? 'TASK_CREATED_FROM_REMARK' : 'TASK_CREATED';
    const desc = listingId 
      ? `Task #${taskId} ("${title.trim()}") created from business remarks (Listing #${listingId}) by ${adminEmail}.`
      : `Task #${taskId} ("${title.trim()}") created by ${adminEmail}.`;
    await logAuditEvent(adminUserId, eventType, desc, 'Info', ip);

    return res.status(201).json({ success: true, taskId, message: "Task created successfully." });
  } catch (err) {
    console.error("createTask error:", err);
    return res.status(500).json({ error: "Failed to create task." });
  }
}

// 4. Update an Existing Task
export async function updateTask(req, res) {
  try {
    await ensureTasksTable();

    const { id } = req.params;
    const { title, description, listing_id, priority, due_date, assigned_to, status } = req.body;

    const adminUserId = req.user?.id || null;
    const adminEmail = req.user?.email || "Admin";
    const ip = req.headers['x-forwarded-for']?.split(',')[0].trim() || req.socket.remoteAddress || '127.0.0.1';

    // Check existing task
    const [existing] = await pool.query('SELECT * FROM admin_tasks WHERE id = ?', [id]);
    if (existing.length === 0) {
      return res.status(404).json({ error: "Task not found." });
    }
    const current = existing[0];

    const cleanTitle = title !== undefined ? title.trim() : current.title;
    const cleanDesc = description !== undefined ? description : current.description;
    const cleanPriority = priority !== undefined ? priority : current.priority;
    const cleanDueDate = due_date !== undefined ? due_date : current.due_date;
    const cleanAssignedTo = assigned_to !== undefined ? (assigned_to ? parseInt(assigned_to, 10) : null) : current.assigned_to;
    const cleanStatus = status !== undefined ? status : current.status;
    const listingId = listing_id !== undefined ? (listing_id ? parseInt(listing_id, 10) : null) : current.listing_id;

    const completedAt = cleanStatus === 'completed' ? (current.completed_at || new Date()) : null;

    await pool.query(
      `UPDATE admin_tasks 
       SET title = ?, description = ?, listing_id = ?, priority = ?, due_date = ?, assigned_to = ?, status = ?, completed_at = ? 
       WHERE id = ?`,
      [cleanTitle, cleanDesc, listingId, cleanPriority, cleanDueDate, cleanAssignedTo, cleanStatus, completedAt, id]
    );

    // If task was marked as completed and is linked to a business listing:
    // User directive: "if the task is done, that Remarks will be cleared too."
    if (cleanStatus === 'completed' && listingId) {
      await pool.query('UPDATE listings SET remarks = NULL WHERE id = ?', [listingId]);
      await logAuditEvent(
        adminUserId,
        'TASK_COMPLETED_REMARKS_CLEARED',
        `Task #${id} marked as Completed. Automatically cleared remarks on Listing #${listingId}.`,
        'Info',
        ip
      );
    } else {
      await logAuditEvent(
        adminUserId,
        'TASK_UPDATED',
        `Task #${id} ("${cleanTitle}") updated by ${adminEmail}. Status: ${cleanStatus}, Priority: ${cleanPriority}.`,
        'Info',
        ip
      );
    }

    return res.json({ success: true, message: "Task updated successfully." });
  } catch (err) {
    console.error("updateTask error:", err);
    return res.status(500).json({ error: "Failed to update task." });
  }
}

// 5. Quick Toggle Task Status (Pending <-> Completed)
export async function toggleTaskStatus(req, res) {
  try {
    await ensureTasksTable();

    const { id } = req.params;
    const adminUserId = req.user?.id || null;
    const adminEmail = req.user?.email || "Admin";
    const ip = req.headers['x-forwarded-for']?.split(',')[0].trim() || req.socket.remoteAddress || '127.0.0.1';

    const [existing] = await pool.query('SELECT * FROM admin_tasks WHERE id = ?', [id]);
    if (existing.length === 0) {
      return res.status(404).json({ error: "Task not found." });
    }
    const current = existing[0];

    const isNowCompleted = current.status !== 'completed';
    const newStatus = isNowCompleted ? 'completed' : 'pending';
    const completedAt = isNowCompleted ? new Date() : null;

    await pool.query(
      `UPDATE admin_tasks SET status = ?, completed_at = ? WHERE id = ?`,
      [newStatus, completedAt, id]
    );

    // If completed and has linked listing, clear listing remarks
    if (isNowCompleted && current.listing_id) {
      await pool.query('UPDATE listings SET remarks = NULL WHERE id = ?', [current.listing_id]);
      await logAuditEvent(
        adminUserId,
        'TASK_COMPLETED_REMARKS_CLEARED',
        `Task #${id} toggled to Completed by ${adminEmail}. Automatically cleared remarks on Listing #${current.listing_id}.`,
        'Info',
        ip
      );
    } else {
      await logAuditEvent(
        adminUserId,
        'TASK_STATUS_CHANGED',
        `Task #${id} ("${current.title}") status changed to ${newStatus} by ${adminEmail}.`,
        'Info',
        ip
      );
    }

    return res.json({ 
      success: true, 
      status: newStatus, 
      remarks_cleared: isNowCompleted && !!current.listing_id,
      message: `Task marked as ${newStatus}.` 
    });
  } catch (err) {
    console.error("toggleTaskStatus error:", err);
    return res.status(500).json({ error: "Failed to toggle task status." });
  }
}

// 6. Delete Task
export async function deleteTask(req, res) {
  try {
    await ensureTasksTable();

    const { id } = req.params;
    const adminUserId = req.user?.id || null;
    const adminEmail = req.user?.email || "Admin";
    const ip = req.headers['x-forwarded-for']?.split(',')[0].trim() || req.socket.remoteAddress || '127.0.0.1';

    const [existing] = await pool.query('SELECT * FROM admin_tasks WHERE id = ?', [id]);
    if (existing.length === 0) {
      return res.status(404).json({ error: "Task not found." });
    }
    const task = existing[0];

    await pool.query('DELETE FROM admin_tasks WHERE id = ?', [id]);

    await logAuditEvent(
      adminUserId,
      'TASK_DELETED',
      `Task #${id} ("${task.title}") deleted by ${adminEmail}.`,
      'Warning',
      ip
    );

    return res.json({ success: true, message: "Task deleted successfully." });
  } catch (err) {
    console.error("deleteTask error:", err);
    return res.status(500).json({ error: "Failed to delete task." });
  }
}
