# Restriction Engine — Architecture Concept

## 1. Business Context

### What is the Partner Portal?

The **Partner Portal** (PartnerHub) is the self-service platform for TravelProviders —
tour operators, travel agencies, and activity providers — to manage their presence on
the consumer-facing platforms (Website, App, reiseagent.ai).

Core functions of the Partner Portal:

| Function | Description |
|---|---|
| **Offer Management** | Create, edit, and publish TravelOffers (group trips, tours, activities) |
| **Media Management** | Upload images, videos, and documents for offers |
| **Profile Management** | Maintain company profile, contact details, branding |
| **Subscription & Billing** | Select and manage pricing plans (Free / Advanced / Premium) via Stripe |
| **Add-on Purchases** | Buy Boosts, AppPlacements, ExtraTrips, Badges, ContentUpgrades |
| **Analytics** | View performance metrics (views, clicks, inquiries) |

### Why Restrictions?

The platform operates on a **tiered pricing model** (Free → Advanced → Premium).
To differentiate plan value, certain portal functions must be **restricted or limited**
based on the provider's active subscription:

- **Free** providers get a basic presence — limited offers, limited media, shorter descriptions.
- **Advanced** providers can create more content with richer media and longer descriptions.
- **Premium** providers have (nearly) unlimited access to all portal features.

Restrictions serve three purposes:

1. **Monetization** — Create clear upgrade incentives by gating functionality behind higher plans.
2. **Content Quality** — Guide providers toward concise, high-quality content at entry level.
3. **Platform Scalability** — Prevent resource abuse (storage, indexing) from free-tier accounts.

### What Gets Restricted?

Restrictions apply to **content creation and editing** within the Partner Portal.
They do **not** affect the consumer-facing display of already-published content.

| Area | Examples |
|---|---|
| **Offer Quantity** | How many TravelOffers a provider can publish |
| **Media per Offer** | Number of images, videos, documents per offer |
| **Text Length per Offer** | Character limits on descriptions, subtitles |
| **Content Sections per Offer** | Number of highlights, itinerary days, included/excluded services, tags |

Restrictions are **not** applied to:

- Profile management (all providers can maintain their profile)
- Analytics access (all providers see their metrics)
- Billing and plan management (all providers can upgrade/downgrade)
- Already-published consumer-facing content (restrictions only affect editing and new content)

---

## 2. Technical Overview

Every `Product` carries a `RestrictionsJson` column that defines the limits a TravelProvider
is subject to while that product is active. Restrictions are evaluated **ad-hoc** at every
relevant API call — no snapshot tables, no background jobs, no eventual consistency.

Restrictions are scoped to **two levels**:

| Scope | Prefix | Enforced on | Example |
|---|---|---|---|
| **TravelProvider** | `provider.*` | Provider account level — counted across all offers | How many offers can this provider have? |
| **TravelOffer** | `offer.*` | Single offer level — checked per individual offer | How many images can this offer have? How long can the description be? |

When a subscription expires or is downgraded and the provider exceeds the new limits,
a **hard-lock** is applied: excess TravelOffers are automatically unpublished and locked.

---

## 3. Schema Change

### New column on `[PartnerHub].[Product]`

| Column | Type | Nullable | Description |
|---|---|---|---|
| `RestrictionsJson` | `NVARCHAR(MAX)` | YES | JSON object keyed by restriction code. Must pass `ISJSON()`. NULL means the product imposes no restrictions (e.g. Badge, ContentUpgrade). |

```sql
ALTER TABLE [PartnerHub].[Product]
    ADD [RestrictionsJson] NVARCHAR(MAX) NULL;
GO

ALTER TABLE [PartnerHub].[Product]
    ADD CONSTRAINT [CHK_Product_RestrictionsJson]
    CHECK ([RestrictionsJson] IS NULL OR ISJSON([RestrictionsJson]) = 1);
GO

EXEC sys.sp_addextendedproperty
    @name       = N'MS_Description',
    @value      = N'JSON object defining plan restrictions keyed by RestrictionCode. '
                + N'Keys are prefixed with scope: provider.* = TravelProvider level, '
                + N'offer.* = per TravelOffer level. '
                + N'Each key maps to an object with "limit" (int, -1 = unlimited, 0 = disabled) '
                + N'and optional "mode" ("set" default, "add" for additive products like ExtraTrips). '
                + N'NULL means the product imposes no restrictions.',
    @level0type = N'SCHEMA',  @level0name = N'PartnerHub',
    @level1type = N'TABLE',   @level1name = N'Product',
    @level2type = N'COLUMN',  @level2name = N'RestrictionsJson';
GO
```

---

## 4. Restriction Code Registry

### 2.1 Convention

**Naming pattern:** `{scope}.{entity_or_field}.{metric}`

| Field | Type | Required | Description |
|---|---|---|---|
| `limit` | `int` | Yes | `-1` = unlimited, `0` = feature disabled / not available, `>0` = hard cap |
| `mode` | `string` | No | `"set"` (default) — value replaces. `"add"` — value is added to the base plan limit. Used only by additive products (ExtraTrips). |

