package mysql

import (
	"database/sql"
)

var migrations = []struct {
	name string
	stmt string
}{
	{
		name: "create-table-box",
		stmt: createTableBox,
	},
	{
		name: "create-table-box-test-template",
		stmt: createTableBoxTestTemplate,
	},
	{
		name: "create-index-box-test-template-box",
		stmt: createIndexBoxTestTemplateBox,
	},
	{
		name: "create-index-box-template-test-template",
		stmt: createIndexBoxTemplateTestTemplate,
	},
	{
		name: "create-table-experiment",
		stmt: createTableExperiment,
	},
	{
		name: "create-index-experiment-box",
		stmt: createIndexExperimentBox,
	},
	{
		name: "create-table-cat-process",
		stmt: createTableCatProcess,
	},
	{
		name: "create-index-cat-process-cat",
		stmt: createIndexCatProcessCat,
	},
	{
		name: "create-index-cat-process-experiment-name",
		stmt: createIndexCatProcessExperimentName,
	},
	{
		name: "create-index-cat-process-box",
		stmt: createIndexCatProcessBox,
	},
	{
		name: "create-index-cat-process-box-name",
		stmt: createIndexCatProcessBoxName,
	},
	{
		name: "create-table-component-version",
		stmt: createTableComponentVersion,
	},
	{
		name: "create-index-component-version-cat-process",
		stmt: createIndexComponentVersionCatProcess,
	},
	{
		name: "create-table-cat-backup-info",
		stmt: createTableCatBackupInfo,
	},
	{
		name: "create-index-cat-backup-info",
		stmt: createIndexCatBackupInfo,
	},
	{
		name: "create-table-test-template",
		stmt: createTableTestTemplate,
	},
	{
		name: "create-table-test-result",
		stmt: createTableTestResult,
	},
	{
		name: "create-index-test-result-experiment",
		stmt: createIndexTestResultExperiment,
	},
	{
		name: "create-table-test-process",
		stmt: createTableTestProcess,
	},
	{
		name: "create-index-test-process-experiment",
		stmt: createIndexTestProcessExperiment,
	},
	{
		name: "create-index-test-process-test-template",
		stmt: createIndexTestProcessTestTemplate,
	},
	{
		name: "create-table-config-template",
		stmt: createTableConfigTemplate,
	},
	{
		name: "create-table-tag",
		stmt: createTableTag,
	},
	{
		name: "create-table-app-process",
		stmt: createTableAppProcess,
	},
	{
		name: "create-table-app-config",
		stmt: createTableAppConfig,
	},
	{
		name: "create-table-endpoint",
		stmt: createTableEndpoint,
	},
	{
		name: "create-table-manager-config",
		stmt: createTableManagerConfig,
	},
	{
		name: "create-table-trigger-job",
		stmt: createTableTriggerJob,
	},
	{
		name: "create-index-trigger-job-box",
		stmt: createIndexTriggerJobBox,
	},
	{
		name: "create-table-object",
		stmt: createTableObject,
	},
	{
		name: "create-index-object-experiment-ID",
		stmt: createIndexObjectExperimentID,
	},
	{
		name: "create-index-object-box-ID",
		stmt: createIndexObjectBoxID,
	},
	{
		name: "create-table-build-task",
		stmt: createTableBuildTask,
	},
	{
		name: "create-index-build-task-status",
		stmt: createIndexBuildTaskStatus,
	},
	{
		name: "create-index-build-task-create-time",
		stmt: createIndexBuildTaskCreateTime,
	},
}

// Migrate performs the database migration. If the migration fails
// and error is returned.
func Migrate(db *sql.DB) error {
	if err := createTable(db); err != nil {
		return err
	}
	completed, err := selectCompleted(db)
	if err != nil && err != sql.ErrNoRows {
		return err
	}
	for _, migration := range migrations {
		if _, ok := completed[migration.name]; ok {

			continue
		}

		if _, err := db.Exec(migration.stmt); err != nil {
			return err
		}
		if err := insertMigration(db, migration.name); err != nil {
			return err
		}

	}
	return nil
}

