-- Smart Tech OMP / Purchase Haat production-readiness migration
-- Release v2 - 25 June 2026
-- Import once AFTER database_updates_v1.sql.

SET SQL_MODE = 'NO_AUTO_VALUE_ON_ZERO';
SET time_zone = '+06:00';
START TRANSACTION;

CREATE TABLE IF NOT EXISTS `security_rate_limits` (
  `rate_key` char(64) NOT NULL,
  `hits` int(11) NOT NULL DEFAULT 0,
  `window_started_at` datetime NOT NULL,
  `expires_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  PRIMARY KEY (`rate_key`),
  KEY `idx_security_rate_expiry` (`expires_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE `users`
  ADD COLUMN IF NOT EXISTS `auth_version` int(11) NOT NULL DEFAULT 1 AFTER `transaction_pin_hash`,
  ADD COLUMN IF NOT EXISTS `must_change_password` tinyint(4) NOT NULL DEFAULT 0 AFTER `auth_version`,
  ADD COLUMN IF NOT EXISTS `password_changed_at` datetime DEFAULT NULL AFTER `must_change_password`;
ALTER TABLE `users`
  ADD INDEX IF NOT EXISTS `idx_users_status_role` (`status`,`role`);

ALTER TABLE `permissions`
  ADD INDEX IF NOT EXISTS `idx_permissions_module_action` (`module`,`action`);
ALTER TABLE `role_permissions`
  ADD INDEX IF NOT EXISTS `idx_role_permissions_permission` (`permission_id`);

INSERT INTO `permissions` (`module`,`action`,`description`,`created_at`)
SELECT 'dashboard','access','System overview, live sales, vendors, customers, orders, wallet and affiliate KPIs.',NOW()
WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `module`='dashboard' AND `action`='access');

INSERT INTO `permissions` (`module`,`action`,`description`,`created_at`)
SELECT 'orders','access','Manage orders, statuses, partial payments, invoices, tracking and item-level vendor split.',NOW()
WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `module`='orders' AND `action`='access');

INSERT INTO `permissions` (`module`,`action`,`description`,`created_at`)
SELECT 'order-status','access','Configure order statuses, email/SMS triggers, timeline colors and vendor/customer visibility.',NOW()
WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `module`='order-status' AND `action`='access');

INSERT INTO `permissions` (`module`,`action`,`description`,`created_at`)
SELECT 'invoices','access','Download, print and search invoices with VAT/tax/wallet/point details.',NOW()
WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `module`='invoices' AND `action`='access');

INSERT INTO `permissions` (`module`,`action`,`description`,`created_at`)
SELECT 'refunds','access','Refund requests, approval, gateway/wallet settlement and reason tracking.',NOW()
WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `module`='refunds' AND `action`='access');

INSERT INTO `permissions` (`module`,`action`,`description`,`created_at`)
SELECT 'returns','access','Return and replacement requests, courier pickup, inspection and approval workflow.',NOW()
WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `module`='returns' AND `action`='access');

INSERT INTO `permissions` (`module`,`action`,`description`,`created_at`)
SELECT 'abandoned-carts','access','Abandoned cart recovery, customer reminders and conversion tracking.',NOW()
WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `module`='abandoned-carts' AND `action`='access');

INSERT INTO `permissions` (`module`,`action`,`description`,`created_at`)
SELECT 'products','access','Unlimited physical/digital products with product-wise landing, SEO, barcode, QR and stock.',NOW()
WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `module`='products' AND `action`='access');

INSERT INTO `permissions` (`module`,`action`,`description`,`created_at`)
SELECT 'product-create','access','Advanced product form: gallery, video, variants, purchase/sale/flash price, reward point, shipping and SEO.',NOW()
WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `module`='product-create' AND `action`='access');

INSERT INTO `permissions` (`module`,`action`,`description`,`created_at`)
SELECT 'product-variants','access','Variant dimensions such as size, color, weight, material, model, storage and SKU-level stock.',NOW()
WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `module`='product-variants' AND `action`='access');

INSERT INTO `permissions` (`module`,`action`,`description`,`created_at`)
SELECT 'product-specifications','access','Specification groups and product specification values.',NOW()
WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `module`='product-specifications' AND `action`='access');