### 2.2 Provider-Level Restrictions (`provider.*`)

Enforced against `[PartnerHub].[TravelOffer]` aggregated per `TravelProviderId`.

| Restriction Code | Metric | How to Measure | DB Reference |
|---|---|---|---|
| `provider.offers.max_count` | Max number of published TravelOffers | `COUNT(*) FROM TravelOffer WHERE TravelProviderId = @Id AND IsPublished = 1 AND IsDeleted = 0` | `TravelOffer.IsPublished` |

### 2.3 Offer-Level Restrictions (`offer.*`)

Enforced against individual fields on a single `[PartnerHub].[TravelOffer]` row.

| Restriction Code | Metric | How to Measure | DB Column |
|---|---|---|---|
| `offer.images.max_count` | Max images per offer | `JSON_QUERY` array length | `TravelOffer.Images` |
| `offer.videos.max_count` | Max videos per offer | `JSON_QUERY` array length | `TravelOffer.Videos` |
| `offer.documents.max_count` | Max documents per offer | `JSON_QUERY` array length | `TravelOffer.Documents` |
| `offer.highlights.max_count` | Max highlight items per offer | `JSON_QUERY` array length | `TravelOffer.Highlights` |
| `offer.itinerary.max_days` | Max itinerary day entries | `JSON_QUERY` array length | `TravelOffer.Itinerary` |
| `offer.subtitle.max_length` | Max characters for subtitle | `LEN(Subtitle)` | `TravelOffer.Subtitle` |
| `offer.detailed_description.max_length` | Max characters for detailed description | `LEN(DetailedDescription)` | `TravelOffer.DetailedDescription` |
| `offer.accommodation_description.max_length` | Max characters for accommodation description | `LEN(AccommodationDescription)` | `TravelOffer.AccommodationDescription` |
| `offer.included_services.max_count` | Max included service items | `JSON_QUERY` array length | `TravelOffer.IncludedServices` |
| `offer.excluded_services.max_count` | Max excluded service items | `JSON_QUERY` array length | `TravelOffer.ExcludedServices` |
| `offer.tags.max_count` | Max tags per offer | Comma-separated count or JSON array length | `TravelOffer.Tags` |

---

## 5. JSON Structure per Plan

### 3.1 Free (`CG_PLAN_FREE_V1`)

```json
{
  "provider.offers.max_count":                   { "limit": 3 },

  "offer.images.max_count":                      { "limit": 5 },
  "offer.videos.max_count":                      { "limit": 0 },
  "offer.documents.max_count":                   { "limit": 0 },
  "offer.highlights.max_count":                  { "limit": 3 },
  "offer.itinerary.max_days":                    { "limit": 0 },
  "offer.subtitle.max_length":                   { "limit": 200 },
  "offer.detailed_description.max_length":       { "limit": 500 },
  "offer.accommodation_description.max_length":  { "limit": 0 },
  "offer.included_services.max_count":           { "limit": 5 },
  "offer.excluded_services.max_count":           { "limit": 3 },
  "offer.tags.max_count":                        { "limit": 3 }
}
```

### 3.2 Advanced (`CG_PLAN_ADV_MONTHLY_V1` / `CG_PLAN_ADV_ANNUAL_V1`)

```json
{
  "provider.offers.max_count":                   { "limit": 15 },

  "offer.images.max_count":                      { "limit": 20 },
  "offer.videos.max_count":                      { "limit": 3 },
  "offer.documents.max_count":                   { "limit": 5 },
  "offer.highlights.max_count":                  { "limit": 10 },
  "offer.itinerary.max_days":                    { "limit": 30 },
  "offer.subtitle.max_length":                   { "limit": 500 },
  "offer.detailed_description.max_length":       { "limit": 3000 },
  "offer.accommodation_description.max_length":  { "limit": 1000 },
  "offer.included_services.max_count":           { "limit": 15 },
  "offer.excluded_services.max_count":           { "limit": 10 },
  "offer.tags.max_count":                        { "limit": 10 }
}
```

### 3.3 Premium (`CG_PLAN_PREM_MONTHLY_V1` / `CG_PLAN_PREM_ANNUAL_V1`)

```json
{
  "provider.offers.max_count":                   { "limit": -1 },

  "offer.images.max_count":                      { "limit": -1 },
  "offer.videos.max_count":                      { "limit": 10 },
  "offer.documents.max_count":                   { "limit": -1 },
  "offer.highlights.max_count":                  { "limit": -1 },
  "offer.itinerary.max_days":                    { "limit": -1 },
  "offer.subtitle.max_length":                   { "limit": 500 },
  "offer.detailed_description.max_length":       { "limit": -1 },
  "offer.accommodation_description.max_length":  { "limit": -1 },
  "offer.included_services.max_count":           { "limit": -1 },
  "offer.excluded_services.max_count":           { "limit": -1 },
  "offer.tags.max_count":                        { "limit": -1 }
}
```

### 3.4 ExtraTrips Products (Additive — Provider-Level Only)

**Paket S** (`CG_EXTRA_TRIPS_S_V1`)
```json
{
  "provider.offers.max_count": { "limit": 10, "mode": "add" }
}
```