func createTable(db *sql.DB) error {
	_, err := db.Exec(migrationTableCreate)
	return err
}

func insertMigration(db *sql.DB, name string) error {
	_, err := db.Exec(migrationInsert, name)
	return err
}

func selectCompleted(db *sql.DB) (map[string]struct{}, error) {
	migrations := map[string]struct{}{}
	rows, err := db.Query(migrationSelect)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	for rows.Next() {
		var name string
		if err := rows.Scan(&name); err != nil {
			return nil, err
		}
		migrations[name] = struct{}{}
	}
	return migrations, nil
}

//
// migration table ddl and sql
//

var migrationTableCreate = `
CREATE TABLE IF NOT EXISTS migrations (
 name VARCHAR(255)
,UNIQUE(name)
)
`

var migrationInsert = `
INSERT INTO migrations (name) VALUES (?)
`

var migrationSelect = `
SELECT name FROM migrations
`

//
// 01_box.sql
//

var createTableBox = `
CREATE TABLE IF NOT EXISTS box (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(128),
  creator VARCHAR(256),
  description LONGTEXT,
  status VARCHAR(32),
  create_time DATETIME,
  update_time DATETIME,
  experiment_count BIGINT,
  update_node JSON,
  without_cat BOOLEAN,
  cat JSON,
  test_in_order BOOLEAN,
  test_tests JSON,
  test_labels VARCHAR(256),
  slack_channel_for_error VARCHAR(255),
  slack_channel_for_success VARCHAR(255),
  destroy_tidb_cluster BOOLEAN,
  box_type VARCHAR(128),
  tags JSON,
  rules JSON,
  timeout BIGINT,
  UNIQUE(name)
);
`

var createTableBoxTestTemplate = `
CREATE TABLE IF NOT EXISTS box_test_template (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  args LONGTEXT,
  test_template BIGINT,
  box BIGINT
);
`

var createIndexBoxTestTemplateBox = `
CREATE INDEX idx_box_test_template__box ON box_test_template (box);
`

var createIndexBoxTemplateTestTemplate = `
CREATE INDEX idx_box_test_template__test_template ON box_test_template (test_template);
`

//
// 02_experiment.sql
//

var createTableExperiment = `
CREATE TABLE IF NOT EXISTS experiment (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  labels JSON,
  name VARCHAR(256),
  namespace VARCHAR(256),
  box BIGINT,
  box_name VARCHAR(256),
  creator VARCHAR(256),
  box_index BIGINT,
  destroyed BOOLEAN,
  create_time DATETIME,
  update_time DATETIME,
  start_time DATETIME,
  end_time DATETIME,
  fail_time DATETIME,
  state VARCHAR(128),
  stage VARCHAR(128),
  author VARCHAR(128),
  annotation VARCHAR(1024),
  cat_count BIGINT,
  config JSON,
  report JSON
);
`

var createIndexExperimentBox = `
CREATE INDEX idx_experiment__box ON experiment (box);
`

//
// 03_cat.sql
//

//
// 04_cat_process.sql
//

var createTableCatProcess = `
CREATE TABLE IF NOT EXISTS cat_process (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  labels JSON,
  box BIGINT,
  box_name VARCHAR(256),
  experiment_name VARCHAR(256),
  cat BIGINT,
  owner VARCHAR(256),
  creator VARCHAR(256),
  create_time DATETIME,
  update_time DATETIME,
  name VARCHAR(256),
  namespace VARCHAR(256),
  destroyed BOOLEAN,
  config JSON,
  state VARCHAR(128),
  stage VARCHAR(128),
  report JSON,
  use_box_label BOOLEAN,
  ignore_labels JSON,
  backup_name VARCHAR(256)
);
`

var createIndexCatProcessCat = `
CREATE INDEX idx_cat_process__cat ON cat_process (cat);
`

