<?php

class TbMigrate {
  var $oldDb;
  var $newDb;

  function __construct() {
    print "Opening 'taskboard.db'...";
    try {
      $this->oldDb = new SQLite3('taskboard.db', SQLITE3_OPEN_READONLY);
      print "\t\tDone.\n";
    } catch (Exception $e) {
      print "\t\tError opening 'taskboard.db'\n";
      print "  " . $e->getMessage() . "\n";
      return;
    }

    print "Opening 'taskboard.sqlite'...";
    try {
      unlink('taskboard.sqlite'); // Always start fresh.

      $this->newDb = new SQLite3('taskboard.sqlite');
      print "\t\tDone.\n\n";
    } catch (Exception $e) {
      print "\t\tError creating 'taskboard.sqlite'\n";
      print "  " . $e->getMessage() . "\n";
      return;
    }
  }

  function __destruct() {
    print "\n";
    print "Closing 'taskboard.sqlite'...";
    $this->newDb->exec('PRAGMA foreign_keys = ON');
    $this->newDb->close();
    print "\t\tDone.\n";

    print "Closing 'taskboard.db'...";
    $this->oldDb->close();
    print "\t\tDone.\n";
  }

  function migrate() {
    $this->createTables();

    $this->migrateActivity();
    // TODO: attachments table
    $this->migrateAutoAction();
    $this->migrateBoard();
    $this->migrateBoardUser();
    $this->migrateCategory();
    $this->migrateCollapsed();
    $this->migrateComment();
    $this->migrateItem();
    $this->migrateLane();
    $this->migrateUser();
  }

  private function createTables() {
    print " Creating new tables...";
    $schema = file_get_contents('schema');

    $this->newDb->exec($schema);
    $this->newDb->exec('PRAGMA foreign_keys = OFF');
    print "\t\t\tDone.\n\n";
  }

  private function getItemType($str) {
    if (strpos($str, 'item') !== false) {
      return 'task';
    }

    if (strpos($str, 'column') !== false) {
      return 'column';
    }

    if (strpos($str, 'board') !== false) {
      return 'board';
    }

    if (strpos($str, 'changed') !== false || strpos($str, 'logged') !== false ||
      strpos($str, 'added') !== false || strpos($str, 'removed') !== false) {
      return 'user';
    }

    return null;
  }

  private function getItemId($itemId, $newValue) {
    if (isset($itemId)) {
      return $itemId;
    }

    $obj = json_decode($newValue);

    if ($obj !== null) {
      return $obj->id;
    }

    return null;
  }

  private function getActionType($row) {
    switch ($row['action_id']) {
    case 0:
      return 1;
      break;
    case 2:
      return 4;
      break;
    case 3:
      return 6;
      break;
    default:
      return null;
    }
  }

  private function getActionChange($row) {
    if (isset($row['color'])) {
      return $row['color'];
    }

    if (isset($row['category_id'])) {
      return $row['category_id'];
    }

    if (isset($row['assignee_id'])) {
      return $row['assignee_id'];
    }

    return null;
  }

  private function migrateTable($stmtStr, $fn, $oldTable = null) {
    $table = isset($oldTable) ? $oldTable : explode(' ', $stmtStr)[2];

    print " Migrating table `$table`...";

    $results = $this->oldDb->query("SELECT * from $table ORDER BY id");

    if ($results->numColumns() === 0) {
      print " no rows.\n";
      return;
    }

    $stmt = $this->newDb->prepare($stmtStr);
    $rowCount = 0;
    $errCount = 0;

    while ($row = $results->fetchArray()) {
      $fn($stmt, $row);

      if ($stmt->execute() === false) {
        $errCount++;
        continue;
      }

      $rowCount++;
    }

    $stmt->close();
    print strlen($table) < 10 ? "\t" : "";
    print "\t$rowCount rows migrated.\n";

    if ($errCount > 0) {
      print " $errCount rows failed.\n";
    }
  }

