-- Migration: Create staff_sales_contributions table
-- Purpose: Track monthly sales contributions per staff member
-- Date: 2026-02-16

CREATE TABLE IF NOT EXISTS staff_sales_contributions (
  id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  user_id BIGINT UNSIGNED NOT NULL,
  branch_id BIGINT UNSIGNED NOT NULL,
  month_year DATE NOT NULL COMMENT 'First day of month (YYYY-MM-01)',
  
  -- Sales metrics by type
  total_sales_count INT UNSIGNED NOT NULL DEFAULT 0,
  total_sales_amount DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  total_rental_count INT UNSIGNED NOT NULL DEFAULT 0,
  total_rental_amount DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  total_maintenance_count INT UNSIGNED NOT NULL DEFAULT 0,
  total_maintenance_amount DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  
  -- Combined totals
  combined_transaction_count INT UNSIGNED NOT NULL DEFAULT 0,
  combined_total_amount DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  
  -- Contribution percentages (calculated)
  branch_contribution_pct DECIMAL(5,2) NOT NULL DEFAULT 0.00 COMMENT 'Percentage of branch total',
  company_contribution_pct DECIMAL(5,2) NOT NULL DEFAULT 0.00 COMMENT 'Percentage of company total',
  
  -- Bonus allocation
  bonus_pool_amount DECIMAL(10,2) NULL DEFAULT NULL,
  bonus_paid TINYINT(1) NOT NULL DEFAULT 0,
  bonus_paid_at DATETIME NULL,
  
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  
  UNIQUE KEY uq_user_month (user_id, month_year),
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN KEY (branch_id) REFERENCES branches(id) ON DELETE CASCADE,
  INDEX idx_month_year (month_year),
  INDEX idx_branch_month (branch_id, month_year),
  INDEX idx_user_month (user_id, month_year)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;