5945176ad4
기존 TBM 시스템의 daily_work_reports 테이블과 이름 충돌. 협력업체 업무현황 테이블을 partner_work_reports로 분리. Co-Authored-By: Claude Opus 4.6 <noreply@anthropic.com>
33 lines
1.4 KiB
SQL
33 lines
1.4 KiB
SQL
-- migration-purchase-safety-patch.sql
|
|
-- 배포 후 스키마 보완 패치
|
|
-- 생성일: 2026-03-13
|
|
|
|
-- 4-a. check_in_time NOT NULL (체크인 시 시간은 항상 존재)
|
|
ALTER TABLE partner_work_checkins
|
|
MODIFY check_in_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP;
|
|
|
|
-- 4-b. partner_work_reports 테이블 생성 (daily_work_reports 이름 충돌 → 별도 이름)
|
|
CREATE TABLE IF NOT EXISTS partner_work_reports (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
schedule_id INT NOT NULL,
|
|
checkin_id INT NOT NULL,
|
|
company_id INT NOT NULL,
|
|
report_date DATE NOT NULL,
|
|
reporter_id INT NOT NULL,
|
|
actual_workers INT,
|
|
work_content TEXT,
|
|
progress_rate TINYINT,
|
|
issues TEXT,
|
|
next_plan TEXT,
|
|
confirmed_by INT,
|
|
confirmed_at DATETIME,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
INDEX idx_pwr_report_date (report_date),
|
|
INDEX idx_pwr_schedule (schedule_id),
|
|
UNIQUE INDEX uq_pwr_schedule_report_date (schedule_id, report_date),
|
|
CONSTRAINT fk_pwr_schedule FOREIGN KEY (schedule_id) REFERENCES partner_schedules(id),
|
|
CONSTRAINT fk_pwr_checkin FOREIGN KEY (checkin_id) REFERENCES partner_work_checkins(id),
|
|
CONSTRAINT fk_pwr_company FOREIGN KEY (company_id) REFERENCES partner_companies(id)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|