-- ═══════════════════════════════════════════════════════════════════════════
-- rsflow.dspera · Auth + Approval Chain schema (MVP-0)
-- Applied to fresh DB rookpmgt_rsflow.
-- Preserves the shape of hqdspera's user_groups, user_groups_perms,
-- fin_chain_defs, fin_chain_steps, fin_approvals, fin_approval_log
-- so ApprovalChain::* engine can be lifted verbatim later.
-- ═══════════════════════════════════════════════════════════════════════════

DROP TABLE IF EXISTS fin_approval_log;
DROP TABLE IF EXISTS fin_approvals;
DROP TABLE IF EXISTS fin_chain_steps;
DROP TABLE IF EXISTS fin_chain_defs;
DROP TABLE IF EXISTS user_groups_perms;
DROP TABLE IF EXISTS special_user_perms;
DROP TABLE IF EXISTS controllers_actions;
DROP TABLE IF EXISTS users;
DROP TABLE IF EXISTS user_groups;

CREATE TABLE user_groups (
  c0d              INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  user_group_name  VARCHAR(60) NOT NULL,
  created_by       INT NOT NULL DEFAULT 0,
  created_on       TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  edited_by        INT NOT NULL DEFAULT 0,
  edited_on        TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  deleted          TINYINT(1) NOT NULL DEFAULT 0,
  UNIQUE KEY uq_group_name (user_group_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE users (
  c0d              INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  username         VARCHAR(60) NOT NULL,
  password         VARCHAR(255) NOT NULL,
  full_name        VARCHAR(120) NOT NULL DEFAULT '',
  email            VARCHAR(120) NOT NULL DEFAULT '',
  phone            VARCHAR(40)  NOT NULL DEFAULT '',
  user_group_id    INT NOT NULL,
  user_group_name  VARCHAR(60) NOT NULL,
  status           VARCHAR(20) NOT NULL DEFAULT 'ACTIVE',
  last_login_on    TIMESTAMP NULL DEFAULT NULL,
  created_by       INT NOT NULL DEFAULT 0,
  created_on       TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  edited_by        INT NOT NULL DEFAULT 0,
  edited_on        TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  deleted          TINYINT(1) NOT NULL DEFAULT 0,
  UNIQUE KEY uq_username (username),
  KEY ix_group (user_group_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE controllers_actions (
  c0d              INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  controller_name  VARCHAR(80) NOT NULL,
  action_name      VARCHAR(80) NOT NULL,
  created_on       TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_ctrl_action (controller_name, action_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE user_groups_perms (
  c0d               INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  user_group_id     INT NOT NULL,
  controller_allowed VARCHAR(80) NOT NULL,
  action_allowed    VARCHAR(80) NOT NULL,
  created_by        INT NOT NULL DEFAULT 0,
  created_on        TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_perm (user_group_id, controller_allowed, action_allowed),
  KEY ix_group (user_group_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE special_user_perms (
  c0d               INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  user_id           INT NOT NULL,
  controller_allowed VARCHAR(80) NOT NULL,
  action_allowed    VARCHAR(80) NOT NULL,
  created_by        INT NOT NULL DEFAULT 0,
  created_on        TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_special (user_id, controller_allowed, action_allowed),
  KEY ix_user (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Approval chain engine (verbatim from hqdspera shape) --------------------

CREATE TABLE fin_chain_defs (
  c0d           INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  chain_key     VARCHAR(80) NOT NULL,
  title         VARCHAR(160) NOT NULL,
  description   TEXT,
  status        VARCHAR(20) NOT NULL DEFAULT 'ACTIVE',
  created_by    INT NOT NULL DEFAULT 0,
  created_on    TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  edited_by     INT NOT NULL DEFAULT 0,
  edited_on     TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  deleted       TINYINT(1) NOT NULL DEFAULT 0,
  UNIQUE KEY uq_chain_key (chain_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE fin_chain_steps (
  c0d            INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  chain_id       INT NOT NULL,
  step_no        INT NOT NULL,
  step_key       VARCHAR(60) NOT NULL,
  step_label     VARCHAR(120) NOT NULL,
  role_required  VARCHAR(60) NOT NULL,
  is_terminal    TINYINT(1) NOT NULL DEFAULT 0,
  description    TEXT,
  sort_order     INT NOT NULL DEFAULT 0,
  created_by     INT NOT NULL DEFAULT 0,
  created_on     TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  edited_by      INT NOT NULL DEFAULT 0,
  edited_on      TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  deleted        TINYINT(1) NOT NULL DEFAULT 0,
  KEY ix_chain (chain_id),
  KEY ix_role_req (role_required)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE fin_approvals (
  c0d               INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  chain_id          INT NOT NULL,
  entity_type       VARCHAR(60) NOT NULL,
  entity_id         INT NOT NULL,
  current_step_id   INT DEFAULT NULL,
  status            VARCHAR(20) NOT NULL DEFAULT 'IN_PROGRESS',
  ref_label         VARCHAR(200) NOT NULL DEFAULT '',
  summary_json      TEXT,
  return_url        VARCHAR(255),
  submitted_by      INT NOT NULL DEFAULT 0,
  submitted_by_name VARCHAR(160),
  submitted_on      TIMESTAMP NULL DEFAULT NULL,
  decided_on        TIMESTAMP NULL DEFAULT NULL,
  created_by        INT NOT NULL DEFAULT 0,
  created_on        TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  edited_by         INT NOT NULL DEFAULT 0,
  edited_on         TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  deleted           TINYINT(1) NOT NULL DEFAULT 0,
  KEY ix_chain (chain_id),
  KEY ix_entity (entity_type, entity_id),
  KEY ix_step (current_step_id),
  KEY ix_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE fin_approval_log (
  c0d               INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  approval_id       INT NOT NULL,
  from_step_id      INT DEFAULT NULL,
  to_step_id        INT DEFAULT NULL,
  decision          VARCHAR(20) NOT NULL,
  decided_by        INT NOT NULL,
  decided_by_name   VARCHAR(160) NOT NULL DEFAULT '',
  decided_by_group  VARCHAR(60) NOT NULL DEFAULT '',
  decided_on        TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  comment           TEXT,
  KEY ix_approval (approval_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
