-- Preez Brands ERP Pre-Restore SQL Backup
-- Generated: 2026-06-20 22:25:42



CREATE TABLE "migrations" ("id" integer primary key autoincrement not null, "migration" varchar not null, "batch" integer not null);

INSERT INTO `migrations` (`id`, `migration`, `batch`) VALUES ('1', '2024_01_01_000001_create_roles_table', '1');
INSERT INTO `migrations` (`id`, `migration`, `batch`) VALUES ('2', '2024_01_01_000002_create_users_table', '1');
INSERT INTO `migrations` (`id`, `migration`, `batch`) VALUES ('3', '2024_01_01_000003_create_settings_and_support_tables', '1');
INSERT INTO `migrations` (`id`, `migration`, `batch`) VALUES ('4', '2024_01_01_000004_create_clients_table', '1');
INSERT INTO `migrations` (`id`, `migration`, `batch`) VALUES ('5', '2024_01_01_000005_create_inventory_tables', '1');
INSERT INTO `migrations` (`id`, `migration`, `batch`) VALUES ('6', '2024_01_01_000006_create_services_suppliers_table', '1');
INSERT INTO `migrations` (`id`, `migration`, `batch`) VALUES ('7', '2024_01_01_000007_create_purchase_orders_table', '1');
INSERT INTO `migrations` (`id`, `migration`, `batch`) VALUES ('8', '2024_01_01_000008_create_quotations_invoices_table', '1');
INSERT INTO `migrations` (`id`, `migration`, `batch`) VALUES ('9', '2024_01_01_000009_create_sales_payments_table', '1');
INSERT INTO `migrations` (`id`, `migration`, `batch`) VALUES ('10', '2024_01_01_000010_create_debts_expenses_refunds_table', '1');
INSERT INTO `migrations` (`id`, `migration`, `batch`) VALUES ('11', '2024_01_01_000011_create_audit_notifications_table', '1');
INSERT INTO `migrations` (`id`, `migration`, `batch`) VALUES ('12', '2026_06_20_184540_add_received_at_to_purchase_orders_table', '1');
INSERT INTO `migrations` (`id`, `migration`, `batch`) VALUES ('13', '2026_06_21_000000_update_permission_matrix', '1');


CREATE TABLE "roles" ("id" integer primary key autoincrement not null, "name" varchar not null, "display_name" varchar not null, "description" text, "is_active" tinyint(1) not null default '1', "created_at" datetime, "updated_at" datetime);

INSERT INTO `roles` (`id`, `name`, `display_name`, `description`, `is_active`, `created_at`, `updated_at`) VALUES ('1', 'super_admin', 'Super Admin', NULL, '1', '2026-06-20 22:25:42', '2026-06-20 22:25:42');


CREATE TABLE "permissions" ("id" integer primary key autoincrement not null, "name" varchar not null, "display_name" varchar not null, "module" varchar not null, "description" text, "created_at" datetime, "updated_at" datetime);

INSERT INTO `permissions` (`id`, `name`, `display_name`, `module`, `description`, `created_at`, `updated_at`) VALUES ('1', 'sales.edit', 'Edit Finalized Sale', 'sales', 'Modify or edit details of finalized sales', '2026-06-20 22:25:41', '2026-06-20 22:25:41');
INSERT INTO `permissions` (`id`, `name`, `display_name`, `module`, `description`, `created_at`, `updated_at`) VALUES ('2', 'clients.create', 'Create Client', 'crm', 'Add new clients to the system', '2026-06-20 22:25:41', '2026-06-20 22:25:41');
INSERT INTO `permissions` (`id`, `name`, `display_name`, `module`, `description`, `created_at`, `updated_at`) VALUES ('3', 'clients.edit', 'Edit Client', 'crm', 'Modify client profiles and credentials', '2026-06-20 22:25:41', '2026-06-20 22:25:41');
INSERT INTO `permissions` (`id`, `name`, `display_name`, `module`, `description`, `created_at`, `updated_at`) VALUES ('4', 'products.create', 'Add Product', 'inventory', 'Create and register new products/variants', '2026-06-20 22:25:41', '2026-06-20 22:25:41');
INSERT INTO `permissions` (`id`, `name`, `display_name`, `module`, `description`, `created_at`, `updated_at`) VALUES ('5', 'products.edit', 'Edit Product', 'inventory', 'Modify existing product/variant information', '2026-06-20 22:25:41', '2026-06-20 22:25:41');
INSERT INTO `permissions` (`id`, `name`, `display_name`, `module`, `description`, `created_at`, `updated_at`) VALUES ('6', 'backups.restore', 'Restore Backup', 'support', 'Restore database backups', '2026-06-20 22:25:41', '2026-06-20 22:25:41');
INSERT INTO `permissions` (`id`, `name`, `display_name`, `module`, `description`, `created_at`, `updated_at`) VALUES ('7', 'backups.manage', 'Manage Backups', 'support', NULL, '2026-06-20 22:25:42', '2026-06-20 22:25:42');


