CREATE TABLE users (
  id VARCHAR(36) NOT NULL PRIMARY KEY,
  username VARCHAR(32) NOT NULL UNIQUE,
  email VARCHAR(255) NOT NULL UNIQUE,
  role VARCHAR(20) NOT NULL,
  last_login_at TIMESTAMP,
  min_issued_at TIMESTAMP NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  version BIGINT NOT NULL DEFAULT 0
);

CREATE TABLE instances (
  id VARCHAR(36) NOT NULL PRIMARY KEY,
  friendly_name VARCHAR(32) NOT NULL,
  icon VARCHAR(64) NOT NULL,
  owner_id VARCHAR(36) NOT NULL,
  session_lifecycle VARCHAR(20) NOT NULL,
  settings TEXT NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  version BIGINT NOT NULL DEFAULT 0,
  FOREIGN KEY (owner_id) REFERENCES users(id) ON DELETE CASCADE
);

CREATE TABLE instance_audit_logs (
  id VARCHAR(36) NOT NULL PRIMARY KEY,
  type VARCHAR(30) NOT NULL,
  data VARCHAR(4000),
  instance_id VARCHAR(36) NOT NULL,
  user_id VARCHAR(36) NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  version BIGINT NOT NULL DEFAULT 0,
  FOREIGN KEY (instance_id) REFERENCES instances(id) ON DELETE CASCADE,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

CREATE TABLE server_config (
  id BIGINT NOT NULL PRIMARY KEY,
  settings TEXT NOT NULL,
  version BIGINT NOT NULL DEFAULT 0
);

CREATE TABLE scripts (
  id VARCHAR(36) NOT NULL PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  description VARCHAR(1000),
  instance_id VARCHAR(36) NOT NULL,
  nodes_json TEXT NOT NULL,
  edges_json TEXT NOT NULL,
  paused BOOLEAN NOT NULL DEFAULT FALSE,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  version BIGINT NOT NULL DEFAULT 0,
  FOREIGN KEY (instance_id) REFERENCES instances(id) ON DELETE CASCADE
);