**Paket M** (`CG_EXTRA_TRIPS_M_V1`)
```json
{
  "provider.offers.max_count": { "limit": 25, "mode": "add" }
}
```

**Paket L** (`CG_EXTRA_TRIPS_L_V1`)
```json
{
  "provider.offers.max_count": { "limit": 50, "mode": "add" }
}
```

### 3.5 Products Without Restrictions

Products that do not impose restrictions have `RestrictionsJson = NULL`:

- Badge (`CG_BADGE_VERIFIED_V1`)
- ContentUpgrade (`CG_CONTENT_UP_FREE_V1`, `CG_CONTENT_UP_ADV_V1`)
- Boost (`CG_BOOST_TRAVEL_MONTHLY_V1`, `CG_BOOST_PROFILE_MONTHLY_V1`)
- AppPlacement (`CG_APP_DEAL_WEEK_V1`, `CG_APP_TOP_WEEK_V1`)

### 3.6 Plan Comparison Matrix

| Restriction Code | Free | Advanced | Premium |
|---|---|---|---|
| **Provider Level** | | | |
| `provider.offers.max_count` | 3 | 15 | unlimited |
| **Offer Level — Media** | | | |
| `offer.images.max_count` | 5 | 20 | unlimited |
| `offer.videos.max_count` | disabled | 3 | 10 |
| `offer.documents.max_count` | disabled | 5 | unlimited |
| **Offer Level — Text** | | | |
| `offer.subtitle.max_length` | 200 chars | 500 chars | 500 chars |
| `offer.detailed_description.max_length` | 500 chars | 3 000 chars | unlimited |
| `offer.accommodation_description.max_length` | disabled | 1 000 chars | unlimited |
| **Offer Level — Lists** | | | |
| `offer.highlights.max_count` | 3 | 10 | unlimited |
| `offer.itinerary.max_days` | disabled | 30 | unlimited |
| `offer.included_services.max_count` | 5 | 15 | unlimited |
| `offer.excluded_services.max_count` | 3 | 10 | unlimited |
| `offer.tags.max_count` | 3 | 10 | unlimited |

### 3.7 Extensibility

Adding a new restriction requires **no schema change**. Examples:

| Future Feature | Code | Change |
|---|---|---|
| AI-generated descriptions | `offer.ai_description.enabled` | `{ "limit": 1 }` on Premium, `{ "limit": 0 }` on others |
| Max upload file size | `offer.upload.max_size_mb` | `{ "limit": 5 }` on Free, `{ "limit": 50 }` on Premium |
| Social media posts | `provider.social_posts.max_per_month` | `{ "limit": 4 }` |
| Available spots | `offer.available_spots.max_count` | `{ "limit": 1 }` on Free, `{ "limit": -1 }` on Premium |
| Thumbnails per offer | `offer.thumbnails.max_count` | `{ "limit": 1 }` on Free, `{ "limit": 5 }` on Premium |
| New ExtraTrips XL | `provider.offers.max_count` | New Product row: `{ "limit": 100, "mode": "add" }` |

---

## 6. Ad-hoc Stored Procedure

### `sp_portal_restrictionCheck`

Returns the effective restrictions for a given TravelProvider by merging the active plan
with all active additive products (ExtraTrips). Returns **both** provider-level and
offer-level restrictions in one result set — the caller filters by prefix.

