require 'mysql2'

class DatabaseMethodsWifi

  attr_accessor :database_username, :database_password, :database_host

  def initialize(database_username, database_password, database_host)
    @database_username = database_username
    @database_password = database_password
    @database_host = database_host
  end


  def wifi_get_max_ap_number
    client = Mysql2::Client.new(:host => database_host, :username => database_username, :password => database_password)
    client.query("use mtsPortalWiFI", :cast => false)
    client.query("SELECT MAX(AP_ID) AS AP_ID FROM List_mikroTik_AP",:as => :array).each do |row|
      client.close
      #"row" + row[0]
      return row [0]
    end
  end

  def wifi_get_max_tunnel_id
    client = Mysql2::Client.new(:host => database_host, :username => database_username, :password => database_password)
    client.query("use mtsPortalWiFI", :cast => false)
    client.query("SELECT MAX(EoIP_ID) AS EoIP_ID FROM List_mikroTik_eoip_tunnels",:as => :array).each do |row|
      client.close
      #"row" + row[0]
      return row [0]
    end
  end

  def add_mikrotik_ap_3g_eoip_data(apid, phonenumber, eoipid_man, simip, companyname, bridgename_athub, bridgeip_athub)
    client = Mysql2::Client.new(:host => database_host, :username => database_username, :password => database_password)
    client.query("use mtsPortalWiFI", :cast => false)
    row = ""
    client.query("INSERT INTO List_mikroTik_eoip_tunnels (AP_ID, Phone_number, EoIP_ID, SIM_ip, bridge_ip_at_AP_site, tunnel_name_at_hub_site, MikroTik_hub_ip, state, IWAG_gateway_ip, subscribers_network_mask,  tunnel_name_at_ap_site, bridge_name_at_hub_site, bridge_name_at_ap_site,  bridge_ip_at_hub_site )  VALUES  ( #{apid}, '#{phonenumber}',  #{eoipid_man + 1}, '#{simip}',   '100.71.112.#{apid.to_s}',  '#{apid.to_s}_tunnel_ap1_hub1_#{companyname}',  '172.24.211.11', 'on', '100.71.112.1', '21', 'tunnel_to_hab1',  '#{bridgename_athub}',  'bridge_tunnel', '#{bridgeip_athub}' )", :cast => false)
    #  client.query("INSERT INTO List_mikroTik_eoip_tunnels (AP_ID, Phone_number, EoIP_ID, SIM_ip, bridge_ip_at_AP_site, tunnel_name_at_hub_site, MikroTik_hub_ip, state, IWAG_gateway_ip, subscribers_network_mask,  tunnel_name_at_ap_site, bridge_name_at_hub_site, bridge_name_at_ap_site,  bridge_ip_at_hub_site )  VALUES  ( 89, '67567',  45, '5236256',   '100.71.112.45',  '45_tunnel_ap1_hub1_45',  '172.24.211.11', 'on', '100.71.112.1', '21', 'tunnel_to_hab1',  'gfhhdgh',  'bridge_tunnel', '5452453' )", :cast => false)
    client.close
  end

  def add_mikrotik_ap_3g_eoip_man(apid, phonenumber, eoipid_man, simip, companyname)
    client = Mysql2::Client.new(:host => database_host, :username => database_username, :password => database_password)
    client.query("use mtsPortalWiFI", :cast => false)
    row = ""
    client.query("INSERT INTO List_mikroTik_eoip_tunnels (AP_ID, Phone_number, EoIP_ID, SIM_ip, bridge_ip_at_AP_site, tunnel_name_at_hub_site, MikroTik_hub_ip, state, IWAG_gateway_ip, subscribers_network_mask,  tunnel_name_at_ap_site, bridge_name_at_hub_site, bridge_name_at_ap_site,  bridge_ip_at_hub_site )  VALUES  ( #{apid}, '#{phonenumber}',  #{eoipid_man}, '#{simip}',   '100.71.104.#{apid.to_s}',  '#{apid.to_s}_tunnel_management_ap1_hub1_#{companyname}',  '172.24.211.11', 'on', '100.71.104.1', '21', 'tunnel_Management_to_hab1',  'bridge_management_hub1',  'bridge_tunnel', '100.71.104.2' )", :cast => false)
    #  client.query("INSERT INTO List_mikroTik_eoip_tunnels (AP_ID, Phone_number, EoIP_ID, SIM_ip, bridge_ip_at_AP_site, tunnel_name_at_hub_site, MikroTik_hub_ip, state, IWAG_gateway_ip, subscribers_network_mask,  tunnel_name_at_ap_site, bridge_name_at_hub_site, bridge_name_at_ap_site,  bridge_ip_at_hub_site )  VALUES  ( 89, '67567',  45, '5236256',   '100.71.112.45',  '45_tunnel_ap1_hub1_45',  '172.24.211.11', 'on', '100.71.112.1', '21', 'tunnel_to_hab1',  'gfhhdgh',  'bridge_tunnel', '5452453' )", :cast => false)
    client.close
  end

  def add_mikrotik_ap_3g(apid, phonenumber, eoipid_data, simip, companyname, bridgename_athub, bridgeip_athub)
    client = Mysql2::Client.new(:host => database_host, :username => database_username, :password => database_password)
    client.query("use mtsPortalWiFI", :cast => false)
    row = ""
    client.query("INSERT INTO List_mikroTik_eoip_tunnels (AP_ID, Phone_number, EoIP_ID, SIM_ip, bridge_ip_at_AP_site, tunnel_name_at_hub_site, MikroTik_hub_ip, state, IWAG_gateway_ip, subscribers_network_mask,  tunnel_name_at_ap_site, bridge_name_at_hub_site, bridge_name_at_ap_site,  bridge_ip_at_hub_site )  VALUES  ( #{apid}, '#{phonenumber}',  #{eoipid_data}, '#{simip}',   '100.71.112.#{apid.to_s}',  '#{apid.to_s}_tunnel_ap1_hub1_#{companyname}',  '172.24.211.11', 'on', '100.71.112.1', '21', 'tunnel_to_hab1',  '#{bridgename_athub}',  'bridge_tunnel', '#{bridgeip_athub}' )", :cast => false)
    #  client.query("INSERT INTO List_mikroTik_eoip_tunnels (AP_ID, Phone_number, EoIP_ID, SIM_ip, bridge_ip_at_AP_site, tunnel_name_at_hub_site, MikroTik_hub_ip, state, IWAG_gateway_ip, subscribers_network_mask,  tunnel_name_at_ap_site, bridge_name_at_hub_site, bridge_name_at_ap_site,  bridge_ip_at_hub_site )  VALUES  ( 89, '67567',  45, '5236256',   '100.71.112.45',  '45_tunnel_ap1_hub1_45',  '172.24.211.11', 'on', '100.71.112.1', '21', 'tunnel_to_hab1',  'gfhhdgh',  'bridge_tunnel', '5452453' )", :cast => false)
    client.close
  end


  def add_mikrotik_ap_3g_to_list(apid, companyname, app_address, simmsisdn, simimsi, region, contacts, ssid, portal_style, ap_serial_number, ap_model, description)
    client = Mysql2::Client.new(:host => database_host, :username => database_username, :password => database_password)
    client.query("use mtsPortalWiFI", :cast => false)
    row = ""
    client.query("INSERT INTO List_mikroTik_AP (AP_ID, Company_name, AP_address, SIM_MSISDN, SIM_IMSI, Region, Contacts, SSID, Portal_style, AP_serial_number, AP_model, Description) VALUES (#{apid}, '#{companyname}', '#{app_address}',  '#{simmsisdn}', '#{simimsi}', '#{region}', '#{contacts}', '#{ssid}', '#{portal_style}', '#{ap_serial_number}', '#{ap_model}', '#{description}')", :cast => false)
    #  client.query("INSERT INTO List_mikroTik_eoip_tunnels (AP_ID, Phone_number, EoIP_ID, SIM_ip, bridge_ip_at_AP_site, tunnel_name_at_hub_site, MikroTik_hub_ip, state, IWAG_gateway_ip, subscribers_network_mask,  tunnel_name_at_ap_site, bridge_name_at_hub_site, bridge_name_at_ap_site,  bridge_ip_at_hub_site )  VALUES  ( 89, '67567',  45, '5236256',   '100.71.112.45',  '45_tunnel_ap1_hub1_45',  '172.24.211.11', 'on', '100.71.112.1', '21', 'tunnel_to_hab1',  'gfhhdgh',  'bridge_tunnel', '5452453' )", :cast => false)
    client.close
  end


  def check_mikrotik_ap_exists(sim_ipaddr, simmsisdn)
    client = Mysql2::Client.new(:host => database_host, :username => database_username, :password => database_password)
    client.query("use mtsPortalWiFI", :cast => false)
    row = ""
    dffd1= client.query("SELECT AP_ID FROM List_mikroTik_eoip_tunnels WHERE SIM_ip='#{sim_ipaddr}' or Phone_number='#{simmsisdn}'",:as => :array).each do |row|
      client.close
      row[0]
      # if row[0].length ==4
      #   return '3'
      # else return nil
      # end
      return row[0]
      #Mysql2::Client#close
    end
  end
end