
ALTER TABLE `items`
MODIFY COLUMN `item_role` ENUM('stock', 'material', 'non_stock') NOT NULL DEFAULT 'stock';


-- 1. Standardize UOM Groups Table
DROP TABLE IF EXISTS `uom_groups`;
CREATE TABLE `uom_groups` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `code` VARCHAR(10) NOT NULL,
  `name` VARCHAR(50) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `code` (`code`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4;

-- 2. Standardize UOMs Table
DROP TABLE IF EXISTS `uoms`;
CREATE TABLE `uoms` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(50) NOT NULL,
  `symbol` VARCHAR(10) NOT NULL,
  `group_id` INT NOT NULL,
  `base_quantity` DECIMAL(10,4) DEFAULT '1.0000', -- conversion to base unit
  `is_base` TINYINT(1) DEFAULT 0, -- 1 if this is the base unit for the group
  PRIMARY KEY (`id`),
  KEY `group_id` (`group_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4;

-- 3. Example UOM Groups
INSERT INTO `uom_groups` (`id`, `code`, `name`) VALUES
(1, 'CNT', 'Count'),
(2, 'WGT', 'Weight'),
(3, 'VLM', 'Volume'),
(4, 'LGT', 'Length');

-- 4. Example UOMs
INSERT INTO `uoms` (`id`, `name`, `symbol`, `group_id`, `base_quantity`, `is_base`) VALUES
(1, 'Piece', 'pc', 1, 1.0000, 1),
(2, 'Dozen', 'dz', 1, 12.0000, 0),
(3, 'Gram', 'g', 2, 1.0000, 1),
(4, 'Kilogram', 'kg', 2, 1000.0000, 0),
(5, 'Milligram', 'mg', 2, 0.0010, 0),
(6, 'Liter', 'l', 3, 1.0000, 1),
(7, 'Milliliter', 'ml', 3, 0.0010, 0),
(8, 'Centimeter', 'cm', 4, 1.0000, 1),
(9, 'Meter', 'm', 4, 100.0000, 0);

-- 5. (Optional) Update your items table to ensure every item has a uom_id
ALTER TABLE `items`
  MODIFY COLUMN `uom_id` INT NOT NULL,
  ADD CONSTRAINT `fk_items_uom` FOREIGN KEY (`uom_id`) REFERENCES `uoms`(`id`);


ALTER TABLE bom_components MODIFY COLUMN quantity DECIMAL(12,6) NOT NULL;

ALTER TABLE inventory_movements MODIFY COLUMN quantity DECIMAL(12,6) NOT NULL;


INSERT INTO settings (
    category_id, name, code, type, value, default_value, description, is_active
) VALUES (
    4,
    'Show Material Items on POS',
    'show_material_items_on_pos',
    'boolean',
    '0',
    '0',
    'Show material items on POS item list',
    1
); 


ALTER TABLE sale_returns ADD COLUMN customer_remarks VARCHAR(255) NULL AFTER remarks;
ALTER TABLE purchase_returns ADD COLUMN customer_remarks VARCHAR(255) NULL AFTER remarks;


ALTER TABLE inventory_movements 
MODIFY COLUMN reference_type ENUM('purchase','sale','manual','other','sale_return','purchase_return') DEFAULT 'manual';


-- Create table for video help links
CREATE TABLE IF NOT EXISTS `video_help` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `page_name` varchar(100) NOT NULL,
  `page_title` varchar(255) NOT NULL,
  `video_url` text NOT NULL,
  `video_title` varchar(255) NOT NULL,
  `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`),
  UNIQUE KEY `page_name` (`page_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Insert some sample data for common pages
INSERT INTO `video_help` (`page_name`, `page_title`, `video_url`, `video_title`, `description`) VALUES
('item_list', 'Item List', 'https://www.youtube.com/watch?v=9RousDb6aLE', 'How to Add/Manage Products', 'Learn how to add new products, edit existing ones, and manage your product inventory.'),
('pos', 'POS System', 'https://www.youtube.com/watch?v=example2', 'How to Use POS System', 'Complete guide on using the Point of Sale system for sales transactions.'),
('customer_list', 'Customer List', 'https://www.youtube.com/watch?v=example3', 'Customer Management', 'How to add, edit, and manage customer information.'),
('supplier_list', 'Supplier List', 'https://www.youtube.com/watch?v=example4', 'Supplier Management', 'Learn how to manage your suppliers and their information.'),
('sales_report', 'Sales Report', 'https://www.youtube.com/watch?v=example5', 'Understanding Sales Reports', 'How to read and analyze your sales reports.'),
('purchase_list', 'Purchase List', 'https://www.youtube.com/watch?v=example6', 'Purchase Management', 'How to manage purchases and purchase orders.'),
('inventory_report', 'Inventory Report', 'https://www.youtube.com/watch?v=example7', 'Inventory Management', 'Understanding your inventory levels and stock reports.'),
('settings', 'System Settings', 'https://www.youtube.com/watch?v=example8', 'System Configuration', 'How to configure your system settings and preferences.'); 





INSERT INTO `video_help` (`id`, `page_name`, `page_title`, `video_url`, `video_title`, `description`, `is_active`, `created_at`, `updated_at`) VALUES (NULL, 'expense_list', 'Expense List', 'https://youtu.be/HWp9z6GPpZs?si=spRqItxMw67hMZ7X', 'Expense Module ', 'How to Add & Manage Expenses Easily', '1', '2025-07-26 18:59:57', '2025-07-26 18:59:57');

INSERT INTO `video_help` (`id`, `page_name`, `page_title`, `video_url`, `video_title`, `description`, `is_active`, `created_at`, `updated_at`) VALUES (NULL, 'stock_balance_report', 'Stock Balance Report', 'https://www.youtube.com/watch?v=QsARFJimbjc', 'ITR POS Stock Balance Report', 'Track Stock In, Out & Quantity Easily', '1', '2025-07-26 18:59:57', '2025-07-26 18:59:57');

-- Add missing columns to users table
ALTER TABLE users ADD COLUMN is_active TINYINT(1) DEFAULT 1;
ALTER TABLE users ADD COLUMN last_login DATETIME NULL;
ALTER TABLE users ADD COLUMN created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP;
ALTER TABLE users ADD COLUMN updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;

-- Add indexes for better performance
ALTER TABLE users ADD INDEX idx_username (username);
ALTER TABLE users ADD INDEX idx_email (email);
ALTER TABLE users ADD INDEX idx_is_active (is_active);

-- Update existing users to be active by default
UPDATE users SET is_active = 1 WHERE is_active IS NULL;
