/*
===============================================================================
  Madax Escrow Company - Database Schema (madax_schema.sql)
===============================================================================

  This SQL file creates the required database tables for the Madax Escrow
  application, including:
    - users
    - admins
    - escrows
    - transactions_log

  CHARACTER SET: utf8mb4
  STORAGE ENGINE: InnoDB

  HOW TO IMPORT VIA PHPMYADMIN:
  ---------------------------------------------------------------------------
  1. Log in to your Namecheap cPanel.
  2. Open phpMyAdmin.
  3. Select or create a database (e.g. `madax_escrow`).
  4. Click the "Import" tab.
  5. Choose this file (madax_schema.sql) from your computer.
  6. Click "Go" to execute.
  7. Once imported successfully, edit `src/config.php` to match your DB info.
===============================================================================
*/

/* ---------------------------------------------------------------------------
   Drop existing tables (optional, use with caution)
--------------------------------------------------------------------------- */

/* ---------------------------------------------------------------------------
   Table: escrows
   ---------------------------------------------------------------------------
   Stores all escrow transactions created by users.
   Each escrow links to a user (creator/initiator) and includes details like
   amount, currency, and current status.
--------------------------------------------------------------------------- */
CREATE TABLE escrows (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id INT UNSIGNED NOT NULL,
  title VARCHAR(150) NOT NULL,
  description TEXT,
  amount DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  currency VARCHAR(10) NOT NULL DEFAULT 'USD',
  status ENUM('pending','funded','completed','disputed','cancelled') DEFAULT 'pending',
  buyer_email VARCHAR(150),
  seller_email VARCHAR(150),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

/* ---------------------------------------------------------------------------
   Table: transactions_log
   ---------------------------------------------------------------------------
   Logs all escrow-related actions (created, funded, updated, etc.).
   Used for auditing and user/admin visibility.
--------------------------------------------------------------------------- */
CREATE TABLE transactions_log (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  escrow_id INT UNSIGNED NOT NULL,
  actor_type ENUM('user','admin') NOT NULL,
  actor_id INT UNSIGNED NOT NULL,
  action VARCHAR(100) NOT NULL,
  details TEXT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (escrow_id) REFERENCES escrows(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

/* ---------------------------------------------------------------------------
   End of Schema
--------------------------------------------------------------------------- */

-- TABLE DESCRIPTIONS SUMMARY
-- ------------------------------------------------
-- users              → Stores customer login accounts (buyers/sellers)
-- admins             → Stores admin credentials for managing platform
-- escrows            → Stores escrow transactions and statuses
-- transactions_log   → Records history of escrow actions for audit trail

/* =======================================================================
   NOTE:
   - After import, replace the placeholder admin hash above with a real
     bcrypt hash generated by PHP's password_hash().
     Example PHP one-liner:
       <?php echo password_hash('YourStrongPasswordHere', PASSWORD_DEFAULT); ?>
   ======================================================================= */
