-- 2026-03-27: Allow workflow_id to be nullable for grouped programs

-- Workflow tracking schema
-- Supports soft deletion, auditing, JSONB metadata, indexes, and FK constraints
DO $$ BEGIN
    CREATE TYPE workflow_data_type_enum AS ENUM ('string', 'number', 'boolean', 'date', 'json');
EXCEPTION WHEN duplicate_object THEN NULL; END $$;

DO $$ BEGIN
    CREATE TYPE workflow_join_type_enum AS ENUM ('LEFT', 'RIGHT', 'INNER', 'FULL', 'CROSS');
EXCEPTION WHEN duplicate_object THEN NULL; END $$;

DO $$ BEGIN
    CREATE TYPE workflow_variable_scope_enum AS ENUM ('DEFAULT', 'SPECIFIC');
EXCEPTION WHEN duplicate_object THEN NULL; END $$;

DO $$ BEGIN
    CREATE TYPE workflow_lifecycle_status_enum AS ENUM ('ACTIVE', 'INACTIVE', 'DRAFT', 'ARCHIVED', 'DEPRECATED');
EXCEPTION WHEN duplicate_object THEN NULL; END $$;


CREATE TABLE IF NOT EXISTS workflow_configuration (
  id SERIAL PRIMARY KEY,
  workflow_key VARCHAR(100) NOT NULL UNIQUE,
  workflow_name VARCHAR(255) NOT NULL,
  program_type_key VARCHAR(100) NOT NULL,
  program_type_id INT NULL,
  workflow_version VARCHAR(30) NOT NULL,
  lifecycle_status workflow_lifecycle_status_enum NOT NULL DEFAULT 'ACTIVE',
  description TEXT NULL,
  data_query_template TEXT NULL,
  registration_rules JSONB NULL,
  initial_stage JSONB NULL,
  metadata JSONB NULL,
  created_by INT NULL,
  updated_by INT NULL,
  created_at TIMESTAMPTZ NOT NULL,
  updated_at TIMESTAMPTZ NOT NULL,
  deleted_at TIMESTAMPTZ NULL,
  CONSTRAINT fk_workflow_configuration_program_type FOREIGN KEY (program_type_id) REFERENCES program_type_v1(id),
  CONSTRAINT fk_workflow_configuration_created_by FOREIGN KEY (created_by) REFERENCES users(id),
  CONSTRAINT fk_workflow_configuration_updated_by FOREIGN KEY (updated_by) REFERENCES users(id)
);

CREATE INDEX IF NOT EXISTS idx_workflow_configuration_program_type_key ON workflow_configuration(program_type_key);
CREATE INDEX IF NOT EXISTS idx_workflow_configuration_lifecycle_status ON workflow_configuration(lifecycle_status) WHERE deleted_at IS NULL;

-- Link program_v1 to workflow_configuration
ALTER TABLE program_v1
ADD COLUMN IF NOT EXISTS workflow_id INT;

ALTER TABLE program_v1
DROP CONSTRAINT IF EXISTS fk_program_workflow;

ALTER TABLE program_v1
ADD CONSTRAINT fk_program_workflow
FOREIGN KEY (workflow_id)
REFERENCES workflow_configuration(id);

CREATE INDEX IF NOT EXISTS idx_program_workflow_id
ON program_v1(workflow_id);

ALTER TABLE program_v1
ALTER COLUMN workflow_id DROP NOT NULL;

