// Environment Aware DB Configuration $envFile = __DIR__ . '/../.env'; if (file_exists($envFile)) { $lines = file($envFile, FILE_IGNORE_NEW_LINES | FILE_SKIP_EMPTY_LINES); foreach ($lines as $line) { $trimmed = trim($line); if ($trimmed === '' || str_starts_with($trimmed, '#')) continue; $parts = explode('=', $trimmed, 2); if (count($parts) === 2) { putenv(trim($parts[0]) . '=' . trim($parts[1], "\"'")); } } } if (!defined('DB_DRIVER')) define('DB_DRIVER', getenv('DB_DRIVER') ?: 'pgsql'); if (!defined('DB_HOST')) define('DB_HOST', getenv('DB_HOST') ?: 'localhost'); if (!defined('DB_PORT')) define('DB_PORT', getenv('DB_PORT') ?: '5432'); if (!defined('DB_USER')) define('DB_USER', getenv('DB_USER') ?: 'postgres'); if (!defined('DB_PASS')) define('DB_PASS', getenv('DB_PASS') ?: 'Harsha@1991'); if (!defined('DB_NAME')) define('DB_NAME', getenv('DB_NAME') ?: 'uptux_pos'); class Database { private static ?PDO $instance = null; public static function getConnection(): PDO { if (self::$instance === null) { try { if (DB_DRIVER === 'pgsql') { // Connect to PostgreSQL $dsnInit = "pgsql:host=" . DB_HOST . ";port=" . DB_PORT . ";dbname=postgres"; $pdoInit = new PDO($dsnInit, DB_USER, DB_PASS, [ PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC ]); // Check if DB exists $stmtDb = $pdoInit->prepare("SELECT 1 FROM pg_database WHERE datname = :dbname"); $stmtDb->execute(['dbname' => DB_NAME]); if ($stmtDb->rowCount() === 0) { $pdoInit->exec("CREATE DATABASE \"" . DB_NAME . "\""); } $dsnWithDb = "pgsql:host=" . DB_HOST . ";port=" . DB_PORT . ";dbname=" . DB_NAME; self::$instance = new PDO($dsnWithDb, DB_USER, DB_PASS, [ PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC ]); self::ensurePostgresTablesExist(self::$instance); self::ensureMigrations(self::$instance); self::ensurePerformanceIndexes(self::$instance); } else { // Connect to MySQL $dsnWithoutDb = "mysql:host=" . DB_HOST . ";charset=utf8mb4"; $pdoInit = new PDO($dsnWithoutDb, DB_USER, DB_PASS, [ PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC ]); $pdoInit->exec("CREATE DATABASE IF NOT EXISTS `" . DB_NAME . "` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;"); $dsnWithDb = "mysql:host=" . DB_HOST . ";dbname=" . DB_NAME . ";charset=utf8mb4"; self::$instance = new PDO($dsnWithDb, DB_USER, DB_PASS, [ PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC ]); self::ensureTablesExist(self::$instance); self::ensureMigrations(self::$instance); self::ensurePerformanceIndexes(self::$instance); } } catch (PDOException $e) { die("Database Connection Error: " . $e->getMessage()); } } return self::$instance; } private static function ensurePostgresTablesExist(PDO $pdo): void { $tableCheck = $pdo->query("SELECT 1 FROM information_schema.tables WHERE table_name = 'products'")->rowCount(); if ($tableCheck === 0) { $schemaFile = __DIR__ . '/../schema_postgres.sql'; if (file_exists($schemaFile)) { $sql = file_get_contents($schemaFile); $pdo->exec($sql); } } } private static function ensureTablesExist(PDO $pdo): void { $tableCheck = $pdo->query("SHOW TABLES LIKE 'products'")->rowCount(); if ($tableCheck === 0) { $schemaFile = __DIR__ . '/../schema.sql'; if (file_exists($schemaFile)) { $sql = file_get_contents($schemaFile); $pdo->exec($sql); } } } private static function ensureMigrations(PDO $pdo): void { try { if (DB_DRIVER === 'pgsql') { $checkCol = $pdo->query("SELECT column_name FROM information_schema.columns WHERE table_name='products' AND column_name='rent_price'")->fetch(); if (!$checkCol) { $pdo->exec("ALTER TABLE products ADD COLUMN rent_price DECIMAL(10,2) NOT NULL DEFAULT 0.00, ADD COLUMN first_fitting_price DECIMAL(10,2) NOT NULL DEFAULT 0.00, ADD COLUMN returnable_deposit DECIMAL(10,2) NOT NULL DEFAULT 0.00 "); } $pdo->exec("CREATE TABLE IF NOT EXISTS fitting_appointments ( id SERIAL PRIMARY KEY, order_id INT NOT NULL REFERENCES orders(id) ON DELETE CASCADE, fitting_date DATE NOT NULL, fitting_time VARCHAR(20) NULL, fitting_type VARCHAR(100) NOT NULL, fitting_stage VARCHAR(100) NULL, fitting_attempt INT NOT NULL DEFAULT 1, fitting_result VARCHAR(100) NULL, alteration_notes TEXT NULL, next_action VARCHAR(100) NULL, tailor_id INT NULL, status VARCHAR(50) NOT NULL DEFAULT 'Confirmed', notes TEXT NULL, created_by INT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP )"); // Ensure new columns exist on PostgreSQL if table was already created try { $pdo->exec("ALTER TABLE fitting_appointments ADD COLUMN IF NOT EXISTS fitting_stage VARCHAR(100) NULL;"); $pdo->exec("ALTER TABLE fitting_appointments ADD COLUMN IF NOT EXISTS fitting_attempt INT NOT NULL DEFAULT 1;"); $pdo->exec("ALTER TABLE fitting_appointments ADD COLUMN IF NOT EXISTS fitting_result VARCHAR(100) NULL;"); $pdo->exec("ALTER TABLE fitting_appointments ADD COLUMN IF NOT EXISTS alteration_notes TEXT NULL;"); $pdo->exec("ALTER TABLE fitting_appointments ADD COLUMN IF NOT EXISTS next_action VARCHAR(100) NULL;"); $pdo->exec("ALTER TABLE orders ALTER COLUMN return_status TYPE VARCHAR(50);"); } catch (Exception $exIn) {} $pdo->exec("CREATE TABLE IF NOT EXISTS order_caution_logs ( id SERIAL PRIMARY KEY, order_id INT NOT NULL REFERENCES orders(id) ON DELETE CASCADE, action_type VARCHAR(50) NOT NULL, amount NUMERIC(10,2) NOT NULL DEFAULT 0.00, payment_method VARCHAR(30) NOT NULL DEFAULT 'cash', nic_number VARCHAR(30) NULL, notes TEXT NULL, user_id INT NULL REFERENCES users(id) ON DELETE SET NULL, created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP )"); $pdo->exec("CREATE TABLE IF NOT EXISTS fitting_records ( id SERIAL PRIMARY KEY, fitting_appointment_id INT NOT NULL REFERENCES fitting_appointments(id) ON DELETE CASCADE, result VARCHAR(100) NULL, alteration_notes TEXT NULL, completed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, completed_by INT NULL )"); $pdo->exec("CREATE TABLE IF NOT EXISTS fund_accounts ( id SERIAL PRIMARY KEY, account_name VARCHAR(100) NOT NULL, account_type VARCHAR(50) NOT NULL, opening_balance NUMERIC(12,2) NOT NULL DEFAULT 0.00, status VARCHAR(20) NOT NULL DEFAULT 'active', created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP )"); $pdo->exec("CREATE TABLE IF NOT EXISTS fund_transactions ( id SERIAL PRIMARY KEY, transaction_date DATE NOT NULL, transaction_type VARCHAR(50) NOT NULL, from_account_id INT NULL REFERENCES fund_accounts(id) ON DELETE SET NULL, to_account_id INT NULL REFERENCES fund_accounts(id) ON DELETE SET NULL, amount NUMERIC(12,2) NOT NULL DEFAULT 0.00, reference VARCHAR(100) NULL, note TEXT NULL, created_by INT NULL REFERENCES users(id) ON DELETE SET NULL, created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP )"); } else { $columns = $pdo->query("SHOW COLUMNS FROM `products` LIKE 'rent_price'")->fetchAll(); if (empty($columns)) { $pdo->exec("ALTER TABLE `products` ADD COLUMN `rent_price` DECIMAL(10,2) NOT NULL DEFAULT 0.00 AFTER `selling_price`, ADD COLUMN `first_fitting_price` DECIMAL(10,2) NOT NULL DEFAULT 0.00 AFTER `rent_price`, ADD COLUMN `returnable_deposit` DECIMAL(10,2) NOT NULL DEFAULT 0.00 AFTER `first_fitting_price` "); } $itemCols = $pdo->query("SHOW COLUMNS FROM `order_items` LIKE 'item_type_option'")->fetchAll(); if (empty($itemCols)) { $pdo->exec("ALTER TABLE `order_items` ADD COLUMN `item_type_option` VARCHAR(50) NOT NULL DEFAULT 'Purchased' AFTER `selected_color`"); } $roleCols = $pdo->query("SHOW COLUMNS FROM `order_items` LIKE 'selected_role'")->fetchAll(); if (empty($roleCols)) { $pdo->exec("ALTER TABLE `order_items` ADD COLUMN `selected_role` VARCHAR(50) NULL DEFAULT 'Groom' AFTER `selected_color`"); } $pdo->exec("ALTER TABLE `order_items` MODIFY COLUMN `selected_size` VARCHAR(150) NULL"); $custCols = $pdo->query("SHOW COLUMNS FROM `orders` LIKE 'customer_id'")->fetchAll(); if (empty($custCols)) { $pdo->exec("ALTER TABLE `orders` ADD COLUMN `customer_id` INT NULL AFTER `customer_phone`, ADD COLUMN `customer_address` TEXT NULL AFTER `customer_id` "); } $dateCols = $pdo->query("SHOW COLUMNS FROM `orders` LIKE 'fit_on_date'")->fetchAll(); if (empty($dateCols)) { $pdo->exec("ALTER TABLE `orders` ADD COLUMN `fit_on_date` DATE NULL AFTER `event_fitting_date`, ADD COLUMN `delivery_date` DATE NULL AFTER `fit_on_date`, ADD COLUMN `function_date` DATE NULL AFTER `delivery_date`, ADD COLUMN `return_date` DATE NULL AFTER `function_date` "); } $cautionCols = $pdo->query("SHOW COLUMNS FROM `orders` LIKE 'caution_deposit_status'")->fetchAll(); if (empty($cautionCols)) { $pdo->exec("ALTER TABLE `orders` ADD COLUMN `caution_deposit_amount` DECIMAL(10,2) NOT NULL DEFAULT 0.00 AFTER `return_status`, ADD COLUMN `caution_deposit_status` ENUM('held', 'refunded', 'forfeited', 'not_required') NOT NULL DEFAULT 'not_required' AFTER `caution_deposit_amount`, ADD COLUMN `caution_refunded_amount` DECIMAL(10,2) NOT NULL DEFAULT 0.00 AFTER `caution_deposit_status`, ADD COLUMN `nic_held` TINYINT(1) NOT NULL DEFAULT 0 AFTER `caution_refunded_amount`, ADD COLUMN `nic_number` VARCHAR(30) NULL AFTER `nic_held` "); } try { $pdo->exec("ALTER TABLE `orders` MODIFY COLUMN `return_status` VARCHAR(50) NOT NULL DEFAULT 'pending_pickup'"); } catch (Exception $exRet) {} $pdo->exec("CREATE TABLE IF NOT EXISTS `fitting_appointments` ( `id` INT AUTO_INCREMENT PRIMARY KEY, `order_id` INT NOT NULL, `fitting_date` DATE NOT NULL, `fitting_time` VARCHAR(20) NULL, `fitting_type` VARCHAR(100) NOT NULL, `fitting_stage` VARCHAR(100) NULL, `fitting_attempt` INT NOT NULL DEFAULT 1, `fitting_result` VARCHAR(100) NULL, `alteration_notes` TEXT NULL, `next_action` VARCHAR(100) NULL, `tailor_id` INT NULL, `status` VARCHAR(50) NOT NULL DEFAULT 'Confirmed', `notes` TEXT NULL, `created_by` INT NULL, `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (`order_id`) REFERENCES `orders`(`id`) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;"); $pdo->exec("CREATE TABLE IF NOT EXISTS `order_caution_logs` ( `id` INT AUTO_INCREMENT PRIMARY KEY, `order_id` INT NOT NULL, `action_type` VARCHAR(50) NOT NULL, `amount` DECIMAL(10,2) NOT NULL DEFAULT 0.00, `payment_method` VARCHAR(30) NOT NULL DEFAULT 'cash', `nic_number` VARCHAR(30) NULL, `notes` TEXT NULL, `user_id` INT NULL, `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (`order_id`) REFERENCES `orders`(`id`) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;"); $pdo->exec("CREATE TABLE IF NOT EXISTS `fund_accounts` ( `id` INT AUTO_INCREMENT PRIMARY KEY, `account_name` VARCHAR(100) NOT NULL, `account_type` VARCHAR(50) NOT NULL, `opening_balance` DECIMAL(12,2) NOT NULL DEFAULT 0.00, `status` VARCHAR(20) NOT NULL DEFAULT 'active', `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;"); $pdo->exec("CREATE TABLE IF NOT EXISTS `fund_transactions` ( `id` INT AUTO_INCREMENT PRIMARY KEY, `transaction_date` DATE NOT NULL, `transaction_type` VARCHAR(50) NOT NULL, `from_account_id` INT NULL, `to_account_id` INT NULL, `amount` DECIMAL(12,2) NOT NULL DEFAULT 0.00, `reference` VARCHAR(100) NULL, `note` TEXT NULL, `created_by` INT NULL, `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (`from_account_id`) REFERENCES `fund_accounts`(`id`) ON DELETE SET NULL, FOREIGN KEY (`to_account_id`) REFERENCES `fund_accounts`(`id`) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;"); } try { if (DB_DRIVER === 'pgsql') { $pdo->exec("ALTER TABLE expenses ADD COLUMN IF NOT EXISTS fund_account_id INT NULL REFERENCES fund_accounts(id) ON DELETE SET NULL;"); } else { $expCols = $pdo->query("SHOW COLUMNS FROM `expenses` LIKE 'fund_account_id'")->fetchAll(); if (empty($expCols)) { $pdo->exec("ALTER TABLE `expenses` ADD COLUMN `fund_account_id` INT NULL AFTER `user_id`"); } } } catch (Exception $exExp) {} // Seed initial fund accounts if empty $checkAccounts = $pdo->query("SELECT COUNT(*) FROM fund_accounts")->fetchColumn(); if ($checkAccounts == 0) { $seedAccounts = [ ['Bank Balance', 'Bank', 850000.00], ['Savings Account', 'Savings', 300000.00], ['Cashier Cash', 'Cashier', 120000.00], ['Petty Cash', 'Petty Cash', 75000.00] ]; $insAcc = $pdo->prepare("INSERT INTO fund_accounts (account_name, account_type, opening_balance) VALUES (:name, :type, :bal)"); foreach ($seedAccounts as $acc) { $insAcc->execute(['name' => $acc[0], 'type' => $acc[1], 'bal' => $acc[2]]); } } } catch (Exception $e) { // Silently ignore if already migrated } } private static function ensurePerformanceIndexes(PDO $pdo): void { self::addIndexIfNotExists($pdo, 'orders', 'idx_orders_created_at', 'created_at'); self::addIndexIfNotExists($pdo, 'orders', 'idx_orders_due_status', 'paid_amount, grand_total'); self::addIndexIfNotExists($pdo, 'orders', 'idx_orders_pmt_date', 'payment_method, created_at'); self::addIndexIfNotExists($pdo, 'orders', 'idx_orders_cust_phone', 'customer_phone'); self::addIndexIfNotExists($pdo, 'orders', 'idx_orders_cust_id', 'customer_id'); self::addIndexIfNotExists($pdo, 'orders', 'idx_orders_return_status', 'return_status, created_at'); self::addIndexIfNotExists($pdo, 'orders', 'idx_orders_fitting_dates', 'event_fitting_date, function_date'); self::addIndexIfNotExists($pdo, 'order_items', 'idx_order_items_order_id', 'order_id'); self::addIndexIfNotExists($pdo, 'order_items', 'idx_order_items_product_id', 'product_id'); self::addIndexIfNotExists($pdo, 'order_items', 'idx_order_items_item_type', 'item_type_option'); self::addIndexIfNotExists($pdo, 'order_payments', 'idx_order_pmts_order_id', 'order_id'); self::addIndexIfNotExists($pdo, 'order_payments', 'idx_order_pmts_method_date', 'payment_method, created_at'); self::addIndexIfNotExists($pdo, 'order_fittings', 'idx_fittings_order_status', 'order_id, status'); self::addIndexIfNotExists($pdo, 'order_fittings', 'idx_fittings_date', 'fitting_date, status'); self::addIndexIfNotExists($pdo, 'order_caution_logs', 'idx_caution_logs_order_id', 'order_id'); self::addIndexIfNotExists($pdo, 'products', 'idx_products_barcode_sku', 'barcode, sku'); self::addIndexIfNotExists($pdo, 'products', 'idx_products_category', 'category_id'); self::addIndexIfNotExists($pdo, 'products', 'idx_products_stock', 'stock_quantity, min_stock_alert'); self::addIndexIfNotExists($pdo, 'customers', 'idx_customers_phone', 'phone'); self::addIndexIfNotExists($pdo, 'customers', 'idx_customers_name', 'name'); self::addIndexIfNotExists($pdo, 'expenses', 'idx_expenses_date', 'expense_date'); self::addIndexIfNotExists($pdo, 'expenses', 'idx_expenses_pmt', 'payment_method, expense_date'); self::addIndexIfNotExists($pdo, 'cheques', 'idx_cheques_status', 'status, cheque_date'); self::addIndexIfNotExists($pdo, 'cheques', 'idx_cheques_order_id', 'order_id'); self::addIndexIfNotExists($pdo, 'sms_logs', 'idx_sms_logs_sent_at', 'sent_at'); } private static function addIndexIfNotExists(PDO $pdo, string $table, string $indexName, string $columns): void { try { if (DB_DRIVER === 'pgsql') { $stmt = $pdo->prepare("SELECT 1 FROM pg_indexes WHERE tablename = :tbl AND indexname = :idx"); $stmt->execute(['tbl' => $table, 'idx' => $indexName]); if ($stmt->rowCount() === 0) { $pdo->exec("CREATE INDEX \"$indexName\" ON \"$table\" ($columns)"); } } else { $stmt = $pdo->prepare("SHOW INDEX FROM `$table` WHERE Key_name = :idx"); $stmt->execute(['idx' => $indexName]); if ($stmt->rowCount() === 0) { $pdo->exec("CREATE INDEX `$indexName` ON `$table` ($columns)"); } } } catch (Exception $e) { // Silently ignore if table or column doesn't exist yet } } } Executive Login - Uptux Wedding Wear Portal

Uptux Wedding Wear

Executive Portal Navigation & POS System

Default Credentials: admin / admin123