DROP TABLE IF EXISTS `Taxes`;;

DROP TABLE IF EXISTS `backup_history`;;

DROP TABLE IF EXISTS `packages`;;

DROP TABLE IF EXISTS `notifications`;;

DROP TABLE IF EXISTS `roles`;;;

DROP TABLE IF EXISTS `default_settings`;;

DROP TABLE IF EXISTS `coupon_utilizations`;;

DROP TABLE IF EXISTS `discounts`;;

DROP TABLE IF EXISTS `referrals`;;

DROP TABLE IF EXISTS `fine_setup`;;

DROP TABLE IF EXISTS `book_issue_rules`;;

DROP TABLE IF EXISTS `book_sub_categories`;;

DROP TABLE IF EXISTS `book_categories`;;

DROP TABLE IF EXISTS `library_transactions`;;

DROP TABLE IF EXISTS `books`;;

DROP TABLE IF EXISTS `expenses`;;

DROP TABLE IF EXISTS `payment_receipts`;;

DROP TABLE IF EXISTS `staff_leave_applications`;;

DROP TABLE IF EXISTS `staff_daily_attendance`;;

DROP TABLE IF EXISTS `student_subscriptions`;;;

DROP TABLE IF EXISTS `tables`;;

DROP TABLE IF EXISTS `meeting_hall_reservations`;;

DROP TABLE IF EXISTS `table_reservations`;;

DROP TABLE IF EXISTS `student_lockers`;;

DROP TABLE IF EXISTS `coupon_codes`;;

DROP TABLE IF EXISTS `invoices`;;;;

DROP TABLE IF EXISTS `lockers`;;

DROP TABLE IF EXISTS `locker_types`;;

DROP TABLE IF EXISTS `rooms`;;

DROP TABLE IF EXISTS `room_types`;;


DROP TABLE IF EXISTS `companies`;;


SELECT CONSTRAINT_NAME as constraint_name,CONSTRAINT_NAME as constraintName,CONSTRAINT_SCHEMA as constraintSchema,CONSTRAINT_SCHEMA as constraintCatalog,TABLE_NAME as tableName,TABLE_SCHEMA as tableSchema,TABLE_SCHEMA as tableCatalog,COLUMN_NAME as columnName,REFERENCED_TABLE_SCHEMA as referencedTableSchema,REFERENCED_TABLE_SCHEMA as referencedTableCatalog,REFERENCED_TABLE_NAME as referencedTableName,REFERENCED_COLUMN_NAME as referencedColumnName FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE where TABLE_NAME = 'room_types' AND CONSTRAINT_NAME!='PRIMARY' AND CONSTRAINT_SCHEMA='dummy' AND REFERENCED_TABLE_NAME IS NOT NULL;;

SELECT CONSTRAINT_NAME as constraint_name,CONSTRAINT_NAME as constraintName,CONSTRAINT_SCHEMA as constraintSchema,CONSTRAINT_SCHEMA as constraintCatalog,TABLE_NAME as tableName,TABLE_SCHEMA as tableSchema,TABLE_SCHEMA as tableCatalog,COLUMN_NAME as columnName,REFERENCED_TABLE_SCHEMA as referencedTableSchema,REFERENCED_TABLE_SCHEMA as referencedTableCatalog,REFERENCED_TABLE_NAME as referencedTableName,REFERENCED_COLUMN_NAME as referencedColumnName FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE where TABLE_NAME = 'rooms' AND CONSTRAINT_NAME!='PRIMARY' AND CONSTRAINT_SCHEMA='dummy' AND REFERENCED_TABLE_NAME IS NOT NULL;;

SELECT CONSTRAINT_NAME as constraint_name,CONSTRAINT_NAME as constraintName,CONSTRAINT_SCHEMA as constraintSchema,CONSTRAINT_SCHEMA as constraintCatalog,TABLE_NAME as tableName,TABLE_SCHEMA as tableSchema,TABLE_SCHEMA as tableCatalog,COLUMN_NAME as columnName,REFERENCED_TABLE_SCHEMA as referencedTableSchema,REFERENCED_TABLE_SCHEMA as referencedTableCatalog,REFERENCED_TABLE_NAME as referencedTableName,REFERENCED_COLUMN_NAME as referencedColumnName FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE where TABLE_NAME = 'locker_types' AND CONSTRAINT_NAME!='PRIMARY' AND CONSTRAINT_SCHEMA='dummy' AND REFERENCED_TABLE_NAME IS NOT NULL;;

SELECT CONSTRAINT_NAME as constraint_name,CONSTRAINT_NAME as constraintName,CONSTRAINT_SCHEMA as constraintSchema,CONSTRAINT_SCHEMA as constraintCatalog,TABLE_NAME as tableName,TABLE_SCHEMA as tableSchema,TABLE_SCHEMA as tableCatalog,COLUMN_NAME as columnName,REFERENCED_TABLE_SCHEMA as referencedTableSchema,REFERENCED_TABLE_SCHEMA as referencedTableCatalog,REFERENCED_TABLE_NAME as referencedTableName,REFERENCED_COLUMN_NAME as referencedColumnName FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE where TABLE_NAME = 'lockers' AND CONSTRAINT_NAME!='PRIMARY' AND CONSTRAINT_SCHEMA='dummy' AND REFERENCED_TABLE_NAME IS NOT NULL;;

SELECT CONSTRAINT_NAME as constraint_name,CONSTRAINT_NAME as constraintName,CONSTRAINT_SCHEMA as constraintSchema,CONSTRAINT_SCHEMA as constraintCatalog,TABLE_NAME as tableName,TABLE_SCHEMA as tableSchema,TABLE_SCHEMA as tableCatalog,COLUMN_NAME as columnName,REFERENCED_TABLE_SCHEMA as referencedTableSchema,REFERENCED_TABLE_SCHEMA as referencedTableCatalog,REFERENCED_TABLE_NAME as referencedTableName,REFERENCED_COLUMN_NAME as referencedColumnName FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE where TABLE_NAME = 'app_users' AND CONSTRAINT_NAME!='PRIMARY' AND CONSTRAINT_SCHEMA='dummy' AND REFERENCED_TABLE_NAME IS NOT NULL;;

SELECT CONSTRAINT_NAME as constraint_name,CONSTRAINT_NAME as constraintName,CONSTRAINT_SCHEMA as constraintSchema,CONSTRAINT_SCHEMA as constraintCatalog,TABLE_NAME as tableName,TABLE_SCHEMA as tableSchema,TABLE_SCHEMA as tableCatalog,COLUMN_NAME as columnName,REFERENCED_TABLE_SCHEMA as referencedTableSchema,REFERENCED_TABLE_SCHEMA as referencedTableCatalog,REFERENCED_TABLE_NAME as referencedTableName,REFERENCED_COLUMN_NAME as referencedColumnName FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE where TABLE_NAME = 'staff' AND CONSTRAINT_NAME!='PRIMARY' AND CONSTRAINT_SCHEMA='dummy' AND REFERENCED_TABLE_NAME IS NOT NULL;;

SELECT CONSTRAINT_NAME as constraint_name,CONSTRAINT_NAME as constraintName,CONSTRAINT_SCHEMA as constraintSchema,CONSTRAINT_SCHEMA as constraintCatalog,TABLE_NAME as tableName,TABLE_SCHEMA as tableSchema,TABLE_SCHEMA as tableCatalog,COLUMN_NAME as columnName,REFERENCED_TABLE_SCHEMA as referencedTableSchema,REFERENCED_TABLE_SCHEMA as referencedTableCatalog,REFERENCED_TABLE_NAME as referencedTableName,REFERENCED_COLUMN_NAME as referencedColumnName FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE where TABLE_NAME = 'invoices' AND CONSTRAINT_NAME!='PRIMARY' AND CONSTRAINT_SCHEMA='dummy' AND REFERENCED_TABLE_NAME IS NOT NULL;;