```sql
CREATE OR ALTER PROCEDURE [PartnerHub].[sp_portal_restrictionCheck]
    @TravelProviderId BIGINT
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @Now DATETIME2(7) = SYSUTCDATETIME();

    -------------------------------------------------------------------
    -- 1. Find the active Plan for this provider
    -------------------------------------------------------------------
    DECLARE @PlanRestrictionsJson NVARCHAR(MAX);
    DECLARE @PlanProductTitle     NVARCHAR(120);
    DECLARE @PlanValidTo          DATETIME2(7);

    SELECT TOP 1
        @PlanRestrictionsJson = p.RestrictionsJson,
        @PlanProductTitle     = p.Title,
        @PlanValidTo          = o.ValidTo
    FROM PartnerHub.ProviderOrder o
    JOIN PartnerHub.Product p ON o.ProductId = p.Id
    WHERE o.TravelProviderId = @TravelProviderId
      AND p.ProductType      = 'Plan'
      AND o.Status IN ('Active', 'PastDue', 'Cancelled')
      AND (o.ValidTo IS NULL OR o.ValidTo > @Now)
    ORDER BY o.ValidFrom DESC;  -- latest plan wins

    -- Fallback: no active plan → load Free plan restrictions
    IF @PlanRestrictionsJson IS NULL
    BEGIN
        SELECT
            @PlanRestrictionsJson = p.RestrictionsJson,
            @PlanProductTitle     = 'Free (Fallback)'
        FROM PartnerHub.Product p
        WHERE p.ProductCode = 'CG_PLAN_FREE_V1'
          AND p.IsActive = 1;

        SET @PlanValidTo = NULL;
    END;

    -------------------------------------------------------------------
    -- 2. Parse base plan restrictions into a temp table
    -------------------------------------------------------------------
    CREATE TABLE #Restrictions (
        RestrictionCode NVARCHAR(100) NOT NULL PRIMARY KEY,
        Scope           NVARCHAR(20)  NOT NULL,   -- 'provider' or 'offer'
        BaseLimit       INT           NOT NULL,
        AddonBonus      INT           NOT NULL DEFAULT 0,
        EffectiveLimit  INT           NOT NULL
    );

    INSERT INTO #Restrictions (RestrictionCode, Scope, BaseLimit, EffectiveLimit)
    SELECT
        j.[key],
        CASE
            WHEN j.[key] LIKE 'provider.%' THEN 'provider'
            WHEN j.[key] LIKE 'offer.%'    THEN 'offer'
            ELSE 'unknown'
        END,
        CAST(JSON_VALUE(j.[value], '$.limit') AS INT),
        CAST(JSON_VALUE(j.[value], '$.limit') AS INT)
    FROM OPENJSON(@PlanRestrictionsJson) j;

    -------------------------------------------------------------------
    -- 3. Add bonuses from active ExtraTrips (mode = "add")
    -------------------------------------------------------------------
    ;WITH AddonValues AS (
        SELECT
            j.[key]                                        AS RestrictionCode,
            CAST(JSON_VALUE(j.[value], '$.limit') AS INT)  AS AddonLimit
        FROM PartnerHub.ProviderOrder o
        JOIN PartnerHub.Product p ON o.ProductId = p.Id
        CROSS APPLY OPENJSON(p.RestrictionsJson) j
        WHERE o.TravelProviderId = @TravelProviderId
          AND p.ProductType      = 'ExtraTrips'
          AND o.Status IN ('Active', 'PastDue', 'Cancelled')
          AND (o.ValidTo IS NULL OR o.ValidTo > @Now)
          AND ISNULL(JSON_VALUE(j.[value], '$.mode'), 'set') = 'add'
    ),
    AddonSums AS (
        SELECT RestrictionCode, SUM(AddonLimit) AS TotalBonus
        FROM AddonValues
        GROUP BY RestrictionCode
    )
    UPDATE r
    SET r.AddonBonus     = a.TotalBonus,
        r.EffectiveLimit = CASE
                               WHEN r.BaseLimit = -1 THEN -1
                               ELSE r.BaseLimit + a.TotalBonus
                           END
    FROM #Restrictions r
    JOIN AddonSums a ON a.RestrictionCode = r.RestrictionCode;

    -------------------------------------------------------------------
    -- 4. Return results
    -------------------------------------------------------------------
    SELECT
        @TravelProviderId   AS TravelProviderId,
        @PlanProductTitle   AS ActivePlan,
        @PlanValidTo        AS PlanValidTo,
        r.Scope,
        r.RestrictionCode,
        r.BaseLimit,
        r.AddonBonus,
        r.EffectiveLimit
    FROM #Restrictions r
    ORDER BY r.Scope, r.RestrictionCode;

    DROP TABLE #Restrictions;
END;
GO
```

### Usage

```sql
-- All restrictions for provider 42
EXEC [PartnerHub].[sp_portal_restrictionCheck] @TravelProviderId = 42;
```

### Example Result

| TravelProviderId | ActivePlan | PlanValidTo | Scope | RestrictionCode | BaseLimit | AddonBonus | EffectiveLimit |
|---|---|---|---|---|---|---|---|
| 42 | Advanced | 2026-05-01 | provider | provider.offers.max_count | 15 | 10 | 25 |
| 42 | Advanced | 2026-05-01 | offer | offer.accommodation_description.max_length | 1000 | 0 | 1000 |
| 42 | Advanced | 2026-05-01 | offer | offer.detailed_description.max_length | 3000 | 0 | 3000 |
| 42 | Advanced | 2026-05-01 | offer | offer.documents.max_count | 5 | 0 | 5 |
| 42 | Advanced | 2026-05-01 | offer | offer.excluded_services.max_count | 10 | 0 | 10 |
| 42 | Advanced | 2026-05-01 | offer | offer.highlights.max_count | 10 | 0 | 10 |
| 42 | Advanced | 2026-05-01 | offer | offer.images.max_count | 20 | 0 | 20 |
| 42 | Advanced | 2026-05-01 | offer | offer.included_services.max_count | 15 | 0 | 15 |
| 42 | Advanced | 2026-05-01 | offer | offer.itinerary.max_days | 30 | 0 | 30 |
| 42 | Advanced | 2026-05-01 | offer | offer.subtitle.max_length | 500 | 0 | 500 |
| 42 | Advanced | 2026-05-01 | offer | offer.tags.max_count | 10 | 0 | 10 |
| 42 | Advanced | 2026-05-01 | offer | offer.videos.max_count | 3 | 0 | 3 |

---

## 7. Enforcement

### 5.1 Where Each Scope Is Enforced

