Design a schema model for a consumer requirement.
Use a normalized model in which consumers can have many requirements, and requirements can be associated with many consumers. Include a query that reports the requirement summary for every consumer, including consumers with no assignments.
Output
- One row per consumer
- Columns:
consumer_id, consumer_name, active_requirement_count, and last_assigned_at
- Include consumers without assignments, with a count of zero and a null latest-assignment date
- Order by active requirement count descending, then consumer ID ascending
