╔══════════════════════════════════════════════════════════════════╗
║   FERRERO ASSET TRACKING DATABASE - QUICK REFERENCE CARD        ║
╚══════════════════════════════════════════════════════════════════╝

CONNECTION DETAILS
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
  Host:     localhost
  Port:     5433
  Database: ferrero_tracking
  User:     ferrero_user
  Password: ferrero_pass_2025

CONNECTION STRING
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
  postgresql://ferrero_user:ferrero_pass_2025@localhost:5433/ferrero_tracking

COMMAND LINE ACCESS
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
  PGPASSWORD=ferrero_pass_2025 psql -h localhost -p 5433 \
    -U ferrero_user -d ferrero_tracking

PYTHON (psycopg2)
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
  import psycopg2
  conn = psycopg2.connect(
      host="localhost", port=5433,
      database="ferrero_tracking",
      user="ferrero_user", password="ferrero_pass_2025"
  )

PHP (PDO)
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
  $pdo = new PDO(
      'pgsql:host=localhost;port=5433;dbname=ferrero_tracking',
      'ferrero_user', 'ferrero_pass_2025'
  );

NODE.JS (pg)
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
  const client = new Client({
    host: 'localhost', port: 5433,
    database: 'ferrero_tracking',
    user: 'ferrero_user', password: 'ferrero_pass_2025'
  });

MAIN TABLES
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
  master_assets          - Master assets from OpenText DAM
  derivative_assets      - Derivative assets linked to masters
  asset_lifecycle_events - Complete audit trail
  tracking_id_log        - Tracking ID generation log

USEFUL VIEWS
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
  v_master_assets_complete    - Masters with derivative count
  v_derivatives_with_master   - Derivatives with master info

COMMON QUERIES
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
  -- Lookup by tracking ID
  SELECT * FROM master_assets WHERE tracking_id = '5643CQ';

  -- Lookup by OpenText ID
  SELECT * FROM master_assets WHERE opentext_id = 'OT_12345';

  -- Lookup by upload directory
  SELECT * FROM master_assets
  WHERE upload_directory = '/data/assets/2025/10/ferrero';

  -- Search by brand
  SELECT * FROM master_assets WHERE brand_code = 'NUT';

  -- Get asset with derivatives
  SELECT * FROM v_master_assets_complete WHERE tracking_id = '5643CQ';

  -- Get lifecycle events
  SELECT * FROM asset_lifecycle_events
  WHERE tracking_id = '5643CQ'
  ORDER BY event_timestamp DESC;

REST API (Alternative to Direct DB Access)
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
  Base URL: http://ferrero-naming-tool-v2:8893/api.php

  Lookup tracking ID:
    curl -X POST 'http://ferrero-naming-tool-v2:8893/api.php?action=lookup-tracking-id' \
      -H "Content-Type: application/json" \
      -d '{"tracking_id":"5643CQ"}'

  Generate tracking ID:
    curl 'http://ferrero-naming-tool-v2:8893/api.php?action=generate-tracking-id'

  Test connection:
    curl 'http://ferrero-naming-tool-v2:8893/api.php?action=test-connection'

KEY FIELDS IN master_assets
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
  tracking_id         VARCHAR(6)      - Unique 6-char ID (e.g., '5643CQ')
  opentext_id         VARCHAR(255)    - OpenText DAM asset ID
  original_filename   VARCHAR(500)    - Filename without extension
  file_extension      VARCHAR(20)     - Extension (e.g., '.mp4')
  upload_directory    VARCHAR(1000)   - File system path (NEW!)
  brand_code          VARCHAR(5)      - Brand code (e.g., 'NUT')
  brand_name          VARCHAR(255)    - Full brand name
  country_code        VARCHAR(2)      - ISO 3166-1 code
  language_code       VARCHAR(3)      - ISO 639-1 code
  asset_type          VARCHAR(3)      - Asset type (OLV, TVC, PKI, etc.)
  file_size_bytes     BIGINT          - File size
  mime_type           VARCHAR(100)    - MIME type
  tags                TEXT[]          - PostgreSQL array
  description         TEXT            - Asset description
  status              VARCHAR(50)     - Status (active, archived, etc.)
  created_at          TIMESTAMP       - When ingested

MANAGEMENT COMMANDS (After Linux deployment)
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
  ferrero-db-status.sh   - View status and statistics
  ferrero-db-backup.sh   - Create backup
  ferrero-db-restart.sh  - Restart database

LOCAL DEVELOPMENT COMMANDS
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
  ./db-start.sh          - Start database
  ./db-stop.sh           - Stop database
  ./db-logs.sh           - View logs
  ./test-api.sh          - Test API endpoints

DOCUMENTATION
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
  DATABASE-CONNECTION-INFO.md  - Complete integration guide (this file)
  DEPLOY-LINUX.md              - Linux deployment guide
  README-POSTGRES.md           - Technical documentation
  INSTALL-V2.md                - Installation guide

PGADMIN WEB INTERFACE
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
  URL:      http://localhost:5050
  Email:    admin@ferrero.local
  Password: admin

  Add Server:
    Host: host.docker.internal (Mac) or localhost
    Port: 5432 (internal Docker port)
    Database: ferrero_tracking
    Username: ferrero_user
    Password: ferrero_pass_2025

SAMPLE TRACKING IDs (For Testing)
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
  5643CQ  - Your test asset (8000500247167_8.tif)
  a7K9mP  - Raffaello Maestro SD
  b3Xk2N  - Nutella Breakfast HD
  c9Qm4P  - Kinder Bueno Christmas

━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
© Ferrero. All rights reserved.
Last Updated: 2025-10-27
Database Version: 1.1 (with upload_directory field)
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
