-- Profit-based bonus pools (10% of net profit) with manual allocations and approval workflow

CREATE TABLE IF NOT EXISTS profit_bonus_pools (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  month_year DATE NOT NULL COMMENT 'First day of month (YYYY-MM-01)',
  branch_id BIGINT UNSIGNED NOT NULL,

  net_profit DECIMAL(14,2) NOT NULL DEFAULT 0.00,
  pool_percent DECIMAL(5,2) NOT NULL DEFAULT 10.00,
  pool_amount DECIMAL(14,2) NOT NULL DEFAULT 0.00,

  status ENUM('DRAFT','PENDING_APPROVAL','APPROVED','POSTED') NOT NULL DEFAULT 'DRAFT',
  approved_at DATETIME NULL,
  approved_by BIGINT UNSIGNED NULL,

  created_by BIGINT UNSIGNED NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,

  PRIMARY KEY (id),
  UNIQUE KEY uq_profit_bonus_month_branch (month_year, branch_id),
  KEY idx_profit_bonus_status (status, month_year),
  KEY idx_profit_bonus_branch (branch_id, month_year),
  CONSTRAINT fk_profit_bonus_branch FOREIGN KEY (branch_id) REFERENCES branches(id) ON DELETE RESTRICT,
  CONSTRAINT fk_profit_bonus_approved_by FOREIGN KEY (approved_by) REFERENCES users(id) ON DELETE SET NULL,
  CONSTRAINT fk_profit_bonus_created_by FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS profit_bonus_pool_allocations (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  pool_id BIGINT UNSIGNED NOT NULL,
  user_id BIGINT UNSIGNED NOT NULL,
  percent DECIMAL(6,3) NOT NULL DEFAULT 0.000,
  amount DECIMAL(14,2) NOT NULL DEFAULT 0.00,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_profit_bonus_pool_user (pool_id, user_id),
  KEY idx_profit_bonus_alloc_pool (pool_id),
  KEY idx_profit_bonus_alloc_user (user_id),
  CONSTRAINT fk_profit_bonus_alloc_pool FOREIGN KEY (pool_id) REFERENCES profit_bonus_pools(id) ON DELETE CASCADE,
  CONSTRAINT fk_profit_bonus_alloc_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;