INSERT INTO `permissions` (`module`,`action`,`description`,`created_at`)
SELECT 'inventory','access','Stock ledger, low-stock alerts, vendor stock and product/variant inventory adjustments.',NOW()
WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `module`='inventory' AND `action`='access');

INSERT INTO `permissions` (`module`,`action`,`description`,`created_at`)
SELECT 'barcode-qr','access','Generate and print barcode/QR payloads with URL, SKU, product ID, price, vendor and stock.',NOW()
WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `module`='barcode-qr' AND `action`='access');

INSERT INTO `permissions` (`module`,`action`,`description`,`created_at`)
SELECT 'categories','access','Unlimited categories/subcategories with banners and SEO.',NOW()
WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `module`='categories' AND `action`='access');

INSERT INTO `permissions` (`module`,`action`,`description`,`created_at`)
SELECT 'shop-categories','access','Vendor shop category taxonomy and landing settings.',NOW()
WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `module`='shop-categories' AND `action`='access');

INSERT INTO `permissions` (`module`,`action`,`description`,`created_at`)
SELECT 'brands','access','Brand management with logo, landing page and SEO.',NOW()
WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `module`='brands' AND `action`='access');

INSERT INTO `permissions` (`module`,`action`,`description`,`created_at`)
SELECT 'product-reviews','access','Approve, reject and reply to product reviews and ratings.',NOW()
WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `module`='product-reviews' AND `action`='access');

INSERT INTO `permissions` (`module`,`action`,`description`,`created_at`)
SELECT 'product-questions','access','Product Q&A moderation and vendor/admin answers.',NOW()
WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `module`='product-questions' AND `action`='access');

INSERT INTO `permissions` (`module`,`action`,`description`,`created_at`)
SELECT 'related-products','access','Manual and automatic related product, upsell and cross-sell mapping.',NOW()
WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `module`='related-products' AND `action`='access');

INSERT INTO `permissions` (`module`,`action`,`description`,`created_at`)
SELECT 'digital-products','access','Digital product files, license keys, download limits and expiry settings.',NOW()
WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `module`='digital-products' AND `action`='access');

INSERT INTO `permissions` (`module`,`action`,`description`,`created_at`)
SELECT 'vendors','access','Vendor registration, approval, verification, shop details and policies.',NOW()
WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `module`='vendors' AND `action`='access');

INSERT INTO `permissions` (`module`,`action`,`description`,`created_at`)
SELECT 'vendor-verification','access','KYC/NID/trade-license verification queue and notes.',NOW()
WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `module`='vendor-verification' AND `action`='access');

INSERT INTO `permissions` (`module`,`action`,`description`,`created_at`)
SELECT 'vendor-packages','access','Vendor subscription/package plans, pricing, limits and duration.',NOW()
WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `module`='vendor-packages' AND `action`='access');

INSERT INTO `permissions` (`module`,`action`,`description`,`created_at`)
SELECT 'vendor-subscriptions','access','Subscription activation, expiry, payment status and renewal.',NOW()
WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `module`='vendor-subscriptions' AND `action`='access');

INSERT INTO `permissions` (`module`,`action`,`description`,`created_at`)
SELECT 'vendor-commission','access','Fixed, percentage, category-wise, product-wise and vendor-wise commission rules.',NOW()
WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `module`='vendor-commission' AND `action`='access');

INSERT INTO `permissions` (`module`,`action`,`description`,`created_at`)
SELECT 'vendor-payouts','access','Vendor wallet, payout requests, settlement and statement export.',NOW()
WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `module`='vendor-payouts' AND `action`='access');

INSERT INTO `permissions` (`module`,`action`,`description`,`created_at`)
SELECT 'vendor-ratings','access','Vendor-wise rating and review moderation.',NOW()
WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `module`='vendor-ratings' AND `action`='access');

INSERT INTO `permissions` (`module`,`action`,`description`,`created_at`)
SELECT 'vendor-shipping','access','Vendor-wise shipping options, pickup address and courier preferences.',NOW()
WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `module`='vendor-shipping' AND `action`='access');

INSERT INTO `permissions` (`module`,`action`,`description`,`created_at`)
SELECT 'customers','access','Customer profile, login history, wallet, orders, reviews and points.',NOW()
WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `module`='customers' AND `action`='access');

