SET NAMES utf8mb4;
CREATE TABLE admin_roles (id TINYINT UNSIGNED PRIMARY KEY AUTO_INCREMENT, name VARCHAR(40) NOT NULL UNIQUE, permissions JSON NOT NULL) ENGINE=InnoDB;
CREATE TABLE admins (id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, role_id TINYINT UNSIGNED NOT NULL, email VARCHAR(190) NOT NULL UNIQUE, password_hash VARCHAR(255) NOT NULL, totp_secret_enc TEXT NULL, failed_logins INT NOT NULL DEFAULT 0, locked_until DATETIME NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (role_id) REFERENCES admin_roles(id)) ENGINE=InnoDB;
CREATE TABLE donors (id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, first_name VARCHAR(80), last_name VARCHAR(80), email VARCHAR(190) NOT NULL, country CHAR(2), address VARCHAR(255), marketing_opt_in TINYINT(1) DEFAULT 0, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, INDEX(email)) ENGINE=InnoDB;
CREATE TABLE campaigns (id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, slug VARCHAR(140) NOT NULL UNIQUE, title VARCHAR(190) NOT NULL, description TEXT, image_path VARCHAR(255), target_amount DECIMAL(14,2) DEFAULT 0, deadline DATE NULL, is_active TINYINT(1) DEFAULT 1, is_featured TINYINT(1) DEFAULT 0, archived_at DATETIME NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP) ENGINE=InnoDB;
CREATE TABLE payment_methods (code VARCHAR(30) PRIMARY KEY, name VARCHAR(80) NOT NULL, is_enabled TINYINT(1) NOT NULL DEFAULT 0, mode ENUM('test','live') NOT NULL DEFAULT 'test', sort_order INT DEFAULT 0, connection_status ENUM('untested','ok','failed') DEFAULT 'untested', last_tested_at DATETIME NULL, min_amount DECIMAL(14,2) DEFAULT 1, max_amount DECIMAL(14,2) NULL, currencies JSON NULL) ENGINE=InnoDB;
CREATE TABLE payment_gateway_settings (id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, method_code VARCHAR(30) NOT NULL, setting_key VARCHAR(60) NOT NULL, value_enc TEXT NOT NULL, is_secret TINYINT(1) DEFAULT 0, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, UNIQUE(method_code,setting_key), FOREIGN KEY (method_code) REFERENCES payment_methods(code)) ENGINE=InnoDB;
CREATE TABLE donations (id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT, reference CHAR(16) NOT NULL UNIQUE, donor_id INT UNSIGNED NOT NULL, campaign_id INT UNSIGNED NULL, method_code VARCHAR(30) NOT NULL, amount DECIMAL(14,2) NOT NULL, currency CHAR(3) NOT NULL, is_recurring TINYINT(1) DEFAULT 0, is_anonymous TINYINT(1) DEFAULT 0, dedication VARCHAR(500), in_memory_of VARCHAR(190), on_behalf_of VARCHAR(190),
 status ENUM('pending','processing','confirmed','failed','cancelled','refunded','expired') NOT NULL DEFAULT 'pending', verified_by ENUM('gateway','webhook','blockchain','manual') NULL, verified_at DATETIME NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 INDEX(status), INDEX(method_code), INDEX(created_at), FOREIGN KEY (donor_id) REFERENCES donors(id), FOREIGN KEY (campaign_id) REFERENCES campaigns(id), FOREIGN KEY (method_code) REFERENCES payment_methods(code)) ENGINE=InnoDB;
CREATE TABLE payment_transactions (id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT, donation_id BIGINT UNSIGNED NOT NULL, provider_ref VARCHAR(120) NOT NULL, status VARCHAR(30) NOT NULL, raw_response JSON NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, UNIQUE(provider_ref), FOREIGN KEY (donation_id) REFERENCES donations(id)) ENGINE=InnoDB;
CREATE TABLE crypto_wallets (id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, asset VARCHAR(10) NOT NULL, network VARCHAR(20) NOT NULL, address VARCHAR(120) NOT NULL, min_amount DECIMAL(20,8) DEFAULT 0, confirmations_required INT DEFAULT 3, is_enabled TINYINT(1) DEFAULT 0, instructions TEXT, UNIQUE(asset,network)) ENGINE=InnoDB;
CREATE TABLE crypto_transactions (id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT, donation_id BIGINT UNSIGNED NOT NULL, wallet_id INT UNSIGNED NOT NULL, tx_hash VARCHAR(140) NOT NULL, amount_expected DECIMAL(28,8), amount_received DECIMAL(28,8) NULL, confirmations INT DEFAULT 0, verification ENUM('submitted','seen','confirmed','mismatch','rejected') DEFAULT 'submitted', last_checked_at DATETIME NULL, UNIQUE(wallet_id,tx_hash), FOREIGN KEY (donation_id) REFERENCES donations(id), FOREIGN KEY (wallet_id) REFERENCES crypto_wallets(id)) ENGINE=InnoDB;
CREATE TABLE bank_transfer_submissions (id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT, donation_id BIGINT UNSIGNED NOT NULL, proof_path VARCHAR(255), note VARCHAR(500), review ENUM('submitted','approved','rejected') DEFAULT 'submitted', reviewed_by INT UNSIGNED NULL, reviewed_at DATETIME NULL, FOREIGN KEY (donation_id) REFERENCES donations(id)) ENGINE=InnoDB;
CREATE TABLE refunds (id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT, donation_id BIGINT UNSIGNED NOT NULL, amount DECIMAL(14,2) NOT NULL, provider_ref VARCHAR(120), reason VARCHAR(255), created_by INT UNSIGNED, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (donation_id) REFERENCES donations(id)) ENGINE=InnoDB;
CREATE TABLE email_settings (setting_key VARCHAR(40) PRIMARY KEY, value_enc TEXT) ENGINE=InnoDB;
CREATE TABLE website_settings (setting_key VARCHAR(60) PRIMARY KEY, value TEXT) ENGINE=InnoDB;
CREATE TABLE admin_activity_logs (id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT, admin_id INT UNSIGNED NULL, action VARCHAR(80) NOT NULL, target VARCHAR(120), ip VARBINARY(16), detail JSON NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, INDEX(admin_id)) ENGINE=InnoDB;
CREATE TABLE webhook_events (id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT, provider VARCHAR(30) NOT NULL, event_id VARCHAR(120) NOT NULL, payload MEDIUMTEXT, processed_at DATETIME NULL, UNIQUE(provider,event_id)) ENGINE=InnoDB;
INSERT INTO payment_methods (code,name,sort_order) VALUES ('crypto','Cryptocurrency',1),('paypal','PayPal',2),('card','Credit/Debit Card',3),('apple_pay','Apple Pay',4),('google_pay','Google Pay',5),('bank_transfer','Bank Transfer',6),('stripe','Stripe',7),('paystack','Paystack',8),('flutterwave','Flutterwave',9);