-- 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()
);

-- 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 ingestion_logs ENABLE ROW LEVEL SECURITY;

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'
    )
);

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 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;
