Database Functions & LINQ Data Access

CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Applied to tables via triggers:
CREATE TRIGGER trg_users_updated_at
    BEFORE UPDATE ON admin.users
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

CREATE TRIGGER trg_purchase_requests_updated_at
    BEFORE UPDATE ON pr.purchase_requests
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

-- Applied to all 166 tables that have updated_at column

CREATE OR REPLACE FUNCTION audit.log_data_change()
RETURNS TRIGGER AS $$
DECLARE
    v_old_data JSONB;
    v_new_data JSONB;
    v_changed_columns TEXT[];
    v_user_id UUID;
BEGIN
    -- Get current user from session variable (set by application)
    BEGIN
        v_user_id := current_setting('app.current_user_id')::UUID;
    EXCEPTION WHEN OTHERS THEN
        v_user_id := NULL;
    END;

    IF TG_OP = 'INSERT' THEN
        v_new_data := to_jsonb(NEW);
        INSERT INTO audit.data_change_logs (
            schema_name, table_name, record_id, operation,
            new_data, changed_by, transaction_id
        ) VALUES (
            TG_TABLE_SCHEMA, TG_TABLE_NAME, NEW.id, TG_OP,
            v_new_data, v_user_id, txid_current()
        );
        RETURN NEW;

    ELSIF TG_OP = 'UPDATE' THEN
        v_old_data := to_jsonb(OLD);
        v_new_data := to_jsonb(NEW);

        -- Get list of changed columns
        SELECT array_agg(key) INTO v_changed_columns
        FROM (
            SELECT key FROM jsonb_each(v_old_data)
            WHERE v_old_data->key IS DISTINCT FROM v_new_data->key
        ) changed;

        IF v_changed_columns IS NOT NULL THEN
            INSERT INTO audit.data_change_logs (
                schema_name, table_name, record_id, operation,
                old_data, new_data, changed_columns, changed_by
            ) VALUES (
                TG_TABLE_SCHEMA, TG_TABLE_NAME, NEW.id, TG_OP,
                v_old_data, v_new_data, v_changed_columns, v_user_id
            );
        END IF;
        RETURN NEW;

    ELSIF TG_OP = 'DELETE' THEN
        v_old_data := to_jsonb(OLD);
        INSERT INTO audit.data_change_logs (...);
        RETURN OLD;
    END IF;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION system.get_next_document_number(
    p_company_id UUID,
    p_document_type VARCHAR(50)
) RETURNS VARCHAR(50) AS $$
DECLARE
    v_seq RECORD;
    v_next_number BIGINT;
    v_doc_number VARCHAR(50);
BEGIN
    -- Get and lock sequence configuration (thread-safe)
    SELECT * INTO v_seq
    FROM system.document_sequences
    WHERE company_id = p_company_id
      AND document_type = p_document_type
      AND is_active = true
    FOR UPDATE;

    -- Check if reset is needed (YEARLY, MONTHLY, DAILY)
    IF v_seq.reset_frequency = 'YEARLY'
       AND EXTRACT(YEAR FROM CURRENT_DATE) != EXTRACT(YEAR FROM v_seq.last_reset_date) THEN
        v_seq.current_number := v_seq.start_number - v_seq.increment_by;
        UPDATE system.document_sequences SET last_reset_date = CURRENT_DATE
        WHERE company_id = p_company_id AND document_type = p_document_type;
    END IF;

    -- Increment and build document number
    v_next_number := v_seq.current_number + v_seq.increment_by;

    UPDATE system.document_sequences
    SET current_number = v_next_number
    WHERE company_id = p_company_id AND document_type = p_document_type;

    v_doc_number := v_seq.prefix || '-' || TO_CHAR(CURRENT_DATE, 'YYYY') ||
                    '-' || LPAD(v_next_number::TEXT, v_seq.number_length, '0');

    RETURN v_doc_number;  -- e.g., "PR-2026-00001"
END;
$$ LANGUAGE plpgsql;

// PurchaseRequestService.cs
public class PurchaseRequestService : IPurchaseRequestService
{
    private readonly ProKureDbContext _context;

