-- ============================================================================
-- Migration Script: sql_2.sql -> sql.sql structure
-- Generated: 2026-01-03 (Fixed Version)
-- Purpose: Sync sql_2.sql structure to match sql.sql (keeps all data)
-- Encoding: utf8mb4_unicode_ci
-- NOTE: Employee table is SKIPPED per user request
-- ============================================================================

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ============================================================================
-- PART 1: CREATE MISSING TABLES
-- ============================================================================

-- 1.1 Create `clinics` table
CREATE TABLE IF NOT EXISTS `clinics` (
  `clinic_id` int(11) NOT NULL AUTO_INCREMENT,
  `clinic_name` varchar(255) NOT NULL,
  `clinic_address` varchar(500) DEFAULT NULL,
  `clinic_phone` varchar(50) DEFAULT NULL,
  `clinic_email` varchar(100) DEFAULT NULL,
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `em_id` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`clinic_id`),
  KEY `idx_clinics_em_id` (`em_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 1.2 Create `em_seq` table
CREATE TABLE IF NOT EXISTS `em_seq` (
  `id` int(11) NOT NULL DEFAULT 1,
  `seq` int(11) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Insert initial sequence value if not exists
INSERT IGNORE INTO `em_seq` (`id`, `seq`) VALUES (1, 0);

-- 1.3 Create `user_accounts` table
CREATE TABLE IF NOT EXISTS `user_accounts` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(100) NOT NULL,
  `email` varchar(100) NOT NULL,
  `password` varchar(255) NOT NULL,
  `full_name` varchar(100) DEFAULT NULL,
  `phone` varchar(20) DEFAULT NULL,
  `role` varchar(50) NOT NULL DEFAULT 'user',
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `last_login` datetime DEFAULT NULL,
  `em_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `username` (`username`),
  UNIQUE KEY `email` (`email`),
  KEY `idx_user_accounts_em_id` (`em_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='User accounts. em_id NULL = access all clinics';

-- ============================================================================
-- PART 2: ADD MISSING COLUMNS TO EXISTING TABLES
-- ============================================================================

DELIMITER //

-- Helper procedure to add column if not exists
DROP PROCEDURE IF EXISTS add_column_if_not_exists//
CREATE PROCEDURE add_column_if_not_exists(
    IN p_table VARCHAR(64),
    IN p_column VARCHAR(64),
    IN p_definition VARCHAR(255)
)
BEGIN
    DECLARE column_count INT;
    SELECT COUNT(*) INTO column_count
    FROM information_schema.columns
    WHERE table_schema = DATABASE()
      AND table_name = p_table
      AND column_name = p_column;
    
    IF column_count = 0 THEN
        SET @sql = CONCAT('ALTER TABLE `', p_table, '` ADD COLUMN `', p_column, '` ', p_definition);
        PREPARE stmt FROM @sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
    END IF;
END//

-- Helper procedure to add id column with primary key restructure
DROP PROCEDURE IF EXISTS add_id_column_and_restructure_pk//
CREATE PROCEDURE add_id_column_and_restructure_pk(
    IN p_table VARCHAR(64),
    IN p_old_pk VARCHAR(64)
)
BEGIN
    DECLARE column_count INT;
    DECLARE pk_count INT;
    
    -- Check if 'id' column already exists
    SELECT COUNT(*) INTO column_count
    FROM information_schema.columns
    WHERE table_schema = DATABASE()
      AND table_name = p_table
      AND column_name = 'id';
    
    IF column_count = 0 THEN
        -- First, remove AUTO_INCREMENT from old PK column
        SET @sql = CONCAT('ALTER TABLE `', p_table, '` MODIFY `', p_old_pk, '` int(11) NOT NULL');
        PREPARE stmt FROM @sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
        
        -- Drop old primary key
        SET @sql = CONCAT('ALTER TABLE `', p_table, '` DROP PRIMARY KEY');
        PREPARE stmt FROM @sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
        
        -- Add new id column as first col with AUTO_INCREMENT and PRIMARY KEY
        SET @sql = CONCAT('ALTER TABLE `', p_table, '` ADD COLUMN `id` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST');
        PREPARE stmt FROM @sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
    END IF;
END//

-- Helper procedure to add index if not exists
DROP PROCEDURE IF EXISTS add_index_if_not_exists//
CREATE PROCEDURE add_index_if_not_exists(
    IN p_table VARCHAR(64),
    IN p_index VARCHAR(64),
    IN p_columns VARCHAR(255)
)
BEGIN
    DECLARE index_count INT;
    SELECT COUNT(*) INTO index_count
    FROM information_schema.statistics
    WHERE table_schema = DATABASE()
      AND table_name = p_table
      AND index_name = p_index;
    
    IF index_count = 0 THEN
        SET @sql = CONCAT('CREATE INDEX `', p_index, '` ON `', p_table, '` (', p_columns, ')');
        PREPARE stmt FROM @sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
    END IF;
END//

DELIMITER ;

-- ============================================================================
-- 2.1 customers: Add em_id column
-- ============================================================================
CALL add_column_if_not_exists('customers', 'em_id', 'varchar(20) DEFAULT NULL');

-- ============================================================================
-- 2.2 details_treatment: Add id and em_id columns
-- details_treatment does NOT have AUTO_INCREMENT, so we handle differently
-- ============================================================================
CALL add_column_if_not_exists('details_treatment', 'id', 'int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST');
CALL add_column_if_not_exists('details_treatment', 'em_id', 'varchar(20) DEFAULT NULL');

-- ============================================================================
-- 2.3 discount: Restructure primary key and add em_id
-- ============================================================================
CALL add_id_column_and_restructure_pk('discount', 'discount_id');
CALL add_column_if_not_exists('discount', 'em_id', 'varchar(20) DEFAULT NULL');

-- ============================================================================
-- 2.4 doctor: Restructure primary key and add columns
-- ============================================================================
CALL add_id_column_and_restructure_pk('doctor', 'doctor_id');
CALL add_column_if_not_exists('doctor', 'tel_doctor', 'varchar(50) DEFAULT NULL AFTER `name_doctor`');
CALL add_column_if_not_exists('doctor', 'em_id', 'varchar(20) DEFAULT NULL');

-- ============================================================================
-- 2.5 money_cus_pay: Restructure primary key and add em_id
-- ============================================================================
CALL add_id_column_and_restructure_pk('money_cus_pay', 'money_cus_pay_id');
CALL add_column_if_not_exists('money_cus_pay', 'em_id', 'varchar(20) DEFAULT NULL');

-- ============================================================================
-- 2.6 status_pay: Restructure primary key and add em_id
-- ============================================================================
CALL add_id_column_and_restructure_pk('status_pay', 'status_pay_id');
CALL add_column_if_not_exists('status_pay', 'em_id', 'varchar(20) DEFAULT NULL');

-- ============================================================================
-- 2.7 target: Restructure primary key and add em_id
-- ============================================================================
CALL add_id_column_and_restructure_pk('target', 'target_id');
CALL add_column_if_not_exists('target', 'em_id', 'varchar(20) DEFAULT NULL');

-- ============================================================================
-- 2.8 timers: Add em_id column (timers_id stays as PK, just add em_id)
-- ============================================================================
CALL add_column_if_not_exists('timers', 'em_id', 'varchar(20) DEFAULT NULL');

-- ============================================================================
-- PART 3: ADD MISSING INDEXES
-- ============================================================================

-- 3.1 customers indexes
CALL add_index_if_not_exists('customers', 'idx_cus_id', '`cus_id`');
CALL add_index_if_not_exists('customers', 'idx_customers_em_id', '`em_id`');

-- 3.2 details_treatment index
CALL add_index_if_not_exists('details_treatment', 'idx_details_treatment_em_id', '`em_id`');

-- 3.3 discount index
CALL add_index_if_not_exists('discount', 'idx_discount_em_id', '`em_id`');

-- 3.4 doctor index
CALL add_index_if_not_exists('doctor', 'idx_doctor_em_id', '`em_id`');

-- 3.5 money_cus_pay index
CALL add_index_if_not_exists('money_cus_pay', 'idx_money_cus_pay_em_id', '`em_id`');

-- 3.6 status_pay index
CALL add_index_if_not_exists('status_pay', 'idx_status_pay_em_id', '`em_id`');

-- 3.7 target index
CALL add_index_if_not_exists('target', 'idx_target_em_id', '`em_id`');

-- 3.8 timers indexes
CALL add_index_if_not_exists('timers', 'idx_user_created', '`user_id`, `created_at`');
CALL add_index_if_not_exists('timers', 'idx_end_date', '`end_date`');
CALL add_index_if_not_exists('timers', 'idx_timers_em_id', '`em_id`');

-- ============================================================================
-- PART 4: CLEANUP - Drop helper procedures
-- ============================================================================
DROP PROCEDURE IF EXISTS add_column_if_not_exists;
DROP PROCEDURE IF EXISTS add_id_column_and_restructure_pk;
DROP PROCEDURE IF EXISTS add_index_if_not_exists;

-- ============================================================================
-- PART 5: (SKIPPED) EMPLOYEE TABLE
-- User requested to skip employee table modification
-- ============================================================================

SET FOREIGN_KEY_CHECKS = 1;

-- ============================================================================
-- MIGRATION COMPLETE
-- ============================================================================