```
┌─────────────────────────────────────────────────────────────────────┐
│                        API Request                                  │
│                                                                     │
│  ┌──────────────────────────────────────────────────────────────┐   │
│  │ Provider-Level Check (provider.*)                            │   │
│  │                                                              │   │
│  │ Triggered by:                                                │   │
│  │  • POST /offers              → Create new offer              │   │
│  │  • PUT  /offers/{id}/publish → Publish offer                 │   │
│  │                                                              │   │
│  │ Validates:                                                   │   │
│  │  provider.offers.max_count    → COUNT published offers       │   │
│  │  provider.offers.max_draft_count → COUNT draft offers        │   │
│  └──────────────────────────────────────────────────────────────┘   │
│                                                                     │
│  ┌──────────────────────────────────────────────────────────────┐   │
│  │ Offer-Level Check (offer.*)                                  │   │
│  │                                                              │   │
│  │ Triggered by:                                                │   │
│  │  • PUT /offers/{id}          → Save offer                    │   │
│  │  • POST /offers/{id}/images  → Upload image                  │   │
│  │  • POST /offers/{id}/videos  → Upload video                  │   │
│  │  • POST /offers/{id}/docs    → Upload document               │   │
│  │                                                              │   │
│  │ Validates per offer:                                         │   │
│  │  offer.images.max_count      → images array length           │   │
│  │  offer.title.max_length      → LEN(Title)                   │   │
│  │  offer.detailed_description.max_length → LEN(...)            │   │
│  │  ... etc.                                                    │   │
│  └──────────────────────────────────────────────────────────────┘   │
└─────────────────────────────────────────────────────────────────────┘
```

### 5.2 C# Service

```csharp
public class RestrictionService(AppDbContext db)
{
    /// Returns all effective restrictions, grouped by scope.
    public async Task<RestrictionResult> GetEffective(long providerId)
    {
        var rows = await db.Database
            .SqlQueryRaw<RestrictionRow>(
                "EXEC [PartnerHub].[sp_portal_restrictionCheck] @TravelProviderId = {0}",
                providerId)
            .ToListAsync();

        return new RestrictionResult
        {
            ActivePlan  = rows.FirstOrDefault()?.ActivePlan ?? "Free",
            PlanValidTo = rows.FirstOrDefault()?.PlanValidTo,
            Provider    = rows.Where(r => r.Scope == "provider").ToDictionary(r => r.RestrictionCode, r => r.EffectiveLimit),
            Offer       = rows.Where(r => r.Scope == "offer").ToDictionary(r => r.RestrictionCode, r => r.EffectiveLimit)
        };
    }

    /// Enforce a provider-level restriction.
    public async Task EnforceProvider(long providerId, string code, int currentUsage)
    {
        var restrictions = await GetEffective(providerId);
        if (!restrictions.Provider.TryGetValue(code, out var limit)) return;
        if (limit != -1 && currentUsage >= limit)
            throw new RestrictionExceededException(code, limit, currentUsage);
    }

    /// Enforce an offer-level restriction.
    public async Task EnforceOffer(long providerId, string code, int currentValue)
    {
        var restrictions = await GetEffective(providerId);
        if (!restrictions.Offer.TryGetValue(code, out var limit)) return;
        if (limit != -1 && currentValue >= limit)
            throw new RestrictionExceededException(code, limit, currentValue);
    }
}
```

**Usage in controllers:**

```csharp
// Creating a new offer → provider-level check
var publishedCount = await db.TravelOffers
    .CountAsync(o => o.TravelProviderId == providerId && o.IsPublished && !o.IsDeleted);
await restrictionService.EnforceProvider(providerId, "provider.offers.max_count", publishedCount);

// Uploading an image → offer-level check
var imageCount = GetJsonArrayLength(offer.Images);
await restrictionService.EnforceOffer(providerId, "offer.images.max_count", imageCount);

// Saving description text → offer-level check
await restrictionService.EnforceOffer(providerId, "offer.detailed_description.max_length", text.Length);
```

---

## 8. Subscription Lifecycle & Hard-Lock Behaviour

### 6.1 Lifecycle Matrix

| # | Scenario | ProviderOrder.Status | ValidTo vs Now | Effective Plan | Restrictions Apply? |
|---|---|---|---|---|---|
| 1 | Active subscription, payment current | `Active` | Future | Purchased plan | Yes — purchased plan limits |
| 2 | Payment failed, Stripe retrying | `PastDue` | Future | Purchased plan | Yes — grace period, no downgrade |
| 3 | Provider cancelled, paid period remaining | `Cancelled` | Future | Purchased plan | Yes — access until ValidTo |
| 4 | Provider cancelled, paid period ended | `Cancelled` | Past | **Free (Fallback)** | Yes — hard-lock triggered |
| 5 | Subscription expired (OneTime duration ended) | `Expired` | Past | **Free (Fallback)** | Yes — hard-lock triggered |
| 6 | Subscription paused (e.g. trial ended) | `Paused` | — | **Free (Fallback)** | Yes — hard-lock triggered |
| 7 | Initial payment not confirmed | `Incomplete` | — | **Free (Fallback)** | Yes — never activated |
| 8 | Downgrade Premium → Advanced (mid-cycle) | Old: `Cancelled` / New: `Active` | New: Future | Advanced | Yes — hard-lock if over new limits |
| 9 | Upgrade Advanced → Premium | Old: replaced / New: `Active` | New: Future | Premium | Yes — limits relaxed instantly |
| 10 | ExtraTrips cancelled, base plan still active | ExtraTrips: `Cancelled` (past ValidTo) | Past for addon | Base plan only | Yes — hard-lock if over base limits |

