// MySQL Database Service Layer
import {
  executeQuery,
  executeQuerySingle,
  executeInsert,
  executeUpdate,
  executeTransaction,
  buildWhereClause,
  buildUpdateClause,
  buildInsertClause,
  buildPaginationClause,
  buildSearchClause,
} from './mysql-config';

// Base interfaces
export interface BaseEntity {
  id: number;
  created_at: Date;
  updated_at: Date;
}

export interface PaginationOptions {
  page?: number;
  limit?: number;
  orderBy?: string;
  orderDirection?: 'ASC' | 'DESC';
}

export interface SearchOptions {
  searchTerm?: string;
  searchFields?: string[];
}

export interface QueryResult<T> {
  data: T[];
  total: number;
  page: number;
  limit: number;
  totalPages: number;
}

// Shop entity
export interface Shop extends BaseEntity {
  shop_name: string;
  email: string;
  address?: string;
  contact?: string;
  logo_url?: string;
  owner_id?: number;
}

// User entity
export interface User extends BaseEntity {
  email: string;
  password_hash: string;
  display_name?: string;
  phone?: string;
  role: 'owner' | 'manager' | 'employee';
  shop_id: number;
  is_active: boolean;
  last_login?: Date;
}

// Customer entity
export interface Customer extends BaseEntity {
  name: string;
  phone: string;
  email?: string;
  address?: string;
  shop_id: number;
  total_orders: number;
  total_spent: number;
  last_order_date?: Date;
  notes?: string;
}

// Customer Measurement entity
export interface CustomerMeasurement extends BaseEntity {
  customer_id: number;
  item_name: string;
  measurements: string;
  notes?: string;
  shop_id: number;
}

// Inventory entity
export interface InventoryItem extends BaseEntity {
  name: string;
  type: 'Cotton' | 'Silk' | 'Wool' | 'Linen' | 'Other';
  color: string;
  price_per_meter: number;
  quantity: number;
  low_stock_threshold: number;
  supplier?: string;
  purchase_date?: Date;
  expiry_date?: Date;
  shop_id: number;
}

// Tailoring Item entity
export interface TailoringItem extends BaseEntity {
  name: string;
  category: 'Men' | 'Women' | 'Kids' | 'Other';
  sewing_price: number;
  artisan_wage: number;
  description?: string;
  estimated_time_hours?: number;
  is_active: boolean;
  shop_id: number;
}

// Employee entity
export interface Employee extends BaseEntity {
  name: string;
  phone: string;
  email?: string;
  role: 'Tailor' | 'Manager' | 'Cutter' | 'Assistant';
  monthly_salary?: number;
  hire_date: Date;
  is_active: boolean;
  address?: string;
  emergency_contact?: string;
  shop_id: number;
}

// Order entity
export interface Order extends BaseEntity {
  memo_id?: string;
  customer_id: number;
  customer_name: string;
  customer_phone: string;
  type: 'pickup' | 'delivery';
  status: 'New' | 'In Progress' | 'Ready for Delivery' | 'Delivered' | 'Completed' | 'Cancelled';
  order_date: Date;
  delivery_date?: Date;
  delivery_address?: string;
  total_amount: number;
  paid_amount: number;
  total_artisan_wage: number;
  artisan_id?: number;
  artisan_name?: string;
  cutting_master_id?: number;
  cutting_master_name?: string;
  artisan_assigned_date?: Date;
  artisan_completed_date?: Date;
  cutting_completed_date?: Date;
  priority: 'low' | 'medium' | 'high';
  notes?: string;
  shop_id: number;
}

// Order Item entity
export interface OrderItem {
  id: number;
  order_id: number;
  name: string;
  quantity: number;
  price: number;
  artisan_wage: number;
  measurements?: string;
  fabric_used?: number;
  notes?: string;
  created_at: Date;
}

// Transaction entities
export interface IncomeTransaction extends BaseEntity {
  category: string;
  amount: number;
  description?: string;
  transaction_date: Date;
  order_id?: number;
  payment_method: 'cash' | 'card' | 'upi' | 'bank_transfer' | 'other';
  is_editable: boolean;
  shop_id: number;
}

export interface ExpenseTransaction extends BaseEntity {
  category: string;
  amount: number;
  description?: string;
  person?: string;
  transaction_date: Date;
  employee_id?: number;
  receipt_url?: string;
  payment_method: 'cash' | 'card' | 'upi' | 'bank_transfer' | 'other';
  is_editable: boolean;
  shop_id: number;
}