CREATE TABLE "role_permissions" ("id" integer primary key autoincrement not null, "role_id" integer not null, "permission_id" integer not null, "created_at" datetime, "updated_at" datetime, foreign key("role_id") references "roles"("id") on delete cascade, foreign key("permission_id") references "permissions"("id") on delete cascade);

INSERT INTO `role_permissions` (`id`, `role_id`, `permission_id`, `created_at`, `updated_at`) VALUES ('1', '1', '7', NULL, NULL);


CREATE TABLE "users" ("id" integer primary key autoincrement not null, "role_id" integer not null, "employee_code" varchar not null, "full_name" varchar not null, "username" varchar not null, "email" varchar, "phone" varchar, "password" varchar not null, "profile_photo" varchar, "status" varchar check ("status" in ('active', 'suspended', 'inactive')) not null default 'active', "failed_logins" integer not null default '0', "locked_until" datetime, "last_login_at" datetime, "last_login_ip" varchar, "remember_token" varchar, "email_verified_at" datetime, "deleted_at" datetime, "created_at" datetime, "updated_at" datetime, foreign key("role_id") references "roles"("id") on delete restrict);

INSERT INTO `users` (`id`, `role_id`, `employee_code`, `full_name`, `username`, `email`, `phone`, `password`, `profile_photo`, `status`, `failed_logins`, `locked_until`, `last_login_at`, `last_login_ip`, `remember_token`, `email_verified_at`, `deleted_at`, `created_at`, `updated_at`) VALUES ('1', '1', 'EMP-001', 'Admin User', 'admin', 'admin@test.com', '123', '$2y$04$ro8NtEdKkBpNzBAmmbVmguX0h6S84Z26rC9l3mSjNRqFzHmxdxQcy', NULL, 'active', '0', NULL, NULL, NULL, NULL, NULL, NULL, '2026-06-20 22:25:42', '2026-06-20 22:25:42');


CREATE TABLE "user_permissions" ("id" integer primary key autoincrement not null, "user_id" integer not null, "permission_id" integer not null, "granted" tinyint(1) not null default '1', "granted_by" integer, "created_at" datetime, "updated_at" datetime, foreign key("user_id") references "users"("id") on delete cascade, foreign key("permission_id") references "permissions"("id") on delete cascade, foreign key("granted_by") references "users"("id") on delete set null);



CREATE TABLE "password_reset_tokens" ("email" varchar not null, "token" varchar not null, "created_at" datetime, primary key ("email"));



CREATE TABLE "sessions" ("id" varchar not null, "user_id" integer, "ip_address" varchar, "user_agent" text, "payload" text not null, "last_activity" integer not null, primary key ("id"));



CREATE TABLE "settings" ("id" integer primary key autoincrement not null, "key" varchar not null, "value" text, "type" varchar not null default 'string', "group" varchar not null default 'general', "display_name" varchar, "description" text, "created_at" datetime, "updated_at" datetime);



CREATE TABLE "expense_categories" ("id" integer primary key autoincrement not null, "name" varchar not null, "description" text, "is_active" tinyint(1) not null default '1', "deleted_at" datetime, "created_at" datetime, "updated_at" datetime);



CREATE TABLE "cache" ("key" varchar not null, "value" text not null, "expiration" integer not null, primary key ("key"));



CREATE TABLE "cache_locks" ("key" varchar not null, "owner" varchar not null, "expiration" integer not null, primary key ("key"));



