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

CREATE TABLE Companies (
    CompanyID INT PRIMARY KEY AUTO_INCREMENT,
    CompanyCode VARCHAR(20) UNIQUE NOT NULL,
    CompanyName NVARCHAR(200) NOT NULL,
    LegalName NVARCHAR(200),
    TaxNumber VARCHAR(20),
    NationalID VARCHAR(20),
    EconomicCode VARCHAR(20),
    RegisterNumber VARCHAR(50),
    Address TEXT,
    Phone VARCHAR(15),
    Fax VARCHAR(15),
    Email VARCHAR(100),
    Website VARCHAR(100),
    FiscalYearStart DATE,
    FiscalYearEnd DATE,
    BaseCurrencyID INT NOT NULL,
    TaxRate DECIMAL(5,2) DEFAULT 0,
    Logo VARCHAR(255),
    IsActive BOOLEAN DEFAULT TRUE,
    CreatedAt DATETIME DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (BaseCurrencyID) REFERENCES Currencies(CurrencyID)
);

CREATE TABLE FiscalPeriods (
    PeriodID INT PRIMARY KEY AUTO_INCREMENT,
    CompanyID INT NOT NULL,
    PeriodName NVARCHAR(100) NOT NULL,
    PeriodYear INT NOT NULL,
    PeriodNumber INT NOT NULL, -- شماره دوره (1 تا 12)
    StartDate DATE NOT NULL,
    EndDate DATE NOT NULL,
    Status ENUM('Open', 'Closed', 'Locked') DEFAULT 'Open',
    IsCurrent BOOLEAN DEFAULT FALSE,
    ClosingEntryNumber VARCHAR(50),
    ClosedBy INT,
    ClosedAt DATETIME,
    
    FOREIGN KEY (CompanyID) REFERENCES Companies(CompanyID),
    FOREIGN KEY (ClosedBy) REFERENCES Users(UserID),
    UNIQUE KEY unique_period (CompanyID, PeriodYear, PeriodNumber)
);

CREATE TABLE Currencies (
    CurrencyID INT PRIMARY KEY AUTO_INCREMENT,
    CurrencyCode VARCHAR(3) UNIQUE NOT NULL, -- USD, EUR, IRR
    CurrencyName NVARCHAR(50) NOT NULL,
    Symbol NVARCHAR(10),
    IsBaseCurrency BOOLEAN DEFAULT FALSE,
    DecimalPlaces INT DEFAULT 2,
    IsActive BOOLEAN DEFAULT TRUE
);

CREATE TABLE ExchangeRates (
    RateID INT PRIMARY KEY AUTO_INCREMENT,
    FromCurrencyID INT NOT NULL,
    ToCurrencyID INT NOT NULL,
    RateDate DATE NOT NULL,
    RateValue DECIMAL(15,6) NOT NULL,
    RateType ENUM('Official', 'Market', 'Custom') DEFAULT 'Official',
    IsActive BOOLEAN DEFAULT TRUE,
    CreatedAt DATETIME DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (FromCurrencyID) REFERENCES Currencies(CurrencyID),
    FOREIGN KEY (ToCurrencyID) REFERENCES Currencies(CurrencyID),
    UNIQUE KEY unique_rate (FromCurrencyID, ToCurrencyID, RateDate)
);

CREATE TABLE AccountGroups (
    GroupID INT PRIMARY KEY AUTO_INCREMENT,
    GroupCode VARCHAR(10) NOT NULL,
    GroupName NVARCHAR(100) NOT NULL,
    ParentGroupID INT NULL,
    GroupLevel INT DEFAULT 1,
    AccountType ENUM('Asset', 'Liability', 'Equity', 'Income', 'Expense') NOT NULL,
    NormalBalance ENUM('Debit', 'Credit') NOT NULL,
    IsActive BOOLEAN DEFAULT TRUE,
    SortOrder INT DEFAULT 0,
    
    FOREIGN KEY (ParentGroupID) REFERENCES AccountGroups(GroupID),
    UNIQUE KEY unique_group_code (GroupCode)
);

