const pool = require('../config/database');

const User = {
  /**
   * Get all active users with pagination and search
   */
  getAll: async (page = 1, limit = 10, search = '', startDate = null, endDate = null, filterType = 'all', subscriptionType = null) => {
    try {
      const offset = (page - 1) * limit;

      let query = `
        SELECT
          u.*,
          COALESCE(rc.report_count, 0) AS report_count
        FROM users u
        LEFT JOIN (
          SELECT user_id, COUNT(*) AS report_count
          FROM cart
          GROUP BY user_id
        ) rc ON rc.user_id = u.user_id
        WHERE 1=1
      `;
      let countQuery = 'SELECT COUNT(*) as total FROM users u WHERE 1=1';
      let params = [];

      // Filter Type Logic (All vs Subscribed vs Deleted)
      if (filterType === 'deleted') {
        query += " AND u.status = 'deleted'";
        countQuery += " AND u.status = 'deleted'";
      } else if (filterType === 'subscribed') {
        query += " AND u.status != 'deleted' AND u.license_code != '' AND u.license_code IS NOT NULL AND u.license_code_status = 1";
        countQuery += " AND u.status != 'deleted' AND u.license_code != '' AND u.license_code IS NOT NULL AND u.license_code_status = 1";
      } else {
        query += " AND u.status != 'deleted'";
        countQuery += " AND u.status != 'deleted'";
      }

      if (subscriptionType && subscriptionType !== 'all') {
        query += ' AND u.subscription_type = ?';
        countQuery += ' AND u.subscription_type = ?';
        params.push(parseInt(subscriptionType, 10));
      }

      // Add search filter
      if (search && search.trim()) {
        const searchTerm = `%${search}%`;
        query += ' AND (u.first_name LIKE ? OR u.last_name LIKE ? OR u.email LIKE ?)';
        countQuery += ' AND (u.first_name LIKE ? OR u.last_name LIKE ? OR u.email LIKE ?)';
        params.push(searchTerm, searchTerm, searchTerm);
      }

      // Add date range filter (using date_created)
      if (startDate) {
        query += ' AND DATE(u.date_created) >= ?';
        countQuery += ' AND DATE(u.date_created) >= ?';
        params.push(startDate);
      }
      if (endDate) {
        query += ' AND DATE(u.date_created) <= ?';
        countQuery += ' AND DATE(u.date_created) <= ?';
        params.push(endDate);
      }

      // Get total count
      const [countResult] = await pool.query(countQuery, params);
      const total = countResult[0].total;

      // Get paginated data
      const limitNum = parseInt(limit, 10) || 10;
      const offsetNum = parseInt(offset, 10) || 0;

      query += ' ORDER BY u.date_created DESC LIMIT ? OFFSET ?';
      const [rows] = await pool.query(query, [...params, limitNum, offsetNum]);

      return {
        data: rows,
        pagination: {
          total,
          page: parseInt(page, 10),
          limit: limitNum,
          pages: Math.ceil(total / limitNum),
        },
      };
    } catch (error) {
      throw new Error(`Database error: ${error.message}`);
    }
  },

  /**
   * Get user by id
   */
  findById: async (id) => {
    try {
      const [rows] = await pool.query(
        'SELECT * FROM users WHERE user_id = ?',
        [id]
      );
      return rows.length > 0 ? rows[0] : null;
    } catch (error) {
      throw new Error(`Database error: ${error.message}`);
    }
  },

  /**
   * Create new user with full details
   */
  create: async (data) => {
    try {
      const allowedFields = [
        'first_name', 'last_name', 'email', 'phone', 'status', 'middle_name',
        'password', 'country', 'website', 'dob', 'active_status', 'gender',
        'curnt_first_name', 'curnt_middle_name', 'curnt_last_name',
        'promotional_emails', 'license_code',
        'license_code_status', 'license_date', 'subscription_type', 'role', 'notes'
      ];

      const fields = [];
      const values = [];
      const placeholders = [];

      // Always set dates
      fields.push('date_created', 'date_updated');
      values.push(new Date(), new Date());
      placeholders.push('?', '?');

      for (const field of allowedFields) {
        if (data[field] !== undefined) {
          fields.push(field);
          values.push(data[field]);
          placeholders.push('?');
        }
      }

      // Default Status if not provided
      if (!data.status) {
        fields.push('status');
        values.push('1');
        placeholders.push('?');
      }

      const query = `INSERT INTO users (${fields.join(', ')}) VALUES (${placeholders.join(', ')})`;

      const [result] = await pool.query(query, values);
      return result.insertId;
    } catch (error) {
      if (error.code === 'ER_DUP_ENTRY') {
        throw new Error('Email already exists');
      }
      throw new Error(`Database error: ${error.message}`);
    }
  },

  /**
   * Update user
   */
  update: async (id, data) => {
    try {
      // Allowed fields for update
      const allowedFields = [
        'first_name', 'last_name', 'email', 'phone', 'status', 'middle_name',
        'password', 'country', 'website', 'dob', 'active_status', 'gender',
        'curnt_first_name', 'curnt_middle_name', 'curnt_last_name',
        'promotional_emails', 'license_code',
        'license_code_status', 'license_date', 'subscription_type', 'role', 'notes'
        // autorenew removed
      ];

      const updates = [];
      const params = [];

      // Check if data is object or individual params (backward compatibility if needed, but switching to object)
      if (typeof data === 'object') {
        for (const field of allowedFields) {
          if (data[field] !== undefined) {
            updates.push(`${field} = ?`);
            params.push(data[field]);
          }
        }
      }

      updates.push('date_updated = NOW()');

      if (updates.length === 1) { // Only date_updated
        return false;
      }

      params.push(id);
      const query = `UPDATE users SET ${updates.join(', ')} WHERE user_id = ?`;
      const [result] = await pool.query(query, params);

      return result.affectedRows > 0;
    } catch (error) {
      if (error.code === 'ER_DUP_ENTRY') {
        throw new Error('Email already exists');
      }
      throw error;
    }
  },

  /**
   * Soft delete user (set status to deleted)
   */
  delete: async (id) => {
    try {
      const [result] = await pool.query(
        'UPDATE users SET status = "deleted", date_updated = NOW() WHERE user_id = ?',
        [id]
      );
      return result.affectedRows > 0;
    } catch (error) {
      throw new Error(`Database error: ${error.message}`);
    }
  },

  /**
   * Restore user (set status back to 1)
   */
  restore: async (id) => {
    try {
      const [result] = await pool.query(
        'UPDATE users SET status = "1", date_updated = NOW() WHERE user_id = ?',
        [id]
      );
      return result.affectedRows > 0;
    } catch (error) {
      throw new Error(`Database error: ${error.message}`);
    }
  },

  /**
   * Get dashboard metrics
   */
  getDashboardMetrics: async () => {
    try {
      const queries = {
        // User Accounts (Role 1): role == 1 AND status == 1
        user_accounts_role_1: "SELECT COUNT(*) as count FROM users WHERE role = 1 AND status = '1'",

        // All Users counts (all user count) (status == 1 users only)
        all_users_active: "SELECT COUNT(*) as count FROM users WHERE status = '1'",

        // New Users Yesterday(date_created)
        new_users_yesterday: "SELECT COUNT(*) as count FROM users WHERE date_created >= DATE_SUB(CURDATE(), INTERVAL 1 DAY) AND date_created < CURDATE()",

        // New Users Last 7 days(date_created)
        new_users_last_7_days: "SELECT COUNT(*) as count FROM users WHERE date_created >= DATE_SUB(CURDATE(), INTERVAL 7 DAY)",

        // New Subscription Yesterday(license_date)
        // Subscription condition: license_code not empty, license_code_status === 1
        new_subs_yesterday: "SELECT COUNT(*) as count FROM users WHERE license_date >= DATE_SUB(CURDATE(), INTERVAL 1 DAY) AND license_date < CURDATE() AND license_code != '' AND license_code IS NOT NULL AND license_code_status = 1",

        // New Subscription Last 7 days(license_date)
        new_subs_last_7_days: "SELECT COUNT(*) as count FROM users WHERE license_date >= DATE_SUB(CURDATE(), INTERVAL 7 DAY) AND license_code != '' AND license_code IS NOT NULL AND license_code_status = 1",

        // Total subscription(license_date) count (implies active subs)
        total_subs: "SELECT COUNT(*) as count FROM users WHERE license_code != '' AND license_code IS NOT NULL AND license_code_status = 1",

        // Overall purchased reports count from cart table
        overall_report_counts: 'SELECT COUNT(*) as count FROM cart',

        // Active subscribers by subscription_type
        subscription_type_1: "SELECT COUNT(*) as count FROM users WHERE license_code != '' AND license_code IS NOT NULL AND license_code_status = 1 AND subscription_type = 1",
        subscription_type_2: "SELECT COUNT(*) as count FROM users WHERE license_code != '' AND license_code IS NOT NULL AND license_code_status = 1 AND subscription_type = 2",
        subscription_type_3: "SELECT COUNT(*) as count FROM users WHERE license_code != '' AND license_code IS NOT NULL AND license_code_status = 1 AND subscription_type = 3",
        subscription_type_4: "SELECT COUNT(*) as count FROM users WHERE license_code != '' AND license_code IS NOT NULL AND license_code_status = 1 AND subscription_type = 4",
        subscription_type_5: "SELECT COUNT(*) as count FROM users WHERE license_code != '' AND license_code IS NOT NULL AND license_code_status = 1 AND subscription_type = 5",
        subscription_type_6: "SELECT COUNT(*) as count FROM users WHERE license_code != '' AND license_code IS NOT NULL AND license_code_status = 1 AND subscription_type = 6",
        subscription_type_7: "SELECT COUNT(*) as count FROM users WHERE license_code != '' AND license_code IS NOT NULL AND license_code_status = 1 AND subscription_type = 7"
      };

      const results = {};
      for (const [key, query] of Object.entries(queries)) {
        const [rows] = await pool.query(query);
        results[key] = rows[0].count;
      }

      return results;
    } catch (error) {
      throw error;
    }
  },

  /**
   * Get user statistics (Deprecated/Legacy, redirects to dashboard metrics or simple count)
   */
  getStats: async () => {
    // Re-using getDashboardMetrics logic for consistency if called
    return await User.getDashboardMetrics();
  },

  /**
   * Add subscription to user
   */
  addSubscription: async (userId, planName, startDate, endDate, status = 'active') => {
    try {
      const [result] = await pool.query(
        'INSERT INTO subscriptions (user_id, plan_name, start_date, end_date, status) VALUES (?, ?, ?, ?, ?)',
        [userId, planName, startDate, endDate, status]
      );
      return result.insertId;
    } catch (error) {
      throw new Error(`Database error: ${error.message}`);
    }
  },

  /**
   * Get user subscriptions
   */
  getSubscriptions: async (userId) => {
    try {
      const [rows] = await pool.query(
        'SELECT * FROM subscriptions WHERE user_id = ? ORDER BY created_at DESC',
        [userId]
      );
      return rows;
    } catch (error) {
      throw new Error(`Database error: ${error.message}`);
    }
  },
};

module.exports = User;
