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 `email_settings`;; 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 `students`;; 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 `staff`;; DROP TABLE IF EXISTS `app_users`;; 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 `app_users`;; DROP TABLE IF EXISTS `staff`;; 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 `students`;; 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 `email_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 `app_users`;; CREATE TABLE IF NOT EXISTS `app_users` (`id` BIGINT UNSIGNED auto_increment , `companyId` BIGINT UNSIGNED, `name` VARCHAR(255) NOT NULL, `email` VARCHAR(255) DEFAULT '', `mobileNumber` VARCHAR(255) DEFAULT '', `username` VARCHAR(255) NOT NULL, `password` VARCHAR(255) NOT NULL, `role` VARCHAR(255) DEFAULT 'Staff', `isActive` TINYINT(1) DEFAULT true, `isUsed` TINYINT(1) DEFAULT false, `created_at` DATETIME NOT NULL, `updated_at` DATETIME NOT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB;; SHOW INDEX FROM `app_users`; DROP TABLE IF EXISTS `staff`;; CREATE TABLE IF NOT EXISTS `staff` (`id` BIGINT UNSIGNED auto_increment , `companyId` BIGINT UNSIGNED, `code` VARCHAR(255) NOT NULL, `fullName` VARCHAR(255) NOT NULL, `phone` VARCHAR(255) DEFAULT '', `email` VARCHAR(255) DEFAULT '', `username` VARCHAR(255) DEFAULT '', `password` VARCHAR(255) DEFAULT '', `permanentAddress` TEXT, `temporaryAddress` TEXT, `sameAsPermanent` TINYINT(1) DEFAULT true, `documentType` VARCHAR(255) DEFAULT 'Citizenship', `documentNumber` VARCHAR(255) DEFAULT '', `expiryDate` VARCHAR(255) DEFAULT '', `documentFrontName` VARCHAR(255) DEFAULT '', `documentBackName` 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 `staff`; 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 `students`;; CREATE TABLE IF NOT EXISTS `students` (`id` BIGINT UNSIGNED auto_increment , `companyId` BIGINT UNSIGNED, `packageId` BIGINT UNSIGNED, `code` VARCHAR(255) NOT NULL, `fullName` VARCHAR(255) NOT NULL, `phone` VARCHAR(255) NOT NULL DEFAULT '', `email` VARCHAR(255) NOT NULL DEFAULT '', `username` VARCHAR(255) NOT NULL DEFAULT '', `password` VARCHAR(255) NOT NULL DEFAULT '', `permanentAddress` TEXT, `temporaryAddress` TEXT, `sameAsPermanent` TINYINT(1) NOT NULL DEFAULT true, `documentType` VARCHAR(255) NOT NULL DEFAULT 'Citizenship', `documentNumber` VARCHAR(255) NOT NULL DEFAULT '', `expiryDate` VARCHAR(255) NOT NULL DEFAULT '', `documentFrontName` VARCHAR(255) NOT NULL DEFAULT '', `documentBackName` VARCHAR(255) NOT NULL DEFAULT '', `referredByStudentCode` VARCHAR(255) NOT NULL DEFAULT '', `referredByStudentName` 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 `students`; 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 `email_settings`;; CREATE TABLE IF NOT EXISTS `email_settings` (`id` INTEGER auto_increment , `companyId` BIGINT UNSIGNED, `mailDriver` VARCHAR(255) DEFAULT 'SMTP', `encryption` VARCHAR(255) DEFAULT 'TLS', `fromName` VARCHAR(255) DEFAULT 'no-reply', `fromEmail` VARCHAR(255) DEFAULT 'no-reply@shanvireadingroom.com', `replyToEmail` VARCHAR(255), `smtpHost` VARCHAR(255) DEFAULT 'mail.shanvireadingroom.com', `smtpPort` VARCHAR(255) DEFAULT '465', `smtpUsername` VARCHAR(255), `smtpPassword` VARCHAR(255), `createdAt` DATETIME NOT NULL, `updatedAt` DATETIME NOT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB;; SHOW INDEX FROM `email_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`; -- Seeding Default Users INSERT INTO `app_users` (`name`, `email`, `username`, `password`, `mobileNumber`, `role`, `isActive`, `isUsed`, `created_at`, `updated_at`) VALUES ('Super Admin', 'superadmin@sanvi.com', 'superadmin', '$2b$10$CcfKDdvWWA..eOwXyIi1e.s3rXPDUlnQicSmPQLVkshlIE/Mh0OOq', '9841000001', 'SuperAdmin', 1, 0, NOW(), NOW()), ('Admin User', 'admin@sanvi.com', 'admin', '$2b$10$zcLCV7ZQ6Bl2khys1Euf/uVh90Zp1Pl65y0mM98TL50.gEmnFRtKm', '9841234567', 'Admin', 1, 0, NOW(), NOW()), ('Test Staff', 'staff@sanvi.com', 'staff', '$2b$10$O01o4Vq.1gJdF9N683v86ezqQfU.H5o8J3XmFwL58r.R3d.q6mQyG', '9843456789', 'Staff', 1, 0, NOW(), NOW());