-- ========================================
-- ALL-IN-ONE SAFE DEPLOYMENT
-- Positions & Roles to Production
-- ========================================
-- This single script does everything in one transaction
-- to avoid table locking issues in phpMyAdmin
-- ========================================

USE m1_erp;

-- Unlock tables if locked
UNLOCK TABLES;

-- Show current state
SELECT '=== STEP 1: BEFORE SYNC ===' as Status;
SELECT 'Production Positions:' as Info, COUNT(*) as Count FROM positions;
SELECT 'Production Roles:' as Info, COUNT(*) as Count FROM roles;

-- ========================================
-- STEP 2: CREATE BACKUPS
-- ========================================
DROP TABLE IF EXISTS positions_backup_20260210;
CREATE TABLE positions_backup_20260210 AS SELECT * FROM positions;

DROP TABLE IF EXISTS roles_backup_20260210;
CREATE TABLE roles_backup_20260210 AS SELECT * FROM roles;

SELECT '=== STEP 2: BACKUPS CREATED ===' as Status;
SELECT CONCAT('✓ Backed up ', COUNT(*), ' positions') as Status FROM positions_backup_20260210;
SELECT CONCAT('✓ Backed up ', COUNT(*), ' roles') as Status FROM roles_backup_20260210;

-- ========================================
-- STEP 3: CREATE TEMPORARY STAGING TABLES
-- ========================================
DROP TABLE IF EXISTS positions_temp;
CREATE TEMPORARY TABLE positions_temp (
    id INT PRIMARY KEY,
    title VARCHAR(100) NOT NULL,
    department VARCHAR(100) NULL,
    description TEXT NULL,
    level VARCHAR(50) NULL,
    reports_to_position_id INT NULL,
    is_active TINYINT(1) DEFAULT 1,
    role_id INT(10) UNSIGNED NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

DROP TABLE IF EXISTS roles_temp;
CREATE TEMPORARY TABLE roles_temp (
    id INT(10) UNSIGNED PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    description TEXT NULL,
    is_system TINYINT(1) NOT NULL DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

SELECT '=== STEP 3: TEMPORARY TABLES CREATED ===' as Status;

-- ========================================
-- STEP 4: STOP HERE - RUN STAGING FILES
-- ========================================
-- You need to run these next:
-- 1. Modify positions_data_STAGING.sql: Change `positions_staging` to `positions_temp`
-- 2. Modify roles_data_STAGING.sql: Change `roles_staging` to `roles_temp`  
-- 3. Run both files
-- 4. Then run DEPLOY_ALL_IN_ONE_PART2.sql
-- ========================================