CREATE TABLE `users` (
    `id` BIGINT AUTO_INCREMENT PRIMARY KEY,
    `company_id` BIGINT,
    `name` VARCHAR(120),
    `email` VARCHAR(120),
    `email_verified_at` DATETIME DEFAULT NULL,
    `phone` VARCHAR(50),
    `password` VARCHAR(50),
    `is_active` TINYINT DEFAULT 1, 
    `is_admin` TINYINT DEFAULT 0,
    `is_otp_enabled` TINYINT DEFAULT 0,
    `remember_token` VARCHAR(50),
    `permissions` JSON,
    `role_name` VARCHAR(50),
    `user_role_id` INT DEFAULT 0,
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `deleted_at` DATETIME DEFAULT NULL
);

CREATE TABLE `user_roles` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `company_id` BIGINT,
    `name` VARCHAR(120),
    `code` VARCHAR(120),
    `permissions` JSON DEFAULT NULL,
    `created_by` BIGINT,
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `deleted_at` DATETIME DEFAULT NULL
);

CREATE TABLE `currencies` (
    `id` BIGINT AUTO_INCREMENT PRIMARY KEY,
    `name` VARCHAR(120),
    `code` VARCHAR(120),
    `symbol` VARCHAR(120),
    `is_default` TINYINT DEFAULT 0,
    `is_active` TINYINT DEFAULT 1,
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `deleted_at` DATETIME DEFAULT NULL
);

CREATE TABLE `customers` (
    `id` BIGINT AUTO_INCREMENT PRIMARY KEY,
    `company_id` BIGINT,
    `user_id` BIGINT,
    `name` VARCHAR(120),
    `email` VARCHAR(120),
    `phone` VARCHAR(50),
    `country` INT,
    `city` INT,
    `state` INT,
    `address` VARCHAR(400),
    `zip_code` VARCHAR(50),
    `tax_number` VARCHAR(50),
    `tax_type` VARCHAR(50),
    `tax_office` VARCHAR(50),
    `website` VARCHAR(50),
    `group` VARCHAR(50),
    `notes` TEXT DEFAULT NULL,
    `orders_completed_count` INT DEFAULT 0,
    `orders_uncompleted_count` INT DEFAULT 0,
    `orders_total_count` INT DEFAULT 0,
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `deleted_at` DATETIME DEFAULT NULL
);

CREATE TABLE `orders` (
    `id` BIGINT AUTO_INCREMENT PRIMARY KEY,
    `company_id` BIGINT,
    `user_id` BIGINT,
    `customer_id` BIGINT,
    `order_id` VARCHAR(50),
    `name` VARCHAR(120),
    `order_date` DATETIME,
    `delivery_date` DATETIME DEFAULT NULL,
    `currency_id` BIGINT,
    `products` TEXT DEFAULT NULL,
    `total_price` DOUBLE,
    `discount_amount` DOUBLE DEFAULT 0,
    `discount_rate` DOUBLE DEFAULT 0,
    `tax_amount` DOUBLE DEFAULT 0,
    `tax_rate` DOUBLE DEFAULT 0,
    `general_total_price` DOUBLE,
    `advance` DOUBLE,
    `balance` DOUBLE,
    `total_collection` DOUBLE DEFAULT 0,
    `remaining_collection` DOUBLE DEFAULT 0,
    `is_collection_completed` TINYINT DEFAULT 0,
    `status` VARCHAR(50),
    `notes` TEXT DEFAULT NULL,
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `deleted_at` DATETIME DEFAULT NULL
);

CREATE TABLE `order_products` (
    `id` BIGINT AUTO_INCREMENT PRIMARY KEY,
    `user_id` BIGINT,
    `company_id` BIGINT,
    `customer_id` BIGINT,
    `order_id` BIGINT,
    `product_id` BIGINT,
    `name` VARCHAR(120),
    `type` VARCHAR(120),
    `color` VARCHAR(120),
    `currency_id` BIGINT,
    `unit_price` DOUBLE,
    `total_price` DOUBLE,
    `quantity` INT,
    `other_features` TEXT DEFAULT NULL,
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `deleted_at` DATETIME DEFAULT NULL
);

CREATE TABLE `order_statuses` (
    `id` BIGINT AUTO_INCREMENT PRIMARY KEY,
    `name` VARCHAR(120),
    `code` VARCHAR(120),
    `theme` VARCHAR(120),
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `deleted_at` DATETIME DEFAULT NULL
);

CREATE TABLE `products` (
    `id` BIGINT AUTO_INCREMENT PRIMARY KEY,
    `company_id` BIGINT,
    `user_id` BIGINT,
    `name` VARCHAR(120),
    `type` VARCHAR(120),
    `color` VARCHAR(120),
    `quantity` INT,
    `currency_id` BIGINT,
    `unit_price` DOUBLE,
    `sale_price` DOUBLE,
    `other_features` TEXT DEFAULT NULL,
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `deleted_at` DATETIME DEFAULT NULL
);

CREATE TABLE `product_stocks` (
    `id` BIGINT AUTO_INCREMENT PRIMARY KEY,
    `company_id` BIGINT,
    `product_id` BIGINT,
    `warehouse_id` BIGINT,
    `quantity` INT,
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `deleted_at` DATETIME DEFAULT NULL
);

