-- ============================================================
-- Famosity ERP v3 — Safe DB Patch
-- Run this via cPanel phpMyAdmin or mysql CLI
-- ============================================================

SET FOREIGN_KEY_CHECKS = 0;

-- ── 1. Add missing columns to expenses ──────────────────────
ALTER TABLE `expenses` 
  ADD COLUMN IF NOT EXISTS `expense_type` varchar(50) NOT NULL DEFAULT 'miscellaneous' AFTER `expense_date`,
  ADD COLUMN IF NOT EXISTS `gst_rate` decimal(5,2) NOT NULL DEFAULT '0.00' AFTER `gst_amount`,
  ADD COLUMN IF NOT EXISTS `hsn_sac_code` varchar(8) DEFAULT NULL AFTER `gst_rate`,
  ADD COLUMN IF NOT EXISTS `itc_eligible` tinyint(1) NOT NULL DEFAULT '0' AFTER `hsn_sac_code`,
  ADD COLUMN IF NOT EXISTS `gst_applicable` tinyint(1) NOT NULL DEFAULT '0' AFTER `itc_eligible`,
  ADD COLUMN IF NOT EXISTS `notes` text DEFAULT NULL AFTER `receipt`,
  ADD COLUMN IF NOT EXISTS `employee_id` bigint UNSIGNED DEFAULT NULL AFTER `account_id`,
  ADD COLUMN IF NOT EXISTS `created_by` bigint UNSIGNED DEFAULT NULL AFTER `notes`;

-- Update category in expenses (it may not have a default)
UPDATE `expenses` SET `expense_type` = 'miscellaneous' WHERE `expense_type` = '' OR `expense_type` IS NULL;

-- ── 2. Add missing columns to products ──────────────────────
ALTER TABLE `products`
  ADD COLUMN IF NOT EXISTS `show_on_storefront` tinyint(1) NOT NULL DEFAULT '0' AFTER `is_active`,
  ADD COLUMN IF NOT EXISTS `is_available` tinyint(1) NOT NULL DEFAULT '1' AFTER `show_on_storefront`,
  ADD COLUMN IF NOT EXISTS `storefront_badge` varchar(50) DEFAULT NULL AFTER `is_available`,
  ADD COLUMN IF NOT EXISTS `display_order` int NOT NULL DEFAULT '0' AFTER `storefront_badge`;

