#!/usr/bin/ruby
  #
  # This file is part of centurio.work/ing/commands.
  #
  # centurio.work/ing/commands is free software: you can redistribute it and/or
  # modify it under the terms of the GNU General Public License as published by
  # the Free Software Foundation, either version 3 of the License, or (at your
  # option) any later version.
  #
  # centurio.work/ing/commands is distributed in the hope that it will be useful,
  # but WITHOUT ANY WARRANTY; without even the implied warranty of
  # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU General
  # Public License for more details.
  #
  # You should have received a copy of the GNU General Public License along with
  # centurio.work/ing/commands (file COPYING in the main directory). If not, see
# <http://www.gnu.org/licenses/>.

require 'rubygems'
require 'json'
require 'riddl/server'
require 'riddl/client'
require 'fileutils'
require 'nokogiri'
require 'sqlite3'
require 'net/http'


$db = SQLite3::Database.open 'database/stations.db'


class GetStations < Riddl::Implementation
  def response
    result = $db.execute "SELECT * FROM stations"
    
    builder = Nokogiri::XML::Builder.new do |xml|
      xml.stations {
        result.each do |row|
          xml.station(:id => row[0]){
            resultstation = $db.execute "SELECT * FROM station WHERE station = #{row[0]}"
            resultstation.each do |row|
              xml.pattern(:id => row[1], :value => row[2], :description => row[3], :changed => row[4])
            end
          }
        end
      }
    end
    #puts builder.to_xml
    Riddl::Parameter::Complex.new('stations','application/xml',builder.to_xml)
  end
end

class CreateStation < Riddl::Implementation
  def self::createDB(n)
    
    #result = $db.execute "SELECT MAX(station) FROM stations"
      #if(result[0][0] == nil)
      #  $db.execute "INSERT INTO stations (station) VALUES (?)", 0
      #else
      #  $db.execute "INSERT INTO stations (station) VALUES (?)", result[0][0]+1
      #end
    #$db.execute ("INSERT INTO stations (station, name) VALUES (?,?)", [n,n])
    
    $db.execute("INSERT OR IGNORE INTO stations (station, name) VALUES (?,?)", [n,n])
  end
  
  def response
    GetStation::createDB
  end
end



class GetStation < Riddl::Implementation
  def self::prepare(id)
    result = $db.execute "SELECT * FROM station WHERE station = #{id}"
    Nokogiri::XML::Builder.new do |xml|
      xml.station(:id => id){
        result.each do |row|
          xml.pattern(:id => row[1], :value => row[2], :description => row[3], :changed => row[4])
        end
      }
    end
  end
  
  def response
    builder = GetStation::prepare(@r.last)
    Riddl::Parameter::Complex.new('station','application/xml',builder.to_xml)
  end
end

class CreatePattern < Riddl::Implementation
  def response

    doc = Nokogiri::XML(@p[0].value)
    result = $db.execute "SELECT MAX(patternID) FROM station"
    if(result[0][0] == nil)
      id = 0
      else
      id = result[0][0] +1
    end
    
    values = doc.xpath("/*/@value")[0].value.split(".")
    $db.execute("INSERT INTO station (station, patternID, pattern, description, date, P0, P1, P2, P3, P4, P5, P6, P7, P8, P9, P10) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)", [@r.last, id, doc.xpath("/*/@value")[0].value, doc.xpath("/*/@description")[0].value, doc.xpath("/*/@changed")[0].value, values[0], values[1], values[2], values[3], values[4], values[5], values[6], values[7], values[8], values[9], values[10]])
    
    
    ret = {:id => id}
    Riddl::Parameter::Complex.new('list','application/json',JSON::pretty_generate(ret))
    
  end
end



class GetPattern < Riddl::Implementation
  def response
    result = $db.execute "SELECT * FROM station WHERE station = #{@r[0]} and patternID = #{@r[1]}"
    builder = Nokogiri::XML::Builder.new do |xml|
      xml.pattern(:id => result[0][1], :value => result[0][2], :description => result[0][3], :changed => result[0][4])
    end
    Riddl::Parameter::Complex.new('pattern','application/xml',builder.to_xml)
  end
end

