-- Create workflow instance tracking tables

CREATE TABLE IF NOT EXISTS workflow_instances (
  id SERIAL PRIMARY KEY,
  workflow_id INTEGER NOT NULL,
  record_id INTEGER NOT NULL,
  current_state_id INTEGER,
  started_by INTEGER,
  status VARCHAR(50) DEFAULT 'pending',
  started_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  isDeleted BOOLEAN NOT NULL DEFAULT FALSE,
  updatedAt TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS workflow_tasks (
  id SERIAL PRIMARY KEY,
  instance_id INTEGER NOT NULL,
  state_id INTEGER NOT NULL,
  assignee_user_id INTEGER,
  assignee_role VARCHAR(100),
  status VARCHAR(50) DEFAULT 'pending',
  due_at TIMESTAMP DEFAULT NULL,
  isDeleted BOOLEAN NOT NULL DEFAULT FALSE,
  updatedAt TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX workflow_instances_wf_idx ON workflow_instances(workflow_id);
CREATE INDEX workflow_tasks_instance_idx ON workflow_tasks(instance_id);
CREATE INDEX workflow_tasks_assignee_idx ON workflow_tasks(assignee_user_id);