SELECT CONSTRAINT_NAME as constraint_name,CONSTRAINT_NAME as constraintName,CONSTRAINT_SCHEMA as constraintSchema,CONSTRAINT_SCHEMA as constraintCatalog,TABLE_NAME as tableName,TABLE_SCHEMA as tableSchema,TABLE_SCHEMA as tableCatalog,COLUMN_NAME as columnName,REFERENCED_TABLE_SCHEMA as referencedTableSchema,REFERENCED_TABLE_SCHEMA as referencedTableCatalog,REFERENCED_TABLE_NAME as referencedTableName,REFERENCED_COLUMN_NAME as referencedColumnName FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE where TABLE_NAME = 'coupon_codes' AND CONSTRAINT_NAME!='PRIMARY' AND CONSTRAINT_SCHEMA='dummy' AND REFERENCED_TABLE_NAME IS NOT NULL;;

SELECT CONSTRAINT_NAME as constraint_name,CONSTRAINT_NAME as constraintName,CONSTRAINT_SCHEMA as constraintSchema,CONSTRAINT_SCHEMA as constraintCatalog,TABLE_NAME as tableName,TABLE_SCHEMA as tableSchema,TABLE_SCHEMA as tableCatalog,COLUMN_NAME as columnName,REFERENCED_TABLE_SCHEMA as referencedTableSchema,REFERENCED_TABLE_SCHEMA as referencedTableCatalog,REFERENCED_TABLE_NAME as referencedTableName,REFERENCED_COLUMN_NAME as referencedColumnName FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE where TABLE_NAME = 'student_lockers' AND CONSTRAINT_NAME!='PRIMARY' AND CONSTRAINT_SCHEMA='dummy' AND REFERENCED_TABLE_NAME IS NOT NULL;;

SELECT CONSTRAINT_NAME as constraint_name,CONSTRAINT_NAME as constraintName,CONSTRAINT_SCHEMA as constraintSchema,CONSTRAINT_SCHEMA as constraintCatalog,TABLE_NAME as tableName,TABLE_SCHEMA as tableSchema,TABLE_SCHEMA as tableCatalog,COLUMN_NAME as columnName,REFERENCED_TABLE_SCHEMA as referencedTableSchema,REFERENCED_TABLE_SCHEMA as referencedTableCatalog,REFERENCED_TABLE_NAME as referencedTableName,REFERENCED_COLUMN_NAME as referencedColumnName FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE where TABLE_NAME = 'table_reservations' AND CONSTRAINT_NAME!='PRIMARY' AND CONSTRAINT_SCHEMA='dummy' AND REFERENCED_TABLE_NAME IS NOT NULL;;

SELECT CONSTRAINT_NAME as constraint_name,CONSTRAINT_NAME as constraintName,CONSTRAINT_SCHEMA as constraintSchema,CONSTRAINT_SCHEMA as constraintCatalog,TABLE_NAME as tableName,TABLE_SCHEMA as tableSchema,TABLE_SCHEMA as tableCatalog,COLUMN_NAME as columnName,REFERENCED_TABLE_SCHEMA as referencedTableSchema,REFERENCED_TABLE_SCHEMA as referencedTableCatalog,REFERENCED_TABLE_NAME as referencedTableName,REFERENCED_COLUMN_NAME as referencedColumnName FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE where TABLE_NAME = 'meeting_hall_reservations' AND CONSTRAINT_NAME!='PRIMARY' AND CONSTRAINT_SCHEMA='dummy' AND REFERENCED_TABLE_NAME IS NOT NULL;;

SELECT CONSTRAINT_NAME as constraint_name,CONSTRAINT_NAME as constraintName,CONSTRAINT_SCHEMA as constraintSchema,CONSTRAINT_SCHEMA as constraintCatalog,TABLE_NAME as tableName,TABLE_SCHEMA as tableSchema,TABLE_SCHEMA as tableCatalog,COLUMN_NAME as columnName,REFERENCED_TABLE_SCHEMA as referencedTableSchema,REFERENCED_TABLE_SCHEMA as referencedTableCatalog,REFERENCED_TABLE_NAME as referencedTableName,REFERENCED_COLUMN_NAME as referencedColumnName FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE where TABLE_NAME = 'tables' AND CONSTRAINT_NAME!='PRIMARY' AND CONSTRAINT_SCHEMA='dummy' AND REFERENCED_TABLE_NAME IS NOT NULL;;

SELECT CONSTRAINT_NAME as constraint_name,CONSTRAINT_NAME as constraintName,CONSTRAINT_SCHEMA as constraintSchema,CONSTRAINT_SCHEMA as constraintCatalog,TABLE_NAME as tableName,TABLE_SCHEMA as tableSchema,TABLE_SCHEMA as tableCatalog,COLUMN_NAME as columnName,REFERENCED_TABLE_SCHEMA as referencedTableSchema,REFERENCED_TABLE_SCHEMA as referencedTableCatalog,REFERENCED_TABLE_NAME as referencedTableName,REFERENCED_COLUMN_NAME as referencedColumnName FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE where TABLE_NAME = 'students' AND CONSTRAINT_NAME!='PRIMARY' AND CONSTRAINT_SCHEMA='dummy' AND REFERENCED_TABLE_NAME IS NOT NULL;;

SELECT CONSTRAINT_NAME as constraint_name,CONSTRAINT_NAME as constraintName,CONSTRAINT_SCHEMA as constraintSchema,CONSTRAINT_SCHEMA as constraintCatalog,TABLE_NAME as tableName,TABLE_SCHEMA as tableSchema,TABLE_SCHEMA as tableCatalog,COLUMN_NAME as columnName,REFERENCED_TABLE_SCHEMA as referencedTableSchema,REFERENCED_TABLE_SCHEMA as referencedTableCatalog,REFERENCED_TABLE_NAME as referencedTableName,REFERENCED_COLUMN_NAME as referencedColumnName FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE where TABLE_NAME = 'student_subscriptions' AND CONSTRAINT_NAME!='PRIMARY' AND CONSTRAINT_SCHEMA='dummy' AND REFERENCED_TABLE_NAME IS NOT NULL;;

SELECT CONSTRAINT_NAME as constraint_name,CONSTRAINT_NAME as constraintName,CONSTRAINT_SCHEMA as constraintSchema,CONSTRAINT_SCHEMA as constraintCatalog,TABLE_NAME as tableName,TABLE_SCHEMA as tableSchema,TABLE_SCHEMA as tableCatalog,COLUMN_NAME as columnName,REFERENCED_TABLE_SCHEMA as referencedTableSchema,REFERENCED_TABLE_SCHEMA as referencedTableCatalog,REFERENCED_TABLE_NAME as referencedTableName,REFERENCED_COLUMN_NAME as referencedColumnName FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE where TABLE_NAME = 'staff_daily_attendance' AND CONSTRAINT_NAME!='PRIMARY' AND CONSTRAINT_SCHEMA='dummy' AND REFERENCED_TABLE_NAME IS NOT NULL;;

