-- ========================================
-- DEPLOY POSITIONS AND ROLES TO PRODUCTION
-- ========================================
-- Created: 2026-02-10
-- Purpose: Replace production positions and roles tables with development data
--
-- Dev Database: brickwal_m1_ds
-- - Positions: 98 active records
-- - Roles: 88 records
--
-- Production Database: m1_erp
-- - Will be replaced with dev data
--
-- ========================================
-- DEPLOYMENT INSTRUCTIONS
-- ========================================
-- Option 1: Via SSH on production server
--   ssh to production server
--   mysql -u mavrixone -p m1_erp < DEPLOY_positions_roles_to_prod.sql
--   mysql -u mavrixone -p m1_erp < positions_data_export.sql
--   mysql -u mavrixone -p m1_erp < roles_data_export.sql
--
-- Option 2: Via phpMyAdmin on https://merph.mavrixone/phpmyadmin
--   1. Login to phpMyAdmin
--   2. Select m1_erp database
--   3. Go to SQL tab
--   4. Run this file first (creates backups and clears tables)
--   5. Run positions_data_export.sql
--   6. Run roles_data_export.sql
--   7. Run verification queries at bottom
--
-- Option 3: Use the deployment script below
-- ========================================

USE m1_erp;

-- Show current counts before sync
SELECT '=== BEFORE SYNC ===' as Status;
SELECT 'Current Positions:' as Info, COUNT(*) as Count FROM positions;
SELECT 'Current Roles:' as Info, COUNT(*) as Count FROM roles;

-- Disable foreign key checks temporarily
SET FOREIGN_KEY_CHECKS = 0;

-- ========================================
-- 1. BACKUP CURRENT DATA
-- ========================================
DROP TABLE IF EXISTS positions_backup_20260210;
CREATE TABLE positions_backup_20260210 AS SELECT * FROM positions;
SELECT 'Positions backed up to positions_backup_20260210' as Status;

DROP TABLE IF EXISTS roles_backup_20260210;
CREATE TABLE roles_backup_20260210 AS SELECT * FROM roles;
SELECT 'Roles backed up to roles_backup_20260210' as Status;

-- ========================================
-- 2. CLEAR EXISTING DATA
-- ========================================
-- Note: Using DELETE instead of TRUNCATE because these tables
-- have foreign key constraints from other tables
DELETE FROM positions;
SELECT 'Positions table cleared' as Status;

DELETE FROM roles;
SELECT 'Roles table cleared' as Status;

-- ========================================
-- 3. IMPORT DATA
-- ========================================
-- At this point, you need to run:
-- SOURCE positions_data_export.sql;
-- SOURCE roles_data_export.sql;
--
-- Or import them via phpMyAdmin SQL tab
-- ========================================

-- Re-enable foreign key checks
SET FOREIGN_KEY_CHECKS = 1;

-- ========================================
-- 4. VERIFICATION QUERIES
-- ========================================
SELECT '=== AFTER SYNC ===' as Status;
SELECT 'New Positions Count:' as Info, COUNT(*) as Count FROM positions;
SELECT 'New Roles Count:' as Info, COUNT(*) as Count FROM roles;
SELECT 'Active Positions:' as Info, COUNT(*) as Count FROM positions WHERE is_active = 1;

-- Show sample data to verify
SELECT '=== SAMPLE POSITIONS ===' as Status;
SELECT id, title, department, level, is_active 
FROM positions 
WHERE is_active = 1 
ORDER BY level, title 
LIMIT 15;

SELECT '=== SAMPLE ROLES ===' as Status;
SELECT id, name, is_system 
FROM roles 
ORDER BY name 
LIMIT 15;

-- ========================================
-- ROLLBACK (if needed)
-- ========================================
-- To rollback if something goes wrong:
-- SET FOREIGN_KEY_CHECKS = 0;
-- DELETE FROM positions;
-- INSERT INTO positions SELECT * FROM positions_backup_20260210;
-- DELETE FROM roles;
-- INSERT INTO roles SELECT * FROM roles_backup_20260210;
-- SET FOREIGN_KEY_CHECKS = 1;
-- ========================================