-- =====================================================================
-- LogShip template cleanup — Strapi DB truncation
-- ---------------------------------------------------------------------
-- Clears all CONTENT (files metadata, custom collections ad_attachments
-- and m_products, upload folders, publish history) while keeping:
--   admin_users / admin_roles / admin_permissions   (admin logins)
--   strapi_api_tokens*                              (frontend API token)
--   up_users / up_roles / up_permissions            (users-permissions)
--   strapi_core_store_settings, migrations, webhooks, workflows
--
-- The actual upload FILES on disk (/opt/mediaserver/public/uploads/*)
-- are removed by run-cleanup.sh, not here.
--
-- Usage:  psql -U postgres -d strapi -f 02-strapi-truncate.sql
-- =====================================================================

\set ON_ERROR_STOP on

BEGIN;

DO $$
DECLARE
  t text;
BEGIN
  FOREACH t IN ARRAY ARRAY[
    'files_related_mph',
    'files_folder_lnk',
    'files',
    'upload_folders_parent_lnk',
    'upload_folders',
    'ad_attachments',
    'm_products',
    'strapi_history_versions',
    'strapi_release_actions_release_lnk',
    'strapi_release_actions',
    'strapi_releases'
  ] LOOP
    IF to_regclass('public.'||t) IS NOT NULL THEN
      EXECUTE format('TRUNCATE TABLE public.%I RESTART IDENTITY CASCADE', t);
      RAISE NOTICE 'truncated %', t;
    ELSE
      RAISE NOTICE 'skipped % (does not exist)', t;
    END IF;
  END LOOP;
END $$;

SELECT 'files' AS tbl, count(*) FROM files
UNION ALL SELECT 'ad_attachments', count(*) FROM ad_attachments
UNION ALL SELECT 'm_products', count(*) FROM m_products
UNION ALL SELECT 'admin_users (kept)', count(*) FROM admin_users
UNION ALL SELECT 'strapi_api_tokens (kept)', count(*) FROM strapi_api_tokens;

COMMIT;