--
-- MokoSuite Library Tables
--

CREATE TABLE IF NOT EXISTS `#__mokosuitelibrary_items` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `title` VARCHAR(255) NOT NULL,
  `subtitle` VARCHAR(255) NOT NULL DEFAULT '',
  `isbn` VARCHAR(20) NOT NULL DEFAULT '',
  `barcode` VARCHAR(50) NOT NULL DEFAULT '',
  `item_type` ENUM('book','dvd','equipment','tool','game','periodical','digital') NOT NULL DEFAULT 'book',
  `category` VARCHAR(100) NOT NULL DEFAULT '',
  `author` VARCHAR(255) NOT NULL DEFAULT '',
  `publisher` VARCHAR(255) NOT NULL DEFAULT '',
  `publish_year` SMALLINT UNSIGNED DEFAULT NULL,
  `description` TEXT,
  `cover_image` VARCHAR(500) NOT NULL DEFAULT '',
  `total_copies` INT UNSIGNED NOT NULL DEFAULT 1,
  `available_copies` INT UNSIGNED NOT NULL DEFAULT 1,
  `published` TINYINT NOT NULL DEFAULT 1,
  `created` DATETIME NOT NULL,
  `modified` DATETIME DEFAULT NULL,
  `created_by` INT NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  KEY `idx_isbn` (`isbn`),
  KEY `idx_barcode` (`barcode`),
  KEY `idx_type` (`item_type`),
  KEY `idx_category` (`category`),
  KEY `idx_author` (`author`(100)),
  FULLTEXT `ft_search` (`title`, `subtitle`, `author`, `description`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `#__mokosuitelibrary_copies` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `item_id` INT UNSIGNED NOT NULL,
  `copy_number` INT UNSIGNED NOT NULL DEFAULT 1,
  `barcode` VARCHAR(50) NOT NULL DEFAULT '',
  `condition_grade` ENUM('new','good','fair','poor','withdrawn') NOT NULL DEFAULT 'good',
  `location` VARCHAR(100) NOT NULL DEFAULT '',
  `status` ENUM('available','checked_out','on_hold','in_repair','lost','withdrawn') NOT NULL DEFAULT 'available',
  `notes` TEXT,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_barcode` (`barcode`),
  KEY `idx_item` (`item_id`),
  KEY `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `#__mokosuitelibrary_patrons` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `contact_id` INT DEFAULT NULL,
  `card_number` VARCHAR(50) NOT NULL DEFAULT '',
  `name` VARCHAR(255) NOT NULL,
  `email` VARCHAR(255) NOT NULL DEFAULT '',
  `phone` VARCHAR(50) NOT NULL DEFAULT '',
  `max_items` INT UNSIGNED NOT NULL DEFAULT 10,
  `status` ENUM('active','suspended','expired') NOT NULL DEFAULT 'active',
  `suspended_reason` VARCHAR(255) NOT NULL DEFAULT '',
  `membership_expires` DATE DEFAULT NULL,
  `created` DATETIME NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_card` (`card_number`),
  KEY `idx_contact` (`contact_id`),
  KEY `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `#__mokosuitelibrary_checkouts` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `copy_id` INT UNSIGNED NOT NULL,
  `patron_id` INT UNSIGNED NOT NULL,
  `checked_out_at` DATETIME NOT NULL,
  `due_date` DATE NOT NULL,
  `returned_at` DATETIME DEFAULT NULL,
  `renewals` INT UNSIGNED NOT NULL DEFAULT 0,
  `status` ENUM('active','returned','overdue','lost') NOT NULL DEFAULT 'active',
  `created_by` INT NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  KEY `idx_copy` (`copy_id`),
  KEY `idx_patron` (`patron_id`),
  KEY `idx_due` (`due_date`),
  KEY `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `#__mokosuitelibrary_reservations` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `item_id` INT UNSIGNED NOT NULL,
  `patron_id` INT UNSIGNED NOT NULL,
  `position` INT UNSIGNED NOT NULL DEFAULT 1,
  `status` ENUM('waiting','ready','fulfilled','cancelled','expired') NOT NULL DEFAULT 'waiting',
  `notified_at` DATETIME DEFAULT NULL,
  `expires_at` DATETIME DEFAULT NULL,
  `created` DATETIME NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_item` (`item_id`, `position`),
  KEY `idx_patron` (`patron_id`),
  KEY `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `#__mokosuitelibrary_fines` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `patron_id` INT UNSIGNED NOT NULL,
  `checkout_id` INT UNSIGNED DEFAULT NULL,
  `amount` DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  `paid` DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  `reason` VARCHAR(255) NOT NULL DEFAULT 'overdue',
  `status` ENUM('outstanding','paid','waived') NOT NULL DEFAULT 'outstanding',
  `waived_reason` VARCHAR(255) NOT NULL DEFAULT '',
  `created` DATETIME NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_patron` (`patron_id`),
  KEY `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `#__mokosuitelibrary_fine_payments` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `fine_id` INT UNSIGNED NOT NULL,
  `amount` DECIMAL(10,2) NOT NULL,
  `method` ENUM('cash','card','online','waiver') NOT NULL DEFAULT 'cash',
  `paid_at` DATETIME NOT NULL,
  `received_by` INT NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  KEY `idx_fine` (`fine_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;