# -*- coding: utf-8 -*-
class CreateShifts < ActiveRecord::Migration
  def self.up
    create_table :shifts do |t|
      t.date :shift_date
      t.time :time_from, :time_to
      t.string :department, :type
      t.integer :regular_shift_id, :employee_id, :status
      t.decimal :nighttime, :evening_time,  :precision => 3, :scale => 1
      t.timestamps
    end
    
    create_table :regular_shifts do |t|
      t.time :time_from, :time_to
      t.string :department, :type
      t.integer :employee_id, :day_of_week
      t.timestamps
    end
    ## Менеджеры  
    Shift.connection.execute("INSERT INTO shifts (employee_id, shift_date, department, type) SELECT b.empl_id, b.date, department, 'ManagerShift' FROM managers b")
    
    ## Регулярные смены
    Shift.connection.execute("INSERT INTO regular_shifts (employee_id, day_of_week, department, type, time_from, time_to) SELECT b.empl_id, WEEKDAY(DATE(r.date))+1, r.department, 'RegularWorkShift', b.time_addwork, b.time_stopwork FROM done_employees b LEFT JOIN reports r on r.id = b.report_id WHERE DATE(r.date) BETWEEN DATE('2000/1/2') AND DATE('2000/1/12') AND global_reasons=1")
    
    ## Смены софтов со временем
    Shift.connection.execute("INSERT INTO shifts (employee_id, shift_date, department, type, time_from, time_to) SELECT b.empl_id, DATE(r.date), 'soft', 'WorkShift', b.time_addwork, b.time_stopwork FROM done_employees b LEFT JOIN reports r on r.id = b.report_id WHERE DATE(r.date) > DATE('2000/1/12') AND global_reasons=1 and r.department = 'soft' and b.role = 1")

    ## Смены мод, couriers и корп без времени
    Shift.connection.execute("INSERT INTO shifts (employee_id, shift_date, department, type) SELECT b.empl_id, DATE(r.date), r.department, 'WorkShift' FROM done_employees b LEFT JOIN reports r on r.id = b.report_id WHERE DATE(r.date) > DATE('2000/1/12') AND global_reasons=1 and r.department != 'welder' and r.department != 'soft' and b.role = 1")
    
    ## Сварщики
    Shift.connection.execute("INSERT INTO shifts (employee_id, shift_date, department, type, nighttime, evening_time, status) SELECT b.empl_id, DATE(r.date), 'welder', 'WorkShift', b.time_stopwork, b.time_work, r.status FROM done_employees b LEFT JOIN reports r on r.id = b.report_id WHERE DATE(r.date) > DATE('2000/1/12') AND global_reasons=1 and r.department = 'welder' and b.role = 1")

    Shift.connection.execute("UPDATE reports r SET status = 2 WHERE r.department = 'welder'")

    ## Связываем рабочие смены с регулярным расписанием
    WorkShift.from_old_format

    ## Удаляем все лишнее
    drop_table :managers
    Report.destroy_all("global_reasons = 1 AND department != 'ams'")

  end


  def self.down
    drop_table :shifts
    drop_table :regular_shifts
  end
end