UP_SQL = """
CREATE TABLE afe_drones (
  id INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
  hostname VARCHAR(255) NOT NULL
) ENGINE=InnoDB;

ALTER TABLE afe_drones
ADD CONSTRAINT afe_drones_unique
UNIQUE KEY (hostname);


CREATE TABLE afe_drone_sets (
  id INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
  name VARCHAR(255) NOT NULL
) ENGINE=InnoDB;

ALTER TABLE afe_drone_sets
ADD CONSTRAINT afe_drone_sets_unique
UNIQUE KEY (name);


CREATE TABLE afe_drone_sets_drones (
  id INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
  droneset_id INT NOT NULL,
  drone_id INT NOT NULL
) ENGINE=InnoDB;

ALTER TABLE afe_drone_sets_drones
ADD CONSTRAINT afe_drone_sets_drones_droneset_ibfk
FOREIGN KEY (droneset_id) REFERENCES afe_drone_sets (id);

ALTER TABLE afe_drone_sets_drones
ADD CONSTRAINT afe_drone_sets_drones_drone_ibfk
FOREIGN KEY (drone_id) REFERENCES afe_drones (id);

ALTER TABLE afe_drone_sets_drones
ADD CONSTRAINT afe_drone_sets_drones_unique
UNIQUE KEY (droneset_id, drone_id);


ALTER TABLE afe_jobs
ADD COLUMN drone_set_id INT;

ALTER TABLE afe_jobs
ADD CONSTRAINT afe_jobs_drone_set_ibfk
FOREIGN KEY (drone_set_id) REFERENCES afe_drone_sets (id);


ALTER TABLE afe_users
ADD COLUMN drone_set_id INT;

ALTER TABLE afe_users
ADD CONSTRAINT afe_users_drone_set_ibfk
FOREIGN KEY (drone_set_id) REFERENCES afe_drone_sets (id);


UPDATE afe_special_tasks SET requested_by_id = (
  SELECT id FROM afe_users WHERE login = 'autotest_system')
WHERE requested_by_id IS NULL;

SET foreign_key_checks = 0;

ALTER TABLE afe_special_tasks
MODIFY COLUMN requested_by_id INT NOT NULL;

SET foreign_key_checks = 1;
"""


DOWN_SQL = """
ALTER TABLE afe_special_tasks
MODIFY COLUMN requested_by_id INT DEFAULT NULL;

ALTER TABLE afe_users
DROP FOREIGN KEY afe_users_drone_set_ibfk;

ALTER TABLE afe_users
DROP COLUMN drone_set_id;

ALTER TABLE afe_jobs
DROP FOREIGN KEY afe_jobs_drone_set_ibfk;

ALTER TABLE afe_jobs
DROP COLUMN drone_set_id;

DROP TABLE IF EXISTS afe_drone_sets_drones;
DROP TABLE IF EXISTS afe_drone_sets;
DROP TABLE IF EXISTS afe_drones;
"""