-- Migration: Create bonus_pool_settings table
-- Purpose: Configure and manage monthly bonus pools
-- Date: 2026-02-16

CREATE TABLE IF NOT EXISTS bonus_pool_settings (
  id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  month_year DATE NOT NULL COMMENT 'First day of month (YYYY-MM-01)',
  branch_id BIGINT UNSIGNED NULL COMMENT 'NULL = company-wide',
  
  total_pool_amount DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  distribution_method ENUM('PROPORTIONAL','TIERED','FIXED') NOT NULL DEFAULT 'PROPORTIONAL',
  
  -- Tiered thresholds (optional, for TIERED method)
  -- Example: {"tiers": [{"min": 0, "max": 20, "multiplier": 1.0}, {"min": 20, "max": 40, "multiplier": 1.2}, {"min": 40, "max": 100, "multiplier": 1.5}]}
  tier_config JSON NULL COMMENT 'Tier thresholds and multipliers',
  
  is_finalized TINYINT(1) NOT NULL DEFAULT 0,
  finalized_at DATETIME NULL,
  finalized_by BIGINT UNSIGNED NULL,
  
  notes TEXT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  
  UNIQUE KEY uq_month_branch (month_year, branch_id),
  FOREIGN KEY (branch_id) REFERENCES branches(id) ON DELETE CASCADE,
  FOREIGN KEY (finalized_by) REFERENCES users(id) ON DELETE SET NULL,
  INDEX idx_month (month_year),
  INDEX idx_finalized (is_finalized)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;