-- Native vSphere inventory + sessions (independent of Proxmox resources).

CREATE TABLE vsphere_sessions (
    id text PRIMARY KEY,
    username text NOT NULL,
    created_at timestamptz NOT NULL DEFAULT now(),
    expires_at timestamptz NOT NULL
);

CREATE INDEX vsphere_sessions_expires_idx ON vsphere_sessions (expires_at);

CREATE TABLE vsphere_objects (
    moid text PRIMARY KEY,
    type text NOT NULL,
    name text NOT NULL,
    parent_moid text REFERENCES vsphere_objects (moid) ON DELETE SET NULL,
    props jsonb NOT NULL DEFAULT '{}'::jsonb,
    created_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX vsphere_objects_type_idx ON vsphere_objects (type);
CREATE INDEX vsphere_objects_parent_idx ON vsphere_objects (parent_moid);
CREATE INDEX vsphere_objects_name_idx ON vsphere_objects (name);

CREATE TABLE vsphere_credentials (
    username text PRIMARY KEY,
    password_hash text NOT NULL,
    roles text[] NOT NULL DEFAULT '{Administrator}'
);