CREATE EXTENSION IF NOT EXISTS pgcrypto;

CREATE TABLE bell_roles (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    code text NOT NULL UNIQUE CHECK (code IN ('administrator','operator','viewer')),
    name text NOT NULL
);
CREATE TABLE bell_permissions (code text PRIMARY KEY, description text NOT NULL);
CREATE TABLE bell_role_permissions (
    role_id uuid NOT NULL REFERENCES bell_roles(id),
    permission_code text NOT NULL REFERENCES bell_permissions(code),
    PRIMARY KEY (role_id, permission_code)
);
CREATE TABLE bell_users (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    username text NOT NULL UNIQUE CHECK (username = lower(username)),
    display_name text NOT NULL,
    password_hash text NOT NULL,
    enabled boolean NOT NULL DEFAULT true,
    created_at timestamptz NOT NULL DEFAULT now()
);
CREATE TABLE bell_user_roles (
    user_id uuid NOT NULL REFERENCES bell_users(id),
    role_id uuid NOT NULL REFERENCES bell_roles(id),
    PRIMARY KEY (user_id, role_id)
);
CREATE TABLE bell_sessions (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id uuid NOT NULL REFERENCES bell_users(id),
    token_digest bytea NOT NULL UNIQUE,
    created_at timestamptz NOT NULL DEFAULT now(),
    expires_at timestamptz NOT NULL,
    revoked_at timestamptz,
    CHECK (expires_at > created_at)
);
CREATE INDEX bell_sessions_user_active_idx ON bell_sessions(user_id, expires_at) WHERE revoked_at IS NULL;
CREATE TABLE bell_audit_log (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    occurred_at timestamptz NOT NULL DEFAULT now(),
    actor_user_id uuid REFERENCES bell_users(id),
    action text NOT NULL,
    target_type text NOT NULL,
    target_id text,
    outcome text NOT NULL CHECK (outcome IN ('success','failure','denied')),
    details jsonb NOT NULL DEFAULT '{}'::jsonb
);

CREATE OR REPLACE FUNCTION bell_reject_audit_mutation() RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN RAISE EXCEPTION 'bell audit facts are append-only'; END $$;
CREATE TRIGGER bell_audit_no_update BEFORE UPDATE OR DELETE ON bell_audit_log FOR EACH ROW EXECUTE FUNCTION bell_reject_audit_mutation();

INSERT INTO bell_roles(code,name) VALUES ('administrator','管理员'),('operator','处置员'),('viewer','只读用户');
INSERT INTO bell_permissions(code,description) VALUES
 ('dashboard:read','查看工作台'),('alerts:read','查看预警'),('alerts:handle','处置预警'),
 ('events:read','查看事件'),('rules:read','查看规则'),('rules:write','管理规则'),
 ('users:read','查看用户'),('users:write','管理用户与角色'),('audit:read','查看审计');
INSERT INTO bell_role_permissions(role_id,permission_code)
SELECT r.id,p.code FROM bell_roles r CROSS JOIN bell_permissions p WHERE r.code='administrator';
INSERT INTO bell_role_permissions(role_id,permission_code)
SELECT r.id,p.code FROM bell_roles r JOIN bell_permissions p ON p.code IN ('dashboard:read','alerts:read','alerts:handle','events:read','rules:read') WHERE r.code='operator';
INSERT INTO bell_role_permissions(role_id,permission_code)
SELECT r.id,p.code FROM bell_roles r JOIN bell_permissions p ON p.code IN ('dashboard:read','alerts:read','events:read','rules:read') WHERE r.code='viewer';