File "database.php"

Full path: /home/itsevak/public_html/prepaiddev.itsevak.com/config/database.php
File size: 17.53 B (17.53 KB bytes)
MIME-type: text/x-php
Charset: utf-8

Download   Open   Edit   Advanced Editor   Back

<?php
require_once __DIR__ . '/../vendor/autoload.php';

use Dotenv\Dotenv;

// Load environment variables
$dotenv = Dotenv::createImmutable(__DIR__ . '/..');
$dotenv->load();

class Database {
    private static $instance = null;
    private $connection;
    
    private function __construct() {
        try {
            $this->connection = new PDO(
                "mysql:host=" . $_ENV['DB_HOST'] . ";dbname=" . $_ENV['DB_NAME'] . ";charset=utf8mb4",
                $_ENV['DB_USER'],
                $_ENV['DB_PASS'],
                [
                    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
                    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
                    PDO::ATTR_EMULATE_PREPARES => false
                ]
            );
        } catch (PDOException $e) {
            die("Database connection failed: " . $e->getMessage());
        }
    }
    
    public static function getInstance() {
        if (self::$instance === null) {
            self::$instance = new self();
        }
        return self::$instance;
    }
    
    public function getConnection() {
        return $this->connection;
    }
    
    public function query($sql, $params = []) {
        $stmt = $this->connection->prepare($sql);
        $stmt->execute($params);
        return $stmt;
    }
    
    public function fetch($sql, $params = []) {
        return $this->query($sql, $params)->fetch();
    }
    
    public function fetchAll($sql, $params = []) {
        return $this->query($sql, $params)->fetchAll();
    }
    
    public function lastInsertId() {
        return $this->connection->lastInsertId();
    }
}