CREATE TABLE IF NOT EXISTS workflow_stage (
  id SERIAL PRIMARY KEY,
  workflow_config_id INT NOT NULL,
  stage_key VARCHAR(100) NOT NULL,
  stage_name VARCHAR(255) NOT NULL,
  stage_order INT NOT NULL,
  stage_category VARCHAR(100) NOT NULL,
  is_optional BOOLEAN NOT NULL,
  description TEXT NULL,
  permissions JSONB NULL,
  prerequisite_rules_config JSONB NULL,
  execution_rules_config JSONB NULL,
  action_buttons JSONB NULL,
  next_stages JSONB NULL,
  completion_actions_config JSONB NULL,
  metadata JSONB NULL,
  created_by INT NULL,
  updated_by INT NULL,
  created_at TIMESTAMPTZ NOT NULL,
  updated_at TIMESTAMPTZ NOT NULL,
  deleted_at TIMESTAMPTZ NULL,
  CONSTRAINT fk_workflow_stage_workflow_config FOREIGN KEY (workflow_config_id) REFERENCES workflow_configuration(id),
  CONSTRAINT fk_workflow_stage_created_by FOREIGN KEY (created_by) REFERENCES users(id),
  CONSTRAINT fk_workflow_stage_updated_by FOREIGN KEY (updated_by) REFERENCES users(id),
  CONSTRAINT uq_workflow_stage_workflow_stage_key UNIQUE (workflow_config_id, stage_key)
);

CREATE INDEX IF NOT EXISTS idx_workflow_stage_order ON workflow_stage(workflow_config_id, stage_order) WHERE deleted_at IS NULL;

CREATE TABLE IF NOT EXISTS workflow_action (
  id SERIAL PRIMARY KEY,
  workflow_config_id INT NOT NULL,
  action_key VARCHAR(100) NOT NULL,
  action_name VARCHAR(255) NOT NULL,
  description TEXT NULL,
  permissions JSONB NULL,
  endpoints JSONB NULL,
  metadata JSONB NULL,
  created_by INT NULL,
  updated_by INT NULL,
  created_at TIMESTAMPTZ NOT NULL,
  updated_at TIMESTAMPTZ NOT NULL,
  deleted_at TIMESTAMPTZ NULL,
  CONSTRAINT fk_workflow_action_workflow_config FOREIGN KEY (workflow_config_id) REFERENCES workflow_configuration(id),
  CONSTRAINT fk_workflow_action_created_by FOREIGN KEY (created_by) REFERENCES users(id),
  CONSTRAINT fk_workflow_action_updated_by FOREIGN KEY (updated_by) REFERENCES users(id),
  CONSTRAINT uq_workflow_action_workflow_action_key UNIQUE (workflow_config_id, action_key)
);

CREATE INDEX IF NOT EXISTS idx_workflow_action_workflow ON workflow_action(workflow_config_id) WHERE deleted_at IS NULL;

CREATE TABLE IF NOT EXISTS registration_workflow_stage (
  id SERIAL PRIMARY KEY,
  registration_id INT NOT NULL,
  workflow_stage_id INT NOT NULL,
  workflow_stage_key VARCHAR(100) NOT NULL,
  status VARCHAR(50) NOT NULL,
  completed_at TIMESTAMPTZ NULL,
  completion_data JSONB NULL,
  metadata JSONB NULL,
  created_by INT NULL,
  updated_by INT NULL,
  created_at TIMESTAMPTZ NOT NULL,
  updated_at TIMESTAMPTZ NOT NULL,
  deleted_at TIMESTAMPTZ NULL,
  CONSTRAINT fk_registration_workflow_stage_registration FOREIGN KEY (registration_id) REFERENCES hdb_program_registration(id),
  CONSTRAINT fk_registration_workflow_stage_workflow_stage FOREIGN KEY (workflow_stage_id) REFERENCES workflow_stage(id),
  CONSTRAINT fk_registration_workflow_stage_created_by FOREIGN KEY (created_by) REFERENCES users(id),
  CONSTRAINT fk_registration_workflow_stage_updated_by FOREIGN KEY (updated_by) REFERENCES users(id)
);

CREATE INDEX IF NOT EXISTS idx_registration_workflow_stage_registration ON registration_workflow_stage(registration_id) WHERE deleted_at IS NULL;
CREATE INDEX IF NOT EXISTS idx_registration_workflow_stage_status ON registration_workflow_stage(status) WHERE deleted_at IS NULL;
