# Database Schema Documentation

## 📊 Database Tables & Relationships

### Core E-Commerce Tables

#### `carts` Table
Shopping cart for customers
```sql
CREATE TABLE carts (
  id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  user_id BIGINT UNSIGNED NOT NULL FOREIGN KEY,
  vehicle_id BIGINT UNSIGNED NOT NULL FOREIGN KEY,
  quantity INT DEFAULT 1,
  created_at TIMESTAMP NULL,
  updated_at TIMESTAMP NULL,
  UNIQUE KEY (user_id, vehicle_id),
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN KEY (vehicle_id) REFERENCES vehicles(id) ON DELETE CASCADE
);

Indexes:
  - PRIMARY KEY (id)
  - FOREIGN KEY (user_id)
  - FOREIGN KEY (vehicle_id)
  - UNIQUE (user_id, vehicle_id)
```

#### `orders` Table
Customer orders
```sql
CREATE TABLE orders (
  id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  user_id BIGINT UNSIGNED NOT NULL FOREIGN KEY,
  status VARCHAR(50) DEFAULT 'pending',
  total_price DECIMAL(12,2) NOT NULL,
  discount_amount DECIMAL(10,2) DEFAULT 0,
  shipping_cost DECIMAL(10,2) DEFAULT 0,
  tracking_number VARCHAR(255) NULLABLE,
  shipping_address LONGTEXT NOT NULL,
  notes LONGTEXT NULLABLE,
  order_date TIMESTAMP NOT NULL,
  shipped_at TIMESTAMP NULLABLE,
  delivered_at TIMESTAMP NULLABLE,
  created_at TIMESTAMP NULL,
  updated_at TIMESTAMP NULL,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  INDEX (status),
  INDEX (user_id),
  INDEX (order_date),
  FULLTEXT (shipping_address)
);

Status Values: pending, paid, shipped, delivered, cancelled
Indexes:
  - PRIMARY KEY (id)
  - FOREIGN KEY (user_id)
  - INDEX (status)
  - INDEX (user_id)
  - INDEX (order_date)
```

#### `order_items` Table
Individual items within orders
```sql
CREATE TABLE order_items (
  id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  order_id BIGINT UNSIGNED NOT NULL FOREIGN KEY,
  vehicle_id BIGINT UNSIGNED NOT NULL FOREIGN KEY,
  quantity INT DEFAULT 1,
  price DECIMAL(12,2) NOT NULL,
  discount_applied DECIMAL(12,2) DEFAULT 0,
  created_at TIMESTAMP NULL,
  updated_at TIMESTAMP NULL,
  FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE,
  FOREIGN KEY (vehicle_id) REFERENCES vehicles(id) ON DELETE CASCADE,
  INDEX (order_id),
  INDEX (vehicle_id)
);

Indexes:
  - PRIMARY KEY (id)
  - FOREIGN KEY (order_id)
  - FOREIGN KEY (vehicle_id)
  - INDEX (order_id)
```

#### `payments` Table
Payment tracking
```sql
CREATE TABLE payments (
  id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  order_id BIGINT UNSIGNED NOT NULL FOREIGN KEY,
  payment_method VARCHAR(50) NOT NULL,
  transaction_id VARCHAR(255) NULLABLE,
  amount DECIMAL(12,2) NOT NULL,
  status VARCHAR(50) DEFAULT 'pending',
  payment_data JSON NULLABLE,
  paid_at TIMESTAMP NULLABLE,
  created_at TIMESTAMP NULL,
  updated_at TIMESTAMP NULL,
  FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE,
  INDEX (status),
  INDEX (order_id),
  UNIQUE (transaction_id)
);

Payment Methods: card, bank_transfer, cash_on_delivery
Status Values: pending, completed, failed
Indexes:
  - PRIMARY KEY (id)
  - FOREIGN KEY (order_id)
  - INDEX (status)
  - UNIQUE (transaction_id)
```

### Enhanced Existing Tables

#### `vehicles` Table (New Fields)
```sql
ALTER TABLE vehicles ADD COLUMN (
  vehicle_type VARCHAR(255) NULLABLE,
  stock_country VARCHAR(255) NULLABLE,
  stock_status VARCHAR(50) DEFAULT 'available',
  discount_percentage DECIMAL(5,2) DEFAULT 0,
  discount_amount DECIMAL(10,2) DEFAULT 0,
  is_featured BOOLEAN DEFAULT FALSE,
  is_clearance BOOLEAN DEFAULT FALSE
);

New Indexes:
  - INDEX (vehicle_type)
  - INDEX (stock_country)
  - INDEX (stock_status)
  - INDEX (is_featured)
  - INDEX (is_clearance)
```