CREATE TABLE ChartOfAccounts (
    AccountID INT PRIMARY KEY AUTO_INCREMENT,
    CompanyID INT NOT NULL,
    AccountCode VARCHAR(20) NOT NULL,
    AccountName NVARCHAR(200) NOT NULL,
    AccountNameEnglish VARCHAR(200),
    GroupID INT NOT NULL,
    ParentAccountID INT NULL,
    AccountLevel INT DEFAULT 1,
    AccountType ENUM('Asset', 'Liability', 'Equity', 'Income', 'Expense') NOT NULL,
    NormalBalance ENUM('Debit', 'Credit') NOT NULL,
    IsDetailAccount BOOLEAN DEFAULT FALSE, -- حساب معین
    IsCashAccount BOOLEAN DEFAULT FALSE,
    IsBankAccount BOOLEAN DEFAULT FALSE,
    IsReceivableAccount BOOLEAN DEFAULT FALSE,
    IsPayableAccount BOOLEAN DEFAULT FALSE,
    IsInventoryAccount BOOLEAN DEFAULT FALSE,
    IsCostAccount BOOLEAN DEFAULT FALSE,
    IsDepreciationAccount BOOLEAN DEFAULT FALSE,
    CurrencyID INT,
    OpeningBalanceDebit DECIMAL(15,2) DEFAULT 0,
    OpeningBalanceCredit DECIMAL(15,2) DEFAULT 0,
    CurrentBalanceDebit DECIMAL(15,2) DEFAULT 0,
    CurrentBalanceCredit DECIMAL(15,2) DEFAULT 0,
    IsActive BOOLEAN DEFAULT TRUE,
    Notes TEXT,
    CreatedAt DATETIME DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (CompanyID) REFERENCES Companies(CompanyID),
    FOREIGN KEY (GroupID) REFERENCES AccountGroups(GroupID),
    FOREIGN KEY (ParentAccountID) REFERENCES ChartOfAccounts(AccountID),
    FOREIGN KEY (CurrencyID) REFERENCES Currencies(CurrencyID),
    UNIQUE KEY unique_account (CompanyID, AccountCode)
);

CREATE TABLE CostCenters (
    CostCenterID INT PRIMARY KEY AUTO_INCREMENT,
    CompanyID INT NOT NULL,
    CostCenterCode VARCHAR(20) NOT NULL,
    CostCenterName NVARCHAR(100) NOT NULL,
    ParentCostCenterID INT NULL,
    CostCenterLevel INT DEFAULT 1,
    IsActive BOOLEAN DEFAULT TRUE,
    ManagerID INT,
    Budget DECIMAL(15,2) DEFAULT 0,
    Notes TEXT,
    
    FOREIGN KEY (CompanyID) REFERENCES Companies(CompanyID),
    FOREIGN KEY (ParentCostCenterID) REFERENCES CostCenters(CostCenterID),
    FOREIGN KEY (ManagerID) REFERENCES Employees(EmployeeID),
    UNIQUE KEY unique_cost_center (CompanyID, CostCenterCode)
);

CREATE TABLE Projects (
    ProjectID INT PRIMARY KEY AUTO_INCREMENT,
    CompanyID INT NOT NULL,
    ProjectCode VARCHAR(20) NOT NULL,
    ProjectName NVARCHAR(200) NOT NULL,
    ProjectDescription TEXT,
    StartDate DATE,
    EndDate DATE,
    Budget DECIMAL(15,2) DEFAULT 0,
    ActualCost DECIMAL(15,2) DEFAULT 0,
    Status ENUM('Planning', 'Active', 'OnHold', 'Completed', 'Cancelled') DEFAULT 'Planning',
    ProjectManagerID INT,
    CustomerID INT,
    IsActive BOOLEAN DEFAULT TRUE,
    CreatedAt DATETIME DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (CompanyID) REFERENCES Companies(CompanyID),
    FOREIGN KEY (ProjectManagerID) REFERENCES Employees(EmployeeID),
    FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID),
    UNIQUE KEY unique_project (CompanyID, ProjectCode)
);