class UpdatePattern < Riddl::Implementation
  def response
    
    doc = Nokogiri::XML(@p[0].value)
    #puts "UPdate Pattern" + @r[0] + @r[1] + " value " + doc.xpath("/*/@value")[0].value
    #$db.execute("DELETE FROM station WHERE  station = ? AND patternID = ?", [@r[0], @r[1]])
    
    
    values = doc.xpath("/*/@value")[0].value.split(".")    
    #$db.execute("INSERT INTO station (station, patternID, pattern, description, date, P0, P1, P2, P3, P4, P5, P6, P7, P8, P9, P10) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)", [@r[0], @r[1], doc.xpath("/*/@value")[0].value, doc.xpath("/*/@description")[0].value, doc.xpath("/*/@changed")[0].value, values[0], values[1], values[2], values[3], values[4], values[5], values[6], values[7], values[8], values[9], values[10]])
    
    #$db.execute("UPDATE image SET imageID = ? WHERE  station = ? AND patternID = ? AND imageID = ? AND language = ?", [999999, @r[0], @r[1], i, @r[4]])
    
    $db.execute("Update station SET  pattern = ?, description = ?, date = ?, P0 = ?, P1 = ?, P2 = ?, P3 = ?, P4 = ?, P5 = ?, P6 = ?, P7 = ?, P8 = ?, P9 = ?, P10 = ? WHERE station = ? AND patternID = ?", [doc.xpath("/*/@value")[0].value, doc.xpath("/*/@description")[0].value, doc.xpath("/*/@changed")[0].value, values[0], values[1], values[2], values[3], values[4], values[5], values[6], values[7], values[8], values[9], values[10], @r[0], @r[1]])
    
    #$db.execute("INSERT INTO station (station, patternID, pattern, description, date) VALUES (?,?,?,?,?)", [@r[0], @r[1]], doc.xpath("/*/@value")[0].value, doc.xpath("/*/@description")[0].value, doc.xpath("/*/@changed")[0].value)
    
    #$db.execute("UPDATE station SET pattern = ? and description = ? and date = ? WHERE station = ? and patternID = ?", [doc.xpath("/*/@value")[0].value, doc.xpath("/*/@description")[0].value, doc.xpath("/*/@changed")[0].value, @r[0], @r[1]])
  end
end

class DeletePattern < Riddl::Implementation
  def response
    $db.execute("DELETE FROM station WHERE  station = ? AND patternID = ?", [@r[0], @r[1]])
  end
end

class DuplicatePattern < Riddl::Implementation
  def response  
    
    
    #duplicate db etwas mühsam da der eintrag kopiert werden muss und dabei die unique ID verändert werden muss daher geht insert into select nicht so ganz.
    result = $db.execute "SELECT MAX(patternID) FROM station"
    if(result[0][0] == nil)
      id = 0
      else
      id = result[0][0] +1
    end
    
    result = $db.execute "SELECT * FROM station WHERE station = #{@r[0]} and patternID = #{@r[1]}"
    result[0][1] = id;
    questionmarks = ""
    result[0].length().times{questionmarks = questionmarks + ",?"}
    questionmarks[0] = ""
    
    
    $db.execute( "INSERT INTO station values (#{questionmarks})", result[0])
    #duplicate error
    result = $db.execute "SELECT * FROM error WHERE station = #{@r[0]} and patternID = #{@r[1]}"
    
    if(result[0] != nil)
      result.each do |row|
        row[1] = id;
        questionmarks = ""
        row.length().times{questionmarks = questionmarks + ",?"}
        questionmarks[0] = ""
        $db.execute( "INSERT INTO error values (#{questionmarks})", row)
      end
    end
    
    #duplicate image db
    result = $db.execute "SELECT * FROM image WHERE station = #{@r[0]} and patternID = #{@r[1]}"
    if(result[0] != nil)
      result.each do |row|
        row[1] = id;
        questionmarks = ""
        row.length().times{questionmarks = questionmarks + ",?"}
        questionmarks[0] = ""
        $db.execute( "INSERT INTO image values (#{questionmarks})", row)
      end
    end
    
    #duplicate images
    pathorigin = File.join(File.dirname(__dir__),'images/uploads', @r[0], @r[1])
    pathdestination = File.join(File.dirname(__dir__),'images/uploads', @r[0], id.to_s)
    FileUtils.mkdir_p(pathdestination)
    
    FileUtils.copy_entry pathorigin, pathdestination
    
    #duplicate Replacement db
    result = $db.execute "SELECT * FROM replacements WHERE station = #{@r[0]} and patternID = #{@r[1]}"
    if(result[0] != nil)
      result.each do |row|
        row[1] = id;
        questionmarks = ""
        row.length().times{questionmarks = questionmarks + ",?"}
        questionmarks[0] = ""
        $db.execute( "INSERT INTO replacements values (#{questionmarks})", row)
      end
    end
    
    ret = {:id => id}
    Riddl::Parameter::Complex.new('list','application/json',JSON::pretty_generate(ret))
    
  end
end


class SaveError < Riddl::Implementation
  def response
    $db.execute "CREATE TABLE IF NOT EXISTS error(station INT, patternID INT, error TEXT, FOREIGN KEY(station) REFERENCES station(station), FOREIGN KEY(patternID) REFERENCES station(patternID))"
    
    #Delete all, afterwards insert
    $db.execute("DELETE FROM error WHERE  station = ? AND patternID = ?", [@r[0], @r[1]])
    
    doc = Nokogiri::XML(@p[0].value)
    doc.xpath(".//reason").each do |node|
      $db.execute("INSERT INTO error (station, patternID, error) VALUES (?,?,?)", [@r[0], @r[1], node.text])
    end
  end
