-- Migration: Create units table
-- Run this SQL script in your database to create the units table

CREATE TABLE IF NOT EXISTS `units` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `tenant_id` INT UNSIGNED NOT NULL,
  `name` VARCHAR(50) NOT NULL,
  `abbreviation` VARCHAR(10),
  `description` TEXT,
  `is_active` TINYINT(1) DEFAULT 1,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `tenant_id` (`tenant_id`),
  FOREIGN KEY (`tenant_id`) REFERENCES `tenants`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Insert default units for existing tenants
INSERT IGNORE INTO `units` (`tenant_id`, `name`, `abbreviation`, `description`) 
SELECT 
    id as tenant_id,
    'Pieces' as name,
    'pcs' as abbreviation,
    'Individual pieces or items' as description
FROM tenants;

INSERT IGNORE INTO `units` (`tenant_id`, `name`, `abbreviation`, `description`) 
SELECT 
    id as tenant_id,
    'Kilograms' as name,
    'kg' as abbreviation,
    'Metric weight measurement' as description
FROM tenants;

INSERT IGNORE INTO `units` (`tenant_id`, `name`, `abbreviation`, `description`) 
SELECT 
    id as tenant_id,
    'Liters' as name,
    'L' as abbreviation,
    'Metric volume measurement' as description
FROM tenants;

INSERT IGNORE INTO `units` (`tenant_id`, `name`, `abbreviation`, `description`) 
SELECT 
    id as tenant_id,
    'Meters' as name,
    'm' as abbreviation,
    'Metric length measurement' as description
FROM tenants;

INSERT IGNORE INTO `units` (`tenant_id`, `name`, `abbreviation`, `description`) 
SELECT 
    id as tenant_id,
    'Boxes' as name,
    'box' as abbreviation,
    'Packaged in boxes' as description
FROM tenants;

INSERT IGNORE INTO `units` (`tenant_id`, `name`, `abbreviation`, `description`) 
SELECT 
    id as tenant_id,
    'Cartons' as name,
    'ctn' as abbreviation,
    'Packaged in cartons' as description
FROM tenants;

INSERT IGNORE INTO `units` (`tenant_id`, `name`, `abbreviation`, `description`) 
SELECT 
    id as tenant_id,
    'Bottles' as name,
    'btl' as abbreviation,
    'Liquid containers' as description
FROM tenants;

INSERT IGNORE INTO `units` (`tenant_id`, `name`, `abbreviation`, `description`) 
SELECT 
    id as tenant_id,
    'Bags' as name,
    'bag' as abbreviation,
    'Packaged in bags' as description
FROM tenants;

INSERT IGNORE INTO `units` (`tenant_id`, `name`, `abbreviation`, `description`) 
SELECT 
    id as tenant_id,
    'Dozens' as name,
    'doz' as abbreviation,
    'Groups of 12 items' as description
FROM tenants;

INSERT IGNORE INTO `units` (`tenant_id`, `name`, `abbreviation`, `description`) 
SELECT 
    id as tenant_id,
    'Grams' as name,
    'g' as abbreviation,
    'Small weight measurement' as description
FROM tenants;
