-- =========================================================
-- SIMDATADIK
-- Schema Modul Perpustakaan (Tahap L1 - Revisi Kompatibel MariaDB)
-- File: sql/schema_library.sql
-- Catatan:
-- - File ini menambahkan schema perpustakaan baru tanpa mengubah schema lama.
-- - Disusun agar kompatibel dengan MySQL/MariaDB umum di shared hosting / phpMyAdmin.
-- - Validasi yang sebelumnya memakai CHECK dipindahkan ke level aplikasi.
-- =========================================================

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ---------------------------------------------------------
-- 1. library_settings
-- Konfigurasi perpustakaan per sekolah
-- ---------------------------------------------------------
CREATE TABLE IF NOT EXISTS `library_settings` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `school_id` BIGINT UNSIGNED NOT NULL,
    `opening_hours` TEXT NULL COMMENT 'JSON/string jam operasional per hari',
    `loan_duration_student` INT UNSIGNED NOT NULL DEFAULT 7,
    `loan_duration_teacher` INT UNSIGNED NOT NULL DEFAULT 14,
    `loan_duration_staff` INT UNSIGNED NOT NULL DEFAULT 10,
    `max_loans_student` INT UNSIGNED NOT NULL DEFAULT 2,
    `max_loans_teacher` INT UNSIGNED NOT NULL DEFAULT 5,
    `max_loans_staff` INT UNSIGNED NOT NULL DEFAULT 3,
    `fine_per_day` DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    `currency` VARCHAR(10) NOT NULL DEFAULT 'IDR',
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_library_settings_school` (`school_id`),
    KEY `idx_library_settings_school_id` (`school_id`),
    CONSTRAINT `fk_library_settings_school`
        FOREIGN KEY (`school_id`) REFERENCES `schools` (`id`)
        ON UPDATE CASCADE
        ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------
-- 2. library_categories
-- Kategori koleksi perpustakaan per sekolah
-- ---------------------------------------------------------
CREATE TABLE IF NOT EXISTS `library_categories` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `school_id` BIGINT UNSIGNED NOT NULL,
    `name` VARCHAR(100) NOT NULL,
    `description` TEXT NULL,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_library_categories_school_name` (`school_id`, `name`),
    KEY `idx_library_categories_school_id` (`school_id`),
    CONSTRAINT `fk_library_categories_school`
        FOREIGN KEY (`school_id`) REFERENCES `schools` (`id`)
        ON UPDATE CASCADE
        ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------
-- 3. library_books
-- Data bibliografi umum buku fisik
-- ---------------------------------------------------------
CREATE TABLE IF NOT EXISTS `library_books` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `school_id` BIGINT UNSIGNED NOT NULL,
    `isbn` VARCHAR(50) NULL,
    `title` VARCHAR(255) NOT NULL,
    `author` VARCHAR(255) NOT NULL,
    `publisher` VARCHAR(255) NULL,
    `publication_year` YEAR NULL,
    `edition` VARCHAR(50) NULL,
    `description` TEXT NULL,
    `cover_image` VARCHAR(255) NULL,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_library_books_school_id` (`school_id`),
    KEY `idx_library_books_isbn` (`isbn`),
    KEY `idx_library_books_title` (`title`),
    KEY `idx_library_books_author` (`author`),
    CONSTRAINT `fk_library_books_school`
        FOREIGN KEY (`school_id`) REFERENCES `schools` (`id`)
        ON UPDATE CASCADE
        ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------
-- 4. library_book_category
-- Relasi many-to-many buku dengan kategori
-- ---------------------------------------------------------
CREATE TABLE IF NOT EXISTS `library_book_category` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `book_id` BIGINT UNSIGNED NOT NULL,
    `category_id` BIGINT UNSIGNED NOT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_library_book_category_pair` (`book_id`, `category_id`),
    KEY `idx_library_book_category_book_id` (`book_id`),
    KEY `idx_library_book_category_category_id` (`category_id`),
    CONSTRAINT `fk_library_book_category_book`
        FOREIGN KEY (`book_id`) REFERENCES `library_books` (`id`)
        ON UPDATE CASCADE
        ON DELETE CASCADE,
    CONSTRAINT `fk_library_book_category_category`
        FOREIGN KEY (`category_id`) REFERENCES `library_categories` (`id`)
        ON UPDATE CASCADE
        ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------
