--
-- Table structure for table `profit_distribution_records`.
-- This is the complete and final schema for the table.
-- If the table already exists with a different structure, you should drop it before running this script.
-- Example: DROP TABLE IF EXISTS `profit_distribution_records`;
--

CREATE TABLE `profit_distribution_records` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `settlement_month` varchar(7) NOT NULL,
  `total_revenue` decimal(15,2) NOT NULL,
  `total_payroll_expense` decimal(15,2) NOT NULL,
  `other_expenses_total` decimal(15,2) NOT NULL,
  `other_expenses_details` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`other_expenses_details`)),
  `owner_id` int(11) NOT NULL,
  `partner_id` int(11) DEFAULT NULL,
  `owner_final_payout` decimal(15,2) NOT NULL,
  `partner_final_payout` decimal(15,2) DEFAULT NULL,
  `owner_hourly_rate` decimal(10,2) DEFAULT NULL,
  `partner_hourly_rate` decimal(10,2) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `settlement_month` (`settlement_month`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Table structure for table `stock_counts`
--
DROP TABLE IF EXISTS `stock_counts`;
CREATE TABLE `stock_counts` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(255) NOT NULL,
  `type` enum('FULL','BY_LOCATION','BY_PRODUCT') NOT NULL,
  `location_ids` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`location_ids`)),
  `product_ids` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`product_ids`)),
  `created_by` int(11) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `completed_at` timestamp NULL DEFAULT NULL,
  `status` enum('PENDING','IN_PROGRESS','COMPLETED','CANCELLED') NOT NULL DEFAULT 'PENDING',
  PRIMARY KEY (`id`),
  KEY `created_by` (`created_by`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Table structure for table `stock_count_items`
--
DROP TABLE IF EXISTS `stock_count_items`;
CREATE TABLE `stock_count_items` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `stock_count_id` int(11) NOT NULL,
  `product_id` int(11) NOT NULL,
  `location_id` int(11) NOT NULL,
  `expiration_date` date DEFAULT NULL,
  `system_quantity` int(11) NOT NULL,
  `counted_quantity` int(11) DEFAULT NULL,
  `counted_by` int(11) DEFAULT NULL,
  `counted_at` timestamp NULL DEFAULT NULL,
  `discrepancy` int(11) GENERATED ALWAYS AS (IFNULL(`counted_quantity`, 0) - `system_quantity`) STORED,
  `status` enum('PENDING','CONFIRMED') NOT NULL DEFAULT 'PENDING',
  PRIMARY KEY (`id`),
  UNIQUE KEY `stock_count_batch` (`stock_count_id`,`product_id`,`location_id`,`expiration_date`),
  KEY `product_id` (`product_id`),
  KEY `location_id` (`location_id`),
  KEY `counted_by` (`counted_by`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
