-- Import this file after selecting the cPanel-created database:
-- audetdxj_audessa
-- The database itself must be created through cPanel, so this script only
-- creates the application tables inside the currently selected database.

CREATE TABLE IF NOT EXISTS audit_settings (
  audit_id INT UNSIGNED NOT NULL PRIMARY KEY,
  department VARCHAR(120) NOT NULL,
  name VARCHAR(500) NOT NULL,
  frequency VARCHAR(80) NOT NULL,
  cqc_key ENUM('Safe','Effective','Caring','Responsive','Well-led') NOT NULL,
  status ENUM('completed','overdue','due-soon','not-due') NOT NULL,
  due_label VARCHAR(80) NOT NULL,
  score TINYINT UNSIGNED NULL,
  is_draft BOOLEAN NOT NULL DEFAULT FALSE,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS audit_records (
  audit_id INT UNSIGNED NOT NULL PRIMARY KEY,
  payload JSON NOT NULL,
  completed BOOLEAN NOT NULL DEFAULT FALSE,
  score TINYINT UNSIGNED NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_audit_record_setting FOREIGN KEY (audit_id) REFERENCES audit_settings(audit_id) ON DELETE CASCADE
) ENGINE=InnoDB;