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

const router = express.Router();

// Get all clients (API endpoint)
router.get('/', async (req, res) => {
  try {
    const { featured, showcase, 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');
    }
    
    if (showcase !== undefined) {
      paramCount++;
      whereConditions.push(`is_showcase = $${paramCount}`);
      params.push(showcase === '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 clients = await db.query(`
      SELECT 
        id, company_name, industry_en, industry_id, logo_url, website_url,
        project_title_en, project_title_id, project_description_en, project_description_id,
        project_value, project_year, project_status, case_study_en, case_study_id,
        testimonial_en, testimonial_id, testimonial_author, testimonial_position,
        display_order, is_featured, is_showcase, is_active, created_at, updated_at
      FROM clients
      ${whereClause}
      ORDER BY display_order ASC, created_at DESC
      ${limitClause}
    `, params);
    
    res.json({
      success: true,
      clients: clients.rows
    });
    
  } catch (error) {
    console.error('Get clients error:', error);
    res.status(500).json({
      success: false,
      message: 'Error retrieving clients'
    });
  }
});

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

// Create new client
router.post('/',
  uploadRateLimit,
  upload.single('logo'),
  handleUploadError,
  [
    body('company_name')
      .trim()
      .isLength({ min: 2, max: 255 })
      .withMessage('Company name must be between 2 and 255 characters'),
    
    body('industry_en')
      .optional()
      .trim()
      .isLength({ max: 255 })
      .withMessage('Industry (EN) must be less than 255 characters'),
    
    body('industry_id')
      .optional()
      .trim()
      .isLength({ max: 255 })
      .withMessage('Industry (ID) must be less than 255 characters'),
    
    body('project_title_en')
      .optional()
      .trim()
      .isLength({ max: 255 })
      .withMessage('Project title (EN) must be less than 255 characters'),
    
    body('project_title_id')
      .optional()
      .trim()
      .isLength({ max: 255 })
      .withMessage('Project title (ID) must be less than 255 characters'),
    
    body('project_value')
      .optional()
      .isFloat({ min: 0 })
      .withMessage('Project value must be a positive number'),
    
    body('project_year')
      .optional()
      .isInt({ min: 1900, max: new Date().getFullYear() + 10 })
      .withMessage('Project year must be a valid year'),
    
    body('project_status')
      .optional()
      .isIn(['completed', 'ongoing', 'planned'])
      .withMessage('Project status must be completed, ongoing, or planned'),
    
    body('website_url')
      .optional()
      .isURL()
      .withMessage('Website 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 {
        company_name, industry_en, industry_id, website_url,
        project_title_en, project_title_id, project_description_en, project_description_id,
        project_value, project_year, project_status = 'completed',
        case_study_en, case_study_id, testimonial_en, testimonial_id,
        testimonial_author, testimonial_position, display_order = 0,
        is_featured = false, is_showcase = true
      } = req.body;
      
      const logo_url = req.file ? `/uploads/${req.file.filename}` : null;
      
      const result = await db.query(`
        INSERT INTO clients (
          company_name, industry_en, industry_id, logo_url, website_url,
          project_title_en, project_title_id, project_description_en, project_description_id,
          project_value, project_year, project_status, case_study_en, case_study_id,
          testimonial_en, testimonial_id, testimonial_author, testimonial_position,
          display_order, is_featured, is_showcase
        ) VALUES (
          $1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17, $18, $19, $20, $21
        ) RETURNING id
      `, [
        company_name, industry_en, industry_id, logo_url, website_url,
        project_title_en, project_title_id, project_description_en, project_description_id,
        project_value ? parseFloat(project_value) : null,
        project_year ? parseInt(project_year) : null,
        project_status, case_study_en, case_study_id, testimonial_en, testimonial_id,
        testimonial_author, testimonial_position, parseInt(display_order),
        is_featured === 'true' || is_featured === true,
        is_showcase === 'true' || is_showcase === true
      ]);
      
      const clientId = result.rows[0].id;
      
      await logAuditEvent(req.user.id, 'client_created', 'client', clientId, req, { company_name });
      
      res.json({
        success: true,
        message: 'Client created successfully',
        client_id: clientId
      });
      
    } catch (error) {
      console.error('Create client error:', error);
      res.status(500).json({
        success: false,
        message: 'Error creating client'
      });
    }
  }
);

// Update client
router.put('/:id',
  uploadRateLimit,
  upload.single('logo'),
  handleUploadError,
  [
    body('company_name')
      .optional()
      .trim()
      .isLength({ min: 2, max: 255 })
      .withMessage('Company name must be between 2 and 255 characters'),
    
    body('project_value')
      .optional()
      .isFloat({ min: 0 })
      .withMessage('Project value must be a positive number'),
    
    body('project_year')
      .optional()
      .isInt({ min: 1900, max: new Date().getFullYear() + 10 })
      .withMessage('Project year must be a valid year'),
    
    body('project_status')
      .optional()
      .isIn(['completed', 'ongoing', 'planned'])
      .withMessage('Project status must be completed, ongoing, or planned'),
    
    body('website_url')
      .optional()
      .isURL()
      .withMessage('Website 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 client exists
      const existingClient = await db.query('SELECT * FROM clients WHERE id = $1', [id]);
      
      if (existingClient.rows.length === 0) {
        return res.status(404).json({
          success: false,
          message: 'Client 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 === 'project_value') {
            updateValues.push(parseFloat(value));
          } else if (key === 'project_year' || key === 'display_order') {
            updateValues.push(parseInt(value));
          } else if (key === 'is_featured' || key === 'is_showcase' || key === 'is_active') {
            updateValues.push(value === 'true' || value === true);
          } else {
            updateValues.push(value);
          }
        }
      }
      
      // Handle file upload
      if (req.file) {
        paramCount++;
        updateFields.push(`logo_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 clients 
        SET ${updateFields.join(', ')}
        WHERE id = $${paramCount}
      `, updateValues);
      
      await logAuditEvent(req.user.id, 'client_updated', 'client', id, req, req.body);
      
      res.json({
        success: true,
        message: 'Client updated successfully'
      });
      
    } catch (error) {
      console.error('Update client error:', error);
      res.status(500).json({
        success: false,
        message: 'Error updating client'
      });
    }
  }
);

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

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

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

module.exports = router;