-- database.sql
-- Run this script in phpMyAdmin or your MySQL shell to initialize the Maurya Mobile ERP database structures.

CREATE DATABASE IF NOT EXISTS `maurya_mobile_erp` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
USE `maurya_mobile_erp`;

-- 1. System configuration
CREATE TABLE IF NOT EXISTS `app_config` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `appName` varchar(100) DEFAULT 'Maurya Mobile ERP',
  `appLogoUrl` varchar(255) DEFAULT '',
  `globalNotification` text DEFAULT '',
  `defaultPhpBackendUrl` varchar(255) DEFAULT '',
  `marqueeText` text DEFAULT '',
  `notificationTitle` varchar(255) DEFAULT 'Merchant System Update',
  `notificationBody` text DEFAULT '',
  `notificationActive` tinyint(1) DEFAULT 1,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `app_config` (`id`, `appName`, `appLogoUrl`, `globalNotification`, `defaultPhpBackendUrl`, `marqueeText`, `notificationTitle`, `notificationBody`, `notificationActive`) 
VALUES (1, 'Maurya Mobile ERP', '', 'Welcome to Maurya Mobile ERP! Enjoy seamless POS, Repairs, and secure KYC options.', '', '🔥 SPECIAL OFFER: Subscription Plans scaled down! 1 Month @ ₹99, 3 Months @ ₹269, 1 Year @ ₹999 + 18% GST. Check out securely via Cashfree payments standard portal. 🔥', 'Merchant System Update', 'New integrated Cashfree Payments gateway is fully functional on your billing portal. Select under Subscription plans to renew online instantly.', 1)
ON DUPLICATE KEY UPDATE `id` = 1;

-- 2. Shop Owners - Cloud Model
CREATE TABLE IF NOT EXISTS `shop_owners` (
  `id` varchar(100) NOT NULL,
  `name` varchar(150) NOT NULL,
  `ownerName` varchar(150) NOT NULL,
  `mobile` varchar(20) NOT NULL,
  `address` text NOT NULL,
  `password` varchar(100) DEFAULT '123456',
  `logoUrl` varchar(255) DEFAULT '',
  `activeSubscription` tinyint(1) DEFAULT 1,
  `subscriptionExpires` bigint(20) DEFAULT 0,
  `status` varchar(20) DEFAULT 'ACTIVE',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- 3. Point Of Sales Transactions
CREATE TABLE IF NOT EXISTS `pos_sales` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `shopOwnerId` varchar(100) NOT NULL,
  `customerName` varchar(100) NOT NULL,
  `customerMobile` varchar(20) NOT NULL,
  `brand` varchar(50) NOT NULL,
  `model` varchar(100) NOT NULL,
  `price` double(10,2) NOT NULL,
  `paymentMode` varchar(20) NOT NULL,
  `timestamp` bigint(20) NOT NULL,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`shopOwnerId`) REFERENCES `shop_owners` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- 4. Repair Management Jobs
CREATE TABLE IF NOT EXISTS `repair_jobs` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `shopOwnerId` varchar(100) NOT NULL,
  `customerName` varchar(100) NOT NULL,
  `customerMobile` varchar(20) NOT NULL,
  `brand` varchar(50) NOT NULL,
  `model` varchar(100) NOT NULL,
  `issueDescription` text NOT NULL,
  `estimatedCost` double(10,2) NOT NULL,
  `status` varchar(20) DEFAULT 'PENDING', -- PENDING, IN_PROGRESS, READY, DELIVERED
  `assignedTechnician` varchar(150) DEFAULT '',
  `partsUsedDetails` text DEFAULT NULL,
  `partsCost` double(10,2) DEFAULT 0.0,
  `timestamp` bigint(20) NOT NULL,
  `updatedTimestamp` bigint(20) NOT NULL,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`shopOwnerId`) REFERENCES `shop_owners` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- 5. Old Mobile Purchase KYC