// Settings entities
export interface AppearanceSettings extends BaseEntity {
  theme: 'light' | 'dark';
  font: 'inter' | 'manrope' | 'system' | 'poppins' | 'pt-sans';
  shop_id: number;
}

export interface NotificationSettings extends BaseEntity {
  sms_enabled: boolean;
  sms_provider: 'twilio' | 'other';
  sms_api_key?: string;
  sms_api_secret?: string;
  sms_sender_id?: string;
  sms_message_template?: string;
  whatsapp_enabled: boolean;
  whatsapp_api_key?: string;
  whatsapp_sender_number?: string;
  whatsapp_message_template?: string;
  shop_id: number;
}

// Generic service class
export class DatabaseService<T extends BaseEntity> {
  constructor(private tableName: string) {}

  // Create
  async create(data: Omit<T, 'id' | 'created_at' | 'updated_at'>): Promise<T> {
    const { columns, placeholders, params } = buildInsertClause(data as any);
    const query = `INSERT INTO ${this.tableName} (${columns}) VALUES (${placeholders})`;
    
    const id = await executeInsert(query, params);
    return this.findById(id);
  }

  // Find by ID
  async findById(id: number): Promise<T | null> {
    const query = `SELECT * FROM ${this.tableName} WHERE id = ?`;
    return executeQuerySingle<T>(query, [id]);
  }

  // Update
  async update(id: number, data: Partial<Omit<T, 'id' | 'created_at'>>): Promise<T | null> {
    const { clause, params } = buildUpdateClause(data as any);
    const query = `UPDATE ${this.tableName} SET ${clause} WHERE id = ?`;
    
    await executeUpdate(query, [...params, id]);
    return this.findById(id);
  }

  // Delete
  async delete(id: number): Promise<boolean> {
    const query = `DELETE FROM ${this.tableName} WHERE id = ?`;
    const affectedRows = await executeUpdate(query, [id]);
    return affectedRows > 0;
  }

  // Find all with filters
  async findAll(
    filters: Record<string, any> = {},
    pagination: PaginationOptions = {},
    search: SearchOptions = {}
  ): Promise<QueryResult<T>> {
    const { page = 1, limit = 10, orderBy = 'id', orderDirection = 'DESC' } = pagination;
    
    // Build WHERE clause
    let whereClause = '';
    let params: any[] = [];
    
    if (Object.keys(filters).length > 0) {
      const { clause, params: filterParams } = buildWhereClause(filters);
      whereClause = clause;
      params = filterParams;
    }
    
    // Add search clause
    if (search.searchTerm && search.searchFields) {
      const { clause: searchClause, params: searchParams } = buildSearchClause(
        search.searchTerm,
        search.searchFields
      );
      
      if (searchClause) {
        whereClause = whereClause 
          ? `${whereClause} AND ${searchClause}`
          : `WHERE ${searchClause}`;
        params = [...params, ...searchParams];
      }
    }
    
    // Get total count
    const countQuery = `SELECT COUNT(*) as total FROM ${this.tableName} ${whereClause}`;
    const countResult = await executeQuerySingle<{ total: number }>(countQuery, params);
    const total = countResult?.total || 0;
    
    // Get paginated data
    const { clause: paginationClause } = buildPaginationClause(page, limit, orderBy, orderDirection);
    const dataQuery = `SELECT * FROM ${this.tableName} ${whereClause} ${paginationClause}`;
    const data = await executeQuery<T>(dataQuery, params);
    
    return {
      data,
      total,
      page,
      limit,
      totalPages: Math.ceil(total / limit),
    };
  }

  // Find by shop ID
  async findByShop(
    shopId: number,
    pagination: PaginationOptions = {},
    search: SearchOptions = {}
  ): Promise<QueryResult<T>> {
    return this.findAll({ shop_id: shopId }, pagination, search);
  }
}

// Specific service instances
export const shopService = new DatabaseService<Shop>('shops');
export const userService = new DatabaseService<User>('users');
export const customerService = new DatabaseService<Customer>('customers');
export const customerMeasurementService = new DatabaseService<CustomerMeasurement>('customer_measurements');
export const inventoryService = new DatabaseService<InventoryItem>('inventory');
export const tailoringItemService = new DatabaseService<TailoringItem>('tailoring_items');
export const employeeService = new DatabaseService<Employee>('employees');
export const orderService = new DatabaseService<Order>('orders');
export const incomeTransactionService = new DatabaseService<IncomeTransaction>('income_transactions');
export const expenseTransactionService = new DatabaseService<ExpenseTransaction>('expense_transactions');
export const appearanceSettingsService = new DatabaseService<AppearanceSettings>('appearance_settings');
export const notificationSettingsService = new DatabaseService<NotificationSettings>('notification_settings');

