-- =====================================================================
-- NEXORA Smart Home IoT & Integrated Security
-- Skema PostgreSQL — Fase 1 (Fondasi)
-- Mengacu FSD v1.1: bagian 4 (RBAC), 7 (Denah), 8 (Perangkat),
-- 15.3 (Audit trail tamper-evident), 16.1 (Identitas & akses), 18 (Model data)
-- Butuh PostgreSQL 13+ (gen_random_uuid bawaan).
-- =====================================================================

SET client_min_messages = warning;

-- ---------------------------------------------------------------------
-- Migrasi
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS schema_migrations (
    version     TEXT PRIMARY KEY,
    applied_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- ---------------------------------------------------------------------
-- Pengguna & sesi (FSD 16.1)
-- ---------------------------------------------------------------------
CREATE TABLE users (
    id                   BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    email                TEXT NOT NULL,
    full_name            TEXT NOT NULL,
    phone                TEXT,
    password_hash        TEXT NOT NULL,
    must_change_password BOOLEAN NOT NULL DEFAULT TRUE,
    password_changed_at  TIMESTAMPTZ,
    is_platform_admin    BOOLEAN NOT NULL DEFAULT FALSE,
    is_active            BOOLEAN NOT NULL DEFAULT TRUE,
    totp_secret_enc      TEXT,                 -- terenkripsi (libsodium secretbox)
    totp_enabled         BOOLEAN NOT NULL DEFAULT FALSE,
    totp_last_step       BIGINT,               -- cegah replay kode TOTP yang sama
    failed_logins        INT NOT NULL DEFAULT 0,
    locked_until         TIMESTAMPTZ,
    account_expires_at   TIMESTAMPTZ,          -- akun installer sementara (FSD 16.1)
    last_login_at        TIMESTAMPTZ,
    created_by           BIGINT REFERENCES users(id),
    created_at           TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at           TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE UNIQUE INDEX users_email_uq ON users (lower(email));

CREATE TABLE user_sessions (
    id            TEXT PRIMARY KEY,            -- sha256 dari session id PHP
    user_id       BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    ip            INET,
    user_agent    TEXT,
    created_at    TIMESTAMPTZ NOT NULL DEFAULT now(),
    last_seen_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
    revoked_at    TIMESTAMPTZ
);
CREATE INDEX user_sessions_user_idx ON user_sessions (user_id) WHERE revoked_at IS NULL;

CREATE TABLE login_attempts (
    id          BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    email       TEXT,
    ip          INET,
    success     BOOLEAN NOT NULL,
    reason      TEXT,
    attempted_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX login_attempts_ip_idx ON login_attempts (ip, attempted_at DESC);
CREATE INDEX login_attempts_email_idx ON login_attempts (lower(email), attempted_at DESC);

-- ---------------------------------------------------------------------
-- RBAC (FSD 4, 4.1)
-- ---------------------------------------------------------------------
CREATE TABLE roles (
    id          SMALLINT PRIMARY KEY,
    code        TEXT NOT NULL UNIQUE,
    name        TEXT NOT NULL,
    description TEXT
);

CREATE TABLE permissions (
    code         TEXT PRIMARY KEY,
    name         TEXT NOT NULL,
    is_sensitive BOOLEAN NOT NULL DEFAULT FALSE   -- memerlukan re-authentication
);

CREATE TABLE role_permissions (
    role_id   SMALLINT NOT NULL REFERENCES roles(id) ON DELETE CASCADE,
    perm_code TEXT NOT NULL REFERENCES permissions(code) ON DELETE CASCADE,
    PRIMARY KEY (role_id, perm_code)
);

-- ---------------------------------------------------------------------
-- Properti & lokasi (FSD 7.1): Property → Building → Area/Floor → Room/Zone
-- ---------------------------------------------------------------------
CREATE TABLE properties (
    id          BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name        TEXT NOT NULL,
    timezone    TEXT NOT NULL DEFAULT 'Asia/Makassar',
    address     TEXT,
    created_by  BIGINT REFERENCES users(id),
    created_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
    archived_at TIMESTAMPTZ
);

CREATE TABLE property_members (
    property_id     BIGINT NOT NULL REFERENCES properties(id) ON DELETE CASCADE,
    user_id         BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    role_id         SMALLINT NOT NULL REFERENCES roles(id),
    location_scope  BIGINT[],                 -- NULL = semua lokasi; selain itu daftar location_id
    valid_from      TIMESTAMPTZ NOT NULL DEFAULT now(),
    valid_until     TIMESTAMPTZ,              -- akses berbatas waktu (installer, garda JIT)
    granted_by      BIGINT REFERENCES users(id),
    created_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
    PRIMARY KEY (property_id, user_id)
);

CREATE TABLE buildings (
    id          BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    property_id BIGINT NOT NULL REFERENCES properties(id) ON DELETE CASCADE,
    name        TEXT NOT NULL,
    sort_order  INT NOT NULL DEFAULT 0,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
    archived_at TIMESTAMPTZ
);

CREATE TABLE locations (
    id              BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    property_id     BIGINT NOT NULL REFERENCES properties(id) ON DELETE CASCADE,
    building_id     BIGINT REFERENCES buildings(id),
    name            TEXT NOT NULL,
    kind            TEXT NOT NULL CHECK (kind IN ('floor','outdoor','area')),
    level_no        INT,
    sort_order      INT NOT NULL DEFAULT 0,
    current_plan_id BIGINT,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
    archived_at     TIMESTAMPTZ
);
CREATE INDEX locations_property_idx ON locations (property_id, sort_order);

CREATE TABLE rooms (
    id          BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    location_id BIGINT NOT NULL REFERENCES locations(id) ON DELETE CASCADE,
    name        TEXT NOT NULL,
    zone_type   TEXT NOT NULL DEFAULT 'room'
                CHECK (zone_type IN ('room','corridor','entry','service','private','technical','outdoor_zone')),
    sort_order  INT NOT NULL DEFAULT 0,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
    archived_at TIMESTAMPTZ
);

-- Gambar denah berversi (FSD 7.2: simpan versi konfigurasi denah)
CREATE TABLE plan_assets (
    id             BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    location_id    BIGINT NOT NULL REFERENCES locations(id) ON DELETE CASCADE,
    version        INT NOT NULL,
    stored_name    TEXT NOT NULL,
    original_name  TEXT NOT NULL,
    mime           TEXT NOT NULL,
    width_px       INT NOT NULL,
    height_px      INT NOT NULL,
    rotation       SMALLINT NOT NULL DEFAULT 0 CHECK (rotation IN (0,90,180,270)),
    sha256         TEXT NOT NULL,
    uploaded_by    BIGINT REFERENCES users(id),
    created_at     TIMESTAMPTZ NOT NULL DEFAULT now(),
    UNIQUE (location_id, version)
);
ALTER TABLE locations
    ADD CONSTRAINT locations_current_plan_fk FOREIGN KEY (current_plan_id) REFERENCES plan_assets(id) ON DELETE SET NULL;

-- ---------------------------------------------------------------------
-- Controller & perangkat (FSD 8)
-- ---------------------------------------------------------------------
CREATE TABLE controllers (
    id               BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    property_id      BIGINT NOT NULL REFERENCES properties(id) ON DELETE CASCADE,
    code             TEXT NOT NULL,
    name             TEXT NOT NULL,
    protocol         TEXT NOT NULL,
    endpoint_ref     TEXT,                    -- referensi (host/topic), BUKAN kredensial
    channel_count    INT NOT NULL DEFAULT 0 CHECK (channel_count BETWEEN 0 AND 512),
    location_id      BIGINT REFERENCES locations(id) ON DELETE SET NULL,
    manufacturer     TEXT,
    model            TEXT,
    firmware_version TEXT,
    health           TEXT NOT NULL DEFAULT 'unknown'
                     CHECK (health IN ('unknown','online','offline','degraded','maintenance')),
    last_seen_at     TIMESTAMPTZ,
    notes            TEXT,
    created_at       TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at       TIMESTAMPTZ NOT NULL DEFAULT now(),
    archived_at      TIMESTAMPTZ,
    UNIQUE (property_id, code)
);

CREATE TABLE devices (
    id               BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    property_id      BIGINT NOT NULL REFERENCES properties(id) ON DELETE CASCADE,
    code             TEXT NOT NULL,           -- ID stabil yang mudah dibaca, mis. DEV-L1-LGT-001
    name             TEXT NOT NULL,
    category         TEXT NOT NULL,
    manufacturer     TEXT,
    model            TEXT,
    protocol         TEXT NOT NULL,
    controller_id    BIGINT REFERENCES controllers(id) ON DELETE SET NULL,
    channel_no       INT CHECK (channel_no IS NULL OR channel_no >= 1),
    location_id      BIGINT REFERENCES locations(id) ON DELETE SET NULL,
    room_id          BIGINT REFERENCES rooms(id) ON DELETE SET NULL,
    capabilities     TEXT[] NOT NULL DEFAULT '{}',
    lifecycle        TEXT NOT NULL DEFAULT 'registered'
                     CHECK (lifecycle IN ('discovered','registered','configured','commissioned','active',
                                          'degraded','offline','maintenance','disabled','retired')),
    connectivity     TEXT NOT NULL DEFAULT 'unknown'
                     CHECK (connectivity IN ('unknown','online','offline','degraded')),
    reported_state   JSONB,                   -- status aktual dari perangkat
    desired_state    JSONB,                   -- target; dipisahkan dari actual (FSD 18.1)
    state_updated_at TIMESTAMPTZ,
    last_seen_at     TIMESTAMPTZ,
    health           JSONB,                   -- baterai, sinyal, dll.
    firmware_version TEXT,
    is_critical      BOOLEAN NOT NULL DEFAULT FALSE,  -- pembatasan kontrol grup (FSD 9.1)
    enabled          BOOLEAN NOT NULL DEFAULT TRUE,
    commissioned_at  TIMESTAMPTZ,
    notes            TEXT,
    version          INT NOT NULL DEFAULT 1,  -- optimistic concurrency (ETag)
    created_by       BIGINT REFERENCES users(id),
    created_at       TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at       TIMESTAMPTZ NOT NULL DEFAULT now(),
    UNIQUE (property_id, code)
);
CREATE INDEX devices_property_idx ON devices (property_id, category);
-- Satu channel fisik hanya boleh diklaim satu perangkat aktif (FSD 8.4)
CREATE UNIQUE INDEX devices_channel_uq ON devices (controller_id, channel_no)
    WHERE controller_id IS NOT NULL AND channel_no IS NOT NULL AND lifecycle <> 'retired';

-- Placement terpisah dari perangkat (FSD 7.2, 18.1)
CREATE TABLE placements (
    id          BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    device_id   BIGINT NOT NULL REFERENCES devices(id) ON DELETE CASCADE,
    location_id BIGINT NOT NULL REFERENCES locations(id) ON DELETE CASCADE,
    x           NUMERIC(7,6) NOT NULL CHECK (x >= 0 AND x <= 1),
    y           NUMERIC(7,6) NOT NULL CHECK (y >= 0 AND y <= 1),
    rotation    SMALLINT NOT NULL DEFAULT 0 CHECK (rotation BETWEEN 0 AND 359),
    is_primary  BOOLEAN NOT NULL DEFAULT TRUE,
    version     INT NOT NULL DEFAULT 1,
    updated_by  BIGINT REFERENCES users(id),
    created_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE UNIQUE INDEX placements_primary_uq ON placements (device_id) WHERE is_primary;
CREATE INDEX placements_location_idx ON placements (location_id);

-- ---------------------------------------------------------------------
-- Audit log tamper-evident dengan hash berantai (FSD 15.3)
-- ---------------------------------------------------------------------
CREATE TABLE audit_log (
    id           BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    occurred_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
    actor_id     BIGINT,
    actor_label  TEXT,
    property_id  BIGINT,
    category     TEXT NOT NULL,   -- authentication, configuration, access, command, ...
    action       TEXT NOT NULL,
    target_type  TEXT,
    target_id    TEXT,
    result       TEXT NOT NULL CHECK (result IN ('success','denied','failed')),
    ip           INET,
    details      JSONB,
    prev_hash    TEXT NOT NULL,
    hash         TEXT NOT NULL
);
CREATE INDEX audit_property_idx ON audit_log (property_id, occurred_at DESC);
CREATE INDEX audit_actor_idx ON audit_log (actor_id, occurred_at DESC);

-- Audit log append-only: tolak UPDATE/DELETE di level database
CREATE OR REPLACE FUNCTION audit_log_immutable() RETURNS trigger AS $$
BEGIN
    RAISE EXCEPTION 'audit_log bersifat append-only';
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER audit_log_no_update BEFORE UPDATE OR DELETE ON audit_log
    FOR EACH ROW EXECUTE FUNCTION audit_log_immutable();

INSERT INTO schema_migrations (version) VALUES ('001_phase1_foundation');