-- migrations/001_schema.sql
CREATE TABLE IF NOT EXISTS operators (
  id INT AUTO_INCREMENT PRIMARY KEY,
  code VARCHAR(16) NOT NULL UNIQUE,
  name VARCHAR(128) NOT NULL,
  department VARCHAR(64) DEFAULT 'Sales',
  region VARCHAR(64) DEFAULT NULL,
  active TINYINT(1) DEFAULT 1,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS monthly_targets (
  id INT AUTO_INCREMENT PRIMARY KEY,
  ym CHAR(7) NOT NULL,
  target_reactivation INT NOT NULL DEFAULT 150,
  target_new INT NOT NULL DEFAULT 75,
  telegram_target INT NOT NULL DEFAULT 100,
  insta_accounts_target INT NOT NULL DEFAULT 20,
  insta_videos_target INT NOT NULL DEFAULT 200,
  base_fixed INT NOT NULL DEFAULT 1500000,
  bonus_reactivation_max INT NOT NULL DEFAULT 500000,
  bonus_new_max INT NOT NULL DEFAULT 500000,
  bonus_flow_control INT NOT NULL DEFAULT 300000,
  bonus_telegram INT NOT NULL DEFAULT 200000,
  bonus_instagram INT NOT NULL DEFAULT 300000,
  UNIQUE KEY (ym)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS operator_monthly_metrics (
  id INT AUTO_INCREMENT PRIMARY KEY,
  operator_id INT NOT NULL,
  ym CHAR(7) NOT NULL,
  reac_count INT NOT NULL DEFAULT 0,
  new_count INT NOT NULL DEFAULT 0,
  flow_report_submitted TINYINT(1) NOT NULL DEFAULT 0,
  telegram_added INT NOT NULL DEFAULT 0,
  insta_accounts INT NOT NULL DEFAULT 0,
  insta_videos INT NOT NULL DEFAULT 0,
  rating_position INT DEFAULT NULL,
  bad_comm_count INT NOT NULL DEFAULT 0,
  missing_info_count INT NOT NULL DEFAULT 0,
  absent_days INT NOT NULL DEFAULT 0,
  late_minutes INT NOT NULL DEFAULT 0,
  other_bonus INT NOT NULL DEFAULT 0,
  other_penalty INT NOT NULL DEFAULT 0,
  notes TEXT,
  UNIQUE KEY uniq_op_month (operator_id, ym),
  FOREIGN KEY (operator_id) REFERENCES operators(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT IGNORE INTO operators (code,name,department,region) VALUES
('OP001','Ali Karimov','Sales','Chilonzor'),
('OP002','Dilshod Akramov','Sales','Yakkasaroy'),
('OP003','Malika Qodirova','Sales','Sergeli');

INSERT IGNORE INTO monthly_targets (ym) VALUES ('2025-08');
