ساختار پایگاه داده (اسامی جداول با فیلدهای مورد نیاز) یک سیستم جامع مدیریت انبار و انبارداری

CREATE TABLE Warehouses (
    WarehouseID INT PRIMARY KEY AUTO_INCREMENT,
    WarehouseCode VARCHAR(20) UNIQUE NOT NULL,
    WarehouseName NVARCHAR(100) NOT NULL,
    WarehouseType ENUM('Main', 'Regional', 'Local', 'Temporary', 'ColdStorage') DEFAULT 'Main',
    Address TEXT,
    City NVARCHAR(50),
    Province NVARCHAR(50),
    PostalCode VARCHAR(10),
    Phone VARCHAR(15),
    Email VARCHAR(100),
    ManagerID INT, -- مدیر انبار
    Capacity DECIMAL(12,2), -- ظرفیت کل (متر مربع)
    CurrentCapacity DECIMAL(12,2) DEFAULT 0, -- ظرفیت اشغال شده
    IsActive BOOLEAN DEFAULT TRUE,
    TemperatureZone ENUM('Normal', 'Cool', 'Freeze') DEFAULT 'Normal',
    SecurityLevel ENUM('Low', 'Medium', 'High') DEFAULT 'Medium',
    CreatedAt DATETIME DEFAULT CURRENT_TIMESTAMP,
    UpdatedAt DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    INDEX idx_type (WarehouseType),
    INDEX idx_active (IsActive)
);

CREATE TABLE WarehouseZones (
    ZoneID INT PRIMARY KEY AUTO_INCREMENT,
    WarehouseID INT NOT NULL,
    ZoneCode VARCHAR(20) NOT NULL,
    ZoneName NVARCHAR(100) NOT NULL,
    ZoneType ENUM('Storage', 'Picking', 'Receiving', 'Shipping', 'Quarantine') DEFAULT 'Storage',
    Aisle VARCHAR(10),
    Rack VARCHAR(10),
    Shelf VARCHAR(10),
    Bin VARCHAR(10),
    Capacity DECIMAL(10,2),
    CurrentCapacity DECIMAL(10,2) DEFAULT 0,
    TemperatureRequirement DECIMAL(5,2),
    HumidityRequirement DECIMAL(5,2),
    IsActive BOOLEAN DEFAULT TRUE,
    SortOrder INT DEFAULT 0,
    
    FOREIGN KEY (WarehouseID) REFERENCES Warehouses(WarehouseID),
    UNIQUE KEY unique_zone_code (WarehouseID, ZoneCode),
    INDEX idx_warehouse (WarehouseID),
    INDEX idx_type (ZoneType)
);

CREATE TABLE ProductCategories (
    CategoryID INT PRIMARY KEY AUTO_INCREMENT,
    CategoryCode VARCHAR(20) UNIQUE NOT NULL,
    CategoryName NVARCHAR(100) NOT NULL,
    ParentCategoryID INT NULL,
    Description TEXT,
    IsActive BOOLEAN DEFAULT TRUE,
    CreatedAt DATETIME DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (ParentCategoryID) REFERENCES ProductCategories(CategoryID),
    INDEX idx_parent (ParentCategoryID)
);

CREATE TABLE Units (
    UnitID INT PRIMARY KEY AUTO_INCREMENT,
    UnitCode VARCHAR(20) UNIQUE NOT NULL,
    UnitName NVARCHAR(50) NOT NULL,
    UnitType ENUM('Weight', 'Volume', 'Length', 'Count', 'Area') DEFAULT 'Count',
    BaseUnitID INT NULL, -- برای تبدیل واحد
    ConversionFactor DECIMAL(10,4) DEFAULT 1,
    IsActive BOOLEAN DEFAULT TRUE,
    
    FOREIGN KEY (BaseUnitID) REFERENCES Units(UnitID)
);

