-- =====================================================
-- DMS APPLICATION - COMPLETE TRUNCATE SCRIPT
-- Version: 2.2
-- Date: 2026-02-10
-- 
-- This script completely truncates ALL data from the database
-- while preserving the Super Admin user (id=1) and essential system data
-- =====================================================

-- =====================================================
-- STEP 1: DELETE CHILD RECORDS FIRST (Most dependent tables)
-- =====================================================

-- Delete reminder logs
DELETE FROM reminder_logs;

-- Delete favourites
DELETE FROM favourites;

-- Delete uploaded documents
DELETE FROM uploaded_documents;

-- Delete document groups (may not exist)
DO $$ 
BEGIN
    DELETE FROM document_groups;
EXCEPTION WHEN undefined_table THEN
    RAISE NOTICE 'Table document_groups does not exist, skipping';
END $$;

DO $$ 
BEGIN
    DELETE FROM company_document_groups;
EXCEPTION WHEN undefined_table THEN
    RAISE NOTICE 'Table company_document_groups does not exist, skipping';
END $$;

-- Delete child document mandatory (may not exist)
DO $$ 
BEGIN
    DELETE FROM child_document_mandatory;
EXCEPTION WHEN undefined_table THEN
    RAISE NOTICE 'Table child_document_mandatory does not exist, skipping';
END $$;

-- =====================================================
-- STEP 2: DELETE COMPANY-SPECIFIC SETTINGS
-- =====================================================

DO $$ 
BEGIN
    DELETE FROM company_document_head_reminders;
EXCEPTION WHEN undefined_table THEN
    RAISE NOTICE 'Table company_document_head_reminders does not exist, skipping';
END $$;

DO $$ 
BEGIN
    DELETE FROM company_mandatory_documents;
EXCEPTION WHEN undefined_table THEN
    RAISE NOTICE 'Table company_mandatory_documents does not exist, skipping';
END $$;

DO $$ 
BEGIN
    DELETE FROM company_document_heads;
EXCEPTION WHEN undefined_table THEN
    RAISE NOTICE 'Table company_document_heads does not exist, skipping';
END $$;

DO $$ 
BEGIN
    DELETE FROM company_documents;
EXCEPTION WHEN undefined_table THEN
    RAISE NOTICE 'Table company_documents does not exist, skipping';
END $$;

DO $$ 
BEGIN
    DELETE FROM company_authorities;
EXCEPTION WHEN undefined_table THEN
    RAISE NOTICE 'Table company_authorities does not exist, skipping';
END $$;