SELECT CONSTRAINT_NAME as constraint_name,CONSTRAINT_NAME as constraintName,CONSTRAINT_SCHEMA as constraintSchema,CONSTRAINT_SCHEMA as constraintCatalog,TABLE_NAME as tableName,TABLE_SCHEMA as tableSchema,TABLE_SCHEMA as tableCatalog,COLUMN_NAME as columnName,REFERENCED_TABLE_SCHEMA as referencedTableSchema,REFERENCED_TABLE_SCHEMA as referencedTableCatalog,REFERENCED_TABLE_NAME as referencedTableName,REFERENCED_COLUMN_NAME as referencedColumnName FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE where TABLE_NAME = 'staff_leave_applications' AND CONSTRAINT_NAME!='PRIMARY' AND CONSTRAINT_SCHEMA='dummy' AND REFERENCED_TABLE_NAME IS NOT NULL;;

SELECT CONSTRAINT_NAME as constraint_name,CONSTRAINT_NAME as constraintName,CONSTRAINT_SCHEMA as constraintSchema,CONSTRAINT_SCHEMA as constraintCatalog,TABLE_NAME as tableName,TABLE_SCHEMA as tableSchema,TABLE_SCHEMA as tableCatalog,COLUMN_NAME as columnName,REFERENCED_TABLE_SCHEMA as referencedTableSchema,REFERENCED_TABLE_SCHEMA as referencedTableCatalog,REFERENCED_TABLE_NAME as referencedTableName,REFERENCED_COLUMN_NAME as referencedColumnName FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE where TABLE_NAME = 'payment_receipts' AND CONSTRAINT_NAME!='PRIMARY' AND CONSTRAINT_SCHEMA='dummy' AND REFERENCED_TABLE_NAME IS NOT NULL;;

SELECT CONSTRAINT_NAME as constraint_name,CONSTRAINT_NAME as constraintName,CONSTRAINT_SCHEMA as constraintSchema,CONSTRAINT_SCHEMA as constraintCatalog,TABLE_NAME as tableName,TABLE_SCHEMA as tableSchema,TABLE_SCHEMA as tableCatalog,COLUMN_NAME as columnName,REFERENCED_TABLE_SCHEMA as referencedTableSchema,REFERENCED_TABLE_SCHEMA as referencedTableCatalog,REFERENCED_TABLE_NAME as referencedTableName,REFERENCED_COLUMN_NAME as referencedColumnName FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE where TABLE_NAME = 'expenses' AND CONSTRAINT_NAME!='PRIMARY' AND CONSTRAINT_SCHEMA='dummy' AND REFERENCED_TABLE_NAME IS NOT NULL;;

SELECT CONSTRAINT_NAME as constraint_name,CONSTRAINT_NAME as constraintName,CONSTRAINT_SCHEMA as constraintSchema,CONSTRAINT_SCHEMA as constraintCatalog,TABLE_NAME as tableName,TABLE_SCHEMA as tableSchema,TABLE_SCHEMA as tableCatalog,COLUMN_NAME as columnName,REFERENCED_TABLE_SCHEMA as referencedTableSchema,REFERENCED_TABLE_SCHEMA as referencedTableCatalog,REFERENCED_TABLE_NAME as referencedTableName,REFERENCED_COLUMN_NAME as referencedColumnName FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE where TABLE_NAME = 'books' AND CONSTRAINT_NAME!='PRIMARY' AND CONSTRAINT_SCHEMA='dummy' AND REFERENCED_TABLE_NAME IS NOT NULL;;

SELECT CONSTRAINT_NAME as constraint_name,CONSTRAINT_NAME as constraintName,CONSTRAINT_SCHEMA as constraintSchema,CONSTRAINT_SCHEMA as constraintCatalog,TABLE_NAME as tableName,TABLE_SCHEMA as tableSchema,TABLE_SCHEMA as tableCatalog,COLUMN_NAME as columnName,REFERENCED_TABLE_SCHEMA as referencedTableSchema,REFERENCED_TABLE_SCHEMA as referencedTableCatalog,REFERENCED_TABLE_NAME as referencedTableName,REFERENCED_COLUMN_NAME as referencedColumnName FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE where TABLE_NAME = 'library_transactions' AND CONSTRAINT_NAME!='PRIMARY' AND CONSTRAINT_SCHEMA='dummy' AND REFERENCED_TABLE_NAME IS NOT NULL;;

SELECT CONSTRAINT_NAME as constraint_name,CONSTRAINT_NAME as constraintName,CONSTRAINT_SCHEMA as constraintSchema,CONSTRAINT_SCHEMA as constraintCatalog,TABLE_NAME as tableName,TABLE_SCHEMA as tableSchema,TABLE_SCHEMA as tableCatalog,COLUMN_NAME as columnName,REFERENCED_TABLE_SCHEMA as referencedTableSchema,REFERENCED_TABLE_SCHEMA as referencedTableCatalog,REFERENCED_TABLE_NAME as referencedTableName,REFERENCED_COLUMN_NAME as referencedColumnName FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE where TABLE_NAME = 'book_categories' AND CONSTRAINT_NAME!='PRIMARY' AND CONSTRAINT_SCHEMA='dummy' AND REFERENCED_TABLE_NAME IS NOT NULL;;

SELECT CONSTRAINT_NAME as constraint_name,CONSTRAINT_NAME as constraintName,CONSTRAINT_SCHEMA as constraintSchema,CONSTRAINT_SCHEMA as constraintCatalog,TABLE_NAME as tableName,TABLE_SCHEMA as tableSchema,TABLE_SCHEMA as tableCatalog,COLUMN_NAME as columnName,REFERENCED_TABLE_SCHEMA as referencedTableSchema,REFERENCED_TABLE_SCHEMA as referencedTableCatalog,REFERENCED_TABLE_NAME as referencedTableName,REFERENCED_COLUMN_NAME as referencedColumnName FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE where TABLE_NAME = 'book_sub_categories' AND CONSTRAINT_NAME!='PRIMARY' AND CONSTRAINT_SCHEMA='dummy' AND REFERENCED_TABLE_NAME IS NOT NULL;;

SELECT CONSTRAINT_NAME as constraint_name,CONSTRAINT_NAME as constraintName,CONSTRAINT_SCHEMA as constraintSchema,CONSTRAINT_SCHEMA as constraintCatalog,TABLE_NAME as tableName,TABLE_SCHEMA as tableSchema,TABLE_SCHEMA as tableCatalog,COLUMN_NAME as columnName,REFERENCED_TABLE_SCHEMA as referencedTableSchema,REFERENCED_TABLE_SCHEMA as referencedTableCatalog,REFERENCED_TABLE_NAME as referencedTableName,REFERENCED_COLUMN_NAME as referencedColumnName FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE where TABLE_NAME = 'book_issue_rules' AND CONSTRAINT_NAME!='PRIMARY' AND CONSTRAINT_SCHEMA='dummy' AND REFERENCED_TABLE_NAME IS NOT NULL;;

SELECT CONSTRAINT_NAME as constraint_name,CONSTRAINT_NAME as constraintName,CONSTRAINT_SCHEMA as constraintSchema,CONSTRAINT_SCHEMA as constraintCatalog,TABLE_NAME as tableName,TABLE_SCHEMA as tableSchema,TABLE_SCHEMA as tableCatalog,COLUMN_NAME as columnName,REFERENCED_TABLE_SCHEMA as referencedTableSchema,REFERENCED_TABLE_SCHEMA as referencedTableCatalog,REFERENCED_TABLE_NAME as referencedTableName,REFERENCED_COLUMN_NAME as referencedColumnName FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE where TABLE_NAME = 'fine_setup' AND CONSTRAINT_NAME!='PRIMARY' AND CONSTRAINT_SCHEMA='dummy' AND REFERENCED_TABLE_NAME IS NOT NULL;;

