-- 1. Tambah tabel yang hilang
CREATE TABLE IF NOT EXISTS `biblio_mark` (
  `id` varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL,
  `member_id` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL,
  `biblio_id` int NOT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY `id` (`id`),
  KEY `member_id_idx` (`member_id`),
  KEY `biblio_id_idx` (`biblio_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `cache` (
  `name` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL,
  `contents` text COLLATE utf8mb4_unicode_ci NOT NULL,
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  `expired_at` datetime DEFAULT NULL,
  UNIQUE KEY `name` (`name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `files_read` (
  `filelog_id` int NOT NULL AUTO_INCREMENT,
  `file_id` int NOT NULL,
  `date_read` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `member_id` varchar(20) COLLATE utf8mb3_unicode_ci DEFAULT NULL,
  `user_id` int DEFAULT NULL,
  `client_ip` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`filelog_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;

CREATE TABLE IF NOT EXISTS `item_custom` (
  `item_id` int NOT NULL,
  PRIMARY KEY (`item_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='one to one relation with real item table';

CREATE TABLE IF NOT EXISTS `mst_custom_field` (
  `field_id` int NOT NULL AUTO_INCREMENT,
  `primary_table` varchar(100) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci DEFAULT NULL,
  `dbfield` varchar(50) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `label` varchar(80) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `type` enum('text','checklist','numeric','dropdown','longtext','choice','date') CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `default` varchar(80) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci DEFAULT NULL,
  `max` int DEFAULT NULL,
  `data` text CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci,
  `indexed` tinyint(1) DEFAULT NULL,
  `class` varchar(100) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci DEFAULT NULL,
  `is_public` tinyint(1) DEFAULT NULL,
  `width` int DEFAULT '100',
  `note` text CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci,
  PRIMARY KEY (`dbfield`),
  UNIQUE KEY `field_id` (`field_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;

CREATE TABLE IF NOT EXISTS `mst_visitor_room` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
  `unique_code` varchar(5) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'Code for identification each room',
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_code_unq` (`unique_code`),
  KEY `unique_code_idx` (`unique_code`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `user_tokens` (
  `id` int NOT NULL AUTO_INCREMENT,
  `selector` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `hashed_validator` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `user_id` int NOT NULL,
  `expires_at` datetime NOT NULL,
  `created_at` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 2. Tambah kolom placement di biblio_attachment
ALTER TABLE `biblio_attachment` ADD COLUMN `placement` enum('link','popup','embed') CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci DEFAULT NULL AFTER `file_id`;

-- 3. Tambah kolom publish_date di content
ALTER TABLE `content` ADD COLUMN `publish_date` date DEFAULT NULL AFTER `is_draft`;

-- 4. Ubah is_draft di content menjadi default 0
ALTER TABLE `content` MODIFY `is_draft` smallint DEFAULT '0';

-- 5. Tambah kolom menus di group_access
ALTER TABLE `group_access` ADD COLUMN `menus` json DEFAULT NULL AFTER `module_id`;

-- 6. Tambah kolom sub_module dan action di system_log
ALTER TABLE `system_log` ADD COLUMN `sub_module` varchar(50) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci DEFAULT NULL AFTER `log_location`;
ALTER TABLE `system_log` ADD COLUMN `action` varchar(50) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci DEFAULT NULL AFTER `sub_module`;

-- 7. Tambah kolom room_code di visitor_count
ALTER TABLE `visitor_count` ADD COLUMN `room_code` varchar(5) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci DEFAULT NULL AFTER `institution`;
ALTER TABLE `visitor_count` ADD INDEX `room_code` (`room_code`);

-- 8. Perbaiki tipe data dan collation
ALTER TABLE `backup_log` MODIFY `backup_time` datetime DEFAULT NULL;
ALTER TABLE `backup_log` MODIFY `backup_file` text CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci;

ALTER TABLE `biblio` MODIFY `isbn_issn` varchar(32) COLLATE utf8mb3_unicode_ci DEFAULT NULL;
ALTER TABLE `biblio` MODIFY `source` varchar(10) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci DEFAULT NULL;
ALTER TABLE `biblio` ADD INDEX `rda_idx` (`content_type_id`, `media_type_id`, `carrier_type_id`) USING BTREE;

ALTER TABLE `member` MODIFY `last_login_ip` varchar(50) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci DEFAULT NULL;
ALTER TABLE `member_custom` MODIFY `member_id` varchar(20) COLLATE utf8mb3_unicode_ci NOT NULL;
ALTER TABLE `member_custom` CONVERT TO CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci;

ALTER TABLE `search_biblio` MODIFY `carrier_type` varchar(100) COLLATE utf8mb3_unicode_ci NOT NULL;
ALTER TABLE `search_biblio` MODIFY `content_type` varchar(100) COLLATE utf8mb3_unicode_ci NOT NULL;
ALTER TABLE `search_biblio` MODIFY `media_type` varchar(100) COLLATE utf8mb3_unicode_ci NOT NULL;
ALTER TABLE `search_biblio` ADD INDEX `rda_indexes` (`carrier_type`, `media_type`, `content_type`) USING BTREE;

ALTER TABLE `stock_take_item` MODIFY `checked_by` varchar(50) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci DEFAULT NULL;

ALTER TABLE `user` MODIFY `last_login_ip` varchar(50) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci DEFAULT NULL;

-- 9. Hapus tabel duplikat item_copy1 (jika tidak diperlukan)
-- DROP TABLE IF EXISTS `item_copy1`;

-- 10. Perbaiki nilai birth_date yang '0000-00-00'
UPDATE `member` SET `birth_date` = NULL WHERE `birth_date` = '0000-00-00';