-- MySQL schema for Securitask (converted from SQLite migrations)
-- Run this in phpMyAdmin on database `securitask_securitask` before using the app.

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- Drop tables if they exist (order matters because of FKs)
DROP TABLE IF EXISTS user_sessions;
DROP TABLE IF EXISTS overtime_records;
DROP TABLE IF EXISTS holiday_allowances;
DROP TABLE IF EXISTS holiday_requests;
DROP TABLE IF EXISTS check_calls;
DROP TABLE IF EXISTS job_milestones;
DROP TABLE IF EXISTS job_applications;
DROP TABLE IF EXISTS jobs;
DROP TABLE IF EXISTS onboarding_progress;
DROP TABLE IF EXISTS user_documents;
DROP TABLE IF EXISTS password_resets;
DROP TABLE IF EXISTS holidays;
DROP TABLE IF EXISTS marketplace_posts;
DROP TABLE IF EXISTS permissions;
DROP TABLE IF EXISTS invoice_items;
DROP TABLE IF EXISTS invoices;
DROP TABLE IF EXISTS payroll_items;
DROP TABLE IF EXISTS payroll_runs;
DROP TABLE IF EXISTS incidents;
DROP TABLE IF EXISTS patrols;
DROP TABLE IF EXISTS documents;
DROP TABLE IF EXISTS attendance;
DROP TABLE IF EXISTS shifts;
DROP TABLE IF EXISTS sites;
DROP TABLE IF EXISTS clients;
DROP TABLE IF EXISTS employees;
DROP TABLE IF EXISTS users;
DROP TABLE IF EXISTS companies;

