CREATE TABLE IF NOT EXISTS `AssetsRegister` (
  `ID` INT NOT NULL AUTO_INCREMENT,
  `asset_code` VARCHAR(60) NOT NULL,
  `asset_name` VARCHAR(255) NOT NULL,
  `account_id` INT NULL,
  `purchase_date` DATE NULL,
  `purchase_cost` BIGINT NOT NULL DEFAULT 0,
  `depreciation_rate` DECIMAL(7,4) NULL,
  `useful_life` INT NULL,
  `annual_depreciation` BIGINT NULL,
  `salvage_value` BIGINT NULL,
  `department` VARCHAR(180) NULL,
  `unit` VARCHAR(180) NULL,
  `custodian_name` VARCHAR(180) NULL,
  `status` VARCHAR(80) NOT NULL DEFAULT 'active',
  `accumulated_depreciation` BIGINT NOT NULL DEFAULT 0,
  `book_value` BIGINT NULL,
  `sale_date` DATE NULL,
  `sale_price` BIGINT NULL,
  `gain_loss` BIGINT NULL,
  `expense_ref_id` INT NULL,
  `expense_sub_ref_id` INT NULL,
  `note` TEXT NULL,
  `EnteredByUserId` INT NULL,
  `EntryDate` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `UpdateDate` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`ID`),
  UNIQUE KEY `uq_assets_code` (`asset_code`),
  KEY `idx_assets_account` (`account_id`),
  KEY `idx_assets_purchase_date` (`purchase_date`),
  KEY `idx_assets_status` (`status`),
  KEY `idx_assets_expense_ref` (`expense_ref_id`),
  KEY `idx_assets_expense_sub_ref` (`expense_sub_ref_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;