-- =============================================================================
-- Migration 020: Add incident code and deadline to incidents table
-- Tables: incidents
-- Purpose: Add unique incident code (mã sự cố) and deadline field
-- Depends on: 019-add-maintenance-incidents-schema.sql
-- =============================================================================

BEGIN;

-- =============================================================================
-- ALTER TABLE: incidents
-- Add code (mã sự cố) and deadline fields
-- =============================================================================

-- Add code column (unique identifier for incident)
ALTER TABLE incidents
ADD COLUMN IF NOT EXISTS code VARCHAR(50);

-- Add deadline column (expected completion date)
ALTER TABLE incidents
ADD COLUMN IF NOT EXISTS deadline TIMESTAMP WITH TIME ZONE;

-- Create unique index on code (excluding soft-deleted records)
CREATE UNIQUE INDEX IF NOT EXISTS idx_incidents_code_unique 
ON incidents(code) 
WHERE deleted_at IS NULL AND code IS NOT NULL;

-- Create index on deadline for filtering/sorting by deadline
CREATE INDEX IF NOT EXISTS idx_incidents_deadline 
ON incidents(deadline) 
WHERE deleted_at IS NULL;

-- Create index on code for quick lookups
CREATE INDEX IF NOT EXISTS idx_incidents_code 
ON incidents(code) 
WHERE deleted_at IS NULL;

COMMIT;