CREATE TABLE Products (
    ProductID INT PRIMARY KEY AUTO_INCREMENT,
    ProductCode VARCHAR(50) UNIQUE NOT NULL,
    Barcode VARCHAR(100),
    ProductName NVARCHAR(200) NOT NULL,
    ProductDescription TEXT,
    CategoryID INT NOT NULL,
    UnitID INT NOT NULL,
    Brand NVARCHAR(100),
    Model NVARCHAR(100),
    Specifications JSON, -- مشخصات فنی
    Weight DECIMAL(10,3),
    Dimensions VARCHAR(100), -- ابعاد
    Volume DECIMAL(10,3),
    MinStockLevel DECIMAL(12,3) DEFAULT 0,
    MaxStockLevel DECIMAL(12,3) DEFAULT 0,
    ReorderPoint DECIMAL(12,3) DEFAULT 0,
    SafetyStock DECIMAL(12,3) DEFAULT 0,
    IsBatchTracked BOOLEAN DEFAULT FALSE, -- ردیابی سری ساخت
    IsSerialTracked BOOLEAN DEFAULT FALSE, -- ردیابی سریال
    IsExpiryTracked BOOLEAN DEFAULT FALSE, -- ردیابی تاریخ انقضا
    ShelfLife INT, -- عمر قفسه به روز
    StorageConditions TEXT, -- شرایط نگهداری
    IsActive BOOLEAN DEFAULT TRUE,
    CreatedAt DATETIME DEFAULT CURRENT_TIMESTAMP,
    UpdatedAt DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (CategoryID) REFERENCES ProductCategories(CategoryID),
    FOREIGN KEY (UnitID) REFERENCES Units(UnitID),
    INDEX idx_category (CategoryID),
    INDEX idx_barcode (Barcode),
    INDEX idx_active (IsActive)
);

CREATE TABLE Inventory (
    InventoryID BIGINT PRIMARY KEY AUTO_INCREMENT,
    ProductID INT NOT NULL,
    WarehouseID INT NOT NULL,
    ZoneID INT NOT NULL,
    BatchNumber VARCHAR(100), -- شماره سری ساخت
    SerialNumber VARCHAR(100), -- شماره سریال
    ExpiryDate DATE, -- تاریخ انقضا
    ProductionDate DATE, -- تاریخ تولید
    Quantity DECIMAL(12,3) DEFAULT 0,
    ReservedQuantity DECIMAL(12,3) DEFAULT 0, -- مقدار رزرو شده
    AvailableQuantity DECIMAL(12,3) GENERATED ALWAYS AS (Quantity - ReservedQuantity) STORED,
    UnitCost DECIMAL(15,2) DEFAULT 0, -- قیمت تمام شده
    TotalValue DECIMAL(15,2) GENERATED ALWAYS AS (Quantity * UnitCost) STORED,
    LastCountDate DATE, -- تاریخ آخرین شمارش
    Status ENUM('Available', 'Quarantine', 'Blocked', 'Expired') DEFAULT 'Available',
    CreatedAt DATETIME DEFAULT CURRENT_TIMESTAMP,
    UpdatedAt DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (ProductID) REFERENCES Products(ProductID),
    FOREIGN KEY (WarehouseID) REFERENCES Warehouses(WarehouseID),
    FOREIGN KEY (ZoneID) REFERENCES WarehouseZones(ZoneID),
    UNIQUE KEY unique_product_location (ProductID, WarehouseID, ZoneID, BatchNumber, SerialNumber),
    INDEX idx_product (ProductID),
    INDEX idx_warehouse (WarehouseID),
    INDEX idx_expiry (ExpiryDate),
    INDEX idx_status (Status)
);