CREATE TABLE AccountingVouchers (
    VoucherID INT PRIMARY KEY AUTO_INCREMENT,
    CompanyID INT NOT NULL,
    VoucherNumber VARCHAR(50) NOT NULL,
    VoucherDate DATE NOT NULL,
    VoucherType ENUM(
        'General', 'Receipt', 'Payment', 'Sales', 'Purchase', 
        'Journal', 'Opening', 'Closing', 'Adjusting'
    ) DEFAULT 'General',
    PeriodID INT NOT NULL,
    ReferenceNumber VARCHAR(100), -- شماره مرجع
    ReferenceDate DATE,
    Description TEXT,
    TotalDebit DECIMAL(15,2) DEFAULT 0,
    TotalCredit DECIMAL(15,2) DEFAULT 0,
    CurrencyID INT NOT NULL,
    ExchangeRate DECIMAL(15,6) DEFAULT 1,
    Status ENUM('Draft', 'Posted', 'Cancelled') DEFAULT 'Draft',
    PreparedBy INT NOT NULL,
    ApprovedBy INT,
    PostedBy INT,
    PostedAt DATETIME,
    CreatedAt DATETIME DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (CompanyID) REFERENCES Companies(CompanyID),
    FOREIGN KEY (PeriodID) REFERENCES FiscalPeriods(PeriodID),
    FOREIGN KEY (CurrencyID) REFERENCES Currencies(CurrencyID),
    FOREIGN KEY (PreparedBy) REFERENCES Users(UserID),
    FOREIGN KEY (ApprovedBy) REFERENCES Users(UserID),
    FOREIGN KEY (PostedBy) REFERENCES Users(UserID),
    UNIQUE KEY unique_voucher (CompanyID, VoucherNumber)
);

CREATE TABLE VoucherItems (
    VoucherItemID BIGINT PRIMARY KEY AUTO_INCREMENT,
    VoucherID INT NOT NULL,
    AccountID INT NOT NULL,
    Description TEXT,
    DebitAmount DECIMAL(15,2) DEFAULT 0,
    CreditAmount DECIMAL(15,2) DEFAULT 0,
    CurrencyID INT NOT NULL,
    ExchangeRate DECIMAL(15,6) DEFAULT 1,
    BaseDebitAmount DECIMAL(15,2) GENERATED ALWAYS AS (DebitAmount * ExchangeRate) STORED,
    BaseCreditAmount DECIMAL(15,2) GENERATED ALWAYS AS (CreditAmount * ExchangeRate) STORED,
    CostCenterID INT,
    ProjectID INT,
    CustomerID INT,
    SupplierID INT,
    EmployeeID INT,
    AssetID INT,
    ReferenceType ENUM('Invoice', 'Payment', 'Receipt', 'Journal', 'None') DEFAULT 'None',
    ReferenceID INT,
    LineNumber INT DEFAULT 1,
    
    FOREIGN KEY (VoucherID) REFERENCES AccountingVouchers(VoucherID) ON DELETE CASCADE,
    FOREIGN KEY (AccountID) REFERENCES ChartOfAccounts(AccountID),
    FOREIGN KEY (CurrencyID) REFERENCES Currencies(CurrencyID),
    FOREIGN KEY (CostCenterID) REFERENCES CostCenters(CostCenterID),
    FOREIGN KEY (ProjectID) REFERENCES Projects(ProjectID),
    FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID),
    FOREIGN KEY (SupplierID) REFERENCES Suppliers(SupplierID),
    FOREIGN KEY (EmployeeID) REFERENCES Employees(EmployeeID),
    FOREIGN KEY (AssetID) REFERENCES FixedAssets(AssetID),
    INDEX idx_account (AccountID),
    INDEX idx_voucher (VoucherID)
);