CREATE TABLE "jobs" ("id" integer primary key autoincrement not null, "queue" varchar not null, "payload" text not null, "attempts" integer not null, "reserved_at" integer, "available_at" integer not null, "created_at" integer not null);



CREATE TABLE "job_batches" ("id" varchar not null, "name" varchar not null, "total_jobs" integer not null, "pending_jobs" integer not null, "failed_jobs" integer not null, "failed_job_ids" text not null, "options" text, "cancelled_at" integer, "created_at" integer not null, "finished_at" integer, primary key ("id"));



CREATE TABLE "clients" ("id" integer primary key autoincrement not null, "client_code" varchar not null, "full_name" varchar not null, "phone" varchar, "email" varchar, "address" text, "tin" varchar, "business_name" varchar, "credit_limit" numeric not null default '0', "outstanding_balance" numeric not null default '0', "status" varchar check ("status" in ('active', 'inactive', 'blacklisted')) not null default 'active', "notes" text, "created_by" integer, "deleted_at" datetime, "created_at" datetime, "updated_at" datetime, foreign key("created_by") references "users"("id") on delete set null);



CREATE TABLE "customer_ledgers" ("id" integer primary key autoincrement not null, "client_id" integer not null, "reference" varchar not null, "description" text not null, "debit" numeric not null default '0', "credit" numeric not null default '0', "balance" numeric not null default '0', "type" varchar not null, "created_by" integer, "created_at" datetime, "updated_at" datetime, foreign key("client_id") references "clients"("id") on delete cascade, foreign key("created_by") references "users"("id") on delete set null);



CREATE TABLE "categories" ("id" integer primary key autoincrement not null, "name" varchar not null, "type" varchar check ("type" in ('product', 'service')) not null default 'product', "description" text, "is_active" tinyint(1) not null default '1', "deleted_at" datetime, "created_at" datetime, "updated_at" datetime);



CREATE TABLE "brands" ("id" integer primary key autoincrement not null, "name" varchar not null, "description" text, "is_active" tinyint(1) not null default '1', "deleted_at" datetime, "created_at" datetime, "updated_at" datetime);



CREATE TABLE "products" ("id" integer primary key autoincrement not null, "product_code" varchar not null, "product_name" varchar not null, "category_id" integer, "brand_id" integer, "description" text, "base_unit" varchar not null default 'piece', "tax_type" varchar check ("tax_type" in ('vatable', 'exempt', 'zero_rated')) not null default 'vatable', "product_image" varchar, "has_variants" tinyint(1) not null default '0', "status" varchar check ("status" in ('active', 'archived')) not null default 'active', "created_by" integer, "deleted_at" datetime, "created_at" datetime, "updated_at" datetime, foreign key("category_id") references "categories"("id") on delete set null, foreign key("brand_id") references "brands"("id") on delete set null, foreign key("created_by") references "users"("id") on delete set null);



CREATE TABLE "product_variants" ("id" integer primary key autoincrement not null, "product_id" integer not null, "sku" varchar not null, "barcode" varchar, "variant_name" varchar not null, "buying_price" numeric not null default '0', "selling_price" numeric not null default '0', "stock_qty" integer not null default '0', "reorder_level" integer not null default '5', "track_stock" tinyint(1) not null default '1', "status" varchar check ("status" in ('active', 'inactive')) not null default 'active', "deleted_at" datetime, "created_at" datetime, "updated_at" datetime, foreign key("product_id") references "products"("id") on delete cascade);



CREATE TABLE "stock_movements" ("id" integer primary key autoincrement not null, "product_variant_id" integer not null, "type" varchar check ("type" in ('purchase', 'sale', 'return', 'adjustment_in', 'adjustment_out', 'transfer_in', 'transfer_out')) not null, "quantity" integer not null, "stock_before" integer not null, "stock_after" integer not null, "reference" varchar, "notes" text, "created_by" integer, "created_at" datetime, "updated_at" datetime, foreign key("product_variant_id") references "product_variants"("id") on delete cascade, foreign key("created_by") references "users"("id") on delete set null);