INSERT INTO `permissions` (`module`,`action`,`description`,`created_at`)
SELECT 'customer-addresses','access','Customer address book and multiple shipping addresses.',NOW()
WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `module`='customer-addresses' AND `action`='access');

INSERT INTO `permissions` (`module`,`action`,`description`,`created_at`)
SELECT 'wishlist-compare','access','Wishlist, comparison list, saved product and conversion analytics.',NOW()
WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `module`='wishlist-compare' AND `action`='access');

INSERT INTO `permissions` (`module`,`action`,`description`,`created_at`)
SELECT 'referrals','access','Referral link rewards, invited customers, reward history and fraud checks.',NOW()
WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `module`='referrals' AND `action`='access');

INSERT INTO `permissions` (`module`,`action`,`description`,`created_at`)
SELECT 'social-login','access','Google/Facebook login keys and enabled/disabled controls.',NOW()
WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `module`='social-login' AND `action`='access');

INSERT INTO `permissions` (`module`,`action`,`description`,`created_at`)
SELECT 'otp-security','access','Email OTP, phone OTP, admin/vendor 2FA and transaction PIN settings.',NOW()
WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `module`='otp-security' AND `action`='access');

INSERT INTO `permissions` (`module`,`action`,`description`,`created_at`)
SELECT 'affiliate','access','Affiliate PSA overview, latest sales, rank upgrades and royalty status.',NOW()
WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `module`='affiliate' AND `action`='access');

INSERT INTO `permissions` (`module`,`action`,`description`,`created_at`)
SELECT 'affiliate-members','access','IBO/EO/SEO/AGM/GM/ED/CM member profile, sponsor, status and landing page.',NOW()
WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `module`='affiliate-members' AND `action`='access');

INSERT INTO `permissions` (`module`,`action`,`description`,`created_at`)
SELECT 'affiliate-ranks','access','PSA thresholds from IBO to CM, rank automation and eligibility.',NOW()
WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `module`='affiliate-ranks' AND `action`='access');

INSERT INTO `permissions` (`module`,`action`,`description`,`created_at`)
SELECT 'psa-rules','access','Management/marketing PSA split, packaged/individual product profit rules.',NOW()
WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `module`='psa-rules' AND `action`='access');

INSERT INTO `permissions` (`module`,`action`,`description`,`created_at`)
SELECT 'psa-transactions','access','Order-wise profit, management PSA and marketing PSA ledger.',NOW()
WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `module`='psa-transactions' AND `action`='access');

INSERT INTO `permissions` (`module`,`action`,`description`,`created_at`)
SELECT 'management-psa','access','Company, shareholders, logistics, employees, office, sales and training allocation.',NOW()
WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `module`='management-psa' AND `action`='access');

INSERT INTO `permissions` (`module`,`action`,`description`,`created_at`)
SELECT 'global-royalty','access','Weekly minimum sale eligibility, royalty pool and all-rank distribution excluding IBO.',NOW()
WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `module`='global-royalty' AND `action`='access');

INSERT INTO `permissions` (`module`,`action`,`description`,`created_at`)
SELECT 'leadership-royalty','access','Direct leader, mentor and senior mentor bonus distribution.',NOW()
WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `module`='leadership-royalty' AND `action`='access');

INSERT INTO `permissions` (`module`,`action`,`description`,`created_at`)
SELECT 'incentive-fund','access','Admin incentive wallet and future incentive distribution.',NOW()
WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `module`='incentive-fund' AND `action`='access');

INSERT INTO `permissions` (`module`,`action`,`description`,`created_at`)
SELECT 'auto-suspension','access','Monthly royalty miss suspension and automatic reactivation rules.',NOW()
WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `module`='auto-suspension' AND `action`='access');

INSERT INTO `permissions` (`module`,`action`,`description`,`created_at`)
SELECT 'team-tree','access','Level-wise affiliate tree, upline/downline, team sales and PSA.',NOW()
WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `module`='team-tree' AND `action`='access');

INSERT INTO `permissions` (`module`,`action`,`description`,`created_at`)
SELECT 'affiliate-plans','access','Easy, Standard and VIP E-Dealer plans and product limits.',NOW()
WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `module`='affiliate-plans' AND `action`='access');

