Setup Supabase pentru Albums Admin

Rulează o singură dată dacă salvarea setărilor sau a rangurilor nu merge.

ALTER TABLE site_config ADD COLUMN IF NOT EXISTS albums_data jsonb;

ALTER TABLE profiles ADD COLUMN IF NOT EXISTS full_name text;
ALTER TABLE profiles ADD COLUMN IF NOT EXISTS phone text;
ALTER TABLE profiles ADD COLUMN IF NOT EXISTS gdpr_accepted_at timestamptz;
ALTER TABLE profiles ADD COLUMN IF NOT EXISTS status text DEFAULT 'free';
ALTER TABLE profiles ADD COLUMN IF NOT EXISTS role text DEFAULT 'user';
ALTER TABLE profiles ADD COLUMN IF NOT EXISTS plan text DEFAULT 'free';
ALTER TABLE profiles ADD COLUMN IF NOT EXISTS last_login_at timestamptz;
ALTER TABLE profiles ADD COLUMN IF NOT EXISTS tester_free_until date;
ALTER TABLE profiles ADD COLUMN IF NOT EXISTS lifetime_discount_percent int DEFAULT 0;
ALTER TABLE profiles ADD COLUMN IF NOT EXISTS access_expires_at date;
ALTER TABLE profiles ADD COLUMN IF NOT EXISTS commercial_note text DEFAULT '';
ALTER TABLE profiles ENABLE ROW LEVEL SECURITY;

CREATE OR REPLACE FUNCTION is_album_admin()
RETURNS boolean
LANGUAGE sql
SECURITY DEFINER
SET search_path = public
AS $$
  SELECT EXISTS (
    SELECT 1 FROM profiles p
    WHERE p.id = auth.uid()
    AND p.role IN ('admin','creator')
  );
$$;

GRANT EXECUTE ON FUNCTION is_album_admin() TO authenticated;

DROP POLICY IF EXISTS "Albums users read own profile" ON profiles;
DROP POLICY IF EXISTS "Albums users create own free profile" ON profiles;
DROP POLICY IF EXISTS "Albums users update own free profile" ON profiles;
DROP POLICY IF EXISTS "Albums admins manage profiles" ON profiles;

CREATE POLICY "Albums users read own profile"
ON profiles
FOR SELECT
TO authenticated
USING (
  id = auth.uid()
  OR is_album_admin()
);

CREATE POLICY "Albums users create own free profile"
ON profiles
FOR INSERT
TO authenticated
WITH CHECK (
  id = auth.uid()
  AND COALESCE(role, 'user') = 'user'
  AND COALESCE(status, 'free') = 'free'
);

CREATE POLICY "Albums users update own free profile"
ON profiles
FOR UPDATE
TO authenticated
USING (id = auth.uid())
WITH CHECK (
  id = auth.uid()
  AND COALESCE(role, 'user') = 'user'
  AND COALESCE(status, 'free') = 'free'
);

CREATE POLICY "Albums admins manage profiles"
ON profiles
FOR ALL
TO authenticated
USING (is_album_admin())
WITH CHECK (is_album_admin());

CREATE TABLE IF NOT EXISTS activity_log (
  id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  user_id uuid,
  user_email text,
  type text,
  message text,
  details jsonb DEFAULT '{}'::jsonb,
  created_at timestamptz DEFAULT now()
);

ALTER TABLE activity_log ADD COLUMN IF NOT EXISTS user_id uuid;
ALTER TABLE activity_log ADD COLUMN IF NOT EXISTS user_email text;
ALTER TABLE activity_log ADD COLUMN IF NOT EXISTS type text;
ALTER TABLE activity_log ADD COLUMN IF NOT EXISTS message text;
ALTER TABLE activity_log ADD COLUMN IF NOT EXISTS details jsonb DEFAULT '{}'::jsonb;
ALTER TABLE activity_log ADD COLUMN IF NOT EXISTS created_at timestamptz DEFAULT now();
ALTER TABLE activity_log ENABLE ROW LEVEL SECURITY;

DROP POLICY IF EXISTS "Albums users insert own activity" ON activity_log;
DROP POLICY IF EXISTS "Albums admins read activity" ON activity_log;

CREATE POLICY "Albums users insert own activity"
ON activity_log
FOR INSERT
TO authenticated
WITH CHECK (user_id = auth.uid());

CREATE POLICY "Albums admins read activity"
ON activity_log
FOR SELECT
TO authenticated
USING (
  user_id = auth.uid()
  OR is_album_admin()
);

