-- ═══════════════════════════════════════════════════
-- QAFYS — 003 Payroll Schema
-- Monthly payroll tracking per staff member
-- ═══════════════════════════════════════════════════

CREATE TABLE IF NOT EXISTS `payroll_records` (
  `id`              INT UNSIGNED AUTO_INCREMENT,
  `month`           VARCHAR(7)   NOT NULL,      -- YYYY-MM
  `user_id`         VARCHAR(20)  NOT NULL,
  `amount`          DECIMAL(10,2) DEFAULT 0.00,
  `paid`            TINYINT(1)   DEFAULT 0,
  `payment_method`  ENUM('direct','cash') DEFAULT 'direct',
  `paid_date`       DATE         DEFAULT NULL,
  `note`            TEXT         DEFAULT NULL,
  `created_at`      TIMESTAMP    DEFAULT CURRENT_TIMESTAMP,
  `updated_at`      TIMESTAMP    DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_pr_month_user` (`month`, `user_id`),
  KEY `idx_pr_user` (`user_id`),
  KEY `idx_pr_month` (`month`),
  CONSTRAINT `fk_pr_user` FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;