INSERT INTO `permissions` (`module`,`action`,`description`,`created_at`)
SELECT 'member-products','access','Members select products/bundles for their own store based on plan limit.',NOW()
WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `module`='member-products' AND `action`='access');

INSERT INTO `permissions` (`module`,`action`,`description`,`created_at`)
SELECT 'affiliate-clicks','access','Affiliate link clicks, conversion tracking and landing analytics.',NOW()
WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `module`='affiliate-clicks' AND `action`='access');

INSERT INTO `permissions` (`module`,`action`,`description`,`created_at`)
SELECT 'weekly-statements','access','Friday statements excluding CM, ED and IBO reports.',NOW()
WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `module`='weekly-statements' AND `action`='access');

INSERT INTO `permissions` (`module`,`action`,`description`,`created_at`)
SELECT 'monthly-statements','access','Monthly statement generation on day 30 with royalty, wallet and withdraw details.',NOW()
WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `module`='monthly-statements' AND `action`='access');

INSERT INTO `permissions` (`module`,`action`,`description`,`created_at`)
SELECT 'wallets','access','Cash, purchase, point, royalty, commission, withdraw, bonus and reward wallets.',NOW()
WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `module`='wallets' AND `action`='access');

INSERT INTO `permissions` (`module`,`action`,`description`,`created_at`)
SELECT 'admin-wallets','access','Incentive wallet and VAT + TAX wallet balances.',NOW()
WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `module`='admin-wallets' AND `action`='access');

INSERT INTO `permissions` (`module`,`action`,`description`,`created_at`)
SELECT 'wallet-transfers','access','Admin-to-cash/purchase and cash-to-purchase transfers with lock rules.',NOW()
WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `module`='wallet-transfers' AND `action`='access');

INSERT INTO `permissions` (`module`,`action`,`description`,`created_at`)
SELECT 'withdrawals','access','7.5% charge, bank/bKash/Nagad withdrawals, limits, OTP and transaction PIN.',NOW()
WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `module`='withdrawals' AND `action`='access');

INSERT INTO `permissions` (`module`,`action`,`description`,`created_at`)
SELECT 'reward-rules','access','Product/category/order-value points, expiry, conversion and redemption rules.',NOW()
WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `module`='reward-rules' AND `action`='access');

INSERT INTO `permissions` (`module`,`action`,`description`,`created_at`)
SELECT 'reward-history','access','Customer/vendor reward point ledger and conversion history.',NOW()
WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `module`='reward-history' AND `action`='access');

INSERT INTO `permissions` (`module`,`action`,`description`,`created_at`)
SELECT 'tax-vat','access','Tax/VAT wallet, withdrawal charges, invoice VAT settings and export.',NOW()
WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `module`='tax-vat' AND `action`='access');

INSERT INTO `permissions` (`module`,`action`,`description`,`created_at`)
SELECT 'payments','access','Payment transaction list, partial payments and status history.',NOW()
WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `module`='payments' AND `action`='access');

INSERT INTO `permissions` (`module`,`action`,`description`,`created_at`)
SELECT 'payment-gateways','access','SSLCommerz, bKash, Nagad, wallet and COD configuration.',NOW()
WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `module`='payment-gateways' AND `action`='access');

INSERT INTO `permissions` (`module`,`action`,`description`,`created_at`)
SELECT 'sslcommerz','access','SSLCommerz credentials, sandbox/live toggle, callbacks and logs.',NOW()
WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `module`='sslcommerz' AND `action`='access');

INSERT INTO `permissions` (`module`,`action`,`description`,`created_at`)
SELECT 'bkash','access','bKash checkout configuration, token, execute and callback logs.',NOW()
WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `module`='bkash' AND `action`='access');

INSERT INTO `permissions` (`module`,`action`,`description`,`created_at`)
SELECT 'nagad','access','Nagad merchant configuration, callback and verification logs.',NOW()
WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `module`='nagad' AND `action`='access');

INSERT INTO `permissions` (`module`,`action`,`description`,`created_at`)
SELECT 'cod','access','COD availability by zone/vendor/product and order amount.',NOW()
WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `module`='cod' AND `action`='access');

INSERT INTO `permissions` (`module`,`action`,`description`,`created_at`)
SELECT 'payment-logs','access','Gateway request/response audit log.',NOW()
WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `module`='payment-logs' AND `action`='access');

