const express = require('express');
const { body } = require('express-validator');
const { db } = require('../config/database');
const { logAuditEvent } = require('../middleware/auth');
const { 
  handleAPIValidation, 
  upload, 
  handleUploadError,
  uploadRateLimit,
  validateEmail
} = require('../middleware/security');

const router = express.Router();

// Get all team members (API endpoint)
router.get('/', async (req, res) => {
  try {
    const { featured, active = 'true', limit, offset } = req.query;
    
    let whereConditions = [];
    let params = [];
    let paramCount = 0;
    
    if (active !== 'all') {
      paramCount++;
      whereConditions.push(`is_active = $${paramCount}`);
      params.push(active === 'true');
    }
    
    if (featured !== undefined) {
      paramCount++;
      whereConditions.push(`is_featured = $${paramCount}`);
      params.push(featured === 'true');
    }
    
    const whereClause = whereConditions.length > 0 
      ? `WHERE ${whereConditions.join(' AND ')}`
      : '';
    
    let limitClause = '';
    if (limit) {
      paramCount++;
      limitClause += ` LIMIT $${paramCount}`;
      params.push(parseInt(limit));
      
      if (offset) {
        paramCount++;
        limitClause += ` OFFSET $${paramCount}`;
        params.push(parseInt(offset));
      }
    }
    
    const teamMembers = await db.query(`
      SELECT 
        id, name, position_en, position_id, bio_en, bio_id,
        email, phone, linkedin_url, avatar_url, display_order,
        is_featured, is_active, created_at, updated_at
      FROM team_members
      ${whereClause}
      ORDER BY display_order ASC, created_at DESC
      ${limitClause}
    `, params);
    
    res.json({
      success: true,
      team_members: teamMembers.rows
    });
    
  } catch (error) {
    console.error('Get team members error:', error);
    res.status(500).json({
      success: false,
      message: 'Error retrieving team members'
    });
  }
});

// Get single team member
router.get('/:id', async (req, res) => {
  try {
    const { id } = req.params;
    
    const result = await db.query(`
      SELECT *
      FROM team_members
      WHERE id = $1
    `, [id]);
    
    if (result.rows.length === 0) {
      return res.status(404).json({
        success: false,
        message: 'Team member not found'
      });
    }
    
    res.json({
      success: true,
      team_member: result.rows[0]
    });
    
  } catch (error) {
    console.error('Get team member error:', error);
    res.status(500).json({
      success: false,
      message: 'Error retrieving team member'
    });
  }
});

// Create new team member
router.post('/',
  uploadRateLimit,
  upload.single('avatar'),
  handleUploadError,
  [
    body('name')
      .trim()
      .isLength({ min: 2, max: 255 })
      .withMessage('Name must be between 2 and 255 characters'),
    
    body('position_en')
      .trim()
      .isLength({ min: 2, max: 255 })
      .withMessage('Position (EN) must be between 2 and 255 characters'),
    
    body('position_id')
      .trim()
      .isLength({ min: 2, max: 255 })
      .withMessage('Position (ID) must be between 2 and 255 characters'),
    
    body('email')
      .optional()
      .isEmail()
      .normalizeEmail()
      .withMessage('Please provide a valid email address'),
    
    body('phone')
      .optional()
      .matches(/^[\+]?[1-9][\d]{0,15}$/)
      .withMessage('Please provide a valid phone number'),
    
    body('linkedin_url')
      .optional()
      .isURL()
      .withMessage('LinkedIn URL must be valid'),
    
    body('display_order')
      .optional()
      .isInt({ min: 0 })
      .withMessage('Display order must be a non-negative integer')
  ],
  handleAPIValidation,
  async (req, res) => {
    try {
      const {
        name, position_en, position_id, bio_en, bio_id,
        email, phone, linkedin_url, display_order = 0,
        is_featured = false
      } = req.body;
      
      const avatar_url = req.file ? `/uploads/${req.file.filename}` : null;
      
      const result = await db.query(`
        INSERT INTO team_members (
          name, position_en, position_id, bio_en, bio_id,
          email, phone, linkedin_url, avatar_url, display_order, is_featured
        ) VALUES (
          $1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11
        ) RETURNING id
      `, [
        name, position_en, position_id, bio_en, bio_id,
        email, phone, linkedin_url, avatar_url, parseInt(display_order),
        is_featured === 'true' || is_featured === true
      ]);
      
      const memberId = result.rows[0].id;
      
      await logAuditEvent(req.user.id, 'team_member_created', 'team_member', memberId, req, { name });
      
      res.json({
        success: true,
        message: 'Team member created successfully',
        member_id: memberId
      });
      
    } catch (error) {
      console.error('Create team member error:', error);
      res.status(500).json({
        success: false,
        message: 'Error creating team member'
      });
    }
  }
);