CREATE TABLE Customers (
    CustomerID INT PRIMARY KEY AUTO_INCREMENT,
    CompanyID INT NOT NULL,
    CustomerCode VARCHAR(20) NOT NULL,
    CustomerName NVARCHAR(200) NOT NULL,
    CustomerType ENUM('Individual', 'Corporate', 'Government') DEFAULT 'Corporate',
    NationalID VARCHAR(20),
    EconomicCode VARCHAR(20),
    TaxNumber VARCHAR(20),
    RegisterNumber VARCHAR(50),
    ContactPerson NVARCHAR(100),
    Phone VARCHAR(15),
    Mobile VARCHAR(15),
    Email VARCHAR(100),
    Address TEXT,
    City NVARCHAR(50),
    Province NVARCHAR(50),
    PostalCode VARCHAR(10),
    CreditLimit DECIMAL(15,2) DEFAULT 0,
    CurrentBalance DECIMAL(15,2) DEFAULT 0,
    ReceivableAccountID INT,
    CurrencyID INT,
    PaymentTerms ENUM('Cash', 'Net7', 'Net15', 'Net30', 'Net60') DEFAULT 'Net30',
    IsActive BOOLEAN DEFAULT TRUE,
    CreatedAt DATETIME DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (CompanyID) REFERENCES Companies(CompanyID),
    FOREIGN KEY (ReceivableAccountID) REFERENCES ChartOfAccounts(AccountID),
    FOREIGN KEY (CurrencyID) REFERENCES Currencies(CurrencyID),
    UNIQUE KEY unique_customer (CompanyID, CustomerCode)
);

CREATE TABLE Suppliers (
    SupplierID INT PRIMARY KEY AUTO_INCREMENT,
    CompanyID INT NOT NULL,
    SupplierCode VARCHAR(20) NOT NULL,
    SupplierName NVARCHAR(200) NOT NULL,
    SupplierType ENUM('Individual', 'Corporate', 'Government') DEFAULT 'Corporate',
    NationalID VARCHAR(20),
    EconomicCode VARCHAR(20),
    TaxNumber VARCHAR(20),
    RegisterNumber VARCHAR(50),
    ContactPerson NVARCHAR(100),
    Phone VARCHAR(15),
    Mobile VARCHAR(15),
    Email VARCHAR(100),
    Address TEXT,
    City NVARCHAR(50),
    Province NVARCHAR(50),
    PostalCode VARCHAR(10),
    CreditLimit DECIMAL(15,2) DEFAULT 0,
    CurrentBalance DECIMAL(15,2) DEFAULT 0,
    PayableAccountID INT,
    CurrencyID INT,
    PaymentTerms ENUM('Cash', 'Net7', 'Net15', 'Net30', 'Net60') DEFAULT 'Net30',
    BankName NVARCHAR(100),
    BankAccount VARCHAR(30),
    BankSheba VARCHAR(26),
    IsActive BOOLEAN DEFAULT TRUE,
    CreatedAt DATETIME DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (CompanyID) REFERENCES Companies(CompanyID),
    FOREIGN KEY (PayableAccountID) REFERENCES ChartOfAccounts(AccountID),
    FOREIGN KEY (CurrencyID) REFERENCES Currencies(CurrencyID),
    UNIQUE KEY unique_supplier (CompanyID, SupplierCode)
);

CREATE TABLE SalesInvoices (
    InvoiceID INT PRIMARY KEY AUTO_INCREMENT,
    CompanyID INT NOT NULL,
    InvoiceNumber VARCHAR(50) NOT NULL,
    InvoiceDate DATE NOT NULL,
    CustomerID INT NOT NULL,
    CurrencyID INT NOT NULL,
    ExchangeRate DECIMAL(15,6) DEFAULT 1,
    SubTotal DECIMAL(15,2) DEFAULT 0,
    TaxAmount DECIMAL(15,2) DEFAULT 0,
    DiscountAmount DECIMAL(15,2) DEFAULT 0,
    TotalAmount DECIMAL(15,2) DEFAULT 0,
    DueDate DATE,
    PaymentTerms ENUM('Cash', 'Net7', 'Net15', 'Net30', 'Net60') DEFAULT 'Net30',
    Status ENUM('Draft', 'Issued', 'Paid', 'PartialPaid', 'Cancelled', 'Overdue') DEFAULT 'Draft',
    ReceivableAccountID INT,
    SalesAccountID INT,
    TaxAccountID INT,
    ProjectID INT,
    Notes TEXT,
    CreatedBy INT NOT NULL,
    CreatedAt DATETIME DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (CompanyID) REFERENCES Companies(CompanyID),
    FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID),
    FOREIGN KEY (CurrencyID) REFERENCES Currencies(CurrencyID),
    FOREIGN KEY (ReceivableAccountID) REFERENCES ChartOfAccounts(AccountID),
    FOREIGN KEY (SalesAccountID) REFERENCES ChartOfAccounts(AccountID),
    FOREIGN KEY (TaxAccountID) REFERENCES ChartOfAccounts(AccountID),
    FOREIGN KEY (ProjectID) REFERENCES Projects(ProjectID),
    FOREIGN KEY (CreatedBy) REFERENCES Users(UserID),
    UNIQUE KEY unique_invoice (CompanyID, InvoiceNumber)
);

