-- =====================================================
-- POS SYSTEM SETUP SQL
-- Run this file AFTER importing the main database
-- Database: iqraansari__store_new
-- =====================================================

USE `iqraansari__store_new`;

-- Extend payment_method enum in orders table to support POS payment types
ALTER TABLE `orders`
  MODIFY COLUMN `payment_method` enum('cod','cash','card','bank_transfer','other') NOT NULL DEFAULT 'cash';

-- Add POS-specific columns to orders table
ALTER TABLE `orders`
  ADD COLUMN  `sale_channel` enum('online','pos') NOT NULL DEFAULT 'online' AFTER `payment_method`,
  ADD COLUMN  `cashier_id` int(10) UNSIGNED DEFAULT NULL AFTER `sale_channel`,
  ADD COLUMN  `cash_received` decimal(10,2) DEFAULT NULL AFTER `cashier_id`,
  ADD COLUMN  `change_amount` decimal(10,2) DEFAULT NULL AFTER `cash_received`;

-- Extend payment_method enum in payments table
ALTER TABLE `payments`
  MODIFY COLUMN `payment_method` enum('cod','cash','card','bank_transfer','other') NOT NULL DEFAULT 'cash';

-- Table for holding/parking POS orders (put sale on hold)
CREATE TABLE  `pos_held_orders` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `held_by` int(10) UNSIGNED NOT NULL,
  `cart_data` longtext NOT NULL COMMENT 'JSON encoded cart data',
  `customer_name` varchar(100) DEFAULT NULL,
  `customer_phone` varchar(20) DEFAULT NULL,
  `note` varchar(255) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `held_by` (`held_by`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Add index on sale_channel for fast POS reporting
ALTER TABLE `orders` ADD INDEX  `idx_sale_channel` (`sale_channel`);
ALTER TABLE `orders` ADD INDEX  `idx_cashier` (`cashier_id`);

-- =====================================================
-- ADMIN CREDENTIALS
-- =====================================================
-- Reset the main admin password to: admin123
-- (generated with PHP password_hash('admin123', PASSWORD_BCRYPT))
UPDATE `admins`
SET `password` = '$2y$10$pEfYhR5XwXiJH8JCSWOE6uDf5P9U7lE24gCd7SxdyvUtycnVuaC7W'
WHERE `username` = 'admin';

-- Insert a second cashier account if it does not exist yet
-- Username: cashier  |  Password: cashier123
INSERT IGNORE INTO `admins` (`username`, `email`, `password`, `full_name`, `role`, `is_active`)
VALUES (
    'cashier',
    'cashier@hizenpharma.com',
    '$2y$10$NG39D0rgq7.yDJt1HZWUAusFj0rqtqGfv8fOAgez2cfN1fuLRC/.O',
    'POS Cashier',
    'admin',
    1
);

-- =====================================================
-- Insert POS-related settings if not already there
INSERT IGNORE INTO `settings` (`setting_key`, `setting_value`, `setting_type`, `description`) VALUES
('pos_receipt_header', 'Thank you for your purchase!', 'text', 'POS receipt header message'),
('pos_receipt_footer', 'Please retain this receipt for returns.', 'text', 'POS receipt footer message'),
('pos_tax_rate', '0', 'number', 'Tax rate percentage for POS (0 = no tax)'),
('pos_enable_tax', '0', 'boolean', 'Enable tax on POS sales'),
('pos_store_name', 'Hizen Pharma', 'text', 'Store name on POS receipt'),
('pos_store_address', '', 'textarea', 'Store address on POS receipt'),
('pos_store_phone', '', 'text', 'Store phone on POS receipt');