-- =====================================================
-- DMS APPLICATION - SAFE TRUNCATE SCRIPT
-- Version: 1.0
-- Date: 2026-02-10
-- 
-- This script safely truncates test data while preserving
-- the Super Admin user (id=1) and essential system data
-- =====================================================

-- Start transaction for safety
BEGIN;

-- =====================================================
-- STEP 1: DELETE CHILD RECORDS FIRST
-- =====================================================

-- Delete reminder logs
DELETE FROM reminder_logs;
SELECT 'Deleted reminder_logs' as status;

-- Delete favourites
DELETE FROM favourites;
SELECT 'Deleted favourites' as status;

-- Delete uploaded documents
DELETE FROM uploaded_documents;
SELECT 'Deleted uploaded_documents' as status;

-- Delete document groups
DELETE FROM document_groups;
DELETE FROM company_document_groups;
SELECT 'Deleted document groups' as status;

-- Delete company-specific settings
DELETE FROM company_document_head_reminders;
DELETE FROM company_mandatory_documents;
DELETE FROM company_document_heads;
DELETE FROM company_documents;
DELETE FROM company_authorities;
SELECT 'Deleted company settings' as status;

-- Delete user assignments (except super admin)
DELETE FROM user_permissions WHERE user_id > 1;
DELETE FROM user_document_types WHERE user_id > 1;
DELETE FROM user_authorities WHERE user_id > 1;
DELETE FROM user_companies WHERE user_id > 1;
SELECT 'Deleted user assignments' as status;

-- Delete holidays
DELETE FROM holidays;
SELECT 'Deleted holidays' as status;

-- Delete templates (must be before users due to FK constraint)
DELETE FROM company_template_document_heads;
DELETE FROM company_templates;
SELECT 'Deleted templates' as status;

-- =====================================================
-- STEP 2: DELETE MASTER DATA
-- =====================================================

-- Delete documents
DELETE FROM documents;
SELECT 'Deleted documents' as status;

-- Delete document heads (sub_type)
DELETE FROM sub_type;
SELECT 'Deleted sub_type (document heads)' as status;

-- Delete authorities
DELETE FROM authority;
SELECT 'Deleted authorities' as status;

-- Delete companies
DELETE FROM company;
SELECT 'Deleted companies' as status;

-- Delete users except super admin (id=1)
DELETE FROM users WHERE id > 1;
SELECT 'Deleted users (kept id=1)' as status;

-- =====================================================
-- STEP 3: RESET SEQUENCES
-- =====================================================

-- Reset all sequences to start fresh
ALTER SEQUENCE IF EXISTS reminder_logs_id_seq RESTART WITH 1;
ALTER SEQUENCE IF EXISTS favourites_id_seq RESTART WITH 1;
ALTER SEQUENCE IF EXISTS uploaded_documents_id_seq RESTART WITH 1;
ALTER SEQUENCE IF EXISTS document_groups_id_seq RESTART WITH 1;
ALTER SEQUENCE IF EXISTS company_document_groups_id_seq RESTART WITH 1;
ALTER SEQUENCE IF EXISTS company_document_head_reminders_id_seq RESTART WITH 1;
ALTER SEQUENCE IF EXISTS company_mandatory_documents_id_seq RESTART WITH 1;
ALTER SEQUENCE IF EXISTS company_document_heads_id_seq RESTART WITH 1;
ALTER SEQUENCE IF EXISTS company_documents_id_seq RESTART WITH 1;
ALTER SEQUENCE IF EXISTS company_authorities_id_seq RESTART WITH 1;
ALTER SEQUENCE IF EXISTS user_permissions_id_seq RESTART WITH 1;
ALTER SEQUENCE IF EXISTS user_document_types_id_seq RESTART WITH 1;
ALTER SEQUENCE IF EXISTS user_authorities_id_seq RESTART WITH 1;
ALTER SEQUENCE IF EXISTS user_companies_id_seq RESTART WITH 1;
ALTER SEQUENCE IF EXISTS holidays_id_seq RESTART WITH 1;
ALTER SEQUENCE IF EXISTS documents_id_seq RESTART WITH 1;
ALTER SEQUENCE IF EXISTS sub_type_id_seq RESTART WITH 1;
ALTER SEQUENCE IF EXISTS authority_id_seq RESTART WITH 1;
ALTER SEQUENCE IF EXISTS company_id_seq RESTART WITH 1;
ALTER SEQUENCE IF EXISTS company_templates_id_seq RESTART WITH 1;
ALTER SEQUENCE IF EXISTS company_template_document_heads_id_seq RESTART WITH 1;

-- Users sequence - start from 2 (keep 1 for super admin)
SELECT setval('users_id_seq', 2, false);

SELECT 'Reset all sequences' as status;

-- =====================================================
-- STEP 4: ENSURE SUPER ADMIN EXISTS AND HAS PERMISSIONS
-- =====================================================

-- Ensure super admin exists
INSERT INTO users (id, first_name, last_name, email_id, password, role_id, status, parent_id, created_at)
SELECT 1, 'Super', 'Admin', 'superadmin@dms.com', MD5('Admin@123'), 1, '1', 1, NOW()
WHERE NOT EXISTS (SELECT 1 FROM users WHERE id = 1);

-- Update super admin if exists
UPDATE users SET 
    first_name = 'Super',
    last_name = 'Admin',
    email_id = 'superadmin@dms.com',
    password = MD5('Admin@123'),
    role_id = 1,
    status = '1'
WHERE id = 1;

SELECT 'Ensured Super Admin exists' as status;

-- Ensure super admin has full permissions
INSERT INTO user_permissions (user_id, can_view, can_add_edit, can_delete)
VALUES (1, TRUE, TRUE, TRUE)
ON CONFLICT (user_id) DO UPDATE SET 
    can_view = TRUE, 
    can_add_edit = TRUE, 
    can_delete = TRUE;

SELECT 'Super Admin permissions set' as status;

-- =====================================================
-- STEP 5: ENSURE ROLES EXIST
-- =====================================================

INSERT INTO role (id, name, status) VALUES (1, 'Super Admin', '1') ON CONFLICT (id) DO NOTHING;
INSERT INTO role (id, name, status) VALUES (2, 'Admin', '1') ON CONFLICT (id) DO NOTHING;
INSERT INTO role (id, name, status) VALUES (3, 'User', '1') ON CONFLICT (id) DO NOTHING;

SELECT setval('role_id_seq', 4, false);

SELECT 'Roles verified' as status;

-- =====================================================
-- COMMIT TRANSACTION
-- =====================================================

COMMIT;

SELECT '=========================================' as separator;
SELECT 'TRUNCATE COMPLETED SUCCESSFULLY!' as result;
SELECT 'Super Admin credentials:' as info;
SELECT 'Email: superadmin@dms.com' as email;
SELECT 'Password: Admin@123' as password;
SELECT '=========================================' as separator;

