

-- Sidebar headings and missing items
INSERT INTO translations (language_code, `key`, `value`, `context`) VALUES
-- English
('en','updates','Updates','navigation'),
('en','users_access','Users & Access','navigation'),
('en','hardware_devices','Hardware & Devices','navigation'),
('en','financials','Financials','navigation'),
('en','settings','Settings','navigation'),
('en','help','Help','navigation'),
('en','card_types','Card Types','navigation'),
('en','discount_management','Discount Management','navigation'),
('en','database_operation','Database Operation','navigation'),
('en','sync_settings','Sync Settings','navigation'),
('en','contact_support','Contact Support','navigation'),
-- Urdu
('ur','updates','اپڈیٹس','navigation'),
('ur','users_access','صارفین اور رسائی','navigation'),
('ur','hardware_devices','ہارڈویئر اور ڈیوائسز','navigation'),
('ur','financials','مالیات','navigation'),
('ur','settings','سیٹنگز','navigation'),
('ur','help','مدد','navigation'),
('ur','card_types','کارڈ اقسام','navigation'),
('ur','discount_management','ڈسکاؤنٹ مینجمنٹ','navigation'),
('ur','database_operation','ڈیٹا بیس آپریشن','navigation'),
('ur','sync_settings','سنگ سیٹنگز','navigation'),
('ur','contact_support','سپورٹ سے رابطہ کریں','navigation'),
-- Farsi
('fa','updates','به‌روزرسانی‌ها','navigation'),
('fa','users_access','کاربران و دسترسی','navigation'),
('fa','hardware_devices','سخت‌افزار و دستگاه‌ها','navigation'),
('fa','financials','مالی','navigation'),
('fa','settings','تنظیمات','navigation'),
('fa','help','راهنما','navigation'),
('fa','card_types','انواع کارت','navigation'),
('fa','discount_management','مدیریت تخفیف','navigation'),
('fa','database_operation','عملیات پایگاه داده','navigation'),
('fa','sync_settings','تنظیمات همگام‌سازی','navigation'),
('fa','contact_support','تماس با پشتیبانی','navigation')
ON DUPLICATE KEY UPDATE value = VALUES(value);

-- Dashboard labels
INSERT INTO translations (language_code, `key`, `value`, `context`) VALUES
-- English
('en','welcome_to','Welcome to','page_titles'),
('en','itr_pos_by_it_resources','ITR POS by IT Resources','labels'),
('en','todays_gross_sales','Today''s Gross Sales','labels'),
('en','todays_purchase','Today''s Purchase','labels'),
('en','total_gross_sales','Total Gross Sales','labels'),
('en','total_expense_amount','Total Expense Amount','labels'),
('en','customer_outstanding','Customer Outstanding','labels'),
('en','supplier_outstanding','Supplier Outstanding','labels'),
('en','purchase_invoice','Purchase Invoice','labels'),
('en','sales_invoice','Sales Invoice','labels'),
('en','todays_deliveries','Today''s Deliveries','labels'),
('en','pending_deliveries','Pending Deliveries','labels'),
('en','out_for_delivery','Out for Delivery','labels'),
('en','completed_deliveries','Completed Deliveries','labels'),
('en','top_selling_products','Top Selling Products','labels'),
('en','purchase_and_sales','Purchase & Sales','labels'),
('en','no_sales_data','No sales data available.','messages'),
('en','cash_balance','Cash Balance','labels'),
-- Urdu
('ur','welcome_to','خوش آمدید','page_titles'),
('ur','itr_pos_by_it_resources','آئی ٹی ریسورسز کا آئی ٹی آر پی او ایس','labels'),
('ur','todays_gross_sales','آج کی مجموعی سیلز','labels'),
('ur','todays_purchase','آج کی خریداری','labels'),
('ur','total_gross_sales','مجموعی سیلز','labels'),
('ur','total_expense_amount','کل اخراجات','labels'),
('ur','customer_outstanding','کسٹمر واجبات','labels'),
('ur','supplier_outstanding','سپلائر واجبات','labels'),
('ur','purchase_invoice','خریداری کی رسید','labels'),
('ur','sales_invoice','فروخت کی رسید','labels'),
('ur','todays_deliveries','آج کی ڈلیوریز','labels'),
('ur','pending_deliveries','زیر التواء ڈلیوریز','labels'),
('ur','out_for_delivery','ڈلیوری کے لیے روانہ','labels'),
('ur','completed_deliveries','مکمل شدہ ڈلیوریز','labels'),
('ur','top_selling_products','سب سے زیادہ فروخت ہونے والی اشیاء','labels'),
('ur','purchase_and_sales','خرید و فروخت','labels'),
('ur','no_sales_data','کوئی سیلز ڈیٹا دستیاب نہیں۔','messages'),
('ur','cash_balance','نقد بیلنس','labels'),
-- Farsi
('fa','welcome_to','خوش آمدید به','page_titles'),
('fa','itr_pos_by_it_resources','آی‌تی‌آر پی‌او‌اس توسط آی‌تی ریسورسز','labels'),
('fa','todays_gross_sales','فروش ناخالص امروز','labels'),
('fa','todays_purchase','خرید امروز','labels'),
('fa','total_gross_sales','کل فروش ناخالص','labels'),
('fa','total_expense_amount','مجموع هزینه‌ها','labels'),
('fa','customer_outstanding','مطالبات مشتری','labels'),
('fa','supplier_outstanding','بدهی به تامین‌کننده','labels'),
('fa','purchase_invoice','فاکتور خرید','labels'),
('fa','sales_invoice','فاکتور فروش','labels'),
('fa','todays_deliveries','تحویل‌های امروز','labels'),
('fa','pending_deliveries','تحویل‌های در انتظار','labels'),
('fa','out_for_delivery','ارسال برای تحویل','labels'),
('fa','completed_deliveries','تحویل‌های تکمیل‌شده','labels'),
('fa','top_selling_products','پرفروش‌ترین محصولات','labels'),
('fa','purchase_and_sales','خرید و فروش','labels'),
('fa','no_sales_data','داده‌ای برای فروش موجود نیست.','messages'),
('fa','cash_balance','موجودی نقد','labels')
ON DUPLICATE KEY UPDATE value = VALUES(value);



