EMS_Platform/backend/db/migrations/000001_init.up.sql

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

CREATE TABLE IF NOT EXISTS integration_sources (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name VARCHAR(50),
    driver_type VARCHAR(20),
    config JSONB,
    status VARCHAR(20)
);

CREATE TABLE IF NOT EXISTS sys_locations (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    parent_id UUID,
    name VARCHAR(50),
    type VARCHAR(20)
);

CREATE TABLE IF NOT EXISTS devices (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    source_id UUID,
    external_id VARCHAR(100),
    name VARCHAR(100),
    original_name VARCHAR(100),
    device_type VARCHAR(50),
    location_id UUID,
    dept_id UUID,
    metering_mode VARCHAR(20),
    rated_power NUMERIC(10,2),
    attribute_mapping JSONB,
    status VARCHAR(20)
);

CREATE TABLE IF NOT EXISTS alarm_logs (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    device_id UUID,
    type VARCHAR(50),
    content TEXT,
    status VARCHAR(20),
    start_time TIMESTAMP
);

CREATE TABLE IF NOT EXISTS sys_oper_logs (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID,
    action VARCHAR(50),
    target VARCHAR(100),
    result BOOLEAN,
    time TIMESTAMP
);

CREATE TABLE IF NOT EXISTS inspection_ledgers (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    device_id UUID,
    inspector_id UUID,
    status VARCHAR(20),
    calibrated_power NUMERIC(10,2),
    check_time TIMESTAMP
);

CREATE TABLE IF NOT EXISTS users (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    username VARCHAR(50) UNIQUE,
    password VARCHAR(100),
    role VARCHAR(20),
    name VARCHAR(50)
);