-- ===========================================
-- SHIFT MANAGEMENT DATABASE SCHEMA
-- -------------------------------------------
-- Phase 1: Database Foundation
-- Creates tables for schedule-based shift management
-- ===========================================

-- Create shift_types table (master data)
CREATE TABLE IF NOT EXISTS shift_types (
    shift_code VARCHAR(20) PRIMARY KEY,
    shift_name VARCHAR(50) NOT NULL,
    default_start_time TIME NOT NULL,
    default_end_time TIME NOT NULL,
    is_cross_day BOOLEAN DEFAULT FALSE,
    max_hours_regular INT DEFAULT 12,
    max_hours_security INT DEFAULT 14,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Create shift_schedule table (employee schedules)
CREATE TABLE IF NOT EXISTS shift_schedule (
    id INT PRIMARY KEY AUTO_INCREMENT,
    kar_id VARCHAR(20) NOT NULL,
    tanggal DATE NOT NULL,
    shift_code VARCHAR(20) NOT NULL,
    start_time TIME NOT NULL,
    end_time TIME NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    UNIQUE KEY unique_employee_date (kar_id, tanggal),
    INDEX idx_kar_tanggal (kar_id, tanggal),
    INDEX idx_tanggal (tanggal),
    FOREIGN KEY (shift_code) REFERENCES shift_types(shift_code) ON UPDATE CASCADE
);

-- Insert master shift types data
INSERT INTO shift_types (shift_code, shift_name, default_start_time, default_end_time, is_cross_day, max_hours_regular, max_hours_security) VALUES
('pagi', 'Shift Pagi', '08:00:00', '17:00:00', FALSE, 12, 12),
('sore', 'Shift Sore', '16:30:00', '00:30:00', TRUE, 12, 12),
('malam', 'Shift Malam', '00:00:00', '08:00:00', TRUE, 12, 14),
('custom', 'Shift Custom', '00:00:00', '00:00:00', FALSE, 12, 14)
ON DUPLICATE KEY UPDATE
    shift_name = VALUES(shift_name),
    default_start_time = VALUES(default_start_time),
    default_end_time = VALUES(default_end_time),
    is_cross_day = VALUES(is_cross_day),
    max_hours_regular = VALUES(max_hours_regular),
    max_hours_security = VALUES(max_hours_security),
    updated_at = CURRENT_TIMESTAMP;

-- Create indexes for performance optimization
CREATE INDEX IF NOT EXISTS idx_shift_schedule_kar_date ON shift_schedule (kar_id, tanggal);
CREATE INDEX IF NOT EXISTS idx_shift_schedule_date ON shift_schedule (tanggal);
CREATE INDEX IF NOT EXISTS idx_shift_schedule_shift_code ON shift_schedule (shift_code);

-- Add comments for documentation
ALTER TABLE shift_types COMMENT = 'Master data untuk jenis-jenis shift yang tersedia';
ALTER TABLE shift_schedule COMMENT = 'Jadwal shift per karyawan per tanggal';

-- Create view for easy reporting
CREATE OR REPLACE VIEW v_employee_schedule AS
SELECT 
    ss.id,
    ss.kar_id,
    mk.kar_nama,
    ss.tanggal,
    ss.shift_code,
    st.shift_name,
    ss.start_time,
    ss.end_time,
    st.is_cross_day,
    st.max_hours_regular,
    st.max_hours_security,
    ss.created_at,
    ss.updated_at
FROM shift_schedule ss
JOIN shift_types st ON ss.shift_code = st.shift_code
LEFT JOIN mst_karyawan mk ON ss.kar_id = mk.kar_id
WHERE mk.kar_status = 'On' OR mk.kar_status IS NULL;

-- Create procedure for bulk schedule insertion
DELIMITER //
CREATE PROCEDURE IF NOT EXISTS sp_insert_employee_schedule(
    IN p_kar_id VARCHAR(20),
    IN p_tanggal DATE,
    IN p_shift_code VARCHAR(20),
    IN p_start_time TIME,
    IN p_end_time TIME
)
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        RESIGNAL;
    END;
    
    START TRANSACTION;
    
    -- Validate shift_code exists
    IF NOT EXISTS (SELECT 1 FROM shift_types WHERE shift_code = p_shift_code) THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Invalid shift_code';
    END IF;
    
    -- Validate employee exists
    IF NOT EXISTS (SELECT 1 FROM mst_karyawan WHERE kar_id = p_kar_id AND kar_status = 'On') THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Employee not found or inactive';
    END IF;
    
    -- Insert or update schedule
    INSERT INTO shift_schedule (kar_id, tanggal, shift_code, start_time, end_time)
    VALUES (p_kar_id, p_tanggal, p_shift_code, p_start_time, p_end_time)
    ON DUPLICATE KEY UPDATE
        shift_code = VALUES(shift_code),
        start_time = VALUES(start_time),
        end_time = VALUES(end_time),
        updated_at = CURRENT_TIMESTAMP;
    
    COMMIT;
END//
DELIMITER ;

-- Create function to get employee shift for a date
DELIMITER //
CREATE FUNCTION IF NOT EXISTS fn_get_employee_shift(
    p_kar_id VARCHAR(20),
    p_tanggal DATE
) RETURNS JSON
READS SQL DATA
DETERMINISTIC
BEGIN
    DECLARE result JSON;
    
    SELECT JSON_OBJECT(
        'shift_code', shift_code,
        'start_time', start_time,
        'end_time', end_time,
        'found', TRUE
    ) INTO result
    FROM shift_schedule
    WHERE kar_id = p_kar_id AND tanggal = p_tanggal;
    
    IF result IS NULL THEN
        SET result = JSON_OBJECT('found', FALSE);
    END IF;
    
    RETURN result;
END//
DELIMITER ;

-- Grant necessary permissions (adjust as needed)
-- GRANT SELECT, INSERT, UPDATE ON shift_types TO 'sync_user'@'%';
-- GRANT SELECT, INSERT, UPDATE ON shift_schedule TO 'sync_user'@'%';
-- GRANT SELECT ON v_employee_schedule TO 'sync_user'@'%';
-- GRANT EXECUTE ON PROCEDURE sp_insert_employee_schedule TO 'sync_user'@'%';
-- GRANT EXECUTE ON FUNCTION fn_get_employee_shift TO 'sync_user'@'%';

-- Log the migration
INSERT INTO migration_log (migration_name, executed_at, status) 
VALUES ('001_create_shift_tables', NOW(), 'completed')
ON DUPLICATE KEY UPDATE 
    executed_at = NOW(), 
    status = 'completed';

-- Display summary
SELECT 'Shift management tables created successfully' AS status;
SELECT COUNT(*) AS shift_types_count FROM shift_types;
SELECT 'Ready for schedule data import' AS next_step;