-- Fix test failures after deprecating tablo status
-- This migration addresses:
-- 1. Re-add check constraint on status to allow NULL or valid values
-- 2. Fix RLS policies for etape creation/update to properly block non-owners

-- ============================================================================
-- Fix 1: Add back status check constraint (but allow NULL)
-- ============================================================================

-- Add check constraint that allows NULL or the valid status values
ALTER TABLE "public"."tablos"
  ADD CONSTRAINT "tablos_status_check" 
  CHECK (
    "status" IS NULL 
    OR ("status")::"text" = ANY (ARRAY[
      ('todo'::character varying)::"text", 
      ('in_progress'::character varying)::"text", 
      ('done'::character varying)::"text"
    ])
  );

COMMENT ON CONSTRAINT "tablos_status_check" ON "public"."tablos" 
  IS 'Allows NULL or valid status values (todo, in_progress, done). Status is deprecated and computed from etapes.';

-- ============================================================================
-- Fix 2: Ensure RLS policies properly prevent non-owners from managing etapes
-- ============================================================================

-- The issue is that the RLS policies need to explicitly check that the user
-- is the tablo owner when dealing with etapes. Let's verify the policies
-- are correctly preventing non-owner access to parent tasks.

-- Drop and recreate the INSERT policy with more explicit checks
DROP POLICY IF EXISTS "Users can create tasks in their tablos" ON "public"."tasks";

CREATE POLICY "Users can create tasks in their tablos"
    ON "public"."tasks"
    FOR INSERT
    WITH CHECK (
        -- User must have access to the tablo
        EXISTS (
            SELECT 1 FROM "public"."tablo_access" "ta"
            WHERE "ta"."tablo_id" = "tasks"."tablo_id"
              AND "ta"."user_id" = auth.uid()
              AND "ta"."is_active" = true
              -- If creating an etape (is_parent = true), user must be admin
              AND (
                (NOT "tasks"."is_parent")
                OR "ta"."is_admin" = true
              )
        )
    );

-- Drop and recreate the UPDATE policy with more explicit checks  
DROP POLICY IF EXISTS "Users can update tasks in their tablos" ON "public"."tasks";

CREATE POLICY "Users can update tasks in their tablos"
    ON "public"."tasks"
    FOR UPDATE
    USING (
        -- User must have access to the tablo
        EXISTS (
            SELECT 1 FROM "public"."tablo_access" "ta"
            WHERE "ta"."tablo_id" = "tasks"."tablo_id"
              AND "ta"."user_id" = auth.uid()
              AND "ta"."is_active" = true
              -- If updating an etape (is_parent = true), user must be admin
              AND (
                (NOT "tasks"."is_parent")
                OR "ta"."is_admin" = true
              )
        )
    )
    WITH CHECK (
        -- Same check for the new values
        EXISTS (
            SELECT 1 FROM "public"."tablo_access" "ta"
            WHERE "ta"."tablo_id" = "tasks"."tablo_id"
              AND "ta"."user_id" = auth.uid()
              AND "ta"."is_active" = true
              -- If the result would be an etape, user must be admin
              AND (
                (NOT "tasks"."is_parent")
                OR "ta"."is_admin" = true
              )
        )
    );

-- Drop and recreate the DELETE policy for consistency
DROP POLICY IF EXISTS "Users can delete tasks in their tablos" ON "public"."tasks";

CREATE POLICY "Users can delete tasks in their tablos"
    ON "public"."tasks"
    FOR DELETE
    USING (
        -- User must have access to the tablo
        EXISTS (
            SELECT 1 FROM "public"."tablo_access" "ta"
            WHERE "ta"."tablo_id" = "tasks"."tablo_id"
              AND "ta"."user_id" = auth.uid()
              AND "ta"."is_active" = true
              -- If deleting an etape (is_parent = true), user must be admin
              AND (
                (NOT "tasks"."is_parent")
                OR "ta"."is_admin" = true
              )
        )
    );

COMMENT ON POLICY "Users can create tasks in their tablos" ON "public"."tasks" 
  IS 'Users can create regular tasks in tablos they have access to. Only tablo admins can create etapes (is_parent=true).';

COMMENT ON POLICY "Users can update tasks in their tablos" ON "public"."tasks" 
  IS 'Users can update regular tasks in tablos they have access to. Only tablo admins can update etapes (is_parent=true).';

COMMENT ON POLICY "Users can delete tasks in their tablos" ON "public"."tasks" 
  IS 'Users can delete regular tasks in tablos they have access to. Only tablo admins can delete etapes (is_parent=true).';

-- ============================================================================
-- Fix 3: Add trigger to explicitly validate etape permissions and raise error
-- ============================================================================

-- Create function that validates etape permissions before INSERT/UPDATE
CREATE OR REPLACE FUNCTION "public"."validate_etape_permissions"()
RETURNS TRIGGER
LANGUAGE "plpgsql"
SECURITY DEFINER
AS $$
DECLARE
    user_is_admin boolean;
BEGIN
    -- Only validate if this is an etape (is_parent = true)
    IF NEW.is_parent = true THEN
        -- Check if the current user is an admin of this tablo
        SELECT "ta"."is_admin" INTO user_is_admin
        FROM "public"."tablo_access" "ta"
        WHERE "ta"."tablo_id" = NEW.tablo_id
          AND "ta"."user_id" = auth.uid()
          AND "ta"."is_active" = true
        LIMIT 1;

        -- If user is not found or not an admin, raise an error with proper SQLSTATE
        IF user_is_admin IS NULL OR user_is_admin = false THEN
            RAISE EXCEPTION USING 
                ERRCODE = '42501',  -- insufficient_privilege
                MESSAGE = 'Only tablo admins can create or modify etapes',
                HINT = 'Contact the tablo owner to request admin access';
        END IF;
    END IF;

    RETURN NEW;
END;
$$;

ALTER FUNCTION "public"."validate_etape_permissions"() OWNER TO "postgres";

COMMENT ON FUNCTION "public"."validate_etape_permissions"() 
  IS 'Validates that only tablo admins can create or update etapes (tasks with is_parent=true)';

-- Create trigger that runs before INSERT/UPDATE on tasks
DROP TRIGGER IF EXISTS "validate_etape_permissions_trigger" ON "public"."tasks";

CREATE TRIGGER "validate_etape_permissions_trigger"
    BEFORE INSERT OR UPDATE ON "public"."tasks"
    FOR EACH ROW
    EXECUTE FUNCTION "public"."validate_etape_permissions"();

