-- Update database structure for existing installation

-- Add users table if it doesn't exist
CREATE TABLE IF NOT EXISTS `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(50) NOT NULL,
  `password` varchar(255) NOT NULL,
  `role` enum('admin','kasir') NOT NULL DEFAULT 'kasir',
  `name` varchar(100) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `username` (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Insert default admin and kasir users if they don't exist
INSERT IGNORE INTO `users` (`id`, `username`, `password`, `role`, `name`, `created_at`) VALUES
(1, 'admin', '$2y$10$WCYqVsh938PWSyE4IleHmO.g26jSCzuJXD/pWc0qVJMf6nf4Nju4.', 'admin', 'Administrator', '2025-05-03 18:17:58'),
(2, 'kasir', '$2y$10$WCYqVsh938PWSyE4IleHmO.g26jSCzuJXD/pWc0qVJMf6nf4Nju4.', 'kasir', 'Kasir', '2025-12-26 23:00:00');

-- Add user_id column to laporan table if it doesn't exist
SET @column_exists = (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS 
                      WHERE TABLE_SCHEMA = DATABASE() 
                      AND TABLE_NAME = 'laporan' 
                      AND COLUMN_NAME = 'user_id');
                      
SET @sql = IF(@column_exists = 0, 
              'ALTER TABLE `laporan` ADD COLUMN `user_id` int(11) DEFAULT NULL', 
              'SELECT "Column user_id already exists"');
              
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Add is_manual column to produk table if it doesn't exist
SET @column_exists2 = (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS 
                       WHERE TABLE_SCHEMA = DATABASE() 
                       AND TABLE_NAME = 'produk' 
                       AND COLUMN_NAME = 'is_manual');
                       
SET @sql2 = IF(@column_exists2 = 0, 
               'ALTER TABLE `produk` ADD COLUMN `is_manual` tinyint(1) DEFAULT 0', 
               'SELECT "Column is_manual already exists"');
               
PREPARE stmt2 FROM @sql2;
EXECUTE stmt2;
DEALLOCATE PREPARE stmt2;

-- Add harga_satuan and subtotal columns to keranjang table if they don't exist
SET @column_exists3 = (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS 
                       WHERE TABLE_SCHEMA = DATABASE() 
                       AND TABLE_NAME = 'keranjang' 
                       AND COLUMN_NAME = 'harga_satuan');
                       
SET @sql3 = IF(@column_exists3 = 0, 
               'ALTER TABLE `keranjang` ADD COLUMN `harga_satuan` int(11) NOT NULL DEFAULT 0', 
               'SELECT "Column harga_satuan already exists"');
               
PREPARE stmt3 FROM @sql3;
EXECUTE stmt3;
DEALLOCATE PREPARE stmt3;

SET @column_exists4 = (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS 
                       WHERE TABLE_SCHEMA = DATABASE() 
                       AND TABLE_NAME = 'keranjang' 
                       AND COLUMN_NAME = 'subtotal');
                       
SET @sql4 = IF(@column_exists4 = 0, 
               'ALTER TABLE `keranjang` ADD COLUMN `subtotal` int(11) NOT NULL DEFAULT 0', 
               'SELECT "Column subtotal already exists"');
               
PREPARE stmt4 FROM @sql4;
EXECUTE stmt4;
DEALLOCATE PREPARE stmt4;

-- Add harga_satuan and subtotal columns to tb_nota table if they don't exist
SET @column_exists5 = (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS 
                       WHERE TABLE_SCHEMA = DATABASE() 
                       AND TABLE_NAME = 'tb_nota' 
                       AND COLUMN_NAME = 'harga_satuan');
                       
SET @sql5 = IF(@column_exists5 = 0, 
               'ALTER TABLE `tb_nota` ADD COLUMN `harga_satuan` int(11) NOT NULL DEFAULT 0', 
               'SELECT "Column harga_satuan already exists"');
               
PREPARE stmt5 FROM @sql5;
EXECUTE stmt5;
DEALLOCATE PREPARE stmt5;

SET @column_exists6 = (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS 
                       WHERE TABLE_SCHEMA = DATABASE() 
                       AND TABLE_NAME = 'tb_nota' 
                       AND COLUMN_NAME = 'subtotal');
                       
SET @sql6 = IF(@column_exists6 = 0, 
               'ALTER TABLE `tb_nota` ADD COLUMN `subtotal` int(11) NOT NULL DEFAULT 0', 
               'SELECT "Column subtotal already exists"');
               
PREPARE stmt6 FROM @sql6;
EXECUTE stmt6;
DEALLOCATE PREPARE stmt6;

-- Add tanggal_transaksi column to tb_nota table if it doesn't exist
SET @column_exists7 = (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS 
                       WHERE TABLE_SCHEMA = DATABASE() 
                       AND TABLE_NAME = 'tb_nota' 
                       AND COLUMN_NAME = 'tanggal_transaksi');
                       
SET @sql7 = IF(@column_exists7 = 0, 
               'ALTER TABLE `tb_nota` ADD COLUMN `tanggal_transaksi` timestamp NOT NULL DEFAULT current_timestamp()', 
               'SELECT "Column tanggal_transaksi already exists"');
               
PREPARE stmt7 FROM @sql7;
EXECUTE stmt7;
DEALLOCATE PREPARE stmt7;

-- Create retur_barang table if it doesn't exist
CREATE TABLE IF NOT EXISTS `retur_barang` (
  `idretur` int(11) NOT NULL AUTO_INCREMENT,
  `no_nota` varchar(100) NOT NULL,
  `idproduk` int(11) NOT NULL,
  `quantity` int(11) NOT NULL,
  `harga_satuan` int(11) NOT NULL,
  `subtotal` int(11) NOT NULL,
  `tanggal_retur` timestamp NOT NULL DEFAULT current_timestamp(),
  `keterangan` text DEFAULT NULL,
  `user_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`idretur`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Create device_settings table if it doesn't exist
CREATE TABLE IF NOT EXISTS `device_settings` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `printer_name` varchar(100) DEFAULT NULL,
  `printer_ip` varchar(15) DEFAULT NULL,
  `printer_port` int(11) DEFAULT 9100,
  `printer_type` enum('usb','bluetooth','network') DEFAULT 'usb',
  `scanner_type` enum('usb','bluetooth') DEFAULT 'usb',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Insert default device settings if they don't exist
INSERT IGNORE INTO `device_settings` (`id`, `printer_name`, `printer_ip`, `printer_port`, `printer_type`, `scanner_type`, `created_at`, `updated_at`) VALUES
(1, 'Default Printer', '192.168.1.100', 9100, 'usb', 'usb', '2025-12-26 23:00:00', '2025-12-26 23:00:00');