// Extended services with custom methods

// Customer Service Extensions
export class CustomerServiceExtended extends DatabaseService<Customer> {
  constructor() {
    super('customers');
  }

  async searchCustomers(searchTerm: string, shopId: number): Promise<Customer[]> {
    const query = `
      SELECT * FROM customers 
      WHERE shop_id = ? AND (
        name LIKE ? OR 
        phone LIKE ? OR 
        email LIKE ?
      )
      ORDER BY name ASC
    `;
    const searchPattern = `%${searchTerm}%`;
    return executeQuery<Customer>(query, [shopId, searchPattern, searchPattern, searchPattern]);
  }

  async getTopCustomers(shopId: number, limit: number = 10): Promise<Customer[]> {
    const query = `
      SELECT * FROM customers 
      WHERE shop_id = ? 
      ORDER BY total_spent DESC 
      LIMIT ?
    `;
    return executeQuery<Customer>(query, [shopId, limit]);
  }
}

// Order Service Extensions
export class OrderServiceExtended extends DatabaseService<Order> {
  constructor() {
    super('orders');
  }

  async getOrdersWithItems(shopId: number, orderId?: number): Promise<any[]> {
    let whereClause = 'WHERE o.shop_id = ?';
    let params = [shopId];
    
    if (orderId) {
      whereClause += ' AND o.id = ?';
      params.push(orderId);
    }
    
    const query = `
      SELECT 
        o.*,
        JSON_ARRAYAGG(
          JSON_OBJECT(
            'id', oi.id,
            'name', oi.name,
            'quantity', oi.quantity,
            'price', oi.price,
            'artisan_wage', oi.artisan_wage,
            'measurements', oi.measurements,
            'fabric_used', oi.fabric_used,
            'notes', oi.notes
          )
        ) as items
      FROM orders o
      LEFT JOIN order_items oi ON o.id = oi.order_id
      ${whereClause}
      GROUP BY o.id
      ORDER BY o.order_date DESC
    `;
    
    return executeQuery(query, params);
  }

  async updateOrderStatus(
    orderId: number, 
    status: Order['status'], 
    userId: number
  ): Promise<Order | null> {
    return executeTransaction(async (connection) => {
      // Update order status
      const updateData: any = { status };
      
      // Add appropriate timestamps
      const now = new Date();
      switch (status) {
        case 'In Progress':
          updateData.artisan_assigned_date = now;
          break;
        case 'Ready for Delivery':
          updateData.artisan_completed_date = now;
          break;
        case 'Delivered':
        case 'Completed':
          if (!updateData.artisan_completed_date) {
            updateData.artisan_completed_date = now;
          }
          break;
      }
      
      const { clause, params } = buildUpdateClause(updateData);
      await connection.execute(
        `UPDATE orders SET ${clause} WHERE id = ?`,
        [...params, orderId]
      );
      
      // Log the change
      await connection.execute(
        `INSERT INTO audit_logs (action, entity_type, entity_id, user_id, user_name, changes, shop_id) 
         SELECT 'update', 'order', ?, ?, u.display_name, ?, o.shop_id 
         FROM orders o, users u 
         WHERE o.id = ? AND u.id = ?`,
        [orderId, userId, JSON.stringify({ status }), orderId, userId]
      );
      
      // Return updated order
      const [rows] = await connection.execute('SELECT * FROM orders WHERE id = ?', [orderId]);
      return (rows as any[])[0] || null;
    });
  }

  async getOrdersByStatus(shopId: number, status: Order['status']): Promise<Order[]> {
    const query = 'SELECT * FROM orders WHERE shop_id = ? AND status = ? ORDER BY order_date DESC';
    return executeQuery<Order>(query, [shopId, status]);
  }

  async getOrdersByDateRange(shopId: number, startDate: Date, endDate: Date): Promise<Order[]> {
    const query = `
      SELECT * FROM orders 
      WHERE shop_id = ? AND order_date BETWEEN ? AND ? 
      ORDER BY order_date DESC
    `;
    return executeQuery<Order>(query, [shopId, startDate, endDate]);
  }
}

