-- =====================================================================
-- Madax Escrow Database Schema (Updated with Brokers + Commissions)
-- =====================================================================
-- Compatible with MySQL / MariaDB
-- Engine: InnoDB
-- Charset: utf8mb4
-- Collation: utf8mb4_unicode_ci
-- =====================================================================

/*
INSTRUCTIONS FOR IMPORT (phpMyAdmin):
-------------------------------------
1. Log into your Namecheap cPanel.
2. Open phpMyAdmin → select or create a database.
3. Click the “Import” tab.
4. Choose this SQL file (madax_schema.sql).
5. Click “Go” to execute.
6. Edit `src/config.php` with your database credentials.
*/

-- ------------------------------------------------------------
-- Drop old tables if they exist (optional)
-- ------------------------------------------------------------

-- ------------------------------------------------------------
-- ESCROWS TABLE
-- Stores main escrow transactions between buyers and sellers
-- ------------------------------------------------------------
CREATE TABLE escrows (
    id INT AUTO_INCREMENT PRIMARY KEY,
    buyer_id INT NOT NULL,
    seller_id INT NOT NULL,
    amount DECIMAL(12,2) NOT NULL,
    status ENUM('pending','funded','completed','disputed','cancelled') DEFAULT 'pending',
    description TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (buyer_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (seller_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- ESCROW_BROKERS TABLE
-- Junction table linking escrows ↔ brokers (many-to-many)
-- Includes commission tracking fields
-- ------------------------------------------------------------
CREATE TABLE escrow_brokers (
    escrow_id INT NOT NULL,
    broker_id INT NOT NULL,
    commission_percent DECIMAL(5,2) DEFAULT 0.00,
    commission_amount DECIMAL(10,2) DEFAULT 0.00,
    assigned_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (escrow_id, broker_id),
    FOREIGN KEY (escrow_id) REFERENCES escrows(id) ON DELETE CASCADE,
    FOREIGN KEY (broker_id) REFERENCES brokers(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- TRANSACTIONS_LOG TABLE
-- Generic event log for escrow updates and actions
-- ------------------------------------------------------------
CREATE TABLE transactions_log (
    id INT AUTO_INCREMENT PRIMARY KEY,
    escrow_id INT 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;

INSERT INTO escrows (buyer_id, seller_id, amount, status, description)
VALUES (1, 2, 500.00, 'pending', 'Example escrow between John and Alice.');

INSERT INTO escrow_brokers (escrow_id, broker_id, commission_percent, commission_amount)
VALUES (1, 1, 5.00, 25.00);

INSERT INTO transactions_log (escrow_id, action, details)
VALUES (1, 'created', 'Escrow created by John Buyer for Alice Seller.');
