import db from "../db";

export class SilverPositionUserModel {
  /**
   * Initialize the silver_position_users table
   */
  static async initialize() {
    try {
      await db.query(`
        CREATE TABLE IF NOT EXISTS silver_position_users (
          id INT AUTO_INCREMENT PRIMARY KEY,
          wallet_address VARCHAR(42) UNIQUE NOT NULL,
          first_position_date DATETIME DEFAULT CURRENT_TIMESTAMP,
          last_position_date DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
          total_positions_opened INT DEFAULT 1,
          is_active BOOLEAN DEFAULT TRUE,
          created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
          updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
          INDEX idx_wallet (wallet_address),
          INDEX idx_active (is_active)
        )
      `);

      console.info("✅ silver_position_users table initialized successfully");
    } catch (error) {
      console.error("❌ Error initializing silver_position_users table:", error);
    }
  }

  /**
   * Add or update a user when they open a position
   */
  static async addUser(walletAddress: string) {
    const normalizedAddress = walletAddress.toLowerCase();
    
    try {
      await db.query(
        `
        INSERT INTO silver_position_users (wallet_address, total_positions_opened) 
        VALUES (?, 1)
        ON DUPLICATE KEY UPDATE 
          total_positions_opened = total_positions_opened + 1,
          last_position_date = CURRENT_TIMESTAMP,
          is_active = TRUE
        `,
        [normalizedAddress]
      );
      
      return await this.findByWallet(normalizedAddress);
    } catch (error: any) {
      throw new Error(`Failed to add/update silver user: ${error.message}`);
    }
  }

  /**
   * Get all active users
   */
  static async getAllActiveUsers() {
    try {
      const [rows] = await db.query(
        `SELECT * FROM silver_position_users WHERE is_active = TRUE ORDER BY last_position_date DESC`
      );
      return rows as any[];
    } catch (error: any) {
      throw new Error(`Failed to fetch active silver users: ${error.message}`);
    }
  }

  /**
   * Get all users (active and inactive)
   */
  static async getAllUsers() {
    try {
      const [rows] = await db.query(
        `SELECT * FROM silver_position_users ORDER BY last_position_date DESC`
      );
      return rows as any[];
    } catch (error: any) {
      throw new Error(`Failed to fetch all silver users: ${error.message}`);
    }
  }

  /**
   * Find user by wallet address
   */
  static async findByWallet(walletAddress: string) {
    const normalizedAddress = walletAddress.toLowerCase();
    
    try {
      const [rows] = await db.query(
        `SELECT * FROM silver_position_users WHERE wallet_address = ?`,
        [normalizedAddress]
      );
      return (rows as any)[0] || null;
    } catch (error: any) {
      throw new Error(`Failed to find silver user: ${error.message}`);
    }
  }

  /**
   * Mark user as inactive (soft delete)
   */
  static async deactivateUser(walletAddress: string) {
    const normalizedAddress = walletAddress.toLowerCase();
    
    try {
      await db.query(
        `UPDATE silver_position_users SET is_active = FALSE WHERE wallet_address = ?`,
        [normalizedAddress]
      );
    } catch (error: any) {
      throw new Error(`Failed to deactivate silver user: ${error.message}`);
    }
  }

  /**
   * Permanently delete a user
   */
  static async deleteUser(walletAddress: string) {
    const normalizedAddress = walletAddress.toLowerCase();
    
    try {
      await db.query(
        `DELETE FROM silver_position_users WHERE wallet_address = ?`,
        [normalizedAddress]
      );
    } catch (error: any) {
      throw new Error(`Failed to delete silver user: ${error.message}`);
    }
  }

  /**
   * Get count of active users
   */
  static async countActiveUsers() {
    try {
      const [rows] = await db.query(
        `SELECT COUNT(*) as count FROM silver_position_users WHERE is_active = TRUE`
      );
      return Number((rows as any)[0].count || 0);
    } catch (error: any) {
      throw new Error(`Failed to count active silver users: ${error.message}`);
    }
  }
}

// Initialize table on module load
async function bootstrap() {
  await SilverPositionUserModel.initialize();
}

bootstrap();