SELECT CONSTRAINT_NAME as constraint_name,CONSTRAINT_NAME as constraintName,CONSTRAINT_SCHEMA as constraintSchema,CONSTRAINT_SCHEMA as constraintCatalog,TABLE_NAME as tableName,TABLE_SCHEMA as tableSchema,TABLE_SCHEMA as tableCatalog,COLUMN_NAME as columnName,REFERENCED_TABLE_SCHEMA as referencedTableSchema,REFERENCED_TABLE_SCHEMA as referencedTableCatalog,REFERENCED_TABLE_NAME as referencedTableName,REFERENCED_COLUMN_NAME as referencedColumnName FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE where TABLE_NAME = 'referrals' AND CONSTRAINT_NAME!='PRIMARY' AND CONSTRAINT_SCHEMA='dummy' AND REFERENCED_TABLE_NAME IS NOT NULL;;

SELECT CONSTRAINT_NAME as constraint_name,CONSTRAINT_NAME as constraintName,CONSTRAINT_SCHEMA as constraintSchema,CONSTRAINT_SCHEMA as constraintCatalog,TABLE_NAME as tableName,TABLE_SCHEMA as tableSchema,TABLE_SCHEMA as tableCatalog,COLUMN_NAME as columnName,REFERENCED_TABLE_SCHEMA as referencedTableSchema,REFERENCED_TABLE_SCHEMA as referencedTableCatalog,REFERENCED_TABLE_NAME as referencedTableName,REFERENCED_COLUMN_NAME as referencedColumnName FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE where TABLE_NAME = 'discounts' AND CONSTRAINT_NAME!='PRIMARY' AND CONSTRAINT_SCHEMA='dummy' AND REFERENCED_TABLE_NAME IS NOT NULL;;

SELECT CONSTRAINT_NAME as constraint_name,CONSTRAINT_NAME as constraintName,CONSTRAINT_SCHEMA as constraintSchema,CONSTRAINT_SCHEMA as constraintCatalog,TABLE_NAME as tableName,TABLE_SCHEMA as tableSchema,TABLE_SCHEMA as tableCatalog,COLUMN_NAME as columnName,REFERENCED_TABLE_SCHEMA as referencedTableSchema,REFERENCED_TABLE_SCHEMA as referencedTableCatalog,REFERENCED_TABLE_NAME as referencedTableName,REFERENCED_COLUMN_NAME as referencedColumnName FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE where TABLE_NAME = 'coupon_utilizations' AND CONSTRAINT_NAME!='PRIMARY' AND CONSTRAINT_SCHEMA='dummy' AND REFERENCED_TABLE_NAME IS NOT NULL;;

SELECT CONSTRAINT_NAME as constraint_name,CONSTRAINT_NAME as constraintName,CONSTRAINT_SCHEMA as constraintSchema,CONSTRAINT_SCHEMA as constraintCatalog,TABLE_NAME as tableName,TABLE_SCHEMA as tableSchema,TABLE_SCHEMA as tableCatalog,COLUMN_NAME as columnName,REFERENCED_TABLE_SCHEMA as referencedTableSchema,REFERENCED_TABLE_SCHEMA as referencedTableCatalog,REFERENCED_TABLE_NAME as referencedTableName,REFERENCED_COLUMN_NAME as referencedColumnName FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE where TABLE_NAME = 'default_settings' AND CONSTRAINT_NAME!='PRIMARY' AND CONSTRAINT_SCHEMA='dummy' AND REFERENCED_TABLE_NAME IS NOT NULL;;

SELECT CONSTRAINT_NAME as constraint_name,CONSTRAINT_NAME as constraintName,CONSTRAINT_SCHEMA as constraintSchema,CONSTRAINT_SCHEMA as constraintCatalog,TABLE_NAME as tableName,TABLE_SCHEMA as tableSchema,TABLE_SCHEMA as tableCatalog,COLUMN_NAME as columnName,REFERENCED_TABLE_SCHEMA as referencedTableSchema,REFERENCED_TABLE_SCHEMA as referencedTableCatalog,REFERENCED_TABLE_NAME as referencedTableName,REFERENCED_COLUMN_NAME as referencedColumnName FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE where TABLE_NAME = 'email_settings' AND CONSTRAINT_NAME!='PRIMARY' AND CONSTRAINT_SCHEMA='dummy' AND REFERENCED_TABLE_NAME IS NOT NULL;;

SELECT CONSTRAINT_NAME as constraint_name,CONSTRAINT_NAME as constraintName,CONSTRAINT_SCHEMA as constraintSchema,CONSTRAINT_SCHEMA as constraintCatalog,TABLE_NAME as tableName,TABLE_SCHEMA as tableSchema,TABLE_SCHEMA as tableCatalog,COLUMN_NAME as columnName,REFERENCED_TABLE_SCHEMA as referencedTableSchema,REFERENCED_TABLE_SCHEMA as referencedTableCatalog,REFERENCED_TABLE_NAME as referencedTableName,REFERENCED_COLUMN_NAME as referencedColumnName FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE where TABLE_NAME = 'roles' AND CONSTRAINT_NAME!='PRIMARY' AND CONSTRAINT_SCHEMA='dummy' AND REFERENCED_TABLE_NAME IS NOT NULL;;

SELECT CONSTRAINT_NAME as constraint_name,CONSTRAINT_NAME as constraintName,CONSTRAINT_SCHEMA as constraintSchema,CONSTRAINT_SCHEMA as constraintCatalog,TABLE_NAME as tableName,TABLE_SCHEMA as tableSchema,TABLE_SCHEMA as tableCatalog,COLUMN_NAME as columnName,REFERENCED_TABLE_SCHEMA as referencedTableSchema,REFERENCED_TABLE_SCHEMA as referencedTableCatalog,REFERENCED_TABLE_NAME as referencedTableName,REFERENCED_COLUMN_NAME as referencedColumnName FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE where TABLE_NAME = 'notifications' AND CONSTRAINT_NAME!='PRIMARY' AND CONSTRAINT_SCHEMA='dummy' AND REFERENCED_TABLE_NAME IS NOT NULL;;

SELECT CONSTRAINT_NAME as constraint_name,CONSTRAINT_NAME as constraintName,CONSTRAINT_SCHEMA as constraintSchema,CONSTRAINT_SCHEMA as constraintCatalog,TABLE_NAME as tableName,TABLE_SCHEMA as tableSchema,TABLE_SCHEMA as tableCatalog,COLUMN_NAME as columnName,REFERENCED_TABLE_SCHEMA as referencedTableSchema,REFERENCED_TABLE_SCHEMA as referencedTableCatalog,REFERENCED_TABLE_NAME as referencedTableName,REFERENCED_COLUMN_NAME as referencedColumnName FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE where TABLE_NAME = 'packages' AND CONSTRAINT_NAME!='PRIMARY' AND CONSTRAINT_SCHEMA='dummy' AND REFERENCED_TABLE_NAME IS NOT NULL;;

