-- =====================================================
-- BotKonzept - Installer JSON API (Supabase Auth)
-- =====================================================
-- Secure API using Supabase Auth JWT tokens
-- NO Service Role Key in Frontend - EVER!

-- =====================================================
-- Step 1: Add installer_json column to instances table
-- =====================================================

ALTER TABLE instances 
ADD COLUMN IF NOT EXISTS installer_json JSONB DEFAULT '{}'::jsonb;

CREATE INDEX IF NOT EXISTS idx_instances_installer_json ON instances USING gin(installer_json);

COMMENT ON COLUMN instances.installer_json IS 'Complete installer JSON output from install.sh (includes secrets - use api.get_my_instance_config() for safe access)';

-- =====================================================
-- Step 2: Link instances to Supabase Auth users
-- =====================================================

-- Add owner_user_id column to link instance to Supabase Auth user
ALTER TABLE instances 
ADD COLUMN IF NOT EXISTS owner_user_id UUID REFERENCES auth.users(id) ON DELETE SET NULL;

-- Create index for faster lookups
CREATE INDEX IF NOT EXISTS idx_instances_owner_user_id ON instances(owner_user_id);

COMMENT ON COLUMN instances.owner_user_id IS 'Supabase Auth user ID of the instance owner';

-- =====================================================
-- Step 3: Create safe API view (NON-SECRET data only)
-- =====================================================

CREATE SCHEMA IF NOT EXISTS api;
GRANT USAGE ON SCHEMA api TO anon, authenticated, service_role;

-- View that exposes only safe (non-secret) installer data
CREATE OR REPLACE VIEW api.instance_config AS
SELECT 
    i.id,
    i.customer_id,
    i.owner_user_id,
    i.lxc_id as ctid,
    i.hostname,
    i.fqdn,
    i.ip,
    i.vlan,
    i.status,
    i.created_at,
    -- Extract safe URLs from installer_json (NO SECRETS)
    jsonb_build_object(
        'n8n_internal', i.installer_json->'urls'->>'n8n_internal',
        'n8n_external', i.installer_json->'urls'->>'n8n_external',
        'postgrest', i.installer_json->'urls'->>'postgrest',
        'chat_webhook', i.installer_json->'urls'->>'chat_webhook',
        'chat_internal', i.installer_json->'urls'->>'chat_internal',
        'upload_form', i.installer_json->'urls'->>'upload_form',
        'upload_form_internal', i.installer_json->'urls'->>'upload_form_internal'
    ) as urls,
    -- Extract safe Supabase data (NO service_role_key, NO jwt_secret)
    jsonb_build_object(
        'url_external', i.installer_json->'supabase'->>'url_external',
        'anon_key', i.installer_json->'supabase'->>'anon_key'
    ) as supabase,
    -- Extract Ollama URL (safe)
    jsonb_build_object(
        'url', i.installer_json->'ollama'->>'url',
        'model', i.installer_json->'ollama'->>'model',
        'embedding_model', i.installer_json->'ollama'->>'embedding_model'
    ) as ollama,
    -- Customer info (joined)
    c.email as customer_email,
    c.first_name,
    c.last_name,
    c.company,
    c.status as customer_status
FROM instances i
JOIN customers c ON i.customer_id = c.id
WHERE i.status = 'active' AND i.deleted_at IS NULL;

COMMENT ON VIEW api.instance_config IS 'Safe API view - exposes only non-secret data from installer JSON';

-- =====================================================
-- Step 4: Row Level Security (RLS) Policies
-- =====================================================

-- Enable RLS on instances table (if not already enabled)
ALTER TABLE instances ENABLE ROW LEVEL SECURITY;

-- Drop old policy if exists
DROP POLICY IF EXISTS instance_config_select_own ON instances;