  private function migrateActivity() {
    $stmtStr = 'INSERT INTO activity (id, user_id, log_text, before, after, ' .
      'item_type, item_id, timestamp) VALUES (:id, :user_id, :log_text, ' .
      ':before, :after, :item_type, :item_id, :timestamp)';
    $knownNames = [];

    $this->migrateTable($stmtStr, function(&$stmt, $row) use ($knownNames) {
      $name = explode(' ', $row['comment'])[0];

      if (!array_key_exists($name, $knownNames)) {
        $usr = $this->oldDb->query('SELECT id FROM user ' .
          'WHERE username = "' . $name . '"');

        $knownNames[$name] = $usr->fetchArray()['id'];
      }

      $type = $this->getItemType($row['comment']);

      $stmt->bindValue(':id', $row['id']);
      $stmt->bindValue(':user_id', $knownNames[$name]);
      $stmt->bindValue(':log_text', $row['comment']);
      $stmt->bindValue(':before', $row['old_value']);
      $stmt->bindValue(':after', $row['new_value']);
      $stmt->bindValue(':item_type', $type);
      $stmt->bindValue(':item_id', $type === 'user'
        ? $knownNames[$name]
        : $this->getItemId($row['item_id'], $row['new_value']));
      $stmt->bindValue(':timestamp', $row['timestamp']);
    });
  }

  private function migrateAutoAction() {
    $stmtStr = 'INSERT INTO autoaction (id, trigger, source_id, type, ' .
      'change_to, board_id) VALUES (:id, :trigger, :source_id, :type, ' .
      ':change_to, :board_id)';

    $this->migrateTable($stmtStr, function (&$stmt, $row) {
      $stmt->bindValue(':id', $row['id']);
      $stmt->bindValue(':trigger', ((int)$row['trigger_id']) + 1);
      $stmt->bindValue(':source_id', $row['secondary_id']);
      $stmt->bindValue(':type', $this->getActionType($row));
      $stmt->bindValue(':change_to', $this->getActionChange($row));
      $stmt->bindValue(':board_id', $row['board_id']);
    });
  }

  private function migrateBoard() {
    $stmtStr = 'INSERT INTO board (id, name, is_active) ' .
      'VALUES (:id, :name, :is_active)';

    $this->migrateTable($stmtStr, function (&$stmt, $row) {
      $stmt->bindValue(':id', $row['id']);
      $stmt->bindValue(':name', $row['name']);
      $stmt->bindValue(':is_active', $row['active']);
    });
  }

  private function migrateBoardUser() {
    $stmtStr = 'INSERT INTO board_user (id, user_id, board_id) ' .
      'VALUES (:id, :user_id, :board_id)';

    $this->migrateTable($stmtStr, function (&$stmt, $row) {
      $stmt->bindValue(':id', $row['id']);
      $stmt->bindValue(':user_id', $row['user_id']);
      $stmt->bindValue(':board_id', $row['board_id']);
    });
  }

  private function migrateCategory() {
    $stmtStr = 'INSERT INTO category (id, name, default_task_color, board_id) ' .
      'VALUES (:id, :name, "#ffffe0", :board_id)';

    $this->migrateTable($stmtStr, function (&$stmt, $row) {
      $stmt->bindValue(':id', $row['id']);
      $stmt->bindValue(':name', $row['name']);
      $stmt->bindValue(':board_id', $row['board_id']);
    });
  }

  private function migrateCollapsed() {
    $stmtStr = 'INSERT INTO collapsed (id, user_id, column_id) ' .
      'VALUES (:id, :user_id, :column_id)';

    $this->migrateTable($stmtStr, function (&$stmt, $row) {
      $stmt->bindValue(':id', $row['id']);
      $stmt->bindValue(':user_id', $row['user_id']);
      $stmt->bindValue(':column_id', $row['lane_id']);
    });
  }