SELECT CONSTRAINT_NAME as constraint_name,CONSTRAINT_NAME as constraintName,CONSTRAINT_SCHEMA as constraintSchema,CONSTRAINT_SCHEMA as constraintCatalog,TABLE_NAME as tableName,TABLE_SCHEMA as tableSchema,TABLE_SCHEMA as tableCatalog,COLUMN_NAME as columnName,REFERENCED_TABLE_SCHEMA as referencedTableSchema,REFERENCED_TABLE_SCHEMA as referencedTableCatalog,REFERENCED_TABLE_NAME as referencedTableName,REFERENCED_COLUMN_NAME as referencedColumnName FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE where TABLE_NAME = 'backup_history' AND CONSTRAINT_NAME!='PRIMARY' AND CONSTRAINT_SCHEMA='dummy' AND REFERENCED_TABLE_NAME IS NOT NULL;;

SELECT CONSTRAINT_NAME as constraint_name,CONSTRAINT_NAME as constraintName,CONSTRAINT_SCHEMA as constraintSchema,CONSTRAINT_SCHEMA as constraintCatalog,TABLE_NAME as tableName,TABLE_SCHEMA as tableSchema,TABLE_SCHEMA as tableCatalog,COLUMN_NAME as columnName,REFERENCED_TABLE_SCHEMA as referencedTableSchema,REFERENCED_TABLE_SCHEMA as referencedTableCatalog,REFERENCED_TABLE_NAME as referencedTableName,REFERENCED_COLUMN_NAME as referencedColumnName FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE where TABLE_NAME = 'Taxes' AND CONSTRAINT_NAME!='PRIMARY' AND CONSTRAINT_SCHEMA='dummy' AND REFERENCED_TABLE_NAME IS NOT NULL;;

DROP TABLE IF EXISTS `companies`;;


DROP TABLE IF EXISTS `room_types`;;

DROP TABLE IF EXISTS `rooms`;;

DROP TABLE IF EXISTS `locker_types`;;

DROP TABLE IF EXISTS `lockers`;;;;

DROP TABLE IF EXISTS `invoices`;;

DROP TABLE IF EXISTS `coupon_codes`;;

DROP TABLE IF EXISTS `student_lockers`;;

DROP TABLE IF EXISTS `table_reservations`;;

DROP TABLE IF EXISTS `meeting_hall_reservations`;;

DROP TABLE IF EXISTS `tables`;;;

DROP TABLE IF EXISTS `student_subscriptions`;;

DROP TABLE IF EXISTS `staff_daily_attendance`;;

DROP TABLE IF EXISTS `staff_leave_applications`;;

DROP TABLE IF EXISTS `payment_receipts`;;

DROP TABLE IF EXISTS `expenses`;;

DROP TABLE IF EXISTS `books`;;

DROP TABLE IF EXISTS `library_transactions`;;

DROP TABLE IF EXISTS `book_categories`;;

DROP TABLE IF EXISTS `book_sub_categories`;;

DROP TABLE IF EXISTS `book_issue_rules`;;

DROP TABLE IF EXISTS `fine_setup`;;

DROP TABLE IF EXISTS `referrals`;;

DROP TABLE IF EXISTS `discounts`;;

DROP TABLE IF EXISTS `coupon_utilizations`;;

DROP TABLE IF EXISTS `default_settings`;;;

DROP TABLE IF EXISTS `roles`;;

DROP TABLE IF EXISTS `notifications`;;

DROP TABLE IF EXISTS `packages`;;

DROP TABLE IF EXISTS `backup_history`;;

DROP TABLE IF EXISTS `Taxes`;;

DROP TABLE IF EXISTS `companies`;;

