# Database Schema

## Overview

The database is designed around the domain structure with normalized tables and proper relationships.

## Domain Tables

### Art Domain

#### `artists`
Stores information about artwork creators.

```sql
CREATE TABLE artists (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    bio LONGTEXT,
    photo VARCHAR(255),          -- Image path
    website VARCHAR(255),
    social_links JSON,            -- {instagram, facebook, twitter, etc}
    created_at TIMESTAMP,
    updated_at TIMESTAMP
);
```

#### `categories`
Types of artworks.

```sql
CREATE TABLE categories (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    slug VARCHAR(255) NOT NULL UNIQUE,
    created_at TIMESTAMP,
    updated_at TIMESTAMP
);
```

#### `techniques`
Artistic techniques used in artworks.

```sql
CREATE TABLE techniques (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    slug VARCHAR(255) NOT NULL UNIQUE,
    created_at TIMESTAMP,
    updated_at TIMESTAMP
);
```

#### `artworks`
Individual artworks for sale.

```sql
CREATE TABLE artworks (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    artist_id BIGINT NOT NULL,
    category_id BIGINT,
    title VARCHAR(255) NOT NULL,
    description LONGTEXT,
    price DECIMAL(10, 2) NOT NULL,
    dimensions VARCHAR(255),     -- e.g., "50x70 cm"
    year INT,
    status VARCHAR(50),           -- available, reserved, sold
    images JSON,                  -- Array of image paths
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    FOREIGN KEY (artist_id) REFERENCES artists(id),
    FOREIGN KEY (category_id) REFERENCES categories(id)
);

CREATE INDEX idx_artworks_artist ON artworks(artist_id);
CREATE INDEX idx_artworks_category ON artworks(category_id);
CREATE INDEX idx_artworks_status ON artworks(status);
```

#### `artwork_technique` (Pivot)
Many-to-many relationship between artworks and techniques.

```sql
CREATE TABLE artwork_technique (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    artwork_id BIGINT NOT NULL,
    technique_id BIGINT NOT NULL,
    created_at TIMESTAMP,
    FOREIGN KEY (artwork_id) REFERENCES artworks(id) ON DELETE CASCADE,
    FOREIGN KEY (technique_id) REFERENCES techniques(id) ON DELETE CASCADE,
    UNIQUE KEY unique_artwork_technique (artwork_id, technique_id)
);
```

#### `collections`
Curated groupings of artworks.

```sql
CREATE TABLE collections (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    description LONGTEXT,
    cover_image VARCHAR(255),
    created_at TIMESTAMP,
    updated_at TIMESTAMP
);
```

#### `collection_artwork` (Pivot)
Many-to-many relationship between collections and artworks.

```sql
CREATE TABLE collection_artwork (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    collection_id BIGINT NOT NULL,
    artwork_id BIGINT NOT NULL,
    created_at TIMESTAMP,
    FOREIGN KEY (collection_id) REFERENCES collections(id) ON DELETE CASCADE,
    FOREIGN KEY (artwork_id) REFERENCES artworks(id) ON DELETE CASCADE,
    UNIQUE KEY unique_collection_artwork (collection_id, artwork_id)
);
```

---

### Sales Domain

#### `carts`
Shopping carts for users.

```sql
CREATE TABLE carts (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    user_id BIGINT,               -- NULL for guest carts
    session_id VARCHAR(255),      -- For guest users
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

CREATE INDEX idx_carts_user ON carts(user_id);
CREATE INDEX idx_carts_session ON carts(session_id);
```

#### `cart_items`
Items in a shopping cart.

```sql
CREATE TABLE cart_items (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    cart_id BIGINT NOT NULL,
    artwork_id BIGINT NOT NULL,
    quantity INT DEFAULT 1,
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    FOREIGN KEY (cart_id) REFERENCES carts(id) ON DELETE CASCADE,
    FOREIGN KEY (artwork_id) REFERENCES artworks(id),
    UNIQUE KEY unique_cart_artwork (cart_id, artwork_id)
);

CREATE INDEX idx_cart_items_artwork ON cart_items(artwork_id);
```

#### `orders`
Completed orders.

```sql
CREATE TABLE orders (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    user_id BIGINT NOT NULL,
    total_amount DECIMAL(10, 2) NOT NULL,
    status VARCHAR(50),           -- pending, paid, preparing, shipped, delivered, cancelled
    shipping_address LONGTEXT,    -- JSON with address details
    notes LONGTEXT,
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

CREATE INDEX idx_orders_user ON orders(user_id);
CREATE INDEX idx_orders_status ON orders(status);
```

#### `order_items`
Items in an order (immutable snapshot of artworks at purchase time).

