-- AGenWork Cloud Twin D1 Schema Foundation
-- Generated from uploaded AGenWork v2.3F-R2 Apps Script sheet definitions.
-- Foundation mode: flexible imports first; constraints and strict relational rules come after parity testing.
PRAGMA foreign_keys = OFF;

-- Source sheet: System_Config
CREATE TABLE IF NOT EXISTS agw_system_config (
  _id INTEGER PRIMARY KEY AUTOINCREMENT,
  original_row_number INTEGER,
  key_value TEXT,
  value_value TEXT,
  description TEXT,
  updated_at TEXT,
  imported_at TEXT DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX IF NOT EXISTS idx_agw_system_config_key_value ON agw_system_config (key_value);
CREATE INDEX IF NOT EXISTS idx_agw_system_config_updated_at ON agw_system_config (updated_at);

-- Source sheet: Users
CREATE TABLE IF NOT EXISTS agw_users (
  _id INTEGER PRIMARY KEY AUTOINCREMENT,
  original_row_number INTEGER,
  user_id TEXT,
  employee_id TEXT,
  email TEXT,
  username TEXT,
  password_hash TEXT,
  salt TEXT,
  role TEXT,
  status TEXT,
  must_change_password INTEGER,
  failed_attempts INTEGER,
  locked_until TEXT,
  last_login TEXT,
  mfa_enabled INTEGER,
  mfa_secret TEXT,
  mfa_verified_at TEXT,
  mfa_recovery_codes TEXT,
  mfa_enrollment_required INTEGER,
  mfa_enrollment_deadline TEXT,
  mfa_last_qr_sent_at TEXT,
  mfa_recovery_codes_viewed_at TEXT,
  created_at TEXT,
  updated_at TEXT,
  agen_core_identity_status TEXT,
  agen_core_identity_id TEXT,
  agen_core_last_sync_at TEXT,
  agen_core_last_sync_run_id TEXT,
  imported_at TEXT DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX IF NOT EXISTS idx_agw_users_user_id ON agw_users (user_id);
CREATE INDEX IF NOT EXISTS idx_agw_users_employee_id ON agw_users (employee_id);
CREATE INDEX IF NOT EXISTS idx_agw_users_status ON agw_users (status);
CREATE INDEX IF NOT EXISTS idx_agw_users_created_at ON agw_users (created_at);
CREATE INDEX IF NOT EXISTS idx_agw_users_updated_at ON agw_users (updated_at);

-- Source sheet: Employees
CREATE TABLE IF NOT EXISTS agw_employees (
  _id INTEGER PRIMARY KEY AUTOINCREMENT,
  original_row_number INTEGER,
  employee_id TEXT,
  first_name TEXT,
  middle_name TEXT,
  last_name TEXT,
  suffix TEXT,
  preferred_name TEXT,
  profile_image_url TEXT,
  sex TEXT,
  birthdate TEXT,
  civil_status TEXT,
  personal_email TEXT,
  company_email TEXT,
  mobile_number TEXT,
  address TEXT,
  emergency_contact_name TEXT,
  emergency_contact_number TEXT,
  department_id TEXT,
  sub_department_id TEXT,
  team_id TEXT,
  cluster_id TEXT,
  position_id TEXT,
  has_multiple_positions INTEGER,
  secondary_department_id TEXT,
  secondary_sub_department_id TEXT,
  secondary_team_id TEXT,
  secondary_cluster_id TEXT,
  secondary_position_id TEXT,
  supervisor_employee_id TEXT,
  employment_status TEXT,
  employment_type TEXT,
  hire_date TEXT,
  probationary_date TEXT,
  regularization_date TEXT,
  separation_date TEXT,
  sss_number TEXT,
  phil_health_number TEXT,
  pag_ibig_number TEXT,
  tin TEXT,
  bank_name TEXT,
  bank_account_number INTEGER,
  base_salary REAL,
  pay_frequency TEXT,
  taxable_meal_allowance REAL,
  taxable_transport_allowance REAL,
  taxable_client_specific_allowance REAL,
  taxable_company_specific_allowance REAL,
  taxable_communication_allowance REAL,
  non_taxable_meal_allowance REAL,
  non_taxable_transport_allowance REAL,
  non_taxable_client_specific_allowance REAL,
  non_taxable_company_specific_allowance REAL,
  non_taxable_internet_allowance REAL,
  sss_deduction REAL,
  phil_health_deduction REAL,
  pag_ibig_deduction REAL,
  withholding_tax_deduction REAL,
  cash_advance_deduction REAL,
  hmo_deduction REAL,
  company_loan_deduction REAL,
  equipment_deduction REAL,
  other_deduction REAL,
  work_location TEXT,
  client_code TEXT,
  client_name TEXT,
  client_lob TEXT,
  client_sub_lob TEXT,
  work_type TEXT,
  profile_status TEXT,
  notes TEXT,
  created_at TEXT,
  updated_at TEXT,
  agen_core_identity_status TEXT,
  agen_core_identity_id TEXT,
  agen_core_last_sync_at TEXT,
  agen_core_last_sync_run_id TEXT,
  imported_at TEXT DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX IF NOT EXISTS idx_agw_employees_employee_id ON agw_employees (employee_id);
CREATE INDEX IF NOT EXISTS idx_agw_employees_created_at ON agw_employees (created_at);
CREATE INDEX IF NOT EXISTS idx_agw_employees_updated_at ON agw_employees (updated_at);

-- Source sheet: Departments
CREATE TABLE IF NOT EXISTS agw_departments (
  _id INTEGER PRIMARY KEY AUTOINCREMENT,
  original_row_number INTEGER,
  department_id TEXT,
  department_name TEXT,
  department_head_employee_id TEXT,
  status TEXT,
  description TEXT,
  created_at TEXT,
  updated_at TEXT,
  imported_at TEXT DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX IF NOT EXISTS idx_agw_departments_department_id ON agw_departments (department_id);
CREATE INDEX IF NOT EXISTS idx_agw_departments_status ON agw_departments (status);
CREATE INDEX IF NOT EXISTS idx_agw_departments_created_at ON agw_departments (created_at);
CREATE INDEX IF NOT EXISTS idx_agw_departments_updated_at ON agw_departments (updated_at);

-- Source sheet: Sub_Departments
CREATE TABLE IF NOT EXISTS agw_sub_departments (
  _id INTEGER PRIMARY KEY AUTOINCREMENT,
  original_row_number INTEGER,
  sub_department_id TEXT,
  department_id TEXT,
  sub_department_name TEXT,
  sub_department_head_employee_id TEXT,
  status TEXT,
  description TEXT,
  created_at TEXT,
  updated_at TEXT,
  imported_at TEXT DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX IF NOT EXISTS idx_agw_sub_departments_sub_department_id ON agw_sub_departments (sub_department_id);
CREATE INDEX IF NOT EXISTS idx_agw_sub_departments_status ON agw_sub_departments (status);
CREATE INDEX IF NOT EXISTS idx_agw_sub_departments_created_at ON agw_sub_departments (created_at);
CREATE INDEX IF NOT EXISTS idx_agw_sub_departments_updated_at ON agw_sub_departments (updated_at);

-- Source sheet: Teams
CREATE TABLE IF NOT EXISTS agw_teams (
  _id INTEGER PRIMARY KEY AUTOINCREMENT,
  original_row_number INTEGER,
  team_id TEXT,
  department_id TEXT,
  sub_department_id TEXT,
  team_name TEXT,
  team_lead_employee_id TEXT,
  status TEXT,
  description TEXT,
  created_at TEXT,
  updated_at TEXT,
  imported_at TEXT DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX IF NOT EXISTS idx_agw_teams_team_id ON agw_teams (team_id);
CREATE INDEX IF NOT EXISTS idx_agw_teams_status ON agw_teams (status);
CREATE INDEX IF NOT EXISTS idx_agw_teams_created_at ON agw_teams (created_at);
CREATE INDEX IF NOT EXISTS idx_agw_teams_updated_at ON agw_teams (updated_at);

-- Source sheet: Clusters
CREATE TABLE IF NOT EXISTS agw_clusters (
  _id INTEGER PRIMARY KEY AUTOINCREMENT,
  original_row_number INTEGER,
  cluster_id TEXT,
  department_id TEXT,
  sub_department_id TEXT,
  team_id TEXT,
  cluster_name TEXT,
  cluster_lead_employee_id TEXT,
  status TEXT,
  description TEXT,
  created_at TEXT,
  updated_at TEXT,
  imported_at TEXT DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX IF NOT EXISTS idx_agw_clusters_cluster_id ON agw_clusters (cluster_id);
CREATE INDEX IF NOT EXISTS idx_agw_clusters_status ON agw_clusters (status);
CREATE INDEX IF NOT EXISTS idx_agw_clusters_created_at ON agw_clusters (created_at);
CREATE INDEX IF NOT EXISTS idx_agw_clusters_updated_at ON agw_clusters (updated_at);

-- Source sheet: Positions
CREATE TABLE IF NOT EXISTS agw_positions (
  _id INTEGER PRIMARY KEY AUTOINCREMENT,
  original_row_number INTEGER,
  position_id TEXT,
  position_title TEXT,
  job_level TEXT,
  department_id TEXT,
  status TEXT,
  description TEXT,
  created_at TEXT,
  updated_at TEXT,
  imported_at TEXT DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX IF NOT EXISTS idx_agw_positions_position_id ON agw_positions (position_id);
CREATE INDEX IF NOT EXISTS idx_agw_positions_status ON agw_positions (status);
CREATE INDEX IF NOT EXISTS idx_agw_positions_created_at ON agw_positions (created_at);
CREATE INDEX IF NOT EXISTS idx_agw_positions_updated_at ON agw_positions (updated_at);

-- Source sheet: Client_Catalog
CREATE TABLE IF NOT EXISTS agw_client_catalog (
  _id INTEGER PRIMARY KEY AUTOINCREMENT,
  original_row_number INTEGER,
  client_id TEXT,
  client_code TEXT,
  client_name TEXT,
  client_lob TEXT,
  client_sub_lob TEXT,
  client_types TEXT,
  status TEXT,
  description TEXT,
  created_at TEXT,
  updated_at TEXT,
  updated_by TEXT,
  imported_at TEXT DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX IF NOT EXISTS idx_agw_client_catalog_client_id ON agw_client_catalog (client_id);
CREATE INDEX IF NOT EXISTS idx_agw_client_catalog_status ON agw_client_catalog (status);
CREATE INDEX IF NOT EXISTS idx_agw_client_catalog_created_at ON agw_client_catalog (created_at);
CREATE INDEX IF NOT EXISTS idx_agw_client_catalog_updated_at ON agw_client_catalog (updated_at);

-- Source sheet: Employment_Status
CREATE TABLE IF NOT EXISTS agw_employment_status (
  _id INTEGER PRIMARY KEY AUTOINCREMENT,
  original_row_number INTEGER,
  status_id TEXT,
  status_name TEXT,
  description TEXT,
  is_active INTEGER,
  created_at TEXT,
  updated_at TEXT,
  imported_at TEXT DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX IF NOT EXISTS idx_agw_employment_status_status_id ON agw_employment_status (status_id);
CREATE INDEX IF NOT EXISTS idx_agw_employment_status_created_at ON agw_employment_status (created_at);
CREATE INDEX IF NOT EXISTS idx_agw_employment_status_updated_at ON agw_employment_status (updated_at);

-- Source sheet: Employee_Documents
CREATE TABLE IF NOT EXISTS agw_employee_documents (
  _id INTEGER PRIMARY KEY AUTOINCREMENT,
  original_row_number INTEGER,
  document_id TEXT,
  employee_id TEXT,
  document_type TEXT,
  document_category TEXT,
  document_name TEXT,
  drive_url TEXT,
  issue_date TEXT,
  expiry_date TEXT,
  confidentiality TEXT,
  status TEXT,
  remarks TEXT,
  submitted_by TEXT,
  submitted_at TEXT,
  reviewed_by TEXT,
  reviewed_at TEXT,
  review_action TEXT,
  review_remarks TEXT,
  uploaded_by TEXT,
  uploaded_at TEXT,
  updated_at TEXT,
  imported_at TEXT DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX IF NOT EXISTS idx_agw_employee_documents_document_id ON agw_employee_documents (document_id);
CREATE INDEX IF NOT EXISTS idx_agw_employee_documents_employee_id ON agw_employee_documents (employee_id);
CREATE INDEX IF NOT EXISTS idx_agw_employee_documents_status ON agw_employee_documents (status);
CREATE INDEX IF NOT EXISTS idx_agw_employee_documents_updated_at ON agw_employee_documents (updated_at);

-- Source sheet: Employee_Movements
CREATE TABLE IF NOT EXISTS agw_employee_movements (
  _id INTEGER PRIMARY KEY AUTOINCREMENT,
  original_row_number INTEGER,
  movement_id TEXT,
  employee_id TEXT,
  movement_type TEXT,
  movement_status TEXT,
  role_status TEXT,
  effective_date TEXT,
  probation_end_date TEXT,
  review_date TEXT,
  contract_reference TEXT,
  previous_department_id TEXT,
  new_department_id TEXT,
  previous_sub_department_id TEXT,
  new_sub_department_id TEXT,
  previous_team_id TEXT,
  new_team_id TEXT,
  previous_cluster_id TEXT,
  new_cluster_id TEXT,
  previous_position_id TEXT,
  new_position_id TEXT,
  previous_client_name TEXT,
  new_client_name TEXT,
  previous_client_lob TEXT,
  new_client_lob TEXT,
  previous_client_sub_lob TEXT,
  new_client_sub_lob TEXT,
  previous_salary REAL,
  new_salary REAL,
  non_taxable_allowance REAL,
  taxable_allowance REAL,
  remarks TEXT,
  requested_by TEXT,
  requested_at TEXT,
  approval_status TEXT,
  approval_department_id TEXT,
  approval_level_required TEXT,
  approved_by TEXT,
  approved_at TEXT,
  rejected_by TEXT,
  rejected_at TEXT,
  review_remarks TEXT,
  created_at TEXT,
  updated_at TEXT,
  imported_at TEXT DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX IF NOT EXISTS idx_agw_employee_movements_movement_id ON agw_employee_movements (movement_id);
CREATE INDEX IF NOT EXISTS idx_agw_employee_movements_employee_id ON agw_employee_movements (employee_id);
CREATE INDEX IF NOT EXISTS idx_agw_employee_movements_created_at ON agw_employee_movements (created_at);
CREATE INDEX IF NOT EXISTS idx_agw_employee_movements_updated_at ON agw_employee_movements (updated_at);

-- Source sheet: Leave_Balances
CREATE TABLE IF NOT EXISTS agw_leave_balances (
  _id INTEGER PRIMARY KEY AUTOINCREMENT,
  original_row_number INTEGER,
  leave_balance_id TEXT,
  employee_id TEXT,
  leave_type TEXT,
  year TEXT,
  opening_balance_hours REAL,
  accrued_hours REAL,
  used_hours REAL,
  remaining_hours REAL,
  sil_hours REAL,
  pto_excess_hours REAL,
  payable_hours REAL,
  forfeitable_hours REAL,
  expired_pto_hours REAL,
  carryover_hours REAL,
  paid_out_hours REAL,
  year_end_status TEXT,
  year_end_processed_at TEXT,
  leave_eligibility TEXT,
  updated_at TEXT,
  imported_at TEXT DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX IF NOT EXISTS idx_agw_leave_balances_leave_balance_id ON agw_leave_balances (leave_balance_id);
CREATE INDEX IF NOT EXISTS idx_agw_leave_balances_employee_id ON agw_leave_balances (employee_id);
CREATE INDEX IF NOT EXISTS idx_agw_leave_balances_updated_at ON agw_leave_balances (updated_at);

-- Source sheet: Leave_Requests
CREATE TABLE IF NOT EXISTS agw_leave_requests (
  _id INTEGER PRIMARY KEY AUTOINCREMENT,
  original_row_number INTEGER,
  leave_request_id TEXT,
  employee_id TEXT,
  leave_type TEXT,
  leave_category TEXT,
  start_date TEXT,
  end_date TEXT,
  hours_per_day REAL,
  total_hours REAL,
  comment TEXT,
  status TEXT,
  approval_chain TEXT,
  current_step INTEGER,
  next_approver TEXT,
  approval_history TEXT,
  final_approved_at TEXT,
  rejected_at TEXT,
  rejected_by TEXT,
  reject_reason TEXT,
  created_at TEXT,
  updated_at TEXT,
  cancelled_at TEXT,
  cancel_reason TEXT,
  imported_at TEXT DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX IF NOT EXISTS idx_agw_leave_requests_leave_request_id ON agw_leave_requests (leave_request_id);
CREATE INDEX IF NOT EXISTS idx_agw_leave_requests_employee_id ON agw_leave_requests (employee_id);
CREATE INDEX IF NOT EXISTS idx_agw_leave_requests_status ON agw_leave_requests (status);
CREATE INDEX IF NOT EXISTS idx_agw_leave_requests_created_at ON agw_leave_requests (created_at);
CREATE INDEX IF NOT EXISTS idx_agw_leave_requests_updated_at ON agw_leave_requests (updated_at);

-- Source sheet: Time_Schedules
CREATE TABLE IF NOT EXISTS agw_time_schedules (
  _id INTEGER PRIMARY KEY AUTOINCREMENT,
  original_row_number INTEGER,
  schedule_id TEXT,
  employee_id TEXT,
  date TEXT,
  shift_start TEXT,
  shift_end TEXT,
  break_start TEXT,
  break_end TEXT,
  lunch_start TEXT,
  lunch_end TEXT,
  status TEXT,
  remarks TEXT,
  created_by TEXT,
  created_at TEXT,
  updated_at TEXT,
  imported_at TEXT DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX IF NOT EXISTS idx_agw_time_schedules_schedule_id ON agw_time_schedules (schedule_id);
CREATE INDEX IF NOT EXISTS idx_agw_time_schedules_employee_id ON agw_time_schedules (employee_id);
CREATE INDEX IF NOT EXISTS idx_agw_time_schedules_status ON agw_time_schedules (status);
CREATE INDEX IF NOT EXISTS idx_agw_time_schedules_date ON agw_time_schedules (date);
CREATE INDEX IF NOT EXISTS idx_agw_time_schedules_created_at ON agw_time_schedules (created_at);

-- Source sheet: Time_Attendance
CREATE TABLE IF NOT EXISTS agw_time_attendance (
  _id INTEGER PRIMARY KEY AUTOINCREMENT,
  original_row_number INTEGER,
  entry_id TEXT,
  employee_id TEXT,
  date TEXT,
  time_in TEXT,
  time_out TEXT,
  status TEXT,
  source TEXT,
  remarks TEXT,
  created_by TEXT,
  created_at TEXT,
  updated_at TEXT,
  imported_at TEXT DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX IF NOT EXISTS idx_agw_time_attendance_entry_id ON agw_time_attendance (entry_id);
CREATE INDEX IF NOT EXISTS idx_agw_time_attendance_employee_id ON agw_time_attendance (employee_id);
CREATE INDEX IF NOT EXISTS idx_agw_time_attendance_status ON agw_time_attendance (status);
CREATE INDEX IF NOT EXISTS idx_agw_time_attendance_date ON agw_time_attendance (date);
CREATE INDEX IF NOT EXISTS idx_agw_time_attendance_created_at ON agw_time_attendance (created_at);

-- Source sheet: Time_Aux
CREATE TABLE IF NOT EXISTS agw_time_aux (
  _id INTEGER PRIMARY KEY AUTOINCREMENT,
  original_row_number INTEGER,
  aux_id TEXT,
  employee_id TEXT,
  date TEXT,
  start_time TEXT,
  end_time TEXT,
  type TEXT,
  remarks TEXT,
  created_by TEXT,
  created_at TEXT,
  updated_at TEXT,
  source_module TEXT,
  source_record_id TEXT,
  import_run_id TEXT,
  paid_flag TEXT,
  productive_flag TEXT,
  imported_at TEXT,
  imported_at TEXT DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX IF NOT EXISTS idx_agw_time_aux_aux_id ON agw_time_aux (aux_id);
CREATE INDEX IF NOT EXISTS idx_agw_time_aux_employee_id ON agw_time_aux (employee_id);
CREATE INDEX IF NOT EXISTS idx_agw_time_aux_date ON agw_time_aux (date);
CREATE INDEX IF NOT EXISTS idx_agw_time_aux_created_at ON agw_time_aux (created_at);
CREATE INDEX IF NOT EXISTS idx_agw_time_aux_updated_at ON agw_time_aux (updated_at);

-- Source sheet: Scheduled_Off
CREATE TABLE IF NOT EXISTS agw_scheduled_off (
  _id INTEGER PRIMARY KEY AUTOINCREMENT,
  original_row_number INTEGER,
  off_id TEXT,
  employee_id TEXT,
  date TEXT,
  type TEXT,
  remarks TEXT,
  assigned_by TEXT,
  created_at TEXT,
  updated_at TEXT,
  imported_at TEXT DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX IF NOT EXISTS idx_agw_scheduled_off_off_id ON agw_scheduled_off (off_id);
CREATE INDEX IF NOT EXISTS idx_agw_scheduled_off_employee_id ON agw_scheduled_off (employee_id);
CREATE INDEX IF NOT EXISTS idx_agw_scheduled_off_date ON agw_scheduled_off (date);
CREATE INDEX IF NOT EXISTS idx_agw_scheduled_off_created_at ON agw_scheduled_off (created_at);
CREATE INDEX IF NOT EXISTS idx_agw_scheduled_off_updated_at ON agw_scheduled_off (updated_at);

-- Source sheet: Time_Summary
CREATE TABLE IF NOT EXISTS agw_time_summary (
  _id INTEGER PRIMARY KEY AUTOINCREMENT,
  original_row_number INTEGER,
  summary_id TEXT,
  employee_id TEXT,
  period_start TEXT,
  period_end TEXT,
  time_source TEXT,
  time_source_label TEXT,
  scheduled_hours REAL,
  actual_work_hours REAL,
  agen_work_time_hours REAL,
  agen_tel_aux_hours REAL,
  regular_work_hours REAL,
  paid_leave_hours REAL,
  unpaid_leave_hours REAL,
  absence_hours REAL,
  scheduled_off_days TEXT,
  aux_hours REAL,
  payroll_paid_hours REAL,
  payroll_unpaid_hours REAL,
  pending_leave_hours REAL,
  time_summary_status TEXT,
  generated_at REAL,
  submitted_by TEXT,
  submitted_at TEXT,
  reviewed_by TEXT,
  reviewed_at TEXT,
  approval_status TEXT,
  approval_action TEXT,
  approval_remarks TEXT,
  next_approver TEXT,
  approval_required INTEGER,
  workweek_hours REAL,
  export_batch_id TEXT,
  export_batch_number TEXT,
  export_locked INTEGER,
  exported_at TEXT,
  exported_by TEXT,
  imported_at TEXT DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX IF NOT EXISTS idx_agw_time_summary_summary_id ON agw_time_summary (summary_id);
CREATE INDEX IF NOT EXISTS idx_agw_time_summary_employee_id ON agw_time_summary (employee_id);

-- Source sheet: Time_Edit_Audit
CREATE TABLE IF NOT EXISTS agw_time_edit_audit (
  _id INTEGER PRIMARY KEY AUTOINCREMENT,
  original_row_number INTEGER,
  audit_id TEXT,
  timestamp TEXT,
  entry_id TEXT,
  employee_id TEXT,
  date TEXT,
  actor_user_id TEXT,
  actor_employee_id TEXT,
  actor_role TEXT,
  before_time_in TEXT,
  after_time_in TEXT,
  before_time_out TEXT,
  after_time_out TEXT,
  before_remarks TEXT,
  after_remarks TEXT,
  approval_status_at_edit TEXT,
  edit_action TEXT,
  details TEXT,
  imported_at TEXT DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX IF NOT EXISTS idx_agw_time_edit_audit_audit_id ON agw_time_edit_audit (audit_id);
CREATE INDEX IF NOT EXISTS idx_agw_time_edit_audit_employee_id ON agw_time_edit_audit (employee_id);
CREATE INDEX IF NOT EXISTS idx_agw_time_edit_audit_date ON agw_time_edit_audit (date);

-- Source sheet: Payroll_Export_Batches
CREATE TABLE IF NOT EXISTS agw_payroll_export_batches (
  _id INTEGER PRIMARY KEY AUTOINCREMENT,
  original_row_number INTEGER,
  batch_id TEXT,
  batch_number TEXT,
  cutoff_month TEXT,
  cutoff_period TEXT,
  period_start TEXT,
  period_end TEXT,
  report_key TEXT,
  status TEXT,
  row_count INTEGER,
  total_workweek_hours REAL,
  total_regular_hours REAL,
  total_overtime_hours REAL,
  total_payroll_paid_hours REAL,
  total_payroll_unpaid_hours REAL,
  generated_by REAL,
  generated_at REAL,
  locked_by TEXT,
  locked_at TEXT,
  voided_by TEXT,
  voided_at TEXT,
  void_reason TEXT,
  remarks TEXT,
  updated_at TEXT,
  agen_pay_handoff_status TEXT,
  agen_pay_handoff_run_id TEXT,
  agen_pay_handoff_at TEXT,
  agen_pay_target_record_id TEXT,
  agen_pay_return_status TEXT,
  agen_pay_return_run_id TEXT,
  agen_pay_return_at TEXT,
  agen_pay_result_status TEXT,
  agen_pay_payroll_run_id TEXT,
  agen_pay_payslip_status TEXT,
  agen_pay_payment_status TEXT,
  agen_pay_disbursement_status TEXT,
  agen_pay_exception_count INTEGER,
  agen_pay_exception_message TEXT,
  agen_pay_processed_at TEXT,
  agen_pay_released_at TEXT,
  imported_at TEXT DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX IF NOT EXISTS idx_agw_payroll_export_batches_batch_id ON agw_payroll_export_batches (batch_id);
CREATE INDEX IF NOT EXISTS idx_agw_payroll_export_batches_status ON agw_payroll_export_batches (status);
CREATE INDEX IF NOT EXISTS idx_agw_payroll_export_batches_updated_at ON agw_payroll_export_batches (updated_at);

-- Source sheet: Payroll_Export_Batch_Items
CREATE TABLE IF NOT EXISTS agw_payroll_export_batch_items (
  _id INTEGER PRIMARY KEY AUTOINCREMENT,
  original_row_number INTEGER,
  batch_item_id TEXT,
  batch_id TEXT,
  batch_number TEXT,
  summary_id TEXT,
  employee_id TEXT,
  employee_name TEXT,
  period_start TEXT,
  period_end TEXT,
  workweek_hours REAL,
  regular_hours REAL,
  overtime_hours REAL,
  payroll_paid_hours REAL,
  payroll_unpaid_hours REAL,
  approval_status TEXT,
  export_locked INTEGER,
  created_at TEXT,
  imported_at TEXT DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX IF NOT EXISTS idx_agw_payroll_export_batch_items_batch_item_id ON agw_payroll_export_batch_items (batch_item_id);
CREATE INDEX IF NOT EXISTS idx_agw_payroll_export_batch_items_employee_id ON agw_payroll_export_batch_items (employee_id);
CREATE INDEX IF NOT EXISTS idx_agw_payroll_export_batch_items_created_at ON agw_payroll_export_batch_items (created_at);
CREATE INDEX IF NOT EXISTS idx_agw_payroll_export_batch_items_batch_id ON agw_payroll_export_batch_items (batch_id);

-- Source sheet: Payroll_Export_Audit
CREATE TABLE IF NOT EXISTS agw_payroll_export_audit (
  _id INTEGER PRIMARY KEY AUTOINCREMENT,
  original_row_number INTEGER,
  audit_id TEXT,
  timestamp TEXT,
  batch_id TEXT,
  batch_number TEXT,
  actor_user_id TEXT,
  actor_employee_id TEXT,
  actor_role TEXT,
  action TEXT,
  old_status TEXT,
  new_status TEXT,
  details TEXT,
  imported_at TEXT DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX IF NOT EXISTS idx_agw_payroll_export_audit_audit_id ON agw_payroll_export_audit (audit_id);
CREATE INDEX IF NOT EXISTS idx_agw_payroll_export_audit_batch_id ON agw_payroll_export_audit (batch_id);

-- Source sheet: Audit_Log
CREATE TABLE IF NOT EXISTS agw_audit_log (
  _id INTEGER PRIMARY KEY AUTOINCREMENT,
  original_row_number INTEGER,
  log_id TEXT,
  timestamp TEXT,
  actor_user_id TEXT,
  actor_employee_id TEXT,
  action TEXT,
  entity_type TEXT,
  entity_id TEXT,
  details TEXT,
  ipaddress TEXT,
  imported_at TEXT DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX IF NOT EXISTS idx_agw_audit_log_log_id ON agw_audit_log (log_id);

-- Source sheet: Security_Sessions
CREATE TABLE IF NOT EXISTS agw_security_sessions (
  _id INTEGER PRIMARY KEY AUTOINCREMENT,
  original_row_number INTEGER,
  session_id TEXT,
  user_id TEXT,
  employee_id TEXT,
  username TEXT,
  role TEXT,
  status TEXT,
  created_at TEXT,
  expires_at TEXT,
  last_heartbeat_at TEXT,
  ended_at TEXT,
  end_reason TEXT,
  device_id TEXT,
  user_agent TEXT,
  imported_at TEXT DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX IF NOT EXISTS idx_agw_security_sessions_session_id ON agw_security_sessions (session_id);
CREATE INDEX IF NOT EXISTS idx_agw_security_sessions_employee_id ON agw_security_sessions (employee_id);
CREATE INDEX IF NOT EXISTS idx_agw_security_sessions_user_id ON agw_security_sessions (user_id);
CREATE INDEX IF NOT EXISTS idx_agw_security_sessions_status ON agw_security_sessions (status);
CREATE INDEX IF NOT EXISTS idx_agw_security_sessions_created_at ON agw_security_sessions (created_at);

-- Source sheet: Security_Events
CREATE TABLE IF NOT EXISTS agw_security_events (
  _id INTEGER PRIMARY KEY AUTOINCREMENT,
  original_row_number INTEGER,
  event_id TEXT,
  timestamp TEXT,
  user_id TEXT,
  employee_id TEXT,
  username TEXT,
  event_type TEXT,
  result TEXT,
  details TEXT,
  device_id TEXT,
  user_agent TEXT,
  imported_at TEXT DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX IF NOT EXISTS idx_agw_security_events_event_id ON agw_security_events (event_id);
CREATE INDEX IF NOT EXISTS idx_agw_security_events_employee_id ON agw_security_events (employee_id);
CREATE INDEX IF NOT EXISTS idx_agw_security_events_user_id ON agw_security_events (user_id);

-- Source sheet: Identity_Roles
CREATE TABLE IF NOT EXISTS agw_identity_roles (
  _id INTEGER PRIMARY KEY AUTOINCREMENT,
  original_row_number INTEGER,
  role_id TEXT,
  role_name TEXT,
  role_level TEXT,
  role_category TEXT,
  mfa_required TEXT,
  status TEXT,
  description TEXT,
  created_at TEXT,
  updated_at TEXT,
  imported_at TEXT DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX IF NOT EXISTS idx_agw_identity_roles_role_id ON agw_identity_roles (role_id);
CREATE INDEX IF NOT EXISTS idx_agw_identity_roles_status ON agw_identity_roles (status);
CREATE INDEX IF NOT EXISTS idx_agw_identity_roles_created_at ON agw_identity_roles (created_at);
CREATE INDEX IF NOT EXISTS idx_agw_identity_roles_updated_at ON agw_identity_roles (updated_at);

-- Source sheet: Permission_Catalog
CREATE TABLE IF NOT EXISTS agw_permission_catalog (
  _id INTEGER PRIMARY KEY AUTOINCREMENT,
  original_row_number INTEGER,
  permission_key TEXT,
  module TEXT,
  action TEXT,
  description TEXT,
  risk_level TEXT,
  status TEXT,
  created_at TEXT,
  updated_at TEXT,
  imported_at TEXT DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX IF NOT EXISTS idx_agw_permission_catalog_permission_key ON agw_permission_catalog (permission_key);
CREATE INDEX IF NOT EXISTS idx_agw_permission_catalog_status ON agw_permission_catalog (status);
CREATE INDEX IF NOT EXISTS idx_agw_permission_catalog_created_at ON agw_permission_catalog (created_at);
CREATE INDEX IF NOT EXISTS idx_agw_permission_catalog_updated_at ON agw_permission_catalog (updated_at);

-- Source sheet: Role_Permissions
CREATE TABLE IF NOT EXISTS agw_role_permissions (
  _id INTEGER PRIMARY KEY AUTOINCREMENT,
  original_row_number INTEGER,
  role_name TEXT,
  permission_key TEXT,
  allowed INTEGER,
  scope_type TEXT,
  description TEXT,
  updated_at TEXT,
  imported_at TEXT DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX IF NOT EXISTS idx_agw_role_permissions_role_name ON agw_role_permissions (role_name);
CREATE INDEX IF NOT EXISTS idx_agw_role_permissions_updated_at ON agw_role_permissions (updated_at);
CREATE INDEX IF NOT EXISTS idx_agw_role_permissions_permission_key ON agw_role_permissions (permission_key);

-- Source sheet: User_Access_Overrides
CREATE TABLE IF NOT EXISTS agw_user_access_overrides (
  _id INTEGER PRIMARY KEY AUTOINCREMENT,
  original_row_number INTEGER,
  override_id TEXT,
  user_id TEXT,
  employee_id TEXT,
  permission_key TEXT,
  allowed INTEGER,
  scope_type TEXT,
  reason TEXT,
  effective_from TEXT,
  effective_to TEXT,
  status TEXT,
  created_by TEXT,
  created_at TEXT,
  updated_at TEXT,
  imported_at TEXT DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX IF NOT EXISTS idx_agw_user_access_overrides_override_id ON agw_user_access_overrides (override_id);
CREATE INDEX IF NOT EXISTS idx_agw_user_access_overrides_employee_id ON agw_user_access_overrides (employee_id);
CREATE INDEX IF NOT EXISTS idx_agw_user_access_overrides_user_id ON agw_user_access_overrides (user_id);
CREATE INDEX IF NOT EXISTS idx_agw_user_access_overrides_status ON agw_user_access_overrides (status);
CREATE INDEX IF NOT EXISTS idx_agw_user_access_overrides_created_at ON agw_user_access_overrides (created_at);

-- Source sheet: MFA_Enrollment_Audit
CREATE TABLE IF NOT EXISTS agw_mfa_enrollment_audit (
  _id INTEGER PRIMARY KEY AUTOINCREMENT,
  original_row_number INTEGER,
  event_id TEXT,
  timestamp TEXT,
  user_id TEXT,
  employee_id TEXT,
  username TEXT,
  email TEXT,
  event_type TEXT,
  result TEXT,
  details TEXT,
  imported_at TEXT DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX IF NOT EXISTS idx_agw_mfa_enrollment_audit_event_id ON agw_mfa_enrollment_audit (event_id);
CREATE INDEX IF NOT EXISTS idx_agw_mfa_enrollment_audit_employee_id ON agw_mfa_enrollment_audit (employee_id);
CREATE INDEX IF NOT EXISTS idx_agw_mfa_enrollment_audit_user_id ON agw_mfa_enrollment_audit (user_id);

-- Source sheet: Access_Policies
CREATE TABLE IF NOT EXISTS agw_access_policies (
  _id INTEGER PRIMARY KEY AUTOINCREMENT,
  original_row_number INTEGER,
  role TEXT,
  module TEXT,
  can_view INTEGER,
  can_edit INTEGER,
  can_delete INTEGER,
  description TEXT,
  updated_at TEXT,
  imported_at TEXT DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX IF NOT EXISTS idx_agw_access_policies_updated_at ON agw_access_policies (updated_at);

-- Source sheet: Metadata_Config
CREATE TABLE IF NOT EXISTS agw_metadata_config (
  _id INTEGER PRIMARY KEY AUTOINCREMENT,
  original_row_number INTEGER,
  config_key TEXT,
  config_value TEXT,
  description TEXT,
  data_type TEXT,
  active INTEGER,
  updated_at TEXT,
  imported_at TEXT DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX IF NOT EXISTS idx_agw_metadata_config_config_key ON agw_metadata_config (config_key);
CREATE INDEX IF NOT EXISTS idx_agw_metadata_config_updated_at ON agw_metadata_config (updated_at);

-- Source sheet: Metadata_Modules
CREATE TABLE IF NOT EXISTS agw_metadata_modules (
  _id INTEGER PRIMARY KEY AUTOINCREMENT,
  original_row_number INTEGER,
  module_key TEXT,
  module_name TEXT,
  parent_key TEXT,
  category TEXT,
  sort_order INTEGER,
  default_visibility TEXT,
  default_access_scope TEXT,
  active INTEGER,
  description TEXT,
  created_at TEXT,
  updated_at TEXT,
  imported_at TEXT DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX IF NOT EXISTS idx_agw_metadata_modules_created_at ON agw_metadata_modules (created_at);
CREATE INDEX IF NOT EXISTS idx_agw_metadata_modules_updated_at ON agw_metadata_modules (updated_at);

-- Source sheet: Metadata_Dropdowns
CREATE TABLE IF NOT EXISTS agw_metadata_dropdowns (
  _id INTEGER PRIMARY KEY AUTOINCREMENT,
  original_row_number INTEGER,
  dropdown_key TEXT,
  value_value TEXT,
  label TEXT,
  active INTEGER,
  sort_order INTEGER,
  description TEXT,
  created_at TEXT,
  updated_at TEXT,
  imported_at TEXT DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX IF NOT EXISTS idx_agw_metadata_dropdowns_created_at ON agw_metadata_dropdowns (created_at);
CREATE INDEX IF NOT EXISTS idx_agw_metadata_dropdowns_updated_at ON agw_metadata_dropdowns (updated_at);

-- Source sheet: Metadata_Access_Scopes
CREATE TABLE IF NOT EXISTS agw_metadata_access_scopes (
  _id INTEGER PRIMARY KEY AUTOINCREMENT,
  original_row_number INTEGER,
  scope_key TEXT,
  scope_name TEXT,
  description TEXT,
  includes_hierarchy TEXT,
  active INTEGER,
  sort_order INTEGER,
  created_at TEXT,
  updated_at TEXT,
  imported_at TEXT DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX IF NOT EXISTS idx_agw_metadata_access_scopes_created_at ON agw_metadata_access_scopes (created_at);
CREATE INDEX IF NOT EXISTS idx_agw_metadata_access_scopes_updated_at ON agw_metadata_access_scopes (updated_at);

-- Source sheet: Metadata_Audit_Log
CREATE TABLE IF NOT EXISTS agw_metadata_audit_log (
  _id INTEGER PRIMARY KEY AUTOINCREMENT,
  original_row_number INTEGER,
  audit_id TEXT,
  timestamp TEXT,
  actor_user_id TEXT,
  actor_employee_id TEXT,
  actor_name TEXT,
  actor_role TEXT,
  action TEXT,
  metadata_table TEXT,
  metadata_key TEXT,
  old_value TEXT,
  new_value TEXT,
  before_summary TEXT,
  after_summary TEXT,
  changed_fields TEXT,
  details TEXT,
  imported_at TEXT DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX IF NOT EXISTS idx_agw_metadata_audit_log_audit_id ON agw_metadata_audit_log (audit_id);

-- Source sheet: Employee_Requests
CREATE TABLE IF NOT EXISTS agw_employee_requests (
  _id INTEGER PRIMARY KEY AUTOINCREMENT,
  original_row_number INTEGER,
  request_id TEXT,
  request_number TEXT,
  employee_id TEXT,
  request_type_id TEXT,
  request_type TEXT,
  request_title TEXT,
  request_details TEXT,
  request_purpose TEXT,
  needed_by_date TEXT,
  delivery_method TEXT,
  requested_changes TEXT,
  requested_before TEXT,
  applied_at TEXT,
  correction_notes TEXT,
  request_status TEXT,
  priority TEXT,
  submitted_at TEXT,
  submitted_by TEXT,
  assigned_to TEXT,
  hr_remarks TEXT,
  completion_notes TEXT,
  approved_by TEXT,
  approved_at TEXT,
  completed_at TEXT,
  closed_at TEXT,
  last_updated_by TEXT,
  created_at TEXT,
  updated_at TEXT,
  imported_at TEXT DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX IF NOT EXISTS idx_agw_employee_requests_request_id ON agw_employee_requests (request_id);
CREATE INDEX IF NOT EXISTS idx_agw_employee_requests_employee_id ON agw_employee_requests (employee_id);
CREATE INDEX IF NOT EXISTS idx_agw_employee_requests_created_at ON agw_employee_requests (created_at);
CREATE INDEX IF NOT EXISTS idx_agw_employee_requests_updated_at ON agw_employee_requests (updated_at);

-- Source sheet: Request_Types
CREATE TABLE IF NOT EXISTS agw_request_types (
  _id INTEGER PRIMARY KEY AUTOINCREMENT,
  original_row_number INTEGER,
  request_type_id TEXT,
  request_type_name TEXT,
  category TEXT,
  description TEXT,
  default_priority TEXT,
  sla_days INTEGER,
  requires_hr_approval INTEGER,
  active INTEGER,
  sort_order INTEGER,
  created_at TEXT,
  updated_at TEXT,
  imported_at TEXT DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX IF NOT EXISTS idx_agw_request_types_request_type_id ON agw_request_types (request_type_id);
CREATE INDEX IF NOT EXISTS idx_agw_request_types_created_at ON agw_request_types (created_at);
CREATE INDEX IF NOT EXISTS idx_agw_request_types_updated_at ON agw_request_types (updated_at);

-- Source sheet: Request_Audit_Log
CREATE TABLE IF NOT EXISTS agw_request_audit_log (
  _id INTEGER PRIMARY KEY AUTOINCREMENT,
  original_row_number INTEGER,
  request_audit_id TEXT,
  timestamp TEXT,
  request_id TEXT,
  request_number TEXT,
  actor_user_id TEXT,
  actor_employee_id TEXT,
  actor_name TEXT,
  actor_role TEXT,
  action TEXT,
  old_status TEXT,
  new_status TEXT,
  details TEXT,
  imported_at TEXT DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX IF NOT EXISTS idx_agw_request_audit_log_request_audit_id ON agw_request_audit_log (request_audit_id);
CREATE INDEX IF NOT EXISTS idx_agw_request_audit_log_request_id ON agw_request_audit_log (request_id);

-- Source sheet: AGW_AGenTel_Paid_Time_Import_Runs
CREATE TABLE IF NOT EXISTS agw_agw_agen_tel_paid_time_import_runs (
  _id INTEGER PRIMARY KEY AUTOINCREMENT,
  original_row_number INTEGER,
  run_id TEXT,
  run_type TEXT,
  source_mode TEXT,
  source_spreadsheet_id TEXT,
  source_sheet_name TEXT,
  period_start TEXT,
  period_end TEXT,
  employee_filter TEXT,
  total_source_rows REAL,
  eligible_rows TEXT,
  imported_rows TEXT,
  duplicate_rows TEXT,
  skipped_rows TEXT,
  paid_hours REAL,
  status TEXT,
  message TEXT,
  created_by TEXT,
  created_at TEXT,
  updated_at TEXT,
  imported_at TEXT DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX IF NOT EXISTS idx_agw_agw_agen_tel_paid_time_import_runs_run_id ON agw_agw_agen_tel_paid_time_import_runs (run_id);
CREATE INDEX IF NOT EXISTS idx_agw_agw_agen_tel_paid_time_import_runs_status ON agw_agw_agen_tel_paid_time_import_runs (status);
CREATE INDEX IF NOT EXISTS idx_agw_agw_agen_tel_paid_time_import_runs_created_at ON agw_agw_agen_tel_paid_time_import_runs (created_at);
CREATE INDEX IF NOT EXISTS idx_agw_agw_agen_tel_paid_time_import_runs_updated_at ON agw_agw_agen_tel_paid_time_import_runs (updated_at);

-- Source sheet: AGW_AGenTel_Paid_Time_Import_Items
CREATE TABLE IF NOT EXISTS agw_agw_agen_tel_paid_time_import_items (
  _id INTEGER PRIMARY KEY AUTOINCREMENT,
  original_row_number INTEGER,
  item_id TEXT,
  run_id TEXT,
  source_record_id TEXT,
  employee_id TEXT,
  username TEXT,
  date TEXT,
  start_time TEXT,
  end_time TEXT,
  type TEXT,
  duration_hours REAL,
  paid_flag TEXT,
  productive_flag TEXT,
  import_status TEXT,
  reason TEXT,
  aux_id TEXT,
  raw_json TEXT,
  created_at TEXT,
  imported_at TEXT DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX IF NOT EXISTS idx_agw_agw_agen_tel_paid_time_import_items_item_id ON agw_agw_agen_tel_paid_time_import_items (item_id);
CREATE INDEX IF NOT EXISTS idx_agw_agw_agen_tel_paid_time_import_items_employee_id ON agw_agw_agen_tel_paid_time_import_items (employee_id);
CREATE INDEX IF NOT EXISTS idx_agw_agw_agen_tel_paid_time_import_items_date ON agw_agw_agen_tel_paid_time_import_items (date);
CREATE INDEX IF NOT EXISTS idx_agw_agw_agen_tel_paid_time_import_items_created_at ON agw_agw_agen_tel_paid_time_import_items (created_at);
CREATE INDEX IF NOT EXISTS idx_agw_agw_agen_tel_paid_time_import_items_run_id ON agw_agw_agen_tel_paid_time_import_items (run_id);

-- Source sheet: AGW_Time_Payroll_Reconciliation_Runs
CREATE TABLE IF NOT EXISTS agw_agw_time_payroll_reconciliation_runs (
  _id INTEGER PRIMARY KEY AUTOINCREMENT,
  original_row_number INTEGER,
  run_id TEXT,
  run_type TEXT,
  period_start TEXT,
  period_end TEXT,
  employee_filter TEXT,
  employee_count INTEGER,
  row_count INTEGER,
  ok_count INTEGER,
  warning_count INTEGER,
  critical_count INTEGER,
  total_scheduled_hours REAL,
  total_attendance_hours REAL,
  total_agen_tel_paid_hours REAL,
  total_leave_hours REAL,
  total_raw_paid_hours REAL,
  total_payroll_paid_hours REAL,
  total_variance_hours REAL,
  status TEXT,
  message TEXT,
  created_by TEXT,
  created_at TEXT,
  updated_at TEXT,
  imported_at TEXT DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX IF NOT EXISTS idx_agw_agw_time_payroll_reconciliation_runs_run_id ON agw_agw_time_payroll_reconciliation_runs (run_id);
CREATE INDEX IF NOT EXISTS idx_agw_agw_time_payroll_reconciliation_runs_status ON agw_agw_time_payroll_reconciliation_runs (status);
CREATE INDEX IF NOT EXISTS idx_agw_agw_time_payroll_reconciliation_runs_created_at ON agw_agw_time_payroll_reconciliation_runs (created_at);
CREATE INDEX IF NOT EXISTS idx_agw_agw_time_payroll_reconciliation_runs_updated_at ON agw_agw_time_payroll_reconciliation_runs (updated_at);

-- Source sheet: AGW_Time_Payroll_Reconciliation_Items
CREATE TABLE IF NOT EXISTS agw_agw_time_payroll_reconciliation_items (
  _id INTEGER PRIMARY KEY AUTOINCREMENT,
  original_row_number INTEGER,
  item_id TEXT,
  run_id TEXT,
  employee_id TEXT,
  employee_name TEXT,
  username TEXT,
  client_name TEXT,
  client_lob TEXT,
  client_sub_lob TEXT,
  period_start TEXT,
  period_end TEXT,
  scheduled_hours REAL,
  attendance_hours REAL,
  agen_tel_paid_hours REAL,
  leave_hours REAL,
  raw_paid_hours REAL,
  time_summary_paid_hours REAL,
  time_summary_unpaid_hours REAL,
  payroll_export_paid_hours REAL,
  payroll_paid_hours REAL,
  variance_hours REAL,
  open_clock_count INTEGER,
  missing_time_summary TEXT,
  missing_payroll_batch TEXT,
  status TEXT,
  recommendation TEXT,
  created_at TEXT,
  imported_at TEXT DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX IF NOT EXISTS idx_agw_agw_time_payroll_reconciliation_items_item_id ON agw_agw_time_payroll_reconciliation_items (item_id);
CREATE INDEX IF NOT EXISTS idx_agw_agw_time_payroll_reconciliation_items_employee_id ON agw_agw_time_payroll_reconciliation_items (employee_id);
CREATE INDEX IF NOT EXISTS idx_agw_agw_time_payroll_reconciliation_items_status ON agw_agw_time_payroll_reconciliation_items (status);
CREATE INDEX IF NOT EXISTS idx_agw_agw_time_payroll_reconciliation_items_created_at ON agw_agw_time_payroll_reconciliation_items (created_at);
CREATE INDEX IF NOT EXISTS idx_agw_agw_time_payroll_reconciliation_items_run_id ON agw_agw_time_payroll_reconciliation_items (run_id);

-- Source sheet: AGW_AGenPay_Batch_Handoff_Runs
CREATE TABLE IF NOT EXISTS agw_agw_agen_pay_batch_handoff_runs (
  _id INTEGER PRIMARY KEY AUTOINCREMENT,
  original_row_number INTEGER,
  run_id TEXT,
  run_type TEXT,
  batch_count INTEGER,
  item_count INTEGER,
  total_payroll_paid_hours REAL,
  total_payroll_unpaid_hours REAL,
  total_taxable_allowance REAL,
  total_non_taxable_allowance REAL,
  total_deductions REAL,
  target_mode TEXT,
  target_spreadsheet_id TEXT,
  target_sheet_name TEXT,
  handoff_status TEXT,
  message TEXT,
  created_by TEXT,
  created_at TEXT,
  updated_at TEXT,
  imported_at TEXT DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX IF NOT EXISTS idx_agw_agw_agen_pay_batch_handoff_runs_run_id ON agw_agw_agen_pay_batch_handoff_runs (run_id);
CREATE INDEX IF NOT EXISTS idx_agw_agw_agen_pay_batch_handoff_runs_created_at ON agw_agw_agen_pay_batch_handoff_runs (created_at);
CREATE INDEX IF NOT EXISTS idx_agw_agw_agen_pay_batch_handoff_runs_updated_at ON agw_agw_agen_pay_batch_handoff_runs (updated_at);

-- Source sheet: AGW_AGenPay_Batch_Handoff_Items
CREATE TABLE IF NOT EXISTS agw_agw_agen_pay_batch_handoff_items (
  _id INTEGER PRIMARY KEY AUTOINCREMENT,
  original_row_number INTEGER,
  item_id TEXT,
  run_id TEXT,
  batch_id TEXT,
  batch_number TEXT,
  summary_id TEXT,
  employee_id TEXT,
  employee_name TEXT,
  period_start TEXT,
  period_end TEXT,
  workweek_hours REAL,
  regular_hours REAL,
  overtime_hours REAL,
  payroll_paid_hours REAL,
  payroll_unpaid_hours REAL,
  base_salary REAL,
  pay_frequency TEXT,
  client_name TEXT,
  client_lob TEXT,
  client_sub_lob TEXT,
  taxable_allowance_total REAL,
  non_taxable_allowance_total REAL,
  deduction_total REAL,
  handoff_status TEXT,
  target_system TEXT,
  target_record_id TEXT,
  raw_json TEXT,
  created_at TEXT,
  return_status TEXT,
  return_run_id TEXT,
  return_at TEXT,
  agen_pay_result_status TEXT,
  agen_pay_payroll_run_id TEXT,
  agen_pay_payslip_status TEXT,
  agen_pay_payment_status TEXT,
  agen_pay_disbursement_status TEXT,
  agen_pay_exception_message TEXT,
  imported_at TEXT DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX IF NOT EXISTS idx_agw_agw_agen_pay_batch_handoff_items_item_id ON agw_agw_agen_pay_batch_handoff_items (item_id);
CREATE INDEX IF NOT EXISTS idx_agw_agw_agen_pay_batch_handoff_items_employee_id ON agw_agw_agen_pay_batch_handoff_items (employee_id);
CREATE INDEX IF NOT EXISTS idx_agw_agw_agen_pay_batch_handoff_items_created_at ON agw_agw_agen_pay_batch_handoff_items (created_at);
CREATE INDEX IF NOT EXISTS idx_agw_agw_agen_pay_batch_handoff_items_batch_id ON agw_agw_agen_pay_batch_handoff_items (batch_id);
CREATE INDEX IF NOT EXISTS idx_agw_agw_agen_pay_batch_handoff_items_run_id ON agw_agw_agen_pay_batch_handoff_items (run_id);

-- Source sheet: AGW_AGenPay_Status_Return_Runs
CREATE TABLE IF NOT EXISTS agw_agw_agen_pay_status_return_runs (
  _id INTEGER PRIMARY KEY AUTOINCREMENT,
  original_row_number INTEGER,
  run_id TEXT,
  run_type TEXT,
  source_mode TEXT,
  source_spreadsheet_id TEXT,
  source_sheet_name TEXT,
  source_row_count INTEGER,
  matched_count INTEGER,
  unmatched_count INTEGER,
  updated_batch_count INTEGER,
  completed_count INTEGER,
  exception_count INTEGER,
  return_status TEXT,
  message TEXT,
  created_by TEXT,
  created_at TEXT,
  updated_at TEXT,
  imported_at TEXT DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX IF NOT EXISTS idx_agw_agw_agen_pay_status_return_runs_run_id ON agw_agw_agen_pay_status_return_runs (run_id);
CREATE INDEX IF NOT EXISTS idx_agw_agw_agen_pay_status_return_runs_created_at ON agw_agw_agen_pay_status_return_runs (created_at);
CREATE INDEX IF NOT EXISTS idx_agw_agw_agen_pay_status_return_runs_updated_at ON agw_agw_agen_pay_status_return_runs (updated_at);

-- Source sheet: AGW_AGenPay_Status_Return_Items
CREATE TABLE IF NOT EXISTS agw_agw_agen_pay_status_return_items (
  _id INTEGER PRIMARY KEY AUTOINCREMENT,
  original_row_number INTEGER,
  item_id TEXT,
  run_id TEXT,
  source_row_number TEXT,
  batch_id TEXT,
  batch_number TEXT,
  agen_pay_handoff_run_id TEXT,
  agen_pay_target_record_id TEXT,
  payroll_run_id TEXT,
  result_status TEXT,
  payslip_status TEXT,
  payment_status TEXT,
  disbursement_status TEXT,
  exception_count INTEGER,
  exception_message TEXT,
  processed_at TEXT,
  released_at TEXT,
  match_status TEXT,
  update_action TEXT,
  raw_json TEXT,
  created_at TEXT,
  imported_at TEXT DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX IF NOT EXISTS idx_agw_agw_agen_pay_status_return_items_item_id ON agw_agw_agen_pay_status_return_items (item_id);
CREATE INDEX IF NOT EXISTS idx_agw_agw_agen_pay_status_return_items_created_at ON agw_agw_agen_pay_status_return_items (created_at);
CREATE INDEX IF NOT EXISTS idx_agw_agw_agen_pay_status_return_items_batch_id ON agw_agw_agen_pay_status_return_items (batch_id);
CREATE INDEX IF NOT EXISTS idx_agw_agw_agen_pay_status_return_items_run_id ON agw_agw_agen_pay_status_return_items (run_id);

-- Source sheet: AGW_AGenCore_Identity_Sync_Runs
CREATE TABLE IF NOT EXISTS agw_agw_agen_core_identity_sync_runs (
  _id INTEGER PRIMARY KEY AUTOINCREMENT,
  original_row_number INTEGER,
  run_id TEXT,
  run_type TEXT,
  target_mode TEXT,
  target_spreadsheet_id TEXT,
  target_sheet_name TEXT,
  candidate_count INTEGER,
  ready_count INTEGER,
  warning_count INTEGER,
  blocked_count INTEGER,
  queued_count INTEGER,
  pushed_count INTEGER,
  sync_status TEXT,
  message TEXT,
  created_by TEXT,
  created_at TEXT,
  updated_at TEXT,
  imported_at TEXT DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX IF NOT EXISTS idx_agw_agw_agen_core_identity_sync_runs_run_id ON agw_agw_agen_core_identity_sync_runs (run_id);
CREATE INDEX IF NOT EXISTS idx_agw_agw_agen_core_identity_sync_runs_created_at ON agw_agw_agen_core_identity_sync_runs (created_at);
CREATE INDEX IF NOT EXISTS idx_agw_agw_agen_core_identity_sync_runs_updated_at ON agw_agw_agen_core_identity_sync_runs (updated_at);

-- Source sheet: AGW_AGenCore_Identity_Sync_Items
CREATE TABLE IF NOT EXISTS agw_agw_agen_core_identity_sync_items (
  _id INTEGER PRIMARY KEY AUTOINCREMENT,
  original_row_number INTEGER,
  item_id TEXT,
  run_id TEXT,
  employee_id TEXT,
  user_id TEXT,
  username TEXT,
  email TEXT,
  role TEXT,
  employee_name TEXT,
  employment_status TEXT,
  profile_status TEXT,
  department_id TEXT,
  team_id TEXT,
  client_name TEXT,
  client_lob TEXT,
  client_sub_lob TEXT,
  agen_core_identity_id TEXT,
  readiness_status TEXT,
  readiness_issues TEXT,
  sync_action TEXT,
  target_status TEXT,
  target_record_id TEXT,
  payload_json TEXT,
  created_at TEXT,
  updated_at TEXT,
  imported_at TEXT DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX IF NOT EXISTS idx_agw_agw_agen_core_identity_sync_items_item_id ON agw_agw_agen_core_identity_sync_items (item_id);
CREATE INDEX IF NOT EXISTS idx_agw_agw_agen_core_identity_sync_items_employee_id ON agw_agw_agen_core_identity_sync_items (employee_id);
CREATE INDEX IF NOT EXISTS idx_agw_agw_agen_core_identity_sync_items_user_id ON agw_agw_agen_core_identity_sync_items (user_id);
CREATE INDEX IF NOT EXISTS idx_agw_agw_agen_core_identity_sync_items_created_at ON agw_agw_agen_core_identity_sync_items (created_at);
CREATE INDEX IF NOT EXISTS idx_agw_agw_agen_core_identity_sync_items_updated_at ON agw_agw_agen_core_identity_sync_items (updated_at);

-- Source sheet: AGW_AGenCore_Identity_Outbox
CREATE TABLE IF NOT EXISTS agw_agw_agen_core_identity_outbox (
  _id INTEGER PRIMARY KEY AUTOINCREMENT,
  original_row_number INTEGER,
  outbox_id TEXT,
  run_id TEXT,
  item_id TEXT,
  event_type TEXT,
  source_module TEXT,
  employee_id TEXT,
  user_id TEXT,
  username TEXT,
  email TEXT,
  role TEXT,
  payload_json TEXT,
  queue_status TEXT,
  target_spreadsheet_id TEXT,
  target_sheet_name TEXT,
  target_record_id TEXT,
  error_message TEXT,
  created_at TEXT,
  updated_at TEXT,
  pushed_at TEXT,
  imported_at TEXT DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX IF NOT EXISTS idx_agw_agw_agen_core_identity_outbox_outbox_id ON agw_agw_agen_core_identity_outbox (outbox_id);
CREATE INDEX IF NOT EXISTS idx_agw_agw_agen_core_identity_outbox_employee_id ON agw_agw_agen_core_identity_outbox (employee_id);
CREATE INDEX IF NOT EXISTS idx_agw_agw_agen_core_identity_outbox_user_id ON agw_agw_agen_core_identity_outbox (user_id);
CREATE INDEX IF NOT EXISTS idx_agw_agw_agen_core_identity_outbox_created_at ON agw_agw_agen_core_identity_outbox (created_at);
CREATE INDEX IF NOT EXISTS idx_agw_agw_agen_core_identity_outbox_updated_at ON agw_agw_agen_core_identity_outbox (updated_at);