end

class GetError < Riddl::Implementation
  def response
    $db.execute "CREATE TABLE IF NOT EXISTS error(station INT, patternID INT, error TEXT, FOREIGN KEY(station) REFERENCES station(station), FOREIGN KEY(patternID) REFERENCES station(patternID))"
    
    result = $db.execute "SELECT * FROM error WHERE station = #{@r[0]} and patternID = #{@r[1]}"
    builder = Nokogiri::XML::Builder.new do |xml|
      xml.error(){
        result.each do |row|
          xml.reason row[2]
        end
      }
    end
    Riddl::Parameter::Complex.new('error','application/xml',builder.to_xml)
  end
end



class SaveReplacement < Riddl::Implementation
  def response  
    #Delete all, afterwards insert
    $db.execute("DELETE FROM replacements WHERE  station = ? AND patternID = ?", [@r[0], @r[1]])
    
    doc = Nokogiri::XML(@p[0].value)
    doc.xpath(".//item").each do |node|
      
      $db.execute("INSERT INTO replacements (station, patternID, abbreviation, url) VALUES (?,?,?,?)", [@r[0], @r[1], node.xpath(".//abbreviation").text, node.xpath(".//url").text])
    end
  end
end

class GetReplacement < Riddl::Implementation
  def response
    result = $db.execute "SELECT * FROM replacements WHERE station = #{@r[0]} and patternID = #{@r[1]}"
    
    builder = Nokogiri::XML::Builder.new do |xml|
      xml.replacement(){
        result.each do |row|
          xml.item(){
            xml.abbreviation row[2]
            xml.url row[3]
          }
        end
      }
    end
    Riddl::Parameter::Complex.new('replacement','application/xml',builder.to_xml)
  end
end





class GetImages < Riddl::Implementation
  def response
    formatted_no_decl = Nokogiri::XML::Node::SaveOptions::FORMAT +
    Nokogiri::XML::Node::SaveOptions::NO_DECLARATION
    
    result = $db.execute "SELECT DISTINCT station, patternID, imageID FROM image WHERE station = #{@r[0]} and patternID = #{@r[1]}"
    builder = Nokogiri::XML::Builder.new do |xml|
      xml.images(){
        result.each do |row|
          xml<< GetImage::prepare(@r[0], @r[1], row[2]).to_xml( save_with:formatted_no_decl )
        end
      }
    end
    Riddl::Parameter::Complex.new('images','application/xml',builder.to_xml)
  end
end

class UploadImage < Riddl::Implementation
  def response
    
    lang = @p[@p.length-1].value
        
    result = $db.execute "SELECT MAX(imageID) FROM image WHERE station = #{@r[0]} and patternID = #{@r[1]} and language = '#{lang}'"
    if(result[0][0] == nil)
      id = 0
      else
      id = result[0][0] +1
    end
    
    #puts JSON.pretty_generate(@p)
    i = 0
    while i < @p.length-1 do
      item = @p[i]
      if(item != nil && item.name == "files[]")
        path = File.join(File.dirname(__dir__),'images/uploads', @r[0], @r[1], id.to_s)
        FileUtils.mkdir_p(path)
        
        #juergen nach alternative fragen
        readFile = File.read(item.value.inspect.to_s[/Tempfile:(.*?)>/m, 1])
        File.open(File.join(path, lang + ".svg"), 'wb') do |file|
          file.write(readFile.to_s)
        end
        
        $db.execute("INSERT INTO image (station, patternID, imageID, language, label) VALUES (?,?,?,?,?)", [@r[0], @r[1], id, lang, "Label"])
        
        id += 1
      end
      i +=1
    end
  end
end



class AddExternalImage < Riddl::Implementation
  def response
    
    doc = Nokogiri::XML(@p[0].value)
    url = doc.xpath("/externalImage/url").text
    lang = doc.xpath("/externalImage/lang").text   
          
    result = $db.execute "SELECT MAX(imageID) FROM image WHERE station = #{@r[0]} and patternID = #{@r[1]} and language = '#{lang}'"
    if(result[0][0] == nil)
      id = 0
      else
      id = result[0][0] +1
    end
    
    #Create link file (we create a file so moving/deleting works always the same and the database does not have to be changed for external files) only thing that had to be changed was imgReplacement.php          
    image = '<svg width="100%" height="100%" xmlns="http://www.w3.org/2000/svg" xmlns:xlink="http://www.w3.org/1999/xlink" overflow="hidden">  <image href="' + url +'" height="100%" width="100%"/>
    </svg>'
    
    path = File.join(File.dirname(__dir__),'images/uploads', @r[0], @r[1], id.to_s)
    File.open(File.join(path, lang + ".svg"), 'wb') do |file|
      file.write(image)
    end
    
    #Create DB entry as usual
    $db.execute("INSERT INTO image (station, patternID, imageID, language, label) VALUES (?,?,?,?,?)", [@r[0], @r[1], id, lang,  url])
    
  end
