-- ============================================
-- V3.0__ops_maintenance_and_energy.sql
-- 运营管理模块 - 维保计划、维保任务、能耗监控
-- ============================================

-- ============================================
-- 维保计划表
-- ============================================
CREATE TABLE IF NOT EXISTS ops_maintenance_plan (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    project_id UUID NOT NULL,
    plan_code VARCHAR(50) UNIQUE NOT NULL,
    plan_name VARCHAR(100) NOT NULL,
    equipment_type VARCHAR(50),
    trigger_type VARCHAR(30) NOT NULL,
    trigger_value INTEGER,
    trigger_unit VARCHAR(20),
    maintenance_items TEXT,
    estimated_duration INTEGER,
    assigned_to UUID,
    sla_response_hours INTEGER,
    sla_complete_hours INTEGER,
    status VARCHAR(20) DEFAULT 'ACTIVE',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX IF NOT EXISTS idx_maintenance_plan_project ON ops_maintenance_plan(project_id);
CREATE INDEX IF NOT EXISTS idx_maintenance_plan_status ON ops_maintenance_plan(status);
CREATE INDEX IF NOT EXISTS idx_maintenance_plan_trigger ON ops_maintenance_plan(trigger_type);

-- ============================================
-- 维保任务表
-- ============================================
CREATE TABLE IF NOT EXISTS ops_maintenance_task (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    project_id UUID NOT NULL,
    task_code VARCHAR(50) UNIQUE NOT NULL,
    plan_id UUID,
    equipment_id UUID,
    task_type VARCHAR(20) DEFAULT 'PREVENTIVE',
    trigger_type VARCHAR(30),
    maintenance_items TEXT,
    status VARCHAR(20) DEFAULT 'PENDING',
    assigned_to UUID,
    scheduled_date TIMESTAMP,
    actual_start_date TIMESTAMP,
    actual_end_date TIMESTAMP,
    labor_hours DECIMAL(10,2),
    materials_cost DECIMAL(12,2),
    remarks TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX IF NOT EXISTS idx_maintenance_task_project ON ops_maintenance_task(project_id);
CREATE INDEX IF NOT EXISTS idx_maintenance_task_status ON ops_maintenance_task(status);
CREATE INDEX IF NOT EXISTS idx_maintenance_task_equipment ON ops_maintenance_task(equipment_id);
CREATE INDEX IF NOT EXISTS idx_maintenance_task_assignee ON ops_maintenance_task(assigned_to);
CREATE INDEX IF NOT EXISTS idx_maintenance_task_scheduled ON ops_maintenance_task(scheduled_date);

-- ============================================
-- 能耗计量点表
-- ============================================
CREATE TABLE IF NOT EXISTS ops_energy_meter (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    project_id UUID NOT NULL,
    meter_code VARCHAR(50) UNIQUE NOT NULL,
    meter_name VARCHAR(100) NOT NULL,
    energy_type VARCHAR(20) NOT NULL,
    space_node_id UUID,
    installation_location VARCHAR(200),
    rated_capacity DECIMAL(10,2),
    unit_price DECIMAL(10,4),
    status VARCHAR(20) DEFAULT 'ACTIVE',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX IF NOT EXISTS idx_energy_meter_project ON ops_energy_meter(project_id);
CREATE INDEX IF NOT EXISTS idx_energy_meter_type ON ops_energy_meter(energy_type);
CREATE INDEX IF NOT EXISTS idx_energy_meter_space ON ops_energy_meter(space_node_id);

-- ============================================
-- 能耗记录表
-- ============================================
CREATE TABLE IF NOT EXISTS ops_energy_consumption (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    project_id UUID NOT NULL,
    meter_id UUID NOT NULL,
    consumption_date DATE NOT NULL,
    previous_reading DECIMAL(12,2),
    current_reading DECIMAL(12,2) NOT NULL,
    consumption DECIMAL(12,2) NOT NULL,
    amount DECIMAL(10,2),
    recorded_by UUID,
    record_method VARCHAR(20) DEFAULT 'MANUAL',
    remarks TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX IF NOT EXISTS idx_energy_consumption_meter ON ops_energy_consumption(meter_id);
CREATE INDEX IF NOT EXISTS idx_energy_consumption_date ON ops_energy_consumption(consumption_date);
CREATE INDEX IF NOT EXISTS idx_energy_consumption_project ON ops_energy_consumption(project_id);

-- ============================================
-- 备件分类表
-- ============================================
CREATE TABLE IF NOT EXISTS ops_spare_part_category (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    parent_id UUID,
    category_code VARCHAR(50) UNIQUE NOT NULL,
    category_name VARCHAR(100) NOT NULL,
    description VARCHAR(500),
    sort_order INTEGER DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX IF NOT EXISTS idx_spare_part_category_parent ON ops_spare_part_category(parent_id);

-- ============================================
-- 备件表
-- ============================================
CREATE TABLE IF NOT EXISTS ops_spare_part (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    project_id UUID NOT NULL,
    spare_part_code VARCHAR(50) UNIQUE NOT NULL,
    spare_part_name VARCHAR(100) NOT NULL,
    category_id UUID,
    specification VARCHAR(200),
    unit VARCHAR(20) NOT NULL,
    safe_stock INTEGER DEFAULT 0,
    current_stock INTEGER DEFAULT 0,
    unit_price DECIMAL(10,2),
    supplier VARCHAR(200),
    supplier_contact VARCHAR(100),
    location VARCHAR(200),
    remarks TEXT,
    status VARCHAR(20) DEFAULT 'ACTIVE',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX IF NOT EXISTS idx_spare_part_project ON ops_spare_part(project_id);
CREATE INDEX IF NOT EXISTS idx_spare_part_category ON ops_spare_part(category_id);
CREATE INDEX IF NOT EXISTS idx_spare_part_stock ON ops_spare_part(current_stock);

-- ============================================
-- 备件记录表
-- ============================================
CREATE TABLE IF NOT EXISTS ops_spare_part_record (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    record_code VARCHAR(50) UNIQUE NOT NULL,
    record_type VARCHAR(20) NOT NULL,
    spare_part_id UUID NOT NULL,
    quantity INTEGER NOT NULL,
    balance INTEGER NOT NULL,
    related_order_id UUID,
    recorded_by UUID,
    record_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    remarks TEXT
);

CREATE INDEX IF NOT EXISTS idx_spare_part_record_part ON ops_spare_part_record(spare_part_id);
CREATE INDEX IF NOT EXISTS idx_spare_part_record_order ON ops_spare_part_record(related_order_id);
CREATE INDEX IF NOT EXISTS idx_spare_part_record_date ON ops_spare_part_record(record_date);

-- ============================================
-- 设备故障历史表
-- ============================================
CREATE TABLE IF NOT EXISTS ops_equipment_failure_history (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    equipment_id UUID NOT NULL,
    failure_date TIMESTAMP NOT NULL,
    failure_type VARCHAR(50),
    failure_cause VARCHAR(200),
    failure_description TEXT,
    repair_start_time TIMESTAMP,
    repair_end_time TIMESTAMP,
    repair_duration DECIMAL(10,2),
    repair_cost DECIMAL(12,2),
    spare_parts_used TEXT,
    work_order_id UUID,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX IF NOT EXISTS idx_equipment_failure_equipment ON ops_equipment_failure_history(equipment_id);
CREATE INDEX IF NOT EXISTS idx_equipment_failure_date ON ops_equipment_failure_history(failure_date);

-- ============================================
-- 设备健康度表
-- ============================================
CREATE TABLE IF NOT EXISTS ops_equipment_health_score (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    equipment_id UUID NOT NULL,
    score_date DATE NOT NULL,
    health_score DECIMAL(5,2),
    mtbf DECIMAL(10,2),
    mttr DECIMAL(10,2),
    failure_count INTEGER,
    maintenance_completion_rate DECIMAL(5,2),
    alert_level VARCHAR(20) DEFAULT 'NORMAL',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX IF NOT EXISTS idx_equipment_health_equipment ON ops_equipment_health_score(equipment_id);
CREATE INDEX IF NOT EXISTS idx_equipment_health_date ON ops_equipment_health_score(score_date);

-- ============================================
-- 点检模板表
-- ============================================
CREATE TABLE IF NOT EXISTS ops_inspection_template (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    project_id UUID NOT NULL,
    template_code VARCHAR(50) UNIQUE NOT NULL,
    template_name VARCHAR(100) NOT NULL,
    equipment_type VARCHAR(50),
    inspection_items TEXT,
    estimated_duration INTEGER,
    status VARCHAR(20) DEFAULT 'ACTIVE',
    version INTEGER DEFAULT 1,
    created_by UUID,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX IF NOT EXISTS idx_inspection_template_project ON ops_inspection_template(project_id);
CREATE INDEX IF NOT EXISTS idx_inspection_template_type ON ops_inspection_template(equipment_type);