-- Core tables
CREATE TABLE companies (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  name VARCHAR(255) NOT NULL,
  email VARCHAR(255) NULL,
  phone VARCHAR(100) NULL,
  address TEXT NULL,
  registration_number VARCHAR(100) NULL,
  license_number VARCHAR(100) NULL,
  license_expiry INT NULL,
  company_logo VARCHAR(255) NULL,
  tax_number VARCHAR(100) NULL,
  insurance_number VARCHAR(100) NULL,
  status VARCHAR(50) NOT NULL DEFAULT 'active',
  created_at INT NOT NULL,
  updated_at INT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE users (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  company_id INT UNSIGNED NULL,
  email VARCHAR(255) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  password_salt VARCHAR(255) NOT NULL,
  name VARCHAR(255) NOT NULL,
  user_type VARCHAR(50) NOT NULL DEFAULT 'individual',
  role VARCHAR(50) NOT NULL,
  phone VARCHAR(100) NULL,
  profile_picture VARCHAR(255) NULL,
  onboarding_complete TINYINT(1) NOT NULL DEFAULT 0,
  registration_ip VARCHAR(100) NULL,
  registration_location VARCHAR(255) NULL,
  status VARCHAR(50) NOT NULL DEFAULT 'active',
  created_at INT NOT NULL,
  updated_at INT NULL,
  PRIMARY KEY (id),
  CONSTRAINT fk_users_company FOREIGN KEY (company_id) REFERENCES companies(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE employees (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  company_id INT UNSIGNED NOT NULL,
  user_id INT UNSIGNED NULL,
  type VARCHAR(50) NOT NULL,
  first_name VARCHAR(100) NOT NULL,
  last_name VARCHAR(100) NOT NULL,
  email VARCHAR(255) NULL,
  phone VARCHAR(100) NOT NULL,
  address TEXT NULL,
  hourly_rate DECIMAL(10,2) NULL,
  daily_rate DECIMAL(10,2) NULL,
  status VARCHAR(50) NOT NULL DEFAULT 'active',
  sia_license VARCHAR(100) NULL,
  sia_expiry INT NULL,
  driving_license VARCHAR(100) NULL,
  driving_expiry INT NULL,
  created_at INT NOT NULL,
  updated_at INT NULL,
  PRIMARY KEY (id),
  CONSTRAINT fk_employees_company FOREIGN KEY (company_id) REFERENCES companies(id),
  CONSTRAINT fk_employees_user FOREIGN KEY (user_id) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE clients (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  company_id INT UNSIGNED NOT NULL,
  name VARCHAR(255) NOT NULL,
  contact_name VARCHAR(255) NULL,
  email VARCHAR(255) NULL,
  phone VARCHAR(100) NULL,
  address TEXT NULL,
  status VARCHAR(50) NOT NULL DEFAULT 'active',
  created_at INT NOT NULL,
  updated_at INT NULL,
  PRIMARY KEY (id),
  CONSTRAINT fk_clients_company FOREIGN KEY (company_id) REFERENCES companies(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE sites (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  client_id INT UNSIGNED NOT NULL,
  name VARCHAR(255) NOT NULL,
  address TEXT NOT NULL,
  latitude DECIMAL(10,7) NULL,
  longitude DECIMAL(10,7) NULL,
  geofence_radius INT NULL DEFAULT 100,
  hourly_rate DECIMAL(10,2) NULL,
  daily_rate DECIMAL(10,2) NULL,
  contact_name VARCHAR(255) NULL,
  contact_phone VARCHAR(100) NULL,
  status VARCHAR(50) NOT NULL DEFAULT 'active',
  created_at INT NOT NULL,
  updated_at INT NULL,
  PRIMARY KEY (id),
  CONSTRAINT fk_sites_client FOREIGN KEY (client_id) REFERENCES clients(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE shifts (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  company_id INT UNSIGNED NOT NULL,
  site_id INT UNSIGNED NOT NULL,
  employee_id INT UNSIGNED NULL,
  shift_date DATE NOT NULL,
  start_time TIME NOT NULL,
  end_time TIME NOT NULL,
  status VARCHAR(50) NOT NULL DEFAULT 'pending',
  hourly_rate DECIMAL(10,2) NULL,
  notes TEXT NULL,
  created_by INT UNSIGNED NULL,
  approved_by INT UNSIGNED NULL,
  approved_at INT NULL,
  created_at INT NOT NULL,
  updated_at INT NULL,
  PRIMARY KEY (id),
  CONSTRAINT fk_shifts_company FOREIGN KEY (company_id) REFERENCES companies(id),
  CONSTRAINT fk_shifts_site FOREIGN KEY (site_id) REFERENCES sites(id),
  CONSTRAINT fk_shifts_employee FOREIGN KEY (employee_id) REFERENCES employees(id),
  CONSTRAINT fk_shifts_created_by FOREIGN KEY (created_by) REFERENCES users(id),
  CONSTRAINT fk_shifts_approved_by FOREIGN KEY (approved_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE attendance (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  shift_id INT UNSIGNED NOT NULL,
  employee_id INT UNSIGNED NOT NULL,
  clock_in INT NULL,
  clock_in_lat DECIMAL(10,7) NULL,
  clock_in_lng DECIMAL(10,7) NULL,
  clock_out INT NULL,
  clock_out_lat DECIMAL(10,7) NULL,
  clock_out_lng DECIMAL(10,7) NULL,
  total_hours DECIMAL(10,2) NULL,
  status VARCHAR(50) NOT NULL DEFAULT 'pending',
  notes TEXT NULL,
  created_at INT NOT NULL,
  updated_at INT NULL,
  PRIMARY KEY (id),
  CONSTRAINT fk_attendance_shift FOREIGN KEY (shift_id) REFERENCES shifts(id),
  CONSTRAINT fk_attendance_employee FOREIGN KEY (employee_id) REFERENCES employees(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE documents (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  entity_type VARCHAR(50) NOT NULL,
  entity_id INT UNSIGNED NOT NULL,
  document_type VARCHAR(100) NOT NULL,
  file_name VARCHAR(255) NOT NULL,
  file_path VARCHAR(255) NOT NULL,
  file_size INT NULL,
  expiry_date INT NULL,
  status VARCHAR(50) NOT NULL DEFAULT 'active',
  visibility VARCHAR(50) NOT NULL DEFAULT 'private',
  uploaded_by INT UNSIGNED NULL,
  created_at INT NOT NULL,
  updated_at INT NULL,
  PRIMARY KEY (id),
  CONSTRAINT fk_documents_uploaded_by FOREIGN KEY (uploaded_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE patrols (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  shift_id INT UNSIGNED NOT NULL,
  employee_id INT UNSIGNED NOT NULL,
  patrol_time INT NOT NULL,
  latitude DECIMAL(10,7) NULL,
  longitude DECIMAL(10,7) NULL,
  notes TEXT NULL,
  photo_path VARCHAR(255) NULL,
  status VARCHAR(50) NOT NULL DEFAULT 'completed',
  created_at INT NOT NULL,
  PRIMARY KEY (id),
  CONSTRAINT fk_patrols_shift FOREIGN KEY (shift_id) REFERENCES shifts(id),
  CONSTRAINT fk_patrols_employee FOREIGN KEY (employee_id) REFERENCES employees(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE incidents (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  shift_id INT UNSIGNED NULL,
  site_id INT UNSIGNED NOT NULL,
  employee_id INT UNSIGNED NOT NULL,
  incident_time INT NOT NULL,
  title VARCHAR(255) NOT NULL,
  description TEXT NOT NULL,
  severity VARCHAR(50) NOT NULL DEFAULT 'low',
  status VARCHAR(50) NOT NULL DEFAULT 'open',
  resolved_at INT NULL,
  resolved_by INT UNSIGNED NULL,
  created_at INT NOT NULL,
  updated_at INT NULL,
  PRIMARY KEY (id),
  CONSTRAINT fk_incidents_shift FOREIGN KEY (shift_id) REFERENCES shifts(id),
  CONSTRAINT fk_incidents_site FOREIGN KEY (site_id) REFERENCES sites(id),
  CONSTRAINT fk_incidents_employee FOREIGN KEY (employee_id) REFERENCES employees(id),
  CONSTRAINT fk_incidents_resolved_by FOREIGN KEY (resolved_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE payroll_runs (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  company_id INT UNSIGNED NOT NULL,
  period_start DATE NOT NULL,
  period_end DATE NOT NULL,
  status VARCHAR(50) NOT NULL DEFAULT 'draft',
  total_amount DECIMAL(12,2) NOT NULL DEFAULT 0,
  processed_by INT UNSIGNED NULL,
  processed_at INT NULL,
  created_at INT NOT NULL,
  updated_at INT NULL,
  PRIMARY KEY (id),
  CONSTRAINT fk_payroll_runs_company FOREIGN KEY (company_id) REFERENCES companies(id),
  CONSTRAINT fk_payroll_runs_processed_by FOREIGN KEY (processed_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE payroll_items (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  payroll_run_id INT UNSIGNED NOT NULL,
  employee_id INT UNSIGNED NOT NULL,
  hours_worked DECIMAL(10,2) NOT NULL DEFAULT 0,
  hourly_rate DECIMAL(10,2) NOT NULL DEFAULT 0,
  gross_amount DECIMAL(12,2) NOT NULL DEFAULT 0,
  deductions DECIMAL(12,2) NOT NULL DEFAULT 0,
  net_amount DECIMAL(12,2) NOT NULL DEFAULT 0,
  status VARCHAR(50) NOT NULL DEFAULT 'pending',
  created_at INT NOT NULL,
  PRIMARY KEY (id),
  CONSTRAINT fk_payroll_items_run FOREIGN KEY (payroll_run_id) REFERENCES payroll_runs(id),
  CONSTRAINT fk_payroll_items_employee FOREIGN KEY (employee_id) REFERENCES employees(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE invoices (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  company_id INT UNSIGNED NOT NULL,
  client_id INT UNSIGNED NOT NULL,
  invoice_number VARCHAR(100) NOT NULL UNIQUE,
  invoice_date DATE NOT NULL,
  due_date DATE NOT NULL,
  subtotal DECIMAL(12,2) NOT NULL DEFAULT 0,
  tax DECIMAL(12,2) NOT NULL DEFAULT 0,
  total DECIMAL(12,2) NOT NULL DEFAULT 0,
  status VARCHAR(50) NOT NULL DEFAULT 'draft',
  notes TEXT NULL,
  created_by INT UNSIGNED NULL,
  created_at INT NOT NULL,
  updated_at INT NULL,
  PRIMARY KEY (id),
  CONSTRAINT fk_invoices_company FOREIGN KEY (company_id) REFERENCES companies(id),
  CONSTRAINT fk_invoices_client FOREIGN KEY (client_id) REFERENCES clients(id),
  CONSTRAINT fk_invoices_created_by FOREIGN KEY (created_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE invoice_items (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  invoice_id INT UNSIGNED NOT NULL,
  shift_id INT UNSIGNED NULL,
  description VARCHAR(255) NOT NULL,
  quantity DECIMAL(10,2) NOT NULL DEFAULT 1,
  unit_price DECIMAL(10,2) NOT NULL DEFAULT 0,
  amount DECIMAL(12,2) NOT NULL DEFAULT 0,
  created_at INT NOT NULL,
  PRIMARY KEY (id),
  CONSTRAINT fk_invoice_items_invoice FOREIGN KEY (invoice_id) REFERENCES invoices(id),
  CONSTRAINT fk_invoice_items_shift FOREIGN KEY (shift_id) REFERENCES shifts(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE permissions (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  role VARCHAR(100) NOT NULL,
  resource VARCHAR(100) NOT NULL,
  action VARCHAR(100) NOT NULL,
  allowed TINYINT(1) NOT NULL DEFAULT 1,
  created_at INT NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uniq_role_resource_action (role, resource, action)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE marketplace_posts (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  company_id INT UNSIGNED NOT NULL,
  shift_id INT UNSIGNED NULL,
  title VARCHAR(255) NOT NULL,
  description TEXT NULL,
  shift_date DATE NOT NULL,
  start_time TIME NOT NULL,
  end_time TIME NOT NULL,
  site_location VARCHAR(255) NULL,
  hourly_rate DECIMAL(10,2) NULL,
  status VARCHAR(50) NOT NULL DEFAULT 'open',
  created_by INT UNSIGNED NULL,
  accepted_by INT UNSIGNED NULL,
  accepted_at INT NULL,
  created_at INT NOT NULL,
  updated_at INT NULL,
  PRIMARY KEY (id),
  CONSTRAINT fk_marketplace_company FOREIGN KEY (company_id) REFERENCES companies(id),
  CONSTRAINT fk_marketplace_shift FOREIGN KEY (shift_id) REFERENCES shifts(id),
  CONSTRAINT fk_marketplace_created_by FOREIGN KEY (created_by) REFERENCES users(id),
  CONSTRAINT fk_marketplace_accepted_by FOREIGN KEY (accepted_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE holidays (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  employee_id INT UNSIGNED NOT NULL,
  start_date DATE NOT NULL,
  end_date DATE NOT NULL,
  days_count INT NOT NULL,
  reason TEXT NULL,
  status VARCHAR(50) NOT NULL DEFAULT 'pending',
  approved_by INT UNSIGNED NULL,
  approved_at INT NULL,
  created_at INT NOT NULL,
  updated_at INT NULL,
  PRIMARY KEY (id),
  CONSTRAINT fk_holidays_employee FOREIGN KEY (employee_id) REFERENCES employees(id),
  CONSTRAINT fk_holidays_approved_by FOREIGN KEY (approved_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE password_resets (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id INT UNSIGNED NOT NULL,
  token VARCHAR(255) NOT NULL UNIQUE,
  expiry INT NOT NULL,
  used TINYINT(1) NOT NULL DEFAULT 0,
  created_at INT NOT NULL,
  PRIMARY KEY (id),
  CONSTRAINT fk_password_resets_user FOREIGN KEY (user_id) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE user_documents (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id INT UNSIGNED NOT NULL,
  document_type VARCHAR(100) NOT NULL,
  document_name VARCHAR(255) NOT NULL,
  file_path VARCHAR(255) NOT NULL,
  expiry_date INT NULL,
  status VARCHAR(50) NOT NULL DEFAULT 'pending',
  uploaded_at INT NOT NULL,
  PRIMARY KEY (id),
  CONSTRAINT fk_user_documents_user FOREIGN KEY (user_id) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE onboarding_progress (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id INT UNSIGNED NOT NULL UNIQUE,
  work_permit_complete TINYINT(1) NOT NULL DEFAULT 0,
  sia_license_complete TINYINT(1) NOT NULL DEFAULT 0,
  other_licenses_complete TINYINT(1) NOT NULL DEFAULT 0,
  profile_picture_complete TINYINT(1) NOT NULL DEFAULT 0,
  id_documents_complete TINYINT(1) NOT NULL DEFAULT 0,
  bank_details_complete TINYINT(1) NOT NULL DEFAULT 0,
  completed_at INT NULL,
  PRIMARY KEY (id),
  CONSTRAINT fk_onboarding_user FOREIGN KEY (user_id) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE jobs (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  posted_by INT UNSIGNED NOT NULL,
  title VARCHAR(255) NOT NULL,
  description TEXT NOT NULL,
  skills_required TEXT NULL,
  budget DECIMAL(12,2) NULL,
  payment_type VARCHAR(50) NOT NULL DEFAULT 'fixed',
  duration VARCHAR(100) NULL,
  location VARCHAR(255) NULL,
  experience_level VARCHAR(100) NULL,
  status VARCHAR(50) NOT NULL DEFAULT 'open',
  applications_count INT NOT NULL DEFAULT 0,
  created_at INT NOT NULL,
  updated_at INT NULL,
  closed_at INT NULL,
  PRIMARY KEY (id),
  CONSTRAINT fk_jobs_posted_by FOREIGN KEY (posted_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE job_applications (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  job_id INT UNSIGNED NOT NULL,
  applicant_id INT UNSIGNED NOT NULL,
  cover_letter TEXT NULL,
  proposed_rate DECIMAL(12,2) NULL,
  status VARCHAR(50) NOT NULL DEFAULT 'pending',
  applied_at INT NOT NULL,
  reviewed_at INT NULL,
  PRIMARY KEY (id),
  CONSTRAINT fk_job_applications_job FOREIGN KEY (job_id) REFERENCES jobs(id),
  CONSTRAINT fk_job_applications_applicant FOREIGN KEY (applicant_id) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE job_milestones (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  job_id INT UNSIGNED NOT NULL,
  title VARCHAR(255) NOT NULL,
  description TEXT NULL,
  amount DECIMAL(12,2) NOT NULL,
  status VARCHAR(50) NOT NULL DEFAULT 'pending',
  due_date INT NULL,
  completed_at INT NULL,
  paid_at INT NULL,
  PRIMARY KEY (id),
  CONSTRAINT fk_job_milestones_job FOREIGN KEY (job_id) REFERENCES jobs(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE check_calls (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  shift_id INT UNSIGNED NOT NULL,
  employee_id INT UNSIGNED NOT NULL,
  check_time INT NOT NULL,
  gps_location TEXT NULL,
  notes TEXT NULL,
  PRIMARY KEY (id),
  CONSTRAINT fk_check_calls_shift FOREIGN KEY (shift_id) REFERENCES shifts(id),
  CONSTRAINT fk_check_calls_employee FOREIGN KEY (employee_id) REFERENCES employees(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE holiday_requests (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  employee_id INT UNSIGNED NOT NULL,
  start_date INT NOT NULL,
  end_date INT NOT NULL,
  days_count INT NOT NULL,
  reason TEXT NULL,
  status VARCHAR(50) NOT NULL DEFAULT 'pending',
  approved_by INT UNSIGNED NULL,
  approved_at INT NULL,
  created_at INT NOT NULL,
  PRIMARY KEY (id),
  CONSTRAINT fk_holiday_requests_employee FOREIGN KEY (employee_id) REFERENCES employees(id),
  CONSTRAINT fk_holiday_requests_approved_by FOREIGN KEY (approved_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE holiday_allowances (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  employee_id INT UNSIGNED NOT NULL UNIQUE,
  total_days INT NOT NULL DEFAULT 28,
  used_days INT NOT NULL DEFAULT 0,
  remaining_days INT NOT NULL DEFAULT 28,
  year INT NOT NULL,
  PRIMARY KEY (id),
  CONSTRAINT fk_holiday_allowances_employee FOREIGN KEY (employee_id) REFERENCES employees(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE overtime_records (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  employee_id INT UNSIGNED NOT NULL,
  shift_id INT UNSIGNED NOT NULL,
  overtime_hours DECIMAL(10,2) NOT NULL,
  overtime_rate DECIMAL(10,2) NOT NULL,
  total_amount DECIMAL(12,2) NOT NULL,
  approved TINYINT(1) NOT NULL DEFAULT 0,
  approved_by INT UNSIGNED NULL,
  approved_at INT NULL,
  created_at INT NOT NULL,
  PRIMARY KEY (id),
  CONSTRAINT fk_overtime_records_employee FOREIGN KEY (employee_id) REFERENCES employees(id),
  CONSTRAINT fk_overtime_records_shift FOREIGN KEY (shift_id) REFERENCES shifts(id),
  CONSTRAINT fk_overtime_records_approved_by FOREIGN KEY (approved_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE user_sessions (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id INT UNSIGNED NOT NULL,
  ip_address VARCHAR(100) NULL,
  user_agent VARCHAR(255) NULL,
  location VARCHAR(255) NULL,
  login_at INT NOT NULL,
  logout_at INT NULL,
  PRIMARY KEY (id),
  CONSTRAINT fk_user_sessions_user FOREIGN KEY (user_id) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

SET FOREIGN_KEY_CHECKS = 1;
