class AddDataToAllMrcaTables < ActiveRecord::Migration
  def up
    self.class.update_data()
  end
  
  def down
    self.class.delete_data()
    
  end
  
  def self.update_data()
    self.delete_data()
    self.insert_data()
  end
  
  
  def self.insert_data()

    #less restrictive conditions first
   [[0,"bmc_2004_ordered"],
    [1,"geo_2003_ordered"]
   ].each { |cr|

     execute "insert into mrca_criters
               (id,name)
              values (#{cr[0]},'#{cr[1]}')"
    }


    #bmc_2004_ordered set
    #less restrictive conditions first
   [[0,"a34","Archaea",3417,4482,4149,289,[13,8],"intern"],
    [1,"a106","Bacteria",3437,4391,3917,272,[1,2,3,4,5,6,9,10,12,14,15,16,21,22],"intern"],
    [2,"a105","a76_Therma",3181,4038,3590,244,[1,2,4,5,6,9,10,12,14,15,16,21,22],"intern"],
    [3,"a104","a76_a103",2815,3530,3139,207,[1,2,4,5,6,9,10,12,14,15,16,21],"intern"],
    [4,"a103","a102_BactChlor",2738,3434,3051,201,[16,5,2,12,6,21,4],"intern"],
    [5,"a102","a101_Chlam_Spir",2658,3339,2963,196,[16,5,2,12,6,21],"intern"],
    [6,"a101","a100_Eps",2460,3128,2761,189,[16,5,2,12],"intern"],
    [7,"a100","a99_Alph",2156,2844,2476,187,[16,5,2],"intern"],
    [8,"a99","Gam_Bet",1569,2310,1924,192,[16,5],"intern"],
    [9,"a80","Chlam_Spir",2438,3115,2744,191,[6,21],"intern"],
    [10,"a76","a75_Fuso_Firm",2713,3382,3009,196,[10,9,1,15,14],"intern"],
    [11,"a75","a71_Actino",2512,3076,2743,173,[10,9,1],"intern"],
    [12,"a71","DeinoTherm_Cyano",2342,2814,2519,159,[10,9],"intern"],
    [13,"a68","Fuso_Firm",2602,3242,2880,210,[15,14],"intern"],
    [14,"a33","Euryarchaeota",3154,4168,3826,270,[13],"group"],
    [15,"a20","Crenarchaeota",3046,3919,3617,229,[8],"group"],
    [16,"a98","Gammaproteobacteria",1382,2113,1732,189,[16],"group"],
    [17,"a87","Betaproteobacteria",1107,1837,1455,187,[5],"group"],
    [18,"a86","Alphaproteobacteria",1650,2390,2007,192,[2],"group"],
    [19,"a81","Epsilonproteobacteria",858,1666,1236,207,[12],"group"],
    [20,"a79","Spirochaetes",1423,2254,1839,213,[21],"group"],
    [21,"a78","Chlamidia",320,1042,592,187,[6],"group"],
    [22,"a74","Actinobacteria",1032,1727,1357,179,[1],"group"],
    [23,"a70","Cyanobacteria",706,1355,1020,169,[9],"group"],
    [24,"a67","Firmicutes",2367,3013,2650,181,[14],"group"],
    [25,"lca","LCA",2500,4500,4290,0,[0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22],"root"]
   ].each { |tx|

     #set 0
     execute <<-EOF
     insert into mrcas
      (id, abrev, name, time_min, time_max, time_med, stdev, mrca_criter_id, level)
     values 
      (#{tx[0]},'#{tx[1]}','#{tx[2]}',#{tx[3]},#{tx[4]},#{tx[5]},#{tx[6]},0,'#{tx[8]}')
     EOF
      
     tx[7].each { |item|
         
        execute "insert into mrca_prok_groups
                  (mrca_id,prok_group_id)
                 values
                  (#{tx[0]},#{item})"  
      }
    }

    #STDEV
   [[0,"a34",[266,247,310,253,283,310,279,272,248,242,356,368,315,280,276],289],
    [1,"a106",[209,229,226,221,210,370,373,377,374,375,287,295,287,287,286,166,164,161,160,156],272],
    [2,"a105",[164,183,182,167,160,334,338,344,342,344,260,270,261,263,264,154,155,154,154,151],244],
    [3,"a104",[115,132,131,113,109,287,292,299,297,299,224,232,224,228,229,133,134,134,133,133],207],
    [4,"a103",[109,125,125,106,103,279,284,291,289,292,218,227,219,222,224,131,132,133,132,131],201],
    [5,"a102",[106,121,120,102,99,271,277,283,282,284,214,221,214,217,220,130,131,131,130,129],196],
    [6,"a101",[108,120,117,99,98,256,263,268,268,269,206,211,207,209,211,133,133,134,132,132],189],
    [7,"a100",[122,130,124,109,109,240,248,251,253,253,200,204,206,205,206,149,146,148,144,147],187],
    [8,"a99",[153,156,150,139,137,219,224,229,230,230,197,200,209,205,205,184,180,188,178,181],192],
    [9,"a80",[110,123,119,101,103,256,263,268,268,270,208,213,209,211,212,136,137,136,137,134],191],
    [10,"a76",[98,113,112,95,92,273,279,286,284,286,214,222,214,218,220,126,127,128,127,126],196],
    [11,"a75",[52,60,59,51,50,244,252,258,259,261,191,200,193,197,201,111,113,113,113,113],173],
    [12,"a71",[1,1,1,1,1,225,234,239,242,244,177,188,182,186,190,104,106,106,107,107],159],
    [13,"a68",[88,101,100,84,83,260,267,274,273,275,205,214,206,210,213,123,124,125,124,124],210],
    [14,"a33",[246,231,288,236,264,287,261,255,233,229,329,341,294,263,260],270],
    [15,"a20",[214,200,249,204,227,248,225,220,201,196,271,283,243,217,216],229],
    [16,"a98",[155,158,152,141,139,210,213,219,219,219,190,194,206,200,199,187,185,193,180,183],189],
    [17,"a87",[160,162,158,148,145,199,199,205,207,208,186,191,202,195,195,194,193,202,188,190],187],
    [18,"a86",[150,154,147,138,136,222,230,233,235,237,199,201,205,206,206,180,175,177,173,179],192],
    [19,"a81",[174,184,176,179,169,207,212,212,219,216,213,210,212,216,216,222,219,230,213,225],207],
    [20,"a79",[170,181,172,169,170,232,243,240,243,247,220,217,226,225,224,211,213,207,217,212],213],
    [21,"a78",[173,174,161,158,167,184,185,164,180,171,192,179,181,190,185,217,219,213,223,211],187],
    [22,"a74",[133,133,137,131,128,192,200,198,205,202,189,194,197,191,198,176,186,178,183,189],179],
    [23,"a70",[134,140,137,137,133,170,179,177,185,180,177,179,183,181,185,172,178,180,176,180],169],
    [24,"a67",[94,103,102,86,87,244,252,258,259,260,197,206,198,203,206,131,128,131,128,129],181]
   ].each { |tx|
     tx[2].each { |item|

        execute "insert into mrca_stdevs
                  (mrca_id,stdev)
                 values
                  (#{tx[0]},#{item})"
      }

   }

    #geo_2003_ordered
      #less restrictive conditions first
   [[100,"glca","root",4290,[1,2,3,4,5,6,7,8,9,10,12,14,13,14,15,16,17,18,19,20,21,22]],
    [101,"gt102","gt104_gt115",3460,[8,13]],
    [102,"gt104","gEuryarchaeota",3330,[13]],
    [103,"gt115","gCrenarchaeota",3010,[8]],

    #Aquifex mapped to Aquificae 3
    [104,"gt202","gAquifex",3460,[3]],
    #Thermotoga mapped to Thermotogae 22
    [105,"gt203","gThermotogae",3300,[22]],
    #Chloroflexus mapped to Chloroflexi 7
    [106,"gt205","gChloroflexus",3100,[7]],
    #Deinococcus mapped to Deinococcus-Thermus 10
    [107,"gt207","gDeinococcus",2850,[10]],
    #Chlamydia/Pirellula mapped to Chlamydiae/Verrucomicrobia 6
    [108,"gt211","gChlamydia",2700,[6]]
   ].each { |tx|

     #set 1
     execute "insert into mrcas
               (id,abrev,name,time_med,mrca_criter_id)
              values (#{tx[0]},'#{tx[1]}','#{tx[2]}',#{tx[3]},1)"

      tx[4].each { |item|

        execute "insert into mrca_prok_groups
                  (mrca_id,prok_group_id)
                 values
                  (#{tx[0]},#{item})"
      }
    }



  end
  
  def self.delete_data()
    execute "delete from mrca_prok_groups"
    execute "delete from mrcas"
    execute "delete from mrca_criters"
    execute "delete from mrca_stdevs"
    

  end
  
end
