EMS_Platform/backend/db/migrations/000003_schema_update.up.sql

-- 000003_schema_update.up.sql

-- 1. Create missing tables
CREATE TABLE IF NOT EXISTS sys_roles (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name VARCHAR(50),
    role_key VARCHAR(50) UNIQUE,
    data_scope VARCHAR(20),
    menu_check_strictly BOOLEAN DEFAULT TRUE,
    status VARCHAR(1)
);

CREATE TABLE IF NOT EXISTS sys_user_roles (
    user_id UUID NOT NULL,
    role_id UUID NOT NULL,
    PRIMARY KEY (user_id, role_id)
);

CREATE TABLE IF NOT EXISTS sys_configs (
    config_id SERIAL PRIMARY KEY,
    config_key VARCHAR(100) UNIQUE,
    config_value TEXT,
    config_type VARCHAR(10),
    remark VARCHAR(500)
);

CREATE TABLE IF NOT EXISTS sys_menus (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    parent_id UUID,
    name VARCHAR(50),
    path VARCHAR(200),
    component VARCHAR(255),
    perms VARCHAR(100),
    icon VARCHAR(100),
    type VARCHAR(1),
    order_num INTEGER,
    visible VARCHAR(1) DEFAULT '0',
    status VARCHAR(1) DEFAULT '0'
);

CREATE TABLE IF NOT EXISTS sys_role_menus (
    role_id UUID NOT NULL,
    menu_id UUID NOT NULL,
    PRIMARY KEY (role_id, menu_id)
);

CREATE TABLE IF NOT EXISTS equipment_cleaning_formula_templates (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name VARCHAR(100) NOT NULL,
    equipment_type VARCHAR(100) NOT NULL,
    formula JSONB,
    description TEXT,
    created_at BIGINT,
    updated_at BIGINT
);

CREATE TABLE IF NOT EXISTS backup_logs (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    file_name VARCHAR(255),
    file_path VARCHAR(500),
    size BIGINT,
    status VARCHAR(20),
    message TEXT,
    start_time TIMESTAMP,
    end_time TIMESTAMP,
    upload_status VARCHAR(20)
);

CREATE TABLE IF NOT EXISTS alarm_rule_bindings (
    rule_id UUID NOT NULL,
    target_id UUID NOT NULL,
    target_type VARCHAR(20),
    PRIMARY KEY (rule_id, target_id)
);

CREATE TABLE IF NOT EXISTS ai_analysis_reports (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    report_type VARCHAR(50),
    title VARCHAR(200),
    content TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    status VARCHAR(20),
    alert_count INTEGER
);

-- 2. Update existing tables (Users table in 000001 missed some fields)
ALTER TABLE users ADD COLUMN IF NOT EXISTS email VARCHAR(100) UNIQUE;
ALTER TABLE users ADD COLUMN IF NOT EXISTS phone_number VARCHAR(20) UNIQUE;
ALTER TABLE users ADD COLUMN IF NOT EXISTS status VARCHAR(1);
ALTER TABLE users ADD COLUMN IF NOT EXISTS created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP;

-- 3. Add Indexes
CREATE INDEX IF NOT EXISTS idx_sys_menus_parent_id ON sys_menus(parent_id);
CREATE INDEX IF NOT EXISTS idx_sys_user_roles_user_id ON sys_user_roles(user_id);
CREATE INDEX IF NOT EXISTS idx_sys_role_menus_role_id ON sys_role_menus(role_id);