-- New sidebar headings/items in Arabic
INSERT INTO translations (language_code, `key`, `value`, `context`) VALUES
('ar','updates','التحديثات','navigation'),
('ar','users_access','المستخدمون والصلاحيات','navigation'),
('ar','hardware_devices','الأجهزة والمعدات','navigation'),
('ar','financials','المالية','navigation'),
('ar','settings','الإعدادات','navigation'),
('ar','help','مساعدة','navigation'),
('ar','card_types','أنواع البطاقات','navigation'),
('ar','discount_management','إدارة الخصومات','navigation'),
('ar','database_operation','عمليات قاعدة البيانات','navigation'),
('ar','sync_settings','إعدادات المزامنة','navigation'),
('ar','contact_support','اتصل بالدعم','navigation')
ON DUPLICATE KEY UPDATE value = VALUES(value);

-- New dashboard labels in Arabic
INSERT INTO translations (language_code, `key`, `value`, `context`) VALUES
('ar','welcome_to','مرحبًا بك في','page_titles'),
('ar','itr_pos_by_it_resources','نظام نقاط البيع ITR بواسطة IT Resources','labels'),
('ar','todays_gross_sales','إجمالي مبيعات اليوم','labels'),
('ar','todays_purchase','مشتريات اليوم','labels'),
('ar','total_gross_sales','إجمالي المبيعات','labels'),
('ar','total_expense_amount','إجمالي المصروفات','labels'),
('ar','customer_outstanding','مستحقات العملاء','labels'),
('ar','supplier_outstanding','مستحقات الموردين','labels'),
('ar','purchase_invoice','فواتير الشراء','labels'),
('ar','sales_invoice','فواتير البيع','labels'),
('ar','todays_deliveries','طلبات اليوم المُسلمة','labels'),
('ar','pending_deliveries','طلبات قيد التسليم','labels'),
('ar','out_for_delivery','خارج للتسليم','labels'),
('ar','completed_deliveries','طلبات مكتملة التسليم','labels'),
('ar','top_selling_products','المنتجات الأكثر مبيعًا','labels'),
('ar','purchase_and_sales','المشتريات والمبيعات','labels'),
('ar','no_sales_data','لا توجد بيانات مبيعات.','messages'),
('ar','cash_balance','رصيد النقد','labels')
ON DUPLICATE KEY UPDATE value = VALUES(value);









ALTER TABLE `items`
ADD COLUMN `is_serialized` TINYINT(1) NOT NULL DEFAULT 0 COMMENT 'Whether this item requires serial number tracking' AFTER `item_role`,
ADD INDEX `idx_items_is_serialized` (`is_serialized`);

