using RaptorSheets.Core.Services;
using RaptorSheets.Core.Models.Google;

// Set up authentication
var credentials = new Dictionary<string, string>
{
    ["type"] = "service_account",
    ["client_email"] = "service@project.iam.gserviceaccount.com",
    ["private_key"] = "-----BEGIN PRIVATE KEY-----\n...",
    // ... other credentials
};

var service = new GoogleSheetService(credentials, spreadsheetId);

// Read data from existing sheet
var sheetData = await service.GetSheetData("MySheet");
Console.WriteLine($"Found {sheetData.Values.Count} rows");

using RaptorSheets.Core.Attributes;
using RaptorSheets.Core.Enums;
using RaptorSheets.Core.Repositories;

// Define entities with automatic type conversion
public class ContactEntity
{
    public int RowId { get; set; }

    // Basic string field with header mapping
    [Column(SheetsConfig.HeaderNames.Name, FieldType.String)]
    public string Name { get; set; } = "";

    // Automatic currency formatting with default "$#,##0.00" pattern
    [Column(SheetsConfig.HeaderNames.Salary, FieldType.Currency)]
    public decimal? Salary { get; set; }

    // Custom format when different from default
    [Column(SheetsConfig.HeaderNames.Score, FieldType.Percentage, "0.0%")]
    public decimal? Score { get; set; }

    // Override JSON name when needed
    [Column(SheetsConfig.HeaderNames.EmailAddress, FieldType.Email)]
    [JsonPropertyName("email")]
    public string EmailAddress { get; set; } = "";
}

// Repository with automatic CRUD operations
public class ContactRepository : BaseEntityRepository<ContactEntity>
{
    public ContactRepository(IGoogleSheetService sheetService) 
        : base(sheetService, "Contacts", hasHeaderRow: true) { }

    // Custom business logic methods
    public async Task<List<ContactEntity>> GetHighScorersAsync()
    {
        var allContacts = await GetAllAsync(); // Automatic type conversion
        return allContacts.Where(c => c.Score > 0.8m).ToList();
    }
}

// Usage
var repository = new ContactRepository(service);

// Automatic type conversion: "$75,000.00" → decimal 75000
var contacts = await repository.GetAllAsync();

// Add new contact with automatic conversion
var newContact = new ContactEntity
{
    Name = "John Doe",
    Salary = 75000m,      // Automatically formatted as "$75,000.00"
    Score = 0.95m,        // Automatically formatted as "95.0%"
    EmailAddress = "john@example.com"
};
await repository.AddAsync(newContact);

// Create custom sheet with formatting
var sheetModel = new SheetModel
{
    Name = "CustomSheet",
    TabColor = ColorEnum.BLUE,
    Headers = new List<SheetCellModel>
    {
        new() { Name = "ID", Format = FormatEnum.NUMBER },
        new() { Name = "Name", Format = FormatEnum.TEXT },
        new() { Name = "Amount", Format = FormatEnum.CURRENCY }
    }
};

// Generate and execute requests
var requests = sheetModel.GenerateRequests();
await service.ExecuteBatchUpdate(requests);

// Single attribute, automated mapping
[Column(SheetsConfig.HeaderNames.Pay, FieldType.Currency, "\"$\"#,##0.00")]
public decimal? Pay { get; set; }

// Automated conversion in mappers
entity.Pay = MapperHelper.MapField<decimal?>("Pay", row, headers);

var credentials = new Dictionary<string, string>
{
    ["type"] = "service_account",
    ["private_key_id"] = "your-key-id",
    ["private_key"] = "your-private-key", 
    ["client_email"] = "service@project.iam.gserviceaccount.com",
    ["client_id"] = "your-client-id"
};

var manager = new GoogleSheetManager(accessToken, spreadsheetId);

// 1. Define your domain entities with ColumnAttribute
public class ProductEntity
{
    public int RowId { get; set; }
    
    [Column(SheetsConfig.HeaderNames.ProductName, FieldType.String)]
    public string Name { get; set; } = "";
    
    [Column(SheetsConfig.HeaderNames.Price, FieldType.Currency)]
    public decimal Price { get; set; }
    
    [Column(SheetsConfig.HeaderNames.LaunchDate, FieldType.DateTime, "M/d/yyyy")]
    public DateTime? LaunchDate { get; set; }
}

// 2. Create repository with automatic CRUD
public class ProductRepository : BaseEntityRepository<ProductEntity>
{
    public ProductRepository(IGoogleSheetService service) 
        : base(service, "Products", hasHeaderRow: true) { }
    
    public async Task<List<ProductEntity>> GetExpensiveProductsAsync()
    {
        var products = await GetAllAsync(); // Automatic conversion
        return products.Where(p => p.Price > 100m).ToList();
    }
}

// 3. Domain-specific manager
public class ProductManager
{
    private readonly ProductRepository _repository;
    
    public ProductManager(Dictionary<string, string> credentials, string spreadsheetId)
    {
        var service = new GoogleSheetService(credentials, spreadsheetId);
        _repository = new ProductRepository(service);
    }
    
    public async Task<List<ProductEntity>> GetProductCatalogAsync()
    {
        return await _repository.GetAllAsync(); // Full type conversion automatically
    }
}