-- Migrations: 20240628_super_admin_system.sql
-- Add 'super_admin' to user_role enum
ALTER TYPE user_role ADD VALUE IF NOT EXISTS 'super_admin';

-- System Audit Logs Table
CREATE TABLE IF NOT EXISTS system_audit_logs (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    admin_id UUID REFERENCES auth.users(id) ON DELETE SET NULL,
    action TEXT NOT NULL,
    target_table TEXT,
    target_id TEXT,
    old_value JSONB,
    new_value JSONB,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Platform Configurations Table
CREATE TABLE IF NOT EXISTS platform_configurations (
    key TEXT PRIMARY KEY,
    value JSONB NOT NULL,
    description TEXT,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_by UUID REFERENCES auth.users(id) ON DELETE SET NULL
);

-- Active Ingest Status Table (For Scrapers, MOJ Ingestion monitoring)
CREATE TABLE IF NOT EXISTS ingestion_logs (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    source TEXT NOT NULL,
    status TEXT NOT NULL, -- 'success', 'failed', 'running'
    records_processed INTEGER DEFAULT 0,
    error_message TEXT,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Enable RLS
ALTER TABLE system_audit_logs ENABLE ROW LEVEL SECURITY;
ALTER TABLE platform_configurations ENABLE ROW LEVEL SECURITY;
ALTER TABLE ingestion_logs ENABLE ROW LEVEL SECURITY;

-- Dynamic Policies for system_audit_logs
CREATE POLICY "Super admins can select audit logs" ON system_audit_logs FOR SELECT
USING (
    EXISTS (
        SELECT 1 FROM public.profiles 
        WHERE profiles.id = auth.uid() AND profiles.role = 'super_admin'
    )
);

CREATE POLICY "Super admins can insert audit logs" ON system_audit_logs FOR INSERT
WITH CHECK (
    EXISTS (
        SELECT 1 FROM public.profiles 
        WHERE profiles.id = auth.uid() AND profiles.role = 'super_admin'
    )
);

-- Dynamic Policies for platform_configurations
CREATE POLICY "Platform configs are viewable by everyone" ON platform_configurations FOR SELECT
USING (true);

CREATE POLICY "Super admins can modify platform configs" ON platform_configurations FOR ALL
USING (
    EXISTS (
        SELECT 1 FROM public.profiles 
        WHERE profiles.id = auth.uid() AND profiles.role = 'super_admin'
    )
)
WITH CHECK (
    EXISTS (
        SELECT 1 FROM public.profiles 
        WHERE profiles.id = auth.uid() AND profiles.role = 'super_admin'
    )
);

-- Dynamic Policies for ingestion_logs
CREATE POLICY "Ingestion logs are readable by super admins" ON ingestion_logs FOR SELECT
USING (
    EXISTS (
        SELECT 1 FROM public.profiles 
        WHERE profiles.id = auth.uid() AND profiles.role = 'super_admin'
    )
);

CREATE POLICY "Write access to ingestion logs for service role or admin" ON ingestion_logs FOR ALL
USING (true); -- Usually inserted by background functions / edge functions

-- Additional policy on profiles to allow super admins to manage roles and status
CREATE POLICY "Super admins can update any user profile" ON public.profiles FOR UPDATE
USING (
    EXISTS (
        SELECT 1 FROM public.profiles 
        WHERE profiles.id = auth.uid() AND profiles.role = 'super_admin'
    )
)
WITH CHECK (
    EXISTS (
        SELECT 1 FROM public.profiles 
        WHERE profiles.id = auth.uid() AND profiles.role = 'super_admin'
    )
);

-- Seed Initial Configurations
INSERT INTO platform_configurations (key, value, description) VALUES
('base_size_elasticity_factor', '0.95', 'Base power size elasticity factor of a property area'),
('speculation_cleanse_threshold', '0.40', 'Price limit multiplier to filter family gift transactions'),
('street_exposure_multipliers', '{"North": 1.08, "East": 1.08, "South": 1.00, "West": 1.00}', 'Map of multipliers for street orientation exposures'),
('moj_ingest_frequency_minutes', '15', 'Cycle period for executing MOJ API query triggers')
ON CONFLICT (key) DO NOTHING;

-- Seed Sample Ingestion Logs for testing display
INSERT INTO ingestion_logs (source, status, records_processed) VALUES
('MOJ Webhook API', 'success', 241),
('Ejar National Integration', 'success', 1832),
('Al-Aqari Crawler', 'success', 89),
('Suhail Property Scraper', 'failed', 0)
ON CONFLICT DO NOTHING;