# Database Schema - MKT Zalo Service

Database design and schema documentation.

## Tables Overview

9 tables with relationships:
- `ZaloCustomers` - Customer profiles
- `CustomerTags` - Customer tags (many-to-many)
- `Conversations` - Chat sessions
- `Messages` - All messages
- `ChatbotRules` - Automation rules
- `RuleConditions` - Rule conditions
- `RuleActions` - Rule actions
- `MessageTemplates` - ZNS templates
- `TemplateParameters` - Template parameters

---

## Core Tables

### ZaloCustomers

```sql
CREATE TABLE "ZaloCustomers" (
    "Id" UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    "ZaloUserId" VARCHAR(50) NOT NULL UNIQUE,
    "DisplayName" VARCHAR(255) NOT NULL,
    "AvatarUrl" VARCHAR(500),
    "PhoneNumber" VARCHAR(20),
    "Email" VARCHAR(255),
    "Segment" INT NOT NULL DEFAULT 0,
    "FirstInteractionAt" TIMESTAMPTZ NOT NULL,
    "LastInteractionAt" TIMESTAMPTZ NOT NULL,
    "CreatedAt" TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    "UpdatedAt" TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX "IX_ZaloCustomers_ZaloUserId" ON "ZaloCustomers" ("ZaloUserId");
CREATE INDEX "IX_ZaloCustomers_Segment" ON "ZaloCustomers" ("Segment");
```

### Conversations

```sql
CREATE TABLE "Conversations" (
    "Id" UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    "CustomerId" UUID NOT NULL REFERENCES "ZaloCustomers"("Id") ON DELETE CASCADE,
    "ZaloUserId" VARCHAR(50) NOT NULL,
    "Status" INT NOT NULL DEFAULT 0,
    "StartedAt" TIMESTAMPTZ NOT NULL,
    "EndedAt" TIMESTAMPTZ,
    "CreatedAt" TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    "UpdatedAt" TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX "IX_Conversations_CustomerId" ON "Conversations" ("CustomerId");
CREATE INDEX "IX_Conversations_Status_StartedAt" ON "Conversations" ("Status", "StartedAt" DESC);
```

### Messages

```sql
CREATE TABLE "Messages" (
    "Id" UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    "ConversationId" UUID NOT NULL REFERENCES "Conversations"("Id") ON DELETE CASCADE,
    "Type" INT NOT NULL,
    "Content" TEXT NOT NULL,
    "Direction" INT NOT NULL,
    "IsFromBot" BOOLEAN NOT NULL DEFAULT FALSE,
    "SentAt" TIMESTAMPTZ NOT NULL,
    "ZaloMessageId" VARCHAR(100),
    "CreatedAt" TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX "IX_Messages_ConversationId_SentAt" ON "Messages" ("ConversationId", "SentAt" DESC);
CREATE INDEX "IX_Messages_Content_FullText" ON "Messages" USING GIN (to_tsvector('english', "Content"));
```

---

## Automation Tables

### ChatbotRules

```sql
CREATE TABLE "ChatbotRules" (
    "Id" UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    "Name" VARCHAR(255) NOT NULL,
    "Type" INT NOT NULL,
    "Priority" INT NOT NULL DEFAULT 50,
    "IsActive" BOOLEAN NOT NULL DEFAULT TRUE,
    "MatchCount" INT NOT NULL DEFAULT 0,
    "LastMatchedAt" TIMESTAMPTZ,
    "CreatedAt" TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    "UpdatedAt" TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX "IX_ChatbotRules_IsActive_Priority" ON "ChatbotRules" ("IsActive", "Priority" DESC);
```

### RuleConditions

```sql
CREATE TABLE "RuleConditions" (
    "Id" UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    "RuleId" UUID NOT NULL REFERENCES "ChatbotRules"("Id") ON DELETE CASCADE,
    "Field" VARCHAR(50) NOT NULL,
    "Operator" VARCHAR(50) NOT NULL,
    "Value" TEXT NOT NULL,
    "CreatedAt" TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX "IX_RuleConditions_RuleId" ON "RuleConditions" ("RuleId");
```

---

## Template Tables

### MessageTemplates

```sql
CREATE TABLE "MessageTemplates" (
    "Id" UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    "ZaloTemplateId" VARCHAR(50) NOT NULL UNIQUE,
    "Name" VARCHAR(255) NOT NULL,
    "Content" TEXT NOT NULL,
    "Status" INT NOT NULL DEFAULT 0,
    "CreatedAt" TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    "UpdatedAt" TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX "IX_MessageTemplates_ZaloTemplateId" ON "MessageTemplates" ("ZaloTemplateId");
```

---

## Relationships

```
ZaloCustomers (1) ──── (M) Conversations
Conversations (1) ──── (M) Messages
ZaloCustomers (1) ──── (M) CustomerTags
ChatbotRules (1) ──── (M) RuleConditions
ChatbotRules (1) ──── (1) RuleActions
MessageTemplates (1) ──── (M) TemplateParameters
```

---

## Indexes

### Performance-Critical Indexes

| Table | Index | Purpose |
|-------|-------|---------|
| ZaloCustomers | IX_ZaloCustomers_ZaloUserId | Fast lookup by Zalo user ID |
| Conversations | IX_Conversations_Status_StartedAt | List active/recent conversations |
| Messages | IX_Messages_ConversationId_SentAt | Get conversation history |
| Messages | IX_Messages_Content_FullText | Full-text search |
| ChatbotRules | IX_ChatbotRules_IsActive_Priority | Fast rule evaluation |

---

## Migrations

### Initial Migration

```bash
dotnet ef migrations add InitialSchema \
  --project src/MktZaloService.Infrastructure
  
dotnet ef database update \
  --project src/MktZaloService.Infrastructure
```

### Seed Data

```sql
INSERT INTO "ChatbotRules" ("Id", "Name", "Type", "Priority", "IsActive")
VALUES 
  (gen_random_uuid(), 'Greeting Rule', 0, 100, TRUE),
  (gen_random_uuid(), 'Pricing Inquiry', 0, 90, TRUE),
  (gen_random_uuid(), 'Store Hours', 0, 90, TRUE);
```

---

## Data Volume Estimates

| Table | 6 Months | Growth Rate |
|-------|----------|-------------|
| ZaloCustomers | 10,000 | +500/day |
| Conversations | 50,000 | +2,000/day |
| Messages | 750,000 | +30,000/day |

**Total DB Size**: ~2GB (6 months)

---

## Backup Strategy

```bash
# Daily automated backups
- Full backup: Daily at 02:00 UTC
- Retention: 30 days
- Point-in-time recovery: Last 7 days
```

---

**Document Version**: 1.0  
**Last Updated**: 2026-01-18