CREATE TABLE IF NOT EXISTS `companies` (`id` BIGINT UNSIGNED auto_increment , `companyName` VARCHAR(255) NOT NULL, `companyEmail` VARCHAR(255) DEFAULT '', `phoneNumber` VARCHAR(255) DEFAULT '', `mobileNumber` VARCHAR(255) DEFAULT '', `website` VARCHAR(255) DEFAULT '', `logoName` VARCHAR(255) DEFAULT '', `logoDataUrl` LONGTEXT, `signatureName` VARCHAR(255) DEFAULT '', `signatureDataUrl` LONGTEXT, `address` TEXT, `dateFormat` VARCHAR(255) DEFAULT 'MM/dd/yyyy', `timeZone` VARCHAR(255) DEFAULT 'UTC', `currency` VARCHAR(255) DEFAULT 'USD', `costingMethod` VARCHAR(255) DEFAULT 'Average Cost', `defaultCustomer` VARCHAR(255) DEFAULT 'Walk In', `salesAccount` VARCHAR(255) DEFAULT '', `purchaseAccount` VARCHAR(255) DEFAULT '', `status` VARCHAR(255) DEFAULT 'Active', `created_at` DATETIME NOT NULL, `updated_at` DATETIME NOT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB;;

SHOW INDEX FROM `companies`;




DROP TABLE IF EXISTS `room_types`;;

CREATE TABLE IF NOT EXISTS `room_types` (`id` BIGINT UNSIGNED auto_increment , `companyId` BIGINT UNSIGNED, `name` VARCHAR(255) NOT NULL, `status` VARCHAR(255) DEFAULT 'Active', `isUsed` TINYINT(1) DEFAULT false, `created_at` DATETIME NOT NULL, `updated_at` DATETIME NOT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB;;

SHOW INDEX FROM `room_types`;

DROP TABLE IF EXISTS `rooms`;;

CREATE TABLE IF NOT EXISTS `rooms` (`id` BIGINT UNSIGNED auto_increment , `companyId` BIGINT UNSIGNED, `name` VARCHAR(255) NOT NULL, `roomType` VARCHAR(255) NOT NULL, `room` VARCHAR(255), `category` VARCHAR(255) DEFAULT 'Meeting Hall', `status` VARCHAR(255) DEFAULT 'Active', `isUsed` TINYINT(1) DEFAULT false, `created_at` DATETIME NOT NULL, `updated_at` DATETIME NOT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB;;

SHOW INDEX FROM `rooms`;

DROP TABLE IF EXISTS `locker_types`;;

CREATE TABLE IF NOT EXISTS `locker_types` (`id` BIGINT UNSIGNED auto_increment , `companyId` BIGINT UNSIGNED, `name` VARCHAR(255) NOT NULL, `status` VARCHAR(255) DEFAULT 'Active', `isUsed` TINYINT(1) DEFAULT false, `created_at` DATETIME NOT NULL, `updated_at` DATETIME NOT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB;;

SHOW INDEX FROM `locker_types`;

DROP TABLE IF EXISTS `lockers`;;

CREATE TABLE IF NOT EXISTS `lockers` (`id` BIGINT UNSIGNED auto_increment , `companyId` BIGINT UNSIGNED, `name` VARCHAR(255) NOT NULL, `lockerType` VARCHAR(255) NOT NULL, `status` VARCHAR(255) DEFAULT 'Active', `isUsed` TINYINT(1) DEFAULT false, `created_at` DATETIME NOT NULL, `updated_at` DATETIME NOT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB;;

SHOW INDEX FROM `lockers`;;;;;

DROP TABLE IF EXISTS `invoices`;;

CREATE TABLE IF NOT EXISTS `invoices` (`id` BIGINT UNSIGNED auto_increment , `companyId` BIGINT UNSIGNED, `invoiceNo` VARCHAR(255) NOT NULL, `billingType` VARCHAR(255) DEFAULT 'Subscription', `referenceId` BIGINT UNSIGNED, `studentCode` VARCHAR(255) DEFAULT '', `studentName` VARCHAR(255) DEFAULT '', `referenceName` VARCHAR(255) DEFAULT '', `qty` VARCHAR(255) DEFAULT '1', `unitPrice` VARCHAR(255) DEFAULT '0', `subtotal` VARCHAR(255) DEFAULT '0', `amount` VARCHAR(255) DEFAULT '0', `paymentStatus` VARCHAR(255) DEFAULT 'Unpaid', `invoiceDate` VARCHAR(255) DEFAULT '', `referralCode` VARCHAR(255) DEFAULT '', `referralDiscount` VARCHAR(255) DEFAULT '0', `couponCode` VARCHAR(255) DEFAULT '', `couponDiscount` VARCHAR(255) DEFAULT '0', `manualDiscountLabel` VARCHAR(255) DEFAULT 'Discount', `manualDiscount` VARCHAR(255) DEFAULT '0', `taxRate` VARCHAR(255) DEFAULT '0', `taxAmount` VARCHAR(255) DEFAULT '0', `dueDate` VARCHAR(255) DEFAULT '', `notes` TEXT, `advance` VARCHAR(255) DEFAULT '0', `cabin` VARCHAR(255) DEFAULT '', `isUsed` TINYINT(1) DEFAULT false, `created_at` DATETIME NOT NULL, `updated_at` DATETIME NOT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB;;

SHOW INDEX FROM `invoices`;

DROP TABLE IF EXISTS `coupon_codes`;;

CREATE TABLE IF NOT EXISTS `coupon_codes` (`id` BIGINT UNSIGNED auto_increment , `companyId` BIGINT UNSIGNED, `name` VARCHAR(255) NOT NULL, `couponCode` VARCHAR(255) NOT NULL, `appliesTo` VARCHAR(255) DEFAULT 'All', `discountType` VARCHAR(255) DEFAULT 'Amount', `discountValue` VARCHAR(255) DEFAULT '0', `minAmount` VARCHAR(255) DEFAULT '0', `maxUsage` VARCHAR(255) DEFAULT '', `expiryDate` VARCHAR(255) DEFAULT '', `status` VARCHAR(255) DEFAULT 'Active', `isUsed` TINYINT(1) DEFAULT false, `created_at` DATETIME NOT NULL, `updated_at` DATETIME NOT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB;;

SHOW INDEX FROM `coupon_codes`;

DROP TABLE IF EXISTS `student_lockers`;;

CREATE TABLE IF NOT EXISTS `student_lockers` (`id` BIGINT UNSIGNED auto_increment , `companyId` BIGINT UNSIGNED, `studentCode` VARCHAR(255) NOT NULL DEFAULT '', `studentName` VARCHAR(255) NOT NULL DEFAULT '', `lockerName` VARCHAR(255) NOT NULL DEFAULT '', `lockerType` VARCHAR(255) NOT NULL DEFAULT '', `reservationDate` VARCHAR(255) NOT NULL DEFAULT '', `reservationTime` VARCHAR(255) NOT NULL DEFAULT '', `status` VARCHAR(255) NOT NULL DEFAULT 'Active', `isUsed` TINYINT(1) NOT NULL DEFAULT false, `created_at` DATETIME NOT NULL, `updated_at` DATETIME NOT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB;;

SHOW INDEX FROM `student_lockers`;

DROP TABLE IF EXISTS `table_reservations`;;

CREATE TABLE IF NOT EXISTS `table_reservations` (`id` BIGINT UNSIGNED auto_increment , `companyId` BIGINT UNSIGNED, `studentCode` VARCHAR(255) NOT NULL DEFAULT '', `studentName` VARCHAR(255) NOT NULL DEFAULT '', `tableName` VARCHAR(255) NOT NULL DEFAULT '', `room` VARCHAR(255) NOT NULL DEFAULT '', `roomType` VARCHAR(255) NOT NULL DEFAULT '', `reservationDate` VARCHAR(255) NOT NULL DEFAULT '', `reservationTime` VARCHAR(255) NOT NULL DEFAULT '', `status` VARCHAR(255) NOT NULL DEFAULT 'Active', `isUsed` TINYINT(1) NOT NULL DEFAULT false, `created_at` DATETIME NOT NULL, `updated_at` DATETIME NOT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB;;

SHOW INDEX FROM `table_reservations`;

DROP TABLE IF EXISTS `meeting_hall_reservations`;;

CREATE TABLE IF NOT EXISTS `meeting_hall_reservations` (`id` BIGINT UNSIGNED auto_increment , `companyId` BIGINT UNSIGNED, `studentCode` VARCHAR(255) NOT NULL DEFAULT '', `studentName` VARCHAR(255) NOT NULL DEFAULT '', `hallName` VARCHAR(255) NOT NULL DEFAULT '', `reservationDate` VARCHAR(255) NOT NULL DEFAULT '', `reservationTime` VARCHAR(255) NOT NULL DEFAULT '', `timeSlot` VARCHAR(255) NOT NULL DEFAULT '', `status` VARCHAR(255) NOT NULL DEFAULT 'Active', `isUsed` TINYINT(1) NOT NULL DEFAULT false, `created_at` DATETIME NOT NULL, `updated_at` DATETIME NOT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB;;

SHOW INDEX FROM `meeting_hall_reservations`;

DROP TABLE IF EXISTS `tables`;;

CREATE TABLE IF NOT EXISTS `tables` (`id` BIGINT UNSIGNED auto_increment , `companyId` BIGINT UNSIGNED, `name` VARCHAR(255) NOT NULL, `qrCode` VARCHAR(255) NOT NULL DEFAULT '', `room` VARCHAR(255) NOT NULL DEFAULT '', `roomType` VARCHAR(255) NOT NULL DEFAULT '', `status` VARCHAR(255) NOT NULL DEFAULT 'Active', `isUsed` TINYINT(1) NOT NULL DEFAULT false, `created_at` DATETIME NOT NULL, `updated_at` DATETIME NOT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB;;

SHOW INDEX FROM `tables`;;;

DROP TABLE IF EXISTS `student_subscriptions`;;

CREATE TABLE IF NOT EXISTS `student_subscriptions` (`id` BIGINT UNSIGNED auto_increment , `companyId` BIGINT UNSIGNED, `studentCode` VARCHAR(255) NOT NULL DEFAULT '', `studentName` VARCHAR(255) NOT NULL DEFAULT '', `subscriptionName` VARCHAR(255) NOT NULL DEFAULT '', `duration` VARCHAR(255) NOT NULL DEFAULT '', `startDate` VARCHAR(255) NOT NULL DEFAULT '', `endDate` VARCHAR(255) NOT NULL DEFAULT '', `status` VARCHAR(255) NOT NULL DEFAULT 'Active', `isUsed` TINYINT(1) NOT NULL DEFAULT false, `created_at` DATETIME NOT NULL, `updated_at` DATETIME NOT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB;;

SHOW INDEX FROM `student_subscriptions`;

DROP TABLE IF EXISTS `staff_daily_attendance`;;

CREATE TABLE IF NOT EXISTS `staff_daily_attendance` (`id` BIGINT UNSIGNED auto_increment , `companyId` BIGINT UNSIGNED, `date` VARCHAR(255) NOT NULL DEFAULT '', `staffCode` VARCHAR(255) NOT NULL DEFAULT '', `staffName` VARCHAR(255) NOT NULL DEFAULT '', `source` VARCHAR(255) NOT NULL DEFAULT 'Manual', `deviceId` VARCHAR(255) NOT NULL DEFAULT '', `deviceName` VARCHAR(255) NOT NULL DEFAULT '', `status` VARCHAR(255) NOT NULL DEFAULT 'Present', `checkIn` VARCHAR(255) NOT NULL DEFAULT '', `checkOut` VARCHAR(255) NOT NULL DEFAULT '', `approvalStatus` VARCHAR(255) NOT NULL DEFAULT 'Pending', `note` TEXT, `created_at` DATETIME NOT NULL, `updated_at` DATETIME NOT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB;;

SHOW INDEX FROM `staff_daily_attendance`;

DROP TABLE IF EXISTS `staff_leave_applications`;;

CREATE TABLE IF NOT EXISTS `staff_leave_applications` (`id` BIGINT UNSIGNED auto_increment , `companyId` BIGINT UNSIGNED, `staffCode` VARCHAR(255) NOT NULL DEFAULT '', `staffName` VARCHAR(255) NOT NULL DEFAULT '', `leaveType` VARCHAR(255) NOT NULL DEFAULT 'Annual Leave', `fromDate` VARCHAR(255) NOT NULL DEFAULT '', `toDate` VARCHAR(255) NOT NULL DEFAULT '', `totalDays` INTEGER UNSIGNED NOT NULL DEFAULT 0, `reason` TEXT, `status` VARCHAR(255) NOT NULL DEFAULT 'Pending', `isUsed` TINYINT(1) NOT NULL DEFAULT false, `created_at` DATETIME NOT NULL, `updated_at` DATETIME NOT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB;;

SHOW INDEX FROM `staff_leave_applications`;

DROP TABLE IF EXISTS `payment_receipts`;;

CREATE TABLE IF NOT EXISTS `payment_receipts` (`id` BIGINT UNSIGNED auto_increment , `companyId` BIGINT UNSIGNED, `receiptNo` VARCHAR(255) NOT NULL, `receiptDate` VARCHAR(255) DEFAULT '', `invoiceId` BIGINT UNSIGNED NOT NULL, `invoiceNo` VARCHAR(255) DEFAULT '', `studentCode` VARCHAR(255) DEFAULT '', `studentName` VARCHAR(255) DEFAULT '', `amount` VARCHAR(255) DEFAULT '0', `paymentMode` VARCHAR(255) DEFAULT 'Cash', `referenceNo` VARCHAR(255) DEFAULT '', `notes` TEXT, `created_at` DATETIME NOT NULL, `updated_at` DATETIME NOT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB;;

SHOW INDEX FROM `payment_receipts`;

DROP TABLE IF EXISTS `expenses`;;

CREATE TABLE IF NOT EXISTS `expenses` (`id` BIGINT UNSIGNED auto_increment , `companyId` BIGINT UNSIGNED, `expenseNo` VARCHAR(255) NOT NULL, `expenseDate` VARCHAR(255) DEFAULT '', `category` VARCHAR(255) DEFAULT 'Miscellaneous', `vendor` VARCHAR(255) DEFAULT '', `paymentMode` VARCHAR(255) DEFAULT 'Cash', `amount` VARCHAR(255) DEFAULT '0', `taxAmount` VARCHAR(255) DEFAULT '0', `status` VARCHAR(255) DEFAULT 'Pending', `notes` TEXT, `isUsed` TINYINT(1) DEFAULT false, `created_at` DATETIME NOT NULL, `updated_at` DATETIME NOT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB;;

SHOW INDEX FROM `expenses`;

DROP TABLE IF EXISTS `books`;;

CREATE TABLE IF NOT EXISTS `books` (`id` INTEGER auto_increment , `companyId` BIGINT UNSIGNED, `title` VARCHAR(255) NOT NULL, `author` VARCHAR(255) NOT NULL, `isbn` VARCHAR(255), `categoryId` INTEGER, `subCategoryId` INTEGER, `publisher` VARCHAR(255), `edition` VARCHAR(255), `price` DECIMAL(10,2) DEFAULT 0, `quantity` INTEGER NOT NULL DEFAULT 1, `availableQuantity` INTEGER NOT NULL DEFAULT 1, `rackLocation` VARCHAR(255), `created_at` DATETIME NOT NULL, `updated_at` DATETIME NOT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB;;

SHOW INDEX FROM `books`;

DROP TABLE IF EXISTS `library_transactions`;;

CREATE TABLE IF NOT EXISTS `library_transactions` (`id` BIGINT UNSIGNED auto_increment , `companyId` BIGINT UNSIGNED, `bookId` INTEGER NOT NULL, `studentId` BIGINT UNSIGNED, `staffId` BIGINT UNSIGNED, `issueDate` DATETIME NOT NULL, `dueDate` DATETIME NOT NULL, `returnDate` DATETIME, `status` ENUM('Issued', 'Returned', 'Lost') NOT NULL DEFAULT 'Issued', `fineAmount` DECIMAL(10,2) NOT NULL DEFAULT 0, `remarks` TEXT, `recoveryStatus` ENUM('Pending', 'Recovered', 'Not Applicable') NOT NULL DEFAULT 'Not Applicable', `created_at` DATETIME NOT NULL, `updated_at` DATETIME NOT NULL, PRIMARY KEY (`id`), FOREIGN KEY (`bookId`) REFERENCES `books` (`id`), FOREIGN KEY (`studentId`) REFERENCES `students` (`id`), FOREIGN KEY (`staffId`) REFERENCES `staff` (`id`)) ENGINE=InnoDB;;

SHOW INDEX FROM `library_transactions`;

DROP TABLE IF EXISTS `book_categories`;;

CREATE TABLE IF NOT EXISTS `book_categories` (`id` BIGINT UNSIGNED auto_increment , `companyId` BIGINT UNSIGNED, `name` VARCHAR(255) NOT NULL, `status` VARCHAR(255) NOT NULL DEFAULT 'Active', `created_at` DATETIME NOT NULL, `updated_at` DATETIME NOT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB;;

SHOW INDEX FROM `book_categories`;

DROP TABLE IF EXISTS `book_sub_categories`;;

CREATE TABLE IF NOT EXISTS `book_sub_categories` (`id` BIGINT UNSIGNED auto_increment , `companyId` BIGINT UNSIGNED, `categoryId` BIGINT UNSIGNED NOT NULL, `name` VARCHAR(255) NOT NULL, `status` VARCHAR(255) NOT NULL DEFAULT 'Active', `created_at` DATETIME NOT NULL, `updated_at` DATETIME NOT NULL, PRIMARY KEY (`id`), FOREIGN KEY (`categoryId`) REFERENCES `book_categories` (`id`)) ENGINE=InnoDB;;

SHOW INDEX FROM `book_sub_categories`;

DROP TABLE IF EXISTS `book_issue_rules`;;

CREATE TABLE IF NOT EXISTS `book_issue_rules` (`id` BIGINT UNSIGNED auto_increment , `companyId` BIGINT UNSIGNED, `memberType` ENUM('Student', 'Staff') NOT NULL, `maxBooksAllowed` INTEGER NOT NULL DEFAULT 1, `maxDaysAllowed` INTEGER NOT NULL DEFAULT 14, `created_at` DATETIME NOT NULL, `updated_at` DATETIME NOT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB;;

SHOW INDEX FROM `book_issue_rules`;

DROP TABLE IF EXISTS `fine_setup`;;

CREATE TABLE IF NOT EXISTS `fine_setup` (`id` BIGINT UNSIGNED auto_increment , `companyId` BIGINT UNSIGNED, `finePerDay` DECIMAL(10,2) NOT NULL DEFAULT 0, `created_at` DATETIME NOT NULL, `updated_at` DATETIME NOT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB;;

SHOW INDEX FROM `fine_setup`;

DROP TABLE IF EXISTS `referrals`;;

CREATE TABLE IF NOT EXISTS `referrals` (`id` BIGINT UNSIGNED auto_increment , `companyId` BIGINT UNSIGNED, `name` VARCHAR(255) NOT NULL, `referralCode` VARCHAR(255) NOT NULL UNIQUE, `rewardType` VARCHAR(255) NOT NULL DEFAULT 'Amount', `amountType` VARCHAR(255) NOT NULL DEFAULT 'Fixed', `amountValue` VARCHAR(255) NOT NULL DEFAULT '0', `extensionDays` VARCHAR(255) NOT NULL DEFAULT '0', `referralCountFrom` VARCHAR(255) NOT NULL DEFAULT '1', `referralCountTo` VARCHAR(255) NOT NULL DEFAULT '1', `minAmount` VARCHAR(255) NOT NULL DEFAULT '0', `maxUsage` VARCHAR(255) NOT NULL DEFAULT '', `expiryDate` VARCHAR(255) NOT NULL DEFAULT '', `status` VARCHAR(255) NOT NULL DEFAULT 'Active', `isUsed` TINYINT(1) NOT NULL DEFAULT false, `created_at` DATETIME NOT NULL, `updated_at` DATETIME NOT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB;;

SHOW INDEX FROM `referrals`;

DROP TABLE IF EXISTS `discounts`;;

CREATE TABLE IF NOT EXISTS `discounts` (`id` BIGINT UNSIGNED auto_increment , `companyId` BIGINT UNSIGNED, `name` VARCHAR(255) NOT NULL, `discountCode` VARCHAR(255) NOT NULL UNIQUE, `appliesTo` VARCHAR(255) NOT NULL DEFAULT 'All', `discountType` VARCHAR(255) NOT NULL DEFAULT 'Amount', `discountValue` VARCHAR(255) NOT NULL DEFAULT '0', `minAmount` VARCHAR(255) NOT NULL DEFAULT '0', `startDate` VARCHAR(255) NOT NULL DEFAULT '', `endDate` VARCHAR(255) NOT NULL DEFAULT '', `status` VARCHAR(255) NOT NULL DEFAULT 'Active', `isUsed` TINYINT(1) NOT NULL DEFAULT false, `created_at` DATETIME NOT NULL, `updated_at` DATETIME NOT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB;;

SHOW INDEX FROM `discounts`;

DROP TABLE IF EXISTS `coupon_utilizations`;;

CREATE TABLE IF NOT EXISTS `coupon_utilizations` (`id` BIGINT UNSIGNED auto_increment , `companyId` BIGINT UNSIGNED, `studentCode` VARCHAR(255) NOT NULL DEFAULT '', `studentName` VARCHAR(255) NOT NULL DEFAULT '', `benefitType` VARCHAR(255) NOT NULL, `sourceId` BIGINT UNSIGNED, `sourceCode` VARCHAR(255) NOT NULL DEFAULT '', `sourceName` VARCHAR(255) NOT NULL DEFAULT '', `discountType` VARCHAR(255) NOT NULL DEFAULT 'Amount', `discountValue` VARCHAR(255) NOT NULL DEFAULT '0', `utilizedOn` VARCHAR(255) NOT NULL DEFAULT '', `status` VARCHAR(255) NOT NULL DEFAULT 'Applied', `referenceNote` TEXT, `isUsed` TINYINT(1) NOT NULL DEFAULT false, `created_at` DATETIME NOT NULL, `updated_at` DATETIME NOT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB;;

SHOW INDEX FROM `coupon_utilizations`;

DROP TABLE IF EXISTS `default_settings`;;

CREATE TABLE IF NOT EXISTS `default_settings` (`id` INTEGER auto_increment , `companyId` BIGINT UNSIGNED, `invoiceNumberStart` VARCHAR(255) DEFAULT '001', `invoiceNumberGap` INTEGER DEFAULT 1, `studentCodeStartFrom` VARCHAR(255) DEFAULT '001', `receiptNumberStartFrom` VARCHAR(255) DEFAULT '001', `expenseNumberStartFrom` VARCHAR(255) DEFAULT '001', `staffCodeStartFrom` VARCHAR(255) DEFAULT '001', `defaultTheme` VARCHAR(255) DEFAULT 'Light', `defaultDateFormat` VARCHAR(255) DEFAULT 'yyyy-MM-dd', `defaultLanguage` VARCHAR(255) DEFAULT 'English', `createdAt` DATETIME NOT NULL, `updatedAt` DATETIME NOT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB;;

SHOW INDEX FROM `default_settings`;;;

DROP TABLE IF EXISTS `roles`;;

CREATE TABLE IF NOT EXISTS `roles` (`id` INTEGER auto_increment , `companyId` BIGINT UNSIGNED, `name` VARCHAR(255) NOT NULL UNIQUE, `description` VARCHAR(255), `isActive` TINYINT(1) DEFAULT true, `permissions` JSON, `createdAt` DATETIME NOT NULL, `updatedAt` DATETIME NOT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB;;

SHOW INDEX FROM `roles`;

DROP TABLE IF EXISTS `notifications`;;

CREATE TABLE IF NOT EXISTS `notifications` (`id` BIGINT UNSIGNED auto_increment , `companyId` BIGINT UNSIGNED, `type` VARCHAR(255) NOT NULL, `title` VARCHAR(255) NOT NULL, `message` VARCHAR(255), `referenceId` VARCHAR(255), `referenceType` VARCHAR(255), `metadata` JSON, `isRead` TINYINT(1) DEFAULT false, `isDismissed` TINYINT(1) DEFAULT false, `createdAt` DATETIME NOT NULL, `updatedAt` DATETIME NOT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB;;

SHOW INDEX FROM `notifications`;

DROP TABLE IF EXISTS `packages`;;

CREATE TABLE IF NOT EXISTS `packages` (`id` BIGINT UNSIGNED auto_increment , `companyId` BIGINT UNSIGNED, `name` VARCHAR(255) NOT NULL UNIQUE, `price` DECIMAL(10,2) NOT NULL DEFAULT 0, `status` VARCHAR(255) NOT NULL DEFAULT 'Active', `created_at` DATETIME NOT NULL, `updated_at` DATETIME NOT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB;;

SHOW INDEX FROM `packages`;

DROP TABLE IF EXISTS `backup_history`;;

CREATE TABLE IF NOT EXISTS `backup_history` (`id` INTEGER auto_increment , `fileName` VARCHAR(255) NOT NULL, `type` ENUM('SQL', 'EXCEL') NOT NULL, `timestamp` DATETIME, PRIMARY KEY (`id`)) ENGINE=InnoDB;;

SHOW INDEX FROM `backup_history`;

DROP TABLE IF EXISTS `Taxes`;;

CREATE TABLE IF NOT EXISTS `Taxes` (`id` INTEGER auto_increment , `name` VARCHAR(255) NOT NULL, `percentage` FLOAT NOT NULL DEFAULT 0, `companyId` BIGINT UNSIGNED, `createdBy` BIGINT UNSIGNED, `created_at` DATETIME NOT NULL, `updated_at` DATETIME NOT NULL, PRIMARY KEY (`id`), FOREIGN KEY (`companyId`) REFERENCES `companies` (`id`), FOREIGN KEY (`createdBy`) REFERENCES `app_users` (`id`)) ENGINE=InnoDB;;

SHOW INDEX FROM `Taxes`;
