-- Saral Portal unified schema + demo data
CREATE DATABASE IF NOT EXISTS `saral_portal_demo` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE `saral_portal_demo`;





CREATE TABLE `business_settings` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `business_id` int(11) NOT NULL,
  `setting_key` varchar(120) NOT NULL,
  `setting_value` varchar(255) NOT NULL,
  `updated_at` datetime NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_bs_key` (`business_id`,`setting_key`),
  KEY `idx_bs_business` (`business_id`),
  CONSTRAINT `fk_bs_business` FOREIGN KEY (`business_id`) REFERENCES `businesses` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `modules` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `code` varchar(40) NOT NULL,
  `name` varchar(80) NOT NULL,
  `icon` varchar(60) DEFAULT NULL,
  `sort_order` int(11) NOT NULL DEFAULT 0,
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_module_code` (`code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `business_modules` (
  `business_id` int(11) NOT NULL,
  `module_id` int(11) NOT NULL,
  `enabled` tinyint(1) NOT NULL DEFAULT 1,
  `enabled_source` enum('subscription','manual') NOT NULL DEFAULT 'manual',
  `enabled_at` datetime NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`business_id`,`module_id`),
  KEY `idx_bm_module` (`module_id`),
  CONSTRAINT `fk_bm_business` FOREIGN KEY (`business_id`) REFERENCES `businesses` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_bm_module` FOREIGN KEY (`module_id`) REFERENCES `modules` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `subscriptions` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `business_id` int(11) NOT NULL,
  `module_id` int(11) NOT NULL,
  `billing_cycle` enum('weekly','monthly') NOT NULL,
  `price` decimal(10,2) NOT NULL DEFAULT 0.00,
  `status` enum('active','past_due','cancelled') NOT NULL DEFAULT 'active',
  `start_at` date NOT NULL,
  `end_at` date DEFAULT NULL,
  `created_by_user_id` int(11) DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_sub_business` (`business_id`),
  KEY `idx_sub_module` (`module_id`),
  CONSTRAINT `fk_sub_business` FOREIGN KEY (`business_id`) REFERENCES `businesses` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_sub_module` FOREIGN KEY (`module_id`) REFERENCES `modules` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `departments` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `business_id` int(11) NOT NULL,
  `name` varchar(100) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_dept` (`business_id`,`name`),
  CONSTRAINT `fk_dept_business` FOREIGN KEY (`business_id`) REFERENCES `businesses` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `staff_roles` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `business_id` int(11) NOT NULL,
  `role_name` varchar(80) NOT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_staff_role` (`business_id`,`role_name`),
  CONSTRAINT `fk_staff_roles_business` FOREIGN KEY (`business_id`) REFERENCES `businesses` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `staff_profile` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `business_id` int(11) NOT NULL,
  `full_name` varchar(120) NOT NULL,
  `short_name` varchar(120) DEFAULT NULL,
  `role_id` int(11) DEFAULT NULL,
  `hourly_rate` decimal(10,2) DEFAULT NULL,
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT NULL ON UPDATE current_timestamp(),
  `department_id` int(11) DEFAULT NULL,
  `email` varchar(150) DEFAULT NULL,
  `mobile` varchar(50) DEFAULT NULL,
  `dob` date DEFAULT NULL,
  `pay_type` varchar(20) DEFAULT 'hourly',
  `address` text DEFAULT NULL,
  `visa_status` varchar(100) DEFAULT NULL,
  `visa_expiry` date DEFAULT NULL,
  `rtw_check` varchar(10) DEFAULT 'N/A',
  `rtw_check_date` date DEFAULT NULL,
  `rtw_next_check` date DEFAULT NULL,
  `joined_date` date DEFAULT NULL,
  `base_salary` decimal(10,2) DEFAULT NULL,
  `weekly_hours` decimal(5,2) DEFAULT 40.00,
  `user_id` int(11) DEFAULT NULL,
  `initials` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_staff_user` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
ALTER TABLE `staff_profile`
  ADD KEY `idx_staff_business` (`business_id`),
  ADD KEY `idx_staff_user` (`user_id`),
  ADD CONSTRAINT `fk_staff_business` FOREIGN KEY (`business_id`) REFERENCES `businesses` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `fk_staff_department` FOREIGN KEY (`department_id`) REFERENCES `departments` (`id`) ON DELETE SET NULL,
  ADD CONSTRAINT `fk_staff_jobrole` FOREIGN KEY (`role_id`) REFERENCES `staff_roles` (`id`) ON DELETE SET NULL,
  ADD CONSTRAINT `fk_staff_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL;

CREATE TABLE `permissions` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `code` varchar(120) NOT NULL,
  `module_code` varchar(40) NOT NULL,
  `label` varchar(180) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_perm_code` (`code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `roles` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `business_id` int(11) NOT NULL,
  `name` varchar(120) NOT NULL,
  `is_admin` tinyint(1) NOT NULL DEFAULT 0,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_role_name` (`business_id`,`name`),
  CONSTRAINT `fk_roles_business` FOREIGN KEY (`business_id`) REFERENCES `businesses` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `user_roles` (
  `user_id` int(11) NOT NULL,
  `role_id` int(11) NOT NULL,
  PRIMARY KEY (`user_id`,`role_id`),
  CONSTRAINT `fk_ur_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_ur_role` FOREIGN KEY (`role_id`) REFERENCES `roles` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `department_roles` (
  `department_id` int(11) NOT NULL,
  `role_id` int(11) NOT NULL,
  PRIMARY KEY (`department_id`,`role_id`),
  CONSTRAINT `fk_dr_department` FOREIGN KEY (`department_id`) REFERENCES `departments` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_dr_role` FOREIGN KEY (`role_id`) REFERENCES `roles` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `staffrole_roles` (
  `staff_role_id` int(11) NOT NULL,
  `role_id` int(11) NOT NULL,
  PRIMARY KEY (`staff_role_id`,`role_id`),
  CONSTRAINT `fk_srr_staff_role` FOREIGN KEY (`staff_role_id`) REFERENCES `staff_roles` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_srr_role` FOREIGN KEY (`role_id`) REFERENCES `roles` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `role_permissions` (
  `role_id` int(11) NOT NULL,
  `permission_id` int(11) NOT NULL,
  PRIMARY KEY (`role_id`,`permission_id`),
  CONSTRAINT `fk_rp_role` FOREIGN KEY (`role_id`) REFERENCES `roles` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_rp_perm` FOREIGN KEY (`permission_id`) REFERENCES `permissions` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `user_permissions` (
  `user_id` int(11) NOT NULL,
  `permission_id` int(11) NOT NULL,
  `effect` enum('allow','deny') NOT NULL DEFAULT 'allow',
  PRIMARY KEY (`user_id`,`permission_id`),
  CONSTRAINT `fk_up_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_up_perm` FOREIGN KEY (`permission_id`) REFERENCES `permissions` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `audit_logs` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `business_id` int(11) DEFAULT NULL,
  `user_id` int(11) DEFAULT NULL,
  `ip_address` varchar(64) DEFAULT NULL,
  `action` varchar(120) NOT NULL,
  `entity` varchar(60) DEFAULT NULL,
  `entity_id` varchar(60) DEFAULT NULL,
  `details_text` text DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_audit_business` (`business_id`),
  KEY `idx_audit_user` (`user_id`),
  CONSTRAINT `fk_audit_business` FOREIGN KEY (`business_id`) REFERENCES `businesses` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_audit_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;



-- =====================
-- Module tables
-- =====================


CREATE TABLE `expense_reasons` (
  `id` int(10) UNSIGNED NOT NULL,
  `business_id` int(11) NOT NULL,
  `name` varchar(190) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `expenses` (
  `id` int(10) UNSIGNED NOT NULL,
  `business_id` int(11) NOT NULL,
  `expense_date` date NOT NULL,
  `reason_id` int(10) UNSIGNED NOT NULL,
  `is_cash_expense` tinyint(1) NOT NULL DEFAULT 0,
  `amount` decimal(10,2) NOT NULL,
  `notes` varchar(255) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `suppliers` (
  `id` int(10) UNSIGNED NOT NULL,
  `business_id` int(11) NOT NULL,
  `name` varchar(190) NOT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `invoices` (
  `id` int(10) UNSIGNED NOT NULL,
  `business_id` int(11) NOT NULL,
  `supplier_id` int(10) UNSIGNED NOT NULL,
  `invoice_date` date NOT NULL,
  `total_amount` decimal(10,2) NOT NULL DEFAULT 0.00,
  `notes` text DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `invoice_files` (
  `id` int(10) UNSIGNED NOT NULL,
  `business_id` int(11) NOT NULL,
  `invoice_id` int(10) UNSIGNED NOT NULL,
  `file_path` varchar(255) NOT NULL,
  `original_name` varchar(190) NOT NULL,
  `mime_type` varchar(100) NOT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `invoice_deductions` (
  `id` int(10) UNSIGNED NOT NULL,
  `business_id` int(11) NOT NULL,
  `invoice_id` int(10) UNSIGNED NOT NULL,
  `deduction_date` date NOT NULL,
  `amount` decimal(10,2) NOT NULL DEFAULT 0.00,
  `reason` varchar(255) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `payments` (
  `id` int(10) UNSIGNED NOT NULL,
  `business_id` int(11) NOT NULL,
  `invoice_id` int(10) UNSIGNED NOT NULL,
  `paid_date` date NOT NULL,
  `amount` decimal(10,2) NOT NULL DEFAULT 0.00,
  `method` varchar(60) DEFAULT NULL,
  `note` varchar(255) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `expense_id` int(10) UNSIGNED DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `bank_transactions` (
  `id` int(10) UNSIGNED NOT NULL,
  `business_id` int(11) NOT NULL,
  `txn_date` date NOT NULL,
  `description` varchar(255) NOT NULL,
  `amount` decimal(10,2) NOT NULL,
  `category` enum('shop_card','online_card','deliveroo','justeat','other_card','ignore') NOT NULL,
  `txn_hash` char(40) NOT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `sales_entries` (
  `id` int(10) UNSIGNED NOT NULL,
  `business_id` int(11) NOT NULL,
  `entry_date` date NOT NULL,
  `data` text NOT NULL,
  `total_cash` decimal(10,2) NOT NULL,
  `total_card` decimal(10,2) NOT NULL,
  `total_expense` decimal(10,2) NOT NULL,
  `cash_in_hand` decimal(10,2) NOT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `sales_entry_details` (
  `id` int(10) UNSIGNED NOT NULL,
  `business_id` int(11) NOT NULL,
  `entry_id` int(10) UNSIGNED NOT NULL,
  `category` enum('cash','card','platform','expense') NOT NULL,
  `field_name` varchar(100) NOT NULL,
  `field_value` decimal(10,2) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `rota_weekly_settings` (
  `business_id` int(11) NOT NULL,
  `week_start` date NOT NULL,
  `closing_time` time NOT NULL DEFAULT '22:45:00',
  `is_published` tinyint(1) DEFAULT 0,
  `published_at` datetime DEFAULT NULL,
  `last_modified` datetime DEFAULT NULL,
  PRIMARY KEY (`business_id`, `week_start`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `staff_rota` (
  `id` bigint(20) NOT NULL,
  `business_id` int(11) NOT NULL,
  `staff_id` int(11) NOT NULL,
  `shift_date` date NOT NULL,
  `start_time` time NOT NULL,
  `end_time` time NOT NULL,
  `note` varchar(255) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `updated_at` datetime DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `staff_shifts` (
  `id` bigint(20) NOT NULL,
  `business_id` int(11) NOT NULL,
  `shift_date` date NOT NULL,
  `staff_id` int(11) NOT NULL,
  `role_id` int(11) DEFAULT NULL,
  `start_time` time NOT NULL,
  `finish_time` time NOT NULL,
  `break_minutes` int(11) NOT NULL DEFAULT 0,
  `worked_minutes` int(11) NOT NULL DEFAULT 0,
  `notes` varchar(255) DEFAULT NULL,
  `created_by_user_id` int(11) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT NULL ON UPDATE current_timestamp(),
  `hourly_rate_snapshot` decimal(10,2) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `staff_payments` (
  `id` bigint(20) NOT NULL,
  `business_id` int(11) NOT NULL,
  `staff_id` int(11) NOT NULL,
  `pay_year` smallint(6) NOT NULL,
  `pay_month` tinyint(4) NOT NULL,
  `payment_date` date NOT NULL,
  `cash_amount` decimal(10,2) NOT NULL DEFAULT 0.00,
  `bank_amount` decimal(10,2) NOT NULL DEFAULT 0.00,
  `expense_id` int(11) DEFAULT NULL,
  `notes` varchar(255) DEFAULT NULL,
  `created_by_user_id` int(11) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `staff_salary_history` (
  `id` int(11) NOT NULL,
  `business_id` int(11) NOT NULL,
  `staff_id` int(11) NOT NULL,
  `pay_type` varchar(20) NOT NULL DEFAULT 'hourly',
  `base_salary` decimal(10,2) NOT NULL DEFAULT 0.00,
  `weekly_hours` decimal(5,2) DEFAULT 0.00,
  `calculated_hourly_rate` decimal(10,2) NOT NULL,
  `effective_date` date NOT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `staff_availability` (
  `id` int(11) NOT NULL,
  `business_id` int(11) NOT NULL,
  `staff_id` int(11) NOT NULL,
  `avail_date` date NOT NULL,
  `status` enum('not_available','anytime','range') NOT NULL DEFAULT 'anytime',
  `start_time` time DEFAULT NULL,
  `end_time` time DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `updated_at` datetime NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `staff_availability_locks` (
  `id` int(11) NOT NULL,
  `business_id` int(11) NOT NULL,
  `staff_id` int(11) NOT NULL,
  `week_start` date NOT NULL,
  `share_link_id` int(11) DEFAULT NULL,
  `locked_at` datetime NOT NULL DEFAULT current_timestamp(),
  `locked_source` enum('staff','admin') NOT NULL DEFAULT 'staff',
  `locked_by_user_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `cleaning_areas` (
  `id` int(11) NOT NULL,
  `business_id` int(11) NOT NULL,
  `name` varchar(100) NOT NULL,
  `assigned_group` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `cleaning_items` (
  `id` int(11) NOT NULL,
  `business_id` int(11) NOT NULL,
  `area_id` int(11) NOT NULL,
  `item_name` varchar(100) NOT NULL,
  `frequency` varchar(20) DEFAULT NULL,
  `photo_required` tinyint(1) DEFAULT 0,
  `cleaning_procedure` text DEFAULT NULL,
  `cleaning_when` varchar(20) DEFAULT 'Anytime',
  `interval_days` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `cleaning_logs` (
  `id` int(11) NOT NULL,
  `business_id` int(11) NOT NULL,
  `item_id` int(11) DEFAULT NULL,
  `user_initials` varchar(10) DEFAULT NULL,
  `cleaned_at` datetime DEFAULT NULL,
  `photo_path` varchar(255) DEFAULT NULL,
  `user_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `equipment_types` (
  `id` int(11) NOT NULL,
  `business_id` int(11) NOT NULL,
  `name` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `equipment` (
  `id` int(11) NOT NULL,
  `business_id` int(11) NOT NULL,
  `name` varchar(100) NOT NULL,
  `type` varchar(50) DEFAULT 'Fridge',
  `is_active` tinyint(1) DEFAULT 1,
  `department` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `fridge_logs` (
  `id` int(11) NOT NULL,
  `business_id` int(11) NOT NULL,
  `equipment_id` int(11) NOT NULL,
  `user_initials` varchar(10) DEFAULT NULL,
  `temperature` decimal(4,1) DEFAULT NULL,
  `check_time` datetime DEFAULT current_timestamp(),
  `photo_path` varchar(255) DEFAULT NULL,
  `user_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `process_logs` (
  `id` int(11) NOT NULL,
  `business_id` int(11) NOT NULL,
  `process_type` enum('Cooking','Reheating','Cooling','Delivery','HotHold') DEFAULT NULL,
  `item_name` varchar(150) DEFAULT NULL,
  `supplier` varchar(150) DEFAULT NULL,
  `temp_value` decimal(4,1) DEFAULT NULL,
  `time_value` varchar(20) DEFAULT NULL,
  `start_value` varchar(20) DEFAULT NULL,
  `end_value` varchar(20) DEFAULT NULL,
  `user_initials` varchar(10) DEFAULT NULL,
  `photo_path` varchar(255) DEFAULT NULL,
  `created_at` datetime DEFAULT current_timestamp(),
  `user_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `ingredients` (
  `id` int(11) NOT NULL,
  `business_id` int(11) NOT NULL,
  `name` varchar(100) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `recipes` (
  `id` int(11) NOT NULL,
  `business_id` int(11) NOT NULL,
  `title` varchar(255) NOT NULL,
  `pre_prep` text DEFAULT NULL,
  `created_by` varchar(50) DEFAULT NULL,
  `created_at` datetime DEFAULT current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `recipe_ingredients` (
  `id` int(11) NOT NULL,
  `business_id` int(11) NOT NULL,
  `recipe_id` int(11) NOT NULL,
  `ingredient_id` int(11) NOT NULL,
  `measurement` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `recipe_steps` (
  `id` int(11) NOT NULL,
  `business_id` int(11) NOT NULL,
  `recipe_id` int(11) NOT NULL,
  `step_order` int(11) NOT NULL,
  `instruction` text DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `absences` (
  `id` int(11) NOT NULL,
  `business_id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `absence_type` enum('sick','authorised','unauthorised','other') NOT NULL DEFAULT 'sick',
  `date_from` date NOT NULL,
  `date_to` date NOT NULL,
  `total_days` decimal(5,2) NOT NULL DEFAULT 0.00,
  `reason` text DEFAULT NULL,
  `recorded_by` int(11) NOT NULL,
  `recorded_at` datetime NOT NULL DEFAULT current_timestamp(),
  `is_archived` tinyint(1) NOT NULL DEFAULT 0,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `contracts` (
  `id` int(11) NOT NULL,
  `business_id` int(11) NOT NULL,
  `employee_id` int(11) NOT NULL,
  `template_id` int(11) NOT NULL,
  `created_by` int(11) NOT NULL,
  `status` enum('draft','sent','signed','archived') NOT NULL DEFAULT 'draft',
  `current_version_id` int(11) DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `contract_versions` (
  `id` int(11) NOT NULL,
  `contract_id` int(11) NOT NULL,
  `version_no` int(11) NOT NULL,
  `data_json` mediumtext DEFAULT NULL,
  `html_render` mediumtext DEFAULT NULL,
  `docx_path` varchar(255) DEFAULT NULL,
  `signature_image_path` varchar(255) DEFAULT NULL,
  `employee_signed_at` datetime DEFAULT NULL,
  `signer_ip` varchar(45) DEFAULT NULL,
  `signer_user_agent` varchar(255) DEFAULT NULL,
  `created_by` int(11) NOT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `is_current` tinyint(1) NOT NULL DEFAULT 1,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `custom_fields` (
  `id` int(11) NOT NULL,
  `business_id` int(11) NOT NULL,
  `field_key` varchar(60) NOT NULL,
  `label` varchar(120) NOT NULL,
  `field_type` enum('text','textarea','number','date','select') NOT NULL DEFAULT 'text',
  `options_json` text DEFAULT NULL,
  `is_required` tinyint(1) NOT NULL DEFAULT 0,
  `visible_to_staff` tinyint(1) NOT NULL DEFAULT 1,
  `editable_by_staff` tinyint(1) NOT NULL DEFAULT 1,
  `is_archived` tinyint(1) NOT NULL DEFAULT 0,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `custom_field_values` (
  `id` int(11) NOT NULL,
  `business_id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `field_id` int(11) NOT NULL,
  `value_text` text DEFAULT NULL,
  `updated_by` int(11) DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `documents` (
  `id` int(11) NOT NULL,
  `business_id` int(11) NOT NULL,
  `user_id` int(11) DEFAULT NULL,
  `category` varchar(80) DEFAULT NULL,
  `doc_type` varchar(50) DEFAULT NULL,
  `expiry_date` date DEFAULT NULL,
  `remind_days` int(11) NOT NULL DEFAULT 30,
  `file_path` varchar(255) NOT NULL,
  `original_name` varchar(255) NOT NULL,
  `mime` varchar(120) DEFAULT NULL,
  `size_bytes` int(11) DEFAULT NULL,
  `created_by` int(11) NOT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `is_archived` tinyint(1) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `incidents` (
  `id` int(11) NOT NULL,
  `business_id` int(11) NOT NULL,
  `incident_type` enum('accident','near_miss','injury','other') NOT NULL DEFAULT 'accident',
  `occurred_at` datetime NOT NULL,
  `location` varchar(150) DEFAULT NULL,
  `description` text NOT NULL,
  `actions_taken` text DEFAULT NULL,
  `evidence_path` varchar(255) DEFAULT NULL,
  `reported_by` int(11) DEFAULT NULL,
  `is_archived` tinyint(1) NOT NULL DEFAULT 0,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `job_roles` (
  `id` int(11) NOT NULL,
  `business_id` int(11) DEFAULT NULL,
  `name` varchar(120) NOT NULL,
  `created_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `leave_requests` (
  `id` int(11) NOT NULL,
  `business_id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `leave_type` enum('holiday','unpaid','other') NOT NULL DEFAULT 'holiday',
  `start_date` date NOT NULL,
  `end_date` date NOT NULL,
  `start_half_day` tinyint(1) NOT NULL DEFAULT 0,
  `end_half_day` tinyint(1) NOT NULL DEFAULT 0,
  `total_days` decimal(5,2) NOT NULL DEFAULT 0.00,
  `reason` text DEFAULT NULL,
  `status` enum('pending','approved','rejected','cancelled') NOT NULL DEFAULT 'pending',
  `requested_by` int(11) NOT NULL,
  `requested_at` datetime NOT NULL DEFAULT current_timestamp(),
  `decided_by` int(11) DEFAULT NULL,
  `decided_at` datetime DEFAULT NULL,
  `decision_note` text DEFAULT NULL,
  `is_archived` tinyint(1) NOT NULL DEFAULT 0,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `notifications` (
  `id` int(11) NOT NULL,
  `business_id` int(11) NOT NULL,
  `user_id` int(11) DEFAULT NULL,
  `type` varchar(60) DEFAULT NULL,
  `title` varchar(200) NOT NULL,
  `body` text DEFAULT NULL,
  `is_read` tinyint(1) NOT NULL DEFAULT 0,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `onboarding_tasks` (
  `id` int(11) NOT NULL,
  `business_id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `task_key` varchar(60) NOT NULL,
  `title` varchar(150) NOT NULL,
  `status` enum('pending','done') NOT NULL DEFAULT 'pending',
  `completed_at` datetime DEFAULT NULL,
  `created_by` int(11) DEFAULT NULL,
  `updated_by` int(11) DEFAULT NULL,
  `is_archived` tinyint(1) NOT NULL DEFAULT 0,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `profile_change_requests` (
  `id` int(11) NOT NULL,
  `business_id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `requested_by` int(11) NOT NULL,
  `changes_json` mediumtext NOT NULL,
  `status` enum('pending','approved','rejected') NOT NULL DEFAULT 'pending',
  `reviewed_by` int(11) DEFAULT NULL,
  `reviewed_at` datetime DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `rtw_checks` (
  `id` int(11) NOT NULL,
  `business_id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `check_type` enum('online','manual','other') NOT NULL DEFAULT 'online',
  `share_code` varchar(30) DEFAULT NULL,
  `dob_used` date DEFAULT NULL,
  `check_date` date NOT NULL,
  `next_check_date` date DEFAULT NULL,
  `evidence_doc_id` int(11) DEFAULT NULL,
  `notes` text DEFAULT NULL,
  `created_by` int(11) NOT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `frequency_days` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `templates` (
  `id` int(11) NOT NULL,
  `business_id` int(11) NOT NULL,
  `name` varchar(160) NOT NULL,
  `description` text DEFAULT NULL,
  `docx_path` varchar(255) DEFAULT NULL,
  `html_content` mediumtext DEFAULT NULL,
  `fields_json` mediumtext DEFAULT NULL,
  `is_archived` tinyint(1) NOT NULL DEFAULT 0,
  `created_by` int(11) NOT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `training_records` (
  `id` int(11) NOT NULL,
  `business_id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `training_type_id` int(11) NOT NULL,
  `completed_on` date NOT NULL,
  `expires_on` date DEFAULT NULL,
  `notes` text DEFAULT NULL,
  `evidence_path` varchar(255) DEFAULT NULL,
  `status` enum('pending','approved','rejected') NOT NULL DEFAULT 'pending',
  `created_by` int(11) DEFAULT NULL,
  `approved_by` int(11) DEFAULT NULL,
  `approved_at` datetime DEFAULT NULL,
  `is_archived` tinyint(1) NOT NULL DEFAULT 0,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `training_types` (
  `id` int(11) NOT NULL,
  `business_id` int(11) NOT NULL,
  `name` varchar(120) NOT NULL,
  `description` text DEFAULT NULL,
  `default_valid_months` int(11) DEFAULT NULL,
  `is_archived` tinyint(1) NOT NULL DEFAULT 0,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE `expenses`
  ADD KEY `idx_exp_business` (`business_id`),
  ADD CONSTRAINT `fk_exp_reason` FOREIGN KEY (`reason_id`) REFERENCES `expense_reasons` (`id`) ON DELETE RESTRICT;

ALTER TABLE `invoices`
  ADD KEY `idx_inv_business` (`business_id`),
  ADD CONSTRAINT `fk_inv_supplier` FOREIGN KEY (`supplier_id`) REFERENCES `suppliers` (`id`) ON DELETE RESTRICT;

ALTER TABLE `invoice_files`
  ADD KEY `idx_invfile_business` (`business_id`),
  ADD CONSTRAINT `fk_invfile_invoice` FOREIGN KEY (`invoice_id`) REFERENCES `invoices` (`id`) ON DELETE CASCADE;

ALTER TABLE `invoice_deductions`
  ADD KEY `idx_invded_business` (`business_id`),
  ADD CONSTRAINT `fk_invded_invoice` FOREIGN KEY (`invoice_id`) REFERENCES `invoices` (`id`) ON DELETE CASCADE;

ALTER TABLE `payments`
  ADD KEY `idx_pay_business` (`business_id`),
  ADD CONSTRAINT `fk_pay_invoice` FOREIGN KEY (`invoice_id`) REFERENCES `invoices` (`id`) ON DELETE CASCADE;

ALTER TABLE `sales_entry_details`
  ADD KEY `idx_sed_business` (`business_id`),
  ADD CONSTRAINT `fk_sed_entry` FOREIGN KEY (`entry_id`) REFERENCES `sales_entries` (`id`) ON DELETE CASCADE;

ALTER TABLE `cleaning_items`
  ADD KEY `idx_ci_business` (`business_id`),
  ADD CONSTRAINT `fk_ci_area` FOREIGN KEY (`area_id`) REFERENCES `cleaning_areas` (`id`) ON DELETE CASCADE;

ALTER TABLE `cleaning_logs`
  ADD KEY `idx_cl_business` (`business_id`),
  ADD CONSTRAINT `fk_cl_item` FOREIGN KEY (`item_id`) REFERENCES `cleaning_items` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `fk_cl_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL;

ALTER TABLE `fridge_logs`
  ADD KEY `idx_fl_business` (`business_id`),
  ADD CONSTRAINT `fk_fl_equipment` FOREIGN KEY (`equipment_id`) REFERENCES `equipment` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `fk_fl_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL;

ALTER TABLE `staff_rota`
  ADD KEY `idx_sr_business` (`business_id`),
  ADD CONSTRAINT `fk_sr_staff` FOREIGN KEY (`staff_id`) REFERENCES `staff_profile` (`id`) ON DELETE CASCADE;

ALTER TABLE `staff_shifts`
  ADD KEY `idx_ss_business` (`business_id`),
  ADD CONSTRAINT `fk_ss_staff` FOREIGN KEY (`staff_id`) REFERENCES `staff_profile` (`id`) ON DELETE CASCADE;

ALTER TABLE `staff_payments`
  ADD KEY `idx_sp_business` (`business_id`),
  ADD CONSTRAINT `fk_sp_staff` FOREIGN KEY (`staff_id`) REFERENCES `staff_profile` (`id`) ON DELETE CASCADE;

ALTER TABLE `leave_requests`
  ADD KEY `idx_lr_business` (`business_id`),
  ADD CONSTRAINT `fk_lr_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE;

ALTER TABLE `absences`
  ADD KEY `idx_abs_business` (`business_id`),
  ADD CONSTRAINT `fk_abs_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE;




INSERT INTO `modules` (`id`,`code`,`name`,`icon`,`sort_order`,`is_active`) VALUES
(1,'income','Income Management','cash-register',1,1),
(2,'staff','Staff Management','calendar',2,1),
(3,'accounts','Accounts','file-text',3,1),
(4,'hr','HR Management','id-card',4,1),
(5,'logs','Logs','clipboard-list',5,1),
(6,'reports','Reports','bar-chart',6,1);

INSERT INTO `businesses` (`id`,`legal_name`,`trade_name`,`slug`,`email`,`phone`,`website`,`status`,`is_active`,`staff_login_mode`,`staff_link_key`,`pin_min`,`pin_max`) VALUES
(1,'Nandu Foods Ltd','Fired Dough','fired-dough','demo@fired-dough.example','0131 000 0000','https://example.com','active',1,'pin','02e4dcb7b571e3cf168ce6d7f0a18b41b9d6b4eb',4,8);

INSERT INTO `business_modules` (`business_id`,`module_id`,`enabled`,`enabled_source`) VALUES
(1,1,1,'manual'),
(1,2,1,'manual'),
(1,3,1,'manual'),
(1,4,1,'manual'),
(1,5,1,'manual'),
(1,6,1,'manual');
INSERT INTO business_settings (business_id,setting_key,setting_value) VALUES(1,'name_format.rota','short'),(1,'name_format.timesheet','short'),(1,'name_format.logs','initials');

INSERT INTO `departments` (`id`,`business_id`,`name`) VALUES
(1,1,'Kitchen'),
(2,1,'Front of House');

INSERT INTO `staff_roles` (`id`,`business_id`,`role_name`) VALUES
(1,1,'Chef'),
(2,1,'Waiting Staff');

INSERT INTO `staff_profile` (`id`,`business_id`,`full_name`,`short_name`,`role_id`,`hourly_rate`,`department_id`,`is_active`,`created_at`,`user_id`,`initials`) VALUES
(1,1,'Demo Chef','Chef',1,14.50,1,1,CURRENT_TIMESTAMP,10,'DC'),
(2,1,'Demo Server','Server',2,12.00,2,1,CURRENT_TIMESTAMP,11,'DS');

INSERT INTO `permissions` (`id`,`code`,`module_code`,`label`) VALUES
(1,'module.income.access','income','Access Income'),
(2,'module.staff.access','staff','Access Staff Management'),
(3,'module.accounts.access','accounts','Access Accounts'),
(4,'module.hr.access','hr','Access HR'),
(5,'module.logs.access','logs','Access Logs'),
(6,'module.reports.access','reports','Access Reports'),
(7,'sales.entry.create','income','Create Sales Entries'),
(8,'sales.entry.view','income','View Sales Entries'),
(9,'rota.view','staff','View Rota'),
(10,'rota.edit','staff','Edit Rota'),
(11,'expenses.create','accounts','Create Expenses'),
(12,'expenses.view','accounts','View Expenses'),
(13,'invoices.create','accounts','Create Invoices'),
(14,'invoices.view','accounts','View Invoices'),
(15,'logs.cleaning.record','logs','Record Cleaning Logs'),
(16,'logs.cleaning.view','logs','View Cleaning Logs'),
(17,'logs.temps.record','logs','Record Temperature Logs'),
(18,'logs.temps.view','logs','View Temperature Logs'),
(19,'hr.leave.request','hr','Request Leave'),
(20,'hr.leave.manage','hr','Manage Leave');

INSERT INTO `roles` (`id`,`business_id`,`name`,`is_admin`) VALUES
(1,1,'Business Admin',1),
(2,1,'Manager',0),
(3,1,'Staff',0);

-- Map kitchen/FOH staff to the Staff RBAC role via department and job role
INSERT INTO `department_roles` (`department_id`,`role_id`) VALUES
(1,3),
(2,3);

INSERT INTO `staffrole_roles` (`staff_role_id`,`role_id`) VALUES
(1,3),
(2,3);

INSERT INTO `user_roles` (`user_id`,`role_id`) VALUES
(2,1),
(10,3),
(11,3);

INSERT INTO `role_permissions` (`role_id`,`permission_id`) VALUES
(1,1),
(1,2),
(1,3),
(1,4),
(1,5),
(1,6),
(1,7),
(1,8),
(1,9),
(1,10),
(1,11),
(1,12),
(1,13),
(1,14),
(1,15),
(1,16),
(1,17),
(1,18),
(1,19),
(1,20),
(2,1),
(2,2),
(2,3),
(2,4),
(2,5),
(2,6),
(2,7),
(2,8),
(2,9),
(2,10),
(2,11),
(2,12),
(2,13),
(2,14),
(2,15),
(2,16),
(2,17),
(2,18),
(2,19),
(2,20),
(3,5),
(3,15),
(3,16),
(3,17),
(3,18),
(3,19);

INSERT INTO `sales_entries` (`id`,`business_id`,`entry_date`,`data`,`total_cash`,`total_card`,`total_expense`,`cash_in_hand`,`created_at`) VALUES
(1,1,'2026-01-22','{"notes":"demo"}',120.00,380.00,45.00,75.00,CURRENT_TIMESTAMP);

INSERT INTO `sales_entry_details` (`id`,`business_id`,`entry_id`,`category`,`field_name`,`field_value`) VALUES
(1,1,1,'cash','Cash sales',120.00),
(2,1,1,'card','Card sales',380.00);

INSERT INTO `expense_reasons` (`id`,`business_id`,`name`) VALUES
(1,1,'Supplies'),
(2,1,'Delivery'),
(3,1,'Staff Meal');

INSERT INTO `expenses` (`id`,`business_id`,`expense_date`,`reason_id`,`is_cash_expense`,`amount`,`notes`,`created_at`) VALUES
(1,1,'2026-01-22',1,1,45.00,'Demo expense',CURRENT_TIMESTAMP);

INSERT INTO `suppliers` (`id`,`business_id`,`name`,`created_at`) VALUES
(1,1,'Demo Supplier',CURRENT_TIMESTAMP);

INSERT INTO `invoices` (`id`,`business_id`,`supplier_id`,`invoice_date`,`total_amount`,`notes`,`created_at`) VALUES
(1,1,1,'2026-01-22',200.00,'Demo invoice',CURRENT_TIMESTAMP);

INSERT INTO `payments` (`id`,`business_id`,`invoice_id`,`paid_date`,`amount`,`method`,`note`,`created_at`,`expense_id`) VALUES
(1,1,1,'2026-01-23',200.00,'bank','Demo payment',CURRENT_TIMESTAMP,NULL);

INSERT INTO `rota_weekly_settings` (`business_id`,`week_start`,`closing_time`,`is_published`,`published_at`,`last_modified`) VALUES
(1,'2026-01-19','22:45:00',1,CURRENT_TIMESTAMP,CURRENT_TIMESTAMP);

INSERT INTO `staff_rota` (`id`,`business_id`,`staff_id`,`shift_date`,`start_time`,`end_time`,`note`,`created_at`,`updated_at`) VALUES
(1,1,1,'2026-01-23','16:00:00','22:00:00','Demo rota',CURRENT_TIMESTAMP,CURRENT_TIMESTAMP);

INSERT INTO `cleaning_areas` (`id`,`business_id`,`name`,`assigned_group`) VALUES
(1,1,'Kitchen','Kitchen');

INSERT INTO `cleaning_items` (`id`,`business_id`,`area_id`,`item_name`,`frequency`,`photo_required`,`cleaning_procedure`,`cleaning_when`,`interval_days`) VALUES
(1,1,1,'Worktops','Daily',0,'Wipe down and sanitise','Closing',NULL);

INSERT INTO `cleaning_logs` (`id`,`business_id`,`item_id`,`user_initials`,`cleaned_at`,`photo_path`,`user_id`) VALUES
(1,1,1,'DC',NOW(),NULL,10);

INSERT INTO `equipment_types` (`id`,`business_id`,`name`) VALUES
(1,1,'Fridge');

INSERT INTO `equipment` (`id`,`business_id`,`name`,`type`,`is_active`,`department`) VALUES
(1,1,'Main Fridge','Fridge',1,'Kitchen');

INSERT INTO `fridge_logs` (`id`,`business_id`,`equipment_id`,`user_initials`,`temperature`,`check_time`,`photo_path`,`user_id`) VALUES
(1,1,1,'DS',3.5,NOW(),NULL,11);

INSERT INTO `leave_requests` (`id`,`business_id`,`user_id`,`leave_type`,`start_date`,`end_date`,`start_half_day`,`end_half_day`,`total_days`,`reason`,`status`,`requested_by`,`requested_at`,`decided_by`,`decided_at`,`decision_note`,`is_archived`,`created_at`,`updated_at`) VALUES
(1,1,11,'holiday','2026-02-02','2026-02-04',0,0,3.00,'Demo holiday','pending',11,NOW(),NULL,NULL,NULL,0,NOW(),NULL);

INSERT INTO `absences` (`id`,`business_id`,`user_id`,`absence_type`,`date_from`,`date_to`,`total_days`,`reason`,`recorded_by`,`recorded_at`,`is_archived`,`created_at`,`updated_at`) VALUES
(1,1,10,'sick','2026-01-21','2026-01-22',2.00,'Demo sick',2,NOW(),0,NOW(),NULL);