### 6.2 Hard-Lock: Automatic Unpublish & Lock

When the effective `provider.offers.max_count` drops below the number of currently published
offers, the system must automatically unpublish and lock excess offers.

**Offer-level restrictions are NOT enforced retroactively.** If a provider had 20 images
on an offer under Premium and downgrades to Free (limit 5), the existing images are
**not deleted**. The offer is locked and the provider cannot edit it until they either
remove content to fit the new limits or upgrade again.

| Scope | Hard-Lock Behaviour |
|---|---|
| `provider.offers.max_count` | Excess offers are unpublished (`IsPublished = 0`) and locked (`IsLocked = 1`). Oldest published first. |
| `offer.*` restrictions | Existing content is **preserved** but the offer is **locked** (`IsLocked = 1`). Provider must reduce content or upgrade to unlock. |

#### Stored Procedure: `sp_portal_restrictionEnforceHardLock`

```sql
CREATE OR ALTER PROCEDURE [PartnerHub].[sp_portal_restrictionEnforceHardLock]
    @TravelProviderId BIGINT
AS
BEGIN
    SET NOCOUNT ON;

    -------------------------------------------------------------------
    -- 1. Get effective restrictions
    -------------------------------------------------------------------
    CREATE TABLE #Limits (
        TravelProviderId BIGINT,
        ActivePlan       NVARCHAR(120),
        PlanValidTo      DATETIME2(7),
        Scope            NVARCHAR(20),
        RestrictionCode  NVARCHAR(100),
        BaseLimit        INT,
        AddonBonus       INT,
        EffectiveLimit   INT
    );

    INSERT INTO #Limits
    EXEC [PartnerHub].[sp_portal_restrictionCheck] @TravelProviderId;

    -------------------------------------------------------------------
    -- 2. Enforce provider.offers.max_count — unpublish excess
    -------------------------------------------------------------------
    DECLARE @MaxOffers INT;
    SELECT @MaxOffers = EffectiveLimit
    FROM #Limits WHERE RestrictionCode = 'provider.offers.max_count';

    DECLARE @OffersUnpublished INT = 0;

    IF @MaxOffers IS NOT NULL AND @MaxOffers != -1
    BEGIN
        DECLARE @PublishedCount INT;
        SELECT @PublishedCount = COUNT(*)
        FROM PartnerHub.TravelOffer
        WHERE TravelProviderId = @TravelProviderId
          AND IsPublished = 1 AND IsDeleted = 0;

        IF @PublishedCount > @MaxOffers
        BEGIN
            DECLARE @ExcessCount INT = @PublishedCount - @MaxOffers;

            ;WITH OffersToLock AS (
                SELECT TOP (@ExcessCount) Id
                FROM PartnerHub.TravelOffer
                WHERE TravelProviderId = @TravelProviderId
                  AND IsPublished = 1 AND IsDeleted = 0
                ORDER BY PublishedAt ASC   -- oldest published first
            )
            UPDATE PartnerHub.TravelOffer
            SET IsPublished = 0,
                IsLocked    = 1,
                UpdatedAt   = SYSUTCDATETIME()
            FROM PartnerHub.TravelOffer t
            JOIN OffersToLock ol ON t.Id = ol.Id;

            SET @OffersUnpublished = @ExcessCount;
        END;
    END;

    -------------------------------------------------------------------
    -- 3. Enforce offer-level restrictions — lock non-compliant offers
    --    Content is NOT deleted, but the offer cannot be edited/published
    --    until the provider reduces content or upgrades.
    -------------------------------------------------------------------
    DECLARE @OffersLockedContent INT = 0;

    -- Get offer-level limits into variables for the check
    DECLARE @MaxImages     INT, @MaxVideos     INT,
            @MaxDocuments  INT, @MaxHighlights INT, @MaxItinerary INT,
            @MaxSubtitleLen INT, @MaxDetailDesc INT, @MaxAccomDesc INT,
            @MaxInclServ   INT, @MaxExclServ   INT, @MaxTags      INT;

    SELECT @MaxImages     = EffectiveLimit FROM #Limits WHERE RestrictionCode = 'offer.images.max_count';
    SELECT @MaxVideos     = EffectiveLimit FROM #Limits WHERE RestrictionCode = 'offer.videos.max_count';
    SELECT @MaxDocuments  = EffectiveLimit FROM #Limits WHERE RestrictionCode = 'offer.documents.max_count';
    SELECT @MaxHighlights = EffectiveLimit FROM #Limits WHERE RestrictionCode = 'offer.highlights.max_count';
    SELECT @MaxItinerary  = EffectiveLimit FROM #Limits WHERE RestrictionCode = 'offer.itinerary.max_days';
    SELECT @MaxSubtitleLen= EffectiveLimit FROM #Limits WHERE RestrictionCode = 'offer.subtitle.max_length';
    SELECT @MaxDetailDesc = EffectiveLimit FROM #Limits WHERE RestrictionCode = 'offer.detailed_description.max_length';
    SELECT @MaxAccomDesc  = EffectiveLimit FROM #Limits WHERE RestrictionCode = 'offer.accommodation_description.max_length';
    SELECT @MaxInclServ   = EffectiveLimit FROM #Limits WHERE RestrictionCode = 'offer.included_services.max_count';
    SELECT @MaxExclServ   = EffectiveLimit FROM #Limits WHERE RestrictionCode = 'offer.excluded_services.max_count';
    SELECT @MaxTags       = EffectiveLimit FROM #Limits WHERE RestrictionCode = 'offer.tags.max_count';

    -- Lock any offer that violates ANY offer-level restriction
    -- (only check offers that are not already locked)
    UPDATE PartnerHub.TravelOffer
    SET IsLocked  = 1,
        UpdatedAt = SYSUTCDATETIME()
    WHERE TravelProviderId = @TravelProviderId
      AND IsDeleted = 0
      AND IsLocked = 0
      AND (
          -- Media counts
          (@MaxImages    != -1 AND @MaxImages    IS NOT NULL AND ISJSON(Images)    = 1 AND (SELECT COUNT(*) FROM OPENJSON(Images))    > @MaxImages)
       OR (@MaxVideos    != -1 AND @MaxVideos    IS NOT NULL AND ISJSON(Videos)    = 1 AND (SELECT COUNT(*) FROM OPENJSON(Videos))    > @MaxVideos)
       OR (@MaxDocuments != -1 AND @MaxDocuments IS NOT NULL AND ISJSON(Documents) = 1 AND (SELECT COUNT(*) FROM OPENJSON(Documents)) > @MaxDocuments)
       OR (@MaxHighlights!= -1 AND @MaxHighlights IS NOT NULL AND ISJSON(Highlights)= 1 AND (SELECT COUNT(*) FROM OPENJSON(Highlights))> @MaxHighlights)
       OR (@MaxItinerary != -1 AND @MaxItinerary IS NOT NULL AND ISJSON(Itinerary) = 1 AND (SELECT COUNT(*) FROM OPENJSON(Itinerary)) > @MaxItinerary)
       OR (@MaxInclServ  != -1 AND @MaxInclServ  IS NOT NULL AND ISJSON(IncludedServices)= 1 AND (SELECT COUNT(*) FROM OPENJSON(IncludedServices))> @MaxInclServ)
       OR (@MaxExclServ  != -1 AND @MaxExclServ  IS NOT NULL AND ISJSON(ExcludedServices)= 1 AND (SELECT COUNT(*) FROM OPENJSON(ExcludedServices))> @MaxExclServ)
          -- Text lengths
       OR (@MaxSubtitleLen!=-1 AND @MaxSubtitleLen IS NOT NULL AND LEN(ISNULL(Subtitle,'')) > @MaxSubtitleLen)
       OR (@MaxDetailDesc!= -1 AND @MaxDetailDesc IS NOT NULL AND LEN(ISNULL(DetailedDescription,'')) > @MaxDetailDesc)
       OR (@MaxAccomDesc != -1 AND @MaxAccomDesc IS NOT NULL AND LEN(ISNULL(AccommodationDescription,'')) > @MaxAccomDesc)
      );

    SET @OffersLockedContent = @@ROWCOUNT;

    DROP TABLE #Limits;

    -------------------------------------------------------------------
    -- 4. Return summary
    -------------------------------------------------------------------
    SELECT
        @TravelProviderId     AS TravelProviderId,
        @OffersUnpublished    AS OffersUnpublished,
        @OffersLockedContent  AS OffersLockedForContent;
END;
GO
```

