Setting Up the On-Premises SQL Server Data Source

CREATE TABLE dbo.DRIVERSENTITY (
    DRIVER_ID        INT NOT NULL PRIMARY KEY,
    FIRST_NAME       NVARCHAR (100),
    LAST_NAME        NVARCHAR (100),
    EMAIL_ADDRESS    NVARCHAR (200),
    DATE_OF_BIRTH    DATE,
    CREATED_AT       DATETIME2,
    CREATED_BY       NVARCHAR (100),
    LAST_MODIFIED_AT DATETIME2,
    DATA_SOURCE      NVARCHAR (50)
);

CREATE TABLE dbo.TRIPSHEADERENTITY (
    TRIP_ID              INT NOT NULL PRIMARY KEY,
    DRIVER_ID            INT,           -- FK to DRIVERSENTITY
    VEHICLE_ID           NVARCHAR(50),
    TRIP_STATUS          NVARCHAR(50),   -- 'COMPLETED','CANCELLED','IN_PROGRESS'
    TRIP_START_DATETIME  DATETIME2,
    TRIP_END_DATETIME    DATETIME2,
    LAST_MODIFIED_AT     DATETIME2      -- used as incremental load field
);

CREATE TABLE dbo.TRIPSLINEENTITY (
    TRIP_LINE_ID     INT NOT NULL PRIMARY KEY,
    TRIP_ID          INT,           -- FK to TRIPSHEADERENTITY
    FARE_COMPONENT   NVARCHAR(100),  -- 'BASE_FARE','SURGE','DISCOUNT'
    AMOUNT           DECIMAL(10,2),
    CURRENCY         NVARCHAR(10),
    TIMESTAMP        DATETIME2      -- used as incremental load field
);

-- Method 1: BULK INSERT (fastest for large datasets)
BULK INSERT dbo.DRIVERSENTITY
FROM 'C:\FabricProject\Drivers\Drivers_Data_Bulk_Insert.csv'
WITH (
    FIELDTERMINATOR = ',',
    ROWTERMINATOR   = '\n',
    FIRSTROW        = 2,   -- skip header row
    TABLOCK
);

-- Method 2: INSERT INTO (useful for dev & testing)
INSERT INTO dbo.DRIVERSENTITY
    (DRIVER_ID, FIRST_NAME, LAST_NAME, EMAIL_ADDRESS, DATE_OF_BIRTH, CREATED_AT, CREATED_BY)
VALUES
    (1001, 'Arjun', 'Sharma', 'arjun.sharma@cabco.com', '1990-05-14', GETDATE(), 'admin'),
    (1002, 'Priya', 'Patel', 'priya.patel@cabco.com', '1988-11-22', GETDATE(), 'admin'),
    (1003, 'Marcus', 'Johnson', 'marcus.j@cabco.com', '1995-03-08', GETDATE(), 'admin'),
    (1004, 'Fatima', 'Al-Said', 'fatima.s@cabco.com', '1992-07-19', GETDATE(), 'admin'),
    (1005, 'Rohit', 'Verma', 'rohit.v@cabco.com', '1991-01-30', GETDATE(), 'admin');

-- Step 1: Create the SQL login at server level
CREATE LOGIN fabric_gateway_user
WITH PASSWORD = 'YourStr0ng!Password';

-- Step 2: Create a database user mapped to the login
USE CabBusinessDB;
CREATE USER fabric_gateway_user FOR LOGIN fabric_gateway_user;

-- Step 3: Grant read-only access only
EXEC sp_addrolemember 'db_datareader', 'fabric_gateway_user';

-- Step 4: Verify — this user should only be able to SELECT
EXECUTE AS USER = 'fabric_gateway_user';
SELECT TOP 5 * FROM dbo.DRIVERSENTITY;  -- ✅ Works
-- INSERT INTO dbo.DRIVERSENTITY ...         ❌ Will fail (by design)
REVERT;

table_name,existing_column,new_column
DRIVERSENTITY,DRIVERID,driver_id
DRIVERSENTITY,FIRSTNAME,first_name
DRIVERSENTITY,LASTNAME,last_name
DRIVERSENTITY,EMAILADDRESS,email_address
DRIVERSENTITY,DATEOFBIRTH,date_of_birth
TRIPSHEADERENTITY,TRIPID,trip_id
TRIPSHEADERENTITY,TRIPSTATUS,trip_status
TRIPSHEADERENTITY,LASTMODIFIEDAT,last_modified_at
TRIPSLINEENTITY,TRIPLINEID,trip_line_id
TRIPSLINEENTITY,FARECOMPONENT,fare_component

-- Confirm all tables exist
SELECT TABLE_NAME, TABLE_TYPE
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'dbo';

-- Check row counts
SELECT 'DRIVERSENTITY' AS tbl, COUNT(*) AS rows FROM dbo.DRIVERSENTITY
UNION ALL
SELECT 'TRIPSHEADERENTITY' AS tbl, COUNT(*) AS rows FROM dbo.TRIPSHEADERENTITY
UNION ALL
SELECT 'TRIPSLINEENTITY' AS tbl, COUNT(*) AS rows FROM dbo.TRIPSLINEENTITY;

-- Confirm the Header-Line relationship integrity
SELECT h.TRIP_ID, h.TRIP_STATUS, COUNT(l.TRIP_LINE_ID) AS line_count
FROM dbo.TRIPSHEADERENTITY h
LEFT JOIN dbo.TRIPSLINEENTITY l ON h.TRIP_ID = l.TRIP_ID
GROUP BY h.TRIP_ID, h.TRIP_STATUS
ORDER BY h.TRIP_ID;