CREATE TABLE "services" ("id" integer primary key autoincrement not null, "service_code" varchar not null, "service_name" varchar not null, "category_id" integer, "default_price" numeric not null default '0', "description" text, "tax_type" varchar check ("tax_type" in ('vatable', 'exempt', 'zero_rated')) not null default 'vatable', "status" varchar check ("status" in ('active', 'inactive')) not null default 'active', "created_by" integer, "deleted_at" datetime, "created_at" datetime, "updated_at" datetime, foreign key("category_id") references "categories"("id") on delete set null, foreign key("created_by") references "users"("id") on delete set null);



CREATE TABLE "suppliers" ("id" integer primary key autoincrement not null, "supplier_code" varchar not null, "company_name" varchar not null, "contact_person" varchar, "phone" varchar, "email" varchar, "address" text, "tin" varchar, "outstanding_balance" numeric not null default '0', "status" varchar check ("status" in ('active', 'inactive')) not null default 'active', "notes" text, "created_by" integer, "deleted_at" datetime, "created_at" datetime, "updated_at" datetime, foreign key("created_by") references "users"("id") on delete set null);



CREATE TABLE "supplier_ledgers" ("id" integer primary key autoincrement not null, "supplier_id" integer not null, "reference" varchar not null, "description" text not null, "debit" numeric not null default '0', "credit" numeric not null default '0', "balance" numeric not null default '0', "type" varchar not null, "created_by" integer, "created_at" datetime, "updated_at" datetime, foreign key("supplier_id") references "suppliers"("id") on delete cascade, foreign key("created_by") references "users"("id") on delete set null);



CREATE TABLE "purchase_orders" ("id" integer primary key autoincrement not null, "po_number" varchar not null, "supplier_id" integer not null, "order_date" date not null, "expected_delivery" date, "subtotal" numeric not null default '0', "tax_amount" numeric not null default '0', "discount" numeric not null default '0', "total" numeric not null default '0', "status" varchar check ("status" in ('draft', 'ordered', 'partial', 'received', 'cancelled')) not null default 'draft', "notes" text, "created_by" integer, "approved_by" integer, "approved_at" datetime, "deleted_at" datetime, "created_at" datetime, "updated_at" datetime, "received_at" datetime, foreign key("supplier_id") references "suppliers"("id") on delete restrict, foreign key("created_by") references "users"("id") on delete set null, foreign key("approved_by") references "users"("id") on delete set null);



CREATE TABLE "purchase_order_items" ("id" integer primary key autoincrement not null, "purchase_order_id" integer not null, "product_variant_id" integer not null, "ordered_qty" integer not null, "received_qty" integer not null default '0', "unit_price" numeric not null, "total_price" numeric not null, "created_at" datetime, "updated_at" datetime, foreign key("purchase_order_id") references "purchase_orders"("id") on delete cascade, foreign key("product_variant_id") references "product_variants"("id") on delete restrict);



CREATE TABLE "quotations" ("id" integer primary key autoincrement not null, "quotation_number" varchar not null, "client_id" integer not null, "quotation_date" date not null, "expiry_date" date, "subtotal" numeric not null default '0', "tax_rate" numeric not null default '18', "tax_amount" numeric not null default '0', "discount" numeric not null default '0', "total_amount" numeric not null default '0', "status" varchar check ("status" in ('draft', 'sent', 'approved', 'rejected', 'expired', 'converted')) not null default 'draft', "notes" text, "terms" text, "created_by" integer, "converted_by" integer, "converted_at" datetime, "deleted_at" datetime, "created_at" datetime, "updated_at" datetime, foreign key("client_id") references "clients"("id") on delete restrict, foreign key("created_by") references "users"("id") on delete set null, foreign key("converted_by") references "users"("id") on delete set null);



CREATE TABLE "quotation_items" ("id" integer primary key autoincrement not null, "quotation_id" integer not null, "item_type" varchar check ("item_type" in ('product', 'service')) not null, "item_id" integer not null, "item_name" varchar not null, "item_code" varchar, "description" text, "quantity" integer not null, "unit_price" numeric not null, "discount" numeric not null default '0', "total_price" numeric not null, "created_at" datetime, "updated_at" datetime, foreign key("quotation_id") references "quotations"("id") on delete cascade);



