-- Sales Types Table Creation
-- Following existing database patterns and conventions

CREATE TABLE `db_sales_types` (
  `id` int(5) NOT NULL,
  `store_id` int(11) DEFAULT NULL,
  `type_name` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `short_code` varchar(10) COLLATE utf8mb4_unicode_ci NOT NULL,
  `description` mediumtext COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `status` int(1) DEFAULT 1 COMMENT '1=Active, 0=Inactive',
  `sort_order` int(5) DEFAULT 0,
  `created_date` date DEFAULT NULL,
  `created_time` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `created_by` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `updated_date` date DEFAULT NULL,
  `updated_time` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `updated_by` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `company_id` int(5) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping initial data for table `db_sales_types`
--

INSERT INTO `db_sales_types` (`id`, `store_id`, `type_name`, `short_code`, `description`, `status`, `sort_order`, `created_date`, `created_time`, `created_by`, `company_id`) VALUES
(1, NULL, 'Shop', 'SHOP', 'Direct shop sales', 1, 1, CURDATE(), CURTIME(), 'System', NULL),
(2, NULL, 'Flipcart', 'FC', 'Flipcart marketplace sales', 1, 2, CURDATE(), CURTIME(), 'System', NULL),
(3, NULL, 'Amazon', 'AM', 'Amazon marketplace sales', 1, 3, CURDATE(), CURTIME(), 'System', NULL),
(4, NULL, 'Meesho', 'MO', 'Meesho marketplace sales', 1, 4, CURDATE(), CURTIME(), 'System', NULL);

--
-- Indexes for table `db_sales_types`
--

ALTER TABLE `db_sales_types`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `unique_type_store` (`type_name`, `store_id`),
  ADD UNIQUE KEY `unique_code_store` (`short_code`, `store_id`),
  ADD KEY `store_id` (`store_id`),
  ADD KEY `status` (`status`),
  ADD KEY `sort_order` (`sort_order`);

--
-- AUTO_INCREMENT for table `db_sales_types`
--

ALTER TABLE `db_sales_types`
  MODIFY `id` int(5) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=5;

--
-- Constraints for table `db_sales_types`
--

ALTER TABLE `db_sales_types`
  ADD CONSTRAINT `db_sales_types_ibfk_1` FOREIGN KEY (`store_id`) REFERENCES `db_store` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;

COMMIT;