CREATE TABLE `shipments` (
    `id` BIGINT AUTO_INCREMENT PRIMARY KEY,
    `company_id` BIGINT,
    `user_id` BIGINT,
    `customer_id` BIGINT,
    `shipment_id` BIGINT,
    `name` VARCHAR(120),
    `shipment_date` DATETIME,
    `delivery_date` DATETIME DEFAULT NULL,
    `currency_id` BIGINT,
    `total_price` DOUBLE,
    `tax_amount` DOUBLE DEFAULT 0,
    `tax_rate` DOUBLE DEFAULT 0,
    `general_total_price` DOUBLE,
    `advance` DOUBLE DEFAULT 0,
    `balance` DOUBLE,
    `total_collection` DOUBLE DEFAULT 0,
    `remaining_collection` DOUBLE DEFAULT 0,
    `is_collection_completed` TINYINT DEFAULT 0,
    `status` VARCHAR(50),
    `notes` TEXT DEFAULT NULL,
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `deleted_at` DATETIME DEFAULT NULL
);

CREATE TABLE `shipment_products` (
    `id` BIGINT AUTO_INCREMENT PRIMARY KEY,
    `user_id` BIGINT,
    `company_id` BIGINT,
    `customer_id` BIGINT,
    `shipment_id` BIGINT,
    `product_id` BIGINT,
    `quantity` INT,
    `unit_price` DOUBLE,
    `total_price` DOUBLE,
    `original_quantity` INT,
    `original_total_price` DOUBLE,
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `deleted_at` DATETIME DEFAULT NULL
);

CREATE TABLE `suppliers` (
    `id` BIGINT AUTO_INCREMENT PRIMARY KEY,
    `user_id` BIGINT,
    `company_id` BIGINT,
    `name` VARCHAR(120),
    `email` VARCHAR(120),
    `phone` VARCHAR(50),
    `address` VARCHAR(400),
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `deleted_at` DATETIME DEFAULT NULL
);

CREATE TABLE `supplies` (
    `id` BIGINT AUTO_INCREMENT PRIMARY KEY,
    `user_id` BIGINT,
    `company_id` BIGINT,
    `supplier_id` BIGINT,
    `supply_id` VARCHAR(50) DEFAULT NULL,
    `name` VARCHAR(120),
    `products` TEXT DEFAULT NULL,
    `supply_date` DATETIME,
    `currency_id` BIGINT,
    `total_price` DOUBLE,
    `discount_amount` DOUBLE DEFAULT 0,
    `discount_rate` DOUBLE DEFAULT 0,
    `tax_amount` DOUBLE DEFAULT 0,
    `tax_rate` DOUBLE DEFAULT 0,
    `general_total_price` DOUBLE,
    `advance` DOUBLE DEFAULT 0,
    `balance` DOUBLE,
    `total_collection` DOUBLE DEFAULT 0,
    `remaining_collection` DOUBLE DEFAULT 0,
    `notes` TEXT DEFAULT NULL,
    `status` VARCHAR(50),
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `deleted_at` DATETIME DEFAULT NULL
);

CREATE TABLE `supply_statuses` (
    `id` BIGINT AUTO_INCREMENT PRIMARY KEY,
    `name` VARCHAR(120),
    `code` VARCHAR(120),
    `theme` VARCHAR(120),
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `deleted_at` DATETIME DEFAULT NULL
);

CREATE TABLE `warehouses` (
    `id` BIGINT AUTO_INCREMENT PRIMARY KEY,
    `company_id` BIGINT,
    `name` VARCHAR(120),
    `area_id` BIGINT,
    `created_by` BIGINT,
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `deleted_at` DATETIME DEFAULT NULL
);

CREATE TABLE `warehouse_areas` (
    `id` BIGINT AUTO_INCREMENT PRIMARY KEY,
    `company_id` BIGINT,
    `name` VARCHAR(120),
    `state_id` INT DEFAULT 0,
    `county_id` INT DEFAULT 0,
    `neighborhood_id` INT DEFAULT 0,
    `suburb_id` INT DEFAULT 0,
    `created_by` BIGINT,
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `deleted_at` DATETIME DEFAULT NULL
);

CREATE TABLE `companies` (
    `id` BIGINT AUTO_INCREMENT PRIMARY KEY,
    `name` VARCHAR(120),
    `admin_user_id` BIGINT,
    `demo_start_date` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `demo_end_date` DATETIME DEFAULT NULL,
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `deleted_at` DATETIME DEFAULT NULL
);

CREATE TABLE `system_modules` (
    `id` BIGINT AUTO_INCREMENT PRIMARY KEY,
    `name` VARCHAR(120),
    `code` VARCHAR(120),
    `icon` VARCHAR(120),
    `permission_types` VARCHAR(120),
    `order_number` INT,
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `deleted_at` DATETIME DEFAULT NULL
);

CREATE TABLE `system_module_permission_types` (
    `id` BIGINT AUTO_INCREMENT PRIMARY KEY,
    `name` VARCHAR(120),
    `code` VARCHAR(120),
    `order_number` INT DEFAULT 9999,
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `deleted_at` DATETIME DEFAULT NULL
);