-- ── 3. Create plans table ────────────────────────────────────
CREATE TABLE IF NOT EXISTS `plans` (
  `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `slug` varchar(100) NOT NULL,
  `description` text DEFAULT NULL,
  `price_monthly` decimal(10,2) NOT NULL DEFAULT '0.00',
  `price_yearly` decimal(10,2) NOT NULL DEFAULT '0.00',
  `features` json DEFAULT NULL,
  `max_users` int NOT NULL DEFAULT '1',
  `max_invoices` int NOT NULL DEFAULT '100',
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `is_popular` tinyint(1) NOT NULL DEFAULT '0',
  `sort_order` int NOT NULL DEFAULT '0',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `plans_slug_unique` (`slug`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── 4. Create super_admins table ────────────────────────────
CREATE TABLE IF NOT EXISTS `super_admins` (
  `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `email` varchar(255) NOT NULL,
  `password` varchar(255) NOT NULL,
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `remember_token` varchar(100) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `super_admins_email_unique` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── 5. Add plan columns to tenants ──────────────────────────
ALTER TABLE `tenants`
  ADD COLUMN IF NOT EXISTS `plan_id` bigint UNSIGNED DEFAULT NULL AFTER `plan`,
  ADD COLUMN IF NOT EXISTS `subscription_starts_at` timestamp NULL DEFAULT NULL AFTER `plan_expires_at`,
  ADD COLUMN IF NOT EXISTS `subscription_status` varchar(30) NOT NULL DEFAULT 'trial' AFTER `subscription_starts_at`,
  ADD COLUMN IF NOT EXISTS `razorpay_subscription_id` varchar(100) DEFAULT NULL AFTER `subscription_status`;

-- ── 6. Create employees table ────────────────────────────────
CREATE TABLE IF NOT EXISTS `employees` (
  `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT,
  `tenant_id` bigint UNSIGNED NOT NULL,
  `employee_code` varchar(50) DEFAULT NULL,
  `name` varchar(255) NOT NULL,
  `email` varchar(255) DEFAULT NULL,
  `phone` varchar(20) DEFAULT NULL,
  `designation` varchar(100) DEFAULT NULL,
  `department` varchar(100) DEFAULT NULL,
  `employment_type` enum('full_time','part_time','contract','intern') NOT NULL DEFAULT 'full_time',
  `joining_date` date DEFAULT NULL,
  `leaving_date` date DEFAULT NULL,
  `basic_salary` decimal(15,2) NOT NULL DEFAULT '0.00',
  `hra` decimal(15,2) NOT NULL DEFAULT '0.00',
  `other_allowances` decimal(15,2) NOT NULL DEFAULT '0.00',
  `pf_employee` decimal(15,2) NOT NULL DEFAULT '0.00',
  `pf_employer` decimal(15,2) NOT NULL DEFAULT '0.00',
  `esi_employee` decimal(15,2) NOT NULL DEFAULT '0.00',
  `esi_employer` decimal(15,2) NOT NULL DEFAULT '0.00',
  `professional_tax` decimal(15,2) NOT NULL DEFAULT '0.00',
  `tds_monthly` decimal(15,2) NOT NULL DEFAULT '0.00',
  `pan` varchar(10) DEFAULT NULL,
  `aadhar` varchar(12) DEFAULT NULL,
  `uan_number` varchar(20) DEFAULT NULL,
  `esi_number` varchar(20) DEFAULT NULL,
  `address` text DEFAULT NULL,
  `bank_name` varchar(100) DEFAULT NULL,
  `bank_account` varchar(30) DEFAULT NULL,
  `bank_ifsc` varchar(15) DEFAULT NULL,
  `photo` varchar(255) DEFAULT NULL,
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `employees_tenant_id_index` (`tenant_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── 7. Create salary_payments table ─────────────────────────
CREATE TABLE IF NOT EXISTS `salary_payments` (
  `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT,
  `tenant_id` bigint UNSIGNED NOT NULL,
  `employee_id` bigint UNSIGNED NOT NULL,
  `salary_month` varchar(7) NOT NULL,
  `financial_year` varchar(7) DEFAULT NULL,
  `working_days` int NOT NULL DEFAULT '26',
  `present_days` int NOT NULL DEFAULT '26',
  `basic_salary` decimal(15,2) NOT NULL DEFAULT '0.00',
  `hra` decimal(15,2) NOT NULL DEFAULT '0.00',
  `other_allowances` decimal(15,2) NOT NULL DEFAULT '0.00',
  `overtime_amount` decimal(15,2) NOT NULL DEFAULT '0.00',
  `bonus` decimal(15,2) NOT NULL DEFAULT '0.00',
  `gross_salary` decimal(15,2) NOT NULL DEFAULT '0.00',
  `pf_employee` decimal(15,2) NOT NULL DEFAULT '0.00',
  `employer_pf` decimal(15,2) NOT NULL DEFAULT '0.00',
  `esi_employee` decimal(15,2) NOT NULL DEFAULT '0.00',
  `employer_esi` decimal(15,2) NOT NULL DEFAULT '0.00',
  `professional_tax` decimal(15,2) NOT NULL DEFAULT '0.00',
  `tds` decimal(15,2) NOT NULL DEFAULT '0.00',
  `other_deductions` decimal(15,2) NOT NULL DEFAULT '0.00',
  `advance_deduction` decimal(15,2) NOT NULL DEFAULT '0.00',
  `total_deductions` decimal(15,2) NOT NULL DEFAULT '0.00',
  `net_salary` decimal(15,2) NOT NULL DEFAULT '0.00',
  `payment_mode` enum('bank','cash','upi','cheque') NOT NULL DEFAULT 'bank',
  `payment_date` date DEFAULT NULL,
  `reference_number` varchar(100) DEFAULT NULL,
  `status` enum('draft','paid','pending') NOT NULL DEFAULT 'draft',
  `notes` text DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `salary_month_employee` (`tenant_id`,`employee_id`,`salary_month`),
  KEY `salary_payments_employee_id_index` (`employee_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── 8. Create storefront_settings table ─────────────────────
CREATE TABLE IF NOT EXISTS `storefront_settings` (
  `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT,
  `tenant_id` bigint UNSIGNED NOT NULL,
  `is_enabled` tinyint(1) NOT NULL DEFAULT '0',
  `store_name` varchar(255) DEFAULT NULL,
  `tagline` varchar(255) DEFAULT NULL,
  `description` text DEFAULT NULL,
  `banner_image` varchar(255) DEFAULT NULL,
  `theme_color` varchar(7) NOT NULL DEFAULT '#4F46E5',
  `show_prices` tinyint(1) NOT NULL DEFAULT '1',
  `allow_orders` tinyint(1) NOT NULL DEFAULT '1',
  `require_phone` tinyint(1) NOT NULL DEFAULT '1',
  `require_address` tinyint(1) NOT NULL DEFAULT '0',
  `minimum_order` decimal(10,2) NOT NULL DEFAULT '0.00',
  `delivery_charge` decimal(10,2) NOT NULL DEFAULT '0.00',
  `delivery_enabled` tinyint(1) NOT NULL DEFAULT '1',
  `pickup_enabled` tinyint(1) NOT NULL DEFAULT '1',
  `whatsapp_number` varchar(20) DEFAULT NULL,
  `auto_whatsapp` tinyint(1) NOT NULL DEFAULT '0',
  `auto_email` tinyint(1) NOT NULL DEFAULT '1',
  `open_hours` json DEFAULT NULL,
  `accept_razorpay` tinyint(1) NOT NULL DEFAULT '0',
  `accept_cod` tinyint(1) NOT NULL DEFAULT '1',
  `currency` varchar(3) NOT NULL DEFAULT 'INR',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `storefront_settings_tenant_id_unique` (`tenant_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── 9. Create storefront_orders table ───────────────────────
CREATE TABLE IF NOT EXISTS `storefront_orders` (
  `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT,
  `tenant_id` bigint UNSIGNED NOT NULL,
  `order_number` varchar(100) NOT NULL,
  `invoice_id` bigint UNSIGNED DEFAULT NULL,
  `customer_name` varchar(255) NOT NULL,
  `customer_phone` varchar(20) NOT NULL,
  `customer_email` varchar(255) DEFAULT NULL,
  `customer_gstin` varchar(15) DEFAULT NULL,
  `delivery_address` text DEFAULT NULL,
  `order_type` enum('pickup','delivery','dine_in') NOT NULL DEFAULT 'pickup',
  `table_number` varchar(20) DEFAULT NULL,
  `subtotal` decimal(15,2) NOT NULL DEFAULT '0.00',
  `discount_amount` decimal(15,2) NOT NULL DEFAULT '0.00',
  `taxable_amount` decimal(15,2) NOT NULL DEFAULT '0.00',
  `cgst_amount` decimal(15,2) NOT NULL DEFAULT '0.00',
  `sgst_amount` decimal(15,2) NOT NULL DEFAULT '0.00',
  `igst_amount` decimal(15,2) NOT NULL DEFAULT '0.00',
  `delivery_charge` decimal(10,2) NOT NULL DEFAULT '0.00',
  `total_amount` decimal(15,2) NOT NULL DEFAULT '0.00',
  `payment_mode` enum('razorpay','cod','upi','cash') NOT NULL DEFAULT 'cod',
  `payment_status` enum('pending','paid','failed','refunded') NOT NULL DEFAULT 'pending',
  `razorpay_order_id` varchar(100) DEFAULT NULL,
  `razorpay_payment_id` varchar(100) DEFAULT NULL,
  `razorpay_signature` varchar(255) DEFAULT NULL,
  `status` enum('pending','confirmed','preparing','ready','delivered','cancelled') NOT NULL DEFAULT 'pending',
  `special_instructions` text DEFAULT NULL,
  `cancellation_reason` text DEFAULT NULL,
  `whatsapp_sent` tinyint(1) NOT NULL DEFAULT '0',
  `email_sent` tinyint(1) NOT NULL DEFAULT '0',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `storefront_orders_tenant_id_index` (`tenant_id`),
  UNIQUE KEY `storefront_orders_order_number` (`tenant_id`,`order_number`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── 10. Create storefront_order_items table ──────────────────
CREATE TABLE IF NOT EXISTS `storefront_order_items` (
  `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT,
  `store_order_id` bigint UNSIGNED NOT NULL,
  `product_id` bigint UNSIGNED DEFAULT NULL,
  `item_name` varchar(255) NOT NULL,
  `unit` varchar(20) NOT NULL DEFAULT 'pcs',
  `quantity` decimal(15,3) NOT NULL,
  `unit_price` decimal(15,2) NOT NULL,
  `gst_rate` decimal(5,2) NOT NULL DEFAULT '0.00',
  `gst_amount` decimal(15,2) NOT NULL DEFAULT '0.00',
  `total_amount` decimal(15,2) NOT NULL,
  `special_note` varchar(255) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `storefront_order_items_order_id` (`store_order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── 11. Seed plans ───────────────────────────────────────────
INSERT IGNORE INTO `plans` (`name`,`slug`,`description`,`price_monthly`,`price_yearly`,`features`,`max_users`,`max_invoices`,`is_active`,`is_popular`,`sort_order`,`created_at`,`updated_at`) VALUES
('Starter','starter','Perfect for small businesses',499.00,4788.00,'["GST-Compliant Tax Invoices","Customer & Product Management","Basic Expense Tracking","Trial Balance & P&L","PDF Invoice Download","GSTR-1 & GSTR-3B Summary","2 Users","100 Invoices/month"]',2,100,1,0,1,NOW(),NOW()),
('Professional','professional','For growing businesses',999.00,9588.00,'["Everything in Starter","Unlimited Invoices","Online Store / Menu Page","Employee & Salary Management","Online Payments (Razorpay)","WhatsApp Invoice","Inventory Tracking","5 Users","REST API Access"]',5,0,1,1,2,NOW(),NOW()),
('Enterprise','enterprise','For large businesses',2499.00,23988.00,'["Everything in Professional","Hotel Booking Module","Restaurant Table Management","Advanced Analytics","25 Users","Priority Support"]',25,0,1,0,3,NOW(),NOW());

-- ── 12. Seed super admin ────────────────────────────────────
INSERT IGNORE INTO `super_admins` (`name`,`email`,`password`,`is_active`,`created_at`,`updated_at`) VALUES
('Super Admin','superadmin@famosity.in','$2y$12$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC8.092tDOmCKKQ3BFXS',1,NOW(),NOW());
-- Default password: SuperAdmin@123 (change immediately!)

-- ── 13. Add migration records ────────────────────────────────
INSERT IGNORE INTO `migrations` (`migration`,`batch`) VALUES
('2024_02_01_000001_create_employees_table',2),
('2024_02_01_000002_create_employees_table',2),
('2024_02_01_000003_create_storefront_tables',2),
('2024_03_01_000001_create_plans_super_admins',2),
('2024_03_01_000002_create_expenses_store_tables',2);

SET FOREIGN_KEY_CHECKS = 1;

SELECT 'DB Patch v3 applied successfully!' AS status;