# Livestock Marketplace – Listing Service Spec

This document covers:

1. All DB tables required for animal listings
2. API endpoints – purpose, request, and response formats

---

## 1. Database Tables

> Convention: Every table has `created_at` and `updated_at` (`TIMESTAMP`).

### 1.1 `users` (reference)

Minimal definition (assuming you already have auth elsewhere).

| Column     | Type      | Constraints      | Description            |
| ---------- | --------- | ---------------- | ---------------------- |
| id         | UUID      | PK               | User ID (seller/buyer) |
| name       | VARCHAR   | NOT NULL         | Display name           |
| phone      | VARCHAR   | UNIQUE, NOT NULL | Phone number           |
| created_at | TIMESTAMP | NOT NULL         | Row created time       |
| updated_at | TIMESTAMP | NOT NULL         | Row last updated time  |

**Relationships**

-   1 `user` → N `listings`
-   1 `user` → N `locations` (saved addresses only)

---

### 1.2 `species`

| Column     | Type      | Constraints | Description                |
| ---------- | --------- | ----------- | -------------------------- |
| id         | INT       | PK          | Species ID                 |
| name       | VARCHAR   | UNIQUE      | e.g. Cattle, Buffalo, Goat |
| created_at | TIMESTAMP | NOT NULL    |                            |
| updated_at | TIMESTAMP | NOT NULL    |                            |

**Relationships**

-   1 `species` → N `breeds`
-   1 `species` → N `animals`

---

### 1.3 `breeds`

| Column      | Type      | Constraints     | Description      |
| ----------- | --------- | --------------- | ---------------- |
| id          | INT       | PK              | Breed ID         |
| species_id  | INT       | FK → species.id | Parent species   |
| name        | VARCHAR   | NOT NULL        | e.g. Gir, Murrah |
| description | TEXT      | NULL            | Optional notes   |
| created_at  | TIMESTAMP | NOT NULL        |                  |
| updated_at  | TIMESTAMP | NOT NULL        |                  |

**Relationships**

-   1 `species` → N `breeds`
-   1 `breed` → N `animals`

---

### 1.4 `locations`

Used both for:

-   **Captured locations** (no user, not saved; `user_id = NULL`, `is_saved_address = false`)
-   **Saved addresses** for a user (e.g. farm, home; `user_id` set, `is_saved_address = true`)

| Column            | Type      | Constraints                 | Description                                                           |
| ----------------- | --------- | --------------------------- | --------------------------------------------------------------------- |
| id                | UUID      | PK                          | Location ID                                                           |
| user_id           | UUID      | FK → users.id, NULLABLE     | Owner if this is a saved address; NULL if just captured for a listing |
| is_saved_address  | BOOLEAN   | NOT NULL, default false     | True if user chose to save it as an address                           |
| location_type     | VARCHAR   | NULL (enum suggestion)      | e.g. `farm`, `home`, `office`, `other`                                |
| country           | VARCHAR   | NULL                        | Country                                                               |
| state             | VARCHAR   | NULL                        | State                                                                 |
| district          | VARCHAR   | NULL                        | District                                                              |
| city_village      | VARCHAR   | NULL                        | City / village                                                        |
| pincode           | VARCHAR   | NULL                        | Postal code                                                           |
| lat               | DECIMAL   | NULL                        | Latitude                                                              |
| lng               | DECIMAL   | NULL                        | Longitude                                                             |
| source_type       | VARCHAR   | NOT NULL, default `unknown` | `device_gps`, `manual`, `unknown`                                     |
| source_confidence | VARCHAR   | NOT NULL, default `medium`  | `high`, `medium`, `low`                                               |
| created_at        | TIMESTAMP | NOT NULL                    |                                                                       |
| updated_at        | TIMESTAMP | NOT NULL                    |                                                                       |

**Interpretation**

-   **Captured only**: `user_id = NULL`, `is_saved_address = false`
-   **Captured + saved as user’s farm/home**: set `user_id`, `is_saved_address = true`, `location_type = 'farm'` (or similar).

**Relationships**

-   1 `user` → N `locations` (saved addresses)
-   1 `location` → N `animals` (many animals can share same farm address)

---

### 1.5 `animals`

One animal per listing (enforced via unique constraint at `listings.animal_id`).

