-- Core platform schema: users, zones, device types, devices, automation rules.
-- Deliberately generic — no growbox-specific tables or columns. A "growbox"
-- is just a row in zones with device_types like light/pump/fan attached to it.

CREATE TABLE users (
    id            BIGSERIAL PRIMARY KEY,
    email         TEXT NOT NULL UNIQUE,
    password_hash TEXT NOT NULL,
    role          TEXT NOT NULL DEFAULT 'owner' CHECK (role IN ('owner', 'viewer')),
    created_at    TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE zones (
    id          BIGSERIAL PRIMARY KEY,
    user_id     BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    name        TEXT NOT NULL,
    description TEXT,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE device_types (
    id           BIGSERIAL PRIMARY KEY,
    code         TEXT NOT NULL UNIQUE, -- light, pump, fan, sensor_temp_humidity, vacuum, ...
    category     TEXT NOT NULL CHECK (category IN ('actuator', 'sensor')),
    capabilities JSONB NOT NULL DEFAULT '[]' -- e.g. ["turn_on","turn_off","set_level"]
);

CREATE TABLE devices (
    id             BIGSERIAL PRIMARY KEY,
    user_id        BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    zone_id        BIGINT NOT NULL REFERENCES zones(id) ON DELETE CASCADE,
    device_type_id BIGINT NOT NULL REFERENCES device_types(id),
    name           TEXT NOT NULL,
    external_id    TEXT NOT NULL UNIQUE, -- physical device id (ESP32 chip id, etc.)
    protocol       TEXT NOT NULL DEFAULT 'mqtt',
    status         TEXT NOT NULL DEFAULT 'offline' CHECK (status IN ('online', 'offline')),
    created_at     TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX devices_zone_id_idx ON devices(zone_id);
CREATE INDEX devices_external_id_idx ON devices(external_id);

CREATE TABLE automation_rules (
    id                          BIGSERIAL PRIMARY KEY,
    user_id                     BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    zone_id                     BIGINT NOT NULL REFERENCES zones(id) ON DELETE CASCADE,
    target_device_id            BIGINT NOT NULL REFERENCES devices(id) ON DELETE CASCADE,
    condition_source_device_id  BIGINT NOT NULL REFERENCES devices(id) ON DELETE CASCADE,
    condition_sensor_type       TEXT NOT NULL,
    condition_operator          TEXT NOT NULL CHECK (condition_operator IN ('>', '<', '>=', '<=', '=', '!=')),
    condition_value             DOUBLE PRECISION NOT NULL,
    action_type                 TEXT NOT NULL, -- e.g. turn_on, turn_off, set_level
    action_params               JSONB NOT NULL DEFAULT '{}',
    is_active                   BOOLEAN NOT NULL DEFAULT true,
    created_at                  TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX automation_rules_zone_id_idx ON automation_rules(zone_id);
CREATE INDEX automation_rules_active_idx ON automation_rules(is_active) WHERE is_active;