-- Migration: Remove unused pos_modules and licenses tables and reference functions
-- 1. Drop the tables (cascade to clean up RLS policies automatically)
DROP TABLE IF EXISTS public.licenses CASCADE;
DROP TABLE IF EXISTS public.pos_modules CASCADE;

-- 2. Update database integrity function to remove pos_modules and licenses checks
CREATE OR REPLACE FUNCTION public.check_database_integrity()
RETURNS jsonb
SECURITY DEFINER
AS $$
DECLARE
  tables_to_check text[] := ARRAY[
    'users',
    'settings',
    'companies',
    'categories',
    'products',
    'product_modules',
    'orders',
    'profiles',
    'end_customers'
  ];
  t text;
  t_exists boolean;
  t_count bigint;
  tables_result jsonb := jsonb_build_object();
  result jsonb;
BEGIN
  FOREACH t IN ARRAY tables_to_check LOOP
    SELECT EXISTS (
      SELECT 1 FROM pg_tables WHERE schemaname = 'public' AND tablename = t
    ) INTO t_exists;
    
    t_count := 0;
    IF t_exists THEN
      EXECUTE format('SELECT count(*) FROM public.%I', t) INTO t_count;
    END IF;
    
    tables_result := tables_result || jsonb_build_object(t, jsonb_build_object('exists', t_exists, 'count', t_count));
  END LOOP;

  result := jsonb_build_object(
    'tables', tables_result,
    'errors', jsonb_build_object(
      'foreign_keys', 0
    )
  );

  RETURN result;
END;
$$ LANGUAGE plpgsql;

-- 3. Update database repair schema function to remove creation of pos_modules and licenses
CREATE OR REPLACE FUNCTION public.repair_database_schema()
RETURNS jsonb
SECURITY DEFINER
AS $$
DECLARE
  repaired_tables text[] := '{}';
  result jsonb;
BEGIN
  -- 1. profiles table
  IF NOT EXISTS (SELECT 1 FROM pg_tables WHERE schemaname = 'public' AND tablename = 'profiles') THEN
    CREATE TABLE public.profiles (
      id UUID PRIMARY KEY REFERENCES auth.users(id) ON DELETE CASCADE,
      first_name TEXT,
      last_name TEXT,
      company_name TEXT,
      vat_id TEXT,
      address TEXT,
      city TEXT,
      zip_code TEXT,
      updated_at TIMESTAMP WITH TIME ZONE DEFAULT timezone('utc'::text, now()) NOT NULL
    );
    repaired_tables := array_append(repaired_tables, 'profiles');
  END IF;

  -- 2. end_customers table
  IF NOT EXISTS (SELECT 1 FROM pg_tables WHERE schemaname = 'public' AND tablename = 'end_customers') THEN
    CREATE TABLE public.end_customers (
      id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
      partner_id UUID NOT NULL REFERENCES public.companies(id) ON DELETE CASCADE,
      company_name TEXT NOT NULL,
      vat_id TEXT,
      first_name TEXT,
      last_name TEXT,
      street TEXT,
      zip TEXT,
      city TEXT,
      is_anonymized BOOLEAN NOT NULL DEFAULT false,
      created_at TIMESTAMPTZ NOT NULL DEFAULT now()
    );
    repaired_tables := array_append(repaired_tables, 'end_customers');
  END IF;

  -- Enable RLS
  ALTER TABLE public.profiles ENABLE ROW LEVEL SECURITY;
  ALTER TABLE public.end_customers ENABLE ROW LEVEL SECURITY;

  -- Recreate RLS Policies
  DROP POLICY IF EXISTS "Users can view their own profile" ON public.profiles;
  CREATE POLICY "Users can view their own profile" ON public.profiles FOR SELECT USING (auth.uid() = id);
  DROP POLICY IF EXISTS "Users can update their own profile" ON public.profiles;
  CREATE POLICY "Users can update their own profile" ON public.profiles FOR UPDATE USING (auth.uid() = id);
  DROP POLICY IF EXISTS "Users can insert their own profile" ON public.profiles;
  CREATE POLICY "Users can insert their own profile" ON public.profiles FOR INSERT WITH CHECK (auth.uid() = id);

  result := jsonb_build_object(
    'repaired_tables', repaired_tables
  );

  RETURN result;
END;
$$ LANGUAGE plpgsql;

-- Reload Schema Cache
NOTIFY pgrst, 'reload schema';