INSERT INTO `permissions` (`module`,`action`,`description`,`created_at`)
SELECT 'delivery','access','Delivery overview, zones, pending shipments and courier queue.',NOW()
WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `module`='delivery' AND `action`='access');

INSERT INTO `permissions` (`module`,`action`,`description`,`created_at`)
SELECT 'countries','access','Country master data and active/inactive controls.',NOW()
WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `module`='countries' AND `action`='access');

INSERT INTO `permissions` (`module`,`action`,`description`,`created_at`)
SELECT 'districts','access','Bangladesh district-wise delivery charge setup.',NOW()
WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `module`='districts' AND `action`='access');

INSERT INTO `permissions` (`module`,`action`,`description`,`created_at`)
SELECT 'areas','access','Area/thana/zone master data and charge mapping.',NOW()
WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `module`='areas' AND `action`='access');

INSERT INTO `permissions` (`module`,`action`,`description`,`created_at`)
SELECT 'delivery-zones','access','District/area multi-select zones and price rules.',NOW()
WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `module`='delivery-zones' AND `action`='access');

INSERT INTO `permissions` (`module`,`action`,`description`,`created_at`)
SELECT 'shipping-rules','access','Local/nationwide, vendor-wise/product-wise, free delivery and minimum order rules.',NOW()
WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `module`='shipping-rules' AND `action`='access');

INSERT INTO `permissions` (`module`,`action`,`description`,`created_at`)
SELECT 'couriers','access','Delivery partner/courier management and API configuration.',NOW()
WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `module`='couriers' AND `action`='access');

INSERT INTO `permissions` (`module`,`action`,`description`,`created_at`)
SELECT 'shipments','access','Tracking number, delivery status, shipped/delivered timestamps.',NOW()
WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `module`='shipments' AND `action`='access');

INSERT INTO `permissions` (`module`,`action`,`description`,`created_at`)
SELECT 'pickup-points','access','Vendor pickup location and office pickup point setup.',NOW()
WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `module`='pickup-points' AND `action`='access');

INSERT INTO `permissions` (`module`,`action`,`description`,`created_at`)
SELECT 'cms','access','Unlimited pages and dynamic CMS page builder.',NOW()
WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `module`='cms' AND `action`='access');

INSERT INTO `permissions` (`module`,`action`,`description`,`created_at`)
SELECT 'page-categories','access','Unlimited page categories and page grouping.',NOW()
WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `module`='page-categories' AND `action`='access');

INSERT INTO `permissions` (`module`,`action`,`description`,`created_at`)
SELECT 'page-builder','access','Advanced homepage sections, product blocks and drag ordering.',NOW()
WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `module`='page-builder' AND `action`='access');

INSERT INTO `permissions` (`module`,`action`,`description`,`created_at`)
SELECT 'menus','access','Header/footer/mobile menu builder.',NOW()
WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `module`='menus' AND `action`='access');

INSERT INTO `permissions` (`module`,`action`,`description`,`created_at`)
SELECT 'sliders','access','Homepage sliders with CTA and schedule.',NOW()
WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `module`='sliders' AND `action`='access');

INSERT INTO `permissions` (`module`,`action`,`description`,`created_at`)
SELECT 'banners','access','Promotional banner positions and campaign links.',NOW()
WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `module`='banners' AND `action`='access');

INSERT INTO `permissions` (`module`,`action`,`description`,`created_at`)
SELECT 'theme-settings','access','Logo, color, typography, layout and mobile UI settings.',NOW()
WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `module`='theme-settings' AND `action`='access');

INSERT INTO `permissions` (`module`,`action`,`description`,`created_at`)
SELECT 'seo','access','Meta title/description/keywords, Open Graph image and schema settings.',NOW()
WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `module`='seo' AND `action`='access');

INSERT INTO `permissions` (`module`,`action`,`description`,`created_at`)
SELECT 'sitemap-robots','access','Sitemap generator and robots.txt editor.',NOW()
WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `module`='sitemap-robots' AND `action`='access');

INSERT INTO `permissions` (`module`,`action`,`description`,`created_at`)
SELECT 'schema-markup','access','Product, organization, breadcrumb and FAQ schema controls.',NOW()
WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `module`='schema-markup' AND `action`='access');

