-- ------------------------
-- Metadata (singleton row; enforced at application level)
-- ------------------------
DROP TABLE IF EXISTS metadata;

CREATE TABLE metadata(
    name VARCHAR(50),
    comment VARCHAR(200),
    private_key VARCHAR(500),
    public_key VARCHAR(500),
    defense_p BOOLEAN NOT NULL DEFAULT false
);

-- ------------------------
-- Entity
-- ------------------------
DROP TABLE IF EXISTS entity CASCADE;

CREATE TABLE entity(
    id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    creation_ts TIMESTAMPTZ NOT NULL DEFAULT now(),
    creator INT REFERENCES entity(id),
    name VARCHAR(100) NOT NULL,
    type VARCHAR(20) NOT NULL,           -- person, group, device
    symmetrical_key VARCHAR(100),
    public_key VARCHAR(300) NOT NULL,
    ca_reference VARCHAR(100),
    status VARCHAR(20) NOT NULL DEFAULT 'active',
    expiration DATE,
    CONSTRAINT entity_ca_reference_check CHECK (
        (type = 'group' AND ca_reference IS NOT NULL)
        OR
        (type <> 'group' AND ca_reference IS NULL)
    )
);

CREATE INDEX idx_entity_name ON entity(name);

-- ------------------------
-- Group Member
-- ------------------------
DROP TABLE IF EXISTS group_member;

CREATE TABLE group_member(
    group_id INT NOT NULL REFERENCES entity(id) ON DELETE CASCADE,
    member_id INT NOT NULL REFERENCES entity(id) ON DELETE CASCADE,
    role VARCHAR(10),
    PRIMARY KEY (group_id, member_id)
);

CREATE INDEX idx_group_member ON group_member(member_id, group_id);

-- ------------------------
-- Property
-- ------------------------
DROP TABLE IF EXISTS property;

CREATE TABLE property(
    id INT NOT NULL REFERENCES entity(id) ON DELETE CASCADE,
    property_name VARCHAR(100) NOT NULL,
    validation_policy CHAR(19) NOT NULL DEFAULT 'default',
    source VARCHAR(150),
    PRIMARY KEY (id, property_name)
);

-- ------------------------
-- Log Table
-- ------------------------
DROP TABLE IF EXISTS log;

CREATE TABLE log(
    id SERIAL PRIMARY KEY,
    ts TIMESTAMPTZ NOT NULL DEFAULT now(),
    entry TEXT NOT NULL
);

CREATE INDEX idx_log_ts ON log(ts);