-- ============================================================
-- Asset Management: Phase 2 Core (Assets & Logs)
-- Referensi: Snipe-IT + Polymorphic Assignment + Maintenance
-- ============================================================

-- 1. Assets (The actual hardware items)
CREATE TABLE IF NOT EXISTS `assets` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `creation` datetime NOT NULL,
  `modified` datetime NOT NULL,
  `modified_by` int(11) NOT NULL,
  `owner` int(11) NOT NULL,
  `createdAt` datetime DEFAULT NULL,
  `updatedAt` datetime DEFAULT NULL,
  `enabled` tinyint(1) NOT NULL DEFAULT 1,
  
  `asset_tag` varchar(100) NOT NULL,
  `serial` varchar(150) DEFAULT NULL,
  `name` varchar(150) DEFAULT NULL,
  `model_id` int(11) NOT NULL,
  `status_id` int(11) NOT NULL,
  `location_id` int(11) DEFAULT NULL,
  `rtd_location_id` int(11) DEFAULT NULL COMMENT 'Default Location',
  
  -- Polymorphic Assignment
  `assigned_to` int(11) DEFAULT NULL COMMENT 'ID of Employee, Department, or Location',
  `assigned_type` varchar(50) DEFAULT NULL COMMENT 'Employee|Department|Location',
  `assigned_at` datetime DEFAULT NULL,
  
  `purchase_date` date DEFAULT NULL,
  `purchase_cost` decimal(10,2) DEFAULT NULL,
  `order_number` varchar(100) DEFAULT NULL,
  `notes` text DEFAULT NULL,
  `image` varchar(255) DEFAULT NULL,
  `company_id` int(11) DEFAULT NULL,
  
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_assets_tag` (`asset_tag`),
  KEY `idx_assets_company_id` (`company_id`),
  KEY `idx_assets_status_id` (`status_id`),
  KEY `idx_assets_model_id` (`model_id`),
  KEY `idx_assets_location_id` (`location_id`),
  KEY `idx_assets_assigned` (`assigned_to`, `assigned_type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 2. Asset Logs (Unified Transaction Engine)
CREATE TABLE IF NOT EXISTS `assetlogs` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `creation` datetime NOT NULL,
  `modified` datetime NOT NULL,
  `modified_by` int(11) NOT NULL,
  `owner` int(11) NOT NULL,
  `createdAt` datetime DEFAULT NULL,
  `updatedAt` datetime DEFAULT NULL,
  
  `item_id` int(11) NOT NULL,
  `item_type` varchar(50) NOT NULL COMMENT 'asset|license|accessory|consumable|component',
  `action` varchar(50) NOT NULL COMMENT 'checkout|checkin|update|maintenance|audit|consume',
  
  `target_id` int(11) DEFAULT NULL COMMENT 'ID of Employee, Department, or Asset',
  `target_type` varchar(50) DEFAULT NULL COMMENT 'Employee|Department|Asset|Location',
  
  `location_id` int(11) DEFAULT NULL,
  `note` text DEFAULT NULL,
  `filename` varchar(255) DEFAULT NULL COMMENT 'For uploaded documents/receipts',
  
  `company_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_assetlogs_item` (`item_id`, `item_type`),
  KEY `idx_assetlogs_company_id` (`company_id`),
  KEY `idx_assetlogs_action` (`action`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 3. Asset Maintenances (Service records)
CREATE TABLE IF NOT EXISTS `assetmaintenances` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `creation` datetime NOT NULL,
  `modified` datetime NOT NULL,
  `modified_by` int(11) NOT NULL,
  `owner` int(11) NOT NULL,
  `createdAt` datetime DEFAULT NULL,
  `updatedAt` datetime DEFAULT NULL,
  
  `asset_id` int(11) NOT NULL,
  `supplier_id` int(11) DEFAULT NULL,
  `maintenance_type` varchar(50) NOT NULL COMMENT 'Repair|Upgrade|Routine|Hardware Support',
  `title` varchar(150) NOT NULL,
  `start_date` date DEFAULT NULL,
  `completion_date` date DEFAULT NULL,
  `asset_maintenance_time` int(11) DEFAULT NULL COMMENT 'Downtime in days',
  `cost` decimal(10,2) DEFAULT NULL,
  `notes` text DEFAULT NULL,
  `is_warranty` tinyint(1) NOT NULL DEFAULT 0,
  
  `company_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_maintenances_asset_id` (`asset_id`),
  KEY `idx_maintenances_company_id` (`company_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;