  private function migrateComment() {
    $stmtStr = 'INSERT INTO comment (id, text, user_id, task_id, timestamp, ' .
      'is_edited) VALUES (:id, :text, :user_id, :task_id, :timestamp, 0)';

    $this->migrateTable($stmtStr, function (&$stmt, $row) {
      $stmt->bindValue(':id', $row['id']);
      $stmt->bindValue(':text', $row['text']);
      $stmt->bindValue(':user_id', $row['user_id']);
      $stmt->bindValue(':task_id', $row['item_id']);
      $stmt->bindValue(':timestamp', $row['timestamp']);
    });
  }

  private function migrateItem() {
    $stmtStr = 'INSERT INTO task (id, title, description, color, due_date, ' .
      'points, position, column_id) VALUES (:id, :title, :description, :color, ' .
      ':due_date, :points, :position, :column_id)';

    $this->migrateTable($stmtStr, function (&$stmt, $row) {
      $stmt->bindValue(':id', $row['id']);
      $stmt->bindValue(':title', $row['title']);
      $stmt->bindValue(':description', $row['description']);
      $stmt->bindValue(':color', $row['color']);
      $stmt->bindValue(':due_date', $row['due_date']);
      $stmt->bindValue(':points', $row['points']);
      $stmt->bindValue(':position', $row['position']);
      $stmt->bindValue(':column_id', $row['lane_id']);

      $this->newDb->exec('INSERT INTO task_user (user_id, task_id) ' .
        'VALUES (' . $row['assignee'] . ', ' . $row['id'] . ')');
      $this->newDb->exec('INSERT INTO category_task (category_id, task_id) ' .
        'VALUES (' . $row['category'] . ', ' . $row['id'] . ')');
    }, 'item');
  }

  private function migrateLane() {
    $stmtStr = 'INSERT INTO column (id, name, position, board_id) ' .
      'VALUES (:id, :name, :position, :board_id)';

    $this->migrateTable($stmtStr, function (&$stmt, $row) {
      $stmt->bindValue(':id', $row['id']);
      $stmt->bindValue(':name', $row['name']);
      $stmt->bindValue(':position', $row['position']);
      $stmt->bindValue(':board_id', $row['board_id']);
    }, 'lane');
  }

  private function migrateUser() {
    $stmtStr = 'INSERT INTO user (id, username, security_level, password_hash, ' .
      'email, default_board_id, user_option_id, last_login) VALUES (:id, ' .
      ':uname, :secLev, :hash, :email, :board, :option, :last)';

    $this->migrateTable($stmtStr, function (&$stmt, $row) {
      $stmt->bindValue(':id', $row['id']);
      $stmt->bindValue(':uname', $row['username']);
      $stmt->bindValue(':secLev', $row['is_admin'] === 1 ? 1 : 3);
      $stmt->bindValue(':hash', $row['password']);
      $stmt->bindValue(':email', $row['email']);
      $stmt->bindValue(':board', $row['default_board']);
      $stmt->bindValue(':option', $row['id']);
      $stmt->bindValue(':last', $row['last_login']);
    });

    $users = $this->newDb->query('SELECT id FROM user ORDER BY id');

    while ($row = $users->fetchArray()) {
      $stmt = $this->newDb->prepare('INSERT INTO useroption (id, ' .
        'new_tasks_at_bottom, show_animations, show_assignee, ' .
        'multiple_tasks_per_row, language) VALUES (:id, :atBottom, :anims, ' .
        ':assignee, 0, "en")');

      $opts = $this->oldDb->query(
        'SELECT * FROM option WHERE id = ' . (int)$row['id']
      );
      $opts = $opts->fetchArray();

      if ($opts === false) {
        continue;
      }

      $stmt->bindValue(':id', $opts['id']);
      $stmt->bindValue(':atBottom', isset($opts['tasks_order'])
        ? $opts['tasks_order']
        : $opts['new_task_position']);
      $stmt->bindValue(':anims', isset($opts['animate'])
        ? $opts['animate']
        : $opts['show_animations']);
      $stmt->bindValue(':assignee', isset($opts['show_assignee'])
        ? $opts['show_assignee']
        : 1);

      $stmt->execute();
    }

    $stmt->close();
  }

}

$tbm = new TbMigrate();
$tbm->migrate();