end



class ReorderImages < Riddl::Implementation
  def response
    
    #remove brackets
    iter = @p[0].value.read.chop
    iter[0] =""
    
    
    iter = iter.split(",").map(&:to_i)
    i = 0
    
    while i < iter.length do
      if(i != iter[i])
        #swap
        path = File.join(File.dirname(__dir__),'images/uploads', @r[0], @r[1], i.to_s)
        path2 = File.join(File.dirname(__dir__),'images/uploads', @r[0], @r[1], iter[i].to_s)
        tmp = File.join(File.dirname(__dir__),'images/uploads', @r[0], @r[1])
        
        FileUtils.mv(path + "/" + @r[4] + ".svg", tmp)
        FileUtils.mv(path2 + "/" + @r[4] + ".svg", path)
        FileUtils.mv(tmp + "/" + @r[4] + ".svg", path2)
        
        #DB Swap
        
        
        result = $db.execute "SELECT MAX(imageID) FROM image"
        if(result[0][0] == nil)
          maxImgId = 0
          else
          maxImgId = result[0][0] +1
        end
        
        #Wegen 999999 fragen
        #nutze max ID+1 zum tauschen
        $db.execute("UPDATE image SET imageID = ? WHERE  station = ? AND patternID = ? AND imageID = ? AND language = ?", [maxImgId, @r[0], @r[1], i, @r[4]])
        
        $db.execute("UPDATE image SET imageID = ? WHERE  station = ? AND patternID = ? AND imageID = ? AND language = ?", [i, @r[0], @r[1], iter[i], @r[4]])
        
        $db.execute("UPDATE image SET imageID = ? WHERE  station = ? AND patternID = ? AND imageID = ? AND language = ?", [iter[i], @r[0], @r[1], maxImgId, @r[4]])
        
        iter.map! do |item|
          if(item == i)
            iter[i]
            else
            item
          end
        end
      end
      i +=1
    end
    
  end
end



class GetImage < Riddl::Implementation
  def self::prepare(station, pattern, imageID)
    result = $db.execute "SELECT * FROM image WHERE station = #{station} and patternID = #{pattern} and imageID = #{imageID}"
    Nokogiri::XML::Builder.new do |xml|
      xml.image(:id => imageID){
        result.each do |row|
          xml.variant(:lang => row[3], :label => row[4]){
            xml.text("https://centurio.work/customers/evva/was/images/uploads/#{station}/#{pattern}/#{imageID}/#{row[3]}.svg")
          }
        end
      }
    end
  end
  def response
    builder = GetImage::prepare(@r[0], @r[1], @r[3])
    Riddl::Parameter::Complex.new('image','application/xml',builder.to_xml)
  end
end




class UpdateImageLabel < Riddl::Implementation
  def response
    $db.execute("UPDATE image SET label = ? WHERE  station = ? AND patternID = ? AND imageID = ? AND language = ?", [@p[0].value, @r[0], @r[1], @r[3], @r[4]])
  end
end

