-- =====================================================
-- DMS APPLICATION - TEST DATA TRUNCATE SCRIPT
-- Version: 1.0
-- Date: 2026-02-10
-- Purpose: Clean all test data before running automation tests
-- WARNING: This will DELETE all data. Use with caution!
-- =====================================================

-- Disable foreign key checks temporarily (PostgreSQL approach)
SET session_replication_role = 'replica';

-- =====================================================
-- 1. TRUNCATE TEST-RELATED TABLES (in dependency order)
-- =====================================================

-- Reminder logs
TRUNCATE TABLE reminder_logs RESTART IDENTITY CASCADE;

-- Favourites
TRUNCATE TABLE favourites RESTART IDENTITY CASCADE;

-- Uploaded documents
TRUNCATE TABLE uploaded_documents RESTART IDENTITY CASCADE;

-- Document groups
TRUNCATE TABLE document_groups RESTART IDENTITY CASCADE;
TRUNCATE TABLE company_document_groups RESTART IDENTITY CASCADE;

-- Company specific tables
TRUNCATE TABLE company_document_head_reminders RESTART IDENTITY CASCADE;
TRUNCATE TABLE company_mandatory_documents RESTART IDENTITY CASCADE;
TRUNCATE TABLE company_document_heads RESTART IDENTITY CASCADE;
TRUNCATE TABLE company_documents RESTART IDENTITY CASCADE;
TRUNCATE TABLE company_authorities RESTART IDENTITY CASCADE;

-- User assignment tables
TRUNCATE TABLE user_permissions RESTART IDENTITY CASCADE;
TRUNCATE TABLE user_document_types RESTART IDENTITY CASCADE;
TRUNCATE TABLE user_authorities RESTART IDENTITY CASCADE;
TRUNCATE TABLE user_companies RESTART IDENTITY CASCADE;

-- Holidays
TRUNCATE TABLE holidays RESTART IDENTITY CASCADE;

-- Core data tables (be careful with these)
TRUNCATE TABLE documents RESTART IDENTITY CASCADE;
TRUNCATE TABLE sub_type RESTART IDENTITY CASCADE;
TRUNCATE TABLE authority RESTART IDENTITY CASCADE;

-- Company table (keep at least one for system to work)
-- DELETE FROM company WHERE id > 1;
TRUNCATE TABLE company RESTART IDENTITY CASCADE;

-- Users table (keep super admin with id=1)
DELETE FROM users WHERE id > 1;
-- Reset sequence for users
SELECT setval('users_id_seq', (SELECT COALESCE(MAX(id), 0) + 1 FROM users), false);

-- Role permissions
TRUNCATE TABLE role_permissions RESTART IDENTITY CASCADE;

-- Company templates
TRUNCATE TABLE company_template_document_heads RESTART IDENTITY CASCADE;
TRUNCATE TABLE company_templates RESTART IDENTITY CASCADE;

-- Re-enable foreign key checks
SET session_replication_role = 'origin';

-- =====================================================
-- 2. VERIFY SUPER ADMIN EXISTS
-- =====================================================
DO $$
BEGIN
    IF NOT EXISTS (SELECT 1 FROM users WHERE id = 1) THEN
        INSERT INTO users (id, first_name, last_name, email_id, password, role_id, status, parent_id, created_at)
        VALUES (1, 'Super', 'Admin', 'superadmin@dms.com', MD5('Admin@123'), 1, '1', 1, NOW());
        
        -- Reset sequence
        PERFORM setval('users_id_seq', 2, false);
    END IF;
END $$;

-- =====================================================
-- 3. ENSURE SUPER ADMIN HAS 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;

-- =====================================================
-- 4. INSERT DEFAULT ROLES IF NOT EXISTS
-- =====================================================
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);

-- =====================================================
-- TRUNCATE COMPLETE
-- =====================================================
SELECT 'Truncate completed successfully. Super Admin (id=1) preserved.' AS status;

