-- =============================================================================
-- BeKind Backend — Maintenance Incidents Schema
-- Tables: technicians, incident_types, incidents, incident_files
-- Purpose: Manage maintenance requests, repairs, and incident tracking
-- Depends on: 001-auth-schema.sql (users, update_updated_at_column)
--             002-file-schema.sql (files)
--             005-resident-and-facility-schema.sql (rooms)
-- =============================================================================

BEGIN;

-- =============================================================================
-- EXTENSIONS
-- =============================================================================

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

-- =============================================================================
-- ENUM TYPES
-- =============================================================================

-- Technician status
DO $$ BEGIN
    IF NOT EXISTS (SELECT 1 FROM pg_type WHERE typname = 'technician_status') THEN
        CREATE TYPE technician_status AS ENUM ('active', 'inactive', 'on_leave', 'terminated');
    END IF;
END $$;

-- Incident priority levels
DO $$ BEGIN
    IF NOT EXISTS (SELECT 1 FROM pg_type WHERE typname = 'incident_priority') THEN
        CREATE TYPE incident_priority AS ENUM ('low', 'medium', 'high', 'urgent');
    END IF;
END $$;

-- Incident status workflow
DO $$ BEGIN
    IF NOT EXISTS (SELECT 1 FROM pg_type WHERE typname = 'incident_status') THEN
        CREATE TYPE incident_status AS ENUM (
            'new',                    -- Mới báo cáo
            'assigned',               -- Đã giao cho kỹ thuật viên
            'in_progress',            -- Đang xử lý
            'pending_material',       -- Chờ vật liệu
            'completed',              -- Hoàn thành
            'cancelled',              -- Đã hủy
            'on_hold'                 -- Tạm dừng
        );
    END IF;
END $$;

-- =============================================================================
-- TABLE: technicians (Kỹ thuật viên)
-- =============================================================================

CREATE TABLE IF NOT EXISTS technicians (
    -- Primary
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),

    -- Personal info
    full_name VARCHAR(100) NOT NULL,
    phone VARCHAR(20) NOT NULL,
    email VARCHAR(100),

    -- Specialty (e.g., electrician, plumber, general)
    specialty VARCHAR(100) NOT NULL,

    -- Status
    status technician_status NOT NULL DEFAULT 'active',

    -- Media
    avatar_id UUID REFERENCES files(id) ON DELETE SET NULL,

    -- Notes
    notes TEXT,

    -- Soft delete
    deleted_at TIMESTAMP WITH TIME ZONE,
    deleted_by UUID REFERENCES users(id) ON DELETE SET NULL,

    -- Audit fields
    created_by UUID REFERENCES users(id) ON DELETE SET NULL,
    updated_by UUID REFERENCES users(id) ON DELETE SET NULL,
    created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,

    -- Constraints
    CONSTRAINT technicians_full_name_not_empty CHECK (length(trim(full_name)) > 0),
    CONSTRAINT technicians_phone_not_empty CHECK (length(trim(phone)) > 0),
    CONSTRAINT technicians_soft_delete_consistency CHECK (
        (deleted_at IS NULL AND deleted_by IS NULL)
        OR (deleted_at IS NOT NULL)
    )
);

-- Indexes
CREATE INDEX IF NOT EXISTS idx_technicians_status ON technicians(status) WHERE deleted_at IS NULL;
CREATE INDEX IF NOT EXISTS idx_technicians_specialty ON technicians(specialty) WHERE deleted_at IS NULL;
CREATE UNIQUE INDEX IF NOT EXISTS idx_technicians_phone ON technicians(phone) WHERE deleted_at IS NULL;
CREATE UNIQUE INDEX IF NOT EXISTS idx_technicians_email ON technicians(email) WHERE deleted_at IS NULL AND email IS NOT NULL;

-- =============================================================================
-- TABLE: incident_types (Loại sự cố)
-- Types of maintenance incidents (electrical, plumbing, structural, etc.)
-- =============================================================================

CREATE TABLE IF NOT EXISTS incident_types (
    -- Primary
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),

    -- Type info
    name VARCHAR(100) NOT NULL,             
    description TEXT,                           -- Mô tả chi tiết

    -- Soft delete
    deleted_at TIMESTAMP WITH TIME ZONE,
    deleted_by UUID REFERENCES users(id) ON DELETE SET NULL,

    -- Audit fields
    created_by UUID REFERENCES users(id) ON DELETE SET NULL,
    updated_by UUID REFERENCES users(id) ON DELETE SET NULL,
    created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,

    -- Constraints
    CONSTRAINT incident_types_soft_delete_consistency CHECK (
        (deleted_at IS NULL AND deleted_by IS NULL)
        OR (deleted_at IS NOT NULL)
    )
);

-- Indexes
CREATE UNIQUE INDEX IF NOT EXISTS idx_incident_types_name ON incident_types(name) WHERE deleted_at IS NULL;
-- =============================================================================
-- TABLE: incidents (Sự cố / Yêu cầu sửa chữa)
-- Main incident/maintenance request table
-- =============================================================================

CREATE TABLE IF NOT EXISTS incidents (
    -- Primary
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),

    -- Details
    title VARCHAR(255) NOT NULL,
    description TEXT,

    -- References
    type_id UUID NOT NULL REFERENCES incident_types(id) ON DELETE RESTRICT,
    room_id UUID NOT NULL REFERENCES rooms(id) ON DELETE RESTRICT,
    reporter_id UUID NOT NULL REFERENCES users(id) ON DELETE RESTRICT,

    -- Assignment
    technician_id UUID REFERENCES technicians(id) ON DELETE SET NULL,

    -- Severity & Workflow
    priority incident_priority NOT NULL DEFAULT 'medium',
    status incident_status NOT NULL DEFAULT 'new',

    -- Soft delete
    deleted_at TIMESTAMP WITH TIME ZONE,
    deleted_by UUID REFERENCES users(id) ON DELETE SET NULL,

    -- Audit fields
    created_by UUID REFERENCES users(id) ON DELETE SET NULL,
    updated_by UUID REFERENCES users(id) ON DELETE SET NULL,
    created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,

    -- Constraints
    CONSTRAINT incidents_title_not_empty CHECK (length(trim(title)) > 0)
);

-- Indexes
CREATE INDEX IF NOT EXISTS idx_incidents_type_id ON incidents(type_id) WHERE deleted_at IS NULL;
CREATE INDEX IF NOT EXISTS idx_incidents_room_id ON incidents(room_id) WHERE deleted_at IS NULL;
CREATE INDEX IF NOT EXISTS idx_incidents_reporter_id ON incidents(reporter_id) WHERE deleted_at IS NULL;
CREATE INDEX IF NOT EXISTS idx_incidents_technician_id ON incidents(technician_id) WHERE deleted_at IS NULL;
CREATE INDEX IF NOT EXISTS idx_incidents_priority ON incidents(priority) WHERE deleted_at IS NULL;
CREATE INDEX IF NOT EXISTS idx_incidents_status ON incidents(status) WHERE deleted_at IS NULL;
CREATE INDEX IF NOT EXISTS idx_incidents_created_at ON incidents(created_at DESC) WHERE deleted_at IS NULL;

-- =============================================================================
-- TABLE: incident_files (Tệp đính kèm sự cố)
-- Stores attachments/photos for incidents
-- Linking table between incidents and files (many-to-many)
-- =============================================================================

CREATE TABLE IF NOT EXISTS incident_files (
    -- Primary
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),

    -- Links
    incident_id UUID NOT NULL REFERENCES incidents(id) ON DELETE CASCADE,
    file_id UUID NOT NULL REFERENCES files(id) ON DELETE CASCADE,

    -- Soft delete
    deleted_at TIMESTAMP WITH TIME ZONE,
    deleted_by UUID REFERENCES users(id) ON DELETE SET NULL,

    -- Audit fields
    created_by UUID REFERENCES users(id) ON DELETE SET NULL,
    updated_by UUID REFERENCES users(id) ON DELETE SET NULL,
    created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,

    -- Constraints
    CONSTRAINT incident_files_soft_delete_consistency CHECK (
        (deleted_at IS NULL AND deleted_by IS NULL)
        OR (deleted_at IS NOT NULL)
    )
);

-- Indexes
CREATE INDEX IF NOT EXISTS idx_incident_files_incident_id ON incident_files(incident_id) WHERE deleted_at IS NULL;
CREATE INDEX IF NOT EXISTS idx_incident_files_file_id ON incident_files(file_id);

-- =============================================================================
-- DEFAULT DATA
-- =============================================================================

-- Insert default incident types
INSERT INTO incident_types (name, description) VALUES
    ('Điện', 'Vấn đề liên quan đến hệ thống điện'),
    ('Nước', 'Vấn đề liên quan đến hệ thống nước'),
    ('Điều hòa', 'Vấn đề liên quan đến hệ thống điều hòa không khí'),
    ('Cấu trúc', 'Vấn đề liên quan đến cấu trúc, dột, nứt tường'),
    ('Thiết bị', 'Vấn đề liên quan đến thiết bị, nội thất'),
    ('Côn trùng', 'Vấn đề về côn trùng, chuột'),
    ('Bảo mật', 'Vấn đề liên quan đến an ninh, khóa cửa'),
    ('Vệ sinh', 'Yêu cầu vệ sinh, làm sạch'),
    ('Khác', 'Các vấn đề khác')
ON CONFLICT DO NOTHING;

-- =============================================================================
-- QUERIES & EXAMPLES
-- =============================================================================

-- Get all open incidents sorted by priority and creation date:
-- SELECT i.*, r.id as room_number, t.full_name as technician_name, it.name_vi as incident_type
-- FROM incidents i
-- LEFT JOIN rooms r ON r.id = i.room_id
-- LEFT JOIN technicians t ON t.id = i.technician_id
-- LEFT JOIN incident_types it ON it.id = i.type_id
-- WHERE i.deleted_at IS NULL
--   AND i.status != 'completed'
-- ORDER BY i.priority DESC, i.created_at DESC;

-- Get incidents assigned to a specific technician:
-- SELECT * FROM incidents
-- WHERE technician_id = ? AND deleted_at IS NULL AND status != 'completed'
-- ORDER BY priority DESC, created_at;

-- Get incident with all attachments:
-- SELECT i.*, array_agg(f.id) as file_ids
-- FROM incidents i
-- LEFT JOIN incident_files f ON f.incident_id = i.id AND f.deleted_at IS NULL
-- WHERE i.id = ? AND i.deleted_at IS NULL
-- GROUP BY i.id;

-- =============================================================================
-- ROLLBACK (if needed)
-- =============================================================================
-- DROP TABLE IF EXISTS incident_files;
-- DROP TABLE IF EXISTS incidents;
-- DROP TABLE IF EXISTS incident_types;
-- DROP TABLE IF EXISTS technicians;
-- DROP TYPE IF EXISTS technician_status;
-- DROP TYPE IF EXISTS incident_priority;
-- DROP TYPE IF EXISTS incident_status;
-- =============================================================================

COMMIT;
