-- DLRL KRC Reports — Role Based Access Control
-- Run once against koha_library (safe to re-run: uses IF NOT EXISTS / IGNORE)

CREATE TABLE IF NOT EXISTS `app_roles` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `slug` varchar(100) NOT NULL,
  `description` varchar(255) DEFAULT NULL,
  `is_superadmin` tinyint(1) NOT NULL DEFAULT 0,
  `status` tinyint(1) NOT NULL DEFAULT 1,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_app_roles_slug` (`slug`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `app_modules` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `module_key` varchar(80) NOT NULL,
  `module_name` varchar(150) NOT NULL,
  `module_group` varchar(80) NOT NULL DEFAULT 'Reports',
  `route` varchar(150) DEFAULT NULL,
  `sort_order` int(11) NOT NULL DEFAULT 0,
  `status` tinyint(1) NOT NULL DEFAULT 1,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_app_modules_key` (`module_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `app_role_permissions` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `role_id` int(11) unsigned NOT NULL,
  `module_key` varchar(80) NOT NULL,
  `can_view` tinyint(1) NOT NULL DEFAULT 0,
  `can_export` tinyint(1) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_role_module` (`role_id`,`module_key`),
  KEY `idx_role_perm_module` (`module_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- Extend existing app user table (ignore errors if columns already exist)
-- Prefer running via Install controller which checks columns.

INSERT IGNORE INTO `app_roles` (`id`, `name`, `slug`, `description`, `is_superadmin`, `status`) VALUES
(1, 'Super Admin', 'superadmin', 'Full access to all reports and administration', 1, 1),
(2, 'Report Viewer', 'report_viewer', 'Can view reports only (no export)', 0, 1),
(3, 'Report Officer', 'report_officer', 'Can view and export assigned reports', 0, 1);

INSERT IGNORE INTO `app_modules` (`module_key`, `module_name`, `module_group`, `route`, `sort_order`) VALUES
('overdue_report_details', 'Overdue Report', 'Circulation', 'report/overdue_report_details', 10),
('issue_report', 'Checkout Report', 'Circulation', 'report/issue_report', 20),
('current_checkout', 'Current Checkout Report', 'Circulation', 'report/current_checkout', 25),
('return_report', 'Return Report', 'Circulation', 'report/return_report', 30),
('circulation_report', 'Circulation Report', 'Circulation', 'report/circulation_report', 40),
('reservation_report', 'Reservation Report', 'Circulation', 'report/reservation_report', 50),
('due_clearance', 'Due Clearance Report', 'Circulation', 'report/due_clearance', 60),
('accession_register', 'Accession Register Report', 'Catalogue', 'report/accession_register', 70),
('search_catalogue_report', 'Search Catalogue Report', 'Catalogue', 'report/search_catalogue_report', 72),
('new_arrivals', 'New Arrivals Report', 'Catalogue', 'report/new_arrivals', 80),
('subject_wise_report', 'Subject Wise Report', 'Catalogue', 'report/subject_wise_report', 81),
('patent_report', 'Patent Report', 'Catalogue', 'report/patent_report', 82),
('institutional_repository', 'Institutional Repository Report', 'Catalogue', 'report/institutional_repository', 85),
('standards_data', 'Standards Data Report', 'Catalogue', 'report/standards_data', 86),
('ereport_books', 'EReport Books Report', 'Catalogue', 'report/ereport_books', 87),
('top_books_report', 'Top Books Report', 'Circulation', 'report/top_books_report', 90),
('single_book_report', 'Single Catalogue History Report', 'Circulation', 'report/single_book_report', 100),
('overall_material_statistics_report', 'Material Statistics Report', 'Catalogue', 'report/overall_material_statistics_report', 110),
('publisher_year_wise_report', 'Publication Wise Report', 'Catalogue', 'report/publisher_year_wise_report', 120),
('publisher_person_wise_report', 'Publisher Wise Report', 'Catalogue', 'report/publisher_person_wise_report', 130),
('top_users_report', 'Top Users Report', 'Circulation', 'report/top_users_report', 140),
('single_user_report', 'Single User History Report', 'Circulation', 'report/single_user_report', 150),
('user_report', 'User Report', 'Users', 'report/user_report', 160),
('search_user_report', 'Search User Report', 'Users', 'report/search_user_report', 165),
('so_report', 'SO Report', 'Documents', 'report/so_report', 170),
('crv_report', 'CRV Report', 'Documents', 'report/crv_report', 180),
('manage_users', 'Manage Users', 'Administration', 'users', 900),
('manage_roles', 'Manage Roles', 'Administration', 'roles', 910),
('manage_permissions', 'Role Permissions', 'Administration', 'permissions', 920),
('manage_font_settings', 'Font Settings', 'Administration', 'settings', 930);
