const express = require('express');
const { body } = require('express-validator');
const { db } = require('../config/database');
const { getBilingualContent } = require('../middleware/i18n');
const { 
  validateEmail, 
  validateName, 
  validatePhone,
  checkValidationResult,
  createRateLimit
} = require('../middleware/security');

const router = express.Router();

// Contact form rate limiting
const contactRateLimit = createRateLimit(
  60 * 1000, // 1 minute
  3, // 3 submissions per minute
  'Too many contact form submissions. Please wait before submitting again.'
);

// Homepage
router.get('/', async (req, res) => {
  try {
    // Get company settings
    const settingsResult = await db.query(`
      SELECT key, value_en, value_id FROM settings WHERE is_public = true
    `);
    
    const settings = {};
    settingsResult.rows.forEach(row => {
      const value = getBilingualContent(req, row.value_en, row.value_id);
      settings[row.key] = value;
    });
    
    // Get featured team members
    const teamResult = await db.query(`
      SELECT name, position_en, position_id, bio_en, bio_id, avatar_url, linkedin_url
      FROM team_members 
      WHERE is_featured = true AND is_active = true 
      ORDER BY display_order ASC
      LIMIT 3
    `);
    
    const team = teamResult.rows.map(member => ({
      ...member,
      position: getBilingualContent(req, member.position_en, member.position_id),
      bio: getBilingualContent(req, member.bio_en, member.bio_id)
    }));
    
    // Get featured clients for showcase
    const clientsResult = await db.query(`
      SELECT 
        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
      FROM clients 
      WHERE is_featured = true AND is_showcase = true AND is_active = true 
      ORDER BY display_order ASC
      LIMIT 6
    `);
    
    const clients = clientsResult.rows.map(client => ({
      ...client,
      industry: getBilingualContent(req, client.industry_en, client.industry_id),
      project_title: getBilingualContent(req, client.project_title_en, client.project_title_id),
      project_description: getBilingualContent(req, client.project_description_en, client.project_description_id)
    }));
    
    // Get featured articles
    const articlesResult = await db.query(`
      SELECT 
        a.slug, a.title_en, a.title_id, a.summary_en, a.summary_id,
        a.featured_image_url, a.published_at, a.category_en, a.category_id,
        u.name as author_name
      FROM articles a
      LEFT JOIN users u ON a.author_id = u.id
      WHERE a.is_featured = true AND a.is_published = true 
      ORDER BY a.published_at DESC
      LIMIT 3
    `);
    
    const articles = articlesResult.rows.map(article => ({
      ...article,
      title: getBilingualContent(req, article.title_en, article.title_id),
      summary: getBilingualContent(req, article.summary_en, article.summary_id),
      category: getBilingualContent(req, article.category_en, article.category_id)
    }));
    
    res.render('public/index', {
      title: settings.company_name || 'Indo Advisory',
      settings,
      team,
      clients,
      articles
    });
    
  } catch (error) {
    console.error('Homepage error:', error);
    res.render('public/index', {
      title: 'Indo Advisory',
      settings: {},
      team: [],
      clients: [],
      articles: []
    });
  }
});

// About page
router.get('/about', async (req, res) => {
  try {
    // Get company settings
    const settingsResult = await db.query(`
      SELECT key, value_en, value_id FROM settings WHERE is_public = true
    `);
    
    const settings = {};
    settingsResult.rows.forEach(row => {
      const value = getBilingualContent(req, row.value_en, row.value_id);
      settings[row.key] = value;
    });
    
    // Get all active team members
    const teamResult = await db.query(`
      SELECT name, position_en, position_id, bio_en, bio_id, avatar_url, 
             email, linkedin_url, is_featured
      FROM team_members 
      WHERE is_active = true 
      ORDER BY display_order ASC, created_at ASC
    `);
    
    const team = teamResult.rows.map(member => ({
      ...member,
      position: getBilingualContent(req, member.position_en, member.position_id),
      bio: getBilingualContent(req, member.bio_en, member.bio_id)
    }));
    
    res.render('public/about', {
      title: `${res.locals.__('nav_about')} - ${settings.company_name || 'Indo Advisory'}`,
      settings,
      team
    });
    
  } catch (error) {
    console.error('About page error:', error);
    res.render('public/about', {
      title: 'About - Indo Advisory',
      settings: {},
      team: []
    });
  }
});

