# AIPT Database Schema Documentation
## Complete Database Reference

---

## Table of Contents

1. [Overview](#overview)
2. [Core Tables](#core-tables)
3. [Module Tables](#module-tables)
4. [Relationships](#relationships)
5. [Indexes](#indexes)
6. [Constraints](#constraints)
7. [Triggers](#triggers)
8. [Views](#views)
9. [Stored Procedures](#stored-procedures)
10. [Data Dictionary](#data-dictionary)

---

## Overview

The AIPT database is designed to support a comprehensive intellectual property management system with multiple modules. The schema follows a modular approach with core tables shared across modules and module-specific tables for specialized functionality.

### Database Information
- **Database Engine**: MySQL 5.7+ / MariaDB 10.3+
- **Character Set**: utf8mb4
- **Collation**: utf8mb4_unicode_ci
- **Storage Engine**: InnoDB

### Schema Statistics
- **Total Tables**: 50+
- **Core Tables**: 15
- **Module Tables**: 35+
- **Total Indexes**: 100+
- **Foreign Key Constraints**: 80+

---

## Core Tables

### Users Table
**Purpose**: Stores user accounts and authentication information

```sql
CREATE TABLE users (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    username VARCHAR(255) UNIQUE NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    email_verified_at TIMESTAMP NULL,
    password VARCHAR(255) NOT NULL,
    gender ENUM('male', 'female', 'other') NULL,
    department_id BIGINT UNSIGNED NULL,
    manger_id BIGINT UNSIGNED NULL,
    role_id BIGINT UNSIGNED NULL,
    img_path VARCHAR(255) NULL,
    status BOOLEAN DEFAULT TRUE,
    login_status BOOLEAN DEFAULT FALSE,
    remember_token VARCHAR(100) NULL,
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL,
    deleted_at TIMESTAMP NULL,
    
    INDEX idx_department_id (department_id),
    INDEX idx_manger_id (manger_id),
    INDEX idx_role_id (role_id),
    INDEX idx_status (status),
    INDEX idx_deleted_at (deleted_at),
    
    FOREIGN KEY (department_id) REFERENCES departments(id) ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY (manger_id) REFERENCES users(id) ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY (role_id) REFERENCES roles(id) ON DELETE CASCADE ON UPDATE CASCADE
);
```

### Departments Table
**Purpose**: Organizational structure and department management

```sql
CREATE TABLE departments (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    department_name VARCHAR(255) NOT NULL,
    description TEXT NULL,
    manager_id BIGINT UNSIGNED NULL,
    parent_id BIGINT UNSIGNED NULL,
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL,
    deleted_at TIMESTAMP NULL,
    
    INDEX idx_manager_id (manager_id),
    INDEX idx_parent_id (parent_id),
    INDEX idx_deleted_at (deleted_at),
    
    FOREIGN KEY (manager_id) REFERENCES users(id) ON DELETE SET NULL ON UPDATE CASCADE,
    FOREIGN KEY (parent_id) REFERENCES departments(id) ON DELETE SET NULL ON UPDATE CASCADE
);
```

### Roles Table
**Purpose**: User roles and permission management

```sql
CREATE TABLE roles (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    guard_name VARCHAR(255) NOT NULL,
    description TEXT NULL,
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL,
    
    UNIQUE KEY uk_roles_name_guard (name, guard_name)
);
```

### Permissions Table
**Purpose**: System permissions and access control

```sql
CREATE TABLE permissions (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    guard_name VARCHAR(255) NOT NULL,
    module VARCHAR(100) NULL,
    description TEXT NULL,
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL,
    
    UNIQUE KEY uk_permissions_name_guard (name, guard_name),
    INDEX idx_module (module)
);
```

### Countries Table
**Purpose**: Country reference data for international operations

```sql
CREATE TABLE countries (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    country_name VARCHAR(255) NOT NULL,
    country_code VARCHAR(3) NOT NULL,
    currency_code VARCHAR(3) NULL,
    currency_symbol VARCHAR(10) NULL,
    timezone VARCHAR(50) NULL,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL,
    
    UNIQUE KEY uk_country_code (country_code),
    INDEX idx_country_name (country_name),
    INDEX idx_is_active (is_active)
);
```

---

## Module Tables

### Trademark Management

#### Trade Mark Tickets Table
**Purpose**: Core trademark records and applications

```sql
CREATE TABLE trade_mark_tickets (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    TKT_Type VARCHAR(255) NULL,
    client_id BIGINT UNSIGNED NULL,
    added_by BIGINT UNSIGNED NULL,
    associate_id BIGINT UNSIGNED NULL,
    methode_id BIGINT UNSIGNED NULL,
    assigned_to BIGINT UNSIGNED NULL,
    procedure_id BIGINT UNSIGNED NULL,
    country_id BIGINT UNSIGNED NULL,
    applicant_id BIGINT UNSIGNED NULL,
    clientref VARCHAR(255) DEFAULT 'n/a',
    aiptref TEXT NULL,
    filing_date DATE NULL,
    filing_no VARCHAR(255) NULL,
    class VARCHAR(255) NULL,
    register_no VARCHAR(255) NULL,
    trademark_name VARCHAR(255) NOT NULL,
    trademark_brief TEXT NULL,
    img_paths VARCHAR(255) NULL,
    imageexport VARCHAR(255) NULL,
    folder_path VARCHAR(255) NULL,
    related_TKT BIGINT UNSIGNED NULL,
    status ENUM('pending', 'approved', 'rejected', 'in_progress') DEFAULT 'pending',
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL,
    deleted_at TIMESTAMP NULL,
    
    INDEX idx_client_id (client_id),
    INDEX idx_added_by (added_by),
    INDEX idx_associate_id (associate_id),
    INDEX idx_methode_id (methode_id),
    INDEX idx_assigned_to (assigned_to),
    INDEX idx_procedure_id (procedure_id),
    INDEX idx_country_id (country_id),
    INDEX idx_applicant_id (applicant_id),
    INDEX idx_related_TKT (related_TKT),
    INDEX idx_status (status),
    INDEX idx_filing_date (filing_date),
    INDEX idx_trademark_name (trademark_name),
    INDEX idx_deleted_at (deleted_at),
    
    FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE CASCADE,
    FOREIGN KEY (added_by) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (associate_id) REFERENCES associates(id) ON DELETE CASCADE,
    FOREIGN KEY (methode_id) REFERENCES methodes(id) ON DELETE CASCADE,
    FOREIGN KEY (assigned_to) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (procedure_id) REFERENCES procedures(id) ON DELETE CASCADE,
    FOREIGN KEY (country_id) REFERENCES countries(id) ON DELETE CASCADE,
    FOREIGN KEY (applicant_id) REFERENCES applicants(id) ON DELETE CASCADE,
    FOREIGN KEY (related_TKT) REFERENCES trade_mark_tickets(id) ON DELETE CASCADE ON UPDATE CASCADE
);
```

#### Trademark Ticket Histories Table
**Purpose**: Audit trail and status change history

```sql
CREATE TABLE trademark_ticket_histories (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    type VARCHAR(255) NOT NULL,
    comment TEXT NULL,
    status VARCHAR(255) NULL,
    file_path VARCHAR(255) NULL,
    related_TKT BIGINT UNSIGNED NOT NULL,
    added_by BIGINT UNSIGNED NOT NULL,
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL,
    
    INDEX idx_related_TKT (related_TKT),
    INDEX idx_added_by (added_by),
    INDEX idx_type (type),
    INDEX idx_created_at (created_at),
    
    FOREIGN KEY (related_TKT) REFERENCES trade_mark_tickets(id) ON DELETE CASCADE,
    FOREIGN KEY (added_by) REFERENCES users(id) ON DELETE CASCADE
);
```

#### Trademark Change Logs Table
**Purpose**: Track changes to trademark records for compliance

```sql
CREATE TABLE trademark_change_logs (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    trademark_id BIGINT UNSIGNED NOT NULL,
    changed_by BIGINT UNSIGNED NOT NULL,
    change_reason TEXT NULL,
    old_methode_id BIGINT UNSIGNED NULL,
    new_methode_id BIGINT UNSIGNED NULL,
    old_procedure_id BIGINT UNSIGNED NULL,
    new_procedure_id BIGINT UNSIGNED NULL,
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL,
    
    INDEX idx_trademark_id (trademark_id),
    INDEX idx_changed_by (changed_by),
    INDEX idx_created_at (created_at),
    
    FOREIGN KEY (trademark_id) REFERENCES trade_mark_tickets(id) ON DELETE CASCADE,
    FOREIGN KEY (changed_by) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (old_methode_id) REFERENCES methodes(id) ON DELETE SET NULL,
    FOREIGN KEY (new_methode_id) REFERENCES methodes(id) ON DELETE SET NULL,
    FOREIGN KEY (old_procedure_id) REFERENCES procedures(id) ON DELETE SET NULL,
    FOREIGN KEY (new_procedure_id) REFERENCES procedures(id) ON DELETE SET NULL
);
```

### Accounting Module

#### Accounting Transactions Table
**Purpose**: Financial transactions and billing records

```sql
CREATE TABLE acc_transactions (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    reference_no VARCHAR(255) NULL,
    stages VARCHAR(255) NULL,
    role_ranks JSON NULL,
    department_id BIGINT UNSIGNED NULL,
    country VARCHAR(255) NULL,
    deadline DATE NULL,
    bill_deadline DATE NULL,
    type VARCHAR(255) NULL,
    id_type BIGINT UNSIGNED NULL,
    procedure VARCHAR(255) NULL,
    value DECIMAL(15,2) NULL,
    value_usd DECIMAL(15,2) NULL,
    description TEXT NULL,
    exchangeRate DECIMAL(10,4) NULL,
    currency VARCHAR(255) NULL,
    currency_code VARCHAR(10) NULL,
    related_table VARCHAR(255) NULL,
    related_id BIGINT UNSIGNED NULL,
    has_bills BOOLEAN DEFAULT FALSE,
    created_by BIGINT UNSIGNED NULL,
    aiptref VARCHAR(255) NULL,
    InvoiceNo VARCHAR(255) NULL,
    status ENUM('pending', 'approved', 'rejected', 'paid') DEFAULT 'pending',
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL,
    
    INDEX idx_department_id (department_id),
    INDEX idx_created_by (created_by),
    INDEX idx_related_table (related_table),
    INDEX idx_related_id (related_id),
    INDEX idx_status (status),
    INDEX idx_deadline (deadline),
    INDEX idx_created_at (created_at),
    INDEX idx_aiptref (aiptref),
    
    FOREIGN KEY (department_id) REFERENCES departments(id) ON DELETE CASCADE,
    FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE CASCADE
);
```

#### Accounting Transactions Comments Table
**Purpose**: Comments and notes for transactions

```sql
CREATE TABLE acc_transactions_comments (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    transaction_id BIGINT UNSIGNED NOT NULL,
    comment TEXT NOT NULL,
    added_by BIGINT UNSIGNED NOT NULL,
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL,
    
    INDEX idx_transaction_id (transaction_id),
    INDEX idx_added_by (added_by),
    INDEX idx_created_at (created_at),
    
    FOREIGN KEY (transaction_id) REFERENCES acc_transactions(id) ON DELETE CASCADE,
    FOREIGN KEY (added_by) REFERENCES users(id) ON DELETE CASCADE
);
```

### Patent Module

#### Patent Tickets Table
**Purpose**: Patent applications and management

```sql
CREATE TABLE patent_tickets (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    client_id BIGINT UNSIGNED NULL,
    added_by BIGINT UNSIGNED NULL,
    associate_id BIGINT UNSIGNED NULL,
    methode_id BIGINT UNSIGNED NULL,
    assigned_to BIGINT UNSIGNED NULL,
    class_id BIGINT UNSIGNED NULL,
    procedure_id BIGINT UNSIGNED NULL,
    country_id BIGINT UNSIGNED NULL,
    aiptref VARCHAR(255) NULL,
    clientref VARCHAR(255) NULL,
    title VARCHAR(255) NOT NULL,
    pct_date DATE NULL,
    pct_no VARCHAR(255) NULL,
    regular_date DATE NULL,
    regular_no VARCHAR(255) NULL,
    filingno VARCHAR(255) NULL,
    requesteddate DATE NULL,
    deadline_Status ENUM('pending', 'overdue', 'completed') DEFAULT 'pending',
    related_TKT BIGINT UNSIGNED NULL,
    status ENUM('pending', 'approved', 'rejected', 'in_progress') DEFAULT 'pending',
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL,
    deleted_at TIMESTAMP NULL,
    
    INDEX idx_client_id (client_id),
    INDEX idx_added_by (added_by),
    INDEX idx_associate_id (associate_id),
    INDEX idx_methode_id (methode_id),
    INDEX idx_assigned_to (assigned_to),
    INDEX idx_country_id (country_id),
    INDEX idx_status (status),
    INDEX idx_deadline_status (deadline_Status),
    INDEX idx_deleted_at (deleted_at),
    
    FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE CASCADE,
    FOREIGN KEY (added_by) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (associate_id) REFERENCES associates(id) ON DELETE CASCADE,
    FOREIGN KEY (methode_id) REFERENCES methodes(id) ON DELETE CASCADE,
    FOREIGN KEY (assigned_to) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (country_id) REFERENCES countries(id) ON DELETE CASCADE
);
```

### HR Module

#### Employees Table
**Purpose**: Employee records and HR information

```sql
CREATE TABLE employees (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NOT NULL,
    employee_id VARCHAR(50) UNIQUE NOT NULL,
    position VARCHAR(255) NULL,
    department VARCHAR(255) NULL,
    hire_date DATE NULL,
    salary DECIMAL(10,2) NULL,
    hr_role VARCHAR(100) NULL,
    manager_id BIGINT UNSIGNED NULL,
    status ENUM('active', 'inactive', 'terminated') DEFAULT 'active',
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL,
    
    UNIQUE KEY uk_employee_id (employee_id),
    INDEX idx_user_id (user_id),
    INDEX idx_manager_id (manager_id),
    INDEX idx_status (status),
    INDEX idx_hire_date (hire_date),
    
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (manager_id) REFERENCES employees(id) ON DELETE SET NULL
);
```

#### Attendance Table
**Purpose**: Employee attendance and time tracking

```sql
CREATE TABLE attendance (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    employee_id BIGINT UNSIGNED NOT NULL,
    date DATE NOT NULL,
    check_in TIME NULL,
    check_out TIME NULL,
    hours_worked DECIMAL(4,2) NULL,
    status ENUM('present', 'absent', 'late', 'half_day') DEFAULT 'present',
    notes TEXT NULL,
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL,
    
    UNIQUE KEY uk_employee_date (employee_id, date),
    INDEX idx_employee_id (employee_id),
    INDEX idx_date (date),
    INDEX idx_status (status),
    
    FOREIGN KEY (employee_id) REFERENCES employees(id) ON DELETE CASCADE
);
```

### Litigation Module

#### Litigation Cases Table
**Purpose**: Legal case management and tracking

```sql
CREATE TABLE li_cases (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    aiptref VARCHAR(255) NULL,
    department VARCHAR(255) NULL,
    case_number VARCHAR(255) NULL,
    title VARCHAR(255) NOT NULL,
    client_id BIGINT UNSIGNED NULL,
    attorney_id BIGINT UNSIGNED NULL,
    country_id BIGINT UNSIGNED NULL,
    methode_id BIGINT UNSIGNED NULL,
    case_type ENUM('trademark', 'patent', 'copyright', 'other') DEFAULT 'other',
    status ENUM('open', 'closed', 'settled', 'dismissed') DEFAULT 'open',
    filing_date DATE NULL,
    description TEXT NULL,
    added_by BIGINT UNSIGNED NULL,
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL,
    deleted_at TIMESTAMP NULL,
    
    INDEX idx_client_id (client_id),
    INDEX idx_attorney_id (attorney_id),
    INDEX idx_country_id (country_id),
    INDEX idx_methode_id (methode_id),
    INDEX idx_status (status),
    INDEX idx_case_type (case_type),
    INDEX idx_filing_date (filing_date),
    INDEX idx_deleted_at (deleted_at),
    
    FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE CASCADE,
    FOREIGN KEY (attorney_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (country_id) REFERENCES countries(id) ON DELETE CASCADE,
    FOREIGN KEY (added_by) REFERENCES users(id) ON DELETE CASCADE
);
```

---

## Relationships

### Entity Relationship Diagram

```mermaid
erDiagram
    users ||--o{ trade_mark_tickets : creates
    users ||--o{ acc_transactions : creates
    users ||--o{ patent_tickets : creates
    users ||--o{ employees : has_profile
    users ||--o{ li_cases : manages
    
    departments ||--o{ users : contains
    departments ||--o{ acc_transactions : processes
    
    countries ||--o{ trade_mark_tickets : filed_in
    countries ||--o{ patent_tickets : filed_in
    countries ||--o{ li_cases : jurisdiction
    
    clients ||--o{ trade_mark_tickets : owns
    clients ||--o{ patent_tickets : owns
    clients ||--o{ li_cases : involved_in
    
    associates ||--o{ trade_mark_tickets : represents
    associates ||--o{ patent_tickets : represents
    
    methodes ||--o{ trade_mark_tickets : uses
    methodes ||--o{ patent_tickets : uses
    
    procedures ||--o{ trade_mark_tickets : follows
    procedures ||--o{ patent_tickets : follows
    
    applicants ||--o{ trade_mark_tickets : applies_for
    applicants ||--o{ patent_tickets : applies_for
    
    trade_mark_tickets ||--o{ trademark_ticket_histories : has_history
    trade_mark_tickets ||--o{ trademark_change_logs : has_changes
    trade_mark_tickets ||--o{ acc_transactions : generates
    
    acc_transactions ||--o{ acc_transactions_comments : has_comments
    
    employees ||--o{ attendance : has_records
    employees ||--o{ employees : manages
```

### Key Relationships

#### One-to-Many Relationships
- **Users → Trademark Tickets**: One user can create many trademark tickets
- **Users → Accounting Transactions**: One user can create many transactions
- **Departments → Users**: One department can have many users
- **Clients → Trademark Tickets**: One client can have many trademark tickets
- **Trademark Tickets → Histories**: One trademark can have many history records

#### Many-to-Many Relationships
- **Users ↔ Roles**: Users can have multiple roles
- **Roles ↔ Permissions**: Roles can have multiple permissions
- **Users ↔ Permissions**: Users can have direct permissions

#### Self-Referencing Relationships
- **Users → Manager**: Users can have managers (other users)
- **Departments → Parent**: Departments can have parent departments
- **Employees → Manager**: Employees can have managers

---

## Indexes

### Performance Indexes

#### Primary Indexes
All tables have primary key indexes on the `id` column for optimal performance.

#### Foreign Key Indexes
All foreign key columns are indexed to improve join performance:
```sql
-- Example foreign key indexes
INDEX idx_client_id (client_id)
INDEX idx_user_id (user_id)
INDEX idx_department_id (department_id)
```

#### Search Indexes
Columns frequently used in WHERE clauses are indexed:
```sql
-- Example search indexes
INDEX idx_trademark_name (trademark_name)
INDEX idx_aiptref (aiptref)
INDEX idx_status (status)
INDEX idx_created_at (created_at)
```

#### Composite Indexes
Multi-column indexes for complex queries:
```sql
-- Example composite indexes
INDEX idx_user_status (user_id, status)
INDEX idx_date_status (created_at, status)
INDEX idx_related_lookup (related_table, related_id)
```

### Index Statistics
- **Total Indexes**: 100+
- **Primary Keys**: 50+
- **Foreign Keys**: 80+
- **Search Indexes**: 60+
- **Composite Indexes**: 20+

---

## Constraints

### Primary Key Constraints
All tables have auto-incrementing primary keys:
```sql
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY
```

### Foreign Key Constraints
Foreign key constraints ensure referential integrity:
```sql
-- Example foreign key constraint
FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE CASCADE
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
```

### Unique Constraints
Unique constraints prevent duplicate data:
```sql
-- Example unique constraints
UNIQUE KEY uk_username (username)
UNIQUE KEY uk_email (email)
UNIQUE KEY uk_employee_id (employee_id)
```

### Check Constraints
Data validation constraints:
```sql
-- Example check constraints
CHECK (value >= 0)
CHECK (status IN ('pending', 'approved', 'rejected'))
CHECK (hire_date <= CURDATE())
```

---

## Triggers

### Audit Triggers
Triggers for maintaining audit trails:

```sql
-- Example audit trigger
DELIMITER $$
CREATE TRIGGER tr_trademark_audit_insert
AFTER INSERT ON trade_mark_tickets
FOR EACH ROW
BEGIN
    INSERT INTO trademark_ticket_histories (
        type, comment, status, related_TKT, added_by, created_at
    ) VALUES (
        'created', 'Trademark created', 'active', NEW.id, NEW.added_by, NOW()
    );
END$$
DELIMITER ;
```

### Update Triggers
Triggers for tracking changes:

```sql
-- Example update trigger
DELIMITER $$
CREATE TRIGGER tr_trademark_audit_update
AFTER UPDATE ON trade_mark_tickets
FOR EACH ROW
BEGIN
    IF OLD.trademark_name != NEW.trademark_name THEN
        INSERT INTO trademark_ticket_histories (
            type, comment, status, related_TKT, added_by, created_at
        ) VALUES (
            'updated', CONCAT('Trademark name changed from "', OLD.trademark_name, '" to "', NEW.trademark_name, '"'), 
            'updated', NEW.id, NEW.added_by, NOW()
        );
    END IF;
END$$
DELIMITER ;
```

---

## Views

### Reporting Views

#### Trademark Summary View
```sql
CREATE VIEW v_trademark_summary AS
SELECT 
    t.id,
    t.trademark_name,
    t.aiptref,
    c.client_name,
    co.country_name,
    u.name as assigned_to,
    t.status,
    t.filing_date,
    t.created_at
FROM trade_mark_tickets t
LEFT JOIN clients c ON t.client_id = c.id
LEFT JOIN countries co ON t.country_id = co.id
LEFT JOIN users u ON t.assigned_to = u.id
WHERE t.deleted_at IS NULL;
```

#### Transaction Summary View
```sql
CREATE VIEW v_transaction_summary AS
SELECT 
    t.id,
    t.reference_no,
    t.type,
    t.value,
    t.currency,
    t.status,
    d.department_name,
    u.name as created_by,
    t.created_at
FROM acc_transactions t
LEFT JOIN departments d ON t.department_id = d.id
LEFT JOIN users u ON t.created_by = u.id;
```

#### Employee Summary View
```sql
CREATE VIEW v_employee_summary AS
SELECT 
    e.id,
    e.employee_id,
    u.name,
    u.email,
    e.position,
    e.department,
    e.hire_date,
    e.status,
    m.name as manager_name
FROM employees e
LEFT JOIN users u ON e.user_id = u.id
LEFT JOIN employees m ON e.manager_id = m.id
LEFT JOIN users mu ON m.user_id = mu.id;
```

---

## Stored Procedures

### Utility Procedures

#### Get User Permissions
```sql
DELIMITER $$
CREATE PROCEDURE sp_get_user_permissions(IN user_id BIGINT)
BEGIN
    SELECT DISTINCT p.name, p.module, p.description
    FROM permissions p
    JOIN model_has_permissions mhp ON p.id = mhp.permission_id
    WHERE mhp.model_id = user_id AND mhp.model_type = 'App\\Models\\User'
    UNION
    SELECT DISTINCT p.name, p.module, p.description
    FROM permissions p
    JOIN role_has_permissions rhp ON p.id = rhp.permission_id
    JOIN model_has_roles mhr ON rhp.role_id = mhr.role_id
    WHERE mhr.model_id = user_id AND mhr.model_type = 'App\\Models\\User';
END$$
DELIMITER ;
```

#### Generate Report Data
```sql
DELIMITER $$
CREATE PROCEDURE sp_generate_trademark_report(
    IN start_date DATE,
    IN end_date DATE,
    IN country_id BIGINT
)
BEGIN
    SELECT 
        t.id,
        t.trademark_name,
        t.aiptref,
        c.client_name,
        co.country_name,
        t.filing_date,
        t.status
    FROM trade_mark_tickets t
    LEFT JOIN clients c ON t.client_id = c.id
    LEFT JOIN countries co ON t.country_id = co.id
    WHERE t.created_at BETWEEN start_date AND end_date
    AND (country_id IS NULL OR t.country_id = country_id)
    AND t.deleted_at IS NULL
    ORDER BY t.created_at DESC;
END$$
DELIMITER ;
```

---

## Data Dictionary

### Core Tables Data Dictionary

#### Users Table
| Column | Type | Null | Key | Default | Description |
|--------|------|------|-----|---------|-------------|
| id | BIGINT UNSIGNED | NO | PRI | AUTO_INCREMENT | Unique identifier |
| name | VARCHAR(255) | NO | | | Full name of user |
| username | VARCHAR(255) | NO | UNI | | Unique username |
| email | VARCHAR(255) | NO | UNI | | Email address |
| password | VARCHAR(255) | NO | | | Hashed password |
| department_id | BIGINT UNSIGNED | YES | MUL | NULL | Department foreign key |
| role_id | BIGINT UNSIGNED | YES | MUL | NULL | Role foreign key |
| status | BOOLEAN | YES | | TRUE | Account status |
| created_at | TIMESTAMP | YES | | NULL | Creation timestamp |
| updated_at | TIMESTAMP | YES | | NULL | Last update timestamp |
| deleted_at | TIMESTAMP | YES | MUL | NULL | Soft delete timestamp |

#### Trade Mark Tickets Table
| Column | Type | Null | Key | Default | Description |
|--------|------|------|-----|---------|-------------|
| id | BIGINT UNSIGNED | NO | PRI | AUTO_INCREMENT | Unique identifier |
| trademark_name | VARCHAR(255) | NO | MUL | | Name of trademark |
| aiptref | TEXT | YES | MUL | NULL | AIPT reference number |
| clientref | VARCHAR(255) | YES | | 'n/a' | Client reference |
| client_id | BIGINT UNSIGNED | YES | MUL | NULL | Client foreign key |
| country_id | BIGINT UNSIGNED | YES | MUL | NULL | Country foreign key |
| filing_date | DATE | YES | MUL | NULL | Filing date |
| status | ENUM | YES | MUL | 'pending' | Current status |
| created_at | TIMESTAMP | YES | MUL | NULL | Creation timestamp |
| updated_at | TIMESTAMP | YES | | NULL | Last update timestamp |
| deleted_at | TIMESTAMP | YES | MUL | NULL | Soft delete timestamp |

### Module Tables Data Dictionary

#### Accounting Transactions Table
| Column | Type | Null | Key | Default | Description |
|--------|------|------|-----|---------|-------------|
| id | BIGINT UNSIGNED | NO | PRI | AUTO_INCREMENT | Unique identifier |
| reference_no | VARCHAR(255) | YES | | NULL | Transaction reference |
| value | DECIMAL(15,2) | YES | | NULL | Transaction amount |
| currency | VARCHAR(255) | YES | | NULL | Currency code |
| related_table | VARCHAR(255) | YES | MUL | NULL | Related table name |
| related_id | BIGINT UNSIGNED | YES | MUL | NULL | Related record ID |
| status | ENUM | YES | MUL | 'pending' | Transaction status |
| created_by | BIGINT UNSIGNED | YES | MUL | NULL | Creator user ID |
| created_at | TIMESTAMP | YES | MUL | NULL | Creation timestamp |

#### Employees Table
| Column | Type | Null | Key | Default | Description |
|--------|------|------|-----|---------|-------------|
| id | BIGINT UNSIGNED | NO | PRI | AUTO_INCREMENT | Unique identifier |
| user_id | BIGINT UNSIGNED | NO | MUL | | User foreign key |
| employee_id | VARCHAR(50) | NO | UNI | | Employee ID number |
| position | VARCHAR(255) | YES | | NULL | Job position |
| department | VARCHAR(255) | YES | | NULL | Department name |
| hire_date | DATE | YES | MUL | NULL | Employment start date |
| salary | DECIMAL(10,2) | YES | | NULL | Annual salary |
| status | ENUM | YES | MUL | 'active' | Employment status |

---

## Database Maintenance

### Regular Maintenance Tasks

#### Index Optimization
```sql
-- Analyze table statistics
ANALYZE TABLE trade_mark_tickets;
ANALYZE TABLE acc_transactions;
ANALYZE TABLE users;

-- Optimize tables
OPTIMIZE TABLE trade_mark_tickets;
OPTIMIZE TABLE acc_transactions;
OPTIMIZE TABLE users;
```

#### Data Cleanup
```sql
-- Clean up old soft-deleted records (older than 1 year)
DELETE FROM trade_mark_tickets 
WHERE deleted_at IS NOT NULL 
AND deleted_at < DATE_SUB(NOW(), INTERVAL 1 YEAR);

-- Clean up old audit logs (older than 2 years)
DELETE FROM trademark_ticket_histories 
WHERE created_at < DATE_SUB(NOW(), INTERVAL 2 YEAR);
```

#### Backup Procedures
```bash
# Full database backup
mysqldump -u username -p --single-transaction --routines --triggers aipt_db > backup_$(date +%Y%m%d).sql

# Schema only backup
mysqldump -u username -p --no-data --routines --triggers aipt_db > schema_backup.sql

# Data only backup
mysqldump -u username -p --no-create-info aipt_db > data_backup.sql
```

---

## Performance Optimization

### Query Optimization Tips

1. **Use Indexes**: Ensure all frequently queried columns are indexed
2. **Limit Results**: Use LIMIT clauses for large result sets
3. **Avoid SELECT ***: Only select required columns
4. **Use JOINs Efficiently**: Use appropriate JOIN types
5. **Monitor Slow Queries**: Enable slow query logging

### Configuration Recommendations

```ini
# MySQL Configuration
innodb_buffer_pool_size = 1G
innodb_log_file_size = 256M
innodb_flush_log_at_trx_commit = 2
query_cache_size = 64M
query_cache_type = 1
slow_query_log = 1
long_query_time = 2
```

---

*This database schema documentation is maintained by the AIPT development team. For the most up-to-date information, always refer to the latest version of this guide.*

**Last Updated**: [Current Date]  
**Schema Version**: 1.0  
**Database Version**: MySQL 5.7+ / MariaDB 10.3+