| Column                     | Type      | Constraints               | Description                                     |
| -------------------------- | --------- | ------------------------- | ----------------------------------------------- |
| id                         | UUID      | PK                        | Animal ID                                       |
| species_id                 | INT       | FK → species.id, NOT NULL | Species                                         |
| breed_id                   | INT       | FK → breeds.id, NULL      | Breed (optional)                                |
| sex                        | VARCHAR   | NOT NULL                  | `M`, `F`, `Neutered`                            |
| age_months                 | INT       | NULL                      | Age in months                                   |
| weight_kg                  | DECIMAL   | NULL                      | Approx weight                                   |
| color_markings             | VARCHAR   | NULL                      | Color / markings                                |
| quantity                   | INT       | NOT NULL, default 1       | Number of animals in this listing               |
| purpose                    | VARCHAR   | NOT NULL                  | `dairy`, `meat`, `breeding`, `pet`, `work`, etc |
| health_status              | VARCHAR   | NOT NULL                  | `healthy`, `minor_issues`, `serious_issues`     |
| vaccinated                 | BOOLEAN   | NOT NULL, default false   |                                                 |
| dewormed                   | BOOLEAN   | NOT NULL, default false   |                                                 |
| previous_pregnancies_count | INT       | NULL                      | For females, number of previous pregnancies     |
| pregnancy_status           | VARCHAR   | NULL                      | `not_pregnant`, `pregnant`, `recently_calved`   |
| milk_yield_litre_per_day   | DECIMAL   | NULL                      | Avg daily milk yield                            |
| ear_tag_no                 | VARCHAR   | NULL                      | Tag / registration ID                           |
| description                | TEXT      | NULL                      | Detailed description                            |
| suggested_care             | TEXT      | NULL                      | Suggested food & accessories (free-text)        |
| location_id                | UUID      | FK → locations.id, NULL   | Location of animal (farm etc.). NULL if unknown |
| created_at                 | TIMESTAMP | NOT NULL                  |                                                 |
| updated_at                 | TIMESTAMP | NOT NULL                  |                                                 |

**Relationships**

-   1 `species` → N `animals`
-   1 `breed` → N `animals`
-   1 `location` → N `animals`
-   1 `animal` ↔ 1 `listing` (via `listings.animal_id` UNIQUE)

---

### 1.6 `listings`

One listing per animal (1–1).

| Column                   | Type      | Constraints                       | Description                           |
| ------------------------ | --------- | --------------------------------- | ------------------------------------- |
| id                       | UUID      | PK                                | Listing ID                            |
| seller_id                | UUID      | FK → users.id, NOT NULL           | Seller                                |
| animal_id                | UUID      | FK → animals.id, UNIQUE, NOT NULL | The animal this listing is for        |
| title                    | VARCHAR   | NOT NULL                          | Listing title                         |
| price                    | DECIMAL   | NOT NULL                          | Asking price                          |
| currency                 | VARCHAR   | NOT NULL, e.g. `INR`              | Currency code                         |
| is_negotiable            | BOOLEAN   | NOT NULL, default true            | Price negotiable                      |
| listing_type             | VARCHAR   | NOT NULL                          | `sale`, `stud_service`, `adoption`    |
| status                   | VARCHAR   | NOT NULL, default `active`        | `active`, `sold`, `expired`, `hidden` |
| views_count              | INT       | NOT NULL, default 0               | Total views                           |
| bookmarks_count          | INT       | NOT NULL, default 0               | Times bookmarked                      |
| enquiries_call_count     | INT       | NOT NULL, default 0               | Phone enquiries                       |
| enquiries_whatsapp_count | INT       | NOT NULL, default 0               | WhatsApp enquiries                    |
| clicks_count             | INT       | NOT NULL, default 0               | Other CTA clicks (e.g. “View number”) |
| created_at               | TIMESTAMP | NOT NULL                          |                                       |
| updated_at               | TIMESTAMP | NOT NULL                          |                                       |

**Relationships**

-   1 `user` → N `listings`
-   1 `animal` ↔ 1 `listing`
-   1 `listing` → N `listing_media`

---

### 1.7 `listing_media` (images / videos)

| Column     | Type      | Constraints             | Description                      |
| ---------- | --------- | ----------------------- | -------------------------------- |
| id         | UUID      | PK                      | Media ID                         |
| listing_id | UUID      | FK → listings.id        | Parent listing                   |
| media_url  | VARCHAR   | NOT NULL                | URL to image/video               |
| media_type | VARCHAR   | NOT NULL                | `image`, `video`                 |
| is_primary | BOOLEAN   | NOT NULL, default false | True if main display image/video |
| sort_order | INT       | NOT NULL, default 0     | For ordering media in gallery    |
| created_at | TIMESTAMP | NOT NULL                |                                  |
| updated_at | TIMESTAMP | NOT NULL                |                                  |