var createIndexCatProcessExperimentName = `
CREATE INDEX idx_cat_process__experiment_name ON cat_process (experiment_name);
`

var createIndexCatProcessBox = `
CREATE INDEX idx_cat_process__box ON cat_process (box);
`

var createIndexCatProcessBoxName = `
CREATE INDEX idx_cat_process__box_name ON cat_process (box_name);
`

var createTableComponentVersion = `
CREATE TABLE IF NOT EXISTS component_version (
  id INTEGER PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(256),
  type VARCHAR(128),
  value VARCHAR(128),
  platform VARCHAR(128),
  compile_option VARCHAR(128),
  build_time DATETIME,
  cat_process BIGINT
);
`

var createIndexComponentVersionCatProcess = `
CREATE INDEX idx_component_version__cat_process ON component_version (cat_process);
`

//
// 05_cat_backup_info.sql
//

var createTableCatBackupInfo = `
CREATE TABLE IF NOT EXISTS cat_backup_info (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(256),
  namespace VARCHAR(255),
  cluster_name VARCHAR(255),
  cat_id BIGINT,
  cat_process BIGINT,
  state VARCHAR(128),
  create_time DATETIME,
  update_time DATETIME,
  spec JSON,
  status JSON
);
`

var createIndexCatBackupInfo = `
CREATE INDEX idx_cat_backup_info__cat_process ON cat_backup_info (cat_process);
`

//
// 06_test_template.sql
//

var createTableTestTemplate = `
CREATE TABLE IF NOT EXISTS test_template (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(128),
  creator VARCHAR(256),
  create_time DATETIME,
  update_time DATETIME,
  status VARCHAR(32),
  type VARCHAR(128),
  args LONGTEXT,
  error_info LONGTEXT,
  description LONGTEXT,
  parameters LONGTEXT,
  notes LONGTEXT,
  resource_config JSON ,
  tags JSON,
  source_binary_name VARCHAR(256),
  source_image VARCHAR(256),
  source_type VARCHAR(128),
  source_url VARCHAR(512),
  source_git_type VARCHAR(128),
  source_git_repo VARCHAR(128),
  source_git_value VARCHAR(256)
);
`

//
// 07_test_result.sql
//

var createTableTestResult = `
CREATE TABLE IF NOT EXISTS test_result (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  create_time DATETIME,
  update_time DATETIME,
  experiment BIGINT,
  box VARCHAR(256),
  test_name VARCHAR(256),
  last_test_result BIGINT,
  tag VARCHAR(256),
  eps DOUBLE,
  qps DOUBLE,
  avgms DOUBLE,
  pct_90 DOUBLE,
  pct_99 DOUBLE,
  pct_95 DOUBLE,
  spend DOUBLE,
  qps_diff DOUBLE,
  eps_diff DOUBLE,
  avgms_diff DOUBLE,
  pct_90_diff DOUBLE,
  pct_99_diff DOUBLE,
  pct_95_diff DOUBLE,
  query_results JSON,
  others JSON
);
`

var createIndexTestResultExperiment = `
CREATE INDEX idx_test_result__experiment ON test_result (experiment);
`

//
// 08_test_process.sql
//

var createTableTestProcess = `
CREATE TABLE IF NOT EXISTS test_process (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(256),
  labels JSON,
  box BIGINT,
  experiment BIGINT,
  override_args LONGTEXT,
  create_time DATETIME,
  update_time DATETIME,
  report JSON,
  test_template BIGINT,
  status VARCHAR(128),
  namespace VARCHAR(256),
  mark VARCHAR(1024)
);
`

var createIndexTestProcessExperiment = `
CREATE INDEX idx_test_process__experiment ON test_process (experiment);
`

var createIndexTestProcessTestTemplate = `
CREATE INDEX idx_test_process__test_template ON test_process (test_template);
`

//
// 09_config_template.sql
//

var createTableConfigTemplate = `
CREATE TABLE IF NOT EXISTS config_template (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(256),
  create_time DATETIME,
  update_time DATETIME,
  tag VARCHAR(256),
  description LONGTEXT,
  content LONGTEXT,
  resource_config JSON,
  is_deleted BOOLEAN
);
`

