-- =============================================================
-- Print Portal - Complete Database Schema with Sample Data
-- Database: print_portal
-- Engine: InnoDB, Charset: utf8mb4
-- Default Passwords: bcrypt hashed
--   admin@printportal.local / admin@123
--   rahul / rahul@123
-- =============================================================

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

CREATE DATABASE IF NOT EXISTS `print_portal` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE `print_portal`;

-- -------------------------------------------------------------
-- Users Table
-- -------------------------------------------------------------
DROP TABLE IF EXISTS `users`;
CREATE TABLE `users` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `username` varchar(50) NOT NULL,
  `email` varchar(255) NOT NULL,
  `mobile` varchar(15) NOT NULL,
  `email_verified_at` timestamp NULL DEFAULT NULL,
  `password` varchar(255) NOT NULL,
  `role` enum('admin','master_distributor','distributor','retailer') NOT NULL DEFAULT 'retailer',
  `parent_id` bigint(20) UNSIGNED DEFAULT NULL,
  `balance` decimal(12,2) NOT NULL DEFAULT 0.00,
  `status` enum('active','inactive','blocked') NOT NULL DEFAULT 'active',
  `shop_name` varchar(255) DEFAULT NULL,
  `address` varchar(500) DEFAULT NULL,
  `state` varchar(100) DEFAULT NULL,
  `city` varchar(100) DEFAULT NULL,
  `pincode` varchar(10) DEFAULT NULL,
  `aadhaar_number` varchar(20) DEFAULT NULL,
  `pan_number` varchar(20) DEFAULT NULL,
  `profile_image` varchar(255) DEFAULT NULL,
  `remember_token` varchar(100) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `users_username_unique` (`username`),
  UNIQUE KEY `users_email_unique` (`email`),
  UNIQUE KEY `users_mobile_unique` (`mobile`),
  KEY `users_parent_id_foreign` (`parent_id`),
  CONSTRAINT `users_parent_id_foreign` FOREIGN KEY (`parent_id`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Default Admin password: admin@123  (bcrypt hash below)
-- Master Distributor: master@123
-- Distributor: dist@123
-- Retailer (rahul): rahul@123
INSERT INTO `users` (`name`, `username`, `email`, `mobile`, `password`, `role`, `balance`, `status`, `shop_name`, `state`, `city`, `created_at`, `updated_at`) VALUES
('Super Admin', 'admin', 'admin@printportal.local', '9999999999', '$2y$12$Dx0AFp02ji.G7vQshrmuFOR8L8gFwLb98u7XmuPQOuICN90Hd7BzS', 'admin', 0.00, 'active', NULL, NULL, NULL, NOW(), NOW()),
('Master Distributor Demo', 'masterdist', 'masterdist@printportal.local', '9888888888', '$2y$12$KuM3rmkGEi4hSwWXIqV9SeOmpUgC2dQDmM4tVl9LFvBhEzPYTXAce', 'master_distributor', 10000.00, 'active', NULL, NULL, NULL, NOW(), NOW()),
('Distributor Demo', 'distributor', 'distributor@printportal.local', '9777777777', '$2y$12$fEKYbnsxw9F0FZQfgGgxKuhpu/95E4i2GUFu9eYrlZsZmcDjPVHfa', 'distributor', 5000.00, 'active', NULL, NULL, NULL, NOW(), NOW()),
('Rahul Retailer', 'rahul', 'rahul@printportal.local', '9000000000', '$2y$12$VxLBA7zNL.MeRkksgrU.lOLE.QU.qXJLwTYbCfRcWmHHo7K5wOL/u', 'retailer', 350.00, 'active', 'Rahul Print Center', 'Uttar Pradesh', 'Lucknow', NOW(), NOW());

-- -------------------------------------------------------------
-- Password Reset Tokens
-- -------------------------------------------------------------
DROP TABLE IF EXISTS `password_reset_tokens`;
CREATE TABLE `password_reset_tokens` (
  `email` varchar(255) NOT NULL,
  `token` varchar(255) NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -------------------------------------------------------------
-- Sessions Table
-- -------------------------------------------------------------
DROP TABLE IF EXISTS `sessions`;
CREATE TABLE `sessions` (
  `id` varchar(255) NOT NULL,
  `user_id` bigint(20) UNSIGNED DEFAULT NULL,
  `ip_address` varchar(45) DEFAULT NULL,
  `user_agent` text DEFAULT NULL,
  `payload` longtext NOT NULL,
  `last_activity` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `sessions_user_id_index` (`user_id`),
  KEY `sessions_last_activity_index` (`last_activity`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -------------------------------------------------------------
-- Services Table
-- -------------------------------------------------------------
DROP TABLE IF EXISTS `services`;
CREATE TABLE `services` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `slug` varchar(255) NOT NULL,
  `category` varchar(100) NOT NULL,
  `icon` varchar(255) DEFAULT NULL,
  `badge` varchar(50) DEFAULT NULL,
  `badge_color` varchar(20) NOT NULL DEFAULT 'green',
  `description` text DEFAULT NULL,
  `default_charge` decimal(10,2) NOT NULL DEFAULT 0.00,
  `default_commission` decimal(10,2) NOT NULL DEFAULT 0.00,
  `status` enum('active','inactive') NOT NULL DEFAULT 'active',
  `sort_order` int(11) NOT NULL DEFAULT 0,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `services_slug_unique` (`slug`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `services` (`name`, `slug`, `category`, `icon`, `badge`, `badge_color`, `default_charge`, `default_commission`, `status`, `sort_order`, `created_at`, `updated_at`) VALUES
('Voter PDF Instant', 'voter-pdf-instant', 'Voter Services', 'fa-id-card', 'INSTANT', 'success', 25.00, 5.00, 'active', 1, NOW(), NOW()),
('Voter PDF Download (OTP)', 'voter-pdf-download-otp', 'Voter Services', 'fa-file-pdf', 'OTP', 'info', 20.00, 3.00, 'active', 2, NOW(), NOW()),
('Voter Mobile Link (No OTP)', 'voter-mobile-link-no-otp', 'Voter Services', 'fa-mobile-alt', 'HOT', 'danger', 30.00, 6.00, 'active', 3, NOW(), NOW()),
('Voter Mobile Link (eSign)', 'voter-mobile-link-esign', 'Voter Services', 'fa-signature', 'ESIGN', 'success', 35.00, 7.00, 'active', 4, NOW(), NOW()),
('Aadhaar PDF (Biometric)', 'aadhaar-pdf-biometric', 'Aadhaar Services', 'fa-fingerprint', 'LIVE', 'success', 50.00, 10.00, 'active', 5, NOW(), NOW()),
('Aadhaar PDF (Finger)', 'aadhaar-pdf-finger', 'Aadhaar Services', 'fa-fingerprint', 'API', 'primary', 45.00, 8.00, 'active', 6, NOW(), NOW()),
('Aadhaar PDF (OTP)', 'aadhaar-pdf-otp', 'Aadhaar Services', 'fa-key', 'OTP', 'warning', 40.00, 7.00, 'active', 7, NOW(), NOW()),
('RC PDF Instant', 'rc-pdf-instant', 'Vehicle / RC / DL', 'fa-car', 'HOT', 'danger', 25.00, 5.00, 'active', 8, NOW(), NOW()),
('DL PDF Instant', 'dl-pdf-instant', 'Vehicle / RC / DL', 'fa-id-card-alt', 'INSTANT', 'success', 25.00, 5.00, 'active', 9, NOW(), NOW()),
('Chassis to RC Find', 'chassis-to-rc-find', 'Vehicle / RC / DL', 'fa-search', 'NEW', 'success', 50.00, 10.00, 'active', 10, NOW(), NOW()),
('Ayushman (PMJAY) Verify', 'ayushman-pmjay-verify', 'Government Schemes', 'fa-hands-helping', 'NEW', 'danger', 10.00, 2.00, 'active', 11, NOW(), NOW()),
('e-Shram Details', 'e-shram-details', 'Government Schemes', 'fa-hard-hat', 'LIVE', 'success', 15.00, 3.00, 'active', 12, NOW(), NOW()),
('RTPS Certificate Bihar', 'rtps-certificate-bihar', 'Government Schemes', 'fa-certificate', 'NEW', 'success', 30.00, 5.00, 'active', 13, NOW(), NOW()),
('DBT Farmer PDF & Status', 'dbt-farmer-pdf-status', 'Government Schemes', 'fa-tractor', 'LIVE', 'success', 20.00, 4.00, 'active', 14, NOW(), NOW()),
('LL Exam Instant Pass', 'll-exam-instant-pass', 'Exam & Certificate', 'fa-graduation-cap', 'INSTANT', 'success', 100.00, 20.00, 'active', 15, NOW(), NOW()),
('PUC Certificate', 'puc-certificate', 'Exam & Certificate', 'fa-leaf', 'NEW', 'success', 75.00, 15.00, 'active', 16, NOW(), NOW()),
('Aadhaar Card Print', 'aadhaar-card-print', 'Manual Services', 'fa-print', 'MANUAL', 'dark', 25.00, 5.00, 'active', 17, NOW(), NOW()),
('Voter PDF 2026', 'voter-pdf-2026', 'Manual Services', 'fa-file-pdf', 'MANUAL', 'dark', 30.00, 6.00, 'active', 18, NOW(), NOW()),
('PAN Card Print', 'pan-card-print', 'Manual Services', 'fa-id-badge', 'MANUAL', 'dark', 30.00, 6.00, 'active', 19, NOW(), NOW());

-- -------------------------------------------------------------
-- User Service Charges (Per-user custom commission & charge)
-- -------------------------------------------------------------
DROP TABLE IF EXISTS `user_service_charges`;
CREATE TABLE `user_service_charges` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) UNSIGNED NOT NULL,
  `service_id` bigint(20) UNSIGNED NOT NULL,
  `charge` decimal(10,2) NOT NULL DEFAULT 0.00,
  `commission` decimal(10,2) NOT NULL DEFAULT 0.00,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `user_service_unique` (`user_id`,`service_id`),
  KEY `user_service_charges_user_id_foreign` (`user_id`),
  KEY `user_service_charges_service_id_foreign` (`service_id`),
  CONSTRAINT `user_service_charges_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `user_service_charges_service_id_foreign` FOREIGN KEY (`service_id`) REFERENCES `services` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -------------------------------------------------------------
-- Transactions Table
-- -------------------------------------------------------------
DROP TABLE IF EXISTS `transactions`;
CREATE TABLE `transactions` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) UNSIGNED NOT NULL,
  `reference_no` varchar(255) NOT NULL,
  `type` enum('credit','debit','refund','commission') NOT NULL,
  `amount` decimal(12,2) NOT NULL,
  `balance_before` decimal(12,2) NOT NULL,
  `balance_after` decimal(12,2) NOT NULL,
  `description` varchar(255) DEFAULT NULL,
  `service_request_id` bigint(20) UNSIGNED DEFAULT NULL,
  `admin_id` bigint(20) UNSIGNED DEFAULT NULL,
  `status` enum('pending','success','failed') NOT NULL DEFAULT 'success',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `transactions_reference_no_unique` (`reference_no`),
  KEY `transactions_user_id_foreign` (`user_id`),
  CONSTRAINT `transactions_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -------------------------------------------------------------
-- Service Requests Table
-- -------------------------------------------------------------
DROP TABLE IF EXISTS `service_requests`;
CREATE TABLE `service_requests` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `request_no` varchar(255) NOT NULL,
  `user_id` bigint(20) UNSIGNED NOT NULL,
  `service_id` bigint(20) UNSIGNED NOT NULL,
  `input_data` json DEFAULT NULL,
  `response_data` json DEFAULT NULL,
  `charge` decimal(10,2) NOT NULL DEFAULT 0.00,
  `commission` decimal(10,2) NOT NULL DEFAULT 0.00,
  `status` enum('pending','processing','success','rejected','refunded') NOT NULL DEFAULT 'pending',
  `reject_reason` varchar(255) DEFAULT NULL,
  `result_file` varchar(255) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `service_requests_request_no_unique` (`request_no`),
  KEY `service_requests_user_id_foreign` (`user_id`),
  KEY `service_requests_service_id_foreign` (`service_id`),
  KEY `service_requests_status_index` (`status`),
  CONSTRAINT `service_requests_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `service_requests_service_id_foreign` FOREIGN KEY (`service_id`) REFERENCES `services` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -------------------------------------------------------------
-- Settings Table
-- -------------------------------------------------------------
DROP TABLE IF EXISTS `settings`;
CREATE TABLE `settings` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `key` varchar(255) NOT NULL,
  `value` text DEFAULT NULL,
  `group` varchar(255) NOT NULL DEFAULT 'general',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `settings_key_unique` (`key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `settings` (`key`, `value`, `group`, `created_at`, `updated_at`) VALUES
('site_name', 'Print Portal', 'general', NOW(), NOW()),
('site_logo', '', 'general', NOW(), NOW()),
('contact_email', 'support@printportal.local', 'general', NOW(), NOW()),
('contact_mobile', '9999999999', 'general', NOW(), NOW());

-- -------------------------------------------------------------
-- Migrations Table (for Laravel)
-- -------------------------------------------------------------
DROP TABLE IF EXISTS `migrations`;
CREATE TABLE `migrations` (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `migration` varchar(255) NOT NULL,
  `batch` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `migrations` (`migration`, `batch`) VALUES
('2026_01_01_000001_create_users_table', 1),
('2026_01_01_000002_create_password_resets_table', 1),
('2026_01_01_000003_create_services_table', 1),
('2026_01_01_000004_create_user_service_charges_table', 1),
('2026_01_01_000005_create_transactions_table', 1),
('2026_01_01_000006_create_service_requests_table', 1),
('2026_01_01_000007_create_settings_table', 1),
('2026_01_01_000008_create_sessions_table', 1);

SET FOREIGN_KEY_CHECKS = 1;

-- =============================================================
-- DONE. Login Credentials:
--   Admin:               admin@printportal.local       / admin@123
--   Master Distributor:  masterdist@printportal.local  / master@123
--   Distributor:         distributor@printportal.local / dist@123
--   Retailer:            rahul / rahul@123
-- =============================================================