CREATE TABLE Suppliers (
    SupplierID INT PRIMARY KEY AUTO_INCREMENT,
    SupplierCode VARCHAR(20) UNIQUE NOT NULL,
    SupplierName NVARCHAR(200) NOT NULL,
    ContactPerson NVARCHAR(100),
    Phone VARCHAR(15),
    Email VARCHAR(100),
    Address TEXT,
    City NVARCHAR(50),
    Province NVARCHAR(50),
    TaxNumber VARCHAR(20), -- شماره مالیاتی
    NationalID VARCHAR(20), -- کد ملی/شناسه ملی
    BankName NVARCHAR(100),
    BankAccount VARCHAR(30),
    PaymentTerms ENUM('Cash', 'Net7', 'Net15', 'Net30', 'Net60') DEFAULT 'Net30',
    Rating ENUM('Excellent', 'Good', 'Average', 'Poor') DEFAULT 'Average',
    IsActive BOOLEAN DEFAULT TRUE,
    CreatedAt DATETIME DEFAULT CURRENT_TIMESTAMP,
    UpdatedAt DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE PurchaseRequisitions (
    RequisitionID INT PRIMARY KEY AUTO_INCREMENT,
    RequisitionNumber VARCHAR(50) UNIQUE NOT NULL,
    RequisitionDate DATE NOT NULL,
    RequestedBy INT NOT NULL, -- درخواست دهنده
    DepartmentID INT, -- دپارتمان درخواست کننده
    Purpose TEXT, -- هدف درخواست
    Status ENUM('Draft', 'Submitted', 'Approved', 'Rejected', 'Converted') DEFAULT 'Draft',
    Priority ENUM('Low', 'Medium', 'High', 'Urgent') DEFAULT 'Medium',
    TotalEstimatedCost DECIMAL(15,2) DEFAULT 0,
    ApprovedBy INT,
    ApprovedAt DATETIME,
    RejectedBy INT,
    RejectedAt DATETIME,
    RejectionReason TEXT,
    CreatedAt DATETIME DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (RequestedBy) REFERENCES Employees(EmployeeID),
    FOREIGN KEY (ApprovedBy) REFERENCES Employees(EmployeeID),
    FOREIGN KEY (RejectedBy) REFERENCES Employees(EmployeeID)
);

CREATE TABLE RequisitionItems (
    RequisitionItemID INT PRIMARY KEY AUTO_INCREMENT,
    RequisitionID INT NOT NULL,
    ProductID INT NOT NULL,
    RequiredQuantity DECIMAL(12,3) NOT NULL,
    EstimatedUnitPrice DECIMAL(12,2) DEFAULT 0,
    EstimatedTotalPrice DECIMAL(15,2) GENERATED ALWAYS AS (RequiredQuantity * EstimatedUnitPrice) STORED,
    RequiredDate DATE, -- تاریخ مورد نیاز
    Specifications TEXT, -- مشخصات مورد نیاز
    Status ENUM('Pending', 'Approved', 'Rejected', 'Purchased') DEFAULT 'Pending',
    CreatedAt DATETIME DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (RequisitionID) REFERENCES PurchaseRequisitions(RequisitionID) ON DELETE CASCADE,
    FOREIGN KEY (ProductID) REFERENCES Products(ProductID),
    INDEX idx_requisition (RequisitionID)
);

CREATE TABLE PurchaseOrders (
    OrderID INT PRIMARY KEY AUTO_INCREMENT,
    OrderNumber VARCHAR(50) UNIQUE NOT NULL,
    SupplierID INT NOT NULL,
    OrderDate DATE NOT NULL,
    ExpectedDeliveryDate DATE,
    DeliveryAddress TEXT,
    PaymentTerms ENUM('Cash', 'Net7', 'Net15', 'Net30', 'Net60') DEFAULT 'Net30',
    ShippingMethod ENUM('Pickup', 'SupplierDelivery', 'Courier') DEFAULT 'SupplierDelivery',
    Incoterms VARCHAR(50), -- شرایط تحویل
    Status ENUM('Draft', 'Sent', 'Confirmed', 'PartialReceived', 'Completed', 'Cancelled') DEFAULT 'Draft',
    SubTotal DECIMAL(15,2) DEFAULT 0,
    TaxAmount DECIMAL(15,2) DEFAULT 0,
    ShippingCost DECIMAL(15,2) DEFAULT 0,
    TotalAmount DECIMAL(15,2) GENERATED ALWAYS AS (SubTotal + TaxAmount + ShippingCost) STORED,
    CreatedBy INT NOT NULL,
    ApprovedBy INT,
    Notes TEXT,
    CreatedAt DATETIME DEFAULT CURRENT_TIMESTAMP,
    UpdatedAt DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (SupplierID) REFERENCES Suppliers(SupplierID),
    FOREIGN KEY (CreatedBy) REFERENCES Employees(EmployeeID),
    FOREIGN KEY (ApprovedBy) REFERENCES Employees(EmployeeID),
    INDEX idx_supplier (SupplierID),
    INDEX idx_status (Status)
);

CREATE TABLE PurchaseOrderItems (
    OrderItemID INT PRIMARY KEY AUTO_INCREMENT,
    OrderID INT NOT NULL,
    ProductID INT NOT NULL,
    OrderedQuantity DECIMAL(12,3) NOT NULL,
    ReceivedQuantity DECIMAL(12,3) DEFAULT 0,
    PendingQuantity DECIMAL(12,3) GENERATED ALWAYS AS (OrderedQuantity - ReceivedQuantity) STORED,
    UnitPrice DECIMAL(12,2) NOT NULL,
    TotalPrice DECIMAL(15,2) GENERATED ALWAYS AS (OrderedQuantity * UnitPrice) STORED,
    TaxRate DECIMAL(5,2) DEFAULT 0,
    ExpectedDate DATE,
    BatchNumber VARCHAR(100),
    Notes TEXT,
    
    FOREIGN KEY (OrderID) REFERENCES PurchaseOrders(OrderID) ON DELETE CASCADE,
    FOREIGN KEY (ProductID) REFERENCES Products(ProductID),
    INDEX idx_order (OrderID)
);

CREATE TABLE GoodsReceipts (
    ReceiptID INT PRIMARY KEY AUTO_INCREMENT,
    ReceiptNumber VARCHAR(50) UNIQUE NOT NULL,
    OrderID INT NOT NULL,
    ReceiptDate DATE NOT NULL,
    ReceivedBy INT NOT NULL,
    WarehouseID INT NOT NULL,
    SupplierID INT NOT NULL,
    ReceiptType ENUM('Purchase', 'Return', 'Transfer', 'Production') DEFAULT 'Purchase',
    Status ENUM('Draft', 'Received', 'Verified', 'Cancelled') DEFAULT 'Draft',
    TotalQuantity DECIMAL(12,3) DEFAULT 0,
    TotalValue DECIMAL(15,2) DEFAULT 0,
    Notes TEXT,
    CreatedAt DATETIME DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (OrderID) REFERENCES PurchaseOrders(OrderID),
    FOREIGN KEY (ReceivedBy) REFERENCES Employees(EmployeeID),
    FOREIGN KEY (WarehouseID) REFERENCES Warehouses(WarehouseID),
    FOREIGN KEY (SupplierID) REFERENCES Suppliers(SupplierID),
    INDEX idx_order (OrderID),
    INDEX idx_date (ReceiptDate)
);

CREATE TABLE GoodsReceiptItems (
    ReceiptItemID INT PRIMARY KEY AUTO_INCREMENT,
    ReceiptID INT NOT NULL,
    OrderItemID INT NOT NULL,
    ProductID INT NOT NULL,
    ReceivedQuantity DECIMAL(12,3) NOT NULL,
    UnitPrice DECIMAL(12,2) NOT NULL,
    TotalPrice DECIMAL(15,2) GENERATED ALWAYS AS (ReceivedQuantity * UnitPrice) STORED,
    BatchNumber VARCHAR(100),
    SerialNumber VARCHAR(100),
    ExpiryDate DATE,
    ProductionDate DATE,
    ZoneID INT NOT NULL,
    QualityStatus ENUM('Accepted', 'Rejected', 'Quarantine') DEFAULT 'Accepted',
    RejectedQuantity DECIMAL(12,3) DEFAULT 0,
    RejectionReason TEXT,
    
    FOREIGN KEY (ReceiptID) REFERENCES GoodsReceipts(ReceiptID) ON DELETE CASCADE,
    FOREIGN KEY (OrderItemID) REFERENCES PurchaseOrderItems(OrderItemID),
    FOREIGN KEY (ProductID) REFERENCES Products(ProductID),
    FOREIGN KEY (ZoneID) REFERENCES WarehouseZones(ZoneID),
    INDEX idx_receipt (ReceiptID)
);

CREATE TABLE IssueRequests (
    IssueID INT PRIMARY KEY AUTO_INCREMENT,
    IssueNumber VARCHAR(50) UNIQUE NOT NULL,
    IssueDate DATE NOT NULL,
    RequestedBy INT NOT NULL,
    DepartmentID INT,
    Purpose ENUM('Production', 'Sales', 'Maintenance', 'Sample', 'InternalUse') DEFAULT 'InternalUse',
    Status ENUM('Draft', 'Submitted', 'Approved', 'PartialIssued', 'Completed', 'Cancelled') DEFAULT 'Draft',
    Priority ENUM('Low', 'Medium', 'High', 'Urgent') DEFAULT 'Medium',
    ApprovedBy INT,
    ApprovedAt DATETIME,
    TotalQuantity DECIMAL(12,3) DEFAULT 0,
    TotalValue DECIMAL(15,2) DEFAULT 0,
    Notes TEXT,
    CreatedAt DATETIME DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (RequestedBy) REFERENCES Employees(EmployeeID),
    FOREIGN KEY (ApprovedBy) REFERENCES Employees(EmployeeID)
);

CREATE TABLE IssueRequestItems (
    IssueItemID INT PRIMARY KEY AUTO_INCREMENT,
    IssueID INT NOT NULL,
    ProductID INT NOT NULL,
    RequestedQuantity DECIMAL(12,3) NOT NULL,
    IssuedQuantity DECIMAL(12,3) DEFAULT 0,
    PendingQuantity DECIMAL(12,3) GENERATED ALWAYS AS (RequestedQuantity - IssuedQuantity) STORED,
    UnitCost DECIMAL(12,2) DEFAULT 0,
    TotalCost DECIMAL(15,2) GENERATED ALWAYS AS (IssuedQuantity * UnitCost) STORED,
    RequiredDate DATE,
    Notes TEXT,
    
    FOREIGN KEY (IssueID) REFERENCES IssueRequests(IssueID) ON DELETE CASCADE,
    FOREIGN KEY (ProductID) REFERENCES Products(ProductID),
    INDEX idx_issue (IssueID)
);

CREATE TABLE IssueVouchers (
    VoucherID INT PRIMARY KEY AUTO_INCREMENT,
    VoucherNumber VARCHAR(50) UNIQUE NOT NULL,
    IssueID INT NOT NULL,
    VoucherDate DATE NOT NULL,
    IssuedBy INT NOT NULL,
    WarehouseID INT NOT NULL,
    IssuedTo NVARCHAR(200), -- دریافت کننده
    DepartmentID INT,
    VoucherType ENUM('Production', 'Sales', 'Maintenance', 'Sample', 'InternalUse') DEFAULT 'InternalUse',
    Status ENUM('Draft', 'Issued', 'Cancelled') DEFAULT 'Draft',
    TotalQuantity DECIMAL(12,3) DEFAULT 0,
    TotalValue DECIMAL(15,2) DEFAULT 0,
    Notes TEXT,
    CreatedAt DATETIME DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (IssueID) REFERENCES IssueRequests(IssueID),
    FOREIGN KEY (IssuedBy) REFERENCES Employees(EmployeeID),
    FOREIGN KEY (WarehouseID) REFERENCES Warehouses(WarehouseID)
);

CREATE TABLE IssueVoucherItems (
    VoucherItemID INT PRIMARY KEY AUTO_INCREMENT,
    VoucherID INT NOT NULL,
    IssueItemID INT NOT NULL,
    ProductID INT NOT NULL,
    IssuedQuantity DECIMAL(12,3) NOT NULL,
    UnitCost DECIMAL(12,2) NOT NULL,
    TotalCost DECIMAL(15,2) GENERATED ALWAYS AS (IssuedQuantity * UnitCost) STORED,
    BatchNumber VARCHAR(100),
    SerialNumber VARCHAR(100),
    ZoneID INT NOT NULL,
    CostingMethod ENUM('FIFO', 'LIFO', 'Average') DEFAULT 'FIFO',
    
    FOREIGN KEY (VoucherID) REFERENCES IssueVouchers(VoucherID) ON DELETE CASCADE,
    FOREIGN KEY (IssueItemID) REFERENCES IssueRequestItems(IssueItemID),
    FOREIGN KEY (ProductID) REFERENCES Products(ProductID),
    FOREIGN KEY (ZoneID) REFERENCES WarehouseZones(ZoneID),
    INDEX idx_voucher (VoucherID)
);

CREATE TABLE StockTransfers (
    TransferID INT PRIMARY KEY AUTO_INCREMENT,
    TransferNumber VARCHAR(50) UNIQUE NOT NULL,
    TransferDate DATE NOT NULL,
    FromWarehouseID INT NOT NULL,
    ToWarehouseID INT NOT NULL,
    RequestedBy INT NOT NULL,
    Status ENUM('Draft', 'Approved', 'InTransit', 'PartialReceived', 'Completed', 'Cancelled') DEFAULT 'Draft',
    TotalQuantity DECIMAL(12,3) DEFAULT 0,
    TotalValue DECIMAL(15,2) DEFAULT 0,
    ShippingMethod VARCHAR(100),
    ExpectedDeliveryDate DATE,
    ActualDeliveryDate DATE,
    Notes TEXT,
    CreatedAt DATETIME DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (FromWarehouseID) REFERENCES Warehouses(WarehouseID),
    FOREIGN KEY (ToWarehouseID) REFERENCES Warehouses(WarehouseID),
    FOREIGN KEY (RequestedBy) REFERENCES Employees(EmployeeID)
);

CREATE TABLE StockTransferItems (
    TransferItemID INT PRIMARY KEY AUTO_INCREMENT,
    TransferID INT NOT NULL,
    ProductID INT NOT NULL,
    Quantity DECIMAL(12,3) NOT NULL,
    ReceivedQuantity DECIMAL(12,3) DEFAULT 0,
    PendingQuantity DECIMAL(12,3) GENERATED ALWAYS AS (Quantity - ReceivedQuantity) STORED,
    UnitCost DECIMAL(12,2) NOT NULL,
    TotalCost DECIMAL(15,2) GENERATED ALWAYS AS (Quantity * UnitCost) STORED,
    BatchNumber VARCHAR(100),
    SerialNumber VARCHAR(100),
    FromZoneID INT NOT NULL,
    ToZoneID INT NOT NULL,
    
    FOREIGN KEY (TransferID) REFERENCES StockTransfers(TransferID) ON DELETE CASCADE,
    FOREIGN KEY (ProductID) REFERENCES Products(ProductID),
    FOREIGN KEY (FromZoneID) REFERENCES WarehouseZones(ZoneID),
    FOREIGN KEY (ToZoneID) REFERENCES WarehouseZones(ZoneID),
    INDEX idx_transfer (TransferID)
);

CREATE TABLE StockAdjustments (
    AdjustmentID INT PRIMARY KEY AUTO_INCREMENT,
    AdjustmentNumber VARCHAR(50) UNIQUE NOT NULL,
    AdjustmentDate DATE NOT NULL,
    WarehouseID INT NOT NULL,
    AdjustmentType ENUM('Count', 'WriteOff', 'WriteOn', 'Correction', 'Expiry') DEFAULT 'Count',
    Reason ENUM('Stocktake', 'Damage', 'Theft', 'Expiry', 'MeasurementError') DEFAULT 'Stocktake',
    Status ENUM('Draft', 'Approved', 'Posted', 'Cancelled') DEFAULT 'Draft',
    TotalQuantityDiff DECIMAL(12,3) DEFAULT 0,
    TotalValueDiff DECIMAL(15,2) DEFAULT 0,
    ApprovedBy INT,
    ApprovedAt DATETIME,
    Notes TEXT,
    CreatedAt DATETIME DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (WarehouseID) REFERENCES Warehouses(WarehouseID),
    FOREIGN KEY (ApprovedBy) REFERENCES Employees(EmployeeID)
);

CREATE TABLE StockAdjustmentItems (
    AdjustmentItemID INT PRIMARY KEY AUTO_INCREMENT,
    AdjustmentID INT NOT NULL,
    ProductID INT NOT NULL,
    ZoneID INT NOT NULL,
    BatchNumber VARCHAR(100),
    SerialNumber VARCHAR(100),
    CurrentQuantity DECIMAL(12,3) NOT NULL,
    CountedQuantity DECIMAL(12,3) NOT NULL,
    QuantityDifference DECIMAL(12,3) GENERATED ALWAYS AS (CountedQuantity - CurrentQuantity) STORED,
    UnitCost DECIMAL(12,2) NOT NULL,
    ValueDifference DECIMAL(15,2) GENERATED ALWAYS AS (QuantityDifference * UnitCost) STORED,
    Reason TEXT,
    
    FOREIGN KEY (AdjustmentID) REFERENCES StockAdjustments(AdjustmentID) ON DELETE CASCADE,
    FOREIGN KEY (ProductID) REFERENCES Products(ProductID),
    FOREIGN KEY (ZoneID) REFERENCES WarehouseZones(ZoneID),
    INDEX idx_adjustment (AdjustmentID)
);

CREATE TABLE Stocktakes (
    StocktakeID INT PRIMARY KEY AUTO_INCREMENT,
    StocktakeNumber VARCHAR(50) UNIQUE NOT NULL,
    StocktakeDate DATE NOT NULL,
    WarehouseID INT NOT NULL,
    StocktakeType ENUM('Periodic', 'Cycle', 'Full', 'Spot') DEFAULT 'Periodic',
    Status ENUM('Planned', 'InProgress', 'Completed', 'Cancelled') DEFAULT 'Planned',
    StartedBy INT,
    StartedAt DATETIME,
    CompletedBy INT,
    CompletedAt DATETIME,
    TotalItems INT DEFAULT 0,
    CountedItems INT DEFAULT 0,
    VarianceItems INT DEFAULT 0,
    Notes TEXT,
    CreatedAt DATETIME DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (WarehouseID) REFERENCES Warehouses(WarehouseID),
    FOREIGN KEY (StartedBy) REFERENCES Employees(EmployeeID),
    FOREIGN KEY (CompletedBy) REFERENCES Employees(EmployeeID)
);

CREATE TABLE InventoryTransactions (
    TransactionID BIGINT PRIMARY KEY AUTO_INCREMENT,
    TransactionDate DATETIME DEFAULT CURRENT_TIMESTAMP,
    ProductID INT NOT NULL,
    WarehouseID INT NOT NULL,
    ZoneID INT NOT NULL,
    TransactionType ENUM(
        'PurchaseReceipt', 'PurchaseReturn', 'Issue', 'Return', 
        'TransferIn', 'TransferOut', 'Adjustment', 'Stocktake'
    ) NOT NULL,
    ReferenceID INT, -- ID سند مرتبط
    ReferenceNumber VARCHAR(50), -- شماره سند
    BatchNumber VARCHAR(100),
    SerialNumber VARCHAR(100),
    QuantityIn DECIMAL(12,3) DEFAULT 0,
    QuantityOut DECIMAL(12,3) DEFAULT 0,
    UnitCost DECIMAL(12,2) DEFAULT 0,
    TotalCost DECIMAL(15,2) DEFAULT 0,
    NewQuantity DECIMAL(12,3) DEFAULT 0,
    NewUnitCost DECIMAL(12,2) DEFAULT 0,
    Remarks TEXT,
    CreatedBy INT,
    
    FOREIGN KEY (ProductID) REFERENCES Products(ProductID),
    FOREIGN KEY (WarehouseID) REFERENCES Warehouses(WarehouseID),
    FOREIGN KEY (ZoneID) REFERENCES WarehouseZones(ZoneID),
    FOREIGN KEY (CreatedBy) REFERENCES Employees(EmployeeID),
    INDEX idx_product_date (ProductID, TransactionDate),
    INDEX idx_warehouse (WarehouseID),
    INDEX idx_type (TransactionType)
);

-- درج واحدهای اندازه‌گیری
INSERT INTO Units (UnitCode, UnitName, UnitType) VALUES
('PCS', 'عدد', 'Count'),
('KG', 'کیلوگرم', 'Weight'),
('M', 'متر', 'Length'),
('M2', 'متر مربع', 'Area'),
('L', 'لیتر', 'Volume');

-- درج دسته‌بندی‌های محصول
INSERT INTO ProductCategories (CategoryCode, CategoryName) VALUES
('RAW', 'مواد اولیه'),
('SEMI', 'نیمه ساخته'),
('FIN', 'محصول نهایی'),
('SPARE', 'قطعات یدکی'),
('CONS', 'کالای مصرفی');

-- درج انبارهای پیش‌فرض
INSERT INTO Warehouses (WarehouseCode, WarehouseName, WarehouseType, Capacity) VALUES
('WH-MAIN', 'انبار اصلی', 'Main', 1000),
('WH-RAW', 'انبار مواد اولیه', 'Main', 500),
('WH-FIN', 'انبار محصول نهایی', 'Main', 800);