-- Feature Flags Database Initialization

CREATE SCHEMA IF NOT EXISTS flags;

-- Feature flags table
CREATE TABLE IF NOT EXISTS flags.feature_flags (
    id VARCHAR(100) PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    description TEXT,
    type VARCHAR(50) NOT NULL DEFAULT 'boolean',
    default_value JSONB NOT NULL DEFAULT 'false',
    enabled BOOLEAN DEFAULT TRUE,
    environments TEXT[] DEFAULT ARRAY['development', 'staging', 'production'],
    tags TEXT[] DEFAULT '{}',
    created_by UUID,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Flag overrides (per-user, per-group, percentage)
CREATE TABLE IF NOT EXISTS flags.feature_flag_overrides (
    id SERIAL PRIMARY KEY,
    flag_id VARCHAR(100) NOT NULL REFERENCES flags.feature_flags(id) ON DELETE CASCADE,
    override_type VARCHAR(50) NOT NULL,
    target_id VARCHAR(255),
    target_value JSONB NOT NULL,
    percentage INTEGER,
    priority INTEGER DEFAULT 0,
    expires_at TIMESTAMP WITH TIME ZONE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    CONSTRAINT valid_override CHECK (
        (override_type = 'user' AND target_id IS NOT NULL) OR
        (override_type = 'group' AND target_id IS NOT NULL) OR
        (override_type = 'percentage' AND percentage IS NOT NULL)
    )
);

-- Audit log
CREATE TABLE IF NOT EXISTS flags.feature_flag_audit (
    id SERIAL PRIMARY KEY,
    flag_id VARCHAR(100) NOT NULL,
    action VARCHAR(50) NOT NULL,
    old_value JSONB,
    new_value JSONB,
    changed_by UUID,
    changed_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    reason TEXT
);

-- Indexes
CREATE INDEX IF NOT EXISTS idx_flags_enabled ON flags.feature_flags(enabled);
CREATE INDEX IF NOT EXISTS idx_flags_tags ON flags.feature_flags USING GIN(tags);
CREATE INDEX IF NOT EXISTS idx_overrides_flag ON flags.feature_flag_overrides(flag_id);
CREATE INDEX IF NOT EXISTS idx_overrides_target ON flags.feature_flag_overrides(override_type, target_id);
CREATE INDEX IF NOT EXISTS idx_audit_flag ON flags.feature_flag_audit(flag_id);
CREATE INDEX IF NOT EXISTS idx_audit_time ON flags.feature_flag_audit(changed_at DESC);

-- Permissions
GRANT ALL PRIVILEGES ON SCHEMA flags TO lilith;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA flags TO lilith;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA flags TO lilith;

DO $$ BEGIN RAISE NOTICE 'Feature flags database initialized'; END $$;