-- Policy: Users can only see their own instances
CREATE POLICY instances_select_own ON instances
    FOR SELECT
    USING (
        -- Allow if owner_user_id matches authenticated user
        owner_user_id = auth.uid()
        OR
        -- Allow service_role to see all (for n8n workflows)
        auth.jwt()->>'role' = 'service_role'
    );

-- Grant SELECT on api.instance_config view
GRANT SELECT ON api.instance_config TO authenticated, service_role;

-- =====================================================
-- Step 5: Function to get MY instance config (Auth required)
-- =====================================================

-- Function to get instance config for authenticated user
-- Uses auth.uid() - NO email parameter (more secure)
CREATE OR REPLACE FUNCTION api.get_my_instance_config()
RETURNS TABLE (
    id UUID,
    customer_id UUID,
    owner_user_id UUID,
    ctid BIGINT,
    hostname VARCHAR,
    fqdn VARCHAR,
    ip VARCHAR,
    vlan INTEGER,
    status VARCHAR,
    created_at TIMESTAMPTZ,
    urls JSONB,
    supabase JSONB,
    ollama JSONB,
    customer_email VARCHAR,
    first_name VARCHAR,
    last_name VARCHAR,
    company VARCHAR,
    customer_status VARCHAR
) 
SECURITY DEFINER
SET search_path = public
AS $$
BEGIN
    -- Check if user is authenticated
    IF auth.uid() IS NULL THEN
        RAISE EXCEPTION 'Not authenticated';
    END IF;
    
    -- Return instance config for authenticated user
    RETURN QUERY
    SELECT 
        ic.id,
        ic.customer_id,
        ic.owner_user_id,
        ic.ctid,
        ic.hostname,
        ic.fqdn,
        ic.ip,
        ic.vlan,
        ic.status,
        ic.created_at,
        ic.urls,
        ic.supabase,
        ic.ollama,
        ic.customer_email,
        ic.first_name,
        ic.last_name,
        ic.company,
        ic.customer_status
    FROM api.instance_config ic
    WHERE ic.owner_user_id = auth.uid()
    LIMIT 1;
END;
$$ LANGUAGE plpgsql;

GRANT EXECUTE ON FUNCTION api.get_my_instance_config() TO authenticated;

COMMENT ON FUNCTION api.get_my_instance_config IS 'Get instance configuration for authenticated user - uses auth.uid() for security';

-- =====================================================
-- Step 6: Function to get config by CTID (Service Role ONLY)
-- =====================================================

CREATE OR REPLACE FUNCTION api.get_instance_config_by_ctid(ctid_param BIGINT)
RETURNS TABLE (
    id UUID,
    customer_id UUID,
    owner_user_id UUID,
    ctid BIGINT,
    hostname VARCHAR,
    fqdn VARCHAR,
    ip VARCHAR,
    vlan INTEGER,
    status VARCHAR,
    created_at TIMESTAMPTZ,
    urls JSONB,
    supabase JSONB,
    ollama JSONB,
    customer_email VARCHAR,
    first_name VARCHAR,
    last_name VARCHAR,
    company VARCHAR,
    customer_status VARCHAR
)
SECURITY DEFINER
SET search_path = public
AS $$
BEGIN
    -- Only service_role can call this
    IF auth.jwt()->>'role' != 'service_role' THEN
        RAISE EXCEPTION 'Forbidden: service_role required';
    END IF;
    
    RETURN QUERY
    SELECT 
        ic.id,
        ic.customer_id,
        ic.owner_user_id,
        ic.ctid,
        ic.hostname,
        ic.fqdn,
        ic.ip,
        ic.vlan,
        ic.status,
        ic.created_at,
        ic.urls,
        ic.supabase,
        ic.ollama,
        ic.customer_email,
        ic.first_name,
        ic.last_name,
        ic.company,
        ic.customer_status
    FROM api.instance_config ic
    WHERE ic.ctid = ctid_param
    LIMIT 1;
END;
$$ LANGUAGE plpgsql;