// Order Items Service
export class OrderItemService {
  async createOrderItems(orderId: number, items: Omit<OrderItem, 'id' | 'order_id' | 'created_at'>[]): Promise<OrderItem[]> {
    return executeTransaction(async (connection) => {
      const createdItems: OrderItem[] = [];
      
      for (const item of items) {
        const { columns, placeholders, params } = buildInsertClause({
          ...item,
          order_id: orderId,
        });
        
        const [result] = await connection.execute(
          `INSERT INTO order_items (${columns}) VALUES (${placeholders})`,
          params
        );
        
        const insertId = (result as any).insertId;
        const [rows] = await connection.execute(
          'SELECT * FROM order_items WHERE id = ?',
          [insertId]
        );
        
        createdItems.push((rows as any[])[0]);
      }
      
      return createdItems;
    });
  }

  async getOrderItems(orderId: number): Promise<OrderItem[]> {
    const query = 'SELECT * FROM order_items WHERE order_id = ? ORDER BY id';
    return executeQuery<OrderItem>(query, [orderId]);
  }

  async updateOrderItem(id: number, data: Partial<OrderItem>): Promise<OrderItem | null> {
    const { clause, params } = buildUpdateClause(data);
    const query = `UPDATE order_items SET ${clause} WHERE id = ?`;
    
    await executeUpdate(query, [...params, id]);
    return executeQuerySingle<OrderItem>('SELECT * FROM order_items WHERE id = ?', [id]);
  }

  async deleteOrderItem(id: number): Promise<boolean> {
    const affectedRows = await executeUpdate('DELETE FROM order_items WHERE id = ?', [id]);
    return affectedRows > 0;
  }
}

// Inventory Service Extensions
export class InventoryServiceExtended extends DatabaseService<InventoryItem> {
  constructor() {
    super('inventory');
  }

  async getLowStockItems(shopId: number): Promise<InventoryItem[]> {
    const query = `
      SELECT * FROM inventory 
      WHERE shop_id = ? AND quantity <= low_stock_threshold 
      ORDER BY quantity ASC
    `;
    return executeQuery<InventoryItem>(query, [shopId]);
  }

  async updateQuantity(itemId: number, quantityChange: number): Promise<InventoryItem | null> {
    return executeTransaction(async (connection) => {
      // Get current quantity
      const [rows] = await connection.execute(
        'SELECT quantity FROM inventory WHERE id = ?',
        [itemId]
      );
      
      if ((rows as any[]).length === 0) {
        throw new Error('Inventory item not found');
      }
      
      const currentQuantity = (rows as any[])[0].quantity;
      const newQuantity = currentQuantity + quantityChange;
      
      if (newQuantity < 0) {
        throw new Error('Insufficient inventory');
      }
      
      // Update quantity
      await connection.execute(
        'UPDATE inventory SET quantity = ? WHERE id = ?',
        [newQuantity, itemId]
      );
      
      // Return updated item
      const [updatedRows] = await connection.execute(
        'SELECT * FROM inventory WHERE id = ?',
        [itemId]
      );
      
      return (updatedRows as any[])[0] || null;
    });
  }
}

// Statistics Service
export class StatisticsService {
  async getShopStatistics(shopId: number): Promise<any> {
    const query = 'CALL GetShopStatistics(?)';
    const results = await executeQuery(query, [shopId]);
    return results[0];
  }

  async getFinancialSummary(shopId: number, startDate: Date, endDate: Date): Promise<any> {
    const query = `
      SELECT 
        DATE(transaction_date) as date,
        SUM(CASE WHEN transaction_type = 'income' THEN amount ELSE 0 END) as income,
        SUM(CASE WHEN transaction_type = 'expense' THEN amount ELSE 0 END) as expenses,
        SUM(CASE WHEN transaction_type = 'income' THEN amount ELSE -amount END) as profit
      FROM (
        SELECT transaction_date, amount, 'income' as transaction_type FROM income_transactions WHERE shop_id = ?
        UNION ALL
        SELECT transaction_date, amount, 'expense' as transaction_type FROM expense_transactions WHERE shop_id = ?
      ) combined
      WHERE transaction_date BETWEEN ? AND ?
      GROUP BY DATE(transaction_date)
      ORDER BY date DESC
    `;
    
    return executeQuery(query, [shopId, shopId, startDate, endDate]);
  }
}

// Export service instances
export const customerServiceExtended = new CustomerServiceExtended();
export const orderServiceExtended = new OrderServiceExtended();
export const orderItemService = new OrderItemService();
export const inventoryServiceExtended = new InventoryServiceExtended();
export const statisticsService = new StatisticsService();