-- ================================================
-- MJPOS Database Backup
-- Database : mjposliv_mjpos_db_dubaishop
-- Generated: 2026-07-31 13:49:21
-- Host     : localhost
-- ================================================

SET FOREIGN_KEY_CHECKS = 0;
SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";

-- ------------------------------------------------
-- Table: `api_tokens`
-- ------------------------------------------------

DROP TABLE IF EXISTS `api_tokens`;
CREATE TABLE `api_tokens` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `token` varchar(64) NOT NULL,
  `name` varchar(100) DEFAULT 'Mobile App',
  `last_used` datetime DEFAULT NULL,
  `expires_at` datetime DEFAULT NULL,
  `status` enum('active','revoked') DEFAULT 'active',
  `created_at` datetime DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `token` (`token`),
  KEY `idx_token` (`token`),
  KEY `idx_user` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;


-- ------------------------------------------------
-- Table: `audit_logs`
-- ------------------------------------------------

DROP TABLE IF EXISTS `audit_logs`;
CREATE TABLE `audit_logs` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(10) unsigned DEFAULT NULL,
  `action` varchar(100) NOT NULL,
  `module` varchar(50) NOT NULL,
  `description` text DEFAULT NULL,
  `old_value` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`old_value`)),
  `new_value` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`new_value`)),
  `ip_address` varchar(45) DEFAULT NULL,
  `user_agent` varchar(255) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2503 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `audit_logs` (`id`, `user_id`, `action`, `module`, `description`, `old_value`, `new_value`, `ip_address`, `user_agent`, `created_at`) VALUES