-- 5. library_copies
-- Eksemplar fisik per buku
-- ---------------------------------------------------------
CREATE TABLE IF NOT EXISTS `library_copies` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `book_id` BIGINT UNSIGNED NOT NULL,
    `copy_code` VARCHAR(100) NOT NULL,
    `location_code` VARCHAR(100) NULL,
    `status` ENUM('available', 'on_loan', 'lost', 'damaged', 'inactive') NOT NULL DEFAULT 'available',
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_library_copies_copy_code` (`copy_code`),
    KEY `idx_library_copies_book_id` (`book_id`),
    KEY `idx_library_copies_status` (`status`),
    KEY `idx_library_copies_location_code` (`location_code`),
    CONSTRAINT `fk_library_copies_book`
        FOREIGN KEY (`book_id`) REFERENCES `library_books` (`id`)
        ON UPDATE CASCADE
        ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------
-- 6. library_digital_items
-- Koleksi digital / e-book / file digital
-- Catatan:
-- - Minimal salah satu dari file_path atau external_url wajib diisi
-- - Validasi dilakukan di level aplikasi
-- ---------------------------------------------------------
CREATE TABLE IF NOT EXISTS `library_digital_items` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `school_id` BIGINT UNSIGNED NOT NULL,
    `title` VARCHAR(255) NOT NULL,
    `author` VARCHAR(255) NULL,
    `publisher` VARCHAR(255) NULL,
    `publication_year` YEAR NULL,
    `description` TEXT NULL,
    `file_path` VARCHAR(255) NULL,
    `external_url` VARCHAR(255) NULL,
    `access_level` ENUM('all', 'teachers_only', 'students_only', 'custom') NOT NULL DEFAULT 'all',
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_library_digital_items_school_id` (`school_id`),
    KEY `idx_library_digital_items_title` (`title`),
    KEY `idx_library_digital_items_author` (`author`),
    KEY `idx_library_digital_items_access_level` (`access_level`),
    CONSTRAINT `fk_library_digital_items_school`
        FOREIGN KEY (`school_id`) REFERENCES `schools` (`id`)
        ON UPDATE CASCADE
        ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------
-- 7. library_members
-- Anggota perpustakaan (terhubung user atau eksternal)
-- ---------------------------------------------------------
CREATE TABLE IF NOT EXISTS `library_members` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `school_id` BIGINT UNSIGNED NOT NULL,
    `user_id` BIGINT UNSIGNED NULL,
    `member_code` VARCHAR(100) NOT NULL,
    `name` VARCHAR(150) NOT NULL,
    `member_type` ENUM('student', 'teacher', 'staff', 'external') NOT NULL DEFAULT 'student',
    `status` ENUM('active', 'inactive') NOT NULL DEFAULT 'active',
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_library_members_member_code` (`member_code`),
    UNIQUE KEY `uk_library_members_school_user` (`school_id`, `user_id`),
    KEY `idx_library_members_school_id` (`school_id`),
    KEY `idx_library_members_user_id` (`user_id`),
    KEY `idx_library_members_member_type` (`member_type`),
    KEY `idx_library_members_status` (`status`),
    CONSTRAINT `fk_library_members_school`
        FOREIGN KEY (`school_id`) REFERENCES `schools` (`id`)
        ON UPDATE CASCADE
        ON DELETE CASCADE,
    CONSTRAINT `fk_library_members_user`
        FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
        ON UPDATE CASCADE
        ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------
-- 8. library_loans
-- Transaksi peminjaman buku fisik
-- ---------------------------------------------------------
CREATE TABLE IF NOT EXISTS `library_loans` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `school_id` BIGINT UNSIGNED NOT NULL,
    `member_id` BIGINT UNSIGNED NOT NULL,
    `copy_id` BIGINT UNSIGNED NOT NULL,
    `loan_date` DATETIME NOT NULL,
    `due_date` DATETIME NOT NULL,
    `return_date` DATETIME NULL,
    `status` ENUM('on_loan', 'returned', 'lost', 'damaged') NOT NULL DEFAULT 'on_loan',
    `late_days` INT UNSIGNED NULL DEFAULT NULL,
    `fine_amount` DECIMAL(10,2) NULL DEFAULT 0.00,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_library_loans_school_id` (`school_id`),
    KEY `idx_library_loans_member_id` (`member_id`),
    KEY `idx_library_loans_copy_id` (`copy_id`),
    KEY `idx_library_loans_status` (`status`),
    KEY `idx_library_loans_loan_date` (`loan_date`),
    KEY `idx_library_loans_due_date` (`due_date`),
    KEY `idx_library_loans_return_date` (`return_date`),
    CONSTRAINT `fk_library_loans_school`
        FOREIGN KEY (`school_id`) REFERENCES `schools` (`id`)
        ON UPDATE CASCADE
        ON DELETE CASCADE,
    CONSTRAINT `fk_library_loans_member`
        FOREIGN KEY (`member_id`) REFERENCES `library_members` (`id`)
        ON UPDATE CASCADE
        ON DELETE RESTRICT,
    CONSTRAINT `fk_library_loans_copy`
        FOREIGN KEY (`copy_id`) REFERENCES `library_copies` (`id`)
        ON UPDATE CASCADE
        ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------
