-- --------------------------------------------------------
-- Post-import script: adds relation columns + pivot table data
-- Run AFTER importing the production SQL dump
-- --------------------------------------------------------

-- 1. Add new relation columns to barang table
ALTER TABLE `barang`
  ADD COLUMN `kelas_id` varchar(30) DEFAULT NULL AFTER `assigned_id`,
  ADD COLUMN `personal_id` varchar(10) DEFAULT NULL AFTER `kelas_id`;

ALTER TABLE `barang`
  ADD KEY `barang_kelas_id_index` (`kelas_id`),
  ADD KEY `barang_personal_id_index` (`personal_id`),
  ADD CONSTRAINT `barang_kelas_id_foreign`
    FOREIGN KEY (`kelas_id`) REFERENCES `kelas` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `barang_personal_id_foreign`
    FOREIGN KEY (`personal_id`) REFERENCES `personal` (`id`) ON DELETE CASCADE;

-- 2. Populate the new FK columns based on polymorphic assigned_type
UPDATE `barang` SET `kelas_id` = `assigned_id`
  WHERE `assigned_type` = 'App\\Models\\Kelas';

UPDATE `barang` SET `personal_id` = `assigned_id`
  WHERE `assigned_type` = 'App\\Models\\Personal';

-- 3. Create the barang_assignments pivot table
CREATE TABLE IF NOT EXISTS `barang_assignments` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `barang_id` varchar(50) NOT NULL,
  `kelas_id` varchar(30) DEFAULT NULL,
  `personal_id` varchar(10) DEFAULT NULL,
  `assigned_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `released_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `barang_assignments_idx` (`kelas_id`,`personal_id`,`barang_id`),
  CONSTRAINT `barang_assignments_barang_id_foreign`
    FOREIGN KEY (`barang_id`) REFERENCES `barang` (`id`) ON DELETE CASCADE,
  CONSTRAINT `barang_assignments_kelas_id_foreign`
    FOREIGN KEY (`kelas_id`) REFERENCES `kelas` (`id`) ON DELETE CASCADE,
  CONSTRAINT `barang_assignments_personal_id_foreign`
    FOREIGN KEY (`personal_id`) REFERENCES `personal` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 4. Populate barang_assignments from barang table
INSERT INTO `barang_assignments` (`barang_id`, `kelas_id`, `personal_id`, `assigned_at`)
SELECT
  `id`,
  `kelas_id`,
  `personal_id`,
  COALESCE(`created_at`, CURRENT_TIMESTAMP())
FROM `barang`
WHERE `kelas_id` IS NOT NULL OR `personal_id` IS NOT NULL;

-- --------------------------------------------------------
-- Verification queries (optional, run manually to check)
-- --------------------------------------------------------
-- SELECT COUNT(*) AS total_barang FROM barang;
-- SELECT COUNT(*) AS total_with_kelas FROM barang WHERE kelas_id IS NOT NULL;
-- SELECT COUNT(*) AS total_with_personal FROM barang WHERE personal_id IS NOT NULL;
-- SELECT COUNT(*) AS total_assignments FROM barang_assignments;
-- SELECT b.id, b.nama, b.kelas_id, b.personal_id FROM barang b LIMIT 10;