import { SQLServerDataProvider } from '@memberjunction/sqlserver-dataprovider';
import { ConfigHelper } from '@memberjunction/global';

// Configure database connection
const config = {
  host: 'your-server.database.windows.net',
  port: 1433,
  database: 'YourMJDatabase',
  user: 'your-username',
  password: 'your-password',
  options: {
    encrypt: true,
    trustServerCertificate: false
  }
};

// Create data provider instance
const dataProvider = new SQLServerDataProvider(config);

// Or using environment variables
const dataProvider = new SQLServerDataProvider({
  host: ConfigHelper.getConfigValue('MJ_HOST'),
  port: ConfigHelper.getConfigValue('MJ_PORT', 1433),
  database: ConfigHelper.getConfigValue('MJ_DATABASE'),
  user: ConfigHelper.getConfigValue('MJ_USER'),
  password: ConfigHelper.getConfigValue('MJ_PASSWORD')
});

// Initialize the data provider (connects to the database)
await dataProvider.initialize();

import { SQLServerDataProvider } from '@memberjunction/sqlserver-dataprovider';
import { EntityInfo } from '@memberjunction/core';

// Setup data provider
const dataProvider = new SQLServerDataProvider(/* config */);
await dataProvider.initialize();

// Load an entity
const userResult = await dataProvider.loadEntity('User', 1);
if (userResult.success) {
  const user = userResult.entity;
  console.log(`Loaded user: ${user.FirstName} ${user.LastName}`);
  
  // Update the entity
  user.Email = 'new.email@example.com';
  const saveResult = await dataProvider.saveEntity('User', user);
  
  if (saveResult.success) {
    console.log(`User updated successfully, ID: ${saveResult.entity.ID}`);
  }
}

// Create a new entity
const newUser = {
  ID: 0, // 0 indicates a new entity
  FirstName: 'John',
  LastName: 'Doe',
  Email: 'john.doe@example.com',
  // other required fields...
};

const createResult = await dataProvider.saveEntity('User', newUser);
if (createResult.success) {
  console.log(`New user created with ID: ${createResult.entity.ID}`);
}

// Delete an entity
const deleteResult = await dataProvider.deleteEntity('User', 5);
if (deleteResult.success) {
  console.log('User deleted successfully');
}

import { SQLServerDataProvider } from '@memberjunction/sqlserver-dataprovider';
import { TransactionGroupBase } from '@memberjunction/core';

// Setup data provider
const dataProvider = new SQLServerDataProvider(/* config */);
await dataProvider.initialize();

// Create a transaction group
class OrderTransactionGroup extends TransactionGroupBase {
  constructor() {
    super('CreateOrderWithItems');
  }
}

const transaction = new OrderTransactionGroup();

// Add multiple entities to the transaction
transaction.addEntity('Order', {
  ID: 0,
  CustomerID: 123,
  OrderDate: new Date(),
  Status: 'New'
});

// Reference previous entities in the same transaction
transaction.addEntity('OrderItem', {
  ID: 0,
  OrderID: '@Order.1', // Reference to the first Order in this transaction
  ProductID: 456,
  Quantity: 2,
  Price: 29.99
});

transaction.addEntity('OrderItem', {
  ID: 0,
  OrderID: '@Order.1',
  ProductID: 789,
  Quantity: 1,
  Price: 49.99
});

// Execute the transaction group
const result = await dataProvider.executeTransactionGroup(transaction);

if (result.success) {
  console.log('Transaction completed successfully');
  console.log('Order ID:', result.results.find(r => r.entityName === 'Order')?.entity.ID);
} else {
  console.error('Transaction failed:', result.error);
}

import { SQLServerDataProvider } from '@memberjunction/sqlserver-dataprovider';
import { RunViewOptions } from '@memberjunction/core';

// Setup data provider
const dataProvider = new SQLServerDataProvider(/* config */);
await dataProvider.initialize();

// Run a view with filtering and pagination
const viewOptions: RunViewOptions = {
  EntityName: 'vwActiveUsers',
  ExtraFilter: "Role = 'Administrator'",
  OrderBy: 'LastName, FirstName',
  PageSize: 10,
  PageNumber: 1
};

const viewResult = await dataProvider.runView(viewOptions);

if (viewResult.success) {
  console.log(`Found ${viewResult.Results.length} users`);
  console.log(`Total matching records: ${viewResult.TotalRowCount}`);
  
  viewResult.Results.forEach(user => {
    console.log(`${user.FirstName} ${user.LastName} (${user.Email})`);
  });
}

// Run a report
const reportResult = await dataProvider.runReport('SalesReport', {
  StartDate: '2023-01-01',
  EndDate: '2023-12-31',
  Format: 'JSON'
});

if (reportResult.success) {
  console.log('Report data:', reportResult.results);
}

import { SQLServerDataProvider } from '@memberjunction/sqlserver-dataprovider';

// Setup data provider
const dataProvider = new SQLServerDataProvider(/* config */);
await dataProvider.initialize();

// Execute a parameterized query
const queryResult = await dataProvider.executeQuery(
  'SELECT * FROM Users WHERE Department = @dept AND HireDate > @date',
  {
    dept: 'Engineering',
    date: '2022-01-01'
  }
);

if (queryResult.success) {
  console.log(`Query returned ${queryResult.results.length} rows`);
  queryResult.results.forEach(row => {
    console.log(row);
  });
}

// Execute a stored procedure
const spResult = await dataProvider.executeQuery(
  'EXEC sp_GetUserPermissions @UserID',
  {
    UserID: 123
  }
);

if (spResult.success) {
  console.log('User permissions:', spResult.results);
}

import { SQLServerDataProvider } from '@memberjunction/sqlserver-dataprovider';

// Setup data provider
const dataProvider = new SQLServerDataProvider(/* config */);
await dataProvider.initialize();

// Set current user context
dataProvider.setCurrentUser(123); // User ID

// Get current user
const currentUser = dataProvider.getCurrentUser();
console.log(`Current user: ${currentUser.FirstName} ${currentUser.LastName}`);

// User caching is handled automatically by the provider
// but you can clear the cache if needed
dataProvider.clearUserCache();

import { SQLServerDataProvider } from '@memberjunction/sqlserver-dataprovider';

class CustomSQLProvider extends SQLServerDataProvider {
  // Override to add custom logging or modifications
  async executeQuery(sql: string, params?: any): Promise<any> {
    console.log(`Executing SQL: ${sql}`);
    console.log('Parameters:', params);
    
    // Add timing
    const startTime = Date.now();
    const result = await super.executeQuery(sql, params);
    const duration = Date.now() - startTime;
    
    console.log(`Query executed in ${duration}ms`);
    
    return result;
  }
}