//
// 10_tag.sql
//

var createTableTag = `
CREATE TABLE IF NOT EXISTS tag (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(256),
  create_time DATETIME,
  update_time DATETIME,
  kind VARCHAR(128)
);
`

//
// 11_app_process.sql
//

var createTableAppProcess = `
CREATE TABLE IF NOT EXISTS app_process (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(256),
  namespace VARCHAR(256),
  config_id BIGINT,
  report JSON,
  create_time DATETIME,
  update_time DATETIME,
  status VARCHAR(128),
  mark VARCHAR(1024)
);
`

var createTableAppConfig = `
CREATE TABLE IF NOT EXISTS app_config (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(255),
  type VARCHAR(255),
  namespace VARCHAR(255),
  version VARCHAR(255),
  label JSON,
  test_id VARCHAR(255),
  experiment_id VARCHAR(255),
  box_id VARCHAR(255),
  binary_name VARCHAR(255),
  url VARCHAR(255),
  args VARCHAR(255),
  resource_requirement JSON,
  host VARCHAR(255),
  container_ids JSON,
  port INTEGER,
  envs JSON,
  actions JSON,
  image VARCHAR(255)
);
`

//
// 12_endpoint.sql
//

var createTableEndpoint = `
CREATE TABLE IF NOT EXISTS endpoint (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  labels JSON,
  name VARCHAR(256),
  value VARCHAR(256),
  create_time DATETIME,
  update_time DATETIME,
  type VARCHAR(256)
);
`

//
// 13_manager_config.sql
//

var createTableManagerConfig = `
CREATE TABLE IF NOT EXISTS manager_config (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  general_running_size_limit BIGINT,
  bench_running_size_limit BIGINT
);
`

//
// 14_trigger.sql
//

var createTableTriggerJob = `
CREATE TABLE IF NOT EXISTS trigger_job (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  type VARCHAR(128),
  git_repo VARCHAR(128),
  git_hash VARCHAR(255),
  git_branch VARCHAR(255),
  immediate_triggered BOOLEAN,
  triggerd BOOLEAN,
  box BIGINT UNIQUE,
  value VARCHAR(512)
);
`

var createIndexTriggerJobBox = `
CREATE INDEX idx_trigger_job__box ON trigger_job (box);
`

//
// 15_object.sql
//

var createTableObject = `
CREATE TABLE IF NOT EXISTS object (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  experiment_id BIGINT,
  box_id BIGINT,
  name VARCHAR(255),
  payload LONGBLOB,
  create_time DATETIME,
  update_time DATETIME
);
`

var createIndexObjectExperimentID = `
CREATE INDEX idx_object__experiment_id ON object (experiment_id);
`

var createIndexObjectBoxID = `
CREATE INDEX idx_object__box_id ON object (box_id);
`

//
// 16_build_task.sql
//

var createTableBuildTask = `
CREATE TABLE IF NOT EXISTS build_task
(
    id             BIGINT PRIMARY KEY AUTO_INCREMENT,
    status         VARCHAR(128),
    create_time    DATETIME,
    update_time    DATETIME,
    finish_time    DATETIME,
    repo           VARCHAR(256),
    branch         VARCHAR(256),
    platform       VARCHAR(256),
    compile_option VARCHAR(256),
    build_time     DATETIME,
    git_hash       VARCHAR(256),
    binary_url     VARCHAR(256),
    git_tag        VARCHAR(256),
    case_name      VARCHAR(256),
    force_t        BOOLEAN,
    image          VARCHAR(256),
    build_log      LONGTEXT,
    fuse           BOOLEAN
);
`

var createIndexBuildTaskStatus = `
CREATE INDEX idx_build_task__status ON build_task (status);
`

var createIndexBuildTaskCreateTime = `
CREATE INDEX idx_build_task__create_time ON build_task (create_time);
`