    public PurchaseRequestService(ProKureDbContext context)
    {
        _context = context;
    }

    // Get single PR with related data
    public async Task<PurchaseRequest?> GetByIdAsync(Guid id)
    {
        return await _context.PurchaseRequests
            .Include(pr => pr.Items)
            .Include(pr => pr.Approvals)
            .Include(pr => pr.RequestedBy)
            .Include(pr => pr.Department)
            .FirstOrDefaultAsync(pr => pr.Id == id);
    }

    // Filtered list with pagination
    public async Task<PagedResult<PurchaseRequestDto>> GetListAsync(
        Guid companyId, string? searchText, int page, int pageSize)
    {
        var query = _context.PurchaseRequests
            .Where(pr => pr.CompanyId == companyId && !pr.IsDeleted);

        if (!string.IsNullOrEmpty(searchText))
        {
            query = query.Where(pr =>
                pr.PrNumber.Contains(searchText) ||
                pr.RequestedBy.FirstName.Contains(searchText));
        }

        var totalCount = await query.CountAsync();

        var items = await query
            .OrderByDescending(pr => pr.CreatedAt)
            .Skip((page - 1) * pageSize)
            .Take(pageSize)
            .Select(pr => new PurchaseRequestDto
            {
                Id = pr.Id,
                PrNumber = pr.PrNumber,
                Status = pr.Status,
                TotalAmount = pr.TotalAmount,
                RequestedByName = pr.RequestedBy.FirstName + " " + pr.RequestedBy.LastName,
                CreatedAt = pr.CreatedAt
            })
            .ToListAsync();

        return new PagedResult<PurchaseRequestDto>(items, totalCount, page, pageSize);
    }
}

// RfqService.cs - Complex query with multiple joins
public async Task<List<BidComparisonDto>> GetBidComparisonAsync(Guid rfqId)
{
    return await (
        from rfqItem in _context.RfqItems
        join bidItem in _context.VendorBidItems on rfqItem.Id equals bidItem.RfqItemId
        join bid in _context.VendorBids on bidItem.BidId equals bid.Id
        join vendor in _context.Vendors on bid.VendorId equals vendor.Id
        join product in _context.Products on rfqItem.ProductId equals product.Id
        where rfqItem.RfqId == rfqId
        orderby rfqItem.LineNumber, bidItem.UnitPrice
        select new BidComparisonDto
        {
            LineNumber = rfqItem.LineNumber,
            ProductCode = product.ProductCode,
            ProductName = product.ProductName,
            RequiredQty = rfqItem.Quantity,
            VendorName = vendor.VendorName,
            UnitPrice = bidItem.UnitPrice,
            TotalPrice = bidItem.UnitPrice * rfqItem.Quantity,
            DeliveryDays = bidItem.DeliveryDays,
            Remarks = bidItem.Remarks
        }
    ).ToListAsync();
}

// Alternative using method syntax with GroupJoin
public async Task<List<VendorWithRatingDto>> GetVendorsWithRatingsAsync(Guid categoryId)
{
    return await _context.Vendors
        .Where(v => v.VendorCategories.Any(vc => vc.CategoryId == categoryId))
        .Select(v => new VendorWithRatingDto
        {
            VendorId = v.Id,
            VendorCode = v.VendorCode,
            VendorName = v.VendorName,
            // Subquery for latest rating
            LatestRating = v.VendorRatings
                .OrderByDescending(r => r.RatedAt)
                .Select(r => r.CompositeScore)
                .FirstOrDefault(),
            Classification = v.VendorRatings
                .OrderByDescending(r => r.RatedAt)
                .Select(r => r.Classification)
                .FirstOrDefault()
        })
        .ToListAsync();
}

