Array
(
    [Trigger] => trg_branches_before_insert
    [Event] => INSERT
    [Table] => branches
    [Statement] => BEGIN
    DECLARE v_company_id INT DEFAULT NULL;
    DECLARE v_manager_count INT DEFAULT 0;

    IF NEW.brand_id IS NULL OR NEW.brand_id <= 0 THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'Branch brand_id is required.';
    END IF;

    SELECT b.company_id
    INTO v_company_id
    FROM brands b
    WHERE b.brand_id = NEW.brand_id
      AND b.is_active = 1
    LIMIT 1;

    IF v_company_id IS NULL THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'Invalid or inactive brand selected for branch.';
    END IF;

    SET NEW.company_id = v_company_id;

    IF NEW.assigned_employee_id IS NOT NULL AND TRIM(NEW.assigned_employee_id) <> '' THEN
        SELECT COUNT(*)
        INTO v_manager_count
        FROM employees e
        WHERE e.employee_id = NEW.assigned_employee_id
          AND e.company_id = NEW.company_id
          AND e.brand_id = NEW.brand_id
          AND e.status = 'active';

        IF v_manager_count = 0 THEN
            SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = 'Assigned branch manager must be an active employee under the same brand.';
        END IF;
    END IF;
END
    [Timing] => BEFORE
    [Created] => 2026-05-06 12:53:33.59
    [sql_mode] => NO_AUTO_VALUE_ON_ZERO
    [Definer] => root@localhost
    [character_set_client] => utf8mb4
    [collation_connection] => utf8mb4_general_ci
    [Database Collation] => utf8mb4_general_ci
)
Array
(
    [Trigger] => trg_branches_before_update
    [Event] => UPDATE
    [Table] => branches
    [Statement] => BEGIN
    DECLARE v_company_id INT DEFAULT NULL;
    DECLARE v_manager_count INT DEFAULT 0;
    DECLARE v_employee_count INT DEFAULT 0;

    IF NEW.brand_id IS NULL OR NEW.brand_id <= 0 THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'Branch brand_id is required.';
    END IF;

    IF NOT (OLD.brand_id <=> NEW.brand_id) THEN
        SELECT COUNT(*)
        INTO v_employee_count
        FROM employees e
        WHERE e.branch_id = OLD.branch_id;

        IF v_employee_count > 0 THEN
            SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = 'Cannot move branch to another brand while employees are assigned.';
        END IF;
    END IF;

    SELECT b.company_id
    INTO v_company_id
    FROM brands b
    WHERE b.brand_id = NEW.brand_id
      AND b.is_active = 1
    LIMIT 1;

    IF v_company_id IS NULL THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'Invalid or inactive brand selected for branch.';
    END IF;

    SET NEW.company_id = v_company_id;

    IF NEW.assigned_employee_id IS NOT NULL AND TRIM(NEW.assigned_employee_id) <> '' THEN
        SELECT COUNT(*)
        INTO v_manager_count
        FROM employees e
        WHERE e.employee_id = NEW.assigned_employee_id
          AND e.company_id = NEW.company_id
          AND e.brand_id = NEW.brand_id
          AND e.status = 'active';

        IF v_manager_count = 0 THEN
            SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = 'Assigned branch manager must be an active employee under the same brand.';
        END IF;
    END IF;
END
    [Timing] => BEFORE
    [Created] => 2026-05-06 12:53:33.60
    [sql_mode] => NO_AUTO_VALUE_ON_ZERO
    [Definer] => root@localhost
    [character_set_client] => utf8mb4
    [collation_connection] => utf8mb4_general_ci
    [Database Collation] => utf8mb4_general_ci
)
Array
(
    [Trigger] => trg_branches_after_update
    [Event] => UPDATE
    [Table] => branches
    [Statement] => BEGIN
    IF NOT (OLD.branch_name <=> NEW.branch_name) THEN
        UPDATE employees
        SET branch_name = NEW.branch_name
        WHERE branch_id = OLD.branch_id;
    END IF;
END
    [Timing] => AFTER
    [Created] => 2026-07-20 13:29:01.60
    [sql_mode] => NO_AUTO_VALUE_ON_ZERO
    [Definer] => horizondb_root@localhost
    [character_set_client] => utf8mb4
    [collation_connection] => utf8mb4_unicode_ci
    [Database Collation] => utf8mb4_general_ci
)
Array
(
    [Trigger] => trg_branches_after_delete
    [Event] => DELETE
    [Table] => branches
    [Statement] => BEGIN
    UPDATE employees
    SET branch_id = NULL,
        branch_name = NULL
    WHERE branch_id = OLD.branch_id;
END
    [Timing] => AFTER
    [Created] => 2026-05-06 12:53:33.57
    [sql_mode] => NO_AUTO_VALUE_ON_ZERO
    [Definer] => root@localhost
    [character_set_client] => utf8mb4
    [collation_connection] => utf8mb4_general_ci
    [Database Collation] => utf8mb4_general_ci
)