-- 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;

-- Users table
CREATE TABLE IF NOT EXISTS users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    email VARCHAR(255) UNIQUE NOT NULL,
    username VARCHAR(100) UNIQUE NOT NULL,
    firstname VARCHAR(100) NOT NULL,
    lastname VARCHAR(100) NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    wallet_available DECIMAL(15,2) DEFAULT 0.00,
    wallet_escrowed DECIMAL(15,2) DEFAULT 0.00,
    phone_number VARCHAR(20),
    role ENUM('USER', 'ADMIN') DEFAULT 'USER',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_email (email),
    INDEX idx_username (username),
    INDEX idx_role (role),
    INDEX idx_firstname (firstname),
    INDEX idx_lastname (lastname),
    INDEX idx_created_at (created_at)
);

-- 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)
);

-- API keys for authentication
CREATE TABLE IF NOT EXISTS api_keys (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    api_key VARCHAR(64) UNIQUE NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    expires_at TIMESTAMP NULL,
    last_used_at TIMESTAMP NULL,
    is_active BOOLEAN DEFAULT TRUE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_api_key (api_key),
    INDEX idx_user_id (user_id),
    INDEX idx_expires_at (expires_at),
    INDEX idx_is_active (is_active)
);

-- 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)
);

-- Insert default admin user (password: admin123)
INSERT INTO users (email, username, firstname, lastname, password_hash, wallet_available, wallet_escrowed, role) 
VALUES ('admin@safepoint.com', 'admin', 'System', 'Administrator', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 0.00, 0.00, 'ADMIN')
ON DUPLICATE KEY UPDATE username = username;