<?php
$host = '192.168.10.61';
$port = '1433';
$user = 'zack';
$pass = 'zack123456';
$db   = 'EHONGDB';
$dsn  = "sqlsrv:Server=$host,$port;Database=$db;TrustServerCertificate=true";

try {
    $pdo = new PDO($dsn, $user, $pass, [
        PDO::SQLSRV_ATTR_ENCODING => PDO::SQLSRV_ENCODING_UTF8,
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
    ]);

    // First, let's see if the database CONFIG exists and we can access it
    $stmt = $pdo->query("SELECT name FROM sys.databases WHERE name = 'CONFIG'");
    $db_exists = $stmt->fetch();
    
    if (!$db_exists) {
        die("Error: Database 'CONFIG' not found or not accessible.");
    }

    echo "Database 'CONFIG' is accessible.\n";

    $sql = "
    IF NOT EXISTS (SELECT * FROM CONFIG.sys.objects WHERE object_id = OBJECT_ID(N'CONFIG.dbo.EMD_QUESTIONNAIRE') AND type in (N'U'))
    BEGIN
        CREATE TABLE CONFIG.dbo.EMD_QUESTIONNAIRE (
            id int IDENTITY(1,1) PRIMARY KEY,
            submission_date datetime,
            employee_barcode nvarchar(MAX),
            branch nvarchar(MAX),
            app_code nvarchar(MAX),
            gender nvarchar(MAX),
            birthyear nvarchar(MAX),
            job nvarchar(MAX),
            job_other nvarchar(MAX),
            brand nvarchar(MAX),
            brand_other nvarchar(MAX),
            used_service nvarchar(MAX),
            bought_products nvarchar(MAX),
            bought_other nvarchar(MAX),
            ehong_satisfaction nvarchar(MAX),
            ehong_suggestion nvarchar(MAX),
            ehong_return nvarchar(MAX),
            ehong_return_other nvarchar(MAX),
            ehong_branch nvarchar(MAX),
            ehong_branch_other nvarchar(MAX),
            other_shop_name nvarchar(MAX),
            other_shop_satisfaction nvarchar(MAX),
            other_shop_return nvarchar(MAX),
            other_shop_return_other nvarchar(MAX),
            future_interest_ehong nvarchar(MAX),
            future_interest_ehong_other nvarchar(MAX),
            fullname nvarchar(MAX),
            phone nvarchar(MAX),
            changwat nvarchar(MAX),
            amphoe nvarchar(MAX),
            tambon nvarchar(MAX),
            event_interest nvarchar(MAX),
            event_interest_other nvarchar(MAX)
        );
        echo 'Table created successfully.';
    END ELSE BEGIN
        echo 'Table already exists.';
    END";
    
    // PHP doesn't support 'echo' inside SQL, so I will handle the logic in PHP
    
    $check_sql = "SELECT * FROM CONFIG.sys.objects WHERE object_id = OBJECT_ID(N'CONFIG.dbo.EMD_QUESTIONNAIRE') AND type in (N'U')";
    $check_stmt = $pdo->query($check_sql);
    
    if (!$check_stmt->fetch()) {
        echo "Table does not exist. Creating...\n";
        $create_sql = "
        CREATE TABLE CONFIG.dbo.EMD_QUESTIONNAIRE (
            id int IDENTITY(1,1) PRIMARY KEY,
            submission_date datetime,
            employee_barcode nvarchar(MAX),
            branch nvarchar(MAX),
            app_code nvarchar(MAX),
            gender nvarchar(MAX),
            birthyear nvarchar(MAX),
            job nvarchar(MAX),
            job_other nvarchar(MAX),
            brand nvarchar(MAX),
            brand_other nvarchar(MAX),
            used_service nvarchar(MAX),
            bought_products nvarchar(MAX),
            bought_other nvarchar(MAX),
            ehong_satisfaction nvarchar(MAX),
            ehong_suggestion nvarchar(MAX),
            ehong_return nvarchar(MAX),
            ehong_return_other nvarchar(MAX),
            ehong_branch nvarchar(MAX),
            ehong_branch_other nvarchar(MAX),
            other_shop_name nvarchar(MAX),
            other_shop_satisfaction nvarchar(MAX),
            other_shop_return nvarchar(MAX),
            other_shop_return_other nvarchar(MAX),
            future_interest_ehong nvarchar(MAX),
            future_interest_ehong_other nvarchar(MAX),
            fullname nvarchar(MAX),
            phone nvarchar(MAX),
            changwat nvarchar(MAX),
            amphoe nvarchar(MAX),
            tambon nvarchar(MAX),
            event_interest nvarchar(MAX),
            event_interest_other nvarchar(MAX)
        )";
        $pdo->exec($create_sql);
        echo "Table created successfully.\n";
    } else {
        echo "Table already exists.\n";
    }

} catch (PDOException $e) {
    echo "Error: " . $e->getMessage();
}
