const { Op } = require('sequelize');
const db = require('../db/models');
const moment = require('moment');

module.exports = class DashboardService {
  static async getBusinessMetrics(currentUser) {
    // 1. Get businesses owned by current user
    const businesses = await db.businesses.findAll({
      where: { owner_userId: currentUser.id },
      attributes: ['id', 'name', 'planId', 'renewal_date', 'reliability_score', 'description', 'phone', 'website', 'address', 'hours_json', 'is_active'],
      include: [{ model: db.plans, as: 'plan' }]
    });

    if (!businesses.length) {
      return { 
        no_business: true,
        totalViews: 0,
        activeLeads: 0,
        conversionRate: 0,
        verificationStatus: 'N/A',
        accountStanding: 'N/A'
      };
    }

    const businessIds = businesses.map(b => b.id);
    const last24h = moment().subtract(24, 'hours').toDate();
    const last30d = moment().subtract(30, 'days').toDate();
    const last7d = moment().subtract(7, 'days').toDate();

    // --- Action Queue ---
    // New leads last 24h
    const newLeads24h = await db.lead_matches.count({
      where: {
        businessId: { [Op.in]: businessIds },
        createdAt: { [Op.gte]: last24h }
      }
    });

    // Leads needing response (status NEW in lead_matches OR unread messages)
    const leadsNeedingResponse = await db.lead_matches.count({
      where: {
        businessId: { [Op.in]: businessIds },
        [Op.or]: [
          { status: 'SENT' }, // SENT means it's new for the business
          { leadId: { [Op.in]: db.sequelize.literal(`(SELECT "leadId" FROM messages WHERE "receiver_userId" = '${currentUser.id}' AND "read_at" IS NULL)`) } }
        ]
      }
    });

    const verificationPending = await db.verification_submissions.count({
      where: {
        businessId: { [Op.in]: businessIds },
        status: 'PENDING'
      }
    });

    // --- Lead Pipeline Snapshot ---
    const pipelineStats = await db.lead_matches.findAll({
      where: { businessId: { [Op.in]: businessIds } },
      attributes: [
        'status',
        [db.sequelize.fn('COUNT', db.sequelize.col('id')), 'count']
      ],
      group: ['status']
    });

    const pipeline = pipelineStats.reduce((acc, curr) => {
      acc[curr.status] = parseInt(curr.get('count'));
      return acc;
    }, { SENT: 0, VIEWED: 0, RESPONDED: 0, SCHEDULED: 0, COMPLETED: 0, DECLINED: 0 });

    const won30d = await db.lead_matches.count({
      where: {
        businessId: { [Op.in]: businessIds },
        status: 'COMPLETED',
        updatedAt: { [Op.gte]: last30d }
      }
    });
    const lost30d = await db.lead_matches.count({
      where: {
        businessId: { [Op.in]: businessIds },
        status: 'DECLINED',
        updatedAt: { [Op.gte]: last30d }
      }
    });
    const winRate30d = (won30d + lost30d) > 0 ? Math.round((won30d / (won30d + lost30d)) * 100) : 0;

    // --- Recent Messages ---
    const recentMessages = await db.messages.findAll({
      where: {
        [Op.or]: [
          { sender_userId: currentUser.id },
          { receiver_userId: currentUser.id }
        ]
      },
      limit: 5,
      order: [['createdAt', 'DESC']],
      include: [
        { model: db.leads, as: 'lead' },
        { model: db.users, as: 'sender_user', attributes: ['firstName', 'lastName'] }
      ]
    });

    // --- Performance ---
    const getEventCount = async (type, since) => {
      return await db.listing_events.count({
        where: {
          businessId: { [Op.in]: businessIds },
          event_type: type,
          createdAt: { [Op.gte]: since }
        }
      });
    };

    const views7d = await getEventCount('VIEW', last7d);
    const views30d = await getEventCount('VIEW', last30d);
    const calls7d = await getEventCount('CALL_CLICK', last7d);
    const calls30d = await getEventCount('CALL_CLICK', last30d);
    const website7d = await getEventCount('WEBSITE_CLICK', last7d);
    const website30d = await getEventCount('WEBSITE_CLICK', last30d);

    const totalClicks30d = calls30d + website30d;
    const viewConversionRate = views30d > 0 ? Math.round((totalClicks30d / views30d) * 100) : 0;

    // --- Health Score ---
    const firstBusiness = businesses[0];
    let healthScore = 0;
    const missingFields = [];
    const fieldsToCheck = [
      { name: 'name', weight: 10, label: 'Name' },
      { name: 'description', weight: 20, label: 'Description' },
      { name: 'phone', weight: 10, label: 'Phone' },
      { name: 'website', weight: 10, label: 'Website' },
      { name: 'address', weight: 10, label: 'Address' },
      { name: 'hours_json', weight: 10, label: 'Business Hours' },
    ];
    
    fieldsToCheck.forEach(f => {
      if (firstBusiness[f.name] && firstBusiness[f.name] !== '') {
        healthScore += f.weight;
      } else {
        missingFields.push(f.label);
      }
    });

    const photoCount = await db.business_photos.count({ where: { businessId: firstBusiness.id } });
    if (photoCount > 0) healthScore += 15; else missingFields.push('Photos');
    
    const categoryCount = await db.business_categories.count({ where: { businessId: firstBusiness.id } });
    if (categoryCount > 0) healthScore += 10; else missingFields.push('Categories');

    const priceCount = await db.service_prices.count({ where: { businessId: firstBusiness.id } });
    if (priceCount > 0) healthScore += 5; else missingFields.push('Service Prices');

    // --- Verification & Standing ---
    const lastSubmission = await db.verification_submissions.findOne({
      where: { businessId: { [Op.in]: businessIds } },
      order: [['createdAt', 'DESC']]
    });

    let verificationStatus = 'Not Started';
    if (lastSubmission) {
      verificationStatus = lastSubmission.status.charAt(0).toUpperCase() + lastSubmission.status.slice(1);
    }

    let accountStanding = 'Good';
    if (firstBusiness.reliability_score < 70) accountStanding = 'Reviewing';
    if (firstBusiness.reliability_score < 40) accountStanding = 'At Risk';

    return {
      totalViews: views30d,
      activeLeads: newLeads24h,
      conversionRate: winRate30d,
      viewConversionRate,
      verificationStatus,
      verificationSubtext: lastSubmission?.status === 'APPROVED' ? 'Identity & Business verified' : 'Complete verification to build trust',
      accountStanding,
      accountStandingSubtext: accountStanding === 'Good' ? 'Perfect track record' : 'Contact support for details',
      businesses,
      action_queue: {
        newLeads24h,
        leadsNeedingResponse,
        verificationPending,
        missingFields
      },
      pipeline: {
        NEW: pipeline.SENT,
        CONTACTED: pipeline.VIEWED + pipeline.RESPONDED,
        SCHEDULED: pipeline.SCHEDULED,
        WON: pipeline.COMPLETED,
        LOST: pipeline.DECLINED,
        winRate30d
      },
      recentMessages,
      performance: {
        views7d,
        views30d,
        calls7d,
        calls30d,
        website7d,
        website30d,
        conversionRate: winRate30d
      },
      healthScore: Math.min(healthScore, 100)
    };
  }

  static async getAdminMetrics() {
    const totalUsers = await db.users.count();
    const totalBusinesses = await db.businesses.count();
    
    // Revenue as sum of prices of plans currently active on businesses
    const businessesWithPlans = await db.businesses.findAll({
      where: { planId: { [Op.ne]: null } },
      include: [{ model: db.plans, as: 'plan' }]
    });

    const totalRevenue = businessesWithPlans.reduce((acc, curr) => {
      return acc + (curr.plan ? parseFloat(curr.plan.price) : 0);
    }, 0);

    return {
      totalUsers,
      totalBusinesses,
      totalRevenue
    };
  }
};