-- Systemicly: Tasks table (MVP)
-- Run this once on your systemicly database.

CREATE TABLE IF NOT EXISTS tasks (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  -- NOTE: must match users.id type (in this project it's INT UNSIGNED)
  user_id INT UNSIGNED NOT NULL,
  -- NOTE: must match systems.id type (BIGINT UNSIGNED)
  system_id BIGINT UNSIGNED NULL,
  title VARCHAR(160) NOT NULL,
  notes TEXT NULL,
  due_on DATE NULL,
  status ENUM('open','done') NOT NULL DEFAULT 'open',
  priority TINYINT NOT NULL DEFAULT 0,
  completed_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  deleted_at DATETIME NULL,
  PRIMARY KEY (id),
  KEY idx_user_status_due (user_id, status, due_on),
  KEY idx_user_system (user_id, system_id),
  CONSTRAINT fk_tasks_user
    FOREIGN KEY (user_id) REFERENCES users(id)
    ON DELETE CASCADE,
  CONSTRAINT fk_tasks_system
    FOREIGN KEY (system_id) REFERENCES systems(id)
    ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