CREATE TABLE SalesInvoiceItems (
    InvoiceItemID INT PRIMARY KEY AUTO_INCREMENT,
    InvoiceID INT NOT NULL,
    ProductID INT,
    Description NVARCHAR(500),
    Quantity DECIMAL(12,3) DEFAULT 0,
    UnitPrice DECIMAL(15,2) DEFAULT 0,
    DiscountRate DECIMAL(5,2) DEFAULT 0,
    DiscountAmount DECIMAL(15,2) DEFAULT 0,
    TaxRate DECIMAL(5,2) DEFAULT 0,
    TaxAmount DECIMAL(15,2) DEFAULT 0,
    LineTotal DECIMAL(15,2) DEFAULT 0,
    InventoryAccountID INT,
    COGSAccountID INT, -- حساب بهای تمام شده
    SalesAccountID INT,
    
    FOREIGN KEY (InvoiceID) REFERENCES SalesInvoices(InvoiceID) ON DELETE CASCADE,
    FOREIGN KEY (ProductID) REFERENCES Products(ProductID),
    FOREIGN KEY (InventoryAccountID) REFERENCES ChartOfAccounts(AccountID),
    FOREIGN KEY (COGSAccountID) REFERENCES ChartOfAccounts(AccountID),
    FOREIGN KEY (SalesAccountID) REFERENCES ChartOfAccounts(AccountID)
);

CREATE TABLE PurchaseInvoices (
    PurchaseInvoiceID INT PRIMARY KEY AUTO_INCREMENT,
    CompanyID INT NOT NULL,
    InvoiceNumber VARCHAR(50) NOT NULL,
    InvoiceDate DATE NOT NULL,
    SupplierID INT NOT NULL,
    CurrencyID INT NOT NULL,
    ExchangeRate DECIMAL(15,6) DEFAULT 1,
    SubTotal DECIMAL(15,2) DEFAULT 0,
    TaxAmount DECIMAL(15,2) DEFAULT 0,
    DiscountAmount DECIMAL(15,2) DEFAULT 0,
    TotalAmount DECIMAL(15,2) DEFAULT 0,
    DueDate DATE,
    PaymentTerms ENUM('Cash', 'Net7', 'Net15', 'Net30', 'Net60') DEFAULT 'Net30',
    Status ENUM('Draft', 'Received', 'Paid', 'PartialPaid', 'Cancelled') DEFAULT 'Draft',
    PayableAccountID INT,
    PurchaseAccountID INT,
    TaxAccountID INT,
    ProjectID INT,
    Notes TEXT,
    CreatedBy INT NOT NULL,
    CreatedAt DATETIME DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (CompanyID) REFERENCES Companies(CompanyID),
    FOREIGN KEY (SupplierID) REFERENCES Suppliers(SupplierID),
    FOREIGN KEY (CurrencyID) REFERENCES Currencies(CurrencyID),
    FOREIGN KEY (PayableAccountID) REFERENCES ChartOfAccounts(AccountID),
    FOREIGN KEY (PurchaseAccountID) REFERENCES ChartOfAccounts(AccountID),
    FOREIGN KEY (TaxAccountID) REFERENCES ChartOfAccounts(AccountID),
    FOREIGN KEY (ProjectID) REFERENCES Projects(ProjectID),
    FOREIGN KEY (CreatedBy) REFERENCES Users(UserID),
    UNIQUE KEY unique_purchase_invoice (CompanyID, InvoiceNumber)
);

