-- https://www.phpmyadmin.net/
--
-- হোষ্ট: 127.0.0.1
-- তৈরী করতে ব্যবহৃত সময়: জুন 07, 2026 at 07:39 AM
-- সার্ভার সংস্করন: 10.4.32-MariaDB
-- পিএইছপির সংস্করন: 8.2.12

START TRANSACTION;



--
-- ডাটাবেইজ: `cash_management_db`
--

-- --------------------------------------------------------

--
-- টেবলের জন্য টেবলের গঠন `activity_logs`
--

CREATE TABLE `activity_logs` (
  `id` varchar(50) NOT NULL,
  `user_id` varchar(50) NOT NULL,
  `user_name` varchar(100) NOT NULL,
  `role` varchar(50) NOT NULL,
  `action` varchar(100) NOT NULL,
  `module` varchar(100) NOT NULL,
  `description` text NOT NULL,
  `old_value` text DEFAULT NULL,
  `new_value` text DEFAULT NULL,
  `ip_address` varchar(50) NOT NULL DEFAULT '127.0.0.1',
  `user_agent` varchar(255) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- টেবলের জন্য তথ্য স্তুপ করছি `activity_logs`
--

INSERT INTO `activity_logs` (`id`, `user_id`, `user_name`, `role`, `action`, `module`, `description`, `old_value`, `new_value`, `ip_address`, `user_agent`, `created_at`, `updated_at`) VALUES
('LOG-230813', 'USR-001', 'M. Ashikur Rahman', 'Super Admin', 'Delete Voucher', 'SuperAdmin', 'সুপার এডমিন কর্তৃক ভাউচার নং ADV-2026-870 সম্পূর্ণ ডিলিট করা হয়েছে। অ্যামাউন্ট ছিল: ৳50,000.00', NULL, '{\"id\":\"TX-273631\",\"company_id\":\"COMP-001\",\"branch_id\":\"BR-101\",\"voucher_no\":\"ADV-2026-870\",\"type\":\"Cash Out\",\"amount\":\"50000.00\",\"particulars\":\"Cash advance handed to employee: মোঃ আশিকুর রহমান for \\\"কাজের উদ্দেশ্য ও বিবরণ\\\"\",\"category\":\"Cash Advance Provided\",\"payment_method\":\"Cash\",\"bank_account_id\":null,\"reference_no\":null,\"attachment_name\":null,\"status\":\"Approved\",\"created_by_name\":\"M. Ashikur Rahman\",\"created_by_id\":\"USR-001\",\"approval_notes\":null,\"created_at\":\"2026-06-02T10:08:27.000000Z\",\"updated_at\":\"2026-06-02T10:08:27.000000Z\"}', '127.0.0.1', 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/148.0.0.0 Safari/537.36', '2026-06-02 04:17:59', '2026-06-02 04:17:59'),
('LOG-336421', 'USR-001', 'M. Ashikur Rahman', 'Super Admin', 'Update User', 'Users & Roles', 'ইউজার তথ্য এডিট করা হয়েছেঃ M. Ashikur Rahman (Super Admin)', NULL, '{\"id\":\"USR-001\",\"company_id\":\"COMP-001\",\"branch_id\":\"BR-101\",\"name\":\"M. Ashikur Rahman\",\"email\":\"mdashikurcse@gmail.com\",\"role\":\"Super Admin\",\"is_active\":1,\"photo_url\":\"https:\\/\\/images.unsplash.com\\/photo-1535713875002-d1d0cf377fde?auto=format&fit=crop&w=150&q=80\",\"phone\":\"+8801700000001\",\"designation\":\"SaaS platform Architect\",\"bio\":\"Head of global cash flow, cloud deployment & multi-tenant security structures.\",\"created_at\":\"2026-06-02T05:59:59.000000Z\",\"updated_at\":\"2026-06-02T05:59:59.000000Z\"}', '127.0.0.1', 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/148.0.0.0 Safari/537.36', '2026-06-02 02:15:13', '2026-06-02 02:15:13'),
('LOG-379174', 'USR-001', 'M. Ashikur Rahman', 'Super Admin', 'Delete Voucher', 'SuperAdmin', 'সুপার এডমিন কর্তৃক ভাউচার নং VCH-2026-779 সম্পূর্ণ ডিলিট করা হয়েছে। অ্যামাউন্ট ছিল: ৳50,000.00', NULL, '{\"id\":\"TX-141267\",\"company_id\":\"COMP-001\",\"branch_id\":\"BR-101\",\"voucher_no\":\"VCH-2026-779\",\"type\":\"Cash In\",\"amount\":\"50000.00\",\"particulars\":\"নতুন ক্যাশ ইন\",\"category\":\"অন্যান্যে চাহিদা বাবদ\",\"payment_method\":\"Cash\",\"bank_account_id\":null,\"reference_no\":\"0256\",\"attachment_name\":null,\"status\":\"Approved\",\"created_by_name\":\"M. Ashikur Rahman\",\"created_by_id\":\"USR-001\",\"approval_notes\":null,\"created_at\":\"2026-06-02T10:07:48.000000Z\",\"updated_at\":\"2026-06-02T10:07:48.000000Z\"}', '127.0.0.1', 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/148.0.0.0 Safari/537.36', '2026-06-02 04:18:01', '2026-06-02 04:18:01'),
('LOG-465469', 'USR-001', 'M. Ashikur Rahman', 'Super Admin', 'Delete Voucher', 'SuperAdmin', 'সুপার এডমিন কর্তৃক ভাউচার নং VCH-2026-167 সম্পূর্ণ ডিলিট করা হয়েছে। অ্যামাউন্ট ছিল: ৳50,000.00', NULL, '{\"id\":\"TX-288331\",\"company_id\":\"COMP-001\",\"branch_id\":\"BR-101\",\"voucher_no\":\"VCH-2026-167\",\"type\":\"Cash In\",\"amount\":\"50000.00\",\"particulars\":\"জমার নোট\",\"category\":\"Other Receipts\",\"payment_method\":\"Cash\",\"bank_account_id\":null,\"reference_no\":null,\"attachment_name\":null,\"status\":\"Approved\",\"created_by_name\":\"M. Ashikur Rahman\",\"created_by_id\":\"USR-001\",\"approval_notes\":null,\"created_at\":\"2026-06-02T09:20:53.000000Z\",\"updated_at\":\"2026-06-02T09:20:53.000000Z\"}', '127.0.0.1', 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/148.0.0.0 Safari/537.36', '2026-06-02 03:42:41', '2026-06-02 03:42:41'),
('LOG-501770', 'USR-001', 'M. Ashikur Rahman', 'Super Admin', 'Update Branch Settings', 'Settings', 'শাখার প্রোফাইল সেটিংস আপডেট করা হয়েছে।', NULL, '{\"id\":\"BR-101\",\"company_id\":\"COMP-001\",\"name\":\"Head Office (Dhaka)\",\"code\":\"HQ-DKA\",\"balance\":\"0.00\",\"opening_balance\":\"0\",\"manager_name\":\"Ariful Islam\",\"status\":\"Active\",\"created_at\":\"2026-06-02T05:59:59.000000Z\",\"updated_at\":\"2026-06-02T09:45:22.000000Z\"}', '127.0.0.1', 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/148.0.0.0 Safari/537.36', '2026-06-02 03:45:22', '2026-06-02 03:45:22'),
('LOG-618804', 'USR-001', 'M. Ashikur Rahman', 'Super Admin', 'Delete Voucher', 'SuperAdmin', 'সুপার এডমিন কর্তৃক ভাউচার নং REF-2026-825 সম্পূর্ণ ডিলিট করা হয়েছে। অ্যামাউন্ট ছিল: ৳500.00', NULL, '{\"id\":\"TX-142617\",\"company_id\":\"COMP-001\",\"branch_id\":\"BR-101\",\"voucher_no\":\"REF-2026-825\",\"type\":\"Cash In\",\"amount\":\"500.00\",\"particulars\":\"Surplus cash refunded from advance voucher: ADV-2026-842 by মোঃ আশিকুর রহমান\",\"category\":\"Advance Surplus Refund\",\"payment_method\":\"Cash\",\"bank_account_id\":null,\"reference_no\":null,\"attachment_name\":null,\"status\":\"Approved\",\"created_by_name\":\"M. Ashikur Rahman\",\"created_by_id\":\"USR-001\",\"approval_notes\":null,\"created_at\":\"2026-06-02T06:35:32.000000Z\",\"updated_at\":\"2026-06-02T06:35:32.000000Z\"}', '127.0.0.1', 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/148.0.0.0 Safari/537.36', '2026-06-02 03:42:48', '2026-06-02 03:42:48'),
('LOG-653466', 'USR-001', 'M. Ashikur Rahman', 'Super Admin', 'Shift Closing', 'Closing', 'শিফট ক্লোজিং সম্পন্ন করা হয়েছে। প্রকৃত ক্লোজিং ব্যালেন্সঃ ৳0.00 (অসমন্বয় অসঙ্গতিঃ ৳-1,291,250.00)', NULL, '{\"id\":\"CLS-929191\",\"company_id\":\"COMP-001\",\"branch_id\":\"BR-101\",\"date\":\"2026-06-02\",\"opening_balance\":\"1000000.00\",\"total_cash_in\":0,\"total_cash_out\":0,\"calculated_closing\":\"1291250.00\",\"actual_closing\":\"0\",\"mismatch_amount\":-1291250,\"notes\":null,\"closed_by_name\":\"M. Ashikur Rahman\",\"status\":\"Closed\",\"updated_at\":\"2026-06-02T09:44:46.000000Z\",\"created_at\":\"2026-06-02T09:44:46.000000Z\"}', '127.0.0.1', 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/148.0.0.0 Safari/537.36', '2026-06-02 03:44:46', '2026-06-02 03:44:46'),
('LOG-798237', 'USR-001', 'M. Ashikur Rahman', 'Super Admin', 'Delete Voucher', 'SuperAdmin', 'সুপার এডমিন কর্তৃক ভাউচার নং ADV-2026-842 সম্পূর্ণ ডিলিট করা হয়েছে। অ্যামাউন্ট ছিল: ৳3,500.00', NULL, '{\"id\":\"TX-375610\",\"company_id\":\"COMP-001\",\"branch_id\":\"BR-101\",\"voucher_no\":\"ADV-2026-842\",\"type\":\"Cash Out\",\"amount\":\"3500.00\",\"particulars\":\"Cash advance handed to employee: মোঃ আশিকুর রহমান for \\\"ফ্যান মেরামতের জন্য\\\"\",\"category\":\"Cash Advance Provided\",\"payment_method\":\"Cash\",\"bank_account_id\":null,\"reference_no\":null,\"attachment_name\":null,\"status\":\"Approved\",\"created_by_name\":\"M. Ashikur Rahman\",\"created_by_id\":\"USR-001\",\"approval_notes\":null,\"created_at\":\"2026-06-02T06:34:05.000000Z\",\"updated_at\":\"2026-06-02T06:34:05.000000Z\"}', '127.0.0.1', 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/148.0.0.0 Safari/537.36', '2026-06-02 03:42:55', '2026-06-02 03:42:55'),
('LOG-804575', 'USR-001', 'M. Ashikur Rahman', 'Super Admin', 'Delete Voucher', 'SuperAdmin', 'সুপার এডমিন কর্তৃক ভাউচার নং VCH-2026-601 সম্পূর্ণ ডিলিট করা হয়েছে। অ্যামাউন্ট ছিল: ৳12,500.00', NULL, '{\"id\":\"TX-201\",\"company_id\":\"COMP-001\",\"branch_id\":\"BR-101\",\"voucher_no\":\"VCH-2026-601\",\"type\":\"Cash In\",\"amount\":\"12500.00\",\"particulars\":\"Utility reimbursement refund received from tenant floor\",\"category\":\"Miscellaneous Revenue\",\"payment_method\":\"Cash\",\"bank_account_id\":null,\"reference_no\":\"REF-9923\",\"attachment_name\":null,\"status\":\"Approved\",\"created_by_name\":\"Anika Tabassum\",\"created_by_id\":\"USR-003\",\"approval_notes\":null,\"created_at\":\"2026-06-02T06:00:00.000000Z\",\"updated_at\":\"2026-06-02T06:00:00.000000Z\"}', '127.0.0.1', 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/148.0.0.0 Safari/537.36', '2026-06-02 03:43:07', '2026-06-02 03:43:07'),
('LOG-943212', 'USR-001', 'M. Ashikur Rahman', 'Super Admin', 'Delete Voucher', 'SuperAdmin', 'সুপার এডমিন কর্তৃক ভাউচার নং VCH-2026-602 সম্পূর্ণ ডিলিট করা হয়েছে। অ্যামাউন্ট ছিল: ৳45,000.00', NULL, '{\"id\":\"TX-202\",\"company_id\":\"COMP-001\",\"branch_id\":\"BR-101\",\"voucher_no\":\"VCH-2026-602\",\"type\":\"Cash Out\",\"amount\":\"45000.00\",\"particulars\":\"Purchase of server UPS backup for HQ rack server\",\"category\":\"Office Equipment\",\"payment_method\":\"Cash\",\"bank_account_id\":null,\"reference_no\":\"REF-1122\",\"attachment_name\":null,\"status\":\"Approved\",\"created_by_name\":\"Anika Tabassum\",\"created_by_id\":\"USR-003\",\"approval_notes\":null,\"created_at\":\"2026-06-02T06:00:00.000000Z\",\"updated_at\":\"2026-06-02T06:00:00.000000Z\"}', '127.0.0.1', 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/148.0.0.0 Safari/537.36', '2026-06-02 03:43:01', '2026-06-02 03:43:01');

-- --------------------------------------------------------

--
-- টেবলের জন্য টেবলের গঠন `advance_adjustments`
--

CREATE TABLE `advance_adjustments` (
  `id` varchar(50) NOT NULL,
  `cash_advance_id` varchar(50) NOT NULL,
  `expense_category` varchar(100) NOT NULL,
  `amount` decimal(15,2) NOT NULL,
  `description` text NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- টেবলের জন্য তথ্য স্তুপ করছি `advance_adjustments`
--

INSERT INTO `advance_adjustments` (`id`, `cash_advance_id`, `expense_category`, `amount`, `description`, `created_at`, `updated_at`) VALUES
('ADJ-4272', 'ADV-2638', 'Office Stationary', 3000.00, 'ghhj', '2026-06-02 04:35:03', '2026-06-02 04:35:03'),
('ADJ-6523', 'ADV-8714', 'Office Stationary', 3000.00, 'jhjh', '2026-06-02 04:33:20', '2026-06-02 04:33:20');

-- --------------------------------------------------------

--
-- টেবলের জন্য টেবলের গঠন `bank_accounts`
--

CREATE TABLE `bank_accounts` (
  `id` varchar(50) NOT NULL,
  `company_id` varchar(50) NOT NULL,
  `bank_name` varchar(150) NOT NULL,
  `account_name` varchar(150) NOT NULL,
  `account_number` varchar(100) NOT NULL,
  `branch` varchar(150) NOT NULL,
  `balance` decimal(15,2) NOT NULL DEFAULT 0.00,
  `status` enum('Active','Inactive') NOT NULL DEFAULT 'Active',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- টেবলের জন্য তথ্য স্তুপ করছি `bank_accounts`
--

INSERT INTO `bank_accounts` (`id`, `company_id`, `bank_name`, `account_name`, `account_number`, `branch`, `balance`, `status`, `created_at`, `updated_at`) VALUES
('BNK-001', 'COMP-001', 'Sonali Bank PLC', 'Teletalk Principal Operating', '0021-992384-012', 'Motijheel Corporate Branch', 0.00, 'Active', '2026-06-02 00:00:00', '2026-06-02 03:56:21'),
('BNK-002', 'COMP-001', 'Dutch-Bangla Bank', 'Teletalk Petty Cash Pool', '122.105.992834', 'Gulshan Branch', 0.00, 'Active', '2026-06-02 00:00:00', '2026-06-02 03:56:27');

-- --------------------------------------------------------

--
-- টেবলের জন্য টেবলের গঠন `branches`
--

CREATE TABLE `branches` (
  `id` varchar(50) NOT NULL,
  `company_id` varchar(50) NOT NULL,
  `name` varchar(150) NOT NULL,
  `code` varchar(50) NOT NULL,
  `balance` decimal(15,2) NOT NULL DEFAULT 0.00,
  `opening_balance` decimal(15,2) NOT NULL DEFAULT 0.00,
  `manager_name` varchar(100) DEFAULT NULL,
  `status` enum('Active','Inactive') NOT NULL DEFAULT 'Active',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- টেবলের জন্য তথ্য স্তুপ করছি `branches`
--

INSERT INTO `branches` (`id`, `company_id`, `name`, `code`, `balance`, `opening_balance`, `manager_name`, `status`, `created_at`, `updated_at`) VALUES
('BR-101', 'COMP-001', 'Head Office (Dhaka)', 'HQ-DKA', 94000.00, 0.00, 'Ariful Islam', 'Active', '2026-06-01 23:59:59', '2026-06-02 04:34:20');

-- --------------------------------------------------------

--
-- টেবলের জন্য টেবলের গঠন `cash_advances`
--

CREATE TABLE `cash_advances` (
  `id` varchar(50) NOT NULL,
  `company_id` varchar(50) NOT NULL,
  `branch_id` varchar(50) NOT NULL,
  `voucher_no` varchar(100) NOT NULL,
  `employee_name` varchar(150) NOT NULL,
  `employee_designation` varchar(100) DEFAULT NULL,
  `employee_department` varchar(100) DEFAULT NULL,
  `employee_phone` varchar(50) DEFAULT NULL,
  `purpose` text NOT NULL,
  `advance_amount` decimal(15,2) NOT NULL DEFAULT 0.00,
  `adjusted_amount` decimal(15,2) NOT NULL DEFAULT 0.00,
  `cash_refunded` decimal(15,2) NOT NULL DEFAULT 0.00,
  `cash_due_paid` decimal(15,2) NOT NULL DEFAULT 0.00,
  `status` enum('Pending','Partially Adjusted','Fully Adjusted') NOT NULL DEFAULT 'Pending',
  `created_by_id` varchar(50) NOT NULL,
  `created_by_name` varchar(100) NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- টেবলের জন্য তথ্য স্তুপ করছি `cash_advances`
--

INSERT INTO `cash_advances` (`id`, `company_id`, `branch_id`, `voucher_no`, `employee_name`, `employee_designation`, `employee_department`, `employee_phone`, `purpose`, `advance_amount`, `adjusted_amount`, `cash_refunded`, `cash_due_paid`, `status`, `created_by_id`, `created_by_name`, `created_at`, `updated_at`) VALUES
('ADV-2638', 'COMP-001', 'BR-101', 'ADV-2026-924', 'adff2w2', 'qas', NULL, '0015866666', 'rgvregv', 3000.00, 3000.00, 0.00, 0.00, 'Fully Adjusted', 'USR-001', 'M. Ashikur Rahman', '2026-06-02 04:34:20', '2026-06-02 04:35:03'),
('ADV-8714', 'COMP-001', 'BR-101', 'ADV-2026-595', 'adff', 'qas', NULL, '0015866666', 'jhjuu', 3000.00, 3000.00, 0.00, 0.00, 'Fully Adjusted', 'USR-001', 'M. Ashikur Rahman', '2026-06-02 04:32:31', '2026-06-02 04:33:20');

-- --------------------------------------------------------

--
-- টেবলের জন্য টেবলের গঠন `cash_transactions`
--

CREATE TABLE `cash_transactions` (
  `id` varchar(50) NOT NULL,
  `company_id` varchar(50) NOT NULL,
  `branch_id` varchar(50) NOT NULL,
  `voucher_no` varchar(100) NOT NULL,
  `type` enum('Cash In','Cash Out','Transfer','Contra','Journal','Expense','Income','Deposit','Withdrawal') NOT NULL,
  `amount` decimal(15,2) NOT NULL DEFAULT 0.00,
  `particulars` text NOT NULL,
  `category` varchar(100) NOT NULL,
  `payment_method` enum('Cash','Bank','Cheque') NOT NULL DEFAULT 'Cash',
  `bank_account_id` varchar(50) DEFAULT NULL,
  `reference_no` varchar(100) DEFAULT NULL,
  `attachment_name` varchar(255) DEFAULT NULL,
  `status` enum('Pending','Approved','Completed','Rejected') NOT NULL DEFAULT 'Pending',
  `created_by_name` varchar(100) NOT NULL,
  `created_by_id` varchar(50) NOT NULL,
  `approval_notes` text DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- টেবলের জন্য তথ্য স্তুপ করছি `cash_transactions`
--

INSERT INTO `cash_transactions` (`id`, `company_id`, `branch_id`, `voucher_no`, `type`, `amount`, `particulars`, `category`, `payment_method`, `bank_account_id`, `reference_no`, `attachment_name`, `status`, `created_by_name`, `created_by_id`, `approval_notes`, `created_at`, `updated_at`) VALUES
('TX-209628', 'COMP-001', 'BR-101', 'ADV-2026-595', 'Cash Out', 3000.00, 'Cash advance handed to employee: adff for \"jhjuu\"', 'Cash Advance Provided', 'Cash', NULL, NULL, NULL, 'Approved', 'M. Ashikur Rahman', 'USR-001', NULL, '2026-06-02 04:32:31', '2026-06-02 04:32:31'),
('TX-212804', 'COMP-001', 'BR-101', 'ADV-2026-924', 'Cash Out', 3000.00, 'Cash advance handed to employee: adff2w2 for \"rgvregv\"', 'Cash Advance Provided', 'Cash', NULL, NULL, NULL, 'Approved', 'M. Ashikur Rahman', 'USR-001', NULL, '2026-06-02 04:34:20', '2026-06-02 04:34:20'),
('TX-986321', 'COMP-001', 'BR-101', 'VCH-2026-347', 'Cash In', 100000.00, '20/5/2026', 'অন্যান্যে চাহিদা বাবদ', 'Cash', NULL, NULL, NULL, 'Approved', 'M. Ashikur Rahman', 'USR-001', NULL, '2026-06-02 04:31:22', '2026-06-02 04:31:22');

-- --------------------------------------------------------

--
-- টেবলের জন্য টেবলের গঠন `categories`
--

CREATE TABLE `categories` (
  `id` varchar(50) NOT NULL,
  `company_id` varchar(50) DEFAULT NULL,
  `name` varchar(150) NOT NULL,
  `type` enum('Income','Expense') NOT NULL,
  `status` enum('Active','Inactive') NOT NULL DEFAULT 'Active',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- টেবলের জন্য তথ্য স্তুপ করছি `categories`
--

INSERT INTO `categories` (`id`, `company_id`, `name`, `type`, `status`, `created_at`, `updated_at`) VALUES
('CAT-1894', 'COMP-001', 'চাহিদা', 'Expense', 'Active', '2026-06-02 04:02:41', '2026-06-02 04:02:41'),
('CAT-2116', 'COMP-001', 'অন্যান্যে চাহিদা বাবদ', 'Income', 'Active', '2026-06-02 04:07:17', '2026-06-02 04:07:17'),
('CAT-2582', 'COMP-001', 'নতুন কিছু ক্রয় বাবদ', 'Expense', 'Active', '2026-06-02 04:02:05', '2026-06-02 04:02:05'),
('CAT-5124', 'COMP-001', 'অগ্রিম বাবদ', 'Expense', 'Active', '2026-06-02 04:02:26', '2026-06-02 04:02:26'),
('CAT-6125', 'COMP-001', 'মেরামত', 'Expense', 'Active', '2026-06-02 04:01:27', '2026-06-02 04:01:27');

-- --------------------------------------------------------

--
-- টেবলের জন্য টেবলের গঠন `companies`
--

CREATE TABLE `companies` (
  `id` varchar(50) NOT NULL,
  `name` varchar(150) NOT NULL,
  `logo` varchar(10) NOT NULL DEFAULT '?',
  `subscription` enum('Basic','Enterprise','Premium') NOT NULL DEFAULT 'Basic',
  `status` enum('Active','Inactive') NOT NULL DEFAULT 'Active',
  `phone` varchar(30) DEFAULT NULL,
  `email` varchar(150) DEFAULT NULL,
  `address` text DEFAULT NULL,
  `currency` varchar(10) NOT NULL DEFAULT '৳',
  `tax_number` varchar(50) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- টেবলের জন্য তথ্য স্তুপ করছি `companies`
--

INSERT INTO `companies` (`id`, `name`, `logo`, `subscription`, `status`, `phone`, `email`, `address`, `currency`, `tax_number`, `created_at`, `updated_at`) VALUES
('COMP-001', 'Teletalk Corp Ltd', '⚡', 'Enterprise', 'Active', '+88029555111', 'info@teletalkcorp.com', 'Gulshan-2, Dhaka 1212', '৳', 'BIN-19284756302', '2026-06-01 23:59:59', '2026-06-01 23:59:59'),
('COMP-002', 'Globe Freight Systems', '🌐', 'Premium', 'Active', '+14155552671', 'finance@globefreight.com', 'Sansom St, San Francisco, CA', '$', 'EIN-99238812', '2026-06-01 23:59:59', '2026-06-01 23:59:59');

-- --------------------------------------------------------

--
-- টেবলের জন্য টেবলের গঠন `departments`
--

CREATE TABLE `departments` (
  `id` varchar(50) NOT NULL,
  `company_id` varchar(50) DEFAULT NULL,
  `name` varchar(150) NOT NULL,
  `status` enum('Active','Inactive') NOT NULL DEFAULT 'Active',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- টেবলের জন্য তথ্য স্তুপ করছি `departments`
--

INSERT INTO `departments` (`id`, `company_id`, `name`, `status`, `created_at`, `updated_at`) VALUES
('DEPT-001', 'COMP-001', 'Finance & Accounts', 'Active', '2026-06-02 04:46:35', '2026-06-02 04:46:35'),
('DEPT-002', 'COMP-001', 'Human Resources (HR)', 'Active', '2026-06-02 04:46:35', '2026-06-02 04:46:35'),
('DEPT-003', 'COMP-001', 'Sales & Marketing', 'Active', '2026-06-02 04:46:35', '2026-06-02 04:46:35'),
('DEPT-004', 'COMP-001', 'IT & Support', 'Active', '2026-06-02 04:46:35', '2026-06-02 04:46:35'),
('DEPT-005', 'COMP-001', 'MIS', 'Active', '2026-06-02 04:46:35', '2026-06-02 04:47:17');

-- --------------------------------------------------------

--
-- টেবলের জন্য টেবলের গঠন `migrations`
--

CREATE TABLE `migrations` (
  `id` int(10) UNSIGNED NOT NULL,
  `migration` varchar(255) NOT NULL,
  `batch` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- টেবলের জন্য তথ্য স্তুপ করছি `migrations`
--

INSERT INTO `migrations` (`id`, `migration`, `batch`) VALUES
(1, '2206_01_01_000000_create_cash_management_tables', 1);

-- --------------------------------------------------------

--
-- টেবলের জন্য টেবলের গঠন `sessions`
--

CREATE TABLE `sessions` (
  `id` varchar(255) NOT NULL,
  `user_id` varchar(50) DEFAULT NULL,
  `ip_address` varchar(45) DEFAULT NULL,
  `user_agent` text DEFAULT NULL,
  `payload` longtext NOT NULL,
  `last_activity` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- টেবলের জন্য টেবলের গঠন `shift_closings`
--

CREATE TABLE `shift_closings` (
  `id` varchar(50) NOT NULL,
  `company_id` varchar(50) NOT NULL,
  `branch_id` varchar(50) NOT NULL,
  `date` date NOT NULL,
  `opening_balance` decimal(15,2) NOT NULL DEFAULT 0.00,
  `total_cash_in` decimal(15,2) NOT NULL DEFAULT 0.00,
  `total_cash_out` decimal(15,2) NOT NULL DEFAULT 0.00,
  `calculated_closing` decimal(15,2) NOT NULL DEFAULT 0.00,
  `actual_closing` decimal(15,2) NOT NULL DEFAULT 0.00,
  `mismatch_amount` decimal(15,2) NOT NULL DEFAULT 0.00,
  `notes` text DEFAULT NULL,
  `closed_by_name` varchar(100) NOT NULL,
  `status` enum('Open','Closed') NOT NULL DEFAULT 'Closed',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- টেবলের জন্য তথ্য স্তুপ করছি `shift_closings`
--

INSERT INTO `shift_closings` (`id`, `company_id`, `branch_id`, `date`, `opening_balance`, `total_cash_in`, `total_cash_out`, `calculated_closing`, `actual_closing`, `mismatch_amount`, `notes`, `closed_by_name`, `status`, `created_at`, `updated_at`) VALUES
('CLS-929191', 'COMP-001', 'BR-101', '2026-06-02', 1000000.00, 0.00, 0.00, 1291250.00, 0.00, -1291250.00, NULL, 'M. Ashikur Rahman', 'Closed', '2026-06-02 03:44:46', '2026-06-02 03:44:46');

--
-- স্তুপকৃত টেবলের ইনডেক্স
--

--
-- টেবিলের ইনডেক্সসমুহ `activity_logs`
--
ALTER TABLE `activity_logs`
  ADD PRIMARY KEY (`id`);

--
-- টেবিলের ইনডেক্সসমুহ `advance_adjustments`
--
ALTER TABLE `advance_adjustments`
  ADD PRIMARY KEY (`id`),
  ADD KEY `advance_adjustments_cash_advance_id_foreign` (`cash_advance_id`);

--
-- টেবিলের ইনডেক্সসমুহ `bank_accounts`
--
ALTER TABLE `bank_accounts`
  ADD PRIMARY KEY (`id`),
  ADD KEY `bank_accounts_company_id_foreign` (`company_id`);

--
-- টেবিলের ইনডেক্সসমুহ `branches`
--
ALTER TABLE `branches`
  ADD PRIMARY KEY (`id`),
  ADD KEY `branches_company_id_foreign` (`company_id`);

--
-- টেবিলের ইনডেক্সসমুহ `cash_advances`
--
ALTER TABLE `cash_advances`
  ADD PRIMARY KEY (`id`),
  ADD KEY `cash_advances_company_id_foreign` (`company_id`),
  ADD KEY `cash_advances_branch_id_foreign` (`branch_id`);

--
-- টেবিলের ইনডেক্সসমুহ `cash_transactions`
--
ALTER TABLE `cash_transactions`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `cash_transactions_voucher_no_unique` (`voucher_no`),
  ADD KEY `cash_transactions_company_id_foreign` (`company_id`),
  ADD KEY `cash_transactions_branch_id_foreign` (`branch_id`),
  ADD KEY `cash_transactions_bank_account_id_foreign` (`bank_account_id`);

--
-- টেবিলের ইনডেক্সসমুহ `categories`
--
ALTER TABLE `categories`
  ADD PRIMARY KEY (`id`),
  ADD KEY `categories_company_id_foreign` (`company_id`);

--
-- টেবিলের ইনডেক্সসমুহ `companies`
--
ALTER TABLE `companies`
  ADD PRIMARY KEY (`id`);

--
-- টেবিলের ইনডেক্সসমুহ `departments`
--
ALTER TABLE `departments`
  ADD PRIMARY KEY (`id`),
  ADD KEY `departments_company_id_foreign` (`company_id`);

--
-- টেবিলের ইনডেক্সসমুহ `migrations`
--
ALTER TABLE `migrations`
  ADD PRIMARY KEY (`id`);

--
-- টেবিলের ইনডেক্সসমুহ `sessions`
--
ALTER TABLE `sessions`
  ADD PRIMARY KEY (`id`),
  ADD KEY `sessions_user_id_index` (`user_id`),
  ADD KEY `sessions_last_activity_index` (`last_activity`);

--
-- টেবিলের ইনডেক্সসমুহ `shift_closings`
--
ALTER TABLE `shift_closings`
  ADD PRIMARY KEY (`id`),
  ADD KEY `shift_closings_company_id_foreign` (`company_id`),
  ADD KEY `shift_closings_branch_id_foreign` (`branch_id`);

--
-- স্তুপকৃত টেবলের জন্য স্বয়ক্রীয় বর্দ্ধিত মান (AUTO_INCREMENT)
--

--
-- টেবলের জন্য স্বয়ক্রীয় বর্দ্ধিত মান (AUTO_INCREMENT) `migrations`
--
ALTER TABLE `migrations`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2;

--
-- স্তুপকৃত টেবলের সীমবদ্ধতা
--

--
-- টেবলের সীমবদ্ধতা `advance_adjustments`
--
ALTER TABLE `advance_adjustments`
  ADD CONSTRAINT `advance_adjustments_cash_advance_id_foreign` FOREIGN KEY (`cash_advance_id`) REFERENCES `cash_advances` (`id`) ON DELETE CASCADE;

--
-- টেবলের সীমবদ্ধতা `bank_accounts`
--
ALTER TABLE `bank_accounts`
  ADD CONSTRAINT `bank_accounts_company_id_foreign` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE CASCADE;

--
-- টেবলের সীমবদ্ধতা `branches`
--
ALTER TABLE `branches`
  ADD CONSTRAINT `branches_company_id_foreign` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE CASCADE;

--
-- টেবলের সীমবদ্ধতা `cash_advances`
--
ALTER TABLE `cash_advances`
  ADD CONSTRAINT `cash_advances_branch_id_foreign` FOREIGN KEY (`branch_id`) REFERENCES `branches` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `cash_advances_company_id_foreign` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE CASCADE;

--
-- টেবলের সীমবদ্ধতা `cash_transactions`
--
ALTER TABLE `cash_transactions`
  ADD CONSTRAINT `cash_transactions_bank_account_id_foreign` FOREIGN KEY (`bank_account_id`) REFERENCES `bank_accounts` (`id`) ON DELETE SET NULL,
  ADD CONSTRAINT `cash_transactions_branch_id_foreign` FOREIGN KEY (`branch_id`) REFERENCES `branches` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `cash_transactions_company_id_foreign` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE CASCADE;

--
-- টেবলের সীমবদ্ধতা `categories`
--
ALTER TABLE `categories`
  ADD CONSTRAINT `categories_company_id_foreign` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE CASCADE;

--
-- টেবলের সীমবদ্ধতা `departments`
--
ALTER TABLE `departments`
  ADD CONSTRAINT `departments_company_id_foreign` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE CASCADE;

--
-- টেবলের সীমবদ্ধতা `shift_closings`
--
ALTER TABLE `shift_closings`
  ADD CONSTRAINT `shift_closings_branch_id_foreign` FOREIGN KEY (`branch_id`) REFERENCES `branches` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `shift_closings_company_id_foreign` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE CASCADE;
COMMIT;