('2486', '1', 'backup_restore', 'backup', 'Restore from: upload_2026-07-31_133541.sql — 101 statements, 0 skipped', NULL, NULL, '103.21.166.157', 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/151.0.0.0 Safari/537.36', '2026-07-31 13:35:53'),
('2487', '1', 'backup_restore', 'backup', 'Restore from: upload_2026-07-31_133655.sql — 6 statements, 2 skipped', NULL, NULL, '103.21.166.157', 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/151.0.0.0 Safari/537.36', '2026-07-31 13:36:55'),
('2488', '1', 'backup_restore', 'backup', 'Restore from: upload_2026-07-31_133737.sql — 5 statements, 1 skipped', NULL, NULL, '103.21.166.157', 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/151.0.0.0 Safari/537.36', '2026-07-31 13:37:37'),
('2489', '1', 'logout', 'auth', 'User logged out', NULL, NULL, '103.21.166.157', 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/151.0.0.0 Safari/537.36', '2026-07-31 13:38:11'),
('2490', '1', 'login', 'auth', 'Login from 103.21.166.157', NULL, NULL, '103.21.166.157', 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/151.0.0.0 Safari/537.36', '2026-07-31 13:38:13'),
('2491', '1', 'backup_delete', 'backup', 'Deleted backup: upload_2026-07-31_133541.sql', NULL, NULL, '103.21.166.157', 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/151.0.0.0 Safari/537.36', '2026-07-31 13:38:23'),
('2492', '1', 'backup_delete', 'backup', 'Deleted backup: upload_2026-07-31_133655.sql', NULL, NULL, '103.21.166.157', 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/151.0.0.0 Safari/537.36', '2026-07-31 13:38:25'),
('2493', '1', 'backup_delete', 'backup', 'Deleted backup: upload_2026-07-31_133737.sql', NULL, NULL, '103.21.166.157', 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/151.0.0.0 Safari/537.36', '2026-07-31 13:38:27'),
('2494', '1', 'backup_create', 'backup', 'Backup created: mjposliv_mjpos_db_dubaishop_2026-07-31_133831.sql', NULL, NULL, '103.21.166.157', 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/151.0.0.0 Safari/537.36', '2026-07-31 13:38:31'),
('2495', '1', 'settings_update', 'settings', 'Settings updated', NULL, NULL, '103.21.166.157', 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/151.0.0.0 Safari/537.36', '2026-07-31 13:41:52'),
('2496', '1', 'settings_update', 'settings', 'Settings updated', NULL, NULL, '103.21.166.157', 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/151.0.0.0 Safari/537.36', '2026-07-31 13:45:56'),
('2497', '1', 'logout', 'auth', 'User logged out', NULL, NULL, '103.21.166.157', 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/151.0.0.0 Safari/537.36', '2026-07-31 13:46:36'),
('2498', '1', 'login', 'auth', 'Login from 103.21.166.157', NULL, NULL, '103.21.166.157', 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/151.0.0.0 Safari/537.36', '2026-07-31 13:46:41'),
('2499', '1', 'user_delete', 'users', 'Deactivated user: NIRU123', NULL, NULL, '103.21.166.157', 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/151.0.0.0 Safari/537.36', '2026-07-31 13:47:14'),
('2500', '1', 'user_delete', 'users', 'Deactivated user: Shashi123', NULL, NULL, '103.21.166.157', 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/151.0.0.0 Safari/537.36', '2026-07-31 13:47:27'),
('2501', '1', 'settings_update', 'settings', 'Settings updated', NULL, NULL, '103.21.166.157', 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/151.0.0.0 Safari/537.36', '2026-07-31 13:48:16'),
('2502', '1', 'backup_delete', 'backup', 'Deleted backup: mjposliv_mjpos_db_dubaishop_2026-07-31_133831.sql', NULL, NULL, '103.21.166.157', 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/151.0.0.0 Safari/537.36', '2026-07-31 13:49:19');

-- ------------------------------------------------
-- Table: `branches`
-- ------------------------------------------------

DROP TABLE IF EXISTS `branches`;
CREATE TABLE `branches` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(150) NOT NULL,
  `address` text DEFAULT NULL,
  `phone` varchar(20) DEFAULT NULL,
  `email` varchar(100) DEFAULT NULL,
  `is_main` tinyint(1) NOT NULL DEFAULT 0,
  `status` enum('active','inactive') DEFAULT 'active',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- ------------------------------------------------
-- Table: `brands`
-- ------------------------------------------------

DROP TABLE IF EXISTS `brands`;
CREATE TABLE `brands` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `brand_name` varchar(100) NOT NULL,
  `description` text DEFAULT NULL,
  `status` enum('active','inactive') DEFAULT 'active',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=25 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- ------------------------------------------------
-- Table: `categories`
-- ------------------------------------------------

DROP TABLE IF EXISTS `categories`;
CREATE TABLE `categories` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `category_name` varchar(100) NOT NULL,
  `description` text DEFAULT NULL,
  `status` enum('active','inactive') DEFAULT 'active',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- ------------------------------------------------
-- Table: `cheque_tracking`
-- ------------------------------------------------

DROP TABLE IF EXISTS `cheque_tracking`;
CREATE TABLE `cheque_tracking` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `transaction_type` enum('sale','purchase') NOT NULL DEFAULT 'sale',
  `sale_payment_id` int(10) unsigned DEFAULT NULL,
  `purchase_payment_id` int(10) unsigned DEFAULT NULL,
  `sale_id` int(10) unsigned DEFAULT NULL,
  `purchase_id` int(10) unsigned DEFAULT NULL,
  `party_name` varchar(150) DEFAULT NULL,
  `party_phone` varchar(20) DEFAULT NULL,
  `cheque_no` varchar(50) NOT NULL,
  `bank_name` varchar(100) DEFAULT NULL,
  `branch` varchar(100) DEFAULT NULL,
  `cheque_date` date DEFAULT NULL,
  `amount` decimal(12,2) NOT NULL DEFAULT 0.00,
  `status` enum('pending','cleared','bounced') DEFAULT 'pending',
  `cleared_date` date DEFAULT NULL,
  `note` text DEFAULT NULL,
  `created_by` int(10) unsigned DEFAULT NULL,
  `updated_by` int(10) unsigned DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- ------------------------------------------------
-- Table: `customer_payments`
-- ------------------------------------------------

DROP TABLE IF EXISTS `customer_payments`;
CREATE TABLE `customer_payments` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `customer_id` int(10) unsigned NOT NULL,
  `sale_id` int(10) unsigned DEFAULT NULL,
  `amount` decimal(12,2) NOT NULL,
  `payment_date` date NOT NULL,
  `payment_method` enum('cash','card','bank_transfer','mobile') DEFAULT 'cash',
  `reference` varchar(100) DEFAULT NULL,
  `note` text DEFAULT NULL,
  `created_by` int(10) unsigned DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- ------------------------------------------------
-- Table: `customers`
-- ------------------------------------------------

DROP TABLE IF EXISTS `customers`;
CREATE TABLE `customers` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `customer_name` varchar(150) NOT NULL,
  `phone` varchar(20) DEFAULT NULL,
  `email` varchar(100) DEFAULT NULL,
  `address` text DEFAULT NULL,
  `credit_limit` decimal(12,2) NOT NULL DEFAULT 0.00,
  `opening_balance` decimal(12,2) NOT NULL DEFAULT 0.00,
  `current_balance` decimal(12,2) NOT NULL DEFAULT 0.00 COMMENT 'Positive = customer owes us',
  `loyalty_points` int(10) unsigned NOT NULL DEFAULT 0,
  `status` enum('active','inactive') DEFAULT 'active',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- ------------------------------------------------
-- Table: `employee_attendance`
-- ------------------------------------------------

DROP TABLE IF EXISTS `employee_attendance`;
CREATE TABLE `employee_attendance` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `employee_id` int(10) unsigned NOT NULL,
  `att_date` date NOT NULL,
  `check_in` time DEFAULT NULL,
  `check_out` time DEFAULT NULL,
  `worked_hours` decimal(5,2) DEFAULT 0.00,
  `ot_hours` decimal(5,2) DEFAULT 0.00,
  `status` enum('present','absent','half_day','late','leave','holiday','off') DEFAULT 'present',
  `method` enum('manual','fingerprint','qr','face','card') DEFAULT 'manual',
  `fingerprint_id` varchar(50) DEFAULT NULL,
  `device_id` varchar(50) DEFAULT NULL,
  `note` text DEFAULT NULL,
  `recorded_by` int(10) unsigned DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_attendance_date_status` (`att_date`,`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;


-- ------------------------------------------------
-- Table: `employee_components`
-- ------------------------------------------------

DROP TABLE IF EXISTS `employee_components`;
CREATE TABLE `employee_components` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `employee_id` int(10) unsigned NOT NULL,
  `component_id` int(10) unsigned NOT NULL,
  `amount` decimal(12,2) NOT NULL DEFAULT 0.00,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=32 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- ------------------------------------------------
-- Table: `employees`
-- ------------------------------------------------

DROP TABLE IF EXISTS `employees`;
CREATE TABLE `employees` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(10) unsigned DEFAULT NULL,
  `emp_code` varchar(20) NOT NULL,
  `full_name` varchar(150) NOT NULL,
  `phone` varchar(20) DEFAULT NULL,
  `email` varchar(100) DEFAULT NULL,
  `nic` varchar(20) DEFAULT NULL,
  `designation` varchar(100) DEFAULT NULL,
  `department` varchar(100) DEFAULT NULL,
  `branch_id` int(10) unsigned DEFAULT 1,
  `join_date` date NOT NULL,
  `basic_salary` decimal(12,2) NOT NULL DEFAULT 0.00,
  `pay_type` enum('monthly','daily','hourly') DEFAULT 'monthly',
  `bank_name` varchar(100) DEFAULT NULL,
  `bank_account` varchar(50) DEFAULT NULL,
  `bank_branch` varchar(100) DEFAULT NULL,
  `status` enum('active','inactive','terminated') DEFAULT 'active',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `fingerprint_id` varchar(20) DEFAULT NULL COMMENT 'Device user ID',
  `epf_eligible` tinyint(1) NOT NULL DEFAULT 1,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- ------------------------------------------------
-- Table: `expense_categories`
-- ------------------------------------------------

DROP TABLE IF EXISTS `expense_categories`;
CREATE TABLE `expense_categories` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `category_name` varchar(100) NOT NULL,
  `description` text DEFAULT NULL,
  `status` enum('active','inactive') DEFAULT 'active',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- ------------------------------------------------
-- Table: `expenses`
-- ------------------------------------------------

DROP TABLE IF EXISTS `expenses`;
CREATE TABLE `expenses` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `expense_category_id` int(10) unsigned NOT NULL,
  `branch_id` int(10) unsigned DEFAULT NULL,
  `title` varchar(200) NOT NULL,
  `amount` decimal(12,2) NOT NULL DEFAULT 0.00,
  `expense_date` date NOT NULL,
  `reference` varchar(100) DEFAULT NULL,
  `note` text DEFAULT NULL,
  `created_by` int(10) unsigned DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_expenses_date` (`expense_date`)
) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- ------------------------------------------------
-- Table: `finance_accounts`
-- ------------------------------------------------

DROP TABLE IF EXISTS `finance_accounts`;
CREATE TABLE `finance_accounts` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `account_name` varchar(100) NOT NULL,
  `account_type` enum('cash','bank') NOT NULL DEFAULT 'cash',
  `bank_name` varchar(100) DEFAULT NULL,
  `account_number` varchar(50) DEFAULT NULL,
  `branch` varchar(100) DEFAULT NULL,
  `opening_balance` decimal(14,2) NOT NULL DEFAULT 0.00,
  `current_balance` decimal(14,2) NOT NULL DEFAULT 0.00,
  `status` enum('active','inactive') NOT NULL DEFAULT 'active',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- ------------------------------------------------
-- Table: `finance_transactions`
-- ------------------------------------------------

DROP TABLE IF EXISTS `finance_transactions`;
CREATE TABLE `finance_transactions` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `account_id` int(10) unsigned NOT NULL,
  `txn_type` enum('deposit','withdrawal','transfer_in','transfer_out') NOT NULL,
  `amount` decimal(14,2) NOT NULL,
  `balance_after` decimal(14,2) NOT NULL,
  `related_account_id` int(10) unsigned DEFAULT NULL,
  `reference` varchar(100) DEFAULT NULL,
  `note` text DEFAULT NULL,
  `txn_date` date NOT NULL,
  `created_by` int(10) unsigned DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=509 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- ------------------------------------------------
-- Table: `fingerprint_logs`
-- ------------------------------------------------

DROP TABLE IF EXISTS `fingerprint_logs`;
CREATE TABLE `fingerprint_logs` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `device_id` varchar(50) NOT NULL,
  `device_user_id` varchar(50) NOT NULL,
  `employee_id` int(10) unsigned DEFAULT NULL,
  `punch_time` datetime NOT NULL,
  `punch_type` enum('in','out','unknown') DEFAULT 'unknown',
  `status` enum('pending','processed','error') DEFAULT 'pending',
  `error_msg` varchar(255) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;


-- ------------------------------------------------
-- Table: `held_bills`
-- ------------------------------------------------

DROP TABLE IF EXISTS `held_bills`;
CREATE TABLE `held_bills` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `hold_ref` varchar(50) NOT NULL,
  `customer_id` int(10) unsigned DEFAULT NULL,
  `cart_data` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL COMMENT 'Serialised cart items' CHECK (json_valid(`cart_data`)),
  `subtotal` decimal(12,2) NOT NULL DEFAULT 0.00,
  `note` varchar(255) DEFAULT NULL,
  `created_by` int(10) unsigned DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- ------------------------------------------------
-- Table: `leave_types`
-- ------------------------------------------------

DROP TABLE IF EXISTS `leave_types`;
CREATE TABLE `leave_types` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `days_per_year` int(11) DEFAULT 0,
  `is_paid` tinyint(1) DEFAULT 1,
  `status` enum('active','inactive') DEFAULT 'active',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;


-- ------------------------------------------------
-- Table: `password_resets`
-- ------------------------------------------------

DROP TABLE IF EXISTS `password_resets`;
CREATE TABLE `password_resets` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(10) unsigned NOT NULL,
  `token` varchar(64) NOT NULL,
  `otp` varchar(6) DEFAULT NULL,
  `otp_verified` tinyint(1) NOT NULL DEFAULT 0,
  `expires_at` datetime NOT NULL,
  `used` tinyint(1) NOT NULL DEFAULT 0,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- ------------------------------------------------
-- Table: `payroll_periods`
-- ------------------------------------------------

DROP TABLE IF EXISTS `payroll_periods`;
CREATE TABLE `payroll_periods` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `period_name` varchar(50) NOT NULL,
  `month` tinyint(2) NOT NULL,
  `year` smallint(4) NOT NULL,
  `start_date` date NOT NULL,
  `end_date` date NOT NULL,
  `status` enum('draft','processing','approved','paid') DEFAULT 'draft',
  `notes` text DEFAULT NULL,
  `created_by` int(10) unsigned DEFAULT NULL,
  `approved_by` int(10) unsigned DEFAULT NULL,
  `approved_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- ------------------------------------------------
-- Table: `payroll_record_components`
-- ------------------------------------------------

DROP TABLE IF EXISTS `payroll_record_components`;
CREATE TABLE `payroll_record_components` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `record_id` int(10) unsigned NOT NULL,
  `component_id` int(10) unsigned DEFAULT NULL,
  `name` varchar(100) NOT NULL,
  `type` enum('allowance','deduction') NOT NULL,
  `amount` decimal(12,2) NOT NULL DEFAULT 0.00,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=111 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- ------------------------------------------------
-- Table: `payroll_records`
-- ------------------------------------------------

DROP TABLE IF EXISTS `payroll_records`;
CREATE TABLE `payroll_records` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `period_id` int(10) unsigned NOT NULL,
  `employee_id` int(10) unsigned NOT NULL,
  `basic_salary` decimal(12,2) NOT NULL DEFAULT 0.00,
  `working_days` decimal(5,1) DEFAULT 26.0,
  `present_days` decimal(5,1) DEFAULT 26.0,
  `overtime_hours` decimal(6,2) DEFAULT 0.00,
  `overtime_rate` decimal(10,2) DEFAULT 0.00,
  `overtime_amount` decimal(12,2) DEFAULT 0.00,
  `gross_salary` decimal(12,2) NOT NULL DEFAULT 0.00,
  `total_allowances` decimal(12,2) DEFAULT 0.00,
  `total_deductions` decimal(12,2) DEFAULT 0.00,
  `epf_employee` decimal(12,2) DEFAULT 0.00,
  `epf_employer` decimal(12,2) DEFAULT 0.00,
  `etf` decimal(12,2) DEFAULT 0.00,
  `tax` decimal(12,2) DEFAULT 0.00,
  `net_salary` decimal(12,2) NOT NULL DEFAULT 0.00,
  `payment_method` enum('cash','bank_transfer','cheque') DEFAULT 'bank_transfer',
  `payment_status` enum('pending','paid') DEFAULT 'pending',
  `paid_at` timestamp NULL DEFAULT NULL,
  `notes` text DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- ------------------------------------------------
-- Table: `permissions`
-- ------------------------------------------------

DROP TABLE IF EXISTS `permissions`;
CREATE TABLE `permissions` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `module` varchar(50) NOT NULL,
  `action` varchar(50) NOT NULL,
  `permission_key` varchar(100) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=85 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- ------------------------------------------------
-- Table: `product_batches`
-- ------------------------------------------------

DROP TABLE IF EXISTS `product_batches`;
CREATE TABLE `product_batches` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `product_id` int(10) unsigned NOT NULL,
  `batch_no` varchar(100) NOT NULL,
  `mfg_date` date DEFAULT NULL,
  `expiry_date` date DEFAULT NULL,
  `purchase_price` decimal(12,2) NOT NULL DEFAULT 0.00,
  `quantity` decimal(12,2) NOT NULL DEFAULT 0.00,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- ------------------------------------------------
-- Table: `products`
-- ------------------------------------------------

DROP TABLE IF EXISTS `products`;
CREATE TABLE `products` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `category_id` int(10) unsigned DEFAULT NULL,
  `brand_id` int(10) unsigned DEFAULT NULL,
  `unit_id` int(10) unsigned DEFAULT NULL,
  `product_code` varchar(50) NOT NULL,
  `barcode` varchar(100) DEFAULT NULL,
  `product_name` varchar(200) NOT NULL,
  `generic_name` varchar(200) DEFAULT NULL,
  `description` text DEFAULT NULL,
  `cost_price` decimal(12,2) NOT NULL DEFAULT 0.00,
  `selling_price` decimal(12,2) NOT NULL DEFAULT 0.00,
  `wholesale_price` decimal(12,2) DEFAULT 0.00,
  `reorder_level` int(11) NOT NULL DEFAULT 0,
  `current_stock` decimal(12,2) NOT NULL DEFAULT 0.00,
  `tax_percent` decimal(5,2) DEFAULT 0.00,
  `discount_percent` decimal(5,2) DEFAULT 0.00,
  `expiry_date` date DEFAULT NULL,
  `has_expiry` tinyint(1) NOT NULL DEFAULT 0,
  `low_stock_days` int(11) DEFAULT NULL,
  `product_image` varchar(255) DEFAULT NULL,
  `is_batch` tinyint(1) NOT NULL DEFAULT 0 COMMENT '1 = track batches (pharmacy)',
  `is_serial` tinyint(1) NOT NULL DEFAULT 0 COMMENT '1 = track serial numbers',
  `status` enum('active','inactive') DEFAULT 'active',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=555 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- ------------------------------------------------
-- Table: `purchase_items`
-- ------------------------------------------------

DROP TABLE IF EXISTS `purchase_items`;
CREATE TABLE `purchase_items` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `purchase_id` int(10) unsigned NOT NULL,
  `product_id` int(10) unsigned NOT NULL,
  `batch_id` int(10) unsigned DEFAULT NULL,
  `quantity` decimal(12,2) NOT NULL,
  `cost_price` decimal(12,2) NOT NULL,
  `discount` decimal(12,2) NOT NULL DEFAULT 0.00,
  `tax` decimal(12,2) NOT NULL DEFAULT 0.00,
  `total` decimal(12,2) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=671 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- ------------------------------------------------
-- Table: `purchase_return_items`
-- ------------------------------------------------

DROP TABLE IF EXISTS `purchase_return_items`;
CREATE TABLE `purchase_return_items` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `purchase_return_id` int(10) unsigned NOT NULL,
  `product_id` int(10) unsigned NOT NULL,
  `quantity` decimal(12,2) NOT NULL,
  `cost_price` decimal(12,2) NOT NULL,
  `total` decimal(12,2) NOT NULL,
  `reason` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=33 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- ------------------------------------------------
-- Table: `purchase_returns`
-- ------------------------------------------------

DROP TABLE IF EXISTS `purchase_returns`;
CREATE TABLE `purchase_returns` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `purchase_id` int(10) unsigned NOT NULL,
  `supplier_id` int(10) unsigned NOT NULL,
  `invoice_no` varchar(50) NOT NULL,
  `return_date` date NOT NULL,
  `total` decimal(12,2) NOT NULL DEFAULT 0.00,
  `note` text DEFAULT NULL,
  `created_by` int(10) unsigned DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- ------------------------------------------------
-- Table: `purchases`
-- ------------------------------------------------

DROP TABLE IF EXISTS `purchases`;
CREATE TABLE `purchases` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `supplier_id` int(10) unsigned NOT NULL,
  `branch_id` int(10) unsigned DEFAULT NULL,
  `invoice_no` varchar(50) NOT NULL,
  `purchase_date` date NOT NULL,
  `subtotal` decimal(12,2) NOT NULL DEFAULT 0.00,
  `discount` decimal(12,2) NOT NULL DEFAULT 0.00,
  `tax` decimal(12,2) NOT NULL DEFAULT 0.00,
  `total` decimal(12,2) NOT NULL DEFAULT 0.00,
  `paid` decimal(12,2) NOT NULL DEFAULT 0.00,
  `balance` decimal(12,2) NOT NULL DEFAULT 0.00,
  `payment_status` enum('paid','partial','due') DEFAULT 'due',
  `payment_method` enum('cash','card','bank_transfer','mobile','credit') DEFAULT 'cash',
  `note` text DEFAULT NULL,
  `status` enum('received','pending','cancelled') DEFAULT 'received',
  `created_by` int(10) unsigned DEFAULT NULL,
  `updated_by` int(10) unsigned DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_purchases_date_status` (`purchase_date`,`status`)
) ENGINE=InnoDB AUTO_INCREMENT=112 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- ------------------------------------------------
-- Table: `role_permissions`
-- ------------------------------------------------

DROP TABLE IF EXISTS `role_permissions`;
CREATE TABLE `role_permissions` (
  `role_id` int(10) unsigned NOT NULL,
  `permission_id` int(10) unsigned NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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'),
('1', '21'),
('1', '22'),
('1', '23'),
('1', '24'),
('1', '25'),
('1', '26'),
('1', '27'),
('1', '28'),
('1', '29'),
('1', '30'),
('1', '31'),
('1', '32'),
('1', '33'),
('1', '34'),
('1', '35'),
('1', '36'),
('1', '37'),
('1', '38'),
('1', '39'),
('1', '40'),
('1', '41'),
('3', '1'),
('3', '6'),
('3', '7'),
('3', '8'),
('3', '9'),
('3', '10'),
('3', '11'),
('3', '12'),
('3', '13'),
('3', '14'),
('3', '15'),
('3', '16'),
('3', '17'),
('3', '18'),
('3', '19'),
('3', '20'),
('3', '21'),
('3', '22'),
('3', '23'),
('3', '24'),
('3', '25'),
('3', '26'),
('3', '30'),
('3', '34'),
('3', '35'),
('3', '36'),
('3', '37'),
('3', '38'),
('3', '39'),
('3', '40'),
('3', '41'),
('5', '1'),
('5', '6'),
('5', '7'),
('5', '8'),
('5', '9'),
('5', '10'),
('5', '11'),
('5', '12'),
('5', '19'),
('5', '20'),
('5', '21'),
('5', '22'),
('5', '23'),
('6', '1'),
('6', '13'),
('6', '24'),
('6', '25'),
('6', '26'),
('6', '30'),
('6', '34'),
('6', '35'),
('6', '36'),
('6', '37'),
('6', '39'),
('6', '40'),
('6', '41'),
('7', '1'),
('7', '29'),
('7', '30'),
('7', '31'),
('7', '32'),
('7', '33'),
('7', '34'),
('7', '39'),
('7', '41'),
('8', '1'),
('8', '2'),
('8', '3'),
('8', '4'),
('8', '5'),
('8', '6'),
('8', '7'),
('8', '8'),
('8', '9'),
('8', '10'),
('8', '11'),
('8', '12'),
('8', '13'),
('8', '14'),
('8', '15'),
('8', '16'),
('8', '17'),
('8', '18'),
('8', '19'),
('8', '20'),
('8', '21'),
('8', '22'),
('8', '23'),
('8', '24'),
('8', '25'),
('8', '26'),
('8', '27'),
('8', '28'),
('8', '29'),
('8', '30'),
('8', '31'),
('8', '32'),
('8', '33'),
('8', '34'),
('8', '35'),
('8', '36'),
('8', '37'),
('8', '38'),
('8', '39'),
('8', '40'),
('8', '41'),
('1', '0'),
('3', '0'),
('8', '0'),
('1', '0'),
('3', '0'),
('5', '0'),
('8', '0'),
('2', '40'),
('2', '39'),
('2', '29'),
('2', '36'),
('2', '35'),
('2', '38'),
('2', '37'),
('2', '17'),
('2', '18'),
('2', '16'),
('2', '1'),
('2', '25'),
('2', '24'),
('2', '41'),
('2', '32'),
('2', '33'),
('2', '31'),
('2', '34'),
('2', '30'),
('2', '7'),
('2', '9'),
('2', '8'),
('2', '6'),
('2', '11'),
('2', '12'),
('2', '10'),
('2', '26'),
('2', '14'),
('2', '15'),
('2', '13'),
('2', '28'),
('2', '27'),
('2', '23'),
('2', '22'),
('2', '20'),
('2', '21'),
('2', '19'),
('2', '3'),
('2', '5'),
('2', '4'),
('2', '2'),
('4', '37'),
('4', '1'),
('4', '14'),
('4', '13'),
('4', '22');

-- ------------------------------------------------
-- Table: `roles`
-- ------------------------------------------------

DROP TABLE IF EXISTS `roles`;
CREATE TABLE `roles` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `role_name` varchar(50) NOT NULL,
  `description` varchar(255) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `perms_version` int(10) unsigned NOT NULL DEFAULT 1,
  `perms_updated_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `roles` (`id`, `role_name`, `description`, `created_at`, `perms_version`, `perms_updated_at`) VALUES
('1', 'Super Admin', 'Full system access across all branches', '2026-05-23 13:41:19', '11', '2026-06-30 13:11:14'),
('2', 'admin', 'Shop owner/admin – manage shop, users, products', '2026-05-23 13:41:19', '11', '2026-07-01 13:03:46'),
('3', 'Manager', 'Manage stock, purchases, sales, and reports', '2026-05-23 13:41:19', '10', '2026-06-30 13:11:14'),
('4', 'cashier', 'Create bills, manage daily sales and returns', '2026-05-23 13:41:19', '37', '2026-07-30 11:12:15'),
('5', 'Stock Keeper', 'Manage products, stock, and purchases', '2026-05-23 13:41:19', '10', '2026-06-30 13:11:14'),
('6', 'Accountant', 'View sales, expenses, payments, and profit reports', '2026-05-23 13:41:19', '14', '2026-06-30 13:11:14'),
('7', 'HR Manager', 'HR Manager – payroll, employees, attendance, reports', '2026-06-16 11:55:27', '16', '2026-06-30 13:11:14'),
('8', 'developer', 'Full system + Developer Tools access (clear data, debug, backups)', '2026-06-30 12:22:27', '4', '2026-06-30 13:11:14');

-- ------------------------------------------------
-- Table: `salary_advances`
-- ------------------------------------------------

DROP TABLE IF EXISTS `salary_advances`;
CREATE TABLE `salary_advances` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `employee_id` int(10) unsigned NOT NULL,
  `amount` decimal(12,2) NOT NULL,
  `advance_date` date NOT NULL,
  `reason` text DEFAULT NULL,
  `deduct_month` tinyint(2) DEFAULT NULL,
  `deduct_year` smallint(4) DEFAULT NULL,
  `status` enum('pending','deducted','cancelled') DEFAULT 'pending',
  `created_by` int(10) unsigned DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- ------------------------------------------------
-- Table: `salary_components`
-- ------------------------------------------------

DROP TABLE IF EXISTS `salary_components`;
CREATE TABLE `salary_components` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `type` enum('allowance','deduction') NOT NULL,
  `calc_type` enum('fixed','percentage') DEFAULT 'fixed',
  `amount` decimal(12,2) DEFAULT 0.00,
  `is_taxable` tinyint(1) DEFAULT 0,
  `is_default` tinyint(1) DEFAULT 0,
  `status` enum('active','inactive') DEFAULT 'active',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- ------------------------------------------------
-- Table: `sale_items`
-- ------------------------------------------------

DROP TABLE IF EXISTS `sale_items`;
CREATE TABLE `sale_items` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `sale_id` int(10) unsigned NOT NULL,
  `product_id` int(10) unsigned NOT NULL,
  `batch_id` int(10) unsigned DEFAULT NULL,
  `quantity` decimal(12,2) NOT NULL,
  `cost_price` decimal(12,2) NOT NULL COMMENT 'Snapshot at time of sale for profit calc',
  `selling_price` decimal(12,2) NOT NULL,
  `discount` decimal(12,2) NOT NULL DEFAULT 0.00,
  `tax` decimal(12,2) NOT NULL DEFAULT 0.00,
  `total` decimal(12,2) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_sale_items_sale_id` (`sale_id`),
  KEY `idx_sale_items_product_id` (`product_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1312 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- ------------------------------------------------
-- Table: `sale_payments`
-- ------------------------------------------------

DROP TABLE IF EXISTS `sale_payments`;
CREATE TABLE `sale_payments` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `sale_id` int(10) unsigned NOT NULL,
  `payment_method` enum('cash','card','bank_transfer','mobile','credit','cheque') NOT NULL,
  `amount` decimal(12,2) NOT NULL,
  `reference` varchar(100) DEFAULT NULL COMMENT 'Card ref / transaction id',
  `note` varchar(255) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_sale_payments_sale_id` (`sale_id`),
  KEY `idx_sale_payments_method` (`payment_method`)
) ENGINE=InnoDB AUTO_INCREMENT=517 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- ------------------------------------------------
-- Table: `sales`
-- ------------------------------------------------

DROP TABLE IF EXISTS `sales`;
CREATE TABLE `sales` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `customer_id` int(10) unsigned DEFAULT NULL,
  `customer_phone` varchar(20) DEFAULT NULL,
  `branch_id` int(10) unsigned DEFAULT NULL,
  `invoice_no` varchar(50) NOT NULL,
  `sale_date` datetime NOT NULL,
  `subtotal` decimal(12,2) NOT NULL DEFAULT 0.00,
  `discount` decimal(12,2) NOT NULL DEFAULT 0.00,
  `tax` decimal(12,2) NOT NULL DEFAULT 0.00,
  `total` decimal(12,2) NOT NULL DEFAULT 0.00,
  `paid` decimal(12,2) NOT NULL DEFAULT 0.00,
  `balance` decimal(12,2) NOT NULL DEFAULT 0.00,
  `payment_status` enum('paid','partial','due') DEFAULT 'paid',
  `note` text DEFAULT NULL,
  `status` enum('completed','held','cancelled','returned') DEFAULT 'completed',
  `created_by` int(10) unsigned DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_sales_daily_report` (`sale_date`,`status`),
  KEY `idx_sales_created_by` (`created_by`)
) ENGINE=InnoDB AUTO_INCREMENT=516 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- ------------------------------------------------
-- Table: `sales_return_items`
-- ------------------------------------------------

DROP TABLE IF EXISTS `sales_return_items`;
CREATE TABLE `sales_return_items` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `sales_return_id` int(10) unsigned NOT NULL,
  `product_id` int(10) unsigned NOT NULL,
  `quantity` decimal(12,2) NOT NULL,
  `selling_price` decimal(12,2) NOT NULL,
  `total` decimal(12,2) NOT NULL,
  `reason` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=39 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- ------------------------------------------------
-- Table: `sales_returns`
-- ------------------------------------------------

DROP TABLE IF EXISTS `sales_returns`;
CREATE TABLE `sales_returns` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `sale_id` int(10) unsigned NOT NULL,
  `customer_id` int(10) unsigned DEFAULT NULL,
  `invoice_no` varchar(50) NOT NULL,
  `return_date` date NOT NULL,
  `total` decimal(12,2) NOT NULL DEFAULT 0.00,
  `refund_method` enum('cash','card','bank_transfer','mobile','credit') DEFAULT 'cash',
  `note` text DEFAULT NULL,
  `created_by` int(10) unsigned DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=33 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- ------------------------------------------------
-- Table: `settings`
-- ------------------------------------------------

DROP TABLE IF EXISTS `settings`;
CREATE TABLE `settings` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `setting_key` varchar(100) NOT NULL,
  `setting_value` text DEFAULT NULL,
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_settings_setting_key` (`setting_key`)
) ENGINE=InnoDB AUTO_INCREMENT=2212 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `settings` (`id`, `setting_key`, `setting_value`, `updated_at`) VALUES
('2060', 'shop_name', 'DUBAI SHOP', '2026-07-31 13:41:52'),
('2061', 'shop_tagline', 'IMPORT & FACTORY OUTLETS', '2026-07-31 13:41:52'),
('2062', 'shop_address', '286, Kahatagasthanna, Ruwanwella', '2026-07-31 13:41:52'),
('2063', 'shop_phone', '0721055006', '2026-07-31 13:41:52'),
('2064', 'shop_email', 'info@venusretcar.com', '2026-07-31 13:41:52'),
('2065', 'currency_symbol', 'Rs.', '2026-07-31 13:41:52'),
('2066', 'currency_code', '', '2026-07-31 13:41:52'),
('2067', 'tax_percent', '0', '2026-07-31 13:41:52'),
('2068', 'tax_name', 'VAT', '2026-07-31 13:41:52'),
('2069', 'invoice_prefix', '', '2026-07-31 13:41:52'),
('2070', 'purchase_prefix', '', '2026-07-31 13:41:52'),
('2071', 'return_prefix', '', '2026-07-31 13:41:52'),
('2072', 'receipt_footer', 'Thank you for shopping with us!', '2026-07-31 13:48:16'),
('2073', 'print_auto_enabled', '1', '2026-07-31 13:41:52'),
('2074', 'print_auto_close', '1', '2026-07-31 13:41:52'),
('2075', 'print_paper_width', '80mm', '2026-07-31 13:41:52'),
('2076', 'print_copies', '1', '2026-07-31 13:41:52'),
('2077', 'pmt_account_cash', '', '2026-07-31 13:41:52'),
('2078', 'pmt_account_card', '', '2026-07-31 13:41:52'),
('2079', 'pmt_account_bank_transfer', '', '2026-07-31 13:41:52'),
('2080', 'pmt_account_mobile', '', '2026-07-31 13:41:52'),
('2081', 'card_fee_percent', '2.5', '2026-07-31 13:41:52'),
('2082', 'daily_sales_target', '0', '2026-07-31 13:41:52'),
('2083', 'daily_target_enabled', '0', '2026-07-31 13:41:52'),
('2084', 'sms_enabled', '0', '2026-07-31 13:41:52'),
('2085', 'sms_user_id', '2141', '2026-07-31 13:41:52'),
('2086', 'sms_api_key', '', '2026-07-31 13:41:52'),
('2087', 'sms_sender_id', 'SMSlenzDEMO', '2026-07-31 13:41:52'),
('2088', 'sms_api_url', 'https://smslenz.lk/api', '2026-07-31 13:41:52'),
('2089', 'sms_template', 'Hi {customer}, Invoice {invoice_no} | Total: Rs.{total} | Paid: Rs.{paid} | Thank you! - {shop_name}', '2026-07-31 13:41:52'),
('2090', 'admin_reset_phone', '', '2026-07-31 13:41:52'),
('2091', 'admin_report_phone', '', '2026-07-31 13:41:52'),
('2092', 'low_stock_alert', '10', '2026-07-31 13:41:52'),
('2093', 'expiry_alert_days', '30', '2026-07-31 13:41:52'),
('2094', 'allow_negative_stock', '0', '2026-07-31 13:41:52'),
('2095', 'receipt_show_tax', '1', '2026-07-31 13:41:52'),
('2096', 'receipt_show_logo', '1', '2026-07-31 13:41:52'),
('2097', 'timezone', 'Asia/Colombo', '2026-07-31 13:41:52'),
('2098', 'activity_log_enabled', '1', '2026-07-31 13:41:52'),
('2099', 'activity_log_retention_days', '90', '2026-07-31 13:41:52'),
('2100', 'log_sales', '1', '2026-07-31 13:41:52'),
('2101', 'log_purchases', '1', '2026-07-31 13:41:52'),
('2102', 'log_expenses', '1', '2026-07-31 13:41:52'),
('2103', 'log_auth', '1', '2026-07-31 13:41:52'),
('2104', 'log_settings', '1', '2026-07-31 13:41:52'),
('2105', 'log_stock', '1', '2026-07-31 13:41:52'),
('2106', 'online_timeout_online', '2', '2026-07-31 13:41:52'),
('2107', 'online_timeout_away', '15', '2026-07-31 13:41:52'),
('2108', 'online_poll_interval', '5', '2026-07-31 13:41:52'),
('2109', 'show_online_users', '1', '2026-07-31 13:41:52'),
('2160', 'shop_logo', 'logo.jpg', '2026-07-31 13:45:56'),
('2161', 'site_icon', 'site-icon.jpg', '2026-07-31 13:45:56');

-- ------------------------------------------------
-- Table: `stock_movements`
-- ------------------------------------------------

DROP TABLE IF EXISTS `stock_movements`;
CREATE TABLE `stock_movements` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `product_id` int(10) unsigned NOT NULL,
  `batch_id` int(10) unsigned DEFAULT NULL,
  `movement_type` enum('opening','purchase','purchase_return','sale','sale_return','adjustment_add','adjustment_sub','transfer_in','transfer_out','damaged') NOT NULL,
  `quantity_in` decimal(12,2) NOT NULL DEFAULT 0.00,
  `quantity_out` decimal(12,2) NOT NULL DEFAULT 0.00,
  `stock_after` decimal(12,2) NOT NULL DEFAULT 0.00 COMMENT 'Running balance after this movement',
  `reference_no` varchar(50) DEFAULT NULL,
  `note` text DEFAULT NULL,
  `created_by` int(10) unsigned DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2054 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- ------------------------------------------------
-- Table: `supplier_payments`
-- ------------------------------------------------

DROP TABLE IF EXISTS `supplier_payments`;
CREATE TABLE `supplier_payments` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `supplier_id` int(10) unsigned NOT NULL,
  `purchase_id` int(10) unsigned DEFAULT NULL,
  `amount` decimal(12,2) NOT NULL,
  `payment_date` date NOT NULL,
  `payment_method` enum('cash','card','bank_transfer','mobile','cheque') DEFAULT 'cash',
  `reference` varchar(100) DEFAULT NULL,
  `note` text DEFAULT NULL,
  `created_by` int(10) unsigned DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=115 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- ------------------------------------------------
-- Table: `suppliers`
-- ------------------------------------------------

DROP TABLE IF EXISTS `suppliers`;
CREATE TABLE `suppliers` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `supplier_name` varchar(150) NOT NULL,
  `contact_person` varchar(100) DEFAULT NULL,
  `phone` varchar(20) DEFAULT NULL,
  `email` varchar(100) DEFAULT NULL,
  `address` text DEFAULT NULL,
  `opening_balance` decimal(12,2) NOT NULL DEFAULT 0.00,
  `current_balance` decimal(12,2) NOT NULL DEFAULT 0.00 COMMENT 'Outstanding payable',
  `status` enum('active','inactive') DEFAULT 'active',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=23 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- ------------------------------------------------
-- Table: `units`
-- ------------------------------------------------

DROP TABLE IF EXISTS `units`;
CREATE TABLE `units` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `unit_name` varchar(50) NOT NULL,
  `short_name` varchar(20) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- ------------------------------------------------
-- Table: `users`
-- ------------------------------------------------

DROP TABLE IF EXISTS `users`;
CREATE TABLE `users` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `role_id` int(10) unsigned NOT NULL,
  `branch_id` int(10) unsigned DEFAULT NULL,
  `name` varchar(100) NOT NULL,
  `username` varchar(50) NOT NULL,
  `email` varchar(100) DEFAULT NULL,
  `password_hash` varchar(255) NOT NULL,
  `phone` varchar(20) DEFAULT NULL,
  `avatar` varchar(255) DEFAULT NULL,
  `status` enum('active','inactive') DEFAULT 'active',
  `last_login` datetime DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `last_active` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `users` (`id`, `role_id`, `branch_id`, `name`, `username`, `email`, `password_hash`, `phone`, `avatar`, `status`, `last_login`, `created_at`, `updated_at`, `last_active`) VALUES
('1', '8', '1', 'Developer', 'damithmj', 'damithmjayalath2026@gmail.com', '$2y$12$EXRVb7fD0a1gmFilV0n.2OkVrCdvMCIjdALVo9ydRAKpWmvht1YIu', '0760150476', NULL, 'active', '2026-07-31 13:46:41', '2026-05-23 19:11:19', '2026-07-31 13:49:13', '2026-07-31 13:49:13'),
('2', '2', '1', 'Kussiya Admin', 'admin', 'venustradingpvtltd@gmail.com', '$2y$12$Cgy97kVWRV0Sd8tyySGS/.oA2r7x9yK256frkW8HEm059YH3SDxim', '+94 (78) 205 4006', NULL, 'active', '2026-07-31 11:52:34', '2026-05-23 19:11:19', '2026-07-31 17:35:14', '2026-07-31 17:35:14'),
('6', '4', '1', 'S.K.SHASHIKALA UDESHANI', 'Shashi123', 'shashikalaudeshani692@gmail.com', '$2y$12$x54m6bWqW6zw6j6mQ4Nyx.VuXKSo3wwaL86sX5yCbn8ksqgc2NM3G', '0756412823', NULL, 'inactive', '2026-07-31 11:43:10', '2026-07-01 15:28:20', '2026-07-31 13:47:27', '2026-07-31 17:13:55'),
('7', '3', '1', 'A.G.NIRUDAKA AKALANKA', 'NIRU123', 'nirudakaakalanka65@gmail.com', '$2y$12$TxXTW7w9HUZn.ESAJ79FW.yV1eO6bJPO1nNzqMsBO3uIaEqvQw216', '0719315823', NULL, 'inactive', '2026-07-28 16:32:20', '2026-07-09 05:06:52', '2026-07-31 13:47:14', '2026-07-28 22:04:09');

-- ------------------------------------------------
-- Table: `v_daily_sales`
-- ------------------------------------------------

DROP TABLE IF EXISTS `v_daily_sales`;
CREATE TABLE `v_daily_sales` (
  `sale_day` date DEFAULT NULL,
  `cashier_name` varchar(100) DEFAULT NULL,
  `invoice_count` bigint(21) DEFAULT NULL,
  `subtotal` decimal(34,2) DEFAULT NULL,
  `total_discount` decimal(34,2) DEFAULT NULL,
  `total_tax` decimal(34,2) DEFAULT NULL,
  `gross_total` decimal(34,2) DEFAULT NULL,
  `total_paid` decimal(34,2) DEFAULT NULL,
  `total_balance` decimal(34,2) DEFAULT NULL,
  `gross_profit` decimal(48,4) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;


-- ------------------------------------------------
-- Table: `v_expiry_alert`
-- ------------------------------------------------

DROP TABLE IF EXISTS `v_expiry_alert`;
CREATE TABLE `v_expiry_alert` (
  `batch_id` int(10) unsigned DEFAULT NULL,
  `batch_no` varchar(100) DEFAULT NULL,
  `expiry_date` date DEFAULT NULL,
  `quantity` decimal(12,2) DEFAULT NULL,
  `product_name` varchar(200) DEFAULT NULL,
  `product_code` varchar(50) DEFAULT NULL,
  `days_to_expiry` int(7) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;


-- ------------------------------------------------
-- Table: `v_low_stock`
-- ------------------------------------------------

DROP TABLE IF EXISTS `v_low_stock`;
CREATE TABLE `v_low_stock` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `product_code` varchar(50) DEFAULT NULL,
  `product_name` varchar(200) DEFAULT NULL,
  `current_stock` decimal(12,2) DEFAULT NULL,
  `reorder_level` int(11) DEFAULT NULL,
  `category_name` varchar(100) DEFAULT NULL,
  `unit` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;


-- ------------------------------------------------
-- Table: `v_stock_value`
-- ------------------------------------------------

DROP TABLE IF EXISTS `v_stock_value`;
CREATE TABLE `v_stock_value` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `product_code` varchar(50) DEFAULT NULL,
  `product_name` varchar(200) DEFAULT NULL,
  `current_stock` decimal(12,2) DEFAULT NULL,
  `cost_price` decimal(12,2) DEFAULT NULL,
  `selling_price` decimal(12,2) DEFAULT NULL,
  `cost_value` decimal(24,4) DEFAULT NULL,
  `retail_value` decimal(24,4) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;


SET FOREIGN_KEY_CHECKS = 1;

-- End of backup