// PurchaseOrderService.cs - Transaction example
public async Task<PurchaseOrder> CreateFromRfqAsync(CreatePoFromRfqDto dto)
{
    await using var transaction = await _context.Database.BeginTransactionAsync();

    try
    {
        // Generate document number via DB function
        var poNumber = await _context.Database
            .SqlQuery<string>($"SELECT system.get_next_document_number({dto.CompanyId}, 'PO')")
            .FirstAsync();

        // Create PO header
        var purchaseOrder = new PurchaseOrder
        {
            Id = Guid.NewGuid(),
            PoNumber = poNumber,
            PoDate = DateTime.UtcNow,
            CompanyId = dto.CompanyId,
            VendorId = dto.VendorId,
            RfqId = dto.RfqId,
            Status = PoStatus.Draft,
            CreatedBy = dto.CreatedBy
        };

        _context.PurchaseOrders.Add(purchaseOrder);

        // Create PO items from awarded bid items
        var bidItems = await _context.VendorBidItems
            .Include(bi => bi.RfqItem)
            .Where(bi => dto.AwardedBidItemIds.Contains(bi.Id))
            .ToListAsync();

        var lineNumber = 1;
        foreach (var bidItem in bidItems)
        {
            _context.PoItems.Add(new PoItem
            {
                Id = Guid.NewGuid(),
                PoId = purchaseOrder.Id,
                LineNumber = lineNumber++,
                ProductId = bidItem.RfqItem.ProductId,
                Quantity = bidItem.Quantity,
                UnitPrice = bidItem.UnitPrice,
                RfqItemId = bidItem.RfqItemId
            });
        }

        // Update RFQ status
        var rfq = await _context.Rfqs.FindAsync(dto.RfqId);
        rfq!.Status = RfqStatus.Awarded;

        await _context.SaveChangesAsync();
        await transaction.CommitAsync();

        return purchaseOrder;
    }
    catch
    {
        await transaction.RollbackAsync();
        throw;
    }
}

// ProKureDbContext.cs
public class ProKureDbContext : DbContext
{
    // Admin schema
    public DbSet<User> Users { get; set; }
    public DbSet<Role> Roles { get; set; }
    public DbSet<Permission> Permissions { get; set; }

    // Organization schema
    public DbSet<Company> Companies { get; set; }
    public DbSet<Department> Departments { get; set; }
    public DbSet<Plant> Plants { get; set; }

    // Purchase Requisition schema
    public DbSet<PurchaseRequest> PurchaseRequests { get; set; }
    public DbSet<PrItem> PrItems { get; set; }

    // RFQ schema
    public DbSet<Rfq> Rfqs { get; set; }
    public DbSet<VendorBid> VendorBids { get; set; }
    public DbSet<ReverseAuction> ReverseAuctions { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        // Schema mappings
        modelBuilder.Entity<User>().ToTable("users", "admin");
        modelBuilder.Entity<Role>().ToTable("roles", "admin");
        modelBuilder.Entity<Company>().ToTable("companies", "org");
        modelBuilder.Entity<PurchaseRequest>().ToTable("purchase_requests", "pr");
        modelBuilder.Entity<Rfq>().ToTable("rfqs", "rfq");
        modelBuilder.Entity<ReverseAuction>().ToTable("reverse_auctions", "rfq");

        // JSONB column mapping
        modelBuilder.Entity<Company>()
            .Property(c => c.Address)
            .HasColumnType("jsonb");

        modelBuilder.Entity<PurchaseRequest>()
            .Property(pr => pr.CustomData)
            .HasColumnType("jsonb");

        // Soft delete query filter
        modelBuilder.Entity<User>().HasQueryFilter(u => !u.IsDeleted);
        modelBuilder.Entity<Vendor>().HasQueryFilter(v => !v.IsDeleted);
        modelBuilder.Entity<PurchaseRequest>().HasQueryFilter(pr => !pr.IsDeleted);

        // Relationships
        modelBuilder.Entity<PurchaseRequest>()
            .HasMany(pr => pr.Items)
            .WithOne(i => i.PurchaseRequest)
            .HasForeignKey(i => i.PrId)
            .OnDelete(DeleteBehavior.Cascade);

        // Apply all configurations from assembly
        modelBuilder.ApplyConfigurationsFromAssembly(typeof(ProKureDbContext).Assembly);
    }
}