**Relationships**

-   1 `listing` → N `listing_media`

---

### 1.8 Relationship Summary

-   **1–N**

    -   `users` → `listings`
    -   `users` → `locations` (saved addresses)
    -   `species` → `breeds`
    -   `species` → `animals`
    -   `breeds` → `animals`
    -   `locations` → `animals`
    -   `listings` → `listing_media`

-   **1–1**

    -   `animals` ↔ `listings` (enforced via `listings.animal_id` UNIQUE)

-   **N–M**
    -   None currently; all many-to-many are avoided in this MVP schema.

---

## 2. API Endpoints

### 2.1 Create Listing (with Animal + optional Location)

#### `POST /listings`

**Purpose**

Create a new listing and its animal. Optionally:

-   Use an existing `location_id` or
-   Create a new captured/saved location in the same call.

**Request (JSON)**

```json
{
	"seller_id": "UUID-of-seller",
	"title": "High-yield Gir cow for sale",
	"price": 55000,
	"currency": "INR",
	"is_negotiable": true,
	"listing_type": "sale",
	"animal": {
		"species_id": 1,
		"breed_id": 10,
		"sex": "F",
		"age_months": 36,
		"weight_kg": 450,
		"color_markings": "Brown with white patches",
		"quantity": 1,
		"purpose": "dairy",
		"health_status": "healthy",
		"vaccinated": true,
		"dewormed": true,
		"previous_pregnancies_count": 1,
		"pregnancy_status": "pregnant",
		"milk_yield_litre_per_day": 15,
		"ear_tag_no": "TAG-12345",
		"description": "Calm nature, easy to handle.",
		"suggested_care": "Green fodder, mineral mix, clean shed.",
		"location_id": "existing-location-uuid",
		"new_location": {
			"country": "India",
			"state": "Maharashtra",
			"district": "Pune",
			"city_village": "Baramati",
			"pincode": "413102",
			"lat": 18.15,
			"lng": 74.5833,
			"source_type": "device_gps",
			"source_confidence": "high",
			"save_as_address": true,
			"location_type": "farm"
		}
	},
	"media": [
		{
			"media_url": "https://cdn.app.com/listings/abc1.jpg",
			"media_type": "image",
			"is_primary": true,
			"sort_order": 1
		}
	]
}
```

Notes:

-   Client can either:

    -   Provide `location_id`, **or**
    -   Provide `new_location` object. If `save_as_address = true`, backend should create a `locations` row with `user_id = seller_id`, `is_saved_address = true`.

-   Media is optional in this first call; can also be added later via media APIs.

**Response (201 Created)**

```json
{
	"listing": {
		"id": "listing-uuid",
		"seller_id": "UUID-of-seller",
		"animal_id": "animal-uuid",
		"title": "High-yield Gir cow for sale",
		"price": 55000,
		"currency": "INR",
		"is_negotiable": true,
		"listing_type": "sale",
		"status": "active",
		"views_count": 0,
		"bookmarks_count": 0,
		"enquiries_call_count": 0,
		"enquiries_whatsapp_count": 0,
		"clicks_count": 0,
		"created_at": "2025-11-22T10:00:00Z",
		"updated_at": "2025-11-22T10:00:00Z",
		"animal": {
			"id": "animal-uuid",
			"species_id": 1,
			"breed_id": 10,
			"sex": "F",
			"age_months": 36,
			"weight_kg": 450,
			"color_markings": "Brown with white patches",
			"quantity": 1,
			"purpose": "dairy",
			"health_status": "healthy",
			"vaccinated": true,
			"dewormed": true,
			"previous_pregnancies_count": 1,
			"pregnancy_status": "pregnant",
			"milk_yield_litre_per_day": 15,
			"ear_tag_no": "TAG-12345",
			"description": "Calm nature, easy to handle.",
			"suggested_care": "Green fodder, mineral mix, clean shed.",
			"location": {
				"id": "location-uuid",
				"user_id": "UUID-of-seller",
				"is_saved_address": true,
				"location_type": "farm",
				"country": "India",
				"state": "Maharashtra",
				"district": "Pune",
				"city_village": "Baramati",
				"pincode": "413102",
				"lat": 18.15,
				"lng": 74.5833,
				"source_type": "device_gps",
				"source_confidence": "high"
			}
		},
		"media": [
			{
				"id": "media-uuid",
				"media_url": "https://cdn.app.com/listings/abc1.jpg",
				"media_type": "image",
				"is_primary": true,
				"sort_order": 1
			}
		]
	}
}
```