INSERT INTO `permissions` (`module`,`action`,`description`,`created_at`)
SELECT 'coupons','access','Coupon rules, usage limits, date range and redemption report.',NOW()
WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `module`='coupons' AND `action`='access');

INSERT INTO `permissions` (`module`,`action`,`description`,`created_at`)
SELECT 'flash-sales','access','Flash sale products, prices, countdown and stock locks.',NOW()
WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `module`='flash-sales' AND `action`='access');

INSERT INTO `permissions` (`module`,`action`,`description`,`created_at`)
SELECT 'campaigns','access','Campaign/deal-of-the-day promotional management.',NOW()
WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `module`='campaigns' AND `action`='access');

INSERT INTO `permissions` (`module`,`action`,`description`,`created_at`)
SELECT 'featured-sections','access','Featured, trending, best-selling, new arrivals and custom product sections.',NOW()
WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `module`='featured-sections' AND `action`='access');

INSERT INTO `permissions` (`module`,`action`,`description`,`created_at`)
SELECT 'newsletter','access','Newsletter subscribers, campaigns and export.',NOW()
WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `module`='newsletter' AND `action`='access');

INSERT INTO `permissions` (`module`,`action`,`description`,`created_at`)
SELECT 'notifications','access','Admin/vendor/customer notification center.',NOW()
WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `module`='notifications' AND `action`='access');

INSERT INTO `permissions` (`module`,`action`,`description`,`created_at`)
SELECT 'email-templates','access','Email notification template editor.',NOW()
WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `module`='email-templates' AND `action`='access');

INSERT INTO `permissions` (`module`,`action`,`description`,`created_at`)
SELECT 'sms-templates','access','SMS/OTP template editor and provider config.',NOW()
WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `module`='sms-templates' AND `action`='access');

INSERT INTO `permissions` (`module`,`action`,`description`,`created_at`)
SELECT 'push-notifications','access','Push subscribers, push campaigns and notification log.',NOW()
WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `module`='push-notifications' AND `action`='access');

INSERT INTO `permissions` (`module`,`action`,`description`,`created_at`)
SELECT 'support-chat','access','Customer support chat, WhatsApp/Messenger links and contact inbox.',NOW()
WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `module`='support-chat' AND `action`='access');

INSERT INTO `permissions` (`module`,`action`,`description`,`created_at`)
SELECT 'social-links','access','WhatsApp, Facebook, Messenger, Instagram, TikTok and YouTube settings.',NOW()
WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `module`='social-links' AND `action`='access');

INSERT INTO `permissions` (`module`,`action`,`description`,`created_at`)
SELECT 'reports','access','Sales, vendor, product, customer, delivery, payment, commission, stock and reward reports.',NOW()
WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `module`='reports' AND `action`='access');

INSERT INTO `permissions` (`module`,`action`,`description`,`created_at`)
SELECT 'sales-report','access','Date/vendor/category/product-wise sales report.',NOW()
WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `module`='sales-report' AND `action`='access');

INSERT INTO `permissions` (`module`,`action`,`description`,`created_at`)
SELECT 'vendor-sales-report','access','Vendor sales, commission and payout report.',NOW()
WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `module`='vendor-sales-report' AND `action`='access');

INSERT INTO `permissions` (`module`,`action`,`description`,`created_at`)
SELECT 'product-performance-report','access','Views, carts, sales, conversion, stock and profit report.',NOW()
WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `module`='product-performance-report' AND `action`='access');

INSERT INTO `permissions` (`module`,`action`,`description`,`created_at`)
SELECT 'customer-report','access','Customer orders, wallet, point and retention report.',NOW()
WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `module`='customer-report' AND `action`='access');

INSERT INTO `permissions` (`module`,`action`,`description`,`created_at`)
SELECT 'delivery-report','access','Delivery status, courier performance and charge report.',NOW()
WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `module`='delivery-report' AND `action`='access');

INSERT INTO `permissions` (`module`,`action`,`description`,`created_at`)
SELECT 'payment-report','access','Payment method, gateway and status report.',NOW()
WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `module`='payment-report' AND `action`='access');

INSERT INTO `permissions` (`module`,`action`,`description`,`created_at`)
SELECT 'commission-report','access','Vendor and affiliate commission report.',NOW()
WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `module`='commission-report' AND `action`='access');