### 6.3 When to Call Hard-Lock Enforcement

| Trigger Event | Source | Action |
|---|---|---|
| Stripe webhook: `customer.subscription.updated` | Plan downgrade or ExtraTrips cancellation | Call `sp_portal_restrictionEnforceHardLock` |
| Stripe webhook: `customer.subscription.deleted` | Subscription ended | Call `sp_portal_restrictionEnforceHardLock` |
| Stripe webhook: `invoice.payment_failed` (final attempt) | Payment exhausted | Call `sp_portal_restrictionEnforceHardLock` |
| Scheduled job (every 15 min) | Safety net for missed webhooks | Call for all providers where `ValidTo < NOW()` |
| Manual admin action | Support / admin portal | Call on demand |

### 6.4 Hard-Lock Flow

```
Stripe Webhook (subscription.updated / deleted)
     │
     ▼
Webhook Handler
     │
     ├── 1. Update ProviderOrder (Status, ValidTo)
     │
     ├── 2. EXEC sp_portal_restrictionEnforceHardLock
     │       │
     │       ├── provider.offers.max_count exceeded?
     │       │     YES → Unpublish oldest offers, set IsLocked = 1
     │       │
     │       ├── offer.* restrictions exceeded on any offer?
     │       │     YES → Set IsLocked = 1 (content preserved)
     │       │
     │       └── Return: OffersUnpublished, OffersLockedForContent
     │
     └── 3. If any offers affected:
             ├── Send email notification to provider
             └── Log event for admin audit trail
```