CREATE TABLE "invoices" ("id" integer primary key autoincrement not null, "invoice_number" varchar not null, "client_id" integer not null, "quotation_id" integer, "invoice_date" date not null, "due_date" date, "subtotal" numeric not null default '0', "tax_rate" numeric not null default '18', "tax_amount" numeric not null default '0', "discount" numeric not null default '0', "total_amount" numeric not null default '0', "amount_paid" numeric not null default '0', "balance_due" numeric not null default '0', "status" varchar check ("status" in ('draft', 'unpaid', 'partial', 'paid', 'overdue', 'cancelled', 'voided')) not null default 'draft', "payment_method" varchar check ("payment_method" in ('cash', 'bank_transfer', 'mobile_money', 'cheque', 'other', 'mixed')), "notes" text, "terms" text, "created_by" integer, "voided_by" integer, "voided_at" datetime, "void_reason" text, "deleted_at" datetime, "created_at" datetime, "updated_at" datetime, foreign key("client_id") references "clients"("id") on delete restrict, foreign key("quotation_id") references "quotations"("id") on delete set null, foreign key("created_by") references "users"("id") on delete set null, foreign key("voided_by") references "users"("id") on delete set null);



CREATE TABLE "invoice_items" ("id" integer primary key autoincrement not null, "invoice_id" integer not null, "item_type" varchar check ("item_type" in ('product', 'service')) not null, "item_id" integer not null, "item_name" varchar not null, "item_code" varchar, "description" text, "quantity" integer not null, "unit_price" numeric not null, "discount" numeric not null default '0', "total_price" numeric not null, "created_at" datetime, "updated_at" datetime, foreign key("invoice_id") references "invoices"("id") on delete cascade);



CREATE TABLE "sales" ("id" integer primary key autoincrement not null, "sale_number" varchar not null, "invoice_id" integer not null, "client_id" integer not null, "total_amount" numeric not null, "amount_paid" numeric not null default '0', "change_given" numeric not null default '0', "payment_method" varchar check ("payment_method" in ('cash', 'bank_transfer', 'mobile_money', 'cheque', 'other', 'mixed', 'credit')) not null, "status" varchar check ("status" in ('completed', 'voided', 'refunded', 'partial_refund')) not null default 'completed', "notes" text, "created_by" integer, "voided_by" integer, "voided_at" datetime, "void_reason" text, "deleted_at" datetime, "created_at" datetime, "updated_at" datetime, foreign key("invoice_id") references "invoices"("id") on delete restrict, foreign key("client_id") references "clients"("id") on delete restrict, foreign key("created_by") references "users"("id") on delete set null, foreign key("voided_by") references "users"("id") on delete set null);



CREATE TABLE "payments" ("id" integer primary key autoincrement not null, "payment_number" varchar not null, "client_id" integer not null, "amount" numeric not null, "payment_method" varchar check ("payment_method" in ('cash', 'bank_transfer', 'mobile_money', 'cheque', 'other')) not null, "reference" varchar, "notes" text, "status" varchar check ("status" in ('active', 'reversed')) not null default 'active', "created_by" integer, "reversed_by" integer, "reversed_at" datetime, "created_at" datetime, "updated_at" datetime, foreign key("client_id") references "clients"("id") on delete restrict, foreign key("created_by") references "users"("id") on delete set null, foreign key("reversed_by") references "users"("id") on delete set null);



CREATE TABLE "payment_allocations" ("id" integer primary key autoincrement not null, "payment_id" integer not null, "invoice_id" integer not null, "allocated_amount" numeric not null, "allocation_method" varchar check ("allocation_method" in ('fifo', 'manual', 'split')) not null, "created_at" datetime, "updated_at" datetime, foreign key("payment_id") references "payments"("id") on delete cascade, foreign key("invoice_id") references "invoices"("id") on delete cascade);



CREATE TABLE "debts" ("id" integer primary key autoincrement not null, "client_id" integer not null, "invoice_id" integer not null, "original_amount" numeric not null, "outstanding_balance" numeric not null, "due_date" date, "days_overdue" integer not null default '0', "status" varchar check ("status" in ('active', 'partially_paid', 'paid', 'waived', 'written_off')) not null default 'active', "created_at" datetime, "updated_at" datetime, foreign key("client_id") references "clients"("id") on delete restrict, foreign key("invoice_id") references "invoices"("id") on delete restrict);