INSERT INTO `permissions` (`module`,`action`,`description`,`created_at`)
SELECT 'stock-report','access','Low stock, product/variant stock and adjustment report.',NOW()
WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `module`='stock-report' AND `action`='access');

INSERT INTO `permissions` (`module`,`action`,`description`,`created_at`)
SELECT 'reward-report','access','Reward point earning/redeem/expiry report.',NOW()
WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `module`='reward-report' AND `action`='access');

INSERT INTO `permissions` (`module`,`action`,`description`,`created_at`)
SELECT 'coupon-report','access','Coupon usage, discount and customer report.',NOW()
WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `module`='coupon-report' AND `action`='access');

INSERT INTO `permissions` (`module`,`action`,`description`,`created_at`)
SELECT 'export-center','access','Export reports as CSV, Excel-compatible CSV and print/PDF views.',NOW()
WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `module`='export-center' AND `action`='access');

INSERT INTO `permissions` (`module`,`action`,`description`,`created_at`)
SELECT 'roles','access','Role-based staff/admin permissions by module and action.',NOW()
WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `module`='roles' AND `action`='access');

INSERT INTO `permissions` (`module`,`action`,`description`,`created_at`)
SELECT 'staff','access','Admin/staff users, role assignments and access status.',NOW()
WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `module`='staff' AND `action`='access');

INSERT INTO `permissions` (`module`,`action`,`description`,`created_at`)
SELECT 'activity-logs','access','Admin/vendor/customer activity logs with IP and user agent.',NOW()
WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `module`='activity-logs' AND `action`='access');

INSERT INTO `permissions` (`module`,`action`,`description`,`created_at`)
SELECT 'login-history','access','Login success/failure history and device information.',NOW()
WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `module`='login-history' AND `action`='access');

INSERT INTO `permissions` (`module`,`action`,`description`,`created_at`)
SELECT 'backup-restore','access','Database backup, file backup, restore notes and download links.',NOW()
WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `module`='backup-restore' AND `action`='access');

INSERT INTO `permissions` (`module`,`action`,`description`,`created_at`)
SELECT 'file-manager','access','Upload directory browser for banners, logos, products and documents.',NOW()
WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `module`='file-manager' AND `action`='access');

INSERT INTO `permissions` (`module`,`action`,`description`,`created_at`)
SELECT 'api-settings','access','API tokens, callback URLs and webhook settings.',NOW()
WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `module`='api-settings' AND `action`='access');

INSERT INTO `permissions` (`module`,`action`,`description`,`created_at`)
SELECT 'cron-jobs','access','Cron command reference for rank, royalty, statements and abandoned carts.',NOW()
WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `module`='cron-jobs' AND `action`='access');

INSERT INTO `permissions` (`module`,`action`,`description`,`created_at`)
SELECT 'settings','access','Site, company, contact, currency, tax, order and security settings.',NOW()
WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `module`='settings' AND `action`='access');

INSERT INTO `permissions` (`module`,`action`,`description`,`created_at`)
SELECT 'pos','access','Central POS control center for mobile PWA selling, vendor registers, cashiers, scanners, printers and offline sync.',NOW()
WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `module`='pos' AND `action`='access');

INSERT INTO `permissions` (`module`,`action`,`description`,`created_at`)
SELECT 'pos-outlets','access','Vendor-wise branches/outlets for retail counters, delivery pickup and inventory separation.',NOW()
WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `module`='pos-outlets' AND `action`='access');

INSERT INTO `permissions` (`module`,`action`,`description`,`created_at`)
SELECT 'pos-registers','access','Register code, receipt width, printer type, cash drawer, offline mode and assigned vendor/outlet.',NOW()
WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `module`='pos-registers' AND `action`='access');

INSERT INTO `permissions` (`module`,`action`,`description`,`created_at`)
SELECT 'pos-shifts','access','Opening cash, closing cash, expected drawer, variance and cashier shift lifecycle.',NOW()
WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `module`='pos-shifts' AND `action`='access');

INSERT INTO `permissions` (`module`,`action`,`description`,`created_at`)
SELECT 'pos-cash-drawer','access','Cash in/out, drawer reasons, manager approval and audit log.',NOW()
WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `module`='pos-cash-drawer' AND `action`='access');