class GetRealImage < Riddl::Implementation
  def response
    
    #split on "." and tacke [0] to allow for e.g. de-at.svg
    #would lead to error on de.at.svg //should this be fixed? would also lead to error if everything after last "." would be removed in case of only having de.at
    
    #https://centurio.work/customers/evva/was/server/0/0/images/3/de-at?video=xyz
    
    img = File.read(File.join(File.dirname(__dir__),'images/uploads', @r[0], @r[1], @r[3], @r[4].split(".")[0] + ".svg"))
    
    if false #Currently replacements are done on the client side
      if(@p[0].nil?)
        puts "Undefined p"
        else
        if(@p[0].name == "video" && @p[0].value)
          
          xml = Nokogiri.parse img
          
          puts "Width " + xml.xpath("string(//xmlns:image/@width)")
          puts "Height " + xml.xpath("string(//xmlns:image/@height)")
          #puts "Posi " + xml.xpath("string(//xmlns:clipPath/path/@height)")
          
          puts "Posi " + xml.xpath("string(//xmlns:image/following-sibling::clipPath/@id)")
          
          
          
          
          puts xml.xpath("string(//xmlns:text[starts-with(text(), 'url')])").sub("url=", "")
          
          
          #     text
          
          
          img = img.sub! "</svg>", '
          <g><g transform="translate(562,288),scale(1.35185 1.35185)">
          <foreignObject width="480" height="270">
          <video width="480" height="270" controls="" autoplay="autoplay" muted="muted" xmlns="http://www.w3.org/1999/xhtml" >
          <source src="' + xml.xpath("string(//xmlns:text[starts-with(text(), 'url')])").sub("url=", "") +'" type="video/mp4"/>
          </video>
          </foreignObject>
          </g></g></svg>'
          
          else
          @p.each do |item|
            unless item.name.nil? || item.value.nil?
              img.sub! item.name, item.value
            end
          end
        end
      end
    end
    Riddl::Parameter::Complex.new('theRealImage','image/svg+xml',img)
  end
end


class DeleteImage < Riddl::Implementation
  def response
    #puts "Delete Image"
    #puts File.join(File.dirname(__dir__),'images/uploads', @r[0], @r[1], @r[3] , @r[4] + ".svg")
    
    File.delete(File.join(File.dirname(__dir__),'images/uploads', @r[0], @r[1], @r[3] , @r[4] + ".svg")) if File.exist?(File.join(File.dirname(__dir__),'images/uploads', @r[0], @r[1], @r[3] , @r[4] + ".svg"))
    
    
    $db.execute("DELETE FROM image WHERE  station = ? AND patternID = ? AND imageID = ? AND language = ?", [@r[0], @r[1], @r[3], @r[4]])
    
    result = $db.execute "SELECT MAX(imageID) FROM image WHERE station = #{@r[0]} and patternID = #{@r[1]} and language = '#{@r[4]}'"
    if(result[0][0] == nil)
      id = 0
      else
      id = result[0][0] +1
    end
    
    #reorder if !end gets deleted
    if(id == @r[3])
      return
      else
      cur = @r[3].to_i + 1
      prev = @r[3].to_i
      while cur < id.to_i do
        $db.execute("UPDATE image SET imageID = ? WHERE  station = ? AND patternID = ? AND imageID = ? AND language = ?", [prev, @r[0], @r[1], cur, @r[4]])
        
        src = File.join(File.dirname(__dir__),'images/uploads', @r[0], @r[1], cur.to_s, @r[4] + ".svg")
        target = File.join(File.dirname(__dir__),'images/uploads', @r[0], @r[1], prev.to_s, @r[4] + ".svg")
        FileUtils.mv(src, target)
        cur+=1
        prev += 1
      end
    end
  end
end

class ListSearch < Riddl::Implementation
  def response
    
    ret = {
      :patternID => "/patternID",
      :multi => "/multi",
      :juergen => "/juergen"
    }
    
    Riddl::Parameter::Complex.new('list','application/json',JSON::pretty_generate(ret))
  end
end


class SearchPattern < Riddl::Implementation
  def response
    
    
    
    iter = @p[0].value.split(".")
    searchstring = ""
    count = 0
    
    iter.each do |item|
      if !item.nil?
        if item.to_s != "*"
          searchstring = searchstring + " and (P" + count.to_s + "='*' or P" + count.to_s + " = '" + item.to_s + "')"
        end
        count += 1
      end
    end
    
    result = $db.execute "SELECT patternID FROM station WHERE station = " + @r[0] + searchstring
    
    
    if result.length > 0
      ret = {
        :amount => result.length.to_s,
        :ids => result
      }
    end
    
    Riddl::Parameter::Complex.new('list','application/json',JSON::pretty_generate(ret))
    
    #puts the first found string
      #result = $db.execute "SELECT P0,P1,P2,P3,P4,P5,P6,P7,P8,P9,P10 FROM station WHERE station = 0" + searchstring
      #puts "found" + result.length.to_s
      #result[0].each do |item|
      #  if !item.nil?
      #    puts item + "."
      #  end
    #end
  end
end



class SearchImages < Riddl::Implementation
  def response
    iter = @p[0].value.split(".")
    searchstring = ""
    count = 0
    
    #search = K.*.9.*
    
    #match: = K.5.9.7
      #match: = K.5.9.*
    #match: = K.*.*.*
    
    #no match: = K.*.8.*
    
    iter.each do |item|
      if !item.nil?
        if item.to_s != "*"
          searchstring = searchstring + " and (P" + count.to_s + "='*' or P" + count.to_s + " = '" + item.to_s + "')"
          else
          searchstring = searchstring + " and (P" + count.to_s + "!=''" + ")"
        end
        count += 1
      end
    end
    
    result = $db.execute "SELECT patternID FROM station WHERE station = " + @r[0] + searchstring
    
    
    ret = []
    if result.length > 0
      result.each do |item|
        result2 = $db.execute "SELECT DISTINCT station, patternID, imageID FROM image WHERE station = #{@r[0]} and patternID = #{item[0]}"
        result2.each do |item2|
          ret << item2[0].to_s + "/" + item2[1].to_s + "/" + item2[2].to_s
        end
      end
    end
    
    Riddl::Parameter::Complex.new('list','application/json',JSON::pretty_generate(ret))
  end
end

class SearchImagesSingle < Riddl::Implementation
  def response
    
    
    iter = @p[0].value.split(".")
    searchstring = ""
    count = 0
    
    iter.each do |item|
      if !item.nil?
        if item.to_s != "*"
          searchstring = searchstring + " and (P" + count.to_s + "='*' or P" + count.to_s + " = '" + item.to_s + "')"
          else
          searchstring = searchstring + " and (P" + count.to_s + "!=''" + ")"
        end
        count += 1
      end
    end
    
    result = $db.execute "SELECT patternID FROM station WHERE station = " + @r[0] + searchstring
    
    
    count = 0
    pattern = 0
    image = 0
    
    if result.length > 0
      result.each do |item|
        result2 = $db.execute "SELECT DISTINCT station, patternID, imageID FROM image WHERE station = #{@r[0]} and patternID = #{item[0]}"
        result2.each do |item2|
          if(count == @r[3].to_i)
            puts "Found "
            pattern = item2[1]
            image = item2[2]
          end
          count += 1
        end
      end
    end
    img = File.read(File.join(File.dirname(__dir__),'images/uploads', @r[0].to_s, pattern.to_s, image.to_s, "de-at.svg"))
    Riddl::Parameter::Complex.new('theRealImage','image/svg+xml',img)
    
  end
end

class SearchImages2 < Riddl::Implementation
  def response
    iter = @p[0].value.split(".")
    searchstring = ""
    count = 0
    
    #search = K.*.9.*
    
    #match: = K.5.9.7
      #match: = K.5.9.*
    #match: = K.*.*.*
    
    #no match: = K.*.8.*
    
    iter.each do |item|
      if !item.nil?
        if item.to_s != "*"
          searchstring = searchstring + " and (P" + count.to_s + "='*' or P" + count.to_s + " = '" + item.to_s + "')"
          else
          searchstring = searchstring + " and (P" + count.to_s + "!=''" + ")"
        end
        count += 1
      end
    end
    
    result = $db.execute "SELECT patternID FROM station WHERE station = " + @r[0] + searchstring
    
    
    ret = []
    if result.length > 0
      result.each do |item|
        result2 = $db.execute "SELECT DISTINCT station, patternID, imageID FROM image WHERE station = #{@r[0]} and patternID = #{item[0]}"
        result2.each do |item2|
          ret << item2[0].to_s + "/" + item2[1].to_s + "/" + item2[2].to_s
        end
      end
    end
    
    Riddl::Parameter::Complex.new('list','application/json',JSON::pretty_generate(ret))
  end
end



class SearchImages2Single < Riddl::Implementation
  def response
    iter = @p[0].value.split(".")
    searchstring = ""
    count = 0
    
    iter.each do |item|
      if !item.nil?
        if item.to_s != "*"
          searchstring = searchstring + " and (P" + count.to_s + "='*' or P" + count.to_s + " = '" + item.to_s + "')"
          else
          searchstring = searchstring + " and (P" + count.to_s + "!=''" + ")"
        end
        count += 1
      end
    end
    
    result = $db.execute "SELECT patternID FROM station WHERE station = " + @r[0] + searchstring
    
    count = 0
    pattern = 0
    image = 0
    
    if result.length > 0
      result.each do |item|
        result2 = $db.execute "SELECT DISTINCT station, patternID, imageID FROM image WHERE station = #{@r[0]} and patternID = #{item[0]}"
        result2.each do |item2|
          if(count == @r[3].to_i)
            puts "Found "
            pattern = item2[1]
            image = item2[2]
          end
          count += 1
        end
      end
    end
    
    #https://centurio.work/customers/evva/was/ui/imageReplacement.php?___image___=8/23/0/de-at.svg
    
    img = File.read(File.join(File.dirname(__dir__),'images/uploads', @r[0].to_s, pattern.to_s, image.to_s, "de-at.svg"))
    
    
    
    uri = "https://centurio.work/customers/evva/was/ui/imageReplacement.php?___image___=" +  @r[0].to_s + "/" + pattern.to_s + "/" + image.to_s + "/de-at.svg"       
    
    string = '<html><body style="margin: 0"><iframe style="position: absolute; height: 100%; width: 100%; border: none" src="' + uri +'" title="description"></body></html>'
    
    
    Riddl::Parameter::Complex.new('html','text/html',string)
    
  end
end


class SearchImages3Single < Riddl::Implementation
  def response
    iter = @p[0].value.split(".")
    searchstring = ""
    count = 0
    
    iter.each do |item|
      if !item.nil?
        if item.to_s != "*"
          searchstring = searchstring + " and (P" + count.to_s + "='*' or P" + count.to_s + " = '" + item.to_s + "')"
          else
          searchstring = searchstring + " and (P" + count.to_s + "!=''" + ")"
        end
        count += 1
      end
    end
    
    result = $db.execute "SELECT patternID FROM station WHERE station = " + @r[0] + searchstring
    
    count = 0
    pattern = 0
    image = 0
    
    
    
    
    
    
    builder = Nokogiri::XML::Builder.new do |xml|
      xml.image(:id => @r[3].to_s){
        if result.length > 0
          result.each do |item|
            result2 = $db.execute "SELECT DISTINCT station, patternID, imageID FROM image WHERE station = #{@r[0]} and patternID = #{item[0]}"
            result2.each do |item2|
              if(count == @r[3].to_i)
                
                  
                result3 = $db.execute "SELECT DISTINCT station, patternID, imageID, language, label FROM image WHERE station = #{@r[0]} and patternID = #{item[0]} and imageID =#{item2[2]}"
                
                
                
                
                
                
                
                result3.each do |item3|                
                  uri = "https://centurio.work/customers/evva/was/ui/imageReplacement.php?___image___=" +  @r[0].to_s + "/" + item3[1].to_s + "/" + item3[2].to_s + "/" + item3[3].to_s + ".svg"
                  
                  @p.each_with_index do |item, index|
                    if index != 0
                      uri += "&" + item.name.to_s + "=" + item.value.to_s
                    end
                    
                  end
                
                  xml.variant(:lang => item3[3].to_s, :label => item3[4].to_s){
                    xml.text(uri)
                  }
                end
              end
              count += 1
            end
          end
        end
      }
    end
    
    #https://centurio.work/customers/evva/was/ui/imageReplacement.php?___image___=8/23/0/de-at.svg
    
    
    
          
    
    

    
    #puts builder.to_xml
    Riddl::Parameter::Complex.new('image','application/xml',builder.to_xml)
    
    
    
    
    
  end
end


class SearchErrors < Riddl::Implementation
  def response
    iter = @p[0].value.split(".")
    searchstring = ""
    count = 0
    
    #search = K.*.9.*
    
    #match: = K.5.9.7
      #match: = K.5.9.*
    #match: = K.*.*.*
    
    #no match: = K.*.8.*
    
    iter.each do |item|
      if !item.nil?
        if item.to_s != "*"
          searchstring = searchstring + " and (P" + count.to_s + "='*' or P" + count.to_s + " = '" + item.to_s + "')"
          else
          searchstring = searchstring + " and (P" + count.to_s + "!=''" + ")"
        end
        count += 1
      end
    end
    
    result = $db.execute "SELECT patternID FROM station WHERE station = " + @r[0] + searchstring

    
    ret = []
    if result.length > 0
      result.each do |item|
        result2 = $db.execute "SELECT DISTINCT error FROM error WHERE station = #{@r[0]} and patternID = #{item[0]}"
        result2.each do |item2|
          ret << item2[0].to_s
        end
      end
    end
    
    ret = ret.uniq
    
    
    builder = Nokogiri::XML::Builder.new do |xml|
      xml.reason{
        if result.length > 0
          ret.each do |item|
            xml.reason(:lang => "de-at"){
              xml.text(item)
            }
          end
        end
      }
    end
    
    Riddl::Parameter::Complex.new('errors','application/xml',builder.to_xml)
  end
end




class SearchImagesReverse < Riddl::Implementation
  def response
    iter = @p[0].value.split(".")
    searchstring = ""
    count = 0
    
    #search = K.*.9.*
    
    #match: = K.5.9.7
      #match: = K.5.9.*
    #match: = K.*.*.*
    
    #no match: = K.*.8.*
    
    iter.each do |item|
      if !item.nil?
        if item.to_s != "*"
          searchstring = searchstring + " and (P" + count.to_s + "='*' or P" + count.to_s + " = '" + item.to_s + "')"
          else
          searchstring = searchstring + " and (P" + count.to_s + "!=''" + ")"
        end
        count += 1
      end
    end
    
    result = $db.execute "SELECT patternID FROM station WHERE station = " + @r[0] + searchstring
    
    
    ret = []
    if result.length > 0
      result.each do |item|
        result2 = $db.execute "SELECT DISTINCT station, patternID, imageID FROM image WHERE station = #{@r[0]} and patternID = #{item[0]}"
        result2.each do |item2|
          ret << item2[0].to_s + "/" + item2[1].to_s + "/" + item2[2].to_s
        end
      end
    end
    
    Riddl::Parameter::Complex.new('list','application/json',JSON::pretty_generate(ret))
  end
end

def createDB(opts)
  $db.execute("PRAGMA foreign_keys=ON");
  
  $db.execute "CREATE TABLE IF NOT EXISTS stations(station INT, name VARCHAR(20), PRIMARY KEY(station))"
  
  opts[:appconf]["stations"].each { |n|
    CreateStation::createDB(n)
  }
  
  #  Unique pattern
  #  $db.execute "CREATE TABLE IF NOT EXISTS station(station INT, patternID INT, pattern CHARACTER(20), description TEXT, date TEXT, P0 VARCHAR(2), P1 VARCHAR(2), P2 VARCHAR(2), P3 VARCHAR(2), P4 VARCHAR(2), P5 VARCHAR(2), P6 VARCHAR(2), P7 VARCHAR(2), P8 VARCHAR(2), P9 VARCHAR(2), P10 VARCHAR(2), PRIMARY KEY(station,patternID), UNIQUE(station, pattern))"
  $db.execute "CREATE TABLE IF NOT EXISTS station(station INT, patternID INT, pattern CHARACTER(256), description TEXT, date TEXT, P0 VARCHAR(10), P1 VARCHAR(10), P2 VARCHAR(10), P3 VARCHAR(10), P4 VARCHAR(10), P5 VARCHAR(10), P6 VARCHAR(10), P7 VARCHAR(10), P8 VARCHAR(10), P9 VARCHAR(10), P10 VARCHAR(10), PRIMARY KEY(station,patternID), UNIQUE(station, patternID))"
  
  
  
  $db.execute "CREATE TABLE IF NOT EXISTS error(station INT, patternID INT, error TEXT, FOREIGN KEY(station, patternID) REFERENCES station(station, patternID) ON DELETE CASCADE)"
  
  $db.execute "CREATE TABLE IF NOT EXISTS replacements(station INT, patternID INT, abbreviation TEXT, url TEXT, FOREIGN KEY(station, patternID) REFERENCES station(station, patternID) ON DELETE CASCADE)"
  
  $db.execute "CREATE TABLE IF NOT EXISTS image(station INT, patternID INT, imageID INT, language CHARACTER(20), label TEXT, FOREIGN KEY(station, patternID) REFERENCES station(station, patternID) ON DELETE CASCADE,PRIMARY KEY(station,patternID,imageID,language))"
  
  #  $db.execute "CREATE TABLE IF NOT EXISTS error(station INT, patternID INT, error TEXT, FOREIGN KEY(station) REFERENCES station(station), FOREIGN KEY(patternID) REFERENCES station(patternID) ON DELETE CASCADE)"
  
  #  $db.execute "CREATE TABLE IF NOT EXISTS image(station INT, patternID INT, imageID INT, language CHARACTER(20), label TEXT, FOREIGN KEY(station) REFERENCES station(station), FOREIGN KEY(patternID) REFERENCES station(patternID) ON DELETE CASCADE,PRIMARY KEY(station,patternID,imageID,language))"
  
  
  
end

server = Riddl::Server.new(File.join(__dir__,'/was.xml'), :host => 'localhost') do |opts|
  accessible_description true
  cross_site_xhr true
  
  createDB(opts)
  
  # opts[:appconf]
  
  on resource do
    run GetStations if get
    run CreateStation if post
    on resource '\d+' do
      run GetStation if get
      run CreatePattern if post 'pattern'
      on resource '\d+' do
        run GetPattern if get
        run UpdatePattern if put 'pattern'
        run DeletePattern if delete
        run DuplicatePattern if put 'patternID'
        on resource 'error' do
          run SaveError if put 'error'
          run GetError if get
          #should add deleteerror
        end
        on resource 'replacement' do
          run SaveReplacement if put 'replacement'
          run GetReplacement if get
          #should add deleteReplacement
        end
        on resource 'images' do
          run GetImages if get
          #run CreateImage if post #'image'
          run AddExternalImage if post 'externalImage'
          run UploadImage if post
          on resource 'reorder' do
            on resource do
              run ReorderImages if put 'orderlist'
            end
          end
          on resource '\d+' do
            run GetImage if get
            on resource do
              run UpdateImageLabel if put 'label'
              run GetRealImage if get
              run DeleteImage if delete
            end
          end
        end
      end
      on resource 'search' do
        run ListSearch if get
        on resource 'patternID' do
          run SearchPattern if get
        end
        on resource 'images' do
          run SearchImages if get
          on resource '\d+' do
            run SearchImagesSingle if get
          end
        end
        on resource 'imagesServerSide' do
          run SearchImages2 if get
          on resource '\d+' do
            run SearchImages2Single if get
          end
        end
        on resource 'imagesWIZ' do
          run SearchImages2 if get
          on resource '\d+' do
            run SearchImages3Single if get
          end
        end
        on resource 'errorsWIZ' do
          run SearchErrors if get
        end
        on resource 'imagesReverse' do
          run SearchImagesReverse if get
        end
      end
    end
  end
end.loop!