-- ============================================================================
-- DezKala Accounting System Database Schema
-- ============================================================================

-- 1. Inventory Management Tables
CREATE TABLE `inventories` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `product_id` BIGINT NOT NULL,
  `warehouse_id` INT NOT NULL,
  `quantity` INT NOT NULL DEFAULT 0,
  `reorder_level` INT NOT NULL DEFAULT 10,
  `last_stock_check` TIMESTAMP NULL,
  `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_product_warehouse` (`product_id`, `warehouse_id`),
  KEY `idx_warehouse` (`warehouse_id`),
  KEY `idx_reorder` (`reorder_level`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 2. Warehouse Management
CREATE TABLE `warehouses` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(255) NOT NULL,
  `location` VARCHAR(255),
  `capacity` INT DEFAULT 1000,
  `manager_name` VARCHAR(255),
  `phone` VARCHAR(20),
  `status` ENUM('active', 'inactive', 'maintenance') DEFAULT 'active',
  `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 3. Inventory Movements/Transactions
CREATE TABLE `inventory_movements` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `product_id` BIGINT NOT NULL,
  `warehouse_id` INT NOT NULL,
  `movement_type` ENUM(
    'purchase', 'sale', 'adjustment', 'transfer_out', 'transfer_in',
    'damage', 'return', 'count', 'waste', 'other'
  ) NOT NULL,
  `quantity` INT NOT NULL,
  `reference_id` VARCHAR(255),
  `reference_type` VARCHAR(255),
  `notes` TEXT,
  `user_id` BIGINT,
  `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_product` (`product_id`),
  KEY `idx_warehouse` (`warehouse_id`),
  KEY `idx_movement_type` (`movement_type`),
  KEY `idx_date` (`created_at`),
  KEY `idx_reference` (`reference_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 4. Intra-Warehouse Transfer Tracking
CREATE TABLE `inventory_transfers` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `product_id` BIGINT NOT NULL,
  `source_warehouse_id` INT NOT NULL,
  `destination_warehouse_id` INT NOT NULL,
  `quantity` INT NOT NULL,
  `status` ENUM('pending', 'in_transit', 'received', 'cancelled') DEFAULT 'pending',
  `shipped_date` TIMESTAMP NULL,
  `received_date` TIMESTAMP NULL,
  `created_by` BIGINT,
  `received_by` BIGINT,
  `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_product` (`product_id`),
  KEY `idx_status` (`status`),
  KEY `idx_created` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 5. Price Management
CREATE TABLE `product_prices` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `product_id` BIGINT NOT NULL,
  `base_price` DECIMAL(12,0) NOT NULL,
  `selling_price` DECIMAL(12,0) NOT NULL,
  `wholesale_price` DECIMAL(12,0),
  `discount_percentage` DECIMAL(5,2) DEFAULT 0.00,
  `discount_start` TIMESTAMP NULL,
  `discount_end` TIMESTAMP NULL,
  `currency` CHAR(3) DEFAULT 'IRR',
  `effective_date` TIMESTAMP NOT NULL,
  `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_product` (`product_id`),
  KEY `idx_effective` (`effective_date`),
  KEY `idx_created` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 6. Price Audit Log
CREATE TABLE `price_audit_logs` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `product_id` BIGINT NOT NULL,
  `old_price` DECIMAL(12,0),
  `new_price` DECIMAL(12,0),
  `change_type` VARCHAR(50),
  `changed_by` BIGINT,
  `change_date` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  `change_reason` TEXT,
  PRIMARY KEY (`id`),
  KEY `idx_product` (`product_id`),
  KEY `idx_date` (`change_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 7. Product Batches and Expiration Tracking
CREATE TABLE `product_batches` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `product_id` BIGINT NOT NULL,
  `batch_number` VARCHAR(255) UNIQUE NOT NULL,
  `warehouse_id` INT NOT NULL,
  `quantity` INT NOT NULL DEFAULT 0,
  `manufacturing_date` DATE,
  `expiration_date` DATE NOT NULL,
  `days_to_expiry` INT GENERATED ALWAYS AS (DATEDIFF(`expiration_date`, CURDATE())) VIRTUAL,
  `status` ENUM('active', 'expired', 'recalled', 'disposed') DEFAULT 'active',
  `alert_sent` BOOLEAN DEFAULT FALSE,
  `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_batch_warehouse` (`batch_number`, `warehouse_id`),
  KEY `idx_product` (`product_id`),
  KEY `idx_expiration` (`expiration_date`),
  KEY `idx_status` (`status`),
  KEY `idx_to_expiry` (`days_to_expiry`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 8. Expiration Alerts
CREATE TABLE `expiration_alerts` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `batch_id` BIGINT NOT NULL,
  `alert_level` ENUM('warning', 'critical', 'expired') NOT NULL,
  `days_remaining` INT,
  `quantity` INT,
  `alert_date` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  `sent_to` VARCHAR(255),
  `acknowledged` BOOLEAN DEFAULT FALSE,
  `action_taken` TEXT,
  PRIMARY KEY (`id`),
  KEY `idx_batch` (`batch_id`),
  KEY `idx_level` (`alert_level`),
  KEY `idx_date` (`alert_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 9. Batch Movement History
CREATE TABLE `batch_movements` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `batch_id` BIGINT NOT NULL,
  `source_warehouse_id` INT,
  `destination_warehouse_id` INT,
  `quantity` INT NOT NULL,
  `movement_reason` VARCHAR(255),
  `created_by` BIGINT,
  `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_batch` (`batch_id`),
  KEY `idx_date` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 10. Stock Counts (Physical Inventory)
CREATE TABLE `stock_counts` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `warehouse_id` INT NOT NULL,
  `count_date` DATE NOT NULL,
  `start_time` TIMESTAMP NOT NULL,
  `end_time` TIMESTAMP,
  `status` ENUM('in_progress', 'completed', 'reviewed') DEFAULT 'in_progress',
  `counted_by` BIGINT,
  `verified_by` BIGINT,
  `notes` TEXT,
  `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_warehouse` (`warehouse_id`),
  KEY `idx_date` (`count_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 11. Stock Count Line Items
CREATE TABLE `stock_count_items` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `count_id` BIGINT NOT NULL,
  `product_id` BIGINT NOT NULL,
  `batch_id` BIGINT,
  `system_quantity` INT NOT NULL,
  `physical_quantity` INT NOT NULL,
  `variance` INT GENERATED ALWAYS AS (`physical_quantity` - `system_quantity`) VIRTUAL,
  `notes` TEXT,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`count_id`) REFERENCES `stock_counts` (`id`) ON DELETE CASCADE,
  KEY `idx_product` (`product_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 12. Sync Log (to track synchronization with main store)
CREATE TABLE `sync_logs` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `sync_type` ENUM('inventory', 'price', 'product') NOT NULL,
  `direction` ENUM('send', 'receive') NOT NULL,
  `records_count` INT,
  `status` ENUM('success', 'failed', 'pending') DEFAULT 'pending',
  `error_message` TEXT,
  `last_sync` TIMESTAMP NULL,
  `synced_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_type` (`sync_type`),
  KEY `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 13. Users and Access Control
CREATE TABLE `users` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(255) NOT NULL,
  `email` VARCHAR(255) UNIQUE,
  `phone` VARCHAR(20),
  `role` ENUM('admin', 'manager', 'warehouse_staff', 'accountant', 'viewer') DEFAULT 'viewer',
  `password` VARCHAR(255) NOT NULL,
  `warehouse_id` INT,
  `status` ENUM('active', 'inactive', 'suspended') DEFAULT 'active',
  `last_login` TIMESTAMP NULL,
  `two_factor_enabled` BOOLEAN DEFAULT FALSE,
  `two_factor_secret` VARCHAR(255),
  `email_verified_at` TIMESTAMP NULL,
  `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_role` (`role`),
  KEY `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 14. Activity Logs
CREATE TABLE `activity_logs` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` BIGINT NOT NULL,
  `action` VARCHAR(255) NOT NULL,
  `model_type` VARCHAR(255),
  `model_id` BIGINT,
  `changes` JSON,
  `ip_address` VARCHAR(45),
  `user_agent` TEXT,
  `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_user` (`user_id`),
  KEY `idx_date` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 15. System Settings
CREATE TABLE `settings` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `setting_key` VARCHAR(255) UNIQUE NOT NULL,
  `setting_value` LONGTEXT,
  `setting_type` ENUM('string', 'integer', 'boolean', 'json') DEFAULT 'string',
  `description` TEXT,
  `updated_by` BIGINT,
  `updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_key` (`setting_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Insert Default Warehouses
INSERT INTO `warehouses` (`name`, `location`, `capacity`, `manager_name`, `phone`, `status`) VALUES
('انبار اصلی - دزفول', 'خیابان توحید، دزفول', 1000, 'مهرداد رجایی', '09166809066', 'active'),
('انبار فرعی - شاهدشهر', 'شاهدشهر', 500, 'نام مدیر', '09xxxxxxxxx', 'active'),
('مرکز توزیع - تهران', 'تهران', 800, 'نام مدیر', '09xxxxxxxxx', 'active');

-- Insert Default Settings
INSERT INTO `settings` (`setting_key`, `setting_value`, `setting_type`, `description`) VALUES
('sync_enabled', 'true', 'boolean', 'Enable automatic synchronization with main store'),
('sync_interval', '300', 'integer', 'Synchronization interval in seconds'),
('expiration_warning_days', '30', 'integer', 'Days before expiration to show warning'),
('expiration_critical_days', '7', 'integer', 'Days before expiration to show critical alert'),
('inventory_low_stock_threshold', '10', 'integer', 'Low stock threshold'),
('currency', 'IRR', 'string', 'Currency code'),
('timezone', 'Asia/Tehran', 'string', 'Timezone'),
('app_theme', 'light', 'string', 'Application theme');

-- Create Indexes for Performance
CREATE INDEX idx_inventory_product_quantity ON inventories(product_id, quantity);
CREATE INDEX idx_batches_expiration_status ON product_batches(expiration_date, status);
CREATE INDEX idx_movements_date_type ON inventory_movements(created_at, movement_type);
