-- =============================================================================
-- 워크플로우 단계별 상태 관리 테이블
-- DBMS: MariaDB 10.6+
-- =============================================================================

USE aislo_db;

CREATE TABLE IF NOT EXISTS project_workflow_stages (
  id INT AUTO_INCREMENT PRIMARY KEY,
  project_id CHAR(36) NOT NULL,
  stage_no TINYINT NOT NULL,              -- 0=파일입력, 1=WF1 ... 6=WF6
  stage_key VARCHAR(30) NOT NULL,         -- 'FILE_INPUT','WF1_SURFACE',...,'WF6_ESTIMATION'
  state ENUM('NOT_STARTED','IN_PROGRESS','COMPLETE','FAILED','STALE') NOT NULL DEFAULT 'NOT_STARTED',
  progress_percent TINYINT NOT NULL DEFAULT 0,
  params JSON NULL,                        -- 해당 단계에서 사용자가 선택/입력한 값 (재실행 시 재사용)
  message VARCHAR(255) NULL,               -- 진행/오류 메시지
  started_at TIMESTAMP NULL,
  completed_at TIMESTAMP NULL,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_project_stage (project_id, stage_no),
  INDEX idx_pws_project (project_id),
  CONSTRAINT fk_pws_project_id FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;