---

### 2.2 List / Search Listings

#### `GET /listings`

**Purpose**

List active listings with optional filters (species, location, price, etc.).

**Query parameters (examples)**

-   `species_id` (int, optional)
-   `breed_id` (int, optional)
-   `state` (string, optional)
-   `district` (string, optional)
-   `min_price`, `max_price` (optional)
-   `listing_type` (string, optional)
-   `page`, `page_size` (for pagination)

**Request**

```http
GET /listings?species_id=1&state=Maharashtra&page=1&page_size=20
```

**Response (200 OK)**

```json
{
	"items": [
		{
			"id": "listing-uuid",
			"title": "High-yield Gir cow for sale",
			"price": 55000,
			"currency": "INR",
			"is_negotiable": true,
			"listing_type": "sale",
			"status": "active",
			"species_id": 1,
			"breed_id": 10,
			"animal_id": "animal-uuid",
			"thumbnail_url": "https://cdn.app.com/listings/abc1.jpg",
			"location_summary": {
				"state": "Maharashtra",
				"district": "Pune",
				"city_village": "Baramati"
			},
			"created_at": "2025-11-22T10:00:00Z"
		}
	],
	"page": 1,
	"page_size": 20,
	"total": 1
}
```

---

### 2.3 Get Listing Detail

#### `GET /listings/{listing_id}`

**Purpose**

Get full details of a single listing (including animal, location, media).

**Response (200 OK)**

```json
{
	"id": "listing-uuid",
	"seller_id": "UUID-of-seller",
	"animal_id": "animal-uuid",
	"title": "High-yield Gir cow for sale",
	"price": 55000,
	"currency": "INR",
	"is_negotiable": true,
	"listing_type": "sale",
	"status": "active",
	"views_count": 120,
	"bookmarks_count": 10,
	"enquiries_call_count": 5,
	"enquiries_whatsapp_count": 8,
	"clicks_count": 14,
	"created_at": "2025-11-22T10:00:00Z",
	"updated_at": "2025-11-22T11:00:00Z",
	"animal": {
		"...": "full animal object as above"
	},
	"media": [
		{
			"id": "media-uuid",
			"media_url": "https://cdn.app.com/listings/abc1.jpg",
			"media_type": "image",
			"is_primary": true,
			"sort_order": 1
		}
	]
}
```

---

### 2.4 Update Listing (and Animal)

#### `PUT /listings/{listing_id}`

**Purpose**

Edit listing fields and animal details (e.g. price, status, description, suggested care).

**Request (JSON)**

Only fields to update need to be sent (PATCH style with PUT semantics).

```json
{
	"title": "Gir cow – price reduced",
	"price": 52000,
	"status": "active",
	"animal": {
		"description": "Price reduced, urgent sale.",
		"suggested_care": "Green fodder, clean water, regular deworming."
	}
}
```

**Response (200 OK)**

Returns updated listing object (same shape as `GET /listings/{id}`).

---

### 2.5 Create / Capture Location

#### `POST /locations`

**Purpose**

Create a new location. Used for:

-   Saved address for a user (farm/home)
-   Captured location for an animal/listing (not necessarily saved)

**Request (JSON)**

```json
{
	"user_id": "UUID-of-user-or-null",
	"is_saved_address": true,
	"location_type": "farm",
	"country": "India",
	"state": "Maharashtra",
	"district": "Pune",
	"city_village": "Baramati",
	"pincode": "413102",
	"lat": 18.15,
	"lng": 74.5833,
	"source_type": "device_gps",
	"source_confidence": "high"
}
```

-   For **captured-only** (not saved): set `user_id = null`, `is_saved_address = false`.

**Response (201 Created)**

```json
{
	"id": "location-uuid",
	"user_id": "UUID-of-user-or-null",
	"is_saved_address": true,
	"location_type": "farm",
	"country": "India",
	"state": "Maharashtra",
	"district": "Pune",
	"city_village": "Baramati",
	"pincode": "413102",
	"lat": 18.15,
	"lng": 74.5833,
	"source_type": "device_gps",
	"source_confidence": "high",
	"created_at": "2025-11-22T10:05:00Z",
	"updated_at": "2025-11-22T10:05:00Z"
}
```

---

### 2.6 Update Location (PUT for Locations)

#### `PUT /locations/{location_id}`

**Purpose**

Update location details OR convert a captured location into a saved address for a user (e.g. mark as farm).

**Request (JSON)**

```json
{
	"user_id": "UUID-of-user",
	"is_saved_address": true,
	"location_type": "farm",
	"city_village": "New Village Name",
	"pincode": "413103"
}
```

