-- =====================================================
-- Quick Export Script for pafda_config Database
-- =====================================================
-- This script helps you export your current database
-- Run this in phpMyAdmin SQL tab or MySQL command line
-- =====================================================

-- Step 1: Verify database exists
SHOW DATABASES LIKE 'pafda_config';

-- Step 2: Select the database
USE pafda_config;

-- Step 3: Show all tables
SHOW TABLES;

-- Step 4: Get table count
SELECT COUNT(*) as total_tables 
FROM information_schema.tables 
WHERE table_schema = 'pafda_config';

-- Step 5: Get row counts for all tables
SELECT 
    TABLE_NAME,
    TABLE_ROWS,
    ROUND(((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024), 2) AS 'Size (MB)'
FROM 
    information_schema.TABLES
WHERE 
    TABLE_SCHEMA = 'pafda_config'
ORDER BY 
    TABLE_ROWS DESC;

-- =====================================================
-- Export Individual Table Structures
-- =====================================================

-- Get CREATE TABLE statement for each table
SHOW CREATE TABLE analyst_test_results;
SHOW CREATE TABLE containers;
SHOW CREATE TABLE districts;
SHOW CREATE TABLE divisions;
SHOW CREATE TABLE ingredients;
SHOW CREATE TABLE instruments;
SHOW CREATE TABLE methods;
SHOW CREATE TABLE organizational_units;
SHOW CREATE TABLE packaging_types;
SHOW CREATE TABLE panel_companies;
SHOW CREATE TABLE permissions;
SHOW CREATE TABLE provinces;
SHOW CREATE TABLE rejection_reasons;
SHOW CREATE TABLE reviewer_comments;
SHOW CREATE TABLE roles;
SHOW CREATE TABLE role_permissions;
SHOW CREATE TABLE samples;
SHOW CREATE TABLE sample_accessioning;
SHOW CREATE TABLE sample_collection_sites;
SHOW CREATE TABLE sample_requirements;
SHOW CREATE TABLE test_transfer;
SHOW CREATE TABLE users;

-- =====================================================
-- Data Verification Queries
-- =====================================================

-- Check RBAC data
SELECT 'Users' as Table_Name, COUNT(*) as Row_Count FROM users
UNION ALL
SELECT 'Roles', COUNT(*) FROM roles
UNION ALL
SELECT 'Permissions', COUNT(*) FROM permissions
UNION ALL
SELECT 'Role Permissions', COUNT(*) FROM role_permissions
UNION ALL
SELECT 'Samples', COUNT(*) FROM samples
UNION ALL
SELECT 'Sample Accessioning', COUNT(*) FROM sample_accessioning
UNION ALL
SELECT 'Analyst Test Results', COUNT(*) FROM analyst_test_results
UNION ALL
SELECT 'Test Transfers', COUNT(*) FROM test_transfer
UNION ALL
SELECT 'Containers', COUNT(*) FROM containers
UNION ALL
SELECT 'Instruments', COUNT(*) FROM instruments
UNION ALL
SELECT 'Methods', COUNT(*) FROM methods
UNION ALL
SELECT 'Provinces', COUNT(*) FROM provinces
UNION ALL
SELECT 'Divisions', COUNT(*) FROM divisions
UNION ALL
SELECT 'Districts', COUNT(*) FROM districts;

-- =====================================================
-- Sample Data Preview
-- =====================================================

-- Preview users (without passwords)
SELECT id, username, email, full_name, is_active, created_at 
FROM users 
LIMIT 5;

-- Preview roles
SELECT id, role_name, role_code, description, is_active 
FROM roles;

-- Preview permissions
SELECT id, permission_name, permission_code, resource, action 
FROM permissions 
LIMIT 10;

-- Preview samples
SELECT id, sample_number, sample_name, sample_type, status, priority, created_at 
FROM samples 
LIMIT 5;

-- Preview analyst test results
SELECT id, barcode, analyst_name, status, review_status, created_at 
FROM analyst_test_results 
LIMIT 5;

-- =====================================================
-- For Command Line Export (Copy these commands)
-- =====================================================

/*
Windows Command Prompt:
cd C:\xampp\mysql\bin
mysqldump -u root -p pafda_config > C:\Users\BILAL\Desktop\pafda_config_full.sql

Export structure only:
mysqldump -u root -p --no-data pafda_config > C:\Users\BILAL\Desktop\pafda_config_structure.sql

Export data only:
mysqldump -u root -p --no-create-info pafda_config > C:\Users\BILAL\Desktop\pafda_config_data.sql

Export with compression:
mysqldump -u root -p pafda_config | gzip > C:\Users\BILAL\Desktop\pafda_config.sql.gz
*/

-- =====================================================
-- Verification After Import (Run on Production)
-- =====================================================

-- Verify all tables exist
SELECT COUNT(*) as tables_created 
FROM information_schema.tables 
WHERE table_schema = 'pafda_config';

-- Verify foreign keys
SELECT 
    TABLE_NAME,
    COLUMN_NAME,
    CONSTRAINT_NAME,
    REFERENCED_TABLE_NAME,
    REFERENCED_COLUMN_NAME
FROM
    information_schema.KEY_COLUMN_USAGE
WHERE
    TABLE_SCHEMA = 'pafda_config'
    AND REFERENCED_TABLE_NAME IS NOT NULL
ORDER BY
    TABLE_NAME;

-- Verify indexes
SELECT 
    TABLE_NAME,
    INDEX_NAME,
    GROUP_CONCAT(COLUMN_NAME ORDER BY SEQ_IN_INDEX) AS COLUMNS
FROM
    information_schema.STATISTICS
WHERE
    TABLE_SCHEMA = 'pafda_config'
GROUP BY
    TABLE_NAME, INDEX_NAME
ORDER BY
    TABLE_NAME, INDEX_NAME;

-- =====================================================
-- END OF EXPORT SCRIPT
-- =====================================================
