-- Safepoint Escrow System Database Schema
-- Run this file to create the database structure

-- Create database if it doesn't exist
CREATE DATABASE IF NOT EXISTS escrow_system;
USE escrow_system;

-- Escrows table
CREATE TABLE IF NOT EXISTS escrows (
    id INT PRIMARY KEY AUTO_INCREMENT,
    creator_id INT NOT NULL,
    counterparty_username VARCHAR(100) NOT NULL,
    counterparty_id INT NULL,
    amount DECIMAL(15,2) NOT NULL,
    fee_creator DECIMAL(15,2) NOT NULL,
    fee_counterparty DECIMAL(15,2) NOT NULL,
    fee_payer ENUM('ME', 'OTHER', 'BOTH') NOT NULL,
    release_code_hash VARCHAR(255) NOT NULL,
    state ENUM('PENDING', 'ACCEPTED', 'RELEASED', 'CANCELLED', 'DISPUTED', 'EXPIRED') DEFAULT 'PENDING',
    expires_at TIMESTAMP NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    accepted_at TIMESTAMP NULL,
    released_at TIMESTAMP NULL,
    FOREIGN KEY (creator_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (counterparty_id) REFERENCES users(id) ON DELETE SET NULL,
    INDEX idx_creator_id (creator_id),
    INDEX idx_counterparty_username (counterparty_username),
    INDEX idx_state (state),
    INDEX idx_expires_at (expires_at),
    INDEX idx_created_at (created_at)
);

-- Double-entry ledger table
CREATE TABLE IF NOT EXISTS ledger_entries (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    escrow_id INT NULL,
    transaction_type ENUM('FUND', 'HOLD', 'RELEASE', 'REFUND', 'FEE', 'DISPUTE') NOT NULL,
    amount DECIMAL(15,2) NOT NULL,
    balance_type ENUM('AVAILABLE', 'ESCROWED') NOT NULL,
    movement ENUM('DEBIT', 'CREDIT') NOT NULL,
    reference_id VARCHAR(100),
    description TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (escrow_id) REFERENCES escrows(id) ON DELETE SET NULL,
    INDEX idx_user_id (user_id),
    INDEX idx_escrow_id (escrow_id),
    INDEX idx_transaction_type (transaction_type),
    INDEX idx_created_at (created_at),
    INDEX idx_reference_id (reference_id)
);

-- Disputes table for tracking dispute details
CREATE TABLE IF NOT EXISTS disputes (
    id INT PRIMARY KEY AUTO_INCREMENT,
    escrow_id INT NOT NULL,
    raised_by_user_id INT NOT NULL,
    reason TEXT NOT NULL,
    status ENUM('OPEN', 'RESOLVED', 'CLOSED') DEFAULT 'OPEN',
    admin_notes TEXT,
    resolved_at TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (escrow_id) REFERENCES escrows(id) ON DELETE CASCADE,
    FOREIGN KEY (raised_by_user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_escrow_id (escrow_id),
    INDEX idx_status (status),
    INDEX idx_created_at (created_at)
);

-- Audit log for system events
CREATE TABLE IF NOT EXISTS audit_log (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NULL,
    action VARCHAR(100) NOT NULL,
    entity_type VARCHAR(50) NOT NULL,
    entity_id INT NULL,
    details JSON,
    ip_address VARCHAR(45),
    user_agent TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL,
    INDEX idx_user_id (user_id),
    INDEX idx_action (action),
    INDEX idx_entity_type (entity_type),
    INDEX idx_created_at (created_at)
);

-- Pending funding requests
CREATE TABLE IF NOT EXISTS pending_funding (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    amount DECIMAL(15,2) NOT NULL,
    payment_method VARCHAR(50) NOT NULL,
    payment_reference VARCHAR(100),
    status ENUM('PENDING', 'COMPLETED', 'FAILED', 'CANCELLED') DEFAULT 'PENDING',
    verified_by INT NULL,
    verified_at TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (verified_by) REFERENCES users(id) ON DELETE SET NULL,
    INDEX idx_user_id (user_id),
    INDEX idx_status (status),
    INDEX idx_payment_reference (payment_reference),
    INDEX idx_created_at (created_at)
);
