-- =====================================================================
-- FCQC Digital Workforce Management Platform — Core Schema
-- Covers: Org Structure, Employee Master (+QR passport), Attendance,
-- Shifts, Leave, Skill Matrix, Production Assignment, Performance,
-- Contractors, Assets, Payroll, Visitors.
-- Idempotent for table creation (CREATE TABLE IF NOT EXISTS).
-- ALTERs on existing tables are applied once.
-- =====================================================================

-- ----------------------------------------------------------------
-- Organization structure (Business Unit / Factory / Building / Floor)
-- Factories themselves live in master_contractors (existing ERP);
-- a 'factory' org-unit row wraps one so geofencing + hierarchy works.
-- ----------------------------------------------------------------
CREATE TABLE IF NOT EXISTS hr_org_units (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  company_id INT UNSIGNED NOT NULL DEFAULT 1,
  parent_id INT UNSIGNED NULL,
  type ENUM('business_unit','factory','building','floor') NOT NULL DEFAULT 'business_unit',
  name VARCHAR(255) NOT NULL,
  code VARCHAR(50) NULL,
  contractor_id INT UNSIGNED NULL COMMENT 'master_contractors.id when type=factory',
  address TEXT NULL,
  geofence_lat DECIMAL(10,7) NULL,
  geofence_lng DECIMAL(10,7) NULL,
  geofence_radius INT UNSIGNED NULL COMMENT 'meters',
  status ENUM('active','inactive') NOT NULL DEFAULT 'active',
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  KEY idx_parent (parent_id),
  KEY idx_type (type),
  KEY idx_contractor (contractor_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS hr_sections (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  company_id INT UNSIGNED NOT NULL DEFAULT 1,
  department_id INT UNSIGNED NOT NULL,
  factory_id INT UNSIGNED NULL,
  name VARCHAR(150) NOT NULL,
  code VARCHAR(30) NULL,
  status ENUM('active','inactive') NOT NULL DEFAULT 'active',
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  KEY idx_dept (department_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS designations (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  company_id INT UNSIGNED NOT NULL DEFAULT 1,
  department_id INT UNSIGNED NULL,
  name VARCHAR(150) NOT NULL,
  code VARCHAR(30) NULL,
  grade VARCHAR(20) NULL,
  status ENUM('active','inactive') NOT NULL DEFAULT 'active',
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS employee_face_templates (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  employee_id INT UNSIGNED NOT NULL,
  template JSON NOT NULL,
  device VARCHAR(100) NULL,
  captured_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_employee (employee_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS employee_documents (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  employee_id INT UNSIGNED NOT NULL,
  doc_type VARCHAR(50) NOT NULL COMMENT 'appointment_letter, nda, id_proof, bank_details, pf_esi, medical, certificate, other',
  title VARCHAR(255) NULL,
  file_path VARCHAR(500) NOT NULL,
  expires_at DATE NULL,
  verified TINYINT(1) NOT NULL DEFAULT 0,
  ocr_data JSON NULL,
  uploaded_by INT UNSIGNED NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  KEY idx_employee (employee_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ----------------------------------------------------------------
-- Skill matrix + training
-- ----------------------------------------------------------------
CREATE TABLE IF NOT EXISTS employee_skills (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  employee_id INT UNSIGNED NOT NULL,
  operation VARCHAR(100) NOT NULL COMMENT 'flatlock, overlock, lockstitch, ...',
  skill_level ENUM('beginner','intermediate','advanced','expert') NOT NULL DEFAULT 'beginner',
  efficiency DECIMAL(5,2) NOT NULL DEFAULT 0 COMMENT '0-100',
  certification VARCHAR(255) NULL,
  certified_at DATE NULL,
  expires_at DATE NULL,
  notes TEXT NULL,
  updated_by INT UNSIGNED NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_emp_op (employee_id, operation)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS employee_training (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  employee_id INT UNSIGNED NOT NULL,
  course_name VARCHAR(255) NOT NULL,
  category ENUM('safety','quality','machine','sop','compliance','other') NOT NULL DEFAULT 'other',
  trainer VARCHAR(150) NULL,
  training_date DATE NULL,
  expires_at DATE NULL,
  certificate_no VARCHAR(100) NULL,
  status ENUM('scheduled','completed','expired') NOT NULL DEFAULT 'scheduled',
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  KEY idx_employee (employee_id),
  KEY idx_expires (expires_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ----------------------------------------------------------------
-- Shifts / Attendance
-- ----------------------------------------------------------------
CREATE TABLE IF NOT EXISTS shifts (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  company_id INT UNSIGNED NOT NULL DEFAULT 1,
  name VARCHAR(50) NOT NULL,
  code VARCHAR(20) NULL,
  start_time TIME NOT NULL DEFAULT '08:00:00',
  end_time TIME NOT NULL DEFAULT '17:00:00',
  grace_minutes INT UNSIGNED NOT NULL DEFAULT 15,
  break_minutes INT UNSIGNED NOT NULL DEFAULT 60,
  overtime_after_minutes INT UNSIGNED NOT NULL DEFAULT 480,
  working_hours DECIMAL(4,2) NULL,
  is_night TINYINT(1) NOT NULL DEFAULT 0,
  color VARCHAR(20) NULL,
  status ENUM('active','inactive') NOT NULL DEFAULT 'active',
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS attendance (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  company_id INT UNSIGNED NOT NULL DEFAULT 1,
  employee_id INT UNSIGNED NOT NULL,
  attendance_date DATE NOT NULL,
  shift_id INT UNSIGNED NULL,
  status ENUM('present','absent','late','half_day','leave','holiday','off') NOT NULL DEFAULT 'present',
  check_in DATETIME NULL,
  check_out DATETIME NULL,
  work_minutes INT UNSIGNED NOT NULL DEFAULT 0,
  overtime_minutes INT UNSIGNED NOT NULL DEFAULT 0,
  method ENUM('qr','mobile','manual','face','rfid','supervisor') NULL,
  lat DECIMAL(10,7) NULL,
  lng DECIMAL(10,7) NULL,
  notes VARCHAR(500) NULL,
  verified_by INT UNSIGNED NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_emp_date (employee_id, attendance_date),
  KEY idx_company_date (company_id, attendance_date),
  KEY idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ----------------------------------------------------------------
-- Leave
-- ----------------------------------------------------------------
CREATE TABLE IF NOT EXISTS leave_balances (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  company_id INT UNSIGNED NOT NULL DEFAULT 1,
  employee_id INT UNSIGNED NOT NULL,
  year YEAR NOT NULL,
  leave_type VARCHAR(30) NOT NULL COMMENT 'casual, sick, annual, earned, unpaid',
  entitled DECIMAL(5,1) NOT NULL DEFAULT 0,
  used DECIMAL(5,1) NOT NULL DEFAULT 0,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_emp_year_type (employee_id, year, leave_type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS leave_requests (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  company_id INT UNSIGNED NOT NULL DEFAULT 1,
  employee_id INT UNSIGNED NOT NULL,
  leave_type VARCHAR(30) NOT NULL,
  start_date DATE NOT NULL,
  end_date DATE NOT NULL,
  days DECIMAL(5,1) NOT NULL,
  reason TEXT NULL,
  status ENUM('pending','approved','rejected','cancelled') NOT NULL DEFAULT 'pending',
  approved_by INT UNSIGNED NULL,
  approved_at DATETIME NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  KEY idx_employee (employee_id),
  KEY idx_status (status),
  KEY idx_company (company_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ----------------------------------------------------------------
-- Production assignment + performance
-- ----------------------------------------------------------------
CREATE TABLE IF NOT EXISTS production_assignments (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  company_id INT UNSIGNED NOT NULL DEFAULT 1,
  employee_id INT UNSIGNED NOT NULL,
  assignment_date DATE NOT NULL,
  factory_id INT UNSIGNED NULL,
  line_id INT UNSIGNED NULL,
  operation VARCHAR(100) NULL,
  production_order_id INT UNSIGNED NULL,
  shift_id INT UNSIGNED NULL,
  skill_level VARCHAR(20) NULL,
  assigned_by INT UNSIGNED NULL,
  status ENUM('assigned','reassigned','released','completed') NOT NULL DEFAULT 'assigned',
  notes VARCHAR(500) NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  KEY idx_emp_date (employee_id, assignment_date),
  KEY idx_line (line_id),
  KEY idx_company (company_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS performance_metrics (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  company_id INT UNSIGNED NOT NULL DEFAULT 1,
  employee_id INT UNSIGNED NOT NULL,
  period_start DATE NOT NULL,
  period_end DATE NOT NULL,
  attendance_score DECIMAL(5,2) NOT NULL DEFAULT 0,
  efficiency_score DECIMAL(5,2) NOT NULL DEFAULT 0,
  quality_score DECIMAL(5,2) NOT NULL DEFAULT 0,
  productivity_score DECIMAL(5,2) NOT NULL DEFAULT 0,
  training_score DECIMAL(5,2) NOT NULL DEFAULT 0,
  discipline_score DECIMAL(5,2) NOT NULL DEFAULT 0,
  overall_score DECIMAL(5,2) NOT NULL DEFAULT 0,
  source VARCHAR(50) NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_period (employee_id, period_start, period_end)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ----------------------------------------------------------------
-- Contractors / contractor workers
-- ----------------------------------------------------------------
CREATE TABLE IF NOT EXISTS contractors (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  company_id INT UNSIGNED NOT NULL DEFAULT 1,
  name VARCHAR(255) NOT NULL,
  code VARCHAR(30) NULL,
  contract_type VARCHAR(50) NULL,
  contact_person VARCHAR(150) NULL,
  phone VARCHAR(50) NULL,
  address TEXT NULL,
  status ENUM('active','inactive','blacklisted') NOT NULL DEFAULT 'active',
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS contractor_workers (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  company_id INT UNSIGNED NOT NULL DEFAULT 1,
  contractor_id INT UNSIGNED NOT NULL,
  name VARCHAR(255) NOT NULL,
  national_id VARCHAR(100) NULL,
  designation VARCHAR(100) NULL,
  daily_wage DECIMAL(10,2) NOT NULL DEFAULT 0,
  attendance_days INT UNSIGNED NOT NULL DEFAULT 0,
  status ENUM('active','inactive','released') NOT NULL DEFAULT 'active',
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  KEY idx_contractor (contractor_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ----------------------------------------------------------------
-- Assets
-- ----------------------------------------------------------------
CREATE TABLE IF NOT EXISTS assets (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  company_id INT UNSIGNED NOT NULL DEFAULT 1,
  asset_code VARCHAR(50) NOT NULL,
  name VARCHAR(255) NOT NULL,
  category ENUM('uniform','id_card','laptop','tablet','scanner','ppe','tool','other') NOT NULL DEFAULT 'other',
  serial_no VARCHAR(100) NULL,
  value DECIMAL(12,2) NOT NULL DEFAULT 0,
  status ENUM('available','assigned','maintenance','lost','returned') NOT NULL DEFAULT 'available',
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_asset_code (asset_code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS asset_assignments (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  company_id INT UNSIGNED NOT NULL DEFAULT 1,
  asset_id INT UNSIGNED NOT NULL,
  employee_id INT UNSIGNED NULL,
  assigned_at DATETIME NOT NULL,
  returned_at DATETIME NULL,
  issued_by INT UNSIGNED NULL,
  notes VARCHAR(500) NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_asset (asset_id),
  KEY idx_employee (employee_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ----------------------------------------------------------------
-- Payroll (runs + lines)
-- ----------------------------------------------------------------
CREATE TABLE IF NOT EXISTS payroll_runs (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  company_id INT UNSIGNED NOT NULL DEFAULT 1,
  payroll_month CHAR(7) NOT NULL COMMENT 'YYYY-MM',
  factory_id INT UNSIGNED NULL,
  status ENUM('draft','processing','approved','paid') NOT NULL DEFAULT 'draft',
  total_amount DECIMAL(12,2) NOT NULL DEFAULT 0,
  created_by INT UNSIGNED NULL,
  approved_by INT UNSIGNED NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_month_factory (payroll_month, factory_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS payroll_lines (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  payroll_run_id INT UNSIGNED NOT NULL,
  employee_id INT UNSIGNED NOT NULL,
  basic DECIMAL(10,2) NOT NULL DEFAULT 0,
  attendance_days INT UNSIGNED NOT NULL DEFAULT 0,
  overtime_hours DECIMAL(6,2) NOT NULL DEFAULT 0,
  overtime_amount DECIMAL(10,2) NOT NULL DEFAULT 0,
  allowances DECIMAL(10,2) NOT NULL DEFAULT 0,
  deductions DECIMAL(10,2) NOT NULL DEFAULT 0,
  incentive DECIMAL(10,2) NOT NULL DEFAULT 0,
  gross DECIMAL(10,2) NOT NULL DEFAULT 0,
  net DECIMAL(10,2) NOT NULL DEFAULT 0,
  notes VARCHAR(500) NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_run (payroll_run_id),
  KEY idx_employee (employee_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ----------------------------------------------------------------
-- Geofencing for factories (master_contractors)
-- ----------------------------------------------------------------
ALTER TABLE master_contractors
  ADD COLUMN geofence_lat DECIMAL(10,7) NULL AFTER address,
  ADD COLUMN geofence_lng DECIMAL(10,7) NULL AFTER geofence_lat,
  ADD COLUMN geofence_radius INT UNSIGNED NULL COMMENT 'meters' AFTER geofence_lng;

-- ----------------------------------------------------------------
-- HR permissions (grant to Administrator role id=1)
-- ----------------------------------------------------------------
INSERT INTO permissions (code, label, group_name, description) VALUES
('hr.view', 'View HR Module', 'HR', 'View workforce dashboard and HR records'),
('hr.manage', 'Manage HR Records', 'HR', 'Create/edit employees, org structure, skills, assignments'),
('hr.attendance', 'Record Attendance', 'HR', 'Clock in/out and manage attendance'),
('hr.approve', 'Approve HR Requests', 'HR', 'Approve leave and other HR requests')
ON DUPLICATE KEY UPDATE label = VALUES(label), group_name = VALUES(group_name);

INSERT INTO role_permissions (role_id, permission_id)
SELECT 1, id FROM permissions WHERE code IN ('hr.view','hr.manage','hr.attendance','hr.approve')
ON DUPLICATE KEY UPDATE permission_id = VALUES(permission_id);
