-- Admin Panel Support Migration
-- Adds system settings, audit log, and updates for admin functionality

-- System settings table for dynamic configuration
CREATE TABLE system_settings (
    key VARCHAR(100) PRIMARY KEY,
    value TEXT NOT NULL,
    value_type VARCHAR(20) NOT NULL,  -- STRING, BOOLEAN, INTEGER, ENCRYPTED
    category VARCHAR(50) NOT NULL,    -- auth, geocoding, gps, import, system
    description VARCHAR(500),
    updated_at TIMESTAMP WITHOUT TIME ZONE,
    updated_by UUID REFERENCES users(id)
);

CREATE INDEX idx_system_settings_category ON system_settings(category);

-- Audit log for tracking admin actions
CREATE TABLE audit_log (
    id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    timestamp TIMESTAMP WITHOUT TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
    admin_user_id UUID NOT NULL REFERENCES users(id),
    action_type VARCHAR(50) NOT NULL,
    target_type VARCHAR(50) NOT NULL,
    target_id VARCHAR(255),
    details JSONB,
    ip_address VARCHAR(45)
);

CREATE INDEX idx_audit_log_timestamp ON audit_log(timestamp DESC);
CREATE INDEX idx_audit_log_admin ON audit_log(admin_user_id);
CREATE INDEX idx_audit_log_target ON audit_log(target_type, target_id);
CREATE INDEX idx_audit_log_action ON audit_log(action_type);

-- User invitations for admin-generated registration links
CREATE TABLE user_invitations (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    token VARCHAR(64) NOT NULL UNIQUE,
    created_by UUID NOT NULL REFERENCES users(id),
    created_at TIMESTAMP WITHOUT TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    expires_at TIMESTAMP WITHOUT TIME ZONE NOT NULL,
    used BOOLEAN NOT NULL DEFAULT false,
    used_at TIMESTAMP WITHOUT TIME ZONE,
    used_by UUID REFERENCES users(id),
    revoked BOOLEAN NOT NULL DEFAULT false,
    revoked_at TIMESTAMP WITHOUT TIME ZONE
);

CREATE INDEX idx_user_invitations_token ON user_invitations(token);
CREATE INDEX idx_user_invitations_expires ON user_invitations(expires_at);
CREATE INDEX idx_user_invitations_created_by ON user_invitations(created_by);

-- Note: The 'role' column already exists in users table as VARCHAR
-- We'll use it with values 'USER' and 'ADMIN'