### 6.5 Provider Notification (on Hard-Lock)

When offers are affected, the provider receives an email:

> **Subject:** Your plan has changed — action required for your offers
>
> Your subscription has changed to **Free**. Some of your offers are affected:
>
> - **9 offers** have been unpublished because your plan allows a maximum of **3 active offers**.
> - **4 offers** have been locked because they contain content (images, descriptions)
>   that exceeds your current plan limits.
>
> **To restore your offers**, you can:
> 1. Upgrade your plan to Advanced or Premium
> 2. Add an ExtraTrips package for more offers
> 3. Edit locked offers to reduce content within your Free plan limits
>
> [View your offers →]

---

## 9. API Endpoints

### `GET /api/providers/{id}/restrictions`

Returns all effective restrictions with current usage for the UI.

```json
{
  "travelProviderId": 42,
  "activePlan": "Advanced",
  "planValidTo": "2026-05-01T00:00:00Z",
  "provider": [
    {
      "code": "provider.offers.max_count",
      "effectiveLimit": 25,
      "used": 18,
      "remaining": 7
    }
  ],
  "offer": [
    { "code": "offer.images.max_count",                     "effectiveLimit": 20 },
    { "code": "offer.videos.max_count",                     "effectiveLimit": 3 },
    { "code": "offer.documents.max_count",                  "effectiveLimit": 5 },
    { "code": "offer.highlights.max_count",                 "effectiveLimit": 10 },
    { "code": "offer.itinerary.max_days",                   "effectiveLimit": 30 },
    { "code": "offer.subtitle.max_length",                  "effectiveLimit": 500 },
    { "code": "offer.detailed_description.max_length",      "effectiveLimit": 3000 },
    { "code": "offer.accommodation_description.max_length", "effectiveLimit": 1000 },
    { "code": "offer.included_services.max_count",          "effectiveLimit": 15 },
    { "code": "offer.excluded_services.max_count",          "effectiveLimit": 10 },
    { "code": "offer.tags.max_count",                       "effectiveLimit": 10 }
  ]
}
```

**Note:** `used` and `remaining` are only returned for `provider.*` restrictions because
they are counted across all offers. For `offer.*` restrictions the usage depends on the
individual offer being edited — the UI calculates this client-side from the offer data.

### `GET /api/providers/{id}/offers/{offerId}/restrictions`

Returns offer-level restrictions with **usage for a specific offer**.

```json
{
  "travelOfferId": 510,
  "isLocked": false,
  "violations": [],
  "restrictions": [
    { "code": "offer.images.max_count",                     "limit": 20,   "used": 12,  "remaining": 8 },
    { "code": "offer.videos.max_count",                     "limit": 3,    "used": 0,   "remaining": 3 },
    { "code": "offer.documents.max_count",                  "limit": 5,    "used": 2,   "remaining": 3 },
    { "code": "offer.highlights.max_count",                 "limit": 10,   "used": 6,   "remaining": 4 },
    { "code": "offer.subtitle.max_length",                  "limit": 500,  "used": 85,  "remaining": 415 },
    { "code": "offer.detailed_description.max_length",      "limit": 3000, "used": 1850,"remaining": 1150 },
    { "code": "offer.accommodation_description.max_length", "limit": 1000, "used": 320, "remaining": 680 }
  ]
}
```

For a locked offer after downgrade:

```json
{
  "travelOfferId": 510,
  "isLocked": true,
  "violations": [
    { "code": "offer.images.max_count",          "limit": 5, "used": 12, "over": 7 },
    { "code": "offer.detailed_description.max_length", "limit": 500, "used": 1850, "over": 1350 }
  ],
  "restrictions": [ ... ]
}
```

---

## 10. Summary

| Aspect | Decision |
|---|---|
| **Scope model** | Two prefixes: `provider.*` (account-level), `offer.*` (per-offer-level) |
| **Storage** | `RestrictionsJson` column on `Product` — no extra tables |
| **Evaluation** | Ad-hoc via `sp_portal_restrictionCheck` — no cache, no snapshot |
| **Naming** | `{scope}.{entity_or_field}.{metric}` — e.g. `offer.images.max_count` |
| **DB mapping** | Every restriction code maps to a specific column on `TravelOffer` or an aggregate on `TravelProvider` |
| **Consistency** | Always real-time, no stale state |
| **Extensibility** | New restriction = new JSON key, no schema change |
| **Additive products** | `"mode": "add"` in ExtraTrips JSON, summed at query time (provider-level only) |
| **Fallback** | No active plan → Free plan restrictions apply |
| **Hard-lock (provider)** | Excess offers auto-unpublished + `IsLocked = 1`, oldest first |
| **Hard-lock (offer)** | Content preserved, offer locked — provider must reduce or upgrade |
| **Trigger** | Stripe webhooks + scheduled safety-net job (every 15 min) |
| **Notification** | Email to provider listing unpublished + locked offers |