// Initialize database tables
function initializeDatabase() {
    $db = Database::getInstance();
    $pdo = $db->getConnection();
    
    // Auto Owners table
    $sql = "CREATE TABLE IF NOT EXISTS auto_owners (
        id INT AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(255) NOT NULL,
        email VARCHAR(255) UNIQUE NOT NULL,
        password VARCHAR(255),
        google_id VARCHAR(255),
        phone VARCHAR(20),
        remember_token VARCHAR(255) NULL,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        status ENUM('active', 'inactive', 'pending') DEFAULT 'pending',
        INDEX idx_email (email),
        INDEX idx_google_id (google_id),
        INDEX idx_status (status)
    )";
    $pdo->exec($sql);

    // Passengers table
    $sql = "CREATE TABLE IF NOT EXISTS passengers (
        id INT AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(255) NOT NULL,
        email VARCHAR(255) UNIQUE NOT NULL,
        password VARCHAR(255),
        google_id VARCHAR(255),
        phone VARCHAR(20),
        remember_token VARCHAR(255) NULL,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        status ENUM('active', 'inactive', 'pending') DEFAULT 'pending',
        INDEX idx_email (email),
        INDEX idx_google_id (google_id),
        INDEX idx_status (status)
    )";
    $pdo->exec($sql);
    
    // Auto_Rickshaws table (references auto_owners for new installs)
    $sql = "CREATE TABLE IF NOT EXISTS auto_rickshaws (
        id INT AUTO_INCREMENT PRIMARY KEY,
        owner_id INT NOT NULL,
        number_plate VARCHAR(20) UNIQUE NOT NULL,
        unique_local_id VARCHAR(50) UNIQUE NOT NULL,
        document_photo_1 VARCHAR(255),
        document_photo_2 VARCHAR(255),
        document_photo_3 VARCHAR(255),
        qr_code_path VARCHAR(255),
        qr_code_data TEXT,
        status ENUM('active', 'inactive', 'pending_approval') DEFAULT 'pending_approval',
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        CONSTRAINT fk_auto_rickshaws_owner FOREIGN KEY (owner_id) REFERENCES auto_owners(id) ON DELETE CASCADE,
        INDEX idx_owner_id (owner_id),
        INDEX idx_number_plate (number_plate),
        INDEX idx_unique_local_id (unique_local_id),
        INDEX idx_status (status)
    )";
    $pdo->exec($sql);
    
    // Sessions table for better session management (generic to both types)
    $sql = "CREATE TABLE IF NOT EXISTS user_sessions (
        id INT AUTO_INCREMENT PRIMARY KEY,
        account_id INT NOT NULL,
        user_type ENUM('auto_owner','passenger','operator','validator','admin','super_admin') NOT NULL,
        session_id VARCHAR(255) UNIQUE NOT NULL,
        ip_address VARCHAR(45),
        user_agent TEXT,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        expires_at TIMESTAMP NULL DEFAULT NULL,
        INDEX idx_session_id (session_id),
        INDEX idx_account_id (account_id),
        INDEX idx_user_type (user_type),
        INDEX idx_expires_at (expires_at)
    )";
    $pdo->exec($sql);

    // Operators table (backend users for managing operations)
    $sql = "CREATE TABLE IF NOT EXISTS operators (
        id INT AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(255) NOT NULL,
        email VARCHAR(255) UNIQUE NOT NULL,
        password VARCHAR(255) NOT NULL,
        phone VARCHAR(20),
        remember_token VARCHAR(255) NULL,
        status ENUM('active', 'inactive', 'pending') DEFAULT 'pending',
        created_by INT NULL,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        INDEX idx_email (email),
        INDEX idx_status (status),
        INDEX idx_created_by (created_by)
    )";
    $pdo->exec($sql);

    // Validators table (backend users for validation tasks)
    $sql = "CREATE TABLE IF NOT EXISTS validators (
        id INT AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(255) NOT NULL,
        email VARCHAR(255) UNIQUE NOT NULL,
        password VARCHAR(255) NOT NULL,
        phone VARCHAR(20),
        remember_token VARCHAR(255) NULL,
        status ENUM('active', 'inactive', 'pending') DEFAULT 'pending',
        created_by INT NULL,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        INDEX idx_email (email),
        INDEX idx_status (status),
        INDEX idx_created_by (created_by)
    )";
    $pdo->exec($sql);

    // Admins table (backend users for system administration)
    $sql = "CREATE TABLE IF NOT EXISTS admins (
        id INT AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(255) NOT NULL,
        email VARCHAR(255) UNIQUE NOT NULL,
        password VARCHAR(255) NOT NULL,
        phone VARCHAR(20),
        remember_token VARCHAR(255) NULL,
        status ENUM('active', 'inactive', 'pending') DEFAULT 'pending',
        created_by INT NULL,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        INDEX idx_email (email),
        INDEX idx_status (status),
        INDEX idx_created_by (created_by)
    )";
    $pdo->exec($sql);

    // Super Admins table (highest level backend users)
    $sql = "CREATE TABLE IF NOT EXISTS super_admins (
        id INT AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(255) NOT NULL,
        email VARCHAR(255) UNIQUE NOT NULL,
        password VARCHAR(255) NOT NULL,
        phone VARCHAR(20),
        remember_token VARCHAR(255) NULL,
        status ENUM('active', 'inactive', 'pending') DEFAULT 'pending',
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        INDEX idx_email (email),
        INDEX idx_status (status)
    )";
    $pdo->exec($sql);

    // From Locations table for ride pickup points (only SuperAdmin can manage)
    $sql = "CREATE TABLE IF NOT EXISTS from_locations (
        id INT AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(255) NOT NULL,
        address TEXT NOT NULL,
        city VARCHAR(100) NOT NULL,
        state VARCHAR(100) NOT NULL,
        pincode VARCHAR(10),
        latitude DECIMAL(10, 8) NULL,
        longitude DECIMAL(11, 8) NULL,
        description TEXT,
        status ENUM('active', 'inactive') DEFAULT 'active',
        created_by INT NOT NULL,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        INDEX idx_name (name),
        INDEX idx_city (city),
        INDEX idx_status (status),
        INDEX idx_created_by (created_by),
        INDEX idx_created_by (created_by)
    )";
    $pdo->exec($sql);

    // To Locations table for ride destinations (Admin can manage)
    $sql = "CREATE TABLE IF NOT EXISTS to_locations (
        id INT AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(255) NOT NULL,
        address TEXT NOT NULL,
        city VARCHAR(100) NOT NULL,
        state VARCHAR(100) NOT NULL,
        pincode VARCHAR(10),
        latitude DECIMAL(10, 8) NULL,
        longitude DECIMAL(11, 8) NULL,
        description TEXT,
        status ENUM('active', 'inactive') DEFAULT 'active',
        created_by INT NOT NULL,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        INDEX idx_name (name),
        INDEX idx_city (city),
        INDEX idx_status (status),
        INDEX idx_created_by (created_by)
    )";
    $pdo->exec($sql);

    // Fare Details table for storing fares between locations (Admin can manage)
    $sql = "CREATE TABLE IF NOT EXISTS fare_details (
        id INT AUTO_INCREMENT PRIMARY KEY,
        from_location_id INT NOT NULL,
        to_location_id INT NOT NULL,
        base_fare DECIMAL(10, 2) NULL,
        per_km_rate DECIMAL(8, 2) NULL,
        minimum_fare DECIMAL(10, 2) NULL,
        fix_rate DECIMAL(10, 2) NULL,
        distance_km DECIMAL(8, 2) NULL,
        estimated_duration_minutes INT NULL,
        status ENUM('active', 'inactive') DEFAULT 'active',
        created_by INT NOT NULL,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        UNIQUE KEY unique_route (from_location_id, to_location_id),
        INDEX idx_from_location (from_location_id),
        INDEX idx_to_location (to_location_id),
        INDEX idx_status (status),
        INDEX idx_created_by (created_by),
        INDEX idx_from_location (from_location_id),
        INDEX idx_to_location (to_location_id)
    )";
    $pdo->exec($sql);

    // Add foreign key constraints for created_by fields - only if tables exist and constraints don't already exist
    try {
        $pdo->exec("ALTER TABLE operators ADD CONSTRAINT fk_operators_created_by FOREIGN KEY (created_by) REFERENCES super_admins(id) ON DELETE SET NULL");
    } catch (Exception $e) {
        // Constraint might already exist or table doesn't exist yet
    }
    
    try {
        $pdo->exec("ALTER TABLE validators ADD CONSTRAINT fk_validators_created_by FOREIGN KEY (created_by) REFERENCES super_admins(id) ON DELETE SET NULL");
    } catch (Exception $e) {
        // Constraint might already exist or table doesn't exist yet
    }
    
    try {
        $pdo->exec("ALTER TABLE admins ADD CONSTRAINT fk_admins_created_by FOREIGN KEY (created_by) REFERENCES super_admins(id) ON DELETE SET NULL");
    } catch (Exception $e) {
        // Constraint might already exist or table doesn't exist yet
    }
    
    // Add foreign key constraint for fare_details created_by field - only if tables exist
    try {
        $pdo->exec("ALTER TABLE fare_details ADD CONSTRAINT fk_fare_details_created_by FOREIGN KEY (created_by) REFERENCES admins(id) ON DELETE CASCADE");
    } catch (Exception $e) {
        // Constraint might already exist or table doesn't exist yet
    }
    
    // Bookings table for storing ride bookings
    $sql = "CREATE TABLE IF NOT EXISTS bookings (
        id INT AUTO_INCREMENT PRIMARY KEY,
        passenger_id INT NOT NULL,
        from_location_id INT NULL,
        to_location_id INT NOT NULL,
        auto_rickshaw_id INT NULL,
        operator_id INT NULL,
        booking_type ENUM('qr_code', 'direct') NOT NULL,
        payment_status ENUM('pending', 'completed', 'failed') DEFAULT 'pending',
        payment_method ENUM('phonepe', 'googlepay', 'offline') NULL,
        payment_transaction_id VARCHAR(255) NULL,
        commission_amount DECIMAL(10, 2) NOT NULL,
        fare_amount DECIMAL(10, 2) NOT NULL,
        total_amount DECIMAL(10, 2) NOT NULL,
        qr_code_data TEXT NULL,
        qr_code_path VARCHAR(255) NULL,
        status ENUM('pending', 'confirmed', 'in_progress', 'completed', 'cancelled') DEFAULT 'pending',
        booking_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        confirmation_time TIMESTAMP NULL,
        completion_time TIMESTAMP NULL,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        INDEX idx_passenger_id (passenger_id),
        INDEX idx_from_location (from_location_id),
        INDEX idx_to_location (to_location_id),
        INDEX idx_auto_rickshaw (auto_rickshaw_id),
        INDEX idx_operator (operator_id),
        INDEX idx_payment_status (payment_status),
        INDEX idx_status (status),
        INDEX idx_booking_time (booking_time),
        CONSTRAINT fk_bookings_passenger FOREIGN KEY (passenger_id) REFERENCES passengers(id) ON DELETE CASCADE,
        CONSTRAINT fk_bookings_to_location FOREIGN KEY (to_location_id) REFERENCES to_locations(id) ON DELETE CASCADE
    )";
    $pdo->exec($sql);
    
    // Payments table for tracking payment transactions
    $sql = "CREATE TABLE IF NOT EXISTS payments (
        id INT AUTO_INCREMENT PRIMARY KEY,
        booking_id INT NOT NULL,
        amount DECIMAL(10, 2) NOT NULL,
        payment_method ENUM('phonepe', 'googlepay', 'offline') NOT NULL,
        transaction_id VARCHAR(255) NULL,
        payment_status ENUM('pending', 'completed', 'failed', 'refunded') DEFAULT 'pending',
        gateway_response TEXT NULL,
        payment_time TIMESTAMP NULL,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        INDEX idx_booking_id (booking_id),
        INDEX idx_payment_method (payment_method),
        INDEX idx_payment_status (payment_status),
        INDEX idx_transaction_id (transaction_id),
        INDEX idx_booking_id (booking_id)
    )";
    $pdo->exec($sql);
    
    // QR Codes table for storing generated QR codes
    $sql = "CREATE TABLE IF NOT EXISTS qr_codes (
        id INT AUTO_INCREMENT PRIMARY KEY,
        booking_id INT NOT NULL,
        qr_code_data TEXT NOT NULL,
        qr_code_path VARCHAR(255) NOT NULL,
        qr_code_type ENUM('passenger_booking', 'auto_rickshaw') NOT NULL,
        is_active BOOLEAN DEFAULT TRUE,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        expires_at TIMESTAMP NULL,
        INDEX idx_booking_id (booking_id),
        INDEX idx_qr_code_type (qr_code_type),
        INDEX idx_is_active (is_active),
        INDEX idx_expires_at (expires_at),
        INDEX idx_booking_id (booking_id)
    )";
    $pdo->exec($sql);
    
    // Operator Sessions table for tracking operator work sessions
    $sql = "CREATE TABLE IF NOT EXISTS operator_sessions (
        id INT AUTO_INCREMENT PRIMARY KEY,
        operator_id INT NOT NULL,
        from_location_id INT NOT NULL,
        session_start TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        session_end TIMESTAMP NULL,
        total_bookings INT DEFAULT 0,
        total_earnings DECIMAL(10, 2) DEFAULT 0.00,
        status ENUM('active', 'ended') DEFAULT 'active',
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        INDEX idx_operator_id (operator_id),
        INDEX idx_from_location (from_location_id),
        INDEX idx_status (status),
        INDEX idx_session_start (session_start),
        INDEX idx_operator_id (operator_id),
        INDEX idx_from_location (from_location_id)
    )";
    $pdo->exec($sql);
    
    // Commission Configuration table for super admin to manage commission rates
    $sql = "CREATE TABLE IF NOT EXISTS commission_config (
        id INT AUTO_INCREMENT PRIMARY KEY,
        commission_type ENUM('percentage', 'fixed') NOT NULL DEFAULT 'percentage',
        commission_value DECIMAL(10, 2) NOT NULL DEFAULT 10.00,
        min_commission DECIMAL(10, 2) NULL,
        max_commission DECIMAL(10, 2) NULL,
        description TEXT,
        is_active BOOLEAN DEFAULT TRUE,
        created_by INT NOT NULL,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        INDEX idx_commission_type (commission_type),
        INDEX idx_is_active (is_active),
        INDEX idx_created_by (created_by),
        CONSTRAINT fk_commission_config_created_by FOREIGN KEY (created_by) REFERENCES super_admins(id) ON DELETE CASCADE
    )";
    $pdo->exec($sql);
    
    // Insert default commission configuration (10% commission)
    $sql = "INSERT IGNORE INTO commission_config (commission_type, commission_value, description, created_by) 
            SELECT 'percentage', 10.00, 'Default 10% commission on base fare', id 
            FROM super_admins LIMIT 1";
    $pdo->exec($sql);
}