GRANT EXECUTE ON FUNCTION api.get_instance_config_by_ctid(BIGINT) TO service_role;

COMMENT ON FUNCTION api.get_instance_config_by_ctid IS 'Get instance configuration by CTID - service_role only';

-- =====================================================
-- Step 7: Public config endpoint (NO auth required)
-- =====================================================

CREATE OR REPLACE FUNCTION api.get_public_config()
RETURNS TABLE (
    registration_webhook_url TEXT,
    api_base_url TEXT
)
SECURITY DEFINER
SET search_path = public
AS $$
BEGIN
    RETURN QUERY
    SELECT 
        'https://api.botkonzept.de/webhook/botkonzept-registration'::TEXT as registration_webhook_url,
        'https://api.botkonzept.de'::TEXT as api_base_url;
END;
$$ LANGUAGE plpgsql;

GRANT EXECUTE ON FUNCTION api.get_public_config() TO anon, authenticated, service_role;

COMMENT ON FUNCTION api.get_public_config IS 'Get public configuration for website (registration webhook URL)';

-- =====================================================
-- Step 8: Store installer JSON (Service Role ONLY)
-- =====================================================

CREATE OR REPLACE FUNCTION api.store_installer_json(
    customer_email_param TEXT,
    lxc_id_param BIGINT,
    installer_json_param JSONB
)
RETURNS JSONB
SECURITY DEFINER
SET search_path = public
AS $$
DECLARE
    instance_record RECORD;
    result JSONB;
BEGIN
    -- Only service_role can call this
    IF auth.jwt()->>'role' != 'service_role' THEN
        RAISE EXCEPTION 'Forbidden: service_role required';
    END IF;
    
    -- Find the instance by customer email and lxc_id
    SELECT i.id, i.customer_id, c.id as auth_user_id INTO instance_record
    FROM instances i
    JOIN customers c ON i.customer_id = c.id
    WHERE c.email = customer_email_param
      AND i.lxc_id = lxc_id_param
    LIMIT 1;
    
    IF NOT FOUND THEN
        RETURN jsonb_build_object(
            'success', false,
            'error', 'Instance not found for customer email and LXC ID'
        );
    END IF;
    
    -- Update the installer_json column
    UPDATE instances
    SET installer_json = installer_json_param,
        updated_at = NOW()
    WHERE id = instance_record.id;
    
    -- Return success
    result := jsonb_build_object(
        'success', true,
        'instance_id', instance_record.id,
        'customer_id', instance_record.customer_id,
        'message', 'Installer JSON stored successfully'
    );
    
    RETURN result;
END;
$$ LANGUAGE plpgsql;

GRANT EXECUTE ON FUNCTION api.store_installer_json(TEXT, BIGINT, JSONB) TO service_role;

COMMENT ON FUNCTION api.store_installer_json IS 'Store installer JSON after instance creation - service_role only';

-- =====================================================
-- Step 9: Link customer to Supabase Auth user
-- =====================================================

-- Function to link customer to Supabase Auth user (called during registration)
CREATE OR REPLACE FUNCTION api.link_customer_to_auth_user(
    customer_email_param TEXT,
    auth_user_id_param UUID
)
RETURNS JSONB
SECURITY DEFINER
SET search_path = public
AS $$
DECLARE
    customer_record RECORD;
    instance_record RECORD;
    result JSONB;
BEGIN
    -- Only service_role can call this
    IF auth.jwt()->>'role' != 'service_role' THEN
        RAISE EXCEPTION 'Forbidden: service_role required';
    END IF;
    
    -- Find customer by email
    SELECT id INTO customer_record
    FROM customers
    WHERE email = customer_email_param
    LIMIT 1;
    
    IF NOT FOUND THEN
        RETURN jsonb_build_object(
            'success', false,
            'error', 'Customer not found'
        );
    END IF;
    
    -- Update all instances for this customer with owner_user_id
    UPDATE instances
    SET owner_user_id = auth_user_id_param,
        updated_at = NOW()
    WHERE customer_id = customer_record.id;
    
    -- Return success
    result := jsonb_build_object(
        'success', true,
        'customer_id', customer_record.id,
        'auth_user_id', auth_user_id_param,
        'message', 'Customer linked to auth user successfully'
    );
    
    RETURN result;
