274 lines
9.2 KiB
PL/PgSQL
274 lines
9.2 KiB
PL/PgSQL
-- Postgres database for lamp-control system
|
|
|
|
CREATE TABLE users (
|
|
id SERIAL PRIMARY KEY,
|
|
first_name VARCHAR(50),
|
|
last_name VARCHAR(50),
|
|
role SMALLINT NOT NULL DEFAULT 0 CHECK (role BETWEEN 0 AND 3), -- 0=viewer, 1=user, 2=operator, 3=administrator
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
last_login TIMESTAMPTZ,
|
|
banned_at TIMESTAMPTZ,
|
|
deleted_at TIMESTAMPTZ
|
|
);
|
|
|
|
CREATE TABLE user_sessions (
|
|
id SERIAL PRIMARY KEY,
|
|
user_id INTEGER NOT NULL REFERENCES users(id),
|
|
session_token VARCHAR(255) UNIQUE NOT NULL,
|
|
expires_at TIMESTAMPTZ NOT NULL,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
CREATE TABLE zones (
|
|
id SERIAL PRIMARY KEY,
|
|
name VARCHAR(255) NOT NULL,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
deleted_at TIMESTAMPTZ
|
|
);
|
|
|
|
CREATE TABLE lamp_profiles (
|
|
id SERIAL PRIMARY KEY,
|
|
name VARCHAR(255) NOT NULL,
|
|
voltage INTEGER NOT NULL, -- mV (millivolts)
|
|
current INTEGER NOT NULL, -- mA (milliamps)
|
|
runtime INTEGER NOT NULL, -- minutes
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
deleted_at TIMESTAMPTZ
|
|
);
|
|
|
|
CREATE TABLE sensors (
|
|
id SERIAL PRIMARY KEY,
|
|
device_id VARCHAR(36) UNIQUE,
|
|
gb_id VARCHAR(36) UNIQUE,
|
|
latitude DECIMAL(9,6),
|
|
longitude DECIMAL(9,6),
|
|
zone_id INT REFERENCES zones(id),
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
deleted_at TIMESTAMPTZ
|
|
);
|
|
|
|
CREATE TABLE lamps_relay_boxes (
|
|
id SERIAL PRIMARY KEY,
|
|
device_id VARCHAR(36) UNIQUE, -- uuid
|
|
gb_id VARCHAR(36) UNIQUE, -- uuid
|
|
identification_number VARCHAR(50) UNIQUE,
|
|
-- TODO: calculate status instead of storing it?
|
|
status SMALLINT NOT NULL DEFAULT 0 CHECK (status BETWEEN 0 AND 2), -- 0=off, 1=on, 2=error
|
|
latitude DECIMAL(9,6),
|
|
longitude DECIMAL(9,6),
|
|
lamp_post_number VARCHAR(50) NOT NULL,
|
|
running_minutes INTEGER NOT NULL DEFAULT 0,
|
|
zone_id INT REFERENCES zones(id),
|
|
profile_id INT REFERENCES lamp_profiles(id),
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
deleted_at TIMESTAMPTZ
|
|
);
|
|
CREATE INDEX idx_lamp_relay_boxes_zone_id ON lamp_relay_boxes(zone_id);
|
|
|
|
-- TODO: maybe calculate running minutes based on metrics instead of status changes?
|
|
-- Function to calculate running minutes -- Modified function name and logic
|
|
CREATE OR REPLACE FUNCTION update_lamp_relay_box_running_minutes()
|
|
RETURNS TRIGGER AS $$
|
|
BEGIN
|
|
IF NEW.status = 1 AND OLD.status = 0 THEN
|
|
UPDATE lamp_relay_boxes
|
|
SET running_minutes = running_minutes + EXTRACT(EPOCH FROM (CURRENT_TIMESTAMP - OLD.updated_at)) / 60
|
|
WHERE id = NEW.id;
|
|
END IF;
|
|
RETURN NEW;
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
-- Trigger to update running minutes when lamp_relay_box status changes -- Modified trigger name
|
|
CREATE TRIGGER trg_update_lamp_relay_box_running_minutes
|
|
AFTER UPDATE OF status ON lamp_relay_boxes
|
|
FOR EACH ROW
|
|
EXECUTE FUNCTION trg_update_lamp_relay_box_running_minutes();
|
|
|
|
CREATE TABLE lamp_relay_box_logs (
|
|
id SERIAL PRIMARY KEY,
|
|
lamp_relay_box_id INT NOT NULL REFERENCES lamp_relay_boxes(id),
|
|
status SMALLINT NOT NULL, -- 0=off, 1=on, 2=error
|
|
event_type SMALLINT NOT NULL, -- 0=manual, 1=scheduled, 2=error, 3=gb
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
CREATE INDEX idx_lamp_relay_box_logs_lamp_relay_box_id_created_at ON lamp_relay_box_logs(lamp_relay_box_id, created_at);
|
|
|
|
CREATE TABLE metrics (
|
|
id SERIAL PRIMARY KEY,
|
|
lamp_relay_box_id INTEGER NOT NULL REFERENCES lamp_relay_boxes(id),
|
|
metric SMALLINT NOT NULL CHECK (metric BETWEEN 0 AND 6), -- 0=current, 1=voltage, 2=power, 3=energy, 4=lux, 5=brightness, 6=motions
|
|
value INTEGER NOT NULL,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
CREATE INDEX idx_metrics_lamp_relay_box_id_metric_created_at ON metrics(lamp_relay_box_id, metric, created_at);
|
|
|
|
-- Create a view for real-time lamp_relay_box status
|
|
CREATE VIEW real_time_lamp_relay_box_status AS
|
|
SELECT
|
|
l.id,
|
|
l.identification_number,
|
|
l.status, -- TODO: calculate status instead of storing it?
|
|
l.latitude,
|
|
l.longitude,
|
|
lux.value AS lux,
|
|
motions.value AS motions,
|
|
power.value AS power,
|
|
current.value AS current,
|
|
voltage.value AS voltage,
|
|
energy.value AS energy,
|
|
brightness.value AS brightness,
|
|
GREATEST(lux.created_at, motions.created_at, power.created_at, current.created_at, voltage.created_at, energy.created_at, brightness.created_at) AS last_updated
|
|
FROM lamp_relay_boxes l
|
|
LEFT JOIN LATERAL (
|
|
SELECT value, created_at
|
|
FROM metrics
|
|
WHERE lamp_relay_box_id = l.id
|
|
AND metric = 0 -- current
|
|
ORDER BY created_at DESC
|
|
LIMIT 1
|
|
) current ON true
|
|
LEFT JOIN LATERAL (
|
|
SELECT value, created_at
|
|
FROM metrics
|
|
WHERE lamp_relay_box_id = l.id
|
|
AND metric = 1 -- voltage
|
|
ORDER BY created_at DESC
|
|
LIMIT 1
|
|
) voltage ON true
|
|
LEFT JOIN LATERAL (
|
|
SELECT value, created_at
|
|
FROM metrics
|
|
WHERE lamp_relay_box_id = l.id
|
|
AND metric = 2 -- power
|
|
ORDER BY created_at DESC
|
|
LIMIT 1
|
|
) power ON true
|
|
LEFT JOIN LATERAL (
|
|
SELECT value, created_at
|
|
FROM metrics
|
|
WHERE lamp_relay_box_id = l.id
|
|
AND metric = 3 -- energy
|
|
ORDER BY created_at DESC
|
|
LIMIT 1
|
|
) energy ON true
|
|
LEFT JOIN LATERAL (
|
|
SELECT value, created_at
|
|
FROM metrics
|
|
WHERE lamp_relay_box_id = l.id
|
|
AND metric = 4 -- lux
|
|
ORDER BY created_at DESC
|
|
LIMIT 1
|
|
) lux ON true
|
|
LEFT JOIN LATERAL (
|
|
SELECT value, created_at
|
|
FROM metrics
|
|
WHERE lamp_relay_box_id = l.id
|
|
AND metric = 5 -- brightness
|
|
ORDER BY created_at DESC
|
|
LIMIT 1
|
|
) brightness ON true
|
|
LEFT JOIN LATERAL (
|
|
SELECT value, created_at
|
|
FROM metrics
|
|
WHERE lamp_relay_box_id = l.id
|
|
AND metric = 6 -- motions
|
|
ORDER BY created_at DESC
|
|
LIMIT 1
|
|
) motions ON true;
|
|
|
|
-- Function to update lamp_relay_box status based on metrics
|
|
CREATE OR REPLACE FUNCTION update_lamp_relay_box_status()
|
|
RETURNS TRIGGER AS $$
|
|
BEGIN
|
|
UPDATE lamp_relay_boxes
|
|
SET
|
|
status = CASE
|
|
WHEN NEW.power_watts > 0 THEN 1
|
|
ELSE 0
|
|
END,
|
|
updated_at = NEW.created_at
|
|
WHERE id = NEW.lamp_relay_box_id;
|
|
RETURN NEW;
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
-- Trigger to update lamp_relay_box status when new metrics is inserted
|
|
CREATE TRIGGER trigger_update_lamp_relay_box_status
|
|
AFTER INSERT ON metrics
|
|
FOR EACH ROW
|
|
EXECUTE FUNCTION update_lamp_relay_box_status();
|
|
|
|
CREATE TABLE schedules (
|
|
id SERIAL PRIMARY KEY,
|
|
type SMALLINT NOT NULL, -- 0=manual, 1=scheduled
|
|
start_time TIME NOT NULL,
|
|
end_time TIME NOT NULL,
|
|
status SMALLINT NOT NULL, -- 0=on, 1=off
|
|
days_of_week bit(7) NOT NULL, -- Bitmask for days of the week
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
deleted_at TIMESTAMPTZ
|
|
);
|
|
|
|
CREATE TABLE zone_schedules (
|
|
id SERIAL PRIMARY KEY,
|
|
zone_id INT NOT NULL REFERENCES zones(id),
|
|
schedule_id INTEGER REFERENCES schedules(id),
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
deleted_at TIMESTAMPTZ
|
|
);
|
|
CREATE INDEX idx_zone_schedules_zone_id ON zone_schedules(zone_id);
|
|
|
|
CREATE TABLE manual_overrides (
|
|
id SERIAL PRIMARY KEY,
|
|
zone_id INT NOT NULL REFERENCES zones(id),
|
|
user_id INT NOT NULL REFERENCES users(id),
|
|
start_time TIMESTAMPTZ NOT NULL,
|
|
end_time TIMESTAMPTZ NOT NULL,
|
|
type SMALLINT NOT NULL, -- 0=turn_off, 1=turn_on
|
|
reason VARCHAR(500),
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
deleted_at TIMESTAMPTZ
|
|
);
|
|
CREATE INDEX idx_manual_overrides_zone_id_start_time ON manual_overrides(zone_id, start_time);
|
|
|
|
CREATE TABLE alerts (
|
|
id SERIAL PRIMARY KEY,
|
|
lamp_relay_box_id INT NOT NULL REFERENCES lamp_relay_boxes(id),
|
|
type SMALLINT NOT NULL,
|
|
description VARCHAR(500),
|
|
status SMALLINT NOT NULL DEFAULT 0, -- 0=active, 1=acknowledged, 2=resolved
|
|
acknowledged_by INTEGER REFERENCES users(user_id),
|
|
acknowledged_at TIMESTAMPTZ,
|
|
resolved_by INTEGER REFERENCES users(user_id),
|
|
resolved_at TIMESTAMPTZ,
|
|
comments TEXT,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
CREATE TABLE maintenance_logs (
|
|
id SERIAL PRIMARY KEY,
|
|
lamp_relay_box_id INTEGER REFERENCES lamp_relay_boxes(lamp_relay_box_id),
|
|
user_id INTEGER REFERENCES users(user_id),
|
|
action_type SMALLINT NOT NULL, -- 0=inspection, 1=repair, 2=replacement
|
|
description TEXT,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
-- Index for querying by lamp_relay_box in chronological order
|
|
CREATE INDEX idx_maintenance_logs_lamp_relay_box_id_created_at ON maintenance_logs(lamp_relay_box_id, created_at);
|
|
|
|
-- Index for querying by user in chronological order
|
|
CREATE INDEX idx_maintenance_logs_user_id_created_at ON maintenance_logs(user_id, created_at);
|
|
|
|
|