```sql
CREATE TABLE order_items (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    order_id BIGINT NOT NULL,
    artwork_id BIGINT,            -- Can be NULL if artwork is deleted
    title VARCHAR(255) NOT NULL,  -- Snapshot of artwork title
    price DECIMAL(10, 2) NOT NULL, -- Price at time of purchase
    quantity INT DEFAULT 1,
    created_at TIMESTAMP,
    FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE,
    FOREIGN KEY (artwork_id) REFERENCES artworks(id) ON DELETE SET NULL
);

CREATE INDEX idx_order_items_order ON order_items(order_id);
```

---

### Payments Domain

#### `payments`
Payment records for orders.

```sql
CREATE TABLE payments (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    order_id BIGINT NOT NULL UNIQUE,
    provider VARCHAR(50),         -- mercadopago, paypal
    provider_payment_id VARCHAR(255),
    amount DECIMAL(10, 2) NOT NULL,
    currency VARCHAR(3) DEFAULT 'MXN',
    status VARCHAR(50),           -- pending, authorized, captured, failed, refunded
    idempotency_key CHAR(36) UNIQUE, -- UUID for duplicate prevention
    payload JSON,                 -- Provider response data
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE
);

CREATE INDEX idx_payments_order ON payments(order_id);
CREATE INDEX idx_payments_provider ON payments(provider);
CREATE INDEX idx_payments_status ON payments(status);
CREATE INDEX idx_payments_idempotency ON payments(idempotency_key);
```

---

### Shipping Domain

#### `shipments`
Shipment tracking information.

```sql
CREATE TABLE shipments (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    order_id BIGINT NOT NULL UNIQUE,
    carrier VARCHAR(255),         -- e.g., "DHL", "FedEx", "DHL Express"
    tracking_number VARCHAR(255),
    shipped_at TIMESTAMP,
    delivered_at TIMESTAMP,
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE
);

CREATE INDEX idx_shipments_order ON shipments(order_id);
```

---

### Users Domain

#### `users`
Application users.

```sql
CREATE TABLE users (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    email VARCHAR(255) NOT NULL UNIQUE,
    email_verified_at TIMESTAMP NULL,
    password VARCHAR(255) NOT NULL,
    remember_token VARCHAR(100),
    created_at TIMESTAMP,
    updated_at TIMESTAMP
);

CREATE INDEX idx_users_email ON users(email);
```

#### `personal_access_tokens`
Laravel Sanctum tokens for API authentication.

```sql
CREATE TABLE personal_access_tokens (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    tokenable_type VARCHAR(255) NOT NULL,
    tokenable_id BIGINT NOT NULL,
    name VARCHAR(255) NOT NULL,
    token VARCHAR(64) NOT NULL UNIQUE,
    abilities TEXT,
    last_used_at TIMESTAMP NULL,
    expires_at TIMESTAMP NULL,
    created_at TIMESTAMP,
    updated_at TIMESTAMP
);

CREATE INDEX idx_tokens_tokenable ON personal_access_tokens(tokenable_type, tokenable_id);
```

---

### Settings Domain

#### `site_settings`
Key-value configuration storage.

```sql
CREATE TABLE site_settings (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    key VARCHAR(255) NOT NULL UNIQUE,
    value LONGTEXT,
    group VARCHAR(255),
    created_at TIMESTAMP,
    updated_at TIMESTAMP
);

CREATE INDEX idx_settings_key ON site_settings(key);
CREATE INDEX idx_settings_group ON site_settings(group);
```

**Common Keys:**
- `site_name` - Application name
- `site_logo` - Logo image path
- `site_favicon` - Favicon image path
- `contact_email` - Support email
- `mercadopago_public_key` - MercadoPago public key
- `mercadopago_access_token` - MercadoPago access token
- `paypal_client_id` - PayPal client ID
- `paypal_client_secret` - PayPal client secret
- `primary_color` - Brand primary color
- `secondary_color` - Brand secondary color

---

## Relationships Diagram

```
Artist (1) ──────────────── (M) Artwork
                                    │
                                    ├─── (M) Category
                                    │
                                    ├─── (M) Technique (via artwork_technique)
                                    │
                                    └─── (M) Collection (via collection_artwork)

User (1) ──── (M) Cart ──── (M) CartItem ──── (1) Artwork
User (1) ──── (M) Order ──────────────────────────│

Order (1) ──── (M) OrderItem ──────────────────────│

Order (1) ──── (1) Payment
Order (1) ──── (1) Shipment
```

---

## Indexes

All foreign keys are automatically indexed. Additional indexes for performance:

- `artworks`: status, artist_id, category_id
- `carts`: user_id, session_id
- `orders`: user_id, status
- `cart_items`: artwork_id
- `order_items`: order_id
- `payments`: order_id, provider, status, idempotency_key
- `shipments`: order_id
- `users`: email
- `site_settings`: key, group

---

## Data Integrity Rules

- Cascading deletes for cart_items when cart is deleted
- Cascading deletes for order_items when order is deleted
- Set NULL for deleted artworks in order_items (preserves order history)
- Unique constraints on keys for idempotency
- Foreign key constraints to prevent orphaned records