#### `users` Table (New Fields for Checkout)
```sql
ALTER TABLE users ADD COLUMN (
  phone VARCHAR(20) NULLABLE,
  address VARCHAR(255) NULLABLE,
  city VARCHAR(255) NULLABLE,
  state VARCHAR(255) NULLABLE,
  zip VARCHAR(10) NULLABLE,
  country VARCHAR(255) NULLABLE
);
```

## 🔗 Entity Relationships

### Relationship Diagram
```
users 1——→∞ carts
  ↓
  ├─→∞ orders
  │
  └─→∞ payments (through orders)

vehicles 1—→∞ carts
   ↓
   ├─→ seller (users)
   │
   ├─→∞ order_items
   │   ↓
   │   └─→∞ orders
   │
   └─→∞ vehicle_images
```

### Model Relationships (Laravel)

```php
// User Model
User::hasMany(Cart::class)
User::hasMany(Order::class)
User::hasMany(Vehicle::class, 'seller_id')

// Cart Model
Cart::belongsTo(User::class)
Cart::belongsTo(Vehicle::class)

// Order Model
Order::belongsTo(User::class)
Order::hasMany(OrderItem::class)
Order::hasMany(Payment::class)

// OrderItem Model
OrderItem::belongsTo(Order::class)
OrderItem::belongsTo(Vehicle::class)

// Payment Model
Payment::belongsTo(Order::class)

// Vehicle Model
Vehicle::hasMany(OrderItem::class)
Vehicle::hasMany(Cart::class)
Vehicle::belongsTo(User::class, 'seller_id')
Vehicle::hasMany(VehicleImage::class)
```

## 📊 Sample Data Structures

### Cart Entry
```json
{
  "id": 1,
  "user_id": 5,
  "vehicle_id": 10,
  "quantity": 2,
  "created_at": "2026-04-08T10:00:00Z",
  "updated_at": "2026-04-08T10:00:00Z"
}
```

### Order Full Snapshot
```json
{
  "id": 1,
  "user_id": 5,
  "status": "paid",
  "total_price": 85000,
  "discount_amount": 10000,
  "shipping_cost": 100,
  "tracking_number": "USPS123456",
  "shipping_address": "123 Main St, Anytown, State 12345",
  "notes": "Leave at front door",
  "order_date": "2026-04-08T10:00:00Z",
  "shipped_at": "2026-04-09T15:30:00Z",
  "delivered_at": null,
  "order_items": [
    {
      "id": 1,
      "vehicle_id": 10,
      "quantity": 1,
      "price": 50000,
      "discount_applied": 5000
    },
    {
      "id": 2,
      "vehicle_id": 11,
      "quantity": 1,
      "price": 40000,
      "discount_applied": 5000
    }
  ],
  "payments": [
    {
      "id": 1,
      "payment_method": "card",
      "amount": 85000,
      "status": "completed",
      "transaction_id": "ch_1AB2CD3EF4G5H6I7J8K9",
      "paid_at": "2026-04-08T10:15:00Z"
    }
  ]
}
```

## 🔍 Key Queries

### Cart Management
```sql
-- Get user's cart with vehicle details
SELECT c.*, v.make, v.model, v.price, v.discount_percentage
FROM carts c
JOIN vehicles v ON c.vehicle_id = v.id
WHERE c.user_id = ?;

-- Check if item in cart
SELECT * FROM carts WHERE user_id = ? AND vehicle_id = ?;

-- Clear user cart
DELETE FROM carts WHERE user_id = ?;
```