-- =============================================
-- 2. Serial Master Table
-- =============================================

CREATE TABLE `item_serials` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `item_id` INT UNSIGNED NOT NULL COMMENT 'FK to items.id',
  `serial_no` VARCHAR(191) NOT NULL COMMENT 'Actual serial/IMEI/unique number',
  `details` TEXT DEFAULT NULL COMMENT 'Additional description',
  `cost_price` DECIMAL(10,2) DEFAULT NULL COMMENT 'Purchase cost for this unit',
  `selling_price` DECIMAL(10,2) DEFAULT NULL COMMENT 'Selling price for this unit',
  `warranty_name` VARCHAR(255) DEFAULT NULL COMMENT 'Warranty description',
  `warranty_expiry` DATE DEFAULT NULL COMMENT 'Warranty expiration date',
  `status_code` VARCHAR(3) NOT NULL DEFAULT 'SIN' COMMENT 'Current status',
  `branch_id` INT UNSIGNED DEFAULT NULL COMMENT 'Current location',
  `supplier_id` INT UNSIGNED DEFAULT NULL COMMENT 'Original supplier',
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  
  UNIQUE KEY `uq_item_serial` (`item_id`, `serial_no`),
  INDEX `idx_serial_item` (`item_id`),
  INDEX `idx_serial_no` (`serial_no`),
  INDEX `idx_serial_status` (`status_code`),
  INDEX `idx_serial_branch` (`branch_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =============================================
-- 3. Serial Transaction Log
-- =============================================

CREATE TABLE `item_serial_transactions` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `serial_id` BIGINT UNSIGNED NOT NULL COMMENT 'FK to item_serials.id',
  `item_id` INT UNSIGNED NOT NULL COMMENT 'FK to items.id',
  `trans_type` VARCHAR(3) NOT NULL COMMENT 'GRN/SAL/RET/TRI/TRO/DMG/etc',
  `doc_type` VARCHAR(20) NOT NULL COMMENT 'purchase/sale/sale_return/transfer',
  `doc_id` BIGINT UNSIGNED NOT NULL COMMENT 'FK to purchase_items.id or sale_items.id',
  `header_id` BIGINT UNSIGNED DEFAULT NULL COMMENT 'FK to purchases.id or sales.id',
  `branch_id` INT UNSIGNED DEFAULT NULL,
  `user_id` INT UNSIGNED DEFAULT NULL,
  `remarks` VARCHAR(500) DEFAULT NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  
  INDEX `idx_st_serial` (`serial_id`),
  INDEX `idx_st_item` (`item_id`),
  INDEX `idx_st_type` (`trans_type`),
  INDEX `idx_st_doc` (`doc_type`, `doc_id`),
  
  CONSTRAINT `fk_serial_trans_serial` 
    FOREIGN KEY (`serial_id`) REFERENCES `item_serials`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =============================================
-- 4. Warranty Master Table (Optional)
-- =============================================

CREATE TABLE `warranties` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(100) NOT NULL,
  `months` INT UNSIGNED NOT NULL DEFAULT 0,
  `days` INT UNSIGNED NOT NULL DEFAULT 0,
  `is_active` TINYINT(1) NOT NULL DEFAULT 1,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  
  UNIQUE KEY `uq_warranty_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Insert default warranties
INSERT INTO `warranties` (`name`, `months`, `days`) VALUES
('No Warranty', 0, 0),
('3 Months', 3, 0),
('6 Months', 6, 0),
('1 Year', 12, 0),
('2 Years', 24, 0);





-- Update English translation
UPDATE translations 
SET value = 'Net Profit Analysis' 
WHERE language_code = 'en' AND `key` = 'profit_loss_report';

-- Update Farsi translation (تحلیل سود خالص)
UPDATE translations 
SET value = 'تحلیل سود خالص' 
WHERE language_code = 'fa' AND `key` = 'profit_loss_report';

-- Update Urdu translation (نیٹ پرافٹ تجزیہ)
UPDATE translations 
SET value = 'نیٹ پرافٹ تجزیہ' 
WHERE language_code = 'ur' AND `key` = 'profit_loss_report';

-- If you have Arabic language, update it too (تحليل صافي الربح)
UPDATE translations 
SET value = 'تحليل صافي الربح' 
WHERE language_code = 'ar' AND `key` = 'profit_loss_report';






-- Step 1: Modify expenses table status to include 'reversed'
ALTER TABLE `expenses` 
MODIFY COLUMN `status` ENUM('pending','approved','rejected','reversed') DEFAULT 'approved';

-- Step 2: Add reversal tracking columns
ALTER TABLE `expenses` 
ADD COLUMN `reversal_date` DATE NULL AFTER `approved_at`,
ADD COLUMN `reversal_reason` TEXT NULL AFTER `reversal_date`,
ADD COLUMN `reversal_journal_id` INT NULL AFTER `reversal_reason`;

-- Step 3: Add index for better query performance
ALTER TABLE `expenses`
ADD KEY `idx_status` (`status`),
ADD KEY `idx_reversal_journal` (`reversal_journal_id`);



ALTER TABLE `sup_payments` 
ADD COLUMN `discount_amount` DECIMAL(10,2) DEFAULT 0.00 AFTER `amount_paid`;







ALTER TABLE `production_items`
ADD COLUMN `material_store_id` INT NULL COMMENT 'Store to deduct materials from' AFTER `production_order_id`,
ADD COLUMN `finish_store_id` INT NULL COMMENT 'Store to add finished goods to' AFTER `material_store_id`,
ADD KEY `material_store_id` (`material_store_id`),
ADD KEY `finish_store_id` (`finish_store_id`);



-- 2a. Add 'production' to reference_type enum if not already present
ALTER TABLE `inventory_movements` 
MODIFY COLUMN `reference_type` enum('purchase','sale','manual','other','sale_return','purchase_return','stock_transfer_out','stock_transfer_in','production') DEFAULT 'manual';





ALTER TABLE `inventory_movements` 
MODIFY COLUMN `reference_type` 
enum('purchase','sale','manual','other','sale_return','purchase_return','stock_transfer_out','stock_transfer_in','production','opening_balance') 
DEFAULT 'manual';

-- Add Opening Balances translations for all supported languages
INSERT INTO `translations` (`language_code`, `key`, `value`, `context`) VALUES
('en', 'opening_balances', 'Opening Balances', 'navigation'),
('ar', 'opening_balances', 'الأرصدة الافتتاحية', 'navigation'),
('ur', 'opening_balances', 'ابتدائی بیلنس', 'navigation'),
('fa', 'opening_balances', 'موجودی اولیه', 'navigation');



-- 1. Create discount_categories table
CREATE TABLE IF NOT EXISTS discount_categories (
    id INT AUTO_INCREMENT PRIMARY KEY,
    category_name VARCHAR(100) NOT NULL,
    display_order INT DEFAULT 0,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;


-- 2. Add category_id column to discounts table (THIS IS THE MISSING COLUMN)
ALTER TABLE discounts ADD COLUMN category_id INT DEFAULT NULL AFTER discount_cap;
ALTER TABLE discounts ADD INDEX idx_category_id (category_id);





-- =====================================================
-- 1. Create fiscal_period_setup table (matches idea.sql)
-- =====================================================
CREATE TABLE IF NOT EXISTS `fiscal_period_setup` (
    `start_year` VARCHAR(50) NOT NULL PRIMARY KEY,
    `start_month` INT NULL,
    `start_day` INT NULL,
    `month_count` INT NULL,
    `open_year` INT NULL,
    KEY `idx_open_year` (`open_year`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;


-- =====================================================
-- 2. Add essential columns to fiscal_periods
-- =====================================================
-- Add period_code (like idea.sql PeriodCode)
ALTER TABLE `fiscal_periods` 
ADD COLUMN `period_code` VARCHAR(50) NULL AFTER `id`;

-- Add period_year (like idea.sql PeriodYear)
ALTER TABLE `fiscal_periods` 
ADD COLUMN `period_year` INT NULL AFTER `period_name`;

-- Add period_status (like idea.sql PeriodStatus)
ALTER TABLE `fiscal_periods` 
ADD COLUMN `period_status` VARCHAR(50) DEFAULT 'Open' AFTER `is_closed`;

-- Add unique index on period_code
ALTER TABLE `fiscal_periods` 
ADD UNIQUE KEY `unique_period_code` (`period_code`);

-- Add index on period_status
ALTER TABLE `fiscal_periods` 
ADD KEY `idx_period_status` (`period_status`);


-- =====================================================
-- 3. Link journal_entries to periods
-- =====================================================
ALTER TABLE `journal_entries` 
ADD COLUMN `period_id` INT NULL AFTER `entry_date`;

ALTER TABLE `journal_entries` 
ADD KEY `idx_period_id` (`period_id`);