INSERT INTO `permissions` (`module`,`action`,`description`,`created_at`)
SELECT 'pos-sales','access','Completed POS sales with payment status, cashier, customer, vendor and receipt links.',NOW()
WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `module`='pos-sales' AND `action`='access');

INSERT INTO `permissions` (`module`,`action`,`description`,`created_at`)
SELECT 'pos-sale-items','access','Line-level POS sale item history, SKU, barcode, qty, price and item discounts.',NOW()
WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `module`='pos-sale-items' AND `action`='access');

INSERT INTO `permissions` (`module`,`action`,`description`,`created_at`)
SELECT 'pos-payments','access','Cash, card, bKash, Nagad, wallet and split payment references.',NOW()
WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `module`='pos-payments' AND `action`='access');

INSERT INTO `permissions` (`module`,`action`,`description`,`created_at`)
SELECT 'pos-refunds','access','Return, refund and exchange records with reason, method and approval status.',NOW()
WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `module`='pos-refunds' AND `action`='access');

INSERT INTO `permissions` (`module`,`action`,`description`,`created_at`)
SELECT 'pos-receipts','access','58mm, 80mm and A4 receipt templates with logo, barcode and footer settings.',NOW()
WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `module`='pos-receipts' AND `action`='access');

INSERT INTO `permissions` (`module`,`action`,`description`,`created_at`)
SELECT 'pos-devices','access','Registered mobile browsers, tablets, terminals, barcode devices and last seen tracking.',NOW()
WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `module`='pos-devices' AND `action`='access');

INSERT INTO `permissions` (`module`,`action`,`description`,`created_at`)
SELECT 'pos-staff','access','Assign vendor POS managers/cashiers and granular abilities for selling, refund, shift and reports.',NOW()
WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `module`='pos-staff' AND `action`='access');

INSERT INTO `permissions` (`module`,`action`,`description`,`created_at`)
SELECT 'pos-sync','access','PWA offline sale queue, duplicate protection, sync status and failed payload review.',NOW()
WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `module`='pos-sync' AND `action`='access');

INSERT INTO `permissions` (`module`,`action`,`description`,`created_at`)
SELECT 'pos-reports','access','Cashier, register, payment, day close, tax, refund and product movement reports.',NOW()
WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `module`='pos-reports' AND `action`='access');

INSERT INTO `permissions` (`module`,`action`,`description`,`created_at`)
SELECT 'pos-settings','access','Global and vendor POS settings for scanner, printer, payment methods, offline mode and low-stock warnings.',NOW()
WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `module`='pos-settings' AND `action`='access');

INSERT INTO `permissions` (`module`,`action`,`description`,`created_at`)
SELECT 'feature-map','access','View the platform feature map.',NOW()
WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `module`='feature-map' AND `action`='access');

INSERT INTO `permissions` (`module`,`action`,`description`,`created_at`)
SELECT 'control-panel-check','access','View deployment and environment diagnostics.',NOW()
WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `module`='control-panel-check' AND `action`='access');

-- Conservative default access for the built-in Staff role.
-- Super Admin can change these from Admin > Roles & Staff Permissions.
INSERT IGNORE INTO `role_permissions` (`role_id`,`permission_id`)
SELECT r.id,p.id FROM `roles` r JOIN `permissions` p
WHERE r.code='staff' AND p.action='access' AND p.module IN ('dashboard','orders','order-status','invoices','refunds','returns','products','product-create','product-variants','product-specifications','product-reviews','product-questions','inventory','stock-report','vendors','vendor-verification','customers','customer-addresses','support-chat','notifications','reports','sales-report','customer-report','vendor-sales-report','delivery-report','payment-report','pos','pos-sales','pos-sale-items','pos-payments','pos-refunds','pos-reports','feature-map','control-panel-check');

UPDATE `users` SET `auth_version`=1 WHERE `auth_version` IS NULL OR `auth_version`<1;

-- Force seeded demonstration accounts to replace the shared installation password.
UPDATE `users`
SET `must_change_password`=1
WHERE `password_changed_at` IS NULL
  AND `email` IN (
    'admin@smarttech-omp.com',
    'vendor@smarttech-omp.com',
    'customer@smarttech-omp.com',
    'member@smarttech-omp.com',
    'poscashier@smarttech-omp.com',
    'posmanager@smarttech-omp.com'
  );

COMMIT;
