-- phpMyAdmin SQL Dump
-- version 5.2.1
-- https://www.phpmyadmin.net/
--
-- Máy chủ: 127.0.0.1
-- Thời gian đã tạo: Th3 11, 2026 lúc 04:56 PM
-- Phiên bản máy phục vụ: 10.4.32-MariaDB
-- Phiên bản PHP: 8.2.12

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
START TRANSACTION;
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;

--
-- Cơ sở dữ liệu: `erp_system`
--

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

--
-- Cấu trúc bảng cho bảng `attendance_audit_logs`
--

CREATE TABLE `attendance_audit_logs` (
  `id` int(11) NOT NULL,
  `attendance_log_id` int(11) DEFAULT NULL,
  `changed_by` int(11) DEFAULT NULL,
  `change_type` varchar(50) DEFAULT 'manual_edit',
  `old_check_in` datetime DEFAULT NULL,
  `old_check_out` datetime DEFAULT NULL,
  `new_check_in` datetime DEFAULT NULL,
  `new_check_out` datetime DEFAULT NULL,
  `note` text DEFAULT NULL,
  `created_at` datetime DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Đang đổ dữ liệu cho bảng `attendance_audit_logs`
--

INSERT INTO `attendance_audit_logs` (`id`, `attendance_log_id`, `changed_by`, `change_type`, `old_check_in`, `old_check_out`, `new_check_in`, `new_check_out`, `note`, `created_at`) VALUES
(1, 8, 1, 'manual_edit', NULL, NULL, '2026-03-11 09:01:00', '2026-03-11 17:01:00', '', '2026-03-11 13:31:41'),
(2, 7, 1, 'manual_edit', NULL, NULL, NULL, NULL, 'Xóa bản ghi', '2026-03-11 13:32:03'),
(3, 7, 1, 'manual_edit', NULL, NULL, '2026-03-11 03:00:00', NULL, '', '2026-03-11 13:32:14'),
(4, 8, 1, 'manual_edit', NULL, NULL, '2026-03-11 09:01:00', '2026-03-11 17:01:00', '', '2026-03-11 13:39:19'),
(5, 7, 1, 'manual_edit', NULL, NULL, '2026-03-11 09:00:00', '2026-03-11 17:00:00', '', '2026-03-11 13:40:24'),
(6, 9, 1, 'manual_edit', NULL, NULL, '2026-03-10 07:30:00', '2026-03-10 16:00:00', '', '2026-03-11 22:30:43');

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

--
-- Cấu trúc bảng cho bảng `attendance_logs`
--

CREATE TABLE `attendance_logs` (
  `id` bigint(20) NOT NULL,
  `user_id` int(11) NOT NULL,
  `check_in` datetime DEFAULT NULL,
  `check_out` datetime DEFAULT NULL,
  `work_date` date NOT NULL,
  `shift_id` int(11) DEFAULT NULL,
  `work_hours` decimal(5,2) DEFAULT 0.00,
  `source` enum('machine','manual','system') DEFAULT 'manual',
  `note` text DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `is_late` tinyint(1) DEFAULT 0,
  `late_minutes` int(11) DEFAULT 0,
  `early_leave` tinyint(1) DEFAULT 0,
  `early_leave_minutes` int(11) DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Đang đổ dữ liệu cho bảng `attendance_logs`
--

INSERT INTO `attendance_logs` (`id`, `user_id`, `check_in`, `check_out`, `work_date`, `shift_id`, `work_hours`, `source`, `note`, `created_at`, `updated_at`, `is_late`, `late_minutes`, `early_leave`, `early_leave_minutes`) VALUES
(1, 6, '2026-03-04 08:02:00', '2026-03-04 17:05:00', '2026-03-04', NULL, 9.05, 'manual', NULL, '2026-03-10 06:57:59', '2026-03-10 06:57:59', 0, 0, 0, 0),
(2, 6, '2026-03-05 07:58:00', '2026-03-05 17:00:00', '2026-03-05', NULL, 9.03, 'manual', NULL, '2026-03-10 06:57:59', '2026-03-10 06:57:59', 0, 0, 0, 0),
(3, 6, '2026-03-06 08:10:00', '2026-03-06 17:30:00', '2026-03-06', NULL, 9.33, 'manual', NULL, '2026-03-10 06:57:59', '2026-03-10 06:57:59', 0, 0, 0, 0),
(4, 6, '2026-03-09 08:00:00', '2026-03-09 17:00:00', '2026-03-09', NULL, 9.00, 'manual', NULL, '2026-03-10 06:57:59', '2026-03-10 06:57:59', 0, 0, 0, 0),
(5, 6, '2026-03-10 08:05:00', '2026-03-10 10:58:19', '2026-03-10', NULL, 2.88, 'manual', NULL, '2026-03-10 06:57:59', '2026-03-10 09:58:19', 0, 0, 0, 0),
(6, 1, '2026-03-10 10:47:33', '2026-03-10 10:48:23', '2026-03-10', NULL, 0.00, 'manual', NULL, '2026-03-10 09:47:33', '2026-03-10 09:48:23', 0, 0, 0, 0),
(7, 7, '2026-03-11 09:00:00', '2026-03-11 17:00:00', '2026-03-11', NULL, 8.00, 'manual', '', '2026-03-11 03:31:48', '2026-03-11 06:40:24', 1, 60, 0, 0),
(8, 8, '2026-03-11 09:01:00', '2026-03-11 17:01:00', '2026-03-11', NULL, 8.00, 'manual', '', '2026-03-11 04:01:16', '2026-03-11 06:39:19', 0, 0, 0, 0),
(9, 7, '2026-03-10 07:30:00', '2026-03-10 16:00:00', '2026-03-10', NULL, 8.50, 'manual', '', '2026-03-11 15:30:43', '2026-03-11 15:30:43', 0, 0, 1, 60);

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

--
-- Cấu trúc bảng cho bảng `audit_log`
--

CREATE TABLE `audit_log` (
  `id` int(11) NOT NULL,
  `table_name` varchar(50) NOT NULL,
  `record_id` int(11) NOT NULL,
  `action` enum('create','update','delete') NOT NULL,
  `changed_by` int(11) DEFAULT NULL,
  `changed_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `old_data` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT 'Dữ liệu trước khi sửa' CHECK (json_valid(`old_data`)),
  `new_data` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT 'Dữ liệu sau khi sửa' CHECK (json_valid(`new_data`)),
  `note` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='Lịch sử sửa/xóa toàn hệ thống';

--
-- Đang đổ dữ liệu cho bảng `audit_log`
--

INSERT INTO `audit_log` (`id`, `table_name`, `record_id`, `action`, `changed_by`, `changed_at`, `old_data`, `new_data`, `note`) VALUES
(1, 'delivery_notes', 6, 'delete', 1, '2026-03-11 14:44:41', '{\"id\":6,\"delivery_no\":\"DN-20260311-0001\",\"delivery_date\":\"2026-03-11\",\"sender_name\":null,\"sender_phone\":null,\"vehicle_plate\":null,\"driver_name\":null,\"driver_phone\":null,\"customer_id\":1,\"total_amount\":\"0\",\"status\":\"confirmed\",\"note\":\"\",\"created_by\":1,\"created_at\":\"2026-03-11 21:10:53\",\"updated_at\":\"2026-03-11 21:10:53\",\"items\":[{\"id\":6,\"delivery_note_id\":6,\"production_output_id\":4,\"product_code_id\":1,\"description\":\"hàng ốc vít\",\"unit\":\"cái\",\"quantity\":\"2500.00\",\"unit_price\":\"0\",\"total_price\":\"0\",\"note\":null}]}', NULL, 'Xóa biên bản DN-20260311-0001'),
(2, 'delivery_notes', 5, 'delete', 1, '2026-03-11 14:44:44', '{\"id\":5,\"delivery_no\":\"GH-20260311-001\",\"delivery_date\":\"2026-03-11\",\"sender_name\":null,\"sender_phone\":null,\"vehicle_plate\":null,\"driver_name\":null,\"driver_phone\":null,\"customer_id\":1,\"total_amount\":\"0\",\"status\":\"confirmed\",\"note\":null,\"created_by\":5,\"created_at\":\"2026-03-11 20:15:07\",\"updated_at\":\"2026-03-11 20:15:07\",\"items\":[{\"id\":5,\"delivery_note_id\":5,\"production_output_id\":4,\"product_code_id\":1,\"description\":\"hàng ốc vít\",\"unit\":\"cái\",\"quantity\":\"2000.00\",\"unit_price\":\"0\",\"total_price\":\"0\",\"note\":null}]}', NULL, 'Xóa biên bản GH-20260311-001'),
(3, 'delivery_notes', 4, 'delete', 1, '2026-03-11 14:44:48', '{\"id\":4,\"delivery_no\":\"GH-20260310-001\",\"delivery_date\":\"2026-03-10\",\"sender_name\":null,\"sender_phone\":null,\"vehicle_plate\":null,\"driver_name\":null,\"driver_phone\":null,\"customer_id\":1,\"total_amount\":\"0\",\"status\":\"confirmed\",\"note\":null,\"created_by\":1,\"created_at\":\"2026-03-11 00:38:22\",\"updated_at\":\"2026-03-11 00:38:22\",\"items\":[{\"id\":4,\"delivery_note_id\":4,\"production_output_id\":3,\"product_code_id\":1,\"description\":\"hàng ốc vít\",\"unit\":\"cái\",\"quantity\":\"100.00\",\"unit_price\":\"0\",\"total_price\":\"0\",\"note\":null}]}', NULL, 'Xóa biên bản GH-20260310-001'),
(4, 'production_outputs', 4, 'delete', 1, '2026-03-11 14:44:54', '{\"id\":4,\"output_no\":\"OUT-20260311-001\",\"output_date\":\"2026-03-11\",\"production_receipt_id\":3,\"product_code_id\":1,\"description\":null,\"quantity_completed\":\"5000.00\",\"quantity_defect\":\"1000.00\",\"quantity_delivered\":\"0.00\",\"note\":null,\"created_by\":5,\"created_at\":\"2026-03-11 20:13:23\",\"updated_at\":\"2026-03-11 21:44:44\"}', NULL, 'Xóa output OUT-20260311-001'),
(5, 'production_receipts', 4, 'delete', 1, '2026-03-11 14:44:59', '{\"id\":4,\"receipt_no\":\"PR-20260311-0001\",\"receipt_date\":\"2026-03-11\",\"warehouse_import_id\":2,\"product_code_id\":1,\"description\":null,\"quantity_received\":\"80000.00\",\"note\":\"\",\"created_by\":1,\"created_at\":\"2026-03-11 21:10:15\",\"updated_at\":\"2026-03-11 21:10:15\"}', NULL, 'Xóa phiếu nhận PR-20260311-0001'),
(6, 'production_receipts', 3, 'delete', 1, '2026-03-11 14:45:00', '{\"id\":3,\"receipt_no\":\"SX-20260311-001\",\"receipt_date\":\"2026-03-11\",\"warehouse_import_id\":1,\"product_code_id\":1,\"description\":null,\"quantity_received\":\"8000.00\",\"note\":null,\"created_by\":5,\"created_at\":\"2026-03-11 20:12:47\",\"updated_at\":\"2026-03-11 20:12:47\"}', NULL, 'Xóa phiếu nhận SX-20260311-001'),
(7, 'warehouse_imports', 2, 'delete', 1, '2026-03-11 14:45:22', '{\"id\":2,\"import_no\":\"WH-20260311-001\",\"import_date\":\"2026-03-11\",\"product_code_id\":1,\"description\":\"hàng ốc vít\",\"quantity\":\"100000.00\",\"quantity_sent\":\"0.00\",\"note\":null,\"status\":\"pending\",\"created_by\":2,\"created_at\":\"2026-03-11 20:30:01\",\"updated_at\":\"2026-03-11 21:44:59\",\"product_code\":\"SP-01\"}', NULL, 'Xóa phiếu nhập WH-20260311-001'),
(8, 'warehouse_imports', 3, 'delete', 1, '2026-03-11 15:02:44', '{\"id\":3,\"import_no\":\"WI-20260311-0001\",\"import_date\":\"2026-03-11\",\"product_code_id\":1,\"description\":null,\"quantity\":\"100000.00\",\"quantity_sent\":\"100000.00\",\"note\":\"\",\"status\":\"completed\",\"created_by\":1,\"created_at\":\"2026-03-11 21:57:14\",\"updated_at\":\"2026-03-11 21:57:30\",\"product_code\":\"SP-01\"}', NULL, 'Xóa phiếu nhập WI-20260311-0001'),
(9, 'production_outputs', 5, 'create', 1, '2026-03-11 15:07:17', NULL, '{\"output_no\":\"OUT-20260311-001\",\"output_date\":\"2026-03-11\",\"production_receipt_id\":6,\"quantity_completed\":50000,\"quantity_defect\":0}', 'Tao output OUT-20260311-001');

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

--
-- Cấu trúc bảng cho bảng `communes`
--

CREATE TABLE `communes` (
  `code` varchar(10) NOT NULL,
  `name` varchar(100) NOT NULL,
  `district_code` varchar(10) NOT NULL,
  `full_name` varchar(150) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Cấu trúc bảng cho bảng `cost_entries`
--

CREATE TABLE `cost_entries` (
  `id` int(11) NOT NULL,
  `entry_date` date NOT NULL,
  `cost_type` enum('material','supplies','machinery','transport','other') NOT NULL DEFAULT 'material',
  `supplier_name` varchar(200) DEFAULT NULL,
  `description` text DEFAULT NULL,
  `quantity` decimal(15,2) DEFAULT 0.00,
  `unit` varchar(20) DEFAULT NULL,
  `unit_price` decimal(15,0) DEFAULT 0,
  `total_amount` decimal(15,0) DEFAULT 0,
  `invoice_no` varchar(100) DEFAULT NULL,
  `note` text DEFAULT NULL,
  `created_by` int(11) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Cấu trúc bảng cho bảng `customers`
--

CREATE TABLE `customers` (
  `id` int(11) NOT NULL,
  `customer_code` varchar(30) DEFAULT NULL,
  `customer_name` varchar(200) NOT NULL,
  `address` text DEFAULT NULL,
  `contact_person` varchar(100) DEFAULT NULL,
  `phone` varchar(20) DEFAULT NULL,
  `email` varchar(100) DEFAULT NULL,
  `is_active` tinyint(1) DEFAULT 1,
  `created_by` int(11) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Đang đổ dữ liệu cho bảng `customers`
--

INSERT INTO `customers` (`id`, `customer_code`, `customer_name`, `address`, `contact_person`, `phone`, `email`, `is_active`, `created_by`, `created_at`, `updated_at`) VALUES
(1, 'KH01', 'công ty NTN', 'hà Nội', 'dung', '1111111', 'dung@da', 1, 1, '2026-03-10 17:10:45', '2026-03-10 17:10:45');

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

--
-- Cấu trúc bảng cho bảng `day_close_log`
--

CREATE TABLE `day_close_log` (
  `id` int(11) NOT NULL,
  `close_date` date NOT NULL,
  `close_type` enum('manual','auto') NOT NULL DEFAULT 'manual',
  `qty_completed_returned` decimal(15,2) NOT NULL DEFAULT 0.00,
  `qty_defect_returned` decimal(15,2) NOT NULL DEFAULT 0.00,
  `qty_pending_returned` decimal(15,2) NOT NULL DEFAULT 0.00,
  `closed_by` int(11) DEFAULT NULL,
  `note` text DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Đang đổ dữ liệu cho bảng `day_close_log`
--

INSERT INTO `day_close_log` (`id`, `close_date`, `close_type`, `qty_completed_returned`, `qty_defect_returned`, `qty_pending_returned`, `closed_by`, `note`, `created_at`) VALUES
(1, '2026-03-11', 'manual', 0.00, 0.00, 80000.00, 1, NULL, '2026-03-11 14:25:03');

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

--
-- Cấu trúc bảng cho bảng `debt_payments`
--

CREATE TABLE `debt_payments` (
  `id` int(11) NOT NULL,
  `debt_id` int(11) NOT NULL,
  `invoice_id` int(11) DEFAULT NULL,
  `payment_date` date NOT NULL,
  `amount` decimal(15,0) NOT NULL DEFAULT 0,
  `payment_method` enum('cash','transfer','other') DEFAULT 'transfer',
  `reference_no` varchar(100) DEFAULT NULL,
  `note` text DEFAULT NULL,
  `created_by` int(11) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Cấu trúc bảng cho bảng `debt_tracking`
--

CREATE TABLE `debt_tracking` (
  `id` int(11) NOT NULL,
  `invoice_id` int(11) NOT NULL,
  `customer_id` int(11) NOT NULL,
  `total_amount` decimal(15,0) NOT NULL DEFAULT 0,
  `paid_amount` decimal(15,0) NOT NULL DEFAULT 0,
  `remaining_amount` decimal(15,0) NOT NULL DEFAULT 0,
  `due_date` date DEFAULT NULL,
  `status` enum('unpaid','partial','paid') DEFAULT 'unpaid',
  `note` text DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Cấu trúc đóng vai cho view `deliveries`
-- (See below for the actual view)
--
CREATE TABLE `deliveries` (
`id` int(11)
,`delivery_no` varchar(30)
,`delivery_date` date
,`customer_id` int(11)
,`status` enum('draft','confirmed','invoiced')
,`note` text
,`created_by` int(11)
,`created_at` timestamp
,`updated_at` timestamp
,`total_amount` binary(0)
);

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

--
-- Cấu trúc đóng vai cho view `delivery_items`
-- (See below for the actual view)
--
CREATE TABLE `delivery_items` (
`id` int(11)
,`delivery_id` int(11)
,`production_output_id` int(11)
,`product_code_id` int(11)
,`description` varchar(500)
,`quantity` decimal(15,2)
,`unit_price` binary(0)
,`total_price` binary(0)
,`note` text
);

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

--
-- Cấu trúc bảng cho bảng `delivery_notes`
--

CREATE TABLE `delivery_notes` (
  `id` int(11) NOT NULL,
  `delivery_no` varchar(30) NOT NULL,
  `delivery_date` date NOT NULL,
  `sender_name` varchar(100) DEFAULT NULL,
  `sender_phone` varchar(20) DEFAULT NULL,
  `vehicle_plate` varchar(20) DEFAULT NULL,
  `driver_name` varchar(100) DEFAULT NULL,
  `driver_phone` varchar(20) DEFAULT NULL,
  `customer_id` int(11) NOT NULL,
  `total_amount` decimal(15,0) NOT NULL DEFAULT 0,
  `status` enum('draft','confirmed','invoiced') DEFAULT 'draft',
  `note` text DEFAULT NULL,
  `created_by` int(11) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Đang đổ dữ liệu cho bảng `delivery_notes`
--

INSERT INTO `delivery_notes` (`id`, `delivery_no`, `delivery_date`, `sender_name`, `sender_phone`, `vehicle_plate`, `driver_name`, `driver_phone`, `customer_id`, `total_amount`, `status`, `note`, `created_by`, `created_at`, `updated_at`) VALUES
(7, 'DN-20260311-0001', '2026-03-11', NULL, NULL, NULL, NULL, NULL, 1, 0, 'confirmed', '', 1, '2026-03-11 15:07:34', '2026-03-11 15:07:34');

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

--
-- Cấu trúc bảng cho bảng `delivery_note_items`
--

CREATE TABLE `delivery_note_items` (
  `id` int(11) NOT NULL,
  `delivery_note_id` int(11) NOT NULL,
  `production_output_id` int(11) NOT NULL,
  `product_code_id` int(11) NOT NULL,
  `description` varchar(500) DEFAULT NULL,
  `unit` varchar(50) DEFAULT NULL,
  `quantity` decimal(15,2) NOT NULL DEFAULT 0.00,
  `unit_price` decimal(15,0) NOT NULL DEFAULT 0,
  `total_price` decimal(15,0) NOT NULL DEFAULT 0,
  `note` text DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Đang đổ dữ liệu cho bảng `delivery_note_items`
--

INSERT INTO `delivery_note_items` (`id`, `delivery_note_id`, `production_output_id`, `product_code_id`, `description`, `unit`, `quantity`, `unit_price`, `total_price`, `note`) VALUES
(7, 7, 5, 1, 'hàng ốc vít', 'cái', 5000.00, 0, 0, NULL);

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

--
-- Cấu trúc bảng cho bảng `departments`
--

CREATE TABLE `departments` (
  `id` int(11) NOT NULL,
  `name` varchar(100) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Đang đổ dữ liệu cho bảng `departments`
--

INSERT INTO `departments` (`id`, `name`, `created_at`) VALUES
(1, 'Ban Giám đốc', '2026-03-10 06:57:59'),
(2, 'Kế toán - Tài chính', '2026-03-10 06:57:59'),
(3, 'Kho vận', '2026-03-10 06:57:59'),
(4, 'Sản xuất', '2026-03-10 06:57:59'),
(5, 'Kinh doanh', '2026-03-10 06:57:59');

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

--
-- Cấu trúc bảng cho bảng `districts`
--

CREATE TABLE `districts` (
  `code` varchar(10) NOT NULL,
  `name` varchar(100) NOT NULL,
  `province_code` varchar(10) NOT NULL,
  `full_name` varchar(150) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Cấu trúc bảng cho bảng `document_sequences`
--

CREATE TABLE `document_sequences` (
  `id` int(11) NOT NULL,
  `doc_type` varchar(10) NOT NULL,
  `doc_date` date NOT NULL,
  `last_seq` int(11) NOT NULL DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Đang đổ dữ liệu cho bảng `document_sequences`
--

INSERT INTO `document_sequences` (`id`, `doc_type`, `doc_date`, `last_seq`) VALUES
(1, 'WH', '2026-03-10', 1),
(6, 'SX', '2026-03-10', 2),
(10, 'OUT', '2026-03-10', 3),
(17, 'GH', '2026-03-10', 1),
(18, 'SX', '2026-03-11', 1),
(19, 'OUT', '2026-03-11', 1),
(20, 'GH', '2026-03-11', 1),
(21, 'WH', '2026-03-11', 1);

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

--
-- Cấu trúc bảng cho bảng `employee_profiles`
--

CREATE TABLE `employee_profiles` (
  `id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `gender` enum('male','female','other') NOT NULL DEFAULT 'male',
  `date_of_birth` date DEFAULT NULL,
  `ethnicity` varchar(50) DEFAULT 'Kinh',
  `marital_status` enum('single','married','divorced','widowed') DEFAULT 'single',
  `mobile_phone` varchar(20) DEFAULT NULL,
  `permanent_province` varchar(10) DEFAULT NULL,
  `permanent_district_text` varchar(150) DEFAULT NULL,
  `permanent_commune_text` varchar(150) DEFAULT NULL,
  `permanent_hamlet` varchar(200) DEFAULT NULL,
  `same_as_permanent` tinyint(1) DEFAULT 0,
  `temp_province` varchar(10) DEFAULT NULL,
  `temp_district_text` varchar(150) DEFAULT NULL,
  `temp_commune_text` varchar(150) DEFAULT NULL,
  `temp_hamlet` varchar(200) DEFAULT NULL,
  `identity_no` varchar(20) DEFAULT NULL,
  `identity_issue_date` date DEFAULT NULL,
  `identity_issue_place` varchar(200) DEFAULT NULL,
  `social_book_no` varchar(30) DEFAULT NULL,
  `personal_tax_code` varchar(20) DEFAULT NULL,
  `bank_account` varchar(30) DEFAULT NULL,
  `bank_name` varchar(100) DEFAULT NULL,
  `bank_branch` varchar(200) DEFAULT NULL,
  `dependants` tinyint(4) DEFAULT 0,
  `has_insurance` tinyint(4) DEFAULT 0,
  `insurance_from` date DEFAULT NULL,
  `date_joined` date DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `annual_leave_total` tinyint(4) NOT NULL DEFAULT 9
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Đang đổ dữ liệu cho bảng `employee_profiles`
--

INSERT INTO `employee_profiles` (`id`, `user_id`, `gender`, `date_of_birth`, `ethnicity`, `marital_status`, `mobile_phone`, `permanent_province`, `permanent_district_text`, `permanent_commune_text`, `permanent_hamlet`, `same_as_permanent`, `temp_province`, `temp_district_text`, `temp_commune_text`, `temp_hamlet`, `identity_no`, `identity_issue_date`, `identity_issue_place`, `social_book_no`, `personal_tax_code`, `bank_account`, `bank_name`, `bank_branch`, `dependants`, `has_insurance`, `insurance_from`, `date_joined`, `created_at`, `updated_at`, `annual_leave_total`) VALUES
(1, 2, 'male', NULL, 'Kinh', 'single', '', '77', 'dd', 'dd', 'dd', 0, NULL, '', '', '', '', NULL, '', '', '', '', '', '', 0, 0, NULL, NULL, '2026-03-10 10:51:41', '2026-03-10 10:51:41', 9),
(2, 1, 'male', NULL, 'Kinh', 'single', '', NULL, '', '', '', 0, NULL, '', '', '', '', NULL, '', '', '', '', '', '', 0, 0, NULL, NULL, '2026-03-10 14:16:19', '2026-03-10 14:16:19', 9),
(3, 6, 'male', NULL, 'Kinh', 'single', '', NULL, '', '', '', 0, NULL, '', '', '', '', NULL, '', '', '', '', '', '', 0, 0, NULL, '2026-02-01', '2026-03-10 14:21:55', '2026-03-10 15:35:41', 9),
(4, 7, 'male', NULL, 'Kinh', 'single', '', NULL, '', '', '', 0, NULL, '', '', '', '', NULL, '', '', '', '', '', '', 0, 0, NULL, NULL, '2026-03-10 15:34:57', '2026-03-10 15:34:57', 9),
(5, 8, 'male', NULL, 'Kinh', 'single', '', NULL, '', '', '', 0, NULL, '', '', '', '', NULL, '', '', '', '', '', '', 0, 0, NULL, '2026-03-01', '2026-03-11 03:31:13', '2026-03-11 03:59:06', 9);

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

--
-- Cấu trúc bảng cho bảng `employee_salaries`
--

CREATE TABLE `employee_salaries` (
  `id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `component_id` int(11) DEFAULT NULL,
  `custom_name` varchar(200) DEFAULT NULL,
  `custom_name_en` varchar(200) DEFAULT NULL,
  `component_type` enum('earning','deduction','bonus') NOT NULL DEFAULT 'earning',
  `amount` decimal(15,0) DEFAULT 0,
  `sort_order` int(11) DEFAULT 0,
  `is_active` tinyint(1) DEFAULT 1,
  `note` text DEFAULT NULL,
  `effective_date` date DEFAULT NULL,
  `created_by` int(11) DEFAULT NULL,
  `updated_by` int(11) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Đang đổ dữ liệu cho bảng `employee_salaries`
--

INSERT INTO `employee_salaries` (`id`, `user_id`, `component_id`, `custom_name`, `custom_name_en`, `component_type`, `amount`, `sort_order`, `is_active`, `note`, `effective_date`, `created_by`, `updated_by`, `created_at`, `updated_at`) VALUES
(1, 1, 1, 'Lương cơ bản', 'Basic salary', 'earning', 5000000, 1, 1, '', '2026-03-10', 1, NULL, '2026-03-10 14:16:13', '2026-03-10 14:16:13'),
(2, 6, 1, 'Lương cơ bản', 'Basic salary', 'earning', 5000000, 1, 1, '', '2026-03-10', 1, NULL, '2026-03-10 14:21:54', '2026-03-10 14:21:54'),
(3, 7, 1, 'Lương cơ bản', 'Basic salary', 'earning', 6000000, 1, 1, '', '2026-03-01', 1, NULL, '2026-03-10 15:34:56', '2026-03-11 03:58:22'),
(4, 8, 1, 'Lương cơ bản', 'Basic salary', 'earning', 5000000, 1, 1, '', '2026-03-11', 1, NULL, '2026-03-11 03:30:48', '2026-03-11 03:30:48'),
(5, 8, 8, 'Phụ cấp trách nhiệm', 'Responsibility allowance', 'earning', 5000000, 2, 0, '', '2026-03-11', 1, NULL, '2026-03-11 03:31:02', '2026-03-11 03:58:38'),
(6, 8, 3, 'Trợ cấp trang phục', 'Clothes allowance', 'earning', 400000, 3, 0, '', '2026-03-11', 1, NULL, '2026-03-11 03:31:12', '2026-03-11 03:58:35'),
(7, 7, 3, 'Trợ cấp trang phục', 'Clothes allowance', 'earning', 5000000, 2, 0, '', '2026-03-11', 1, NULL, '2026-03-11 03:31:31', '2026-03-11 03:58:08'),
(8, 7, 4, 'Trợ cấp điện thoại', 'Mobile allowance', 'earning', 3000000, 3, 0, '', '2026-03-11', 1, NULL, '2026-03-11 04:07:01', '2026-03-11 04:50:51'),
(9, 7, NULL, 'Chuyên Cần', '', 'earning', 500000, 4, 0, '', '2026-03-11', 1, NULL, '2026-03-11 04:07:15', '2026-03-11 04:45:38'),
(10, 7, 11, 'Chuyên Cần', 'Attendance Bonus', 'bonus', 500000, 99, 1, NULL, '2026-03-11', 1, NULL, '2026-03-11 04:39:10', '2026-03-11 04:40:58'),
(11, 6, 11, 'Chuyên Cần', 'Attendance Bonus', 'bonus', 500000, 2, 1, '', '2026-03-11', 1, NULL, '2026-03-11 04:46:01', '2026-03-11 04:46:01'),
(12, 7, 3, 'Trợ cấp trang phục', 'Clothes allowance', 'earning', 5000000, 100, 1, '', '2026-03-10', 1, NULL, '2026-03-11 04:53:17', '2026-03-11 04:53:17'),
(13, 6, 2, 'Trợ cấp ăn uống', 'Meal allowance', 'earning', 1040000, 3, 1, '', '2026-03-10', 1, NULL, '2026-03-11 05:06:07', '2026-03-11 05:06:07'),
(14, 8, 11, 'Chuyên Cần', 'Attendance Bonus', 'bonus', 1000000, 4, 1, '', '2026-03-11', 1, NULL, '2026-03-11 13:39:24', '2026-03-11 13:39:24'),
(15, 7, 2, 'Trợ cấp ăn uống', 'Meal allowance', 'earning', 1040000, 101, 1, '', '2026-03-11', 1, NULL, '2026-03-11 15:29:47', '2026-03-11 15:29:47');

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

--
-- Cấu trúc bảng cho bảng `employee_shifts`
--

CREATE TABLE `employee_shifts` (
  `id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `shift_id` int(11) NOT NULL,
  `effective_date` date NOT NULL,
  `end_date` date DEFAULT NULL,
  `created_by` int(11) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Đang đổ dữ liệu cho bảng `employee_shifts`
--

INSERT INTO `employee_shifts` (`id`, `user_id`, `shift_id`, `effective_date`, `end_date`, `created_by`, `created_at`) VALUES
(1, 3, 1, '2026-03-10', NULL, 1, '2026-03-10 09:56:59'),
(2, 1, 1, '2026-03-10', NULL, 1, '2026-03-10 09:56:59'),
(3, 2, 1, '2026-03-10', NULL, 1, '2026-03-10 09:56:59'),
(4, 4, 1, '2026-03-10', NULL, 1, '2026-03-10 09:56:59'),
(5, 5, 1, '2026-03-10', NULL, 1, '2026-03-10 09:56:59'),
(6, 6, 1, '2026-03-10', NULL, 1, '2026-03-10 09:56:59'),
(7, 7, 1, '2026-03-10', NULL, 1, '2026-03-10 09:56:59');

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

--
-- Cấu trúc bảng cho bảng `ethnicities`
--

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

--
-- Đang đổ dữ liệu cho bảng `ethnicities`
--

INSERT INTO `ethnicities` (`id`, `name`) VALUES
(12, 'Ba Na'),
(48, 'Bố Y'),
(53, 'Brâu'),
(24, 'Bru - Vân Kiều'),
(14, 'Chăm'),
(31, 'Chơ Ro'),
(34, 'Chu Ru'),
(44, 'Chứt'),
(29, 'Co'),
(15, 'Cơ Ho'),
(47, 'Cờ Lao'),
(25, 'Cơ Tu'),
(49, 'Cống'),
(9, 'Dao'),
(11, 'Ê Đê'),
(10, 'Gia Rai'),
(26, 'Giáy'),
(30, 'Giẻ Triêng'),
(33, 'Hà Nhì'),
(8, 'Hoa'),
(18, 'Hrê'),
(37, 'Kháng'),
(5, 'Khmer'),
(23, 'Khơ Mú'),
(1, 'Kinh'),
(36, 'La Chí'),
(40, 'La Ha'),
(39, 'La Hủ'),
(35, 'Lào'),
(45, 'Lô Lô'),
(42, 'Lự'),
(28, 'Mạ'),
(46, 'Mảng'),
(20, 'Mnông'),
(6, 'Mông'),
(4, 'Mường'),
(43, 'Ngái'),
(7, 'Nùng'),
(54, 'Ơ Đu'),
(41, 'Pà Thẻn'),
(38, 'Phù Lá'),
(51, 'Pu Péo'),
(19, 'Ra Glai'),
(52, 'Rơ Măm'),
(13, 'Sán Chay'),
(17, 'Sán Dìu'),
(50, 'Si La'),
(27, 'Tà Ôi'),
(2, 'Tày'),
(3, 'Thái'),
(21, 'Thổ'),
(32, 'Xinh Mun'),
(16, 'Xơ Đăng'),
(22, 'Xtiêng');

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

--
-- Cấu trúc bảng cho bảng `holidays`
--

CREATE TABLE `holidays` (
  `id` int(11) NOT NULL,
  `holiday_date` date NOT NULL,
  `holiday_name` varchar(100) NOT NULL,
  `year` year(4) NOT NULL,
  `created_by` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Đang đổ dữ liệu cho bảng `holidays`
--

INSERT INTO `holidays` (`id`, `holiday_date`, `holiday_name`, `year`, `created_by`) VALUES
(1, '2026-01-01', 'Tết Dương lịch', '2026', NULL),
(2, '2026-01-28', 'Tết Nguyên Đán (28/1)', '2026', NULL),
(3, '2026-01-29', 'Tết Nguyên Đán (29/1)', '2026', NULL),
(4, '2026-01-30', 'Tết Nguyên Đán (30/1)', '2026', NULL),
(5, '2026-01-31', 'Tết Nguyên Đán (31/1)', '2026', NULL),
(6, '2026-02-01', 'Tết Nguyên Đán (1/2)', '2026', NULL),
(7, '2026-02-02', 'Tết Nguyên Đán (2/2)', '2026', NULL),
(8, '2026-04-07', 'Giỗ Tổ Hùng Vương', '2026', NULL),
(9, '2026-04-30', 'Ngày Giải phóng miền Nam', '2026', NULL),
(10, '2026-05-01', 'Quốc tế Lao động', '2026', NULL),
(11, '2026-09-02', 'Quốc khánh', '2026', NULL),
(12, '2026-09-03', 'Quốc khánh (bù)', '2026', NULL),
(13, '2025-01-01', 'Tết Dương lịch', '2025', NULL),
(14, '2025-01-27', 'Nghỉ Tết Nguyên Đán (27 tháng Chạp)', '2025', NULL),
(15, '2025-01-28', 'Nghỉ Tết Nguyên Đán (28 tháng Chạp)', '2025', NULL),
(16, '2025-01-29', 'Nghỉ Tết Nguyên Đán (29 tháng Chạp)', '2025', NULL),
(17, '2025-01-30', 'Nghỉ Tết Nguyên Đán (Mùng 1)', '2025', NULL),
(18, '2025-01-31', 'Nghỉ Tết Nguyên Đán (Mùng 2)', '2025', NULL),
(19, '2025-02-01', 'Nghỉ Tết Nguyên Đán (Mùng 3)', '2025', NULL),
(20, '2025-04-07', 'Giỗ Tổ Hùng Vương', '2025', NULL),
(21, '2025-04-30', 'Ngày Giải phóng miền Nam', '2025', NULL),
(22, '2025-05-01', 'Ngày Quốc tế Lao động', '2025', NULL),
(23, '2025-09-01', 'Nghỉ bù Quốc khánh', '2025', NULL),
(24, '2025-09-02', 'Ngày Quốc khánh', '2025', NULL),
(25, '2026-02-17', 'Nghỉ Tết Nguyên Đán (26 tháng Chạp)', '2026', NULL),
(26, '2026-02-18', 'Nghỉ Tết Nguyên Đán (27 tháng Chạp)', '2026', NULL),
(27, '2026-02-19', 'Nghỉ Tết Nguyên Đán (28 tháng Chạp)', '2026', NULL),
(28, '2026-02-20', 'Nghỉ Tết Nguyên Đán (Mùng 1)', '2026', NULL),
(29, '2026-02-21', 'Nghỉ Tết Nguyên Đán (Mùng 2)', '2026', NULL),
(30, '2026-02-22', 'Nghỉ Tết Nguyên Đán (Mùng 3)', '2026', NULL),
(31, '2026-04-16', 'Giỗ Tổ Hùng Vương', '2026', NULL);

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

--
-- Cấu trúc bảng cho bảng `invoices`
--

CREATE TABLE `invoices` (
  `id` int(11) NOT NULL,
  `invoice_no` varchar(30) NOT NULL,
  `invoice_date` date NOT NULL,
  `due_date` date DEFAULT NULL,
  `customer_id` int(11) NOT NULL,
  `total_amount` decimal(15,0) NOT NULL DEFAULT 0,
  `subtotal` decimal(15,0) NOT NULL DEFAULT 0,
  `vat_rate` decimal(5,2) NOT NULL DEFAULT 0.00,
  `vat_amount` decimal(15,0) NOT NULL DEFAULT 0,
  `note` text DEFAULT NULL,
  `delivery_id` int(11) DEFAULT NULL,
  `status` enum('draft','confirmed','cancelled') DEFAULT 'draft',
  `created_by` int(11) DEFAULT NULL,
  `confirmed_by` int(11) DEFAULT NULL,
  `confirmed_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Cấu trúc đóng vai cho view `invoices_v`
-- (See below for the actual view)
--
CREATE TABLE `invoices_v` (
`id` int(11)
,`invoice_no` varchar(30)
,`invoice_date` date
,`customer_id` int(11)
,`total_amount` decimal(15,0)
,`subtotal` decimal(15,0)
,`vat_rate` int(1)
,`vat_amount` int(1)
,`note` text
,`status` enum('draft','confirmed','cancelled')
,`due_date` binary(0)
,`delivery_id` binary(0)
,`created_by` int(11)
,`confirmed_by` int(11)
,`confirmed_at` timestamp
,`created_at` timestamp
,`updated_at` timestamp
);

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

--
-- Cấu trúc bảng cho bảng `invoice_delivery_notes`
--

CREATE TABLE `invoice_delivery_notes` (
  `id` int(11) NOT NULL,
  `invoice_id` int(11) NOT NULL,
  `delivery_note_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Cấu trúc bảng cho bảng `invoice_items`
--

CREATE TABLE `invoice_items` (
  `id` int(11) NOT NULL,
  `invoice_id` int(11) NOT NULL,
  `delivery_note_id` int(11) NOT NULL,
  `delivery_note_item_id` int(11) NOT NULL,
  `product_code_id` int(11) NOT NULL,
  `description` varchar(500) DEFAULT NULL,
  `quantity` decimal(15,2) NOT NULL DEFAULT 0.00,
  `unit_price` decimal(15,0) NOT NULL DEFAULT 0,
  `total_amount` decimal(15,0) NOT NULL DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Cấu trúc bảng cho bảng `kpi_assignments`
--

CREATE TABLE `kpi_assignments` (
  `id` int(11) NOT NULL,
  `assign_date` date NOT NULL,
  `user_id` int(11) NOT NULL COMMENT 'nhân viên sản xuất',
  `manager_id` int(11) NOT NULL COMMENT 'quản lý phân bổ',
  `kpi_target` int(11) NOT NULL DEFAULT 0 COMMENT 'số SP mục tiêu',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Đang đổ dữ liệu cho bảng `kpi_assignments`
--

INSERT INTO `kpi_assignments` (`id`, `assign_date`, `user_id`, `manager_id`, `kpi_target`, `created_at`, `updated_at`) VALUES
(7, '2026-03-11', 6, 4, 600, '2026-03-11 13:16:43', '2026-03-11 13:16:43'),
(8, '2026-03-11', 8, 4, 600, '2026-03-11 13:16:43', '2026-03-11 13:16:43'),
(9, '2026-03-11', 7, 4, 500, '2026-03-11 13:16:43', '2026-03-11 13:16:43');

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

--
-- Cấu trúc bảng cho bảng `kpi_results`
--

CREATE TABLE `kpi_results` (
  `id` int(11) NOT NULL,
  `kpi_assignment_id` int(11) NOT NULL,
  `actual_qty` int(11) NOT NULL DEFAULT 0 COMMENT 'SP thực tế',
  `salary_per_day` decimal(15,0) DEFAULT 0 COMMENT 'lương ngày đầy đủ',
  `salary_actual` decimal(15,0) DEFAULT 0 COMMENT 'lương ngày thực tế sau KPI',
  `is_deducted` tinyint(1) DEFAULT 0 COMMENT '1=trừ lương, 0=đủ ngày công',
  `reason` text DEFAULT NULL COMMENT 'lý do không trừ lương',
  `confirmed_by` int(11) DEFAULT NULL,
  `confirmed_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Đang đổ dữ liệu cho bảng `kpi_results`
--

INSERT INTO `kpi_results` (`id`, `kpi_assignment_id`, `actual_qty`, `salary_per_day`, `salary_actual`, `is_deducted`, `reason`, `confirmed_by`, `confirmed_at`, `created_at`, `updated_at`) VALUES
(32, 7, 650, 232308, 251667, 0, '', 1, '2026-03-11 15:46:04', '2026-03-11 13:17:34', '2026-03-11 15:46:04'),
(33, 8, 750, 192308, 240385, 0, '', 1, '2026-03-11 15:46:08', '2026-03-11 13:17:34', '2026-03-11 15:46:08'),
(34, 9, 250, 463077, 231539, 1, '', 1, '2026-03-11 15:46:12', '2026-03-11 13:17:34', '2026-03-11 15:46:12');

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

--
-- Cấu trúc bảng cho bảng `leave_requests`
--

CREATE TABLE `leave_requests` (
  `id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `leave_type` enum('annual','sick','unpaid','other') NOT NULL DEFAULT 'annual',
  `start_date` date NOT NULL,
  `end_date` date NOT NULL,
  `total_days` decimal(4,1) NOT NULL,
  `reason` text NOT NULL,
  `status` enum('pending','approved','rejected') DEFAULT 'pending',
  `approved_by` int(11) DEFAULT NULL,
  `approved_at` datetime DEFAULT NULL,
  `reject_reason` text DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Đang đổ dữ liệu cho bảng `leave_requests`
--

INSERT INTO `leave_requests` (`id`, `user_id`, `leave_type`, `start_date`, `end_date`, `total_days`, `reason`, `status`, `approved_by`, `approved_at`, `reject_reason`, `created_at`) VALUES
(1, 6, 'annual', '2026-03-10', '2026-03-11', 2.0, 'Em xin nghỉ phép', 'approved', 1, '2026-03-10 17:19:49', '', '2026-03-10 09:59:01'),
(2, 6, 'annual', '2026-03-11', '2026-03-11', 1.0, 'có công việc gia đình', 'approved', 5, '2026-03-10 20:17:52', '', '2026-03-10 13:17:15');

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

--
-- Cấu trúc bảng cho bảng `notifications`
--

CREATE TABLE `notifications` (
  `id` bigint(20) NOT NULL,
  `user_id` int(11) NOT NULL,
  `title` varchar(200) NOT NULL,
  `message` text NOT NULL,
  `type` varchar(50) DEFAULT 'general',
  `reference_id` int(11) DEFAULT NULL,
  `is_read` tinyint(1) DEFAULT 0,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Đang đổ dữ liệu cho bảng `notifications`
--

INSERT INTO `notifications` (`id`, `user_id`, `title`, `message`, `type`, `reference_id`, `is_read`, `created_at`) VALUES
(1, 1, 'Đơn xin nghỉ phép mới', 'Nguyễn Thị Lan xin nghỉ từ 10/03/2026 đến 11/03/2026', 'leave_request', 0, 0, '2026-03-10 09:59:01'),
(2, 3, 'Đơn xin nghỉ phép mới', 'Nguyễn Thị Lan xin nghỉ từ 10/03/2026 đến 11/03/2026', 'leave_request', 1, 0, '2026-03-10 09:59:01'),
(3, 5, 'Đơn xin nghỉ phép mới', 'Nguyễn Thị Lan xin nghỉ từ 10/03/2026 đến 11/03/2026', 'leave_request', 2, 0, '2026-03-10 09:59:01'),
(4, 6, 'Kết quả đơn nghỉ phép', '✅ Đơn xin nghỉ phép của bạn đã được duyệt.', 'leave_request', 1, 0, '2026-03-10 10:19:49'),
(5, 1, 'Đơn xin nghỉ phép mới', 'Nguyễn Thị Lan xin nghỉ từ 11/03/2026 đến 11/03/2026', 'leave_request', 0, 0, '2026-03-10 13:17:15'),
(6, 3, 'Đơn xin nghỉ phép mới', 'Nguyễn Thị Lan xin nghỉ từ 11/03/2026 đến 11/03/2026', 'leave_request', 5, 0, '2026-03-10 13:17:15'),
(7, 5, 'Đơn xin nghỉ phép mới', 'Nguyễn Thị Lan xin nghỉ từ 11/03/2026 đến 11/03/2026', 'leave_request', 6, 0, '2026-03-10 13:17:15'),
(8, 6, 'Kết quả đơn nghỉ phép', '✅ Đơn xin nghỉ phép của bạn đã được duyệt.', 'leave_request', 2, 0, '2026-03-10 13:17:52');

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

--
-- Cấu trúc bảng cho bảng `overtime_requests`
--

CREATE TABLE `overtime_requests` (
  `id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `department_id` int(11) DEFAULT NULL,
  `ot_date` date NOT NULL,
  `start_time` time NOT NULL,
  `end_time` time NOT NULL,
  `hours` decimal(4,2) NOT NULL,
  `actual_hours` decimal(4,2) DEFAULT NULL,
  `reason` text NOT NULL,
  `ot_type` enum('weekday','weekend','holiday') DEFAULT 'weekday',
  `shift_id` int(11) DEFAULT NULL,
  `status` enum('pending','approved','rejected') DEFAULT 'pending',
  `approved_by` int(11) DEFAULT NULL,
  `approved_at` datetime DEFAULT NULL,
  `reject_reason` text DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Cấu trúc đóng vai cho view `payments`
-- (See below for the actual view)
--
CREATE TABLE `payments` (
`id` int(11)
,`invoice_id` int(11)
,`payment_date` date
,`amount` decimal(15,0)
,`payment_method` enum('cash','transfer','other')
,`note` varchar(100)
,`created_by` int(11)
,`created_at` timestamp
);

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

--
-- Cấu trúc bảng cho bảng `payroll_periods`
--

CREATE TABLE `payroll_periods` (
  `id` int(11) NOT NULL,
  `period_year` smallint(6) NOT NULL,
  `period_month` tinyint(4) NOT NULL,
  `period_from` date NOT NULL,
  `period_to` date NOT NULL,
  `working_days` tinyint(4) NOT NULL,
  `status` enum('draft','submitted','approved','locked') DEFAULT 'draft',
  `note` text DEFAULT NULL,
  `created_by` int(11) DEFAULT NULL,
  `submitted_at` datetime DEFAULT NULL,
  `submitted_by` int(11) DEFAULT NULL,
  `approved_at` datetime DEFAULT NULL,
  `approved_by` int(11) DEFAULT NULL,
  `locked_at` datetime DEFAULT NULL,
  `locked_by` int(11) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Đang đổ dữ liệu cho bảng `payroll_periods`
--

INSERT INTO `payroll_periods` (`id`, `period_year`, `period_month`, `period_from`, `period_to`, `working_days`, `status`, `note`, `created_by`, `submitted_at`, `submitted_by`, `approved_at`, `approved_by`, `locked_at`, `locked_by`, `created_at`, `updated_at`) VALUES
(1, 2026, 3, '2026-03-01', '2026-03-31', 26, 'approved', NULL, 1, '2026-03-10 22:07:43', 1, '2026-03-10 22:07:52', 1, NULL, NULL, '2026-03-10 13:46:59', '2026-03-10 15:07:52');

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

--
-- Cấu trúc bảng cho bảng `payroll_slips`
--

CREATE TABLE `payroll_slips` (
  `id` int(11) NOT NULL,
  `period_id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `basic_salary` decimal(15,0) DEFAULT 0,
  `working_days_standard` tinyint(4) DEFAULT 0,
  `salary_per_day` decimal(10,0) DEFAULT 0,
  `salary_per_hour` decimal(10,0) DEFAULT 0,
  `actual_workdays` decimal(5,2) DEFAULT 0.00,
  `paid_leave_days` decimal(5,2) DEFAULT 0.00,
  `other_paid_leave_days` decimal(5,2) DEFAULT 0.00,
  `unpaid_leave_days` decimal(5,2) DEFAULT 0.00,
  `late_early_hours` decimal(5,2) DEFAULT 0.00,
  `late_early_deduction` decimal(15,0) DEFAULT 0,
  `total_paid_days` decimal(5,2) DEFAULT 0.00,
  `basic_salary_received` decimal(15,0) DEFAULT 0,
  `meal_allowance` decimal(15,0) DEFAULT 0,
  `meal_received` decimal(15,0) DEFAULT 0,
  `clothes_allowance` decimal(15,0) DEFAULT 0,
  `clothes_received` decimal(15,0) DEFAULT 0,
  `phone_allowance` decimal(15,0) DEFAULT 0,
  `phone_received` decimal(15,0) DEFAULT 0,
  `transport_allowance` decimal(15,0) DEFAULT 0,
  `transport_received` decimal(15,0) DEFAULT 0,
  `performance_bonus` decimal(15,0) DEFAULT 0,
  `basic_salary_per_hour` decimal(10,0) DEFAULT 0,
  `ot_weekday_hours` decimal(5,2) DEFAULT 0.00,
  `ot_weekend_hours` decimal(5,2) DEFAULT 0.00,
  `ot_holiday_hours` decimal(5,2) DEFAULT 0.00,
  `ot_weekday_amount` decimal(15,0) DEFAULT 0,
  `ot_weekend_amount` decimal(15,0) DEFAULT 0,
  `ot_holiday_amount` decimal(15,0) DEFAULT 0,
  `total_ot_amount` decimal(15,0) DEFAULT 0,
  `annual_leave_total` tinyint(4) DEFAULT 0,
  `annual_leave_used` decimal(5,2) DEFAULT 0.00,
  `annual_leave_remaining` decimal(5,2) DEFAULT 0.00,
  `annual_leave_payout` decimal(15,0) DEFAULT 0,
  `other_income` decimal(15,0) DEFAULT 0,
  `adjustment` decimal(15,0) DEFAULT 0,
  `other_bonus` decimal(15,0) DEFAULT 0,
  `attendance_bonus` decimal(15,0) DEFAULT 0,
  `attendance_bonus_eligible` tinyint(4) DEFAULT 0,
  `has_social_insurance` tinyint(4) DEFAULT 0,
  `si_employee` decimal(15,0) DEFAULT 0,
  `si_company` decimal(15,0) DEFAULT 0,
  `dependants` tinyint(4) DEFAULT 0,
  `personal_deduction` decimal(15,0) DEFAULT 15500000,
  `dependant_deduction` decimal(15,0) DEFAULT 0,
  `ot_exclude_pit` decimal(15,0) DEFAULT 0,
  `taxable_income` decimal(15,0) DEFAULT 0,
  `pit_amount` decimal(15,0) DEFAULT 0,
  `late_deduction` decimal(15,0) DEFAULT 0,
  `kpi_deduction` decimal(15,0) DEFAULT 0,
  `gross_salary` decimal(15,0) DEFAULT 0,
  `advance_payment` decimal(15,0) DEFAULT 0,
  `net_salary` decimal(15,0) DEFAULT 0,
  `pit_adjustment` decimal(15,0) DEFAULT 0,
  `bank_transfer` decimal(15,0) DEFAULT 0,
  `remark` text DEFAULT NULL,
  `is_late_warning` tinyint(4) DEFAULT 0,
  `late_warning_note` text DEFAULT NULL,
  `manually_adjusted` tinyint(4) DEFAULT 0,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Đang đổ dữ liệu cho bảng `payroll_slips`
--

INSERT INTO `payroll_slips` (`id`, `period_id`, `user_id`, `basic_salary`, `working_days_standard`, `salary_per_day`, `salary_per_hour`, `actual_workdays`, `paid_leave_days`, `other_paid_leave_days`, `unpaid_leave_days`, `late_early_hours`, `late_early_deduction`, `total_paid_days`, `basic_salary_received`, `meal_allowance`, `meal_received`, `clothes_allowance`, `clothes_received`, `phone_allowance`, `phone_received`, `transport_allowance`, `transport_received`, `performance_bonus`, `basic_salary_per_hour`, `ot_weekday_hours`, `ot_weekend_hours`, `ot_holiday_hours`, `ot_weekday_amount`, `ot_weekend_amount`, `ot_holiday_amount`, `total_ot_amount`, `annual_leave_total`, `annual_leave_used`, `annual_leave_remaining`, `annual_leave_payout`, `other_income`, `adjustment`, `other_bonus`, `attendance_bonus`, `attendance_bonus_eligible`, `has_social_insurance`, `si_employee`, `si_company`, `dependants`, `personal_deduction`, `dependant_deduction`, `ot_exclude_pit`, `taxable_income`, `pit_amount`, `late_deduction`, `kpi_deduction`, `gross_salary`, `advance_payment`, `net_salary`, `pit_adjustment`, `bank_transfer`, `remark`, `is_late_warning`, `late_warning_note`, `manually_adjusted`, `created_at`, `updated_at`) VALUES
(1, 1, 5, 0, 26, 0, 0, 0.00, 0.00, 0.00, 0.00, 0.00, 0, 0.00, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0.00, 0.00, 0.00, 0, 0, 0, 0, 12, 0.00, 12.00, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 15500000, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, '', 0, '', 0, '2026-03-10 14:20:56', '2026-03-11 15:28:29'),
(2, 1, 3, 0, 26, 0, 0, 0.00, 0.00, 0.00, 0.00, 0.00, 0, 0.00, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0.00, 0.00, 0.00, 0, 0, 0, 0, 12, 0.00, 12.00, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 15500000, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, '', 0, '', 0, '2026-03-10 14:20:56', '2026-03-11 15:28:29'),
(3, 1, 6, 5000000, 26, 232308, 29039, 5.00, 3.00, 0.00, 0.00, 0.00, 0, 8.00, 1538462, 1040000, 200000, 0, 0, 0, 0, 0, 0, 0, 24039, 0.00, 0.00, 0.00, 0, 0, 0, 0, 8, 3.00, 5.00, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 15500000, 0, 0, 0, 0, 0, 0, 1738462, 0, 1738462, 0, 1738462, 'Không có chuyên cần: nghỉ không phép 0 ngày', 0, '', 0, '2026-03-10 14:20:56', '2026-03-11 15:28:29'),
(4, 1, 1, 5000000, 26, 192308, 24039, 1.00, 0.00, 0.00, 0.00, 0.00, 0, 1.00, 192308, 0, 0, 0, 0, 0, 0, 0, 0, 0, 24039, 0.00, 0.00, 0.00, 0, 0, 0, 0, 9, 0.00, 9.00, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 15500000, 0, 0, 0, 0, 0, 0, 192308, 0, 192308, 0, 192308, '', 0, '', 0, '2026-03-10 14:20:56', '2026-03-11 15:28:29'),
(5, 1, 4, 0, 26, 0, 0, 0.00, 0.00, 0.00, 0.00, 0.00, 0, 0.00, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0.00, 0.00, 0.00, 0, 0, 0, 0, 12, 0.00, 12.00, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 15500000, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, '', 0, '', 0, '2026-03-10 14:20:56', '2026-03-11 15:28:29'),
(6, 1, 2, 0, 26, 0, 0, 0.00, 0.00, 0.00, 0.00, 0.00, 0, 0.00, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0.00, 0.00, 0.00, 0, 0, 0, 0, 9, 0.00, 9.00, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 15500000, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, '', 0, '', 0, '2026-03-10 14:20:56', '2026-03-11 15:28:29'),
(7, 1, 7, 6000000, 26, 463077, 57885, 2.00, 0.00, 0.00, 0.00, 2.00, 115770, 2.00, 461538, 1040000, 80000, 5000000, 384615, 0, 0, 0, 0, 0, 28846, 0.00, 0.00, 0.00, 0, 0, 0, 0, 9, 0.00, 9.00, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 15500000, 0, 0, 0, 0, 115770, 211538, 926153, 0, 598845, 0, 598845, 'Trừ KPI: 211,538 đ (1 ngày không đạt); Trừ muộn/sớm: 115,770 đ (trễ: 60p, về sớm: 60p); Không có chuyên cần: bị trừ KPI', 1, '%d/%m(S), %d/%m(T)', 0, '2026-03-10 14:20:56', '2026-03-11 15:30:59'),
(8, 1, 8, 5000000, 26, 192308, 24039, 1.00, 0.00, 0.00, 0.00, 0.00, 0, 1.00, 192308, 0, 0, 0, 0, 0, 0, 0, 0, 0, 24039, 0.00, 0.00, 0.00, 0, 0, 0, 0, 8, 0.00, 8.00, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 15500000, 0, 0, 0, 0, 0, 0, 192308, 0, 192308, 0, 192308, 'Không có chuyên cần: nghỉ không phép 0 ngày', 0, '', 0, '2026-03-11 03:34:25', '2026-03-11 15:28:29');

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

--
-- Cấu trúc đóng vai cho view `prices`
-- (See below for the actual view)
--
CREATE TABLE `prices` (
`id` int(11)
,`product_code_id` int(11)
,`unit_price` decimal(15,0)
,`created_at` date
,`is_active` int(1)
,`created_by` int(11)
);

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

--
-- Cấu trúc bảng cho bảng `production_outputs`
--

CREATE TABLE `production_outputs` (
  `id` int(11) NOT NULL,
  `output_no` varchar(30) NOT NULL,
  `output_date` date NOT NULL,
  `production_receipt_id` int(11) NOT NULL,
  `product_code_id` int(11) NOT NULL,
  `description` varchar(500) DEFAULT NULL,
  `quantity_completed` decimal(15,2) NOT NULL DEFAULT 0.00,
  `quantity_defect` decimal(15,2) NOT NULL DEFAULT 0.00,
  `quantity_delivered` decimal(15,2) NOT NULL DEFAULT 0.00,
  `note` text DEFAULT NULL,
  `created_by` int(11) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Đang đổ dữ liệu cho bảng `production_outputs`
--

INSERT INTO `production_outputs` (`id`, `output_no`, `output_date`, `production_receipt_id`, `product_code_id`, `description`, `quantity_completed`, `quantity_defect`, `quantity_delivered`, `note`, `created_by`, `created_at`, `updated_at`) VALUES
(5, 'OUT-20260311-001', '2026-03-11', 6, 1, NULL, 50000.00, 0.00, 5000.00, '', 1, '2026-03-11 15:07:17', '2026-03-11 15:07:34');

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

--
-- Cấu trúc bảng cho bảng `production_receipts`
--

CREATE TABLE `production_receipts` (
  `id` int(11) NOT NULL,
  `receipt_no` varchar(30) NOT NULL,
  `receipt_date` date NOT NULL,
  `warehouse_import_id` int(11) NOT NULL,
  `product_code_id` int(11) NOT NULL,
  `description` varchar(500) DEFAULT NULL,
  `quantity_received` decimal(15,2) NOT NULL DEFAULT 0.00,
  `note` text DEFAULT NULL,
  `created_by` int(11) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Đang đổ dữ liệu cho bảng `production_receipts`
--

INSERT INTO `production_receipts` (`id`, `receipt_no`, `receipt_date`, `warehouse_import_id`, `product_code_id`, `description`, `quantity_received`, `note`, `created_by`, `created_at`, `updated_at`) VALUES
(6, 'PR-20260311-0001', '2026-03-11', 4, 1, NULL, 50000.00, '', 1, '2026-03-11 15:03:09', '2026-03-11 15:03:09');

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

--
-- Cấu trúc bảng cho bảng `production_stock`
--

CREATE TABLE `production_stock` (
  `id` int(11) NOT NULL,
  `product_code_id` int(11) NOT NULL,
  `stock_date` date NOT NULL,
  `qty_pending` decimal(15,2) DEFAULT 0.00,
  `qty_completed` decimal(15,2) DEFAULT 0.00,
  `qty_defect` decimal(15,2) DEFAULT 0.00,
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Đang đổ dữ liệu cho bảng `production_stock`
--

INSERT INTO `production_stock` (`id`, `product_code_id`, `stock_date`, `qty_pending`, `qty_completed`, `qty_defect`, `updated_at`) VALUES
(2, 1, '2026-03-11', 0.00, 45000.00, 0.00, '2026-03-11 15:07:34');

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

--
-- Cấu trúc bảng cho bảng `product_codes`
--

CREATE TABLE `product_codes` (
  `id` int(11) NOT NULL,
  `product_code` varchar(50) NOT NULL,
  `description` varchar(500) NOT NULL,
  `unit` varchar(20) DEFAULT 'cái',
  `category` varchar(100) DEFAULT NULL,
  `is_active` tinyint(1) DEFAULT 1,
  `created_by` int(11) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Đang đổ dữ liệu cho bảng `product_codes`
--

INSERT INTO `product_codes` (`id`, `product_code`, `description`, `unit`, `category`, `is_active`, `created_by`, `created_at`, `updated_at`) VALUES
(1, 'SP-01', 'hàng ốc vít', 'cái', 'Thành Phẩm', 1, 1, '2026-03-10 17:10:18', '2026-03-10 17:10:18');

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

--
-- Cấu trúc bảng cho bảng `product_prices`
--

CREATE TABLE `product_prices` (
  `id` int(11) NOT NULL,
  `product_code_id` int(11) NOT NULL,
  `unit_price` decimal(15,0) NOT NULL DEFAULT 0,
  `effective_from` date NOT NULL,
  `note` text DEFAULT NULL,
  `created_by` int(11) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Cấu trúc bảng cho bảng `provinces`
--

CREATE TABLE `provinces` (
  `code` varchar(10) NOT NULL,
  `name` varchar(100) NOT NULL,
  `name_en` varchar(100) DEFAULT NULL,
  `full_name` varchar(150) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Đang đổ dữ liệu cho bảng `provinces`
--

INSERT INTO `provinces` (`code`, `name`, `name_en`, `full_name`) VALUES
('01', 'Hà Nội', NULL, 'Thành phố Hà Nội'),
('02', 'Hà Giang', NULL, 'Tỉnh Hà Giang'),
('04', 'Cao Bằng', NULL, 'Tỉnh Cao Bằng'),
('06', 'Bắc Kạn', NULL, 'Tỉnh Bắc Kạn'),
('08', 'Tuyên Quang', NULL, 'Tỉnh Tuyên Quang'),
('10', 'Lào Cai', NULL, 'Tỉnh Lào Cai'),
('11', 'Điện Biên', NULL, 'Tỉnh Điện Biên'),
('12', 'Lai Châu', NULL, 'Tỉnh Lai Châu'),
('14', 'Sơn La', NULL, 'Tỉnh Sơn La'),
('15', 'Yên Bái', NULL, 'Tỉnh Yên Bái'),
('17', 'Hoà Bình', NULL, 'Tỉnh Hoà Bình'),
('19', 'Thái Nguyên', NULL, 'Tỉnh Thái Nguyên'),
('20', 'Lạng Sơn', NULL, 'Tỉnh Lạng Sơn'),
('22', 'Quảng Ninh', NULL, 'Tỉnh Quảng Ninh'),
('24', 'Bắc Giang', NULL, 'Tỉnh Bắc Giang'),
('25', 'Phú Thọ', NULL, 'Tỉnh Phú Thọ'),
('26', 'Vĩnh Phúc', NULL, 'Tỉnh Vĩnh Phúc'),
('27', 'Bắc Ninh', NULL, 'Tỉnh Bắc Ninh'),
('30', 'Hải Dương', NULL, 'Tỉnh Hải Dương'),
('31', 'Hải Phòng', NULL, 'Thành phố Hải Phòng'),
('33', 'Hưng Yên', NULL, 'Tỉnh Hưng Yên'),
('34', 'Thái Bình', NULL, 'Tỉnh Thái Bình'),
('35', 'Hà Nam', NULL, 'Tỉnh Hà Nam'),
('36', 'Nam Định', NULL, 'Tỉnh Nam Định'),
('37', 'Ninh Bình', NULL, 'Tỉnh Ninh Bình'),
('38', 'Thanh Hóa', NULL, 'Tỉnh Thanh Hóa'),
('40', 'Nghệ An', NULL, 'Tỉnh Nghệ An'),
('42', 'Hà Tĩnh', NULL, 'Tỉnh Hà Tĩnh'),
('44', 'Quảng Bình', NULL, 'Tỉnh Quảng Bình'),
('45', 'Quảng Trị', NULL, 'Tỉnh Quảng Trị'),
('46', 'Thừa Thiên Huế', NULL, 'Tỉnh Thừa Thiên Huế'),
('48', 'Đà Nẵng', NULL, 'Thành phố Đà Nẵng'),
('49', 'Quảng Nam', NULL, 'T��nh Quảng Nam'),
('51', 'Quảng Ngãi', NULL, 'Tỉnh Quảng Ngãi'),
('52', 'Bình Định', NULL, 'Tỉnh Bình Định'),
('54', 'Phú Yên', NULL, 'Tỉnh Phú Yên'),
('56', 'Khánh Hòa', NULL, 'Tỉnh Khánh Hòa'),
('58', 'Ninh Thuận', NULL, 'Tỉnh Ninh Thuận'),
('60', 'Bình Thuận', NULL, 'Tỉnh Bình Thuận'),
('62', 'Kon Tum', NULL, 'Tỉnh Kon Tum'),
('64', 'Gia Lai', NULL, 'Tỉnh Gia Lai'),
('66', 'Đắk Lắk', NULL, 'Tỉnh Đắk Lắk'),
('67', 'Đắk Nông', NULL, 'Tỉnh Đắk Nông'),
('68', 'Lâm Đồng', NULL, 'Tỉnh Lâm Đồng'),
('70', 'Bình Phước', NULL, 'Tỉnh Bình Phước'),
('72', 'Tây Ninh', NULL, 'Tỉnh Tây Ninh'),
('74', 'Bình Dương', NULL, 'Tỉnh Bình Dương'),
('75', 'Đồng Nai', NULL, 'Tỉnh Đồng Nai'),
('77', 'Bà Rịa - Vũng Tàu', NULL, 'Tỉnh Bà Rịa - Vũng Tàu'),
('79', 'Hồ Chí Minh', NULL, 'Thành phố Hồ Chí Minh'),
('80', 'Long An', NULL, 'Tỉnh Long An'),
('82', 'Tiền Giang', NULL, 'Tỉnh Tiền Giang'),
('83', 'Bến Tre', NULL, 'Tỉnh Bến Tre'),
('84', 'Trà Vinh', NULL, 'Tỉnh Trà Vinh'),
('86', 'Vĩnh Long', NULL, 'Tỉnh Vĩnh Long'),
('87', 'Đồng Tháp', NULL, 'Tỉnh Đồng Tháp'),
('89', 'An Giang', NULL, 'Tỉnh An Giang'),
('91', 'Kiên Giang', NULL, 'Tỉnh Kiên Giang'),
('92', 'Cần Thơ', NULL, 'Thành phố Cần Thơ'),
('93', 'Hậu Giang', NULL, 'Tỉnh Hậu Giang'),
('94', 'Sóc Trăng', NULL, 'Tỉnh Sóc Trăng'),
('95', 'Bạc Liêu', NULL, 'Tỉnh Bạc Liêu'),
('96', 'Cà Mau', NULL, 'Tỉnh Cà Mau');

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

--
-- Cấu trúc bảng cho bảng `roles`
--

CREATE TABLE `roles` (
  `id` int(11) NOT NULL,
  `name` varchar(50) NOT NULL,
  `display_name` varchar(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Đang đổ dữ liệu cho bảng `roles`
--

INSERT INTO `roles` (`id`, `name`, `display_name`) VALUES
(1, 'director', 'Giám đốc'),
(2, 'accountant', 'Kế toán'),
(3, 'manager', 'Quản lý'),
(4, 'warehouse', 'Quản lý Kho'),
(5, 'production', 'Quản lý Sản xuất'),
(6, 'employee', 'Nhân viên');

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

--
-- Cấu trúc bảng cho bảng `salary_components`
--

CREATE TABLE `salary_components` (
  `id` int(11) NOT NULL,
  `component_code` varchar(30) NOT NULL,
  `component_name` varchar(150) NOT NULL,
  `component_name_en` varchar(150) DEFAULT NULL,
  `component_type` enum('earning','deduction','bonus') DEFAULT 'earning',
  `is_default` tinyint(1) DEFAULT 1,
  `sort_order` int(11) DEFAULT 0,
  `is_active` tinyint(1) DEFAULT 1,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Đang đổ dữ liệu cho bảng `salary_components`
--

INSERT INTO `salary_components` (`id`, `component_code`, `component_name`, `component_name_en`, `component_type`, `is_default`, `sort_order`, `is_active`, `created_at`) VALUES
(1, 'basic', 'Lương cơ bản', 'Basic salary', 'earning', 1, 1, 1, '2026-03-10 12:14:03'),
(2, 'meal', 'Trợ cấp ăn uống', 'Meal allowance', 'earning', 1, 2, 1, '2026-03-10 12:14:03'),
(3, 'clothes', 'Trợ cấp trang phục', 'Clothes allowance', 'earning', 1, 3, 1, '2026-03-10 12:14:03'),
(4, 'phone', 'Trợ cấp điện thoại', 'Mobile allowance', 'earning', 1, 4, 1, '2026-03-10 12:14:03'),
(5, 'transport', 'Trợ cấp xăng xe, đi lại', 'Gas - travelling allowance', 'earning', 1, 5, 1, '2026-03-10 12:14:03'),
(6, 'performance', 'Thưởng hiệu quả công việc', 'Job effectiveness bonus', 'bonus', 1, 6, 1, '2026-03-10 12:14:03'),
(7, 'housing', 'Trợ cấp nhà ở', 'Housing allowance', 'earning', 0, 7, 1, '2026-03-10 12:14:03'),
(8, 'responsibility', 'Phụ cấp trách nhiệm', 'Responsibility allowance', 'earning', 0, 8, 1, '2026-03-10 12:14:03'),
(9, 'seniority', 'Phụ cấp thâm niên', 'Seniority allowance', 'earning', 0, 9, 1, '2026-03-10 12:14:03'),
(10, 'hazard', 'Phụ cấp độc hại, nguy hiểm', 'Hazard allowance', 'earning', 0, 10, 1, '2026-03-10 12:14:03'),
(11, 'attendance_bonus', 'Chuyên Cần', 'Attendance Bonus', 'bonus', 1, 99, 1, '2026-03-11 04:26:12');

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

--
-- Cấu trúc bảng cho bảng `shift_schedules`
--

CREATE TABLE `shift_schedules` (
  `id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `shift_id` int(11) NOT NULL,
  `work_date` date NOT NULL,
  `note` varchar(200) DEFAULT NULL,
  `created_by` int(11) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Cấu trúc bảng cho bảng `users`
--

CREATE TABLE `users` (
  `id` int(11) NOT NULL,
  `employee_code` varchar(20) NOT NULL,
  `full_name` varchar(100) NOT NULL,
  `username` varchar(50) NOT NULL,
  `password_hash` varchar(255) NOT NULL,
  `email` varchar(100) DEFAULT NULL,
  `phone` varchar(20) DEFAULT NULL,
  `role_id` int(11) NOT NULL,
  `department_id` int(11) DEFAULT NULL,
  `is_active` tinyint(1) DEFAULT 1,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Đang đổ dữ liệu cho bảng `users`
--

INSERT INTO `users` (`id`, `employee_code`, `full_name`, `username`, `password_hash`, `email`, `phone`, `role_id`, `department_id`, `is_active`, `created_at`) VALUES
(1, 'NV001', 'Nguyễn Văn Giám', 'giamdoc', '$2y$10$emEr4RfAi1A..b3yVlpVq.mw83V9fV8Lgd3qHaAuFFxc5yzDIaltS', 'giamdoc@company.com', '', 1, 1, 1, '2026-03-10 06:57:59'),
(2, 'NV002', 'Trần Thị Kế', 'ketoan', '$2y$10$emEr4RfAi1A..b3yVlpVq.mw83V9fV8Lgd3qHaAuFFxc5yzDIaltS', 'ketoan@company.com', '', 2, 2, 1, '2026-03-10 06:57:59'),
(3, 'NV003', 'Lê Văn Quản', 'quanly', '$2y$10$emEr4RfAi1A..b3yVlpVq.mw83V9fV8Lgd3qHaAuFFxc5yzDIaltS', 'quanly@company.com', NULL, 3, 1, 1, '2026-03-10 06:57:59'),
(4, 'NV004', 'Phạm Thị Kho', 'quanlykho', '$2y$10$emEr4RfAi1A..b3yVlpVq.mw83V9fV8Lgd3qHaAuFFxc5yzDIaltS', 'kho@company.com', NULL, 4, 3, 1, '2026-03-10 06:57:59'),
(5, 'NV005', 'Hoàng Văn Sản', 'quanlysx', '$2y$10$emEr4RfAi1A..b3yVlpVq.mw83V9fV8Lgd3qHaAuFFxc5yzDIaltS', 'sanxuat@company.com', NULL, 5, 4, 1, '2026-03-10 06:57:59'),
(6, 'NV006', 'Nguyễn Thị Lan', 'nhanvien1', '$2y$10$emEr4RfAi1A..b3yVlpVq.mw83V9fV8Lgd3qHaAuFFxc5yzDIaltS', 'lan@company.com', '', 6, 4, 1, '2026-03-10 06:57:59'),
(7, 'NV007', 'Trần Văn Minh', 'nhanvien2', '$2y$10$zASMpVdS6edNlNCVwBmzXOowB340ETaoDye9jnh49Z4aLLdny97le', 'minh@company.com', '', 6, 4, 1, '2026-03-10 06:57:59'),
(8, 'NV008', 'trần văn ba', 'nhanvien3', '$2y$10$tqtGPhFApardPXfdvKxn1uvb26/ydhqkZfE8CWBx2CkMpzq8AdpfC', '', '', 6, 4, 1, '2026-03-11 03:30:28');

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

--
-- Cấu trúc bảng cho bảng `warehouse_imports`
--

CREATE TABLE `warehouse_imports` (
  `id` int(11) NOT NULL,
  `import_no` varchar(30) NOT NULL,
  `import_date` date NOT NULL,
  `product_code_id` int(11) NOT NULL,
  `description` varchar(500) DEFAULT NULL,
  `quantity` decimal(15,2) NOT NULL DEFAULT 0.00,
  `quantity_sent` decimal(15,2) NOT NULL DEFAULT 0.00,
  `note` text DEFAULT NULL,
  `status` enum('pending','partial','completed') DEFAULT 'pending',
  `created_by` int(11) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Đang đổ dữ liệu cho bảng `warehouse_imports`
--

INSERT INTO `warehouse_imports` (`id`, `import_no`, `import_date`, `product_code_id`, `description`, `quantity`, `quantity_sent`, `note`, `status`, `created_by`, `created_at`, `updated_at`) VALUES
(4, 'WI-20260311-0001', '2026-03-11', 1, NULL, 100000.00, 50000.00, '', 'partial', 1, '2026-03-11 15:02:58', '2026-03-11 15:03:09');

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

--
-- Cấu trúc bảng cho bảng `warehouse_stock`
--

CREATE TABLE `warehouse_stock` (
  `id` int(11) NOT NULL,
  `product_code_id` int(11) NOT NULL,
  `qty_pending` decimal(15,2) DEFAULT 0.00,
  `qty_completed` decimal(15,2) DEFAULT 0.00,
  `qty_defect` decimal(15,2) DEFAULT 0.00,
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Đang đổ dữ liệu cho bảng `warehouse_stock`
--

INSERT INTO `warehouse_stock` (`id`, `product_code_id`, `qty_pending`, `qty_completed`, `qty_defect`, `updated_at`) VALUES
(1, 1, 0.00, 0.00, 0.00, '2026-03-11 15:03:09');

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

--
-- Cấu trúc bảng cho bảng `warehouse_stock_log`
--

CREATE TABLE `warehouse_stock_log` (
  `id` int(11) NOT NULL,
  `product_code_id` int(11) NOT NULL,
  `log_date` date NOT NULL,
  `txn_type` enum('import','send_to_prod','return_completed','return_defect','return_pending','delivery') NOT NULL,
  `stock_type` enum('pending','completed','defect') NOT NULL,
  `qty_change` decimal(15,2) NOT NULL COMMENT 'Dương: nhập vào, Âm: xuất ra',
  `ref_table` varchar(50) DEFAULT NULL COMMENT 'Bảng nguồn tham chiếu',
  `ref_id` int(11) DEFAULT NULL COMMENT 'ID bản ghi nguồn',
  `note` text DEFAULT NULL,
  `created_by` int(11) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='Lịch sử biến động tồn kho (traceback)';

--
-- Đang đổ dữ liệu cho bảng `warehouse_stock_log`
--

INSERT INTO `warehouse_stock_log` (`id`, `product_code_id`, `log_date`, `txn_type`, `stock_type`, `qty_change`, `ref_table`, `ref_id`, `note`, `created_by`, `created_at`) VALUES
(1, 1, '2026-03-11', 'send_to_prod', 'pending', -80000.00, 'production_receipts', 4, 'Chuyển SX: PR-20260311-0001', 1, '2026-03-11 14:10:15'),
(2, 1, '2026-03-11', 'delivery', 'completed', -2500.00, 'delivery_notes', 6, 'Giao hàng: DN-20260311-0001', 1, '2026-03-11 14:10:53'),
(3, 1, '2026-03-11', 'return_pending', 'pending', 80000.00, 'day_close_log', NULL, 'Chốt ngày 2026-03-11', 1, '2026-03-11 14:25:03'),
(5, 1, '2026-03-11', 'send_to_prod', 'pending', -100000.00, 'production_receipts', 5, 'Chuyển SX: PR-20260311-0001', 1, '2026-03-11 14:57:30'),
(6, 1, '2026-03-11', 'import', 'pending', 100000.00, 'warehouse_imports', 4, 'Nhập kho: WI-20260311-0001', 1, '2026-03-11 15:02:58'),
(7, 1, '2026-03-11', 'send_to_prod', 'pending', -50000.00, 'production_receipts', 6, 'Chuyển SX: PR-20260311-0001', 1, '2026-03-11 15:03:09'),
(8, 1, '2026-03-11', 'delivery', 'completed', -5000.00, 'delivery_notes', 7, 'Giao hàng: DN-20260311-0001', 1, '2026-03-11 15:07:34');

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

--
-- Cấu trúc bảng cho bảng `work_shifts`
--

CREATE TABLE `work_shifts` (
  `id` int(11) NOT NULL,
  `shift_code` varchar(20) NOT NULL,
  `shift_name` varchar(100) NOT NULL,
  `start_time` time NOT NULL,
  `end_time` time NOT NULL,
  `late_threshold` int(11) DEFAULT 15,
  `break_minutes` int(11) DEFAULT 60,
  `work_hours` decimal(4,2) DEFAULT 8.00,
  `ot_multiplier` decimal(3,2) DEFAULT 1.50,
  `weekend_multiplier` decimal(3,2) DEFAULT 2.00,
  `holiday_multiplier` decimal(3,2) DEFAULT 3.00,
  `color` varchar(20) DEFAULT '#0d6efd',
  `is_active` tinyint(1) DEFAULT 1,
  `created_by` int(11) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Đang đổ dữ liệu cho bảng `work_shifts`
--

INSERT INTO `work_shifts` (`id`, `shift_code`, `shift_name`, `start_time`, `end_time`, `late_threshold`, `break_minutes`, `work_hours`, `ot_multiplier`, `weekend_multiplier`, `holiday_multiplier`, `color`, `is_active`, `created_by`, `created_at`) VALUES
(1, 'HANHCHINH', 'Hành chính', '08:00:00', '17:00:00', 15, 60, 8.00, 1.50, 2.00, 3.00, '#0d6efd', 1, NULL, '2026-03-10 09:51:42'),
(2, 'CA_SANG', 'Ca sáng', '06:00:00', '14:00:00', 15, 30, 8.00, 1.50, 2.00, 3.00, '#198754', 1, NULL, '2026-03-10 09:51:42'),
(3, 'CA_CHIEU', 'Ca chiều', '14:00:00', '22:00:00', 15, 30, 8.00, 1.50, 2.00, 3.00, '#fd7e14', 1, NULL, '2026-03-10 09:51:42'),
(4, 'CA_DEM', 'Ca đêm', '22:00:00', '06:00:00', 15, 30, 8.00, 2.00, 2.00, 3.00, '#6f42c1', 1, NULL, '2026-03-10 09:51:42');

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

--
-- Cấu trúc cho view `deliveries`
--
DROP TABLE IF EXISTS `deliveries`;

CREATE VIEW `deliveries`  AS SELECT `delivery_notes`.`id` AS `id`, `delivery_notes`.`delivery_no` AS `delivery_no`, `delivery_notes`.`delivery_date` AS `delivery_date`, `delivery_notes`.`customer_id` AS `customer_id`, `delivery_notes`.`status` AS `status`, `delivery_notes`.`note` AS `note`, `delivery_notes`.`created_by` AS `created_by`, `delivery_notes`.`created_at` AS `created_at`, `delivery_notes`.`updated_at` AS `updated_at`, NULL AS `total_amount` FROM `delivery_notes` ;

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

--
-- Cấu trúc cho view `delivery_items`
--
DROP TABLE IF EXISTS `delivery_items`;

CREATE VIEW `delivery_items`  AS SELECT `delivery_note_items`.`id` AS `id`, `delivery_note_items`.`delivery_note_id` AS `delivery_id`, `delivery_note_items`.`production_output_id` AS `production_output_id`, `delivery_note_items`.`product_code_id` AS `product_code_id`, `delivery_note_items`.`description` AS `description`, `delivery_note_items`.`quantity` AS `quantity`, NULL AS `unit_price`, NULL AS `total_price`, `delivery_note_items`.`note` AS `note` FROM `delivery_note_items` ;

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

--
-- Cấu trúc cho view `invoices_v`
--
DROP TABLE IF EXISTS `invoices_v`;

CREATE VIEW `invoices_v`  AS SELECT `invoices`.`id` AS `id`, `invoices`.`invoice_no` AS `invoice_no`, `invoices`.`invoice_date` AS `invoice_date`, `invoices`.`customer_id` AS `customer_id`, `invoices`.`total_amount` AS `total_amount`, `invoices`.`total_amount` AS `subtotal`, 0 AS `vat_rate`, 0 AS `vat_amount`, `invoices`.`note` AS `note`, `invoices`.`status` AS `status`, NULL AS `due_date`, NULL AS `delivery_id`, `invoices`.`created_by` AS `created_by`, `invoices`.`confirmed_by` AS `confirmed_by`, `invoices`.`confirmed_at` AS `confirmed_at`, `invoices`.`created_at` AS `created_at`, `invoices`.`updated_at` AS `updated_at` FROM `invoices` ;

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

--
-- Cấu trúc cho view `payments`
--
DROP TABLE IF EXISTS `payments`;

CREATE VIEW `payments`  AS SELECT `debt_payments`.`id` AS `id`, `debt_payments`.`invoice_id` AS `invoice_id`, `debt_payments`.`payment_date` AS `payment_date`, `debt_payments`.`amount` AS `amount`, `debt_payments`.`payment_method` AS `payment_method`, `debt_payments`.`reference_no` AS `note`, `debt_payments`.`created_by` AS `created_by`, `debt_payments`.`created_at` AS `created_at` FROM `debt_payments` WHERE `debt_payments`.`invoice_id` is not null ;

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

--
-- Cấu trúc cho view `prices`
--
DROP TABLE IF EXISTS `prices`;

CREATE VIEW `prices`  AS SELECT `product_prices`.`id` AS `id`, `product_prices`.`product_code_id` AS `product_code_id`, `product_prices`.`unit_price` AS `unit_price`, `product_prices`.`effective_from` AS `created_at`, 1 AS `is_active`, `product_prices`.`created_by` AS `created_by` FROM `product_prices` ;

--
-- Chỉ mục cho các bảng đã đổ
--

--
-- Chỉ mục cho bảng `attendance_audit_logs`
--
ALTER TABLE `attendance_audit_logs`
  ADD PRIMARY KEY (`id`);

--
-- Chỉ mục cho bảng `attendance_logs`
--
ALTER TABLE `attendance_logs`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `unique_attendance` (`user_id`,`work_date`),
  ADD KEY `shift_id` (`shift_id`);

--
-- Chỉ mục cho bảng `audit_log`
--
ALTER TABLE `audit_log`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_table_record` (`table_name`,`record_id`),
  ADD KEY `idx_changed_by` (`changed_by`),
  ADD KEY `idx_changed_at` (`changed_at`);

--
-- Chỉ mục cho bảng `communes`
--
ALTER TABLE `communes`
  ADD PRIMARY KEY (`code`),
  ADD KEY `district_code` (`district_code`);

--
-- Chỉ mục cho bảng `cost_entries`
--
ALTER TABLE `cost_entries`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_entry_date` (`entry_date`),
  ADD KEY `idx_cost_type` (`cost_type`);

--
-- Chỉ mục cho bảng `customers`
--
ALTER TABLE `customers`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uk_customer_code` (`customer_code`);

--
-- Chỉ mục cho bảng `day_close_log`
--
ALTER TABLE `day_close_log`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uq_close_date` (`close_date`);

--
-- Chỉ mục cho bảng `debt_payments`
--
ALTER TABLE `debt_payments`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_dp_debt` (`debt_id`),
  ADD KEY `idx_dp_date` (`payment_date`);

--
-- Chỉ mục cho bảng `debt_tracking`
--
ALTER TABLE `debt_tracking`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uk_debt_invoice` (`invoice_id`),
  ADD KEY `idx_debt_customer` (`customer_id`),
  ADD KEY `idx_debt_status` (`status`);

--
-- Chỉ mục cho bảng `delivery_notes`
--
ALTER TABLE `delivery_notes`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uk_delivery_no` (`delivery_no`),
  ADD KEY `idx_delivery_date` (`delivery_date`),
  ADD KEY `idx_delivery_status` (`status`),
  ADD KEY `idx_delivery_customer` (`customer_id`);

--
-- Chỉ mục cho bảng `delivery_note_items`
--
ALTER TABLE `delivery_note_items`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_dni_delivery_note` (`delivery_note_id`),
  ADD KEY `idx_dni_output` (`production_output_id`),
  ADD KEY `product_code_id` (`product_code_id`);

--
-- Chỉ mục cho bảng `departments`
--
ALTER TABLE `departments`
  ADD PRIMARY KEY (`id`);

--
-- Chỉ mục cho bảng `districts`
--
ALTER TABLE `districts`
  ADD PRIMARY KEY (`code`),
  ADD KEY `province_code` (`province_code`);

--
-- Chỉ mục cho bảng `document_sequences`
--
ALTER TABLE `document_sequences`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uk_doctype_date` (`doc_type`,`doc_date`);

--
-- Chỉ mục cho bảng `employee_profiles`
--
ALTER TABLE `employee_profiles`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `user_id` (`user_id`),
  ADD KEY `permanent_province` (`permanent_province`),
  ADD KEY `temp_province` (`temp_province`);

--
-- Chỉ mục cho bảng `employee_salaries`
--
ALTER TABLE `employee_salaries`
  ADD PRIMARY KEY (`id`),
  ADD KEY `user_id` (`user_id`),
  ADD KEY `component_id` (`component_id`),
  ADD KEY `created_by` (`created_by`),
  ADD KEY `updated_by` (`updated_by`);

--
-- Chỉ mục cho bảng `employee_shifts`
--
ALTER TABLE `employee_shifts`
  ADD PRIMARY KEY (`id`),
  ADD KEY `user_id` (`user_id`),
  ADD KEY `shift_id` (`shift_id`),
  ADD KEY `created_by` (`created_by`);

--
-- Chỉ mục cho bảng `ethnicities`
--
ALTER TABLE `ethnicities`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `name` (`name`);

--
-- Chỉ mục cho bảng `holidays`
--
ALTER TABLE `holidays`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `holiday_date` (`holiday_date`),
  ADD KEY `created_by` (`created_by`);

--
-- Chỉ mục cho bảng `invoices`
--
ALTER TABLE `invoices`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uk_invoice_no` (`invoice_no`),
  ADD KEY `idx_invoice_date` (`invoice_date`),
  ADD KEY `idx_invoice_status` (`status`),
  ADD KEY `idx_invoice_customer` (`customer_id`);

--
-- Chỉ mục cho bảng `invoice_delivery_notes`
--
ALTER TABLE `invoice_delivery_notes`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uk_inv_dn` (`invoice_id`,`delivery_note_id`),
  ADD KEY `delivery_note_id` (`delivery_note_id`);

--
-- Chỉ mục cho bảng `invoice_items`
--
ALTER TABLE `invoice_items`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_ii_invoice` (`invoice_id`),
  ADD KEY `idx_ii_dn` (`delivery_note_id`),
  ADD KEY `idx_ii_product` (`product_code_id`),
  ADD KEY `delivery_note_item_id` (`delivery_note_item_id`);

--
-- Chỉ mục cho bảng `kpi_assignments`
--
ALTER TABLE `kpi_assignments`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uq_assign` (`assign_date`,`user_id`),
  ADD KEY `fk_kpi_user` (`user_id`),
  ADD KEY `fk_kpi_manager` (`manager_id`);

--
-- Chỉ mục cho bảng `kpi_results`
--
ALTER TABLE `kpi_results`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uq_result` (`kpi_assignment_id`),
  ADD KEY `fk_result_assign` (`kpi_assignment_id`);

--
-- Chỉ mục cho bảng `leave_requests`
--
ALTER TABLE `leave_requests`
  ADD PRIMARY KEY (`id`),
  ADD KEY `user_id` (`user_id`),
  ADD KEY `approved_by` (`approved_by`);

--
-- Chỉ mục cho bảng `notifications`
--
ALTER TABLE `notifications`
  ADD PRIMARY KEY (`id`),
  ADD KEY `user_id` (`user_id`);

--
-- Chỉ mục cho bảng `overtime_requests`
--
ALTER TABLE `overtime_requests`
  ADD PRIMARY KEY (`id`),
  ADD KEY `user_id` (`user_id`),
  ADD KEY `approved_by` (`approved_by`);

--
-- Chỉ mục cho bảng `payroll_periods`
--
ALTER TABLE `payroll_periods`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uq_period` (`period_year`,`period_month`),
  ADD KEY `created_by` (`created_by`),
  ADD KEY `submitted_by` (`submitted_by`),
  ADD KEY `approved_by` (`approved_by`),
  ADD KEY `locked_by` (`locked_by`);

--
-- Chỉ mục cho bảng `payroll_slips`
--
ALTER TABLE `payroll_slips`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uq_slip` (`period_id`,`user_id`),
  ADD KEY `user_id` (`user_id`);

--
-- Chỉ mục cho bảng `production_outputs`
--
ALTER TABLE `production_outputs`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uk_output_no` (`output_no`),
  ADD KEY `idx_output_date` (`output_date`),
  ADD KEY `idx_production_receipt` (`production_receipt_id`),
  ADD KEY `product_code_id` (`product_code_id`);

--
-- Chỉ mục cho bảng `production_receipts`
--
ALTER TABLE `production_receipts`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uk_receipt_no` (`receipt_no`),
  ADD KEY `idx_receipt_date` (`receipt_date`),
  ADD KEY `idx_warehouse_import` (`warehouse_import_id`),
  ADD KEY `product_code_id` (`product_code_id`);

--
-- Chỉ mục cho bảng `production_stock`
--
ALTER TABLE `production_stock`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uq_prod_stock` (`product_code_id`,`stock_date`);

--
-- Chỉ mục cho bảng `product_codes`
--
ALTER TABLE `product_codes`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uk_product_code` (`product_code`);

--
-- Chỉ mục cho bảng `product_prices`
--
ALTER TABLE `product_prices`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_product_effective` (`product_code_id`,`effective_from`);

--
-- Chỉ mục cho bảng `provinces`
--
ALTER TABLE `provinces`
  ADD PRIMARY KEY (`code`);

--
-- Chỉ mục cho bảng `roles`
--
ALTER TABLE `roles`
  ADD PRIMARY KEY (`id`);

--
-- Chỉ mục cho bảng `salary_components`
--
ALTER TABLE `salary_components`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `component_code` (`component_code`);

--
-- Chỉ mục cho bảng `shift_schedules`
--
ALTER TABLE `shift_schedules`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `unique_schedule` (`user_id`,`work_date`),
  ADD KEY `shift_id` (`shift_id`),
  ADD KEY `created_by` (`created_by`);

--
-- Chỉ mục cho bảng `users`
--
ALTER TABLE `users`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `employee_code` (`employee_code`),
  ADD UNIQUE KEY `username` (`username`),
  ADD KEY `role_id` (`role_id`),
  ADD KEY `department_id` (`department_id`);

--
-- Chỉ mục cho bảng `warehouse_imports`
--
ALTER TABLE `warehouse_imports`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uk_import_no` (`import_no`),
  ADD KEY `idx_import_date` (`import_date`),
  ADD KEY `idx_import_status` (`status`),
  ADD KEY `product_code_id` (`product_code_id`);

--
-- Chỉ mục cho bảng `warehouse_stock`
--
ALTER TABLE `warehouse_stock`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uq_wh_stock` (`product_code_id`);

--
-- Chỉ mục cho bảng `warehouse_stock_log`
--
ALTER TABLE `warehouse_stock_log`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_log_date` (`log_date`),
  ADD KEY `idx_log_product` (`product_code_id`),
  ADD KEY `idx_log_type` (`txn_type`);

--
-- Chỉ mục cho bảng `work_shifts`
--
ALTER TABLE `work_shifts`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `shift_code` (`shift_code`),
  ADD KEY `created_by` (`created_by`);

--
-- AUTO_INCREMENT cho các bảng đã đổ
--

--
-- AUTO_INCREMENT cho bảng `attendance_audit_logs`
--
ALTER TABLE `attendance_audit_logs`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=7;

--
-- AUTO_INCREMENT cho bảng `attendance_logs`
--
ALTER TABLE `attendance_logs`
  MODIFY `id` bigint(20) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=10;

--
-- AUTO_INCREMENT cho bảng `audit_log`
--
ALTER TABLE `audit_log`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=10;

--
-- AUTO_INCREMENT cho bảng `cost_entries`
--
ALTER TABLE `cost_entries`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT cho bảng `customers`
--
ALTER TABLE `customers`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2;

--
-- AUTO_INCREMENT cho bảng `day_close_log`
--
ALTER TABLE `day_close_log`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2;

--
-- AUTO_INCREMENT cho bảng `debt_payments`
--
ALTER TABLE `debt_payments`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT cho bảng `debt_tracking`
--
ALTER TABLE `debt_tracking`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT cho bảng `delivery_notes`
--
ALTER TABLE `delivery_notes`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=8;

--
-- AUTO_INCREMENT cho bảng `delivery_note_items`
--
ALTER TABLE `delivery_note_items`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=8;

--
-- AUTO_INCREMENT cho bảng `departments`
--
ALTER TABLE `departments`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=6;

--
-- AUTO_INCREMENT cho bảng `document_sequences`
--
ALTER TABLE `document_sequences`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=23;

--
-- AUTO_INCREMENT cho bảng `employee_profiles`
--
ALTER TABLE `employee_profiles`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=6;

--
-- AUTO_INCREMENT cho bảng `employee_salaries`
--
ALTER TABLE `employee_salaries`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=16;

--
-- AUTO_INCREMENT cho bảng `employee_shifts`
--
ALTER TABLE `employee_shifts`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=8;

--
-- AUTO_INCREMENT cho bảng `ethnicities`
--
ALTER TABLE `ethnicities`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=55;

--
-- AUTO_INCREMENT cho bảng `holidays`
--
ALTER TABLE `holidays`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=33;

--
-- AUTO_INCREMENT cho bảng `invoices`
--
ALTER TABLE `invoices`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=3;

--
-- AUTO_INCREMENT cho bảng `invoice_delivery_notes`
--
ALTER TABLE `invoice_delivery_notes`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT cho bảng `invoice_items`
--
ALTER TABLE `invoice_items`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT cho bảng `kpi_assignments`
--
ALTER TABLE `kpi_assignments`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=10;

--
-- AUTO_INCREMENT cho bảng `kpi_results`
--
ALTER TABLE `kpi_results`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=35;

--
-- AUTO_INCREMENT cho bảng `leave_requests`
--
ALTER TABLE `leave_requests`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=3;

--
-- AUTO_INCREMENT cho bảng `notifications`
--
ALTER TABLE `notifications`
  MODIFY `id` bigint(20) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=9;

--
-- AUTO_INCREMENT cho bảng `overtime_requests`
--
ALTER TABLE `overtime_requests`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT cho bảng `payroll_periods`
--
ALTER TABLE `payroll_periods`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=4;

--
-- AUTO_INCREMENT cho bảng `payroll_slips`
--
ALTER TABLE `payroll_slips`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=9;

--
-- AUTO_INCREMENT cho bảng `production_outputs`
--
ALTER TABLE `production_outputs`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=6;

--
-- AUTO_INCREMENT cho bảng `production_receipts`
--
ALTER TABLE `production_receipts`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=7;

--
-- AUTO_INCREMENT cho bảng `production_stock`
--
ALTER TABLE `production_stock`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=4;

--
-- AUTO_INCREMENT cho bảng `product_codes`
--
ALTER TABLE `product_codes`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2;

--
-- AUTO_INCREMENT cho bảng `product_prices`
--
ALTER TABLE `product_prices`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT cho bảng `roles`
--
ALTER TABLE `roles`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=7;

--
-- AUTO_INCREMENT cho bảng `salary_components`
--
ALTER TABLE `salary_components`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=13;

--
-- AUTO_INCREMENT cho bảng `shift_schedules`
--
ALTER TABLE `shift_schedules`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT cho bảng `users`
--
ALTER TABLE `users`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=9;

--
-- AUTO_INCREMENT cho bảng `warehouse_imports`
--
ALTER TABLE `warehouse_imports`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=5;

--
-- AUTO_INCREMENT cho bảng `warehouse_stock`
--
ALTER TABLE `warehouse_stock`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=8;

--
-- AUTO_INCREMENT cho bảng `warehouse_stock_log`
--
ALTER TABLE `warehouse_stock_log`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=9;

--
-- AUTO_INCREMENT cho bảng `work_shifts`
--
ALTER TABLE `work_shifts`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=5;

--
-- Các ràng buộc cho các bảng đã đổ
--

--
-- Các ràng buộc cho bảng `attendance_logs`
--
ALTER TABLE `attendance_logs`
  ADD CONSTRAINT `attendance_logs_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`),
  ADD CONSTRAINT `attendance_logs_ibfk_2` FOREIGN KEY (`shift_id`) REFERENCES `work_shifts` (`id`);

--
-- Các ràng buộc cho bảng `communes`
--
ALTER TABLE `communes`
  ADD CONSTRAINT `communes_ibfk_1` FOREIGN KEY (`district_code`) REFERENCES `districts` (`code`);

--
-- Các ràng buộc cho bảng `debt_payments`
--
ALTER TABLE `debt_payments`
  ADD CONSTRAINT `debt_payments_ibfk_1` FOREIGN KEY (`debt_id`) REFERENCES `debt_tracking` (`id`);

--
-- Các ràng buộc cho bảng `debt_tracking`
--
ALTER TABLE `debt_tracking`
  ADD CONSTRAINT `debt_tracking_ibfk_1` FOREIGN KEY (`invoice_id`) REFERENCES `invoices` (`id`),
  ADD CONSTRAINT `debt_tracking_ibfk_2` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`);

--
-- Các ràng buộc cho bảng `delivery_notes`
--
ALTER TABLE `delivery_notes`
  ADD CONSTRAINT `delivery_notes_ibfk_1` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`);

--
-- Các ràng buộc cho bảng `delivery_note_items`
--
ALTER TABLE `delivery_note_items`
  ADD CONSTRAINT `delivery_note_items_ibfk_1` FOREIGN KEY (`delivery_note_id`) REFERENCES `delivery_notes` (`id`),
  ADD CONSTRAINT `delivery_note_items_ibfk_2` FOREIGN KEY (`production_output_id`) REFERENCES `production_outputs` (`id`),
  ADD CONSTRAINT `delivery_note_items_ibfk_3` FOREIGN KEY (`product_code_id`) REFERENCES `product_codes` (`id`);

--
-- Các ràng buộc cho bảng `districts`
--
ALTER TABLE `districts`
  ADD CONSTRAINT `districts_ibfk_1` FOREIGN KEY (`province_code`) REFERENCES `provinces` (`code`);

--
-- Các ràng buộc cho bảng `employee_profiles`
--
ALTER TABLE `employee_profiles`
  ADD CONSTRAINT `employee_profiles_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `employee_profiles_ibfk_2` FOREIGN KEY (`permanent_province`) REFERENCES `provinces` (`code`),
  ADD CONSTRAINT `employee_profiles_ibfk_3` FOREIGN KEY (`temp_province`) REFERENCES `provinces` (`code`);

--
-- Các ràng buộc cho bảng `employee_salaries`
--
ALTER TABLE `employee_salaries`
  ADD CONSTRAINT `employee_salaries_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `employee_salaries_ibfk_2` FOREIGN KEY (`component_id`) REFERENCES `salary_components` (`id`),
  ADD CONSTRAINT `employee_salaries_ibfk_3` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`),
  ADD CONSTRAINT `employee_salaries_ibfk_4` FOREIGN KEY (`updated_by`) REFERENCES `users` (`id`);

--
-- Các ràng buộc cho bảng `employee_shifts`
--
ALTER TABLE `employee_shifts`
  ADD CONSTRAINT `employee_shifts_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`),
  ADD CONSTRAINT `employee_shifts_ibfk_2` FOREIGN KEY (`shift_id`) REFERENCES `work_shifts` (`id`),
  ADD CONSTRAINT `employee_shifts_ibfk_3` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`);

--
-- Các ràng buộc cho bảng `holidays`
--
ALTER TABLE `holidays`
  ADD CONSTRAINT `holidays_ibfk_1` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`);

--
-- Các ràng buộc cho bảng `invoices`
--
ALTER TABLE `invoices`
  ADD CONSTRAINT `invoices_ibfk_1` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`);

--
-- Các ràng buộc cho bảng `invoice_delivery_notes`
--
ALTER TABLE `invoice_delivery_notes`
  ADD CONSTRAINT `invoice_delivery_notes_ibfk_1` FOREIGN KEY (`invoice_id`) REFERENCES `invoices` (`id`),
  ADD CONSTRAINT `invoice_delivery_notes_ibfk_2` FOREIGN KEY (`delivery_note_id`) REFERENCES `delivery_notes` (`id`);

--
-- Các ràng buộc cho bảng `invoice_items`
--
ALTER TABLE `invoice_items`
  ADD CONSTRAINT `invoice_items_ibfk_1` FOREIGN KEY (`invoice_id`) REFERENCES `invoices` (`id`),
  ADD CONSTRAINT `invoice_items_ibfk_2` FOREIGN KEY (`delivery_note_id`) REFERENCES `delivery_notes` (`id`),
  ADD CONSTRAINT `invoice_items_ibfk_3` FOREIGN KEY (`delivery_note_item_id`) REFERENCES `delivery_note_items` (`id`),
  ADD CONSTRAINT `invoice_items_ibfk_4` FOREIGN KEY (`product_code_id`) REFERENCES `product_codes` (`id`);

--
-- Các ràng buộc cho bảng `kpi_results`
--
ALTER TABLE `kpi_results`
  ADD CONSTRAINT `fk_result_assign` FOREIGN KEY (`kpi_assignment_id`) REFERENCES `kpi_assignments` (`id`);

--
-- Các ràng buộc cho bảng `leave_requests`
--
ALTER TABLE `leave_requests`
  ADD CONSTRAINT `leave_requests_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`),
  ADD CONSTRAINT `leave_requests_ibfk_2` FOREIGN KEY (`approved_by`) REFERENCES `users` (`id`);

--
-- Các ràng buộc cho bảng `notifications`
--
ALTER TABLE `notifications`
  ADD CONSTRAINT `notifications_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`);

--
-- Các ràng buộc cho bảng `overtime_requests`
--
ALTER TABLE `overtime_requests`
  ADD CONSTRAINT `overtime_requests_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`),
  ADD CONSTRAINT `overtime_requests_ibfk_2` FOREIGN KEY (`approved_by`) REFERENCES `users` (`id`);

--
-- Các ràng buộc cho bảng `payroll_periods`
--
ALTER TABLE `payroll_periods`
  ADD CONSTRAINT `payroll_periods_ibfk_1` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`),
  ADD CONSTRAINT `payroll_periods_ibfk_2` FOREIGN KEY (`submitted_by`) REFERENCES `users` (`id`),
  ADD CONSTRAINT `payroll_periods_ibfk_3` FOREIGN KEY (`approved_by`) REFERENCES `users` (`id`),
  ADD CONSTRAINT `payroll_periods_ibfk_4` FOREIGN KEY (`locked_by`) REFERENCES `users` (`id`);

--
-- Các ràng buộc cho bảng `payroll_slips`
--
ALTER TABLE `payroll_slips`
  ADD CONSTRAINT `payroll_slips_ibfk_1` FOREIGN KEY (`period_id`) REFERENCES `payroll_periods` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `payroll_slips_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`);

--
-- Các ràng buộc cho bảng `production_outputs`
--
ALTER TABLE `production_outputs`
  ADD CONSTRAINT `production_outputs_ibfk_1` FOREIGN KEY (`production_receipt_id`) REFERENCES `production_receipts` (`id`),
  ADD CONSTRAINT `production_outputs_ibfk_2` FOREIGN KEY (`product_code_id`) REFERENCES `product_codes` (`id`);

--
-- Các ràng buộc cho bảng `production_receipts`
--
ALTER TABLE `production_receipts`
  ADD CONSTRAINT `production_receipts_ibfk_1` FOREIGN KEY (`warehouse_import_id`) REFERENCES `warehouse_imports` (`id`),
  ADD CONSTRAINT `production_receipts_ibfk_2` FOREIGN KEY (`product_code_id`) REFERENCES `product_codes` (`id`);

--
-- Các ràng buộc cho bảng `product_prices`
--
ALTER TABLE `product_prices`
  ADD CONSTRAINT `product_prices_ibfk_1` FOREIGN KEY (`product_code_id`) REFERENCES `product_codes` (`id`);

--
-- Các ràng buộc cho bảng `shift_schedules`
--
ALTER TABLE `shift_schedules`
  ADD CONSTRAINT `shift_schedules_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`),
  ADD CONSTRAINT `shift_schedules_ibfk_2` FOREIGN KEY (`shift_id`) REFERENCES `work_shifts` (`id`),
  ADD CONSTRAINT `shift_schedules_ibfk_3` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`);

--
-- Các ràng buộc cho bảng `users`
--
ALTER TABLE `users`
  ADD CONSTRAINT `users_ibfk_1` FOREIGN KEY (`role_id`) REFERENCES `roles` (`id`),
  ADD CONSTRAINT `users_ibfk_2` FOREIGN KEY (`department_id`) REFERENCES `departments` (`id`);

--
-- Các ràng buộc cho bảng `warehouse_imports`
--
ALTER TABLE `warehouse_imports`
  ADD CONSTRAINT `warehouse_imports_ibfk_1` FOREIGN KEY (`product_code_id`) REFERENCES `product_codes` (`id`);

--
-- Các ràng buộc cho bảng `warehouse_stock_log`
--
ALTER TABLE `warehouse_stock_log`
  ADD CONSTRAINT `fk_wsl_pc` FOREIGN KEY (`product_code_id`) REFERENCES `product_codes` (`id`) ON UPDATE CASCADE;

--
-- Các ràng buộc cho bảng `work_shifts`
--
ALTER TABLE `work_shifts`
  ADD CONSTRAINT `work_shifts_ibfk_1` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`);
COMMIT;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