CREATE TABLE ReceiptsPayments (
    TransactionID INT PRIMARY KEY AUTO_INCREMENT,
    CompanyID INT NOT NULL,
    TransactionNumber VARCHAR(50) NOT NULL,
    TransactionDate DATE NOT NULL,
    TransactionType ENUM('Receipt', 'Payment') NOT NULL,
    CustomerID INT,
    SupplierID INT,
    EmployeeID INT,
    BankAccountID INT NOT NULL,
    CurrencyID INT NOT NULL,
    ExchangeRate DECIMAL(15,6) DEFAULT 1,
    TotalAmount DECIMAL(15,2) DEFAULT 0,
    PaymentMethod ENUM('Cash', 'Check', 'BankTransfer', 'Card', 'Online') DEFAULT 'Cash',
    CheckNumber VARCHAR(50),
    CheckDate DATE,
    CheckDueDate DATE,
    BankName NVARCHAR(100),
    ReferenceNumber VARCHAR(100),
    Description TEXT,
    Status ENUM('Draft', 'Posted', 'Cancelled') DEFAULT 'Draft',
    CreatedBy INT NOT NULL,
    CreatedAt DATETIME DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (CompanyID) REFERENCES Companies(CompanyID),
    FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID),
    FOREIGN KEY (SupplierID) REFERENCES Suppliers(SupplierID),
    FOREIGN KEY (EmployeeID) REFERENCES Employees(EmployeeID),
    FOREIGN KEY (BankAccountID) REFERENCES ChartOfAccounts(AccountID),
    FOREIGN KEY (CurrencyID) REFERENCES Currencies(CurrencyID),
    FOREIGN KEY (CreatedBy) REFERENCES Users(UserID),
    UNIQUE KEY unique_transaction (CompanyID, TransactionNumber)
);

CREATE TABLE ReceiptPaymentItems (
    TransactionItemID INT PRIMARY KEY AUTO_INCREMENT,
    TransactionID INT NOT NULL,
    InvoiceID INT, -- فاکتور مرتبط
    InvoiceType ENUM('Sales', 'Purchase') DEFAULT 'Sales',
    Amount DECIMAL(15,2) DEFAULT 0,
    DiscountAmount DECIMAL(15,2) DEFAULT 0,
    TaxAmount DECIMAL(15,2) DEFAULT 0,
    TotalAmount DECIMAL(15,2) DEFAULT 0,
    Description TEXT,
    AccountID INT, -- حساب مرتبط
    
    FOREIGN KEY (TransactionID) REFERENCES ReceiptsPayments(TransactionID) ON DELETE CASCADE,
    FOREIGN KEY (InvoiceID) REFERENCES SalesInvoices(InvoiceID),
    FOREIGN KEY (AccountID) REFERENCES ChartOfAccounts(AccountID)
);

CREATE TABLE FixedAssets (
    AssetID INT PRIMARY KEY AUTO_INCREMENT,
    CompanyID INT NOT NULL,
    AssetCode VARCHAR(20) NOT NULL,
    AssetName NVARCHAR(200) NOT NULL,
    AssetCategoryID INT NOT NULL,
    PurchaseDate DATE,
    PurchaseCost DECIMAL(15,2) DEFAULT 0,
    UsefulLife INT, -- عمر مفید به ماه
    SalvageValue DECIMAL(15,2) DEFAULT 0, -- ارزش اسقاط
    DepreciationMethod ENUM('StraightLine', 'DecliningBalance', 'SumOfYears') DEFAULT 'StraightLine',
    CurrentValue DECIMAL(15,2) DEFAULT 0,
    AccumulatedDepreciation DECIMAL(15,2) DEFAULT 0,
    NetValue DECIMAL(15,2) DEFAULT 0,
    AssetAccountID INT,
    DepreciationAccountID INT,
    AccumulatedDepreciationAccountID INT,
    Location NVARCHAR(100),
    CustodianID INT, -- متصدی دارایی
    Status ENUM('Active', 'Disposed', 'Sold', 'Transferred') DEFAULT 'Active',
    IsActive BOOLEAN DEFAULT TRUE,
    CreatedAt DATETIME DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (CompanyID) REFERENCES Companies(CompanyID),
    FOREIGN KEY (AssetCategoryID) REFERENCES AssetCategories(CategoryID),
    FOREIGN KEY (AssetAccountID) REFERENCES ChartOfAccounts(AccountID),
    FOREIGN KEY (DepreciationAccountID) REFERENCES ChartOfAccounts(AccountID),
    FOREIGN KEY (AccumulatedDepreciationAccountID) REFERENCES ChartOfAccounts(AccountID),
    FOREIGN KEY (CustodianID) REFERENCES Employees(EmployeeID),
    UNIQUE KEY unique_asset (CompanyID, AssetCode)
);