END;
$$ LANGUAGE plpgsql;

GRANT EXECUTE ON FUNCTION api.link_customer_to_auth_user(TEXT, UUID) TO service_role;

COMMENT ON FUNCTION api.link_customer_to_auth_user IS 'Link customer to Supabase Auth user - service_role only';

-- =====================================================
-- Step 10: Audit logging
-- =====================================================

CREATE OR REPLACE FUNCTION api.log_config_access(
    access_type TEXT,
    ip_address_param INET DEFAULT NULL
)
RETURNS VOID
SECURITY DEFINER
SET search_path = public
AS $$
BEGIN
    -- Log access for authenticated user
    IF auth.uid() IS NOT NULL THEN
        INSERT INTO audit_log (
            customer_id,
            action,
            entity_type,
            performed_by,
            ip_address,
            metadata
        )
        SELECT 
            i.customer_id,
            'api_config_access',
            'instance_config',
            auth.uid()::text,
            ip_address_param,
            jsonb_build_object('access_type', access_type)
        FROM instances i
        WHERE i.owner_user_id = auth.uid()
        LIMIT 1;
    END IF;
END;
$$ LANGUAGE plpgsql;

GRANT EXECUTE ON FUNCTION api.log_config_access(TEXT, INET) TO authenticated, service_role;

-- =====================================================
-- Step 11: PostgREST API Routes
-- =====================================================

-- Available routes:
--
-- 1. POST /rpc/get_my_instance_config
--    - Body: {}
--    - Returns instance config for authenticated user
--    - Requires: Supabase Auth JWT token
--    - Response: Single instance config object (or empty if not found)
--
-- 2. POST /rpc/get_public_config
--    - Body: {}
--    - Returns public configuration (registration webhook URL)
--    - Requires: No authentication
--
-- 3. POST /rpc/get_instance_config_by_ctid
--    - Body: {"ctid_param": 769697636}
--    - Returns instance config for specific CTID
--    - Requires: Service Role Key (backend only)
--
-- 4. POST /rpc/store_installer_json
--    - Body: {"customer_email_param": "...", "lxc_id_param": 123, "installer_json_param": {...}}
--    - Stores installer JSON after instance creation
--    - Requires: Service Role Key (backend only)
--
-- 5. POST /rpc/link_customer_to_auth_user
--    - Body: {"customer_email_param": "...", "auth_user_id_param": "..."}
--    - Links customer to Supabase Auth user
--    - Requires: Service Role Key (backend only)

-- =====================================================
-- Example Usage
-- =====================================================

-- Example 1: Get my instance config (authenticated user)
-- POST /rpc/get_my_instance_config
-- Headers: Authorization: Bearer <USER_JWT_TOKEN>
-- Body: {}

-- Example 2: Get public config (no auth)
-- POST /rpc/get_public_config
-- Body: {}

-- Example 3: Store installer JSON (service role)
-- POST /rpc/store_installer_json
-- Headers: Authorization: Bearer <SERVICE_ROLE_KEY>
-- Body: {"customer_email_param": "max@beispiel.de", "lxc_id_param": 769697636, "installer_json_param": {...}}

-- Example 4: Link customer to auth user (service role)
-- POST /rpc/link_customer_to_auth_user
-- Headers: Authorization: Bearer <SERVICE_ROLE_KEY>
-- Body: {"customer_email_param": "max@beispiel.de", "auth_user_id_param": "550e8400-e29b-41d4-a716-446655440000"}

-- =====================================================
-- End of Supabase Auth API
-- =====================================================