CREATE DATABASE IF NOT EXISTS fundz_marketplace CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE fundz_marketplace;
CREATE TABLE users (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,name VARCHAR(150) NOT NULL,email VARCHAR(190) NOT NULL UNIQUE,password_hash VARCHAR(255) NOT NULL,role ENUM('business','provider','operator','admin') NOT NULL DEFAULT 'business',status ENUM('pending','active','suspended','rejected') NOT NULL DEFAULT 'active',last_login_at DATETIME NULL,created_at DATETIME NOT NULL,updated_at DATETIME NULL) ENGINE=InnoDB;
CREATE TABLE business_profiles (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,user_id BIGINT UNSIGNED NOT NULL,company_name VARCHAR(190),industry VARCHAR(100),country VARCHAR(100),phone VARCHAR(50),whatsapp VARCHAR(50),monthly_fx_volume DECIMAL(18,2) DEFAULT 0,preferred_channel VARCHAR(30),created_at DATETIME NOT NULL,UNIQUE(user_id),FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE) ENGINE=InnoDB;
CREATE TABLE provider_profiles (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,user_id BIGINT UNSIGNED NOT NULL,legal_name VARCHAR(190),registration_number VARCHAR(100),license_claim VARCHAR(190),country VARCHAR(100),city VARCHAR(100),website VARCHAR(255),min_transaction DECIMAL(18,2),max_transaction DECIMAL(18,2),verification_status ENUM('pending','verified','rejected','suspended') DEFAULT 'pending',verification_notes TEXT,created_at DATETIME NOT NULL,UNIQUE(user_id),FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE) ENGINE=InnoDB;
CREATE TABLE rate_sources (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,name VARCHAR(150) NOT NULL,source_type ENUM('app','provider','reference') NOT NULL,website_url VARCHAR(255),collection_method ENUM('api','scraper','browser','manual','provider') NOT NULL DEFAULT 'manual',module_key VARCHAR(100),owner_user_id BIGINT UNSIGNED NULL,priority INT DEFAULT 100,status ENUM('active','paused','error') DEFAULT 'active',last_success_at DATETIME NULL,last_error TEXT NULL,created_at DATETIME NOT NULL,FOREIGN KEY(owner_user_id) REFERENCES users(id) ON DELETE SET NULL) ENGINE=InnoDB;
CREATE TABLE rate_quotes (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,source_id BIGINT UNSIGNED NOT NULL,from_currency CHAR(3) NOT NULL,to_currency CHAR(3) NOT NULL,send_amount DECIMAL(18,2) NOT NULL,rate DECIMAL(24,8) NOT NULL,fee_amount DECIMAL(18,2) DEFAULT 0,recipient_amount DECIMAL(24,4) NOT NULL,delivery_label VARCHAR(100),data_method VARCHAR(30),status ENUM('pending','approved','rejected','expired') DEFAULT 'pending',validation_note TEXT,captured_by BIGINT UNSIGNED NULL,approved_by BIGINT UNSIGNED NULL,captured_at DATETIME NOT NULL,approved_at DATETIME NULL,expires_at DATETIME NOT NULL,INDEX pair_fresh(from_currency,to_currency,status,expires_at),FOREIGN KEY(source_id) REFERENCES rate_sources(id),FOREIGN KEY(captured_by) REFERENCES users(id) ON DELETE SET NULL,FOREIGN KEY(approved_by) REFERENCES users(id) ON DELETE SET NULL) ENGINE=InnoDB;
CREATE TABLE quote_requests (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,user_id BIGINT UNSIGNED NOT NULL,from_currency CHAR(3) NOT NULL,to_currency CHAR(3) NOT NULL,amount DECIMAL(18,2) NOT NULL,needed_by DATE,purpose TEXT,status ENUM('open','matched','closed','cancelled') DEFAULT 'open',created_at DATETIME NOT NULL,FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE) ENGINE=InnoDB;
CREATE TABLE provider_quotes (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,request_id BIGINT UNSIGNED NOT NULL,provider_user_id BIGINT UNSIGNED NOT NULL,rate DECIMAL(24,8),fee_amount DECIMAL(18,2),recipient_amount DECIMAL(24,4),expires_at DATETIME,status ENUM('submitted','viewed','accepted','declined','expired') DEFAULT 'submitted',created_at DATETIME NOT NULL,FOREIGN KEY(request_id) REFERENCES quote_requests(id) ON DELETE CASCADE,FOREIGN KEY(provider_user_id) REFERENCES users(id) ON DELETE CASCADE) ENGINE=InnoDB;
CREATE TABLE rate_alerts (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,user_id BIGINT UNSIGNED NOT NULL,from_currency CHAR(3),to_currency CHAR(3),target_rate DECIMAL(24,8),direction ENUM('above','below'),channel ENUM('email','sms','whatsapp'),active TINYINT(1) DEFAULT 1,last_triggered_at DATETIME NULL,created_at DATETIME NOT NULL,FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE) ENGINE=InnoDB;
CREATE TABLE fx_plans (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,user_id BIGINT UNSIGNED NOT NULL,from_currency CHAR(3),to_currency CHAR(3),amount DECIMAL(18,2),needed_on DATE,target_rate DECIMAL(24,8),notes TEXT,status VARCHAR(30) DEFAULT 'planned',created_at DATETIME NOT NULL,FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE) ENGINE=InnoDB;
CREATE TABLE scraper_runs (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,source_id BIGINT UNSIGNED NOT NULL,started_at DATETIME NOT NULL,finished_at DATETIME NULL,status ENUM('running','success','failed','partial') NOT NULL,quotes_found INT DEFAULT 0,error_message TEXT,http_status INT NULL,FOREIGN KEY(source_id) REFERENCES rate_sources(id) ON DELETE CASCADE) ENGINE=InnoDB;
CREATE TABLE audit_logs (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,user_id BIGINT UNSIGNED NULL,action VARCHAR(100) NOT NULL,entity_type VARCHAR(80),entity_id BIGINT UNSIGNED,metadata TEXT,created_at DATETIME NOT NULL,FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE SET NULL) ENGINE=InnoDB;