CREATE TABLE AssetDepreciations (
    DepreciationID INT PRIMARY KEY AUTO_INCREMENT,
    AssetID INT NOT NULL,
    PeriodID INT NOT NULL,
    DepreciationDate DATE NOT NULL,
    DepreciationAmount DECIMAL(15,2) DEFAULT 0,
    AccumulatedDepreciation DECIMAL(15,2) DEFAULT 0,
    NetValue DECIMAL(15,2) DEFAULT 0,
    VoucherID INT, -- سند حسابداری مرتبط
    Status ENUM('Calculated', 'Posted', 'Cancelled') DEFAULT 'Calculated',
    CreatedAt DATETIME DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (AssetID) REFERENCES FixedAssets(AssetID),
    FOREIGN KEY (PeriodID) REFERENCES FiscalPeriods(PeriodID),
    FOREIGN KEY (VoucherID) REFERENCES AccountingVouchers(VoucherID),
    UNIQUE KEY unique_asset_period (AssetID, PeriodID)
);

CREATE TABLE Budgets (
    BudgetID INT PRIMARY KEY AUTO_INCREMENT,
    CompanyID INT NOT NULL,
    BudgetCode VARCHAR(20) NOT NULL,
    BudgetName NVARCHAR(200) NOT NULL,
    BudgetYear INT NOT NULL,
    BudgetType ENUM('Operational', 'Capital', 'Project', 'Department') DEFAULT 'Operational',
    Status ENUM('Draft', 'Approved', 'Active', 'Closed') DEFAULT 'Draft',
    TotalAmount DECIMAL(15,2) DEFAULT 0,
    ApprovedBy INT,
    ApprovedAt DATETIME,
    CreatedBy INT NOT NULL,
    CreatedAt DATETIME DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (CompanyID) REFERENCES Companies(CompanyID),
    FOREIGN KEY (ApprovedBy) REFERENCES Users(UserID),
    FOREIGN KEY (CreatedBy) REFERENCES Users(UserID),
    UNIQUE KEY unique_budget (CompanyID, BudgetCode)
);

CREATE TABLE BudgetItems (
    BudgetItemID INT PRIMARY KEY AUTO_INCREMENT,
    BudgetID INT NOT NULL,
    AccountID INT NOT NULL,
    CostCenterID INT,
    ProjectID INT,
    PeriodNumber INT NOT NULL, -- شماره دوره (1 تا 12)
    BudgetAmount DECIMAL(15,2) DEFAULT 0,
    ActualAmount DECIMAL(15,2) DEFAULT 0,
    VarianceAmount DECIMAL(15,2) GENERATED ALWAYS AS (BudgetAmount - ActualAmount) STORED,
    VariancePercentage DECIMAL(5,2) GENERATED ALWAYS AS (
        CASE 
            WHEN BudgetAmount > 0 THEN ((BudgetAmount - ActualAmount) / BudgetAmount) * 100 
            ELSE 0 
        END
    ) STORED,
    
    FOREIGN KEY (BudgetID) REFERENCES Budgets(BudgetID) ON DELETE CASCADE,
    FOREIGN KEY (AccountID) REFERENCES ChartOfAccounts(AccountID),
    FOREIGN KEY (CostCenterID) REFERENCES CostCenters(CostCenterID),
    FOREIGN KEY (ProjectID) REFERENCES Projects(ProjectID),
    UNIQUE KEY unique_budget_item (BudgetID, AccountID, CostCenterID, ProjectID, PeriodNumber)
);