CREATE TABLE IF NOT EXISTS `old_mobile_kycs` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `shopOwnerId` varchar(100) NOT NULL,
  `customerName` varchar(100) NOT NULL,
  `customerAddress` text NOT NULL,
  `customerMobile` varchar(15) NOT NULL,
  `alternateMobile` varchar(15) DEFAULT '',
  `aadharNumber` varchar(20) NOT NULL,
  `brand` varchar(50) NOT NULL,
  `model` varchar(100) NOT NULL,
  `imei1` varchar(50) NOT NULL,
  `imei2` varchar(50) DEFAULT '',
  `serialNumber` varchar(100) NOT NULL,
  `customerPhotoUri` text DEFAULT NULL,
  `aadharPhotoUri` text DEFAULT NULL,
  `billPhotoUri` text DEFAULT NULL,
  `timestamp` bigint(20) NOT NULL,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`shopOwnerId`) REFERENCES `shop_owners` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- 6. Unlocking, Flashing & Unbricking KYC Checklist
CREATE TABLE IF NOT EXISTS `flashing_kycs` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `shopOwnerId` varchar(100) NOT NULL,
  `customerName` varchar(100) NOT NULL,
  `customerAddress` text NOT NULL,
  `customerMobile` varchar(15) NOT NULL,
  `alternateMobile` varchar(15) DEFAULT '',
  `aadharNumber` varchar(20) NOT NULL,
  `phoneDetails` varchar(255) NOT NULL,
  `imei1` varchar(50) DEFAULT '',
  `imei2` varchar(50) DEFAULT '',
  `complianceOperationType` varchar(100) DEFAULT 'Flashing/Unlock',
  `customerPhotoUri` text DEFAULT NULL,
  `phonePhotoUri` text DEFAULT NULL,
  `aadharPhotoUri` text DEFAULT NULL,
  `billPhotoUri` text DEFAULT NULL,
  `shortVideoUri` text DEFAULT NULL,
  `timestamp` bigint(20) NOT NULL,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`shopOwnerId`) REFERENCES `shop_owners` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- 7. Promotion Dynamic Slider Banners
CREATE TABLE IF NOT EXISTS `promotion_banners` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(150) NOT NULL,
  `subtitle` varchar(255) DEFAULT '',
  `imageUrl` varchar(255) DEFAULT '',
  `isActive` tinyint(1) DEFAULT 1,
  `linkUrl` varchar(150) DEFAULT '',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- 9. Spare Parts Inventory
CREATE TABLE IF NOT EXISTS `spare_parts` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `shopOwnerId` varchar(100) NOT NULL,
  `partName` varchar(150) NOT NULL,
  `quantity` int(11) NOT NULL DEFAULT 0,
  `costPrice` double(10,2) NOT NULL DEFAULT 0.0,
  `sellingPrice` double(10,2) NOT NULL DEFAULT 0.0,
  `brandCompat` varchar(100) DEFAULT 'Universal',
  `timestamp` bigint(20) NOT NULL,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`shopOwnerId`) REFERENCES `shop_owners` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `support_tickets` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `shopOwnerId` varchar(100) NOT NULL,
  `shopName` varchar(150) NOT NULL,
  `subject` varchar(200) NOT NULL,
  `message` text NOT NULL,
  `isBug` tinyint(1) DEFAULT 0,
  `status` varchar(20) DEFAULT 'OPEN', -- OPEN, RESOLVED
  `timestamp` bigint(20) NOT NULL,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`shopOwnerId`) REFERENCES `shop_owners` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `admin_users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(150) NOT NULL,
  `password` varchar(150) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `admin_users` (`username`, `password`) VALUES ('superadmin', 'RIMMY-001-9955-5566-8765') ON DUPLICATE KEY UPDATE `password` = 'RIMMY-001-9955-5566-8765';

CREATE TABLE IF NOT EXISTS `technicians` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `shopOwnerId` varchar(100) NOT NULL,
  `name` varchar(150) NOT NULL,
  `specialization` varchar(200) DEFAULT '',
  `timestamp` bigint(20) NOT NULL,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`shopOwnerId`) REFERENCES `shop_owners` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;