CREATE TABLE IF NOT EXISTS users (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 email VARCHAR(190) NOT NULL UNIQUE,
 password_hash VARCHAR(255) NOT NULL,
 full_name VARCHAR(190) NOT NULL,
 role ENUM('borrower','admin') NOT NULL DEFAULT 'borrower',
 country_code CHAR(2) NULL,
 created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS api_tokens (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 user_id BIGINT UNSIGNED NOT NULL,
 token_hash CHAR(64) NOT NULL UNIQUE,
 expires_at DATETIME NOT NULL,
 created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
 FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS loan_plans (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 name VARCHAR(60) NOT NULL UNIQUE,
 min_amount DECIMAL(18,2) NOT NULL,
 max_amount DECIMAL(18,2) NOT NULL,
 annual_rate DECIMAL(7,4) NOT NULL,
 processing_percent DECIMAL(7,4) NOT NULL,
 min_months SMALLINT UNSIGNED NOT NULL,
 max_months SMALLINT UNSIGNED NOT NULL,
 enabled BOOLEAN NOT NULL DEFAULT TRUE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS kyc_cases (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 user_id BIGINT UNSIGNED NOT NULL UNIQUE,
 status ENUM('UNVERIFIED','PENDING','APPROVED','REJECTED') NOT NULL DEFAULT 'UNVERIFIED',
 review_note TEXT NULL,
 reviewed_by BIGINT UNSIGNED NULL,
 updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS kyc_documents (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 case_id BIGINT UNSIGNED NOT NULL,
 kind ENUM('identity','residence') NOT NULL,
 stored_name VARCHAR(128) NOT NULL,
 mime_type VARCHAR(80) NOT NULL,
 uploaded_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
 FOREIGN KEY (case_id) REFERENCES kyc_cases(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS loan_applications (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 user_id BIGINT UNSIGNED NOT NULL,
 plan_id BIGINT UNSIGNED NOT NULL,
 amount_usd DECIMAL(18,2) NOT NULL,
 term_months SMALLINT UNSIGNED NOT NULL,
 annual_rate DECIMAL(7,4) NOT NULL,
 processing_percent DECIMAL(7,4) NOT NULL,
 security_percent DECIMAL(7,4) NOT NULL DEFAULT 15.0000,
 purpose VARCHAR(1000) NOT NULL,
 status ENUM('SUBMITTED','UNDER_REVIEW','KYC_REQUIRED','REJECTED') NOT NULL DEFAULT 'SUBMITTED',
 created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
 updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 FOREIGN KEY (user_id) REFERENCES users(id),
 FOREIGN KEY (plan_id) REFERENCES loan_plans(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS audit_events (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 actor_user_id BIGINT UNSIGNED NULL,
 event_name VARCHAR(120) NOT NULL,
 target_type VARCHAR(60) NOT NULL,
 target_id BIGINT UNSIGNED NULL,
 details_json JSON NULL,
 created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
 INDEX(created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
INSERT INTO loan_plans (name,min_amount,max_amount,annual_rate,processing_percent,min_months,max_months) VALUES
 ('Starter',1000,2499.99,12,1.5,6,12),('Standard',2500,4999.99,11,1.25,6,18),('Advanced',5000,9999.99,10,1,12,24),('Premium',10000,24999.99,9,0.75,12,36),('Elite',25000,100000,8,0.5,12,48)
ON DUPLICATE KEY UPDATE name=VALUES(name);