# Database Schema

Generated: 2025-11-15

Note: A live SQL dump could not be generated because the database connection failed (auth_gssapi_client). This file summarizes the schema from available migrations. Once the DB connection is fixed, you can generate a full SQL dump with:

```
php artisan migrate
php artisan schema:dump
copy database/schema/mysql-schema.dump ./DATABASE_SCHEMA.sql
```

## Tables (from migrations)
- users
- password_reset_tokens
- personal_access_tokens
- teams
- team_user
- team_invitations
- sessions
- failed_jobs
- webpanel_pages
- companies
- persons
- addresses
- notes
- images
- links
- invoices
- invoice_units
- incoming_invoices
- categories
- comments
- tags
- product_and_services
- colors
- movements
- recurring_movements
- deals
- tasks
- offers
- exchange_rates
- warehouses
- bank_accounts
- bank_transactions
- manufacturings
- productions
- production_plans
- waybills
- activities
- user_accesses
- stock_transactions
- stock_allocations
- employees
- departments
- employee_expenses
- costs
- steps
- rooms
- beds
- reservations
- job_applicants
- hirings
- budget_movements
- labels
- brands
- contacts
- blocked_dates
- logs
- integrations
- product_mappings
- prayer_times
- release_notes
- ai_chat_logs
- email_logs

Migrations that alter existing tables (fields added/changed):
- add_efatura_response_fields_to_invoices_table (invoices)

---

## Detailed Definitions

### ai_chat_logs
- id: big integer, primary key
- user_id: foreign key -> users.id, nullable, on delete set null
- current_team_id: foreign key -> teams.id, nullable, on delete set null
- tool_name: string, nullable, indexed
- session_id: string, indexed
- prompt: text
- response: longText, nullable
- model: string, default 'claude-sonnet-4.5'
- prompt_tokens: integer, nullable
- response_tokens: integer, nullable
- total_tokens: integer, nullable
- cost: decimal(10,6), nullable
- response_time_ms: integer, nullable
- status: string, default 'success'
- error_message: text, nullable
- triggered_record_ids: json, nullable
- triggered_count: integer, default 0
- metadata: json, nullable
- created_at / updated_at: timestamps
- Indexes: (user_id, created_at), triggered_count

### email_logs
- id: big integer, primary key
- user_id: foreign key -> users.id, nullable, on delete set null
- current_team_id: foreign key -> teams.id, nullable, on delete set null
- company_id: foreign key -> companies.id, nullable, on delete set null
- person_id: foreign key -> persons.id, nullable, on delete set null
- type: string, default 'incoming', indexed (incoming|outgoing)
- mailer: string, nullable
- from_email: string, indexed
- from_name: string, nullable
- to_emails: text (JSON array as string)
- cc_emails: text, nullable (JSON array as string)
- bcc_emails: text, nullable (JSON array as string)
- subject: string
- body_html: longText, nullable
- body_text: longText, nullable
- attachments: json, nullable
- status: string, default 'sent' (sent|failed|bounced|queued)
- error_message: text, nullable
- message_id: string, nullable, indexed
- sent_at: timestamp, nullable
- opened_at: timestamp, nullable
- clicked_at: timestamp, nullable
- bounced_at: timestamp, nullable
- open_count: integer, default 0
- click_count: integer, default 0
- metadata: json, nullable
- created_at / updated_at: timestamps
- Indexes: (user_id, created_at), (current_team_id, created_at), (company_id, created_at), (person_id, created_at), (status, created_at), sent_at

---

## How to Generate a Fresh SQL Dump (after DB fix)
1. Configure .env with a working DB connection
2. Run:
```
php artisan migrate
php artisan schema:dump
copy database/schema/mysql-schema.dump ./DATABASE_SCHEMA.sql
```

This will create `DATABASE_SCHEMA.sql` at the repo root with the exact SQL of the current database.