CREATE TABLE "debt_waivers" ("id" integer primary key autoincrement not null, "debt_id" integer not null, "client_id" integer not null, "waived_amount" numeric not null, "reason" text not null, "status" varchar check ("status" in ('pending', 'approved', 'rejected')) not null default 'pending', "requested_by" integer, "approved_by" integer, "approved_at" datetime, "rejection_reason" text, "created_at" datetime, "updated_at" datetime, foreign key("debt_id") references "debts"("id") on delete cascade, foreign key("client_id") references "clients"("id") on delete restrict, foreign key("requested_by") references "users"("id") on delete set null, foreign key("approved_by") references "users"("id") on delete set null);



CREATE TABLE "expenses" ("id" integer primary key autoincrement not null, "expense_number" varchar not null, "category_id" integer not null, "amount" numeric not null, "expense_date" date not null, "description" text not null, "receipt_file" varchar, "status" varchar check ("status" in ('pending', 'approved', 'rejected')) not null default 'pending', "created_by" integer, "approved_by" integer, "approved_at" datetime, "rejection_reason" text, "deleted_at" datetime, "created_at" datetime, "updated_at" datetime, foreign key("category_id") references "expense_categories"("id") on delete restrict, foreign key("created_by") references "users"("id") on delete set null, foreign key("approved_by") references "users"("id") on delete set null);



CREATE TABLE "refunds" ("id" integer primary key autoincrement not null, "refund_number" varchar not null, "invoice_id" integer not null, "client_id" integer not null, "refund_amount" numeric not null, "refund_type" varchar check ("refund_type" in ('product_return', 'service_refund', 'invoice_reversal', 'credit_note')) not null, "reason" text not null, "status" varchar check ("status" in ('pending', 'approved', 'rejected', 'completed')) not null default 'pending', "stock_restored" tinyint(1) not null default '0', "ledger_adjusted" tinyint(1) not null default '0', "requested_by" integer, "approved_by" integer, "approved_at" datetime, "rejection_reason" text, "deleted_at" datetime, "created_at" datetime, "updated_at" datetime, foreign key("invoice_id") references "invoices"("id") on delete restrict, foreign key("client_id") references "clients"("id") on delete restrict, foreign key("requested_by") references "users"("id") on delete set null, foreign key("approved_by") references "users"("id") on delete set null);



CREATE TABLE "refund_items" ("id" integer primary key autoincrement not null, "refund_id" integer not null, "item_type" varchar check ("item_type" in ('product', 'service')) not null, "item_id" integer not null, "item_name" varchar not null, "quantity" integer not null default '1', "unit_price" numeric not null, "total_price" numeric not null, "stock_restored" tinyint(1) not null default '0', "created_at" datetime, "updated_at" datetime, foreign key("refund_id") references "refunds"("id") on delete cascade);



CREATE TABLE "audit_logs" ("id" integer primary key autoincrement not null, "user_id" integer, "user_name" varchar, "action" varchar not null, "entity_type" varchar not null, "entity_id" integer, "old_values" text, "new_values" text, "ip_address" varchar, "user_agent" text, "url" varchar, "created_at" datetime, "updated_at" datetime, foreign key("user_id") references "users"("id") on delete set null);



CREATE TABLE "notifications" ("id" varchar not null, "type" varchar not null, "notifiable_type" varchar not null, "notifiable_id" integer not null, "data" text not null, "read_at" datetime, "created_at" datetime, "updated_at" datetime, primary key ("id"));



CREATE TABLE "backups" ("id" integer primary key autoincrement not null, "filename" varchar not null, "filepath" varchar not null, "file_size" integer not null default '0', "type" varchar check ("type" in ('manual', 'scheduled', 'pre_restore')) not null, "status" varchar check ("status" in ('pending', 'completed', 'failed')) not null default 'pending', "notes" text, "created_by" integer, "created_at" datetime, "updated_at" datetime, foreign key("created_by") references "users"("id") on delete set null);

INSERT INTO `backups` (`id`, `filename`, `filepath`, `file_size`, `type`, `status`, `notes`, `created_by`, `created_at`, `updated_at`) VALUES ('1', 'test-backup.sql', 'backups/test-backup.sql', '15', 'manual', 'completed', 'Test', '1', '2026-06-20 22:25:42', '2026-06-20 22:25:42');