CREATE TABLE IF NOT EXISTS kanadi_bans (
	id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
	entity_type VARCHAR(50) NOT NULL,
	entity_id VARCHAR(255) NOT NULL,
	rule_id VARCHAR(100) NOT NULL,
	decision VARCHAR(20) NOT NULL,
	reason TEXT,
	confidence DECIMAL(3, 2),
	evidence JSONB,
	expires_at TIMESTAMP,
	created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
	updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX IF NOT EXISTS idx_bans_entity ON kanadi_bans(entity_type, entity_id);
CREATE INDEX IF NOT EXISTS idx_bans_expires ON kanadi_bans(expires_at) WHERE expires_at IS NOT NULL;
CREATE INDEX IF NOT EXISTS idx_bans_rule ON kanadi_bans(rule_id);
CREATE INDEX IF NOT EXISTS idx_bans_created ON kanadi_bans(created_at DESC);
CREATE INDEX IF NOT EXISTS idx_bans_active ON kanadi_bans(entity_type, entity_id, expires_at);

CREATE OR REPLACE FUNCTION update_kanadi_bans_updated_at()
RETURNS TRIGGER AS $$
BEGIN
	NEW.updated_at = CURRENT_TIMESTAMP;
	RETURN NEW;
END;
$$ language 'plpgsql';

CREATE TRIGGER trigger_update_kanadi_bans_updated_at
	BEFORE UPDATE ON kanadi_bans
	FOR EACH ROW
	EXECUTE FUNCTION update_kanadi_bans_updated_at();