### Order Management
```sql
-- Get customer orders with items
SELECT o.*, oi.*, v.make, v.model
FROM orders o
LEFT JOIN order_items oi ON o.id = oi.order_id
LEFT JOIN vehicles v ON oi.vehicle_id = v.id
WHERE o.user_id = ?
ORDER BY o.order_date DESC;

-- Get seller's orders
SELECT DISTINCT o.* FROM orders o
JOIN order_items oi ON o.id = oi.order_id
JOIN vehicles v ON oi.vehicle_id = v.id
WHERE v.seller_id = ?
ORDER BY o.order_date DESC;

-- Order statistics
SELECT 
  COUNT(*) as total_orders,
  SUM(CASE WHEN status = 'pending' THEN 1 ELSE 0 END) as pending,
  SUM(CASE WHEN status = 'paid' THEN 1 ELSE 0 END) as paid,
  SUM(CASE WHEN status = 'shipped' THEN 1 ELSE 0 END) as shipped,
  SUM(CASE WHEN status = 'delivered' THEN 1 ELSE 0 END) as delivered,
  SUM(total_price) as total_revenue
FROM orders
WHERE user_id = ? OR status != 'cancelled';
```

## 🛡️ Constraints & Validations

### Primary Key Constraints
- Each table has AUTO_INCREMENT primary key
- Ensures unique row identification

### Foreign Key Constraints
- `carts.user_id` → `users.id` (CASCADE DELETE)
- `carts.vehicle_id` → `vehicles.id` (CASCADE DELETE)
- `orders.user_id` → `users.id` (CASCADE DELETE)
- `order_items.order_id` → `orders.id` (CASCADE DELETE)
- `order_items.vehicle_id` → `vehicles.id` (CASCADE DELETE)
- `payments.order_id` → `orders.id` (CASCADE DELETE)

### Unique Constraints
- `carts` (user_id, vehicle_id) - One entry per user per vehicle
- `payments` (transaction_id) - Prevent duplicate transactions

### Check Constraints (via application)
- `orders.status` - Only valid statuses allowed
- `payments.status` - Only valid statuses allowed
- `payments.payment_method` - Only valid methods allowed
- `quantity` - Must be > 0
- `prices` - Must be >= 0

## 📈 Performance Indexes

### Recommended Indexes
```sql
-- Carts
CREATE INDEX idx_carts_user ON carts(user_id);
CREATE INDEX idx_carts_vehicle ON carts(vehicle_id);

-- Orders
CREATE INDEX idx_orders_user ON orders(user_id);
CREATE INDEX idx_orders_status ON orders(status);
CREATE INDEX idx_orders_date ON orders(order_date);

-- Order Items
CREATE INDEX idx_order_items_order ON order_items(order_id);
CREATE INDEX idx_order_items_vehicle ON order_items(vehicle_id);

-- Payments
CREATE INDEX idx_payments_order ON payments(order_id);
CREATE INDEX idx_payments_status ON payments(status);
CREATE INDEX idx_payments_method ON payments(payment_method);

-- Vehicles
CREATE INDEX idx_vehicles_seller ON vehicles(seller_id);
CREATE INDEX idx_vehicles_type ON vehicles(vehicle_type);
CREATE INDEX idx_vehicles_country ON vehicles(stock_country);
```

## 🔐 Data Integrity Rules

### Business Rules
1. **Cart Uniqueness** - One cart entry per user per vehicle
2. **Order Immutability** - Order items cannot be modified
3. **Payment Uniqueness** - One payment per transaction ID
4. **Status Progression** - Orders follow status pipeline
5. **Discount Application** - Automatically applied at checkout
6. **Price Lock** - Item prices locked at order creation

### Data Consistency
- Orphaned orders deleted with user deletion
- Orphaned cart items deleted with vehicle deletion
- Payment cascades with order deletion
- All prices stored as DECIMAL(12,2) for precision

## 📋 Migration Order

Migrations are applied in this order:
1. Users table (existing)
2. Vehicles table (existing)
3. Orders table
4. Payments table
5. Carts table
6. OrderItems table
7. Enhanced vehicle fields (discount, type, etc.)
8. Enhanced user fields (address, phone, etc.)

## 🧹 Cleanup & Maintenance

### Archive Old Orders
```sql
-- Archive orders older than 1 year
INSERT INTO orders_archive SELECT * FROM orders WHERE order_date < DATE_SUB(NOW(), INTERVAL 1 YEAR);
DELETE FROM orders WHERE order_date < DATE_SUB(NOW(), INTERVAL 1 YEAR);
```

### Cleanup Abandoned Carts
```sql
-- Delete carts not updated in 30 days
DELETE FROM carts WHERE updated_at < DATE_SUB(NOW(), INTERVAL 30 DAY);
```

### Optimize Tables
```sql
OPTIMIZE TABLE carts, orders, order_items, payments;
ANALYZE TABLE carts, orders, order_items, payments;
```