-- ============================================================
-- Asset Management: Master Tables (Phase 1)
-- Referensi: Snipe-IT (Categories, Manufacturers, Status Labels,
--             Models, Locations). Pattern: PATTERN_MASTER_AND_JUNCTION.md
-- ============================================================
-- Jalankan script ini di phpMyAdmin pada database SAPPS.
-- AMAN: Hanya CREATE TABLE IF NOT EXISTS. Tidak mengubah/hapus
--       tabel atau data yang sudah ada.
-- Nama tabel: lowercase, tanpa underscore, plural (sesuai pattern).
-- ============================================================

-- 1. Asset Category (jenis aset: Laptop, Monitor, dll.)
CREATE TABLE IF NOT EXISTS `assetcategories` (
  `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,
  `name` varchar(150) NOT NULL,
  `display_name` varchar(150) NOT NULL,
  `description` text DEFAULT NULL,
  `company_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_assetcategories_company_id` (`company_id`),
  KEY `idx_assetcategories_enabled` (`enabled`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 2. Asset Manufacturer (pabrikan: Dell, HP, Lenovo, dll.)
CREATE TABLE IF NOT EXISTS `assetmanufacturers` (
  `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,
  `name` varchar(150) NOT NULL,
  `display_name` varchar(150) NOT NULL,
  `description` text DEFAULT NULL,
  `company_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_assetmanufacturers_company_id` (`company_id`),
  KEY `idx_assetmanufacturers_enabled` (`enabled`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 3. Asset Status Label (status: Deployable, Undeployable, Pending, dll.)
CREATE TABLE IF NOT EXISTS `assetstatuslabels` (
  `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,
  `name` varchar(150) NOT NULL,
  `display_name` varchar(150) NOT NULL,
  `description` text DEFAULT NULL,
  `type` varchar(50) DEFAULT NULL COMMENT 'deployable|undeployable|pending (Snipe-IT style)',
  `color` varchar(20) DEFAULT NULL COMMENT 'hex or name for UI',
  `company_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_assetstatuslabels_company_id` (`company_id`),
  KEY `idx_assetstatuslabels_enabled` (`enabled`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 4. Asset Location (lokasi fisik; bisa hierarki via parent_id)
CREATE TABLE IF NOT EXISTS `assetlocations` (
  `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,
  `name` varchar(150) NOT NULL,
  `display_name` varchar(150) NOT NULL,
  `description` text DEFAULT NULL,
  `address` text DEFAULT NULL,
  `parent_id` int(11) DEFAULT NULL COMMENT 'parent location for hierarchy',
  `company_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_assetlocations_company_id` (`company_id`),
  KEY `idx_assetlocations_enabled` (`enabled`),
  KEY `idx_assetlocations_parent_id` (`parent_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 5. Asset Model (model aset: gabungan manufacturer + category + nama model)
CREATE TABLE IF NOT EXISTS `assetmodels` (
  `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,
  `name` varchar(150) NOT NULL,
  `display_name` varchar(150) NOT NULL,
  `description` text DEFAULT NULL,
  `model_number` varchar(100) DEFAULT NULL,
  `manufacturer_id` int(11) DEFAULT NULL,
  `category_id` int(11) DEFAULT NULL,
  `company_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_assetmodels_company_id` (`company_id`),
  KEY `idx_assetmodels_enabled` (`enabled`),
  KEY `idx_assetmodels_manufacturer_id` (`manufacturer_id`),
  KEY `idx_assetmodels_category_id` (`category_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- Foreign keys antar tabel asset (aman, tidak menyentuh tabel lain).
-- Jika salah satu ALTER gagal (constraint sudah ada), skip baris itu.
-- ============================================================

-- Asset Location: parent location (self-reference)
-- ALTER TABLE `assetlocations`
--   ADD CONSTRAINT `fk_assetlocations_parent` FOREIGN KEY (`parent_id`) REFERENCES `assetlocations` (`id`) ON DELETE SET NULL ON UPDATE CASCADE;

-- Asset Model: manufacturer & category
-- ALTER TABLE `assetmodels`
--   ADD CONSTRAINT `fk_assetmodels_manufacturer` FOREIGN KEY (`manufacturer_id`) REFERENCES `assetmanufacturers` (`id`) ON DELETE SET NULL ON UPDATE CASCADE;
-- ALTER TABLE `assetmodels`
--   ADD CONSTRAINT `fk_assetmodels_category` FOREIGN KEY (`category_id`) REFERENCES `assetcategories` (`id`) ON DELETE SET NULL ON UPDATE CASCADE;

-- Optional: FK ke companies (uncomment jika tabel companies ada dan struktur sesuai)
-- ALTER TABLE `assetcategories`   ADD CONSTRAINT `fk_assetcategories_company`   FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE SET NULL ON UPDATE CASCADE;
-- ALTER TABLE `assetmanufacturers` ADD CONSTRAINT `fk_assetmanufacturers_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE SET NULL ON UPDATE CASCADE;
-- ALTER TABLE `assetstatuslabels` ADD CONSTRAINT `fk_assetstatuslabels_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE SET NULL ON UPDATE CASCADE;
-- ALTER TABLE `assetlocations`    ADD CONSTRAINT `fk_assetlocations_company`    FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE SET NULL ON UPDATE CASCADE;
-- ALTER TABLE `assetmodels`       ADD CONSTRAINT `fk_assetmodels_company`       FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE SET NULL ON UPDATE CASCADE;
-- ============================================================