CREATE TABLE Users (
    UserID INT PRIMARY KEY AUTO_INCREMENT,
    CompanyID INT NOT NULL,
    Username VARCHAR(50) UNIQUE NOT NULL,
    PasswordHash VARCHAR(255) NOT NULL,
    Email VARCHAR(100),
    FirstName NVARCHAR(50),
    LastName NVARCHAR(50),
    UserType ENUM('Admin', 'Accountant', 'Manager', 'Viewer') DEFAULT 'Accountant',
    IsActive BOOLEAN DEFAULT TRUE,
    LastLogin DATETIME,
    CreatedAt DATETIME DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (CompanyID) REFERENCES Companies(CompanyID)
);

CREATE TABLE UserPermissions (
    PermissionID INT PRIMARY KEY AUTO_INCREMENT,
    UserID INT NOT NULL,
    ModuleName VARCHAR(50) NOT NULL, -- Accounting, Reports, Setup, etc.
    PermissionType ENUM('View', 'Create', 'Edit', 'Delete', 'Approve', 'All') DEFAULT 'View',
    IsGranted BOOLEAN DEFAULT TRUE,
    
    FOREIGN KEY (UserID) REFERENCES Users(UserID) ON DELETE CASCADE,
    UNIQUE KEY unique_permission (UserID, ModuleName, PermissionType)
);

CREATE TABLE AuditLog (
    LogID BIGINT PRIMARY KEY AUTO_INCREMENT,
    CompanyID INT NOT NULL,
    UserID INT NOT NULL,
    ActionType VARCHAR(50) NOT NULL, -- Create, Update, Delete, Post, Cancel
    TableName VARCHAR(50) NOT NULL,
    RecordID INT,
    Description TEXT,
    OldValues JSON,
    NewValues JSON,
    IPAddress VARCHAR(45),
    UserAgent TEXT,
    CreatedAt DATETIME DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (CompanyID) REFERENCES Companies(CompanyID),
    FOREIGN KEY (UserID) REFERENCES Users(UserID),
    INDEX idx_created (CreatedAt),
    INDEX idx_user (UserID)
);

-- درج ارزها
INSERT INTO Currencies (CurrencyCode, CurrencyName, Symbol, IsBaseCurrency) VALUES
('IRR', 'ریال ایران', '﷼', TRUE),
('USD', 'دلار آمریکا', '$', FALSE),
('EUR', 'یورو', '€', FALSE);

-- درج گروه‌های حساب
INSERT INTO AccountGroups (GroupCode, GroupName, AccountType, NormalBalance) VALUES
('1', 'دارایی‌های جاری', 'Asset', 'Debit'),
('2', 'دارایی‌های ثابت', 'Asset', 'Debit'),
('3', 'بدهی‌های جاری', 'Liability', 'Credit'),
('4', 'بدهی‌های بلندمدت', 'Liability', 'Credit'),
('5', 'حقوق صاحبان سهام', 'Equity', 'Credit'),
('6', 'درآمدها', 'Income', 'Credit'),
('7', 'هزینه‌ها', 'Expense', 'Debit');

-- درج حساب‌های کل نمونه
INSERT INTO ChartOfAccounts (CompanyID, AccountCode, AccountName, GroupID, AccountType, NormalBalance) VALUES
(1, '10101', 'صندوق', 1, 'Asset', 'Debit'),
(1, '10102', 'بانک ملی', 1, 'Asset', 'Debit'),
(1, '10301', 'اسناد دریافتنی', 1, 'Asset', 'Debit'),
(1, '20101', 'اثاثه اداری', 2, 'Asset', 'Debit'),
(1, '30101', 'اسناد پرداختنی', 3, 'Liability', 'Credit'),
(1, '40101', 'وام بلندمدت', 4, 'Liability', 'Credit'),
(1, '50101', 'سرمایه', 5, 'Equity', 'Credit'),
(1, '60101', 'فروش محصولات', 6, 'Income', 'Credit'),
(1, '70101', 'هزینه حقوق', 7, 'Expense', 'Debit');