# Entity Relationship Diagram (ERD) - Platform MUAMAR

Dokumen ini mendefinisikan skema basis data relasional untuk sistem marketplace MUAMAR, mengimplementasikan foreign keys, engine InnoDB, dan indeks performa.

```mermaid
erDiagram
    USERS ||--o| PROVIDERS : "owns/manages"
    USERS ||--o{ ORDERS : "places"
    USERS ||--o{ CART_ITEMS : "adds"
    USERS ||--o{ CONVERSATIONS : "customer_in"
    USERS ||--o{ REVIEWS : "writes"
    USERS ||--o{ ADMIN_AUDIT_LOGS : "performs"

    PROVIDERS ||--o{ PRODUCTS : "lists"
    PROVIDERS ||--o{ VENDOR_ORDERS : "fulfills"
    PROVIDERS ||--o{ PROVIDER_AVAILABILITIES : "defines_weekly_hours"
    PROVIDERS ||--o{ PROVIDER_SCHEDULE_OVERRIDES : "defines_exceptions"
    PROVIDERS ||--o{ CONVERSATIONS : "provider_in"
    PROVIDERS ||--o{ REVIEWS : "receives"

    CATEGORIES ||--o{ CATEGORIES : "parent_of"
    CATEGORIES ||--o{ PRODUCTS : "categorizes"

    PRODUCTS ||--o{ PRODUCT_VARIANTS : "has_variants"
    PRODUCTS ||--o| SERVICE_PROFILES : "has_service_rules"
    PRODUCTS ||--o| RENTAL_PROFILES : "has_rental_rules"
    PRODUCTS ||--o{ ORDER_ITEMS : "ordered_in"
    PRODUCTS ||--o{ SERVICE_BOOKINGS : "booked_in"
    PRODUCTS ||--o{ RENTAL_BOOKINGS : "rented_in"
    PRODUCTS ||--o{ REVIEWS : "reviewed_in"

    ORDERS ||--o{ VENDOR_ORDERS : "splits_into"
    ORDERS ||--o{ PAYMENTS : "paid_by"
    ORDERS ||--o{ PAYMENT_AUDIT_LOGS : "logs_events"

    VENDOR_ORDERS ||--o{ ORDER_ITEMS : "contains"
    VENDOR_ORDERS ||--o{ SHIPPING_HISTORIES : "tracks_status"

    ORDER_ITEMS ||--o| SERVICE_BOOKINGS : "schedules_service"
    ORDER_ITEMS ||--o| RENTAL_BOOKINGS : "reserves_rental"
    ORDER_ITEMS ||--o| REVIEWS : "verified_purchase_for"

    CONVERSATIONS ||--o{ MESSAGES : "contains_thread"

    USERS {
        bigint id PK
        string name
        string email UK
        timestamp email_verified_at
        string password
        string phone
        enum role "customer, provider, admin"
        string avatar_url
        boolean is_active
        timestamps created_at_updated_at
    }

    PROVIDERS {
        bigint id PK
        bigint user_id FK
        string business_name
        string slug UK
        text description
        string logo_url
        string banner_url
        string address
        string city
        string province
        string postal_code
        decimal latitude
        decimal longitude
        boolean is_location_public
        string phone
        boolean is_verified
        enum status "pending, approved, suspended"
        boolean qris_active
        boolean cod_active
        timestamps created_at_updated_at
    }

    CATEGORIES {
        bigint id PK
        bigint parent_id FK "nullable"
        string name
        string slug UK
        enum type "product, service, rental, all"
        string icon
        text description
        int sort_order
        boolean is_active
        timestamps created_at_updated_at
    }

    PRODUCTS {
        bigint id PK
        bigint provider_id FK
        bigint category_id FK
        string name
        string slug UK
        text description
        enum type "physical, service, rental"
        bigint base_price "in IDR"
        json images
        boolean is_active
        timestamps created_at_updated_at
    }

    PRODUCT_VARIANTS {
        bigint id PK
        bigint product_id FK
        string sku UK
        string variant_name
        bigint price "in IDR"
        int stock
        boolean is_active
        timestamps created_at_updated_at
    }

    SERVICE_PROFILES {
        bigint id PK
        bigint product_id FK "unique"
        int duration_minutes
        int buffer_minutes
        int capacity_per_slot
        string artist_name
        string service_area
        timestamps created_at_updated_at
    }

    RENTAL_PROFILES {
        bigint id PK
        bigint product_id FK "unique"
        int total_units
        bigint deposit_amount "in IDR"
        int min_rental_days
        int buffer_hours
        string unit_type
        timestamps created_at_updated_at
    }

    PROVIDER_AVAILABILITIES {
        bigint id PK
        bigint provider_id FK
        tinyint day_of_week "0=Sunday to 6=Saturday"
        time start_time
        time end_time
        boolean is_available
        timestamps created_at_updated_at
    }

    PROVIDER_SCHEDULE_OVERRIDES {
        bigint id PK
        bigint provider_id FK
        date override_date
        boolean is_off
        string reason
        timestamps created_at_updated_at
    }

    ORDERS {
        bigint id PK
        string order_number UK
        bigint user_id FK
        bigint total_amount "in IDR"
        bigint total_deposit "in IDR"
        enum status "pending_payment, processing, completed, cancelled"
        enum payment_status "unpaid, paid, refunded"
        enum payment_method "qris, cod"
        json shipping_address_snapshot
        text notes
        timestamp paid_at
        timestamp cancelled_at
        timestamps created_at_updated_at
    }

    VENDOR_ORDERS {
        bigint id PK
        string vendor_order_number UK
        bigint order_id FK
        bigint provider_id FK
        bigint subtotal "in IDR"
        bigint deposit_subtotal "in IDR"
        bigint shipping_cost "in IDR"
        bigint total_amount "in IDR"
        enum status "pending_payment, processing, ready_for_shipment, shipped, delivered, completed, cancelled"
        enum payment_status "unpaid, paid, refunded"
        string shipping_carrier
        string tracking_number
        timestamp shipped_at
        timestamp delivered_at
        timestamps created_at_updated_at
    }

    ORDER_ITEMS {
        bigint id PK
        bigint vendor_order_id FK
        bigint product_id FK
        bigint product_variant_id FK "nullable"
        enum item_type "physical, service, rental"
        string name_snapshot
        string variant_name_snapshot
        bigint price_snapshot "in IDR"
        bigint deposit_snapshot "in IDR"
        int quantity
        bigint subtotal "in IDR"
        json metadata
        timestamps created_at_updated_at
    }

    SERVICE_BOOKINGS {
        bigint id PK
        bigint vendor_order_id FK "nullable"
        bigint order_item_id FK "nullable"
        bigint provider_id FK
        bigint customer_id FK
        bigint product_id FK
        timestamp start_time "UTC"
        timestamp end_time "UTC"
        timestamp buffer_end_time "UTC"
        enum status "hold, confirmed, in_progress, completed, cancelled, expired"
        timestamp hold_expires_at "UTC"
        text notes
        timestamps created_at_updated_at
    }

    RENTAL_BOOKINGS {
        bigint id PK
        bigint vendor_order_id FK "nullable"
        bigint order_item_id FK "nullable"
        bigint provider_id FK
        bigint customer_id FK
        bigint product_id FK
        timestamp start_date "UTC"
        timestamp end_date "UTC"
        timestamp buffer_end_date "UTC"
        int quantity_rented
        bigint deposit_amount "in IDR"
        enum status "hold, confirmed, picked_up, returned, cancelled, expired"
        timestamp hold_expires_at "UTC"
        timestamp returned_at
        timestamp deposit_returned_at
        timestamps created_at_updated_at
    }

    PAYMENTS {
        bigint id PK
        bigint order_id FK
        enum payment_method "qris, cod"
        string provider_reference UK
        text qris_payload
        string qris_image_url
        bigint amount "in IDR"
        enum status "pending, paid, expired, failed"
        string signature_hash
        timestamp paid_at
        json payload_snapshot
        timestamps created_at_updated_at
    }

    PAYMENT_AUDIT_LOGS {
        bigint id PK
        bigint payment_id FK "nullable"
        bigint order_id FK "nullable"
        string event_type
        json payload
        string ip_address
        timestamp created_at
    }

    CART_ITEMS {
        bigint id PK
        bigint user_id FK
        bigint product_id FK
        bigint product_variant_id FK "nullable"
        enum item_type "physical, service, rental"
        int quantity
        timestamp booking_start "UTC nullable"
        timestamp booking_end "UTC nullable"
        json metadata
        timestamps created_at_updated_at
    }

    CONVERSATIONS {
        bigint id PK
        bigint customer_id FK
        bigint provider_id FK
        bigint product_id FK "nullable"
        timestamp last_message_at
        timestamps created_at_updated_at
    }

    MESSAGES {
        bigint id PK
        bigint conversation_id FK
        bigint sender_id FK
        text message
        boolean is_read
        timestamps created_at_updated_at
    }

    REVIEWS {
        bigint id PK
        bigint user_id FK
        bigint provider_id FK
        bigint product_id FK
        bigint order_item_id FK "unique"
        tinyint rating "1-5"
        text comment
        text provider_reply
        boolean is_visible
        timestamps created_at_updated_at
    }

    SHIPPING_HISTORIES {
        bigint id PK
        bigint vendor_order_id FK
        string status
        text notes
        bigint updated_by FK
        timestamp created_at
    }

    ADMIN_AUDIT_LOGS {
        bigint id PK
        bigint user_id FK
        string action
        string entity_type
        bigint entity_id
        json old_values
        json new_values
        string ip_address
        timestamp created_at
    }
```