**Response (200 OK)**

```json
{
	"id": "location-uuid",
	"user_id": "UUID-of-user",
	"is_saved_address": true,
	"location_type": "farm",
	"country": "India",
	"state": "Maharashtra",
	"district": "Pune",
	"city_village": "New Village Name",
	"pincode": "413103",
	"lat": 18.15,
	"lng": 74.5833,
	"source_type": "device_gps",
	"source_confidence": "high",
	"created_at": "2025-11-22T10:05:00Z",
	"updated_at": "2025-11-22T11:00:00Z"
}
```

---

### 2.7 Get Saved Locations for a User

#### `GET /users/{user_id}/locations`

**Purpose**

Fetch all saved addresses for a given user (farm, home, etc.).

**Response (200 OK)**

```json
{
	"items": [
		{
			"id": "location-uuid-1",
			"user_id": "UUID-of-user",
			"is_saved_address": true,
			"location_type": "farm",
			"country": "India",
			"state": "Maharashtra",
			"district": "Pune",
			"city_village": "Baramati",
			"pincode": "413102",
			"lat": 18.15,
			"lng": 74.5833,
			"source_type": "device_gps",
			"source_confidence": "high",
			"created_at": "2025-11-22T10:00:00Z",
			"updated_at": "2025-11-22T10:00:00Z"
		}
	]
}
```

---

### 2.8 Add Media to Listing

#### `POST /listings/{listing_id}/media`

**Purpose**

Attach new images/videos to a listing after creation.

**Request (JSON)**

```json
{
	"items": [
		{
			"media_url": "https://cdn.app.com/listings/abc2.jpg",
			"media_type": "image",
			"is_primary": false,
			"sort_order": 2
		},
		{
			"media_url": "https://cdn.app.com/listings/abc3.mp4",
			"media_type": "video",
			"is_primary": false,
			"sort_order": 3
		}
	]
}
```

**Response (201 Created)**

```json
{
	"media": [
		{
			"id": "media-uuid-2",
			"listing_id": "listing-uuid",
			"media_url": "https://cdn.app.com/listings/abc2.jpg",
			"media_type": "image",
			"is_primary": false,
			"sort_order": 2
		},
		{
			"id": "media-uuid-3",
			"listing_id": "listing-uuid",
			"media_url": "https://cdn.app.com/listings/abc3.mp4",
			"media_type": "video",
			"is_primary": false,
			"sort_order": 3
		}
	]
}
```

---

### 2.9 Update Media (PUT for Images/Media)

#### `PUT /listing-media/{media_id}`

**Purpose**

Update a single media item (e.g. mark as primary, change sort order, fix URL).

**Request (JSON)**

```json
{
	"is_primary": true,
	"sort_order": 1
}
```

**Response (200 OK)**

```json
{
	"id": "media-uuid-2",
	"listing_id": "listing-uuid",
	"media_url": "https://cdn.app.com/listings/abc2.jpg",
	"media_type": "image",
	"is_primary": true,
	"sort_order": 1,
	"created_at": "2025-11-22T10:10:00Z",
	"updated_at": "2025-11-22T10:20:00Z"
}
```

---

### 2.10 Create a Custom Requirement

#### `POST /requirements`

**Purpose**

Custom Requirements allow buyers to express needs such as
"Looking for a cow giving more than 10 litres of milk per day."
These relate to an animal_id and are visible to sellers while listing animals.

**Request Body**

```json
{
	"buyer_id": 12,
	"animal_id": 201,
	"title": "Cow giving 10+ litres milk",
	"description": "Healthy cow with high milk output",
	"min_price": 30000,
	"max_price": 60000,
	"location": "Pune"
}
```

**Response**

```json
{
	"requirement_id": 501,
	"message": "Custom requirement created successfully"
}
```

---

### 2.11 Update an Existing Requirement

#### `PUT /requirements/{requirement_id}`

**Request Body**

```json
{
	"title": "Cow giving 12+ litres",
	"max_price": 65000
}
```

---

### 2.12 Delete a Requirement

#### `DELETE /requirements/{requirement_id}`

---

### 2.13 Get All Requirements for a Buyer

#### `GET /requirements/buyer/{buyer_id}`

**Purpose**

Retrieves all active and past requirements of a buyer.

---

### 2.14 Get Matching Requirements for an Animal (For Sellers)

#### `GET /requirements/matching?animal_id={animal_id}`

**Purpose**

Used when a seller lists an animal so the system can show matching requirements.