-- =====================================================
-- STEP 3: DELETE USER ASSIGNMENTS (ALL - we'll recreate super admin)
-- =====================================================

DO $$ 
BEGIN
    DELETE FROM user_permissions;
EXCEPTION WHEN undefined_table THEN
    RAISE NOTICE 'Table user_permissions does not exist, skipping';
END $$;

DO $$ 
BEGIN
    DELETE FROM user_document_types;
EXCEPTION WHEN undefined_table THEN
    RAISE NOTICE 'Table user_document_types does not exist, skipping';
END $$;

DO $$ 
BEGIN
    DELETE FROM user_authorities;
EXCEPTION WHEN undefined_table THEN
    RAISE NOTICE 'Table user_authorities does not exist, skipping';
END $$;

DO $$ 
BEGIN
    DELETE FROM user_companies;
EXCEPTION WHEN undefined_table THEN
    RAISE NOTICE 'Table user_companies does not exist, skipping';
END $$;

-- =====================================================
-- STEP 4: DELETE HOLIDAYS
-- =====================================================

DO $$ 
BEGIN
    DELETE FROM holidays;
EXCEPTION WHEN undefined_table THEN
    RAISE NOTICE 'Table holidays does not exist, skipping';
END $$;

-- =====================================================
-- STEP 5: DELETE TEMPLATES
-- =====================================================

DO $$ 
BEGIN
    DELETE FROM company_template_document_heads;
EXCEPTION WHEN undefined_table THEN
    RAISE NOTICE 'Table company_template_document_heads does not exist, skipping';
END $$;

DO $$ 
BEGIN
    DELETE FROM company_templates;
EXCEPTION WHEN undefined_table THEN
    RAISE NOTICE 'Table company_templates does not exist, skipping';
END $$;

-- =====================================================
-- STEP 6: DELETE MASTER DATA - DOCUMENT RELATED
-- =====================================================

-- Delete mandatory documents (links sub_type to documents)
DO $$ 
BEGIN
    DELETE FROM mandatory_documents;
EXCEPTION WHEN undefined_table THEN
    RAISE NOTICE 'Table mandatory_documents does not exist, skipping';
END $$;

-- Delete sub_type reminders
DO $$ 
BEGIN
    DELETE FROM sub_type_reminders;
EXCEPTION WHEN undefined_table THEN
    RAISE NOTICE 'Table sub_type_reminders does not exist, skipping';
END $$;

-- Delete documents
DELETE FROM documents;

-- Delete document heads (sub_type)
DELETE FROM sub_type;

-- Delete authorities
DELETE FROM authority;

-- Delete companies
DELETE FROM company;

-- =====================================================
-- STEP 7: DELETE ROLE/ACCESS RELATED TABLES (may not exist)
-- =====================================================

DO $$ 
BEGIN
    DELETE FROM role_access;
EXCEPTION WHEN undefined_table THEN
    RAISE NOTICE 'Table role_access does not exist, skipping';
END $$;

DO $$ 
BEGIN
    DELETE FROM user_priviledges;
EXCEPTION WHEN undefined_table THEN
    RAISE NOTICE 'Table user_priviledges does not exist, skipping';
END $$;

DO $$ 
BEGIN
    DELETE FROM role_permissions;
EXCEPTION WHEN undefined_table THEN
    RAISE NOTICE 'Table role_permissions does not exist, skipping';
END $$;

-- =====================================================
-- STEP 8: DELETE ALL USERS (we'll recreate super admin)
-- =====================================================

DELETE FROM users;

-- =====================================================
-- STEP 9: RESET ALL SEQUENCES
-- =====================================================

-- Reset sequences (ignore errors for non-existent sequences)
DO $$ 
BEGIN
    ALTER SEQUENCE IF EXISTS reminder_logs_id_seq RESTART WITH 1;
EXCEPTION WHEN undefined_table THEN NULL;
END $$;

DO $$ 
BEGIN
    ALTER SEQUENCE IF EXISTS favourites_id_seq RESTART WITH 1;
EXCEPTION WHEN undefined_table THEN NULL;
END $$;

DO $$ 
BEGIN
    ALTER SEQUENCE IF EXISTS uploaded_documents_id_seq RESTART WITH 1;
EXCEPTION WHEN undefined_table THEN NULL;
END $$;

DO $$ 
BEGIN
    ALTER SEQUENCE IF EXISTS document_groups_id_seq RESTART WITH 1;
EXCEPTION WHEN undefined_table THEN NULL;
END $$;

DO $$ 
BEGIN
    ALTER SEQUENCE IF EXISTS company_document_groups_id_seq RESTART WITH 1;
EXCEPTION WHEN undefined_table THEN NULL;
END $$;

DO $$ 
BEGIN
    ALTER SEQUENCE IF EXISTS company_document_head_reminders_id_seq RESTART WITH 1;
EXCEPTION WHEN undefined_table THEN NULL;
END $$;

DO $$ 
BEGIN
    ALTER SEQUENCE IF EXISTS company_mandatory_documents_id_seq RESTART WITH 1;
EXCEPTION WHEN undefined_table THEN NULL;
END $$;

DO $$ 
BEGIN
    ALTER SEQUENCE IF EXISTS company_document_heads_id_seq RESTART WITH 1;
EXCEPTION WHEN undefined_table THEN NULL;
END $$;

DO $$ 
BEGIN
    ALTER SEQUENCE IF EXISTS company_documents_id_seq RESTART WITH 1;
EXCEPTION WHEN undefined_table THEN NULL;
END $$;

DO $$ 
BEGIN
    ALTER SEQUENCE IF EXISTS company_authorities_id_seq RESTART WITH 1;
EXCEPTION WHEN undefined_table THEN NULL;
END $$;

DO $$ 
BEGIN
    ALTER SEQUENCE IF EXISTS user_permissions_id_seq RESTART WITH 1;
EXCEPTION WHEN undefined_table THEN NULL;
END $$;

DO $$ 
BEGIN
    ALTER SEQUENCE IF EXISTS user_document_types_id_seq RESTART WITH 1;
EXCEPTION WHEN undefined_table THEN NULL;
END $$;

DO $$ 
BEGIN
    ALTER SEQUENCE IF EXISTS user_authorities_id_seq RESTART WITH 1;
EXCEPTION WHEN undefined_table THEN NULL;
END $$;

DO $$ 
BEGIN
    ALTER SEQUENCE IF EXISTS user_companies_id_seq RESTART WITH 1;
EXCEPTION WHEN undefined_table THEN NULL;
END $$;

DO $$ 
BEGIN
    ALTER SEQUENCE IF EXISTS holidays_id_seq RESTART WITH 1;
EXCEPTION WHEN undefined_table THEN NULL;
END $$;

DO $$ 
BEGIN
    ALTER SEQUENCE IF EXISTS documents_id_seq RESTART WITH 1;
EXCEPTION WHEN undefined_table THEN NULL;
END $$;

DO $$ 
BEGIN
    ALTER SEQUENCE IF EXISTS sub_type_id_seq RESTART WITH 1;
EXCEPTION WHEN undefined_table THEN NULL;
END $$;

DO $$ 
BEGIN
    ALTER SEQUENCE IF EXISTS authority_id_seq RESTART WITH 1;
EXCEPTION WHEN undefined_table THEN NULL;
END $$;

DO $$ 
BEGIN
    ALTER SEQUENCE IF EXISTS company_id_seq RESTART WITH 1;
EXCEPTION WHEN undefined_table THEN NULL;
END $$;

DO $$ 
BEGIN
    ALTER SEQUENCE IF EXISTS company_templates_id_seq RESTART WITH 1;
EXCEPTION WHEN undefined_table THEN NULL;
END $$;

DO $$ 
BEGIN
    ALTER SEQUENCE IF EXISTS company_template_document_heads_id_seq RESTART WITH 1;
EXCEPTION WHEN undefined_table THEN NULL;
END $$;

DO $$ 
BEGIN
    ALTER SEQUENCE IF EXISTS mandatory_documents_id_seq RESTART WITH 1;
EXCEPTION WHEN undefined_table THEN NULL;
END $$;

DO $$ 
BEGIN
    ALTER SEQUENCE IF EXISTS sub_type_reminders_id_seq RESTART WITH 1;
EXCEPTION WHEN undefined_table THEN NULL;
END $$;

DO $$ 
BEGIN
    ALTER SEQUENCE IF EXISTS child_document_mandatory_id_seq RESTART WITH 1;
EXCEPTION WHEN undefined_table THEN NULL;
END $$;

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

-- =====================================================
-- STEP 10: CREATE SUPER ADMIN USER
-- =====================================================

-- Insert super admin (using actual table columns)
INSERT INTO users (id, first_name, last_name, email_id, password, role_id, status, parent_id)
VALUES (1, 'Super', 'Admin', 'superadmin@dms.com', MD5('Admin@123'), '1', '1', '1');

-- Update sequence to next value
SELECT setval('users_id_seq', 2, false);

-- Ensure super admin has full permissions
DO $$ 
BEGIN
    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;
EXCEPTION WHEN undefined_table THEN
    RAISE NOTICE 'Table user_permissions does not exist, skipping';
END $$;

-- =====================================================
-- STEP 11: ENSURE ROLES EXIST
-- =====================================================

-- Using actual column name 'role_name' instead of 'name'
INSERT INTO role (id, role_name, status) VALUES (1, 'Super Admin', '1') ON CONFLICT (id) DO NOTHING;
INSERT INTO role (id, role_name, status) VALUES (2, 'Admin', '1') ON CONFLICT (id) DO NOTHING;
INSERT INTO role (id, role_name, status) VALUES (3, 'User', '1') ON CONFLICT (id) DO NOTHING;

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

-- =====================================================
-- STEP 12: SEED ROLE PERMISSIONS (if table exists)
-- =====================================================

DO $$ 
BEGIN
    -- Super Admin permissions (role_id = 1) - Full access
    INSERT INTO role_permissions (role_id, permission_key, is_allowed) VALUES
    (1, 'dashboard.view', TRUE),
    (1, 'dashboard.manage', TRUE),
    (1, 'company.view', TRUE),
    (1, 'company.manage', TRUE),
    (1, 'authority.view', TRUE),
    (1, 'authority.manage', TRUE),
    (1, 'type.view', TRUE),
    (1, 'type.manage', TRUE),
    (1, 'document.view', TRUE),
    (1, 'document.manage', TRUE),
    (1, 'upload.view', TRUE),
    (1, 'upload.manage', TRUE),
    (1, 'report.view', TRUE),
    (1, 'report.manage', TRUE),
    (1, 'user.view', TRUE),
    (1, 'user.manage', TRUE),
    (1, 'holiday.view', TRUE),
    (1, 'holiday.manage', TRUE),
    (1, 'company_doc_heads.view', TRUE),
    (1, 'company_doc_heads.manage', TRUE),
    (1, 'reminder.view', TRUE),
    (1, 'reminder.manage', TRUE)
    ON CONFLICT (role_id, permission_key) DO UPDATE SET is_allowed = EXCLUDED.is_allowed;

    -- Admin permissions (role_id = 2)
    INSERT INTO role_permissions (role_id, permission_key, is_allowed) VALUES
    (2, 'dashboard.view', TRUE),
    (2, 'dashboard.manage', TRUE),
    (2, 'company.view', TRUE),
    (2, 'company.manage', FALSE),
    (2, 'authority.view', TRUE),
    (2, 'authority.manage', FALSE),
    (2, 'type.view', TRUE),
    (2, 'type.manage', FALSE),
    (2, 'document.view', TRUE),
    (2, 'document.manage', FALSE),
    (2, 'upload.view', TRUE),
    (2, 'upload.manage', TRUE),
    (2, 'report.view', TRUE),
    (2, 'report.manage', TRUE),
    (2, 'user.view', FALSE),
    (2, 'user.manage', FALSE),
    (2, 'holiday.view', TRUE),
    (2, 'holiday.manage', FALSE),
    (2, 'company_doc_heads.view', TRUE),
    (2, 'company_doc_heads.manage', FALSE),
    (2, 'reminder.view', TRUE),
    (2, 'reminder.manage', FALSE)
    ON CONFLICT (role_id, permission_key) DO UPDATE SET is_allowed = EXCLUDED.is_allowed;

    -- User permissions (role_id = 3)
    INSERT INTO role_permissions (role_id, permission_key, is_allowed) VALUES
    (3, 'dashboard.view', TRUE),
    (3, 'dashboard.manage', FALSE),
    (3, 'company.view', TRUE),
    (3, 'company.manage', FALSE),
    (3, 'authority.view', TRUE),
    (3, 'authority.manage', FALSE),
    (3, 'type.view', TRUE),
    (3, 'type.manage', FALSE),
    (3, 'document.view', TRUE),
    (3, 'document.manage', FALSE),
    (3, 'upload.view', TRUE),
    (3, 'upload.manage', FALSE),
    (3, 'report.view', TRUE),
    (3, 'report.manage', FALSE),
    (3, 'user.view', FALSE),
    (3, 'user.manage', FALSE),
    (3, 'holiday.view', TRUE),
    (3, 'holiday.manage', FALSE),
    (3, 'company_doc_heads.view', TRUE),
    (3, 'company_doc_heads.manage', FALSE),
    (3, 'reminder.view', FALSE),
    (3, 'reminder.manage', FALSE)
    ON CONFLICT (role_id, permission_key) DO UPDATE SET is_allowed = EXCLUDED.is_allowed;
EXCEPTION WHEN undefined_table THEN
    RAISE NOTICE 'Table role_permissions does not exist, skipping';
END $$;

-- =====================================================
-- VERIFICATION QUERIES
-- =====================================================

SELECT '=========================================' as separator;
SELECT 'TRUNCATE COMPLETED SUCCESSFULLY!' as result;
SELECT '=========================================' as separator;

-- Show record counts
SELECT 'Table Record Counts:' as info;
SELECT 'users' as table_name, COUNT(*) as count FROM users
UNION ALL SELECT 'company', COUNT(*) FROM company
UNION ALL SELECT 'authority', COUNT(*) FROM authority
UNION ALL SELECT 'sub_type', COUNT(*) FROM sub_type
UNION ALL SELECT 'documents', COUNT(*) FROM documents
UNION ALL SELECT 'uploaded_documents', COUNT(*) FROM uploaded_documents
UNION ALL SELECT 'favourites', COUNT(*) FROM favourites;

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