// Update team member
router.put('/:id',
  uploadRateLimit,
  upload.single('avatar'),
  handleUploadError,
  [
    body('name')
      .optional()
      .trim()
      .isLength({ min: 2, max: 255 })
      .withMessage('Name must be between 2 and 255 characters'),
    
    body('position_en')
      .optional()
      .trim()
      .isLength({ min: 2, max: 255 })
      .withMessage('Position (EN) must be between 2 and 255 characters'),
    
    body('position_id')
      .optional()
      .trim()
      .isLength({ min: 2, max: 255 })
      .withMessage('Position (ID) must be between 2 and 255 characters'),
    
    body('email')
      .optional()
      .isEmail()
      .normalizeEmail()
      .withMessage('Please provide a valid email address'),
    
    body('phone')
      .optional()
      .matches(/^[\+]?[1-9][\d]{0,15}$/)
      .withMessage('Please provide a valid phone number'),
    
    body('linkedin_url')
      .optional()
      .isURL()
      .withMessage('LinkedIn URL must be valid'),
    
    body('display_order')
      .optional()
      .isInt({ min: 0 })
      .withMessage('Display order must be a non-negative integer')
  ],
  handleAPIValidation,
  async (req, res) => {
    try {
      const { id } = req.params;
      
      // Check if team member exists
      const existingMember = await db.query('SELECT * FROM team_members WHERE id = $1', [id]);
      
      if (existingMember.rows.length === 0) {
        return res.status(404).json({
          success: false,
          message: 'Team member not found'
        });
      }
      
      const updateFields = [];
      const updateValues = [];
      let paramCount = 0;
      
      // Build dynamic update query
      for (const [key, value] of Object.entries(req.body)) {
        if (value !== undefined && value !== '') {
          paramCount++;
          updateFields.push(`${key} = $${paramCount}`);
          
          if (key === 'display_order') {
            updateValues.push(parseInt(value));
          } else if (key === 'is_featured' || key === 'is_active') {
            updateValues.push(value === 'true' || value === true);
          } else {
            updateValues.push(value);
          }
        }
      }
      
      // Handle file upload
      if (req.file) {
        paramCount++;
        updateFields.push(`avatar_url = $${paramCount}`);
        updateValues.push(`/uploads/${req.file.filename}`);
      }
      
      // Add updated_at timestamp
      paramCount++;
      updateFields.push(`updated_at = $${paramCount}`);
      updateValues.push(new Date());
      
      // Add WHERE clause parameter
      paramCount++;
      updateValues.push(id);
      
      if (updateFields.length === 1) { // Only updated_at was added
        return res.status(400).json({
          success: false,
          message: 'No valid fields to update'
        });
      }
      
      await db.query(`
        UPDATE team_members 
        SET ${updateFields.join(', ')}
        WHERE id = $${paramCount}
      `, updateValues);
      
      await logAuditEvent(req.user.id, 'team_member_updated', 'team_member', id, req, req.body);
      
      res.json({
        success: true,
        message: 'Team member updated successfully'
      });
      
    } catch (error) {
      console.error('Update team member error:', error);
      res.status(500).json({
        success: false,
        message: 'Error updating team member'
      });
    }
  }
);

// Delete team member
router.delete('/:id', async (req, res) => {
  try {
    const { id } = req.params;
    
    // Check if team member exists
    const existingMember = await db.query(
      'SELECT name FROM team_members WHERE id = $1', 
      [id]
    );
    
    if (existingMember.rows.length === 0) {
      return res.status(404).json({
        success: false,
        message: 'Team member not found'
      });
    }
    
    await db.query('DELETE FROM team_members WHERE id = $1', [id]);
    
    await logAuditEvent(req.user.id, 'team_member_deleted', 'team_member', id, req, {
      name: existingMember.rows[0].name
    });
    
    res.json({
      success: true,
      message: 'Team member deleted successfully'
    });
    
  } catch (error) {
    console.error('Delete team member error:', error);
    res.status(500).json({
      success: false,
      message: 'Error deleting team member'
    });
  }
});

// Toggle team member featured status
router.post('/:id/featured', async (req, res) => {
  try {
    const { id } = req.params;
    
    const result = await db.query(`
      UPDATE team_members 
      SET is_featured = NOT is_featured, updated_at = CURRENT_TIMESTAMP
      WHERE id = $1
      RETURNING is_featured, name
    `, [id]);
    
    if (result.rows.length === 0) {
      return res.status(404).json({
        success: false,
        message: 'Team member not found'
      });
    }
    
    const { is_featured, name } = result.rows[0];
    
    await logAuditEvent(req.user.id, 'team_member_featured_toggled', 'team_member', id, req, {
      name,
      is_featured
    });
    
    res.json({
      success: true,
      message: `Team member ${is_featured ? 'featured' : 'unfeatured'} successfully`,
      is_featured
    });
    
  } catch (error) {
    console.error('Toggle featured error:', error);
    res.status(500).json({
      success: false,
      message: 'Error updating team member'
    });
  }
});

// Reorder team members
router.post('/reorder', 
  [
    body('members')
      .isArray()
      .withMessage('Members must be an array'),
    body('members.*.id')
      .isUUID()
      .withMessage('Each member must have a valid ID'),
    body('members.*.display_order')
      .isInt({ min: 0 })
      .withMessage('Each member must have a valid display order')
  ],
  handleAPIValidation,
  async (req, res) => {
    try {
      const { members } = req.body;
      
      // Use transaction for atomic updates
      await db.transaction(async (client) => {
        for (const memberData of members) {
          await client.query(
            'UPDATE team_members SET display_order = $1, updated_at = CURRENT_TIMESTAMP WHERE id = $2',
            [memberData.display_order, memberData.id]
          );
        }
      });
      
      await logAuditEvent(req.user.id, 'team_members_reordered', 'team_member', null, req, {
        count: members.length
      });
      
      res.json({
        success: true,
        message: 'Team members reordered successfully'
      });
      
    } catch (error) {
      console.error('Reorder team members error:', error);
      res.status(500).json({
        success: false,
        message: 'Error reordering team members'
      });
    }
  }
);

module.exports = router;