-- 9. library_reservations
-- Reservasi / booking buku
-- ---------------------------------------------------------
CREATE TABLE IF NOT EXISTS `library_reservations` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `school_id` BIGINT UNSIGNED NOT NULL,
    `member_id` BIGINT UNSIGNED NOT NULL,
    `copy_id` BIGINT UNSIGNED NULL,
    `book_id` BIGINT UNSIGNED NOT NULL,
    `status` ENUM('pending', 'ready', 'cancelled', 'expired', 'completed') NOT NULL DEFAULT 'pending',
    `reserved_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `expired_at` DATETIME NULL,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_library_reservations_school_id` (`school_id`),
    KEY `idx_library_reservations_member_id` (`member_id`),
    KEY `idx_library_reservations_copy_id` (`copy_id`),
    KEY `idx_library_reservations_book_id` (`book_id`),
    KEY `idx_library_reservations_status` (`status`),
    KEY `idx_library_reservations_reserved_at` (`reserved_at`),
    CONSTRAINT `fk_library_reservations_school`
        FOREIGN KEY (`school_id`) REFERENCES `schools` (`id`)
        ON UPDATE CASCADE
        ON DELETE CASCADE,
    CONSTRAINT `fk_library_reservations_member`
        FOREIGN KEY (`member_id`) REFERENCES `library_members` (`id`)
        ON UPDATE CASCADE
        ON DELETE RESTRICT,
    CONSTRAINT `fk_library_reservations_copy`
        FOREIGN KEY (`copy_id`) REFERENCES `library_copies` (`id`)
        ON UPDATE CASCADE
        ON DELETE SET NULL,
    CONSTRAINT `fk_library_reservations_book`
        FOREIGN KEY (`book_id`) REFERENCES `library_books` (`id`)
        ON UPDATE CASCADE
        ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------
-- 10. library_fines
-- Detail denda dan status pelunasan
-- ---------------------------------------------------------
CREATE TABLE IF NOT EXISTS `library_fines` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `loan_id` BIGINT UNSIGNED NOT NULL,
    `member_id` BIGINT UNSIGNED NOT NULL,
    `amount` DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    `status` ENUM('unpaid', 'paid', 'waived') NOT NULL DEFAULT 'unpaid',
    `paid_at` DATETIME NULL,
    `notes` TEXT NULL,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_library_fines_loan_id` (`loan_id`),
    KEY `idx_library_fines_member_id` (`member_id`),
    KEY `idx_library_fines_status` (`status`),
    CONSTRAINT `fk_library_fines_loan`
        FOREIGN KEY (`loan_id`) REFERENCES `library_loans` (`id`)
        ON UPDATE CASCADE
        ON DELETE CASCADE,
    CONSTRAINT `fk_library_fines_member`
        FOREIGN KEY (`member_id`) REFERENCES `library_members` (`id`)
        ON UPDATE CASCADE
        ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------
-- 11. library_reading_logs
-- Log aktivitas untuk analitik perpustakaan
-- Catatan:
-- - Minimal salah satu dari book_id atau digital_item_id wajib diisi
-- - Validasi dilakukan di level aplikasi
-- ---------------------------------------------------------
CREATE TABLE IF NOT EXISTS `library_reading_logs` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `school_id` BIGINT UNSIGNED NOT NULL,
    `member_id` BIGINT UNSIGNED NOT NULL,
    `book_id` BIGINT UNSIGNED NULL,
    `digital_item_id` BIGINT UNSIGNED NULL,
    `activity_type` ENUM('borrow', 'return', 'read_digital', 'view_detail') NOT NULL,
    `activity_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_library_reading_logs_school_id` (`school_id`),
    KEY `idx_library_reading_logs_member_id` (`member_id`),
    KEY `idx_library_reading_logs_book_id` (`book_id`),
    KEY `idx_library_reading_logs_digital_item_id` (`digital_item_id`),
    KEY `idx_library_reading_logs_activity_type` (`activity_type`),
    KEY `idx_library_reading_logs_activity_at` (`activity_at`),
    CONSTRAINT `fk_library_reading_logs_school`
        FOREIGN KEY (`school_id`) REFERENCES `schools` (`id`)
        ON UPDATE CASCADE
        ON DELETE CASCADE,
    CONSTRAINT `fk_library_reading_logs_member`
        FOREIGN KEY (`member_id`) REFERENCES `library_members` (`id`)
        ON UPDATE CASCADE
        ON DELETE RESTRICT,
    CONSTRAINT `fk_library_reading_logs_book`
        FOREIGN KEY (`book_id`) REFERENCES `library_books` (`id`)
        ON UPDATE CASCADE
        ON DELETE SET NULL,
    CONSTRAINT `fk_library_reading_logs_digital_item`
        FOREIGN KEY (`digital_item_id`) REFERENCES `library_digital_items` (`id`)
        ON UPDATE CASCADE
        ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SET FOREIGN_KEY_CHECKS = 1;