// Portfolio page
router.get('/portfolio', async (req, res) => {
  try {
    const page = parseInt(req.query.page) || 1;
    const limit = 12;
    const offset = (page - 1) * limit;
    
    // Get all active clients for portfolio
    const clientsResult = await db.query(`
      SELECT 
        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
      FROM clients 
      WHERE is_active = true 
      ORDER BY 
        CASE WHEN is_featured THEN 0 ELSE 1 END,
        display_order ASC, created_at DESC
      LIMIT $1 OFFSET $2
    `, [limit, offset]);
    
    const clients = clientsResult.rows.map(client => ({
      ...client,
      industry: getBilingualContent(req, client.industry_en, client.industry_id),
      project_title: getBilingualContent(req, client.project_title_en, client.project_title_id),
      project_description: getBilingualContent(req, client.project_description_en, client.project_description_id),
      case_study: getBilingualContent(req, client.case_study_en, client.case_study_id),
      testimonial: getBilingualContent(req, client.testimonial_en, client.testimonial_id)
    }));
    
    // Get total count for pagination
    const countResult = await db.query('SELECT COUNT(*) as total FROM clients WHERE is_active = true');
    const totalClients = parseInt(countResult.rows[0].total);
    const totalPages = Math.ceil(totalClients / limit);
    
    res.render('public/portfolio', {
      title: `${res.locals.__('nav_portfolio')} - Indo Advisory`,
      clients,
      currentPage: page,
      totalPages,
      totalClients
    });
    
  } catch (error) {
    console.error('Portfolio page error:', error);
    res.render('public/portfolio', {
      title: 'Portfolio - Indo Advisory',
      clients: [],
      currentPage: 1,
      totalPages: 0,
      totalClients: 0
    });
  }
});

// Insights/Articles page
router.get('/insights', async (req, res) => {
  try {
    const page = parseInt(req.query.page) || 1;
    const category = req.query.category;
    const limit = 10;
    const offset = (page - 1) * limit;
    
    let whereConditions = ['a.is_published = true'];
    let params = [limit, offset];
    let paramCount = 2;
    
    if (category) {
      paramCount++;
      whereConditions.push(`(a.category_en ILIKE $${paramCount} OR a.category_id ILIKE $${paramCount})`);
      params.push(`%${category}%`);
    }
    
    const whereClause = `WHERE ${whereConditions.join(' AND ')}`;
    
    // Get published articles
    const articlesResult = await db.query(`
      SELECT 
        a.slug, a.title_en, a.title_id, a.summary_en, a.summary_id,
        a.featured_image_url, a.published_at, a.category_en, a.category_id,
        a.view_count, a.is_featured,
        u.name as author_name
      FROM articles a
      LEFT JOIN users u ON a.author_id = u.id
      ${whereClause}
      ORDER BY 
        CASE WHEN a.is_featured THEN 0 ELSE 1 END,
        a.published_at DESC
      LIMIT $1 OFFSET $2
    `, params);
    
    const articles = articlesResult.rows.map(article => ({
      ...article,
      title: getBilingualContent(req, article.title_en, article.title_id),
      summary: getBilingualContent(req, article.summary_en, article.summary_id),
      category: getBilingualContent(req, article.category_en, article.category_id)
    }));
    
    // Get total count for pagination
    const countParams = category ? [category] : [];
    const countResult = await db.query(`
      SELECT COUNT(*) as total FROM articles a ${whereClause.replace('LIMIT $1 OFFSET $2', '')}
    `, countParams);
    const totalArticles = parseInt(countResult.rows[0].total);
    const totalPages = Math.ceil(totalArticles / limit);
    
    // Get categories for filter
    const categoriesResult = await db.query(`
      SELECT DISTINCT category_en, category_id 
      FROM articles 
      WHERE is_published = true AND category_en IS NOT NULL
      ORDER BY category_en
    `);
    
    const categories = categoriesResult.rows.map(cat => ({
      value_en: cat.category_en,
      value_id: cat.category_id,
      name: getBilingualContent(req, cat.category_en, cat.category_id)
    }));
    
    res.render('public/insights', {
      title: `${res.locals.__('nav_insights')} - Indo Advisory`,
      articles,
      categories,
      currentPage: page,
      totalPages,
      totalArticles,
      currentCategory: category
    });
    
  } catch (error) {
    console.error('Insights page error:', error);
    res.render('public/insights', {
      title: 'Insights - Indo Advisory',
      articles: [],
      categories: [],
      currentPage: 1,
      totalPages: 0,
      totalArticles: 0,
      currentCategory: null
    });
  }
});

// Single article page
router.get('/insights/:slug', async (req, res) => {
  try {
    const { slug } = req.params;
    
    const articleResult = await db.query(`
      SELECT 
        a.*,
        u.name as author_name
      FROM articles a
      LEFT JOIN users u ON a.author_id = u.id
      WHERE a.slug = $1 AND a.is_published = true
    `, [slug]);
    
    if (articleResult.rows.length === 0) {
      return res.status(404).render('error', {
        title: 'Article Not Found',
        message: 'The article you are looking for does not exist or has been removed.',
        error: { status: 404 }
      });
    }
    
    const article = articleResult.rows[0];
    
    // Increment view count
    await db.query(
      'UPDATE articles SET view_count = view_count + 1 WHERE id = $1',
      [article.id]
    );
    
    // Process bilingual content
    const processedArticle = {
      ...article,
      title: getBilingualContent(req, article.title_en, article.title_id),
      summary: getBilingualContent(req, article.summary_en, article.summary_id),
      content: getBilingualContent(req, article.content_en, article.content_id),
      category: getBilingualContent(req, article.category_en, article.category_id),
      seo_title: getBilingualContent(req, article.seo_title_en, article.seo_title_id),
      seo_description: getBilingualContent(req, article.seo_description_en, article.seo_description_id)
    };
    
    // Get related articles
    const relatedResult = await db.query(`
      SELECT 
        slug, title_en, title_id, summary_en, summary_id,
        featured_image_url, published_at, category_en, category_id
      FROM articles
      WHERE id != $1 AND is_published = true 
        AND (category_en = $2 OR category_id = $3)
      ORDER BY published_at DESC
      LIMIT 3
    `, [article.id, article.category_en, article.category_id]);
    
    const relatedArticles = relatedResult.rows.map(related => ({
      ...related,
      title: getBilingualContent(req, related.title_en, related.title_id),
      summary: getBilingualContent(req, related.summary_en, related.summary_id),
      category: getBilingualContent(req, related.category_en, related.category_id)
    }));
    
    res.render('public/article', {
      title: `${processedArticle.seo_title || processedArticle.title} - Indo Advisory`,
      article: processedArticle,
      relatedArticles
    });
    
  } catch (error) {
    console.error('Article page error:', error);
    res.status(500).render('error', {
      title: 'Error',
      message: 'An error occurred while loading the article.',
      error: {}
    });
  }
});

// Contact page
router.get('/contact', async (req, res) => {
  try {
    // Get company contact settings
    const settingsResult = await db.query(`
      SELECT key, value_en, value_id FROM settings 
      WHERE key IN ('contact_email', 'contact_phone', 'office_address_en') AND is_public = true
    `);
    
    const settings = {};
    settingsResult.rows.forEach(row => {
      const value = getBilingualContent(req, row.value_en, row.value_id);
      settings[row.key] = value;
    });
    
    res.render('public/contact', {
      title: `${res.locals.__('nav_contact')} - Indo Advisory`,
      settings
    });
    
  } catch (error) {
    console.error('Contact page error:', error);
    res.render('public/contact', {
      title: 'Contact - Indo Advisory',
      settings: {}
    });
  }
});

// Contact form submission
router.post('/contact',
  contactRateLimit,
  [
    validateName,
    validateEmail,
    validatePhone,
    body('company')
      .optional()
      .trim()
      .isLength({ max: 255 })
      .withMessage('Company name must be less than 255 characters'),
    body('subject')
      .trim()
      .isLength({ min: 5, max: 500 })
      .withMessage('Subject must be between 5 and 500 characters'),
    body('message')
      .trim()
      .isLength({ min: 10, max: 2000 })
      .withMessage('Message must be between 10 and 2000 characters')
  ],
  checkValidationResult,
  async (req, res) => {
    try {
      const { name, email, phone, company, subject, message } = req.body;
      
      // Get client IP and user agent for security
      const clientIP = req.ip || req.connection.remoteAddress;
      const userAgent = req.get('User-Agent');
      
      // Insert inquiry into database
      await db.query(`
        INSERT INTO inquiries (
          name, email, phone, company, subject, message, 
          inquiry_type, ip_address, user_agent
        ) VALUES ($1, $2, $3, $4, $5, $6, 'general', $7, $8)
      `, [name, email, phone, company, subject, message, clientIP, userAgent]);
      
      req.flash('success', res.locals.__('contact_success'));
      res.redirect('/contact');
      
    } catch (error) {
      console.error('Contact form error:', error);
      req.flash('error', 'An error occurred while sending your message. Please try again.');
      res.redirect('/contact');
    }
  }
);

// Language switcher
router.get('/lang/:lang', (req, res) => {
  const { lang } = req.params;
  const returnTo = req.get('Referer') || '/';
  
  if (['en', 'id'].includes(lang)) {
    req.session.lang = lang;
  }
  
  res.redirect(returnTo);
});

// API endpoints for frontend JavaScript

// Get clients for showcase carousel
router.get('/api/showcase/clients', async (req, res) => {
  try {
    const clientsResult = await db.query(`
      SELECT 
        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
      FROM clients 
      WHERE is_showcase = true AND is_active = true 
      ORDER BY display_order ASC
    `);
    
    const clients = clientsResult.rows.map(client => ({
      company_name: client.company_name,
      industry: getBilingualContent(req, client.industry_en, client.industry_id),
      logo_url: client.logo_url,
      website_url: client.website_url,
      project_title: getBilingualContent(req, client.project_title_en, client.project_title_id),
      project_description: getBilingualContent(req, client.project_description_en, client.project_description_id),
      project_value: client.project_value,
      project_year: client.project_year
    }));
    
    res.json({
      success: true,
      clients
    });
    
  } catch (error) {
    console.error('API showcase clients error:', error);
    res.status(500).json({
      success: false,
      message: 'Error loading clients'
    });
  }
});

module.exports = router;