erp-mecanicas-diesel-database-v2/init/16-dispatch-schema.sql

-- ============================================================
-- 16-dispatch-schema.sql
-- Mecanicas Diesel - ERP Suite
--
-- Dispatch Center Schema (MMD-011)
-- Centro de despacho para asignacion de incidentes
-- ============================================================

-- Create dispatch schema
CREATE SCHEMA IF NOT EXISTS dispatch;

-- ============================================================
-- ENUMS
-- ============================================================

-- Unit operational status
CREATE TYPE dispatch.unit_status_enum AS ENUM (
    'available',
    'assigned',
    'en_route',
    'on_site',
    'returning',
    'offline',
    'maintenance'
);

-- Technician shift types
CREATE TYPE dispatch.shift_type AS ENUM (
    'morning',
    'afternoon',
    'night',
    'full_day',
    'on_call'
);

-- Skill levels
CREATE TYPE dispatch.skill_level AS ENUM (
    'basic',
    'intermediate',
    'advanced',
    'expert'
);

-- Escalation notification channels
CREATE TYPE dispatch.notification_channel AS ENUM (
    'email',
    'sms',
    'whatsapp',
    'push',
    'call'
);

-- Dispatch log actions
CREATE TYPE dispatch.dispatch_action AS ENUM (
    'created',
    'assigned',
    'reassigned',
    'rejected',
    'escalated',
    'cancelled',
    'acknowledged',
    'completed'
);

-- ============================================================
-- TABLES
-- ============================================================

-- Dispatch Board Configuration
CREATE TABLE dispatch.dispatch_boards (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL,
    name VARCHAR(100) NOT NULL,
    description TEXT,

    -- Map defaults
    default_zoom INTEGER DEFAULT 12,
    center_lat DECIMAL(10, 7) DEFAULT 19.4326,
    center_lng DECIMAL(10, 7) DEFAULT -99.1332,

    -- Behavior
    refresh_interval_seconds INTEGER DEFAULT 30,
    show_offline_units BOOLEAN DEFAULT true,
    auto_assign_enabled BOOLEAN DEFAULT false,
    max_suggestions INTEGER DEFAULT 5,

    -- Filters
    default_filters JSONB DEFAULT '{}',

    is_active BOOLEAN DEFAULT true,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW(),
    created_by UUID
);

CREATE INDEX idx_dispatch_boards_tenant ON dispatch.dispatch_boards(tenant_id);

-- Unit Status (real-time unit state)
CREATE TABLE dispatch.unit_statuses (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL,

    -- Unit reference
    unit_id UUID NOT NULL,
    unit_code VARCHAR(50),
    unit_name VARCHAR(100),

    -- Status
    status dispatch.unit_status_enum DEFAULT 'offline',

    -- Current assignment
    current_incident_id UUID,
    current_technician_ids UUID[] DEFAULT '{}',

    -- Location (cached from GPS)
    last_position_id UUID,
    last_known_lat DECIMAL(10, 7),
    last_known_lng DECIMAL(10, 7),
    last_location_update TIMESTAMPTZ,

    -- Timing
    last_status_change TIMESTAMPTZ DEFAULT NOW(),
    estimated_available_at TIMESTAMPTZ,

    -- Capacity and capabilities
    unit_capacity VARCHAR(20) DEFAULT 'light', -- light, medium, heavy
    can_tow BOOLEAN DEFAULT false,
    max_tow_weight_kg INTEGER,

    notes TEXT,
    metadata JSONB DEFAULT '{}',

    updated_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE UNIQUE INDEX idx_unit_statuses_tenant_unit ON dispatch.unit_statuses(tenant_id, unit_id);
CREATE INDEX idx_unit_statuses_status ON dispatch.unit_statuses(tenant_id, status);
CREATE INDEX idx_unit_statuses_current_incident ON dispatch.unit_statuses(current_incident_id) WHERE current_incident_id IS NOT NULL;

-- Technician Skills
CREATE TABLE dispatch.technician_skills (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL,

    -- Technician reference
    technician_id UUID NOT NULL,

    -- Skill definition
    skill_code VARCHAR(50) NOT NULL,
    skill_name VARCHAR(100) NOT NULL,
    skill_description TEXT,
    level dispatch.skill_level DEFAULT 'basic',

    -- Certification
    certification_number VARCHAR(100),
    certified_at DATE,
    expires_at DATE,
    certification_document_url VARCHAR(500),

    -- Status
    is_active BOOLEAN DEFAULT true,
    verified_by UUID,
    verified_at TIMESTAMPTZ,

    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW(),

    CONSTRAINT uq_technician_skill UNIQUE(tenant_id, technician_id, skill_code)
);

CREATE INDEX idx_technician_skills_tenant ON dispatch.technician_skills(tenant_id);
CREATE INDEX idx_technician_skills_technician ON dispatch.technician_skills(tenant_id, technician_id);
CREATE INDEX idx_technician_skills_code ON dispatch.technician_skills(tenant_id, skill_code);
CREATE INDEX idx_technician_skills_expiry ON dispatch.technician_skills(expires_at) WHERE expires_at IS NOT NULL;

-- Technician Shifts
CREATE TABLE dispatch.technician_shifts (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL,

    -- Technician reference
    technician_id UUID NOT NULL,

    -- Schedule
    shift_date DATE NOT NULL,
    shift_type dispatch.shift_type NOT NULL,
    start_time TIME NOT NULL,
    end_time TIME NOT NULL,

    -- On-call specifics
    is_on_call BOOLEAN DEFAULT false,
    on_call_priority INTEGER DEFAULT 0,

    -- Assignment
    assigned_unit_id UUID,

    -- Status
    actual_start_time TIMESTAMPTZ,
    actual_end_time TIMESTAMPTZ,
    is_absent BOOLEAN DEFAULT false,
    absence_reason TEXT,

    notes TEXT,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW(),
    created_by UUID
);

CREATE INDEX idx_technician_shifts_tenant ON dispatch.technician_shifts(tenant_id);
CREATE INDEX idx_technician_shifts_technician_date ON dispatch.technician_shifts(tenant_id, technician_id, shift_date);
CREATE INDEX idx_technician_shifts_date ON dispatch.technician_shifts(tenant_id, shift_date);
CREATE INDEX idx_technician_shifts_unit ON dispatch.technician_shifts(assigned_unit_id) WHERE assigned_unit_id IS NOT NULL;

-- Dispatch Rules (assignment criteria)
CREATE TABLE dispatch.dispatch_rules (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL,

    name VARCHAR(100) NOT NULL,
    description TEXT,
    priority INTEGER DEFAULT 0,

    -- Applicability
    service_type_code VARCHAR(50),
    incident_category VARCHAR(50),

    -- Conditions (JSON structure for flexibility)
    conditions JSONB NOT NULL DEFAULT '{}',
    /*
    conditions structure:
    {
        "requires_skills": ["DIESEL_ENGINE", "ELECTRICAL"],
        "requires_asset_categories": ["DIAGNOSTIC_TOOL"],
        "min_unit_capacity": "heavy",
        "max_distance_km": 50,
        "zone_codes": ["NORTE", "CENTRO"],
        "min_skill_level": "intermediate",
        "exclude_on_call": false
    }
    */

    -- Action
    auto_assign BOOLEAN DEFAULT false,
    assignment_weight INTEGER DEFAULT 100,

    is_active BOOLEAN DEFAULT true,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW(),
    created_by UUID
);

CREATE INDEX idx_dispatch_rules_tenant ON dispatch.dispatch_rules(tenant_id);
CREATE INDEX idx_dispatch_rules_service_type ON dispatch.dispatch_rules(tenant_id, service_type_code) WHERE service_type_code IS NOT NULL;
CREATE INDEX idx_dispatch_rules_priority ON dispatch.dispatch_rules(tenant_id, priority DESC);

-- Escalation Rules
CREATE TABLE dispatch.escalation_rules (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL,

    name VARCHAR(100) NOT NULL,
    description TEXT,

    -- Trigger conditions
    trigger_after_minutes INTEGER NOT NULL,
    trigger_status VARCHAR(50), -- incident status that triggers
    trigger_priority VARCHAR(20), -- only for specific priorities

    -- Escalation target
    escalate_to_role VARCHAR(50) NOT NULL,
    escalate_to_user_ids UUID[],

    -- Notification
    notification_channel dispatch.notification_channel NOT NULL,
    notification_template TEXT,
    notification_data JSONB DEFAULT '{}',

    -- Repeat
    repeat_interval_minutes INTEGER,
    max_escalations INTEGER DEFAULT 3,

    is_active BOOLEAN DEFAULT true,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW(),
    created_by UUID
);

CREATE INDEX idx_escalation_rules_tenant ON dispatch.escalation_rules(tenant_id);
CREATE INDEX idx_escalation_rules_trigger ON dispatch.escalation_rules(tenant_id, trigger_after_minutes);

-- Dispatch Log (audit trail)
CREATE TABLE dispatch.dispatch_logs (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL,

    -- Incident reference
    incident_id UUID NOT NULL,

    -- Action
    action dispatch.dispatch_action NOT NULL,

    -- Unit/Technician changes
    from_unit_id UUID,
    to_unit_id UUID,
    from_technician_id UUID,
    to_technician_id UUID,

    -- Context
    reason TEXT,
    automated BOOLEAN DEFAULT false,
    rule_id UUID, -- dispatch rule that caused action
    escalation_id UUID, -- escalation rule that caused action

    -- Response times
    response_time_seconds INTEGER,

    -- Actor
    performed_by UUID,
    performed_at TIMESTAMPTZ DEFAULT NOW(),

    -- Extra data
    metadata JSONB DEFAULT '{}'
);

CREATE INDEX idx_dispatch_logs_tenant ON dispatch.dispatch_logs(tenant_id);
CREATE INDEX idx_dispatch_logs_incident ON dispatch.dispatch_logs(tenant_id, incident_id);
CREATE INDEX idx_dispatch_logs_time ON dispatch.dispatch_logs(tenant_id, performed_at DESC);
CREATE INDEX idx_dispatch_logs_action ON dispatch.dispatch_logs(tenant_id, action);

-- ============================================================
-- TRIGGERS
-- ============================================================

-- Auto-update updated_at
CREATE OR REPLACE FUNCTION dispatch.update_updated_at()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_dispatch_boards_updated_at
    BEFORE UPDATE ON dispatch.dispatch_boards
    FOR EACH ROW EXECUTE FUNCTION dispatch.update_updated_at();

CREATE TRIGGER trg_unit_statuses_updated_at
    BEFORE UPDATE ON dispatch.unit_statuses
    FOR EACH ROW EXECUTE FUNCTION dispatch.update_updated_at();

CREATE TRIGGER trg_technician_skills_updated_at
    BEFORE UPDATE ON dispatch.technician_skills
    FOR EACH ROW EXECUTE FUNCTION dispatch.update_updated_at();

CREATE TRIGGER trg_technician_shifts_updated_at
    BEFORE UPDATE ON dispatch.technician_shifts
    FOR EACH ROW EXECUTE FUNCTION dispatch.update_updated_at();

CREATE TRIGGER trg_dispatch_rules_updated_at
    BEFORE UPDATE ON dispatch.dispatch_rules
    FOR EACH ROW EXECUTE FUNCTION dispatch.update_updated_at();

CREATE TRIGGER trg_escalation_rules_updated_at
    BEFORE UPDATE ON dispatch.escalation_rules
    FOR EACH ROW EXECUTE FUNCTION dispatch.update_updated_at();

-- Log unit status changes
CREATE OR REPLACE FUNCTION dispatch.log_unit_status_change()
RETURNS TRIGGER AS $$
BEGIN
    IF OLD.status IS DISTINCT FROM NEW.status THEN
        NEW.last_status_change = NOW();
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_unit_status_change
    BEFORE UPDATE ON dispatch.unit_statuses
    FOR EACH ROW EXECUTE FUNCTION dispatch.log_unit_status_change();

-- ============================================================
-- VIEWS
-- ============================================================

-- Available units with current position
CREATE OR REPLACE VIEW dispatch.vw_available_units AS
SELECT
    us.id,
    us.tenant_id,
    us.unit_id,
    us.unit_code,
    us.unit_name,
    us.status,
    us.last_known_lat,
    us.last_known_lng,
    us.last_location_update,
    us.unit_capacity,
    us.can_tow,
    us.max_tow_weight_kg,
    us.estimated_available_at,
    EXTRACT(EPOCH FROM (NOW() - us.last_location_update)) / 60 AS minutes_since_update
FROM dispatch.unit_statuses us
WHERE us.status = 'available';

-- Technicians on shift today
CREATE OR REPLACE VIEW dispatch.vw_technicians_on_shift AS
SELECT
    ts.id,
    ts.tenant_id,
    ts.technician_id,
    ts.shift_date,
    ts.shift_type,
    ts.start_time,
    ts.end_time,
    ts.is_on_call,
    ts.assigned_unit_id,
    ts.is_absent,
    CASE
        WHEN ts.is_absent THEN 'absent'
        WHEN ts.actual_start_time IS NOT NULL AND ts.actual_end_time IS NULL THEN 'active'
        WHEN ts.actual_start_time IS NULL THEN 'pending'
        ELSE 'completed'
    END AS shift_status
FROM dispatch.technician_shifts ts
WHERE ts.shift_date = CURRENT_DATE
  AND ts.is_absent = false;

-- Skills about to expire
CREATE OR REPLACE VIEW dispatch.vw_expiring_skills AS
SELECT
    tsk.id,
    tsk.tenant_id,
    tsk.technician_id,
    tsk.skill_code,
    tsk.skill_name,
    tsk.level,
    tsk.expires_at,
    tsk.expires_at - CURRENT_DATE AS days_until_expiry
FROM dispatch.technician_skills tsk
WHERE tsk.is_active = true
  AND tsk.expires_at IS NOT NULL
  AND tsk.expires_at <= CURRENT_DATE + INTERVAL '30 days';

-- Dispatch activity summary
CREATE OR REPLACE VIEW dispatch.vw_dispatch_activity AS
SELECT
    dl.tenant_id,
    DATE(dl.performed_at) AS activity_date,
    dl.action,
    COUNT(*) AS count,
    AVG(dl.response_time_seconds) AS avg_response_time_seconds,
    COUNT(CASE WHEN dl.automated THEN 1 END) AS automated_count
FROM dispatch.dispatch_logs dl
WHERE dl.performed_at >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY dl.tenant_id, DATE(dl.performed_at), dl.action;

-- ============================================================
-- RLS POLICIES
-- ============================================================

ALTER TABLE dispatch.dispatch_boards ENABLE ROW LEVEL SECURITY;
ALTER TABLE dispatch.unit_statuses ENABLE ROW LEVEL SECURITY;
ALTER TABLE dispatch.technician_skills ENABLE ROW LEVEL SECURITY;
ALTER TABLE dispatch.technician_shifts ENABLE ROW LEVEL SECURITY;
ALTER TABLE dispatch.dispatch_rules ENABLE ROW LEVEL SECURITY;
ALTER TABLE dispatch.escalation_rules ENABLE ROW LEVEL SECURITY;
ALTER TABLE dispatch.dispatch_logs ENABLE ROW LEVEL SECURITY;

-- RLS Policies for each table
CREATE POLICY tenant_isolation_dispatch_boards ON dispatch.dispatch_boards
    FOR ALL USING (tenant_id = current_setting('app.current_tenant', true)::uuid);

CREATE POLICY tenant_isolation_unit_statuses ON dispatch.unit_statuses
    FOR ALL USING (tenant_id = current_setting('app.current_tenant', true)::uuid);

CREATE POLICY tenant_isolation_technician_skills ON dispatch.technician_skills
    FOR ALL USING (tenant_id = current_setting('app.current_tenant', true)::uuid);

CREATE POLICY tenant_isolation_technician_shifts ON dispatch.technician_shifts
    FOR ALL USING (tenant_id = current_setting('app.current_tenant', true)::uuid);

CREATE POLICY tenant_isolation_dispatch_rules ON dispatch.dispatch_rules
    FOR ALL USING (tenant_id = current_setting('app.current_tenant', true)::uuid);

CREATE POLICY tenant_isolation_escalation_rules ON dispatch.escalation_rules
    FOR ALL USING (tenant_id = current_setting('app.current_tenant', true)::uuid);

CREATE POLICY tenant_isolation_dispatch_logs ON dispatch.dispatch_logs
    FOR ALL USING (tenant_id = current_setting('app.current_tenant', true)::uuid);

-- ============================================================
-- COMMENTS
-- ============================================================

COMMENT ON SCHEMA dispatch IS 'Dispatch Center schema for incident assignment and unit management (MMD-011)';
COMMENT ON TABLE dispatch.dispatch_boards IS 'Dispatch board configurations for map and assignment views';
COMMENT ON TABLE dispatch.unit_statuses IS 'Real-time status of fleet units for dispatch';
COMMENT ON TABLE dispatch.technician_skills IS 'Skills and certifications of technicians';
COMMENT ON TABLE dispatch.technician_shifts IS 'Shift schedules for technicians';
COMMENT ON TABLE dispatch.dispatch_rules IS 'Rules for automatic assignment suggestions';
COMMENT ON TABLE dispatch.escalation_rules IS 'Rules for escalating unresponded incidents';
COMMENT ON TABLE dispatch.dispatch_logs IS 'Audit log of all dispatch actions';