CREATE OR REPLACE FUNCTION ensure_album_profile(
  profile_email text DEFAULT '',
  profile_full_name text DEFAULT '',
  profile_phone text DEFAULT '',
  profile_gdpr_accepted_at timestamptz DEFAULT NULL
)
RETURNS profiles
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = public
AS $$
DECLARE saved_profile profiles;
BEGIN
  INSERT INTO profiles (
    id,
    email,
    full_name,
    phone,
    gdpr_accepted_at,
    status,
    role,
    plan,
    last_login_at
  )
  VALUES (
    auth.uid(),
    NULLIF(profile_email, ''),
    COALESCE(NULLIF(profile_full_name, ''), ''),
    COALESCE(NULLIF(profile_phone, ''), ''),
    profile_gdpr_accepted_at,
    'free',
    'user',
    'free',
    now()
  )
  ON CONFLICT (id) DO UPDATE
  SET
    email = COALESCE(NULLIF(EXCLUDED.email, ''), profiles.email),
    full_name = COALESCE(NULLIF(EXCLUDED.full_name, ''), profiles.full_name),
    phone = COALESCE(NULLIF(EXCLUDED.phone, ''), profiles.phone),
    gdpr_accepted_at = COALESCE(profiles.gdpr_accepted_at, EXCLUDED.gdpr_accepted_at),
    last_login_at = now()
  RETURNING * INTO saved_profile;

  RETURN saved_profile;
END;
$$;

GRANT EXECUTE ON FUNCTION ensure_album_profile(text, text, text, timestamptz) TO authenticated;

CREATE OR REPLACE FUNCTION log_album_activity(
  activity_type text,
  activity_message text,
  activity_details jsonb DEFAULT '{}'::jsonb
)
RETURNS void
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = public
AS $$
BEGIN
  INSERT INTO activity_log (
    user_id,
    user_email,
    type,
    message,
    details,
    created_at
  )
  VALUES (
    auth.uid(),
    (SELECT email FROM profiles WHERE id = auth.uid()),
    activity_type,
    activity_message,
    COALESCE(activity_details, '{}'::jsonb),
    now()
  );
END;
$$;

GRANT EXECUTE ON FUNCTION log_album_activity(text, text, jsonb) TO authenticated;

CREATE TABLE IF NOT EXISTS album_user_meta (
  user_id uuid PRIMARY KEY REFERENCES profiles(id) ON DELETE CASCADE,
  stars int DEFAULT 0,
  rank text DEFAULT 'client',
  notes text DEFAULT '',
  flags text[] DEFAULT '{}',
  credits int DEFAULT 0,
  credits_used int DEFAULT 0,
  exports_count int DEFAULT 0,
  last_export_at timestamptz,
  updated_at timestamptz DEFAULT now()
);

ALTER TABLE album_user_meta ADD COLUMN IF NOT EXISTS credits int DEFAULT 0;
ALTER TABLE album_user_meta ADD COLUMN IF NOT EXISTS credits_used int DEFAULT 0;
ALTER TABLE album_user_meta ADD COLUMN IF NOT EXISTS exports_count int DEFAULT 0;
ALTER TABLE album_user_meta ADD COLUMN IF NOT EXISTS last_export_at timestamptz;

ALTER TABLE album_user_meta ENABLE ROW LEVEL SECURITY;

DROP POLICY IF EXISTS "Admins manage album user meta" ON album_user_meta;
DROP POLICY IF EXISTS "Users read own album user meta" ON album_user_meta;

CREATE POLICY "Admins manage album user meta"
ON album_user_meta
FOR ALL
TO authenticated
USING (is_album_admin())
WITH CHECK (is_album_admin());

CREATE POLICY "Users read own album user meta"
ON album_user_meta
FOR SELECT
TO authenticated
USING (user_id = auth.uid());

CREATE OR REPLACE FUNCTION consume_album_credit()
RETURNS album_user_meta
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = public
AS $$
DECLARE updated_meta album_user_meta;
BEGIN
  UPDATE album_user_meta
  SET credits = GREATEST(COALESCE(credits, 0) - 1, 0),
      credits_used = COALESCE(credits_used, 0) + 1,
      exports_count = COALESCE(exports_count, 0) + 1,
      last_export_at = now(),
      updated_at = now()
  WHERE user_id = auth.uid()
    AND COALESCE(credits, 0) > 0
  RETURNING * INTO updated_meta;

  IF NOT FOUND THEN
    RAISE EXCEPTION 'Nu ai credite disponibile pentru export.';
  END IF;

  RETURN updated_meta;
END;
$$;

GRANT EXECUTE ON FUNCTION consume_album_credit() TO authenticated;

Import / Export config

Backup local pentru setările aplicației.