-- FLEXI Platform — schema MySQL/MariaDB limpio, generado 2026-08-11.
-- Sin usuarios, datos demo ni información operativa. Migraciones Laravel son fuente de verdad.
SET NAMES utf8mb4; SET FOREIGN_KEY_CHECKS=0;
CREATE TABLE migrations (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,migration VARCHAR(255) NOT NULL,batch INT NOT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE users (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,public_id VARCHAR(26) NULL UNIQUE,name VARCHAR(255) NOT NULL,email VARCHAR(255) NOT NULL UNIQUE,email_verified_at TIMESTAMP NULL,password VARCHAR(255) NOT NULL,remember_token VARCHAR(100) NULL,created_at TIMESTAMP NULL,updated_at TIMESTAMP NULL,role VARCHAR(20) NOT NULL DEFAULT 'viewer',phone VARCHAR(20) NULL,job_title VARCHAR(100) NULL,department VARCHAR(100) NULL,employee_code VARCHAR(50) NULL UNIQUE,status VARCHAR(20) NOT NULL DEFAULT 'active',must_change_password TINYINT(1) NOT NULL DEFAULT 0,last_login_at TIMESTAMP NULL,password_changed_at TIMESTAMP NULL,failed_login_attempts INT UNSIGNED NOT NULL DEFAULT 0,session_version INT UNSIGNED NOT NULL DEFAULT 1,superadmin_guard CHAR(1) NULL UNIQUE,created_by BIGINT UNSIGNED NULL,INDEX users_role_index(role),INDEX users_status_index(status),CONSTRAINT users_created_by_fk FOREIGN KEY(created_by) REFERENCES users(id) ON DELETE SET NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE password_reset_tokens (email VARCHAR(255) PRIMARY KEY,token VARCHAR(255) NOT NULL,created_at TIMESTAMP NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE sessions (id VARCHAR(255) PRIMARY KEY,user_id BIGINT UNSIGNED NULL,ip_address VARCHAR(45) NULL,user_agent TEXT NULL,payload LONGTEXT NOT NULL,last_activity INT NOT NULL,INDEX sessions_user_id_index(user_id),INDEX sessions_last_activity_index(last_activity)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE cache (`key` VARCHAR(255) PRIMARY KEY,value MEDIUMTEXT NOT NULL,expiration INT NOT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE cache_locks (`key` VARCHAR(255) PRIMARY KEY,owner VARCHAR(255) NOT NULL,expiration INT NOT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE jobs (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,queue VARCHAR(255) NOT NULL,payload LONGTEXT NOT NULL,attempts TINYINT UNSIGNED NOT NULL,reserved_at INT UNSIGNED NULL,available_at INT UNSIGNED NOT NULL,created_at INT UNSIGNED NOT NULL,INDEX jobs_queue_index(queue)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE job_batches (id VARCHAR(255) PRIMARY KEY,name VARCHAR(255) NOT NULL,total_jobs INT NOT NULL,pending_jobs INT NOT NULL,failed_jobs INT NOT NULL,failed_job_ids LONGTEXT NOT NULL,options MEDIUMTEXT NULL,cancelled_at INT NULL,created_at INT NOT NULL,finished_at INT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE failed_jobs (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,uuid VARCHAR(255) NOT NULL UNIQUE,connection TEXT NOT NULL,queue TEXT NOT NULL,payload LONGTEXT NOT NULL,exception LONGTEXT NOT NULL,failed_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE clients (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,public_id VARCHAR(36) NOT NULL UNIQUE,full_name VARCHAR(150) NOT NULL,cedula TEXT NOT NULL,cedula_lookup_hash VARCHAR(64) NOT NULL UNIQUE,email VARCHAR(254) NOT NULL,phone VARCHAR(15) NOT NULL,birth_date DATE NULL,province VARCHAR(100) NULL,municipality VARCHAR(100) NULL,address TEXT NULL,employment_status VARCHAR(80) NULL,employer_or_activity VARCHAR(150) NULL,monthly_income DECIMAL(14,2) NULL,is_active TINYINT(1) NOT NULL DEFAULT 1,created_at TIMESTAMP NULL,updated_at TIMESTAMP NULL,INDEX clients_is_active_index(is_active)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE loan_requests (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,public_id VARCHAR(26) NULL UNIQUE,client_id BIGINT UNSIGNED NULL,request_number VARCHAR(40) NOT NULL UNIQUE,submission_token VARCHAR(64) NOT NULL UNIQUE,full_name VARCHAR(150) NOT NULL,cedula TEXT NOT NULL,cedula_lookup_hash VARCHAR(64) NULL,email VARCHAR(254) NOT NULL,phone VARCHAR(15) NOT NULL,vehicle_make VARCHAR(80) NOT NULL,vehicle_model VARCHAR(80) NOT NULL,vehicle_year SMALLINT UNSIGNED NULL,vehicle_value DECIMAL(14,2) NOT NULL,requested_amount DECIMAL(14,2) NOT NULL,request_reason TEXT NOT NULL,status VARCHAR(20) NOT NULL DEFAULT 'pending',email_status VARCHAR(20) NOT NULL DEFAULT 'pending',email_attempts INT UNSIGNED NOT NULL DEFAULT 0,email_sent_at TIMESTAMP NULL,last_email_attempt_at TIMESTAMP NULL,consent_accepted_at TIMESTAMP NOT NULL,created_at TIMESTAMP NULL,updated_at TIMESTAMP NULL,INDEX lr_status(status),INDEX lr_email_status(email_status),INDEX lr_created(created_at),INDEX lr_cedula_hash(cedula_lookup_hash),CONSTRAINT lr_client_fk FOREIGN KEY(client_id) REFERENCES clients(id) ON DELETE SET NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE contact_messages (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,full_name VARCHAR(150) NOT NULL,email VARCHAR(254) NOT NULL,phone VARCHAR(15) NULL,message TEXT NULL,created_at TIMESTAMP NULL,updated_at TIMESTAMP NULL,INDEX contact_created(created_at)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE loan_request_status_history (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,loan_request_id BIGINT UNSIGNED NOT NULL,from_status VARCHAR(20) NULL,to_status VARCHAR(20) NOT NULL,changed_by BIGINT UNSIGNED NOT NULL,notes TEXT NULL,created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,CONSTRAINT lrsh_request_fk FOREIGN KEY(loan_request_id) REFERENCES loan_requests(id) ON DELETE CASCADE,CONSTRAINT lrsh_user_fk FOREIGN KEY(changed_by) REFERENCES users(id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE loan_request_notes (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,loan_request_id BIGINT UNSIGNED NOT NULL,user_id BIGINT UNSIGNED NOT NULL,note TEXT NOT NULL,created_at TIMESTAMP NULL,updated_at TIMESTAMP NULL,CONSTRAINT lrn_request_fk FOREIGN KEY(loan_request_id) REFERENCES loan_requests(id) ON DELETE CASCADE,CONSTRAINT lrn_user_fk FOREIGN KEY(user_id) REFERENCES users(id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE loans (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,public_id VARCHAR(40) NOT NULL UNIQUE,client_id BIGINT UNSIGNED NOT NULL,loan_request_id BIGINT UNSIGNED NULL,principal_amount DECIMAL(14,2) NOT NULL,interest_rate DECIMAL(8,4) NOT NULL,interest_rate_type VARCHAR(30) NOT NULL,term_count INT UNSIGNED NOT NULL,term_unit VARCHAR(20) NOT NULL,payment_frequency VARCHAR(20) NOT NULL,disbursement_date DATE NOT NULL,first_payment_date DATE NULL,fees_amount DECIMAL(14,2) NOT NULL DEFAULT 0,insurance_amount DECIMAL(14,2) NOT NULL DEFAULT 0,original_total_interest DECIMAL(14,2) NOT NULL,original_total_payable DECIMAL(14,2) NOT NULL,calculation_method VARCHAR(50) NOT NULL DEFAULT 'level_payment_reducing_balance',status VARCHAR(20) NOT NULL DEFAULT 'active',notes TEXT NULL,created_by BIGINT UNSIGNED NOT NULL,created_at TIMESTAMP NULL,updated_at TIMESTAMP NULL,INDEX loans_status(status),CONSTRAINT loans_client_fk FOREIGN KEY(client_id) REFERENCES clients(id) ON DELETE RESTRICT,CONSTRAINT loans_request_fk FOREIGN KEY(loan_request_id) REFERENCES loan_requests(id) ON DELETE SET NULL,CONSTRAINT loans_creator_fk FOREIGN KEY(created_by) REFERENCES users(id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE loan_installments (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,loan_id BIGINT UNSIGNED NOT NULL,installment_number INT UNSIGNED NOT NULL,due_date DATE NOT NULL,opening_balance DECIMAL(14,2) NOT NULL,principal_due DECIMAL(14,2) NOT NULL,interest_due DECIMAL(14,2) NOT NULL,fees_due DECIMAL(14,2) NOT NULL DEFAULT 0,insurance_due DECIMAL(14,2) NOT NULL DEFAULT 0,total_due DECIMAL(14,2) NOT NULL,closing_balance DECIMAL(14,2) NOT NULL,amount_paid DECIMAL(14,2) NOT NULL DEFAULT 0,status VARCHAR(20) NOT NULL DEFAULT 'pending',paid_at TIMESTAMP NULL,created_at TIMESTAMP NULL,updated_at TIMESTAMP NULL,UNIQUE KEY li_number(loan_id,installment_number),INDEX li_status(status),INDEX li_due_status(due_date,status),CONSTRAINT li_loan_fk FOREIGN KEY(loan_id) REFERENCES loans(id) ON DELETE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE payments (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,public_id VARCHAR(36) NOT NULL UNIQUE,loan_id BIGINT UNSIGNED NOT NULL,client_id BIGINT UNSIGNED NOT NULL,amount DECIMAL(14,2) NOT NULL,payment_date DATE NOT NULL,payment_method VARCHAR(50) NULL,reference VARCHAR(100) NULL,notes TEXT NULL,created_by BIGINT UNSIGNED NOT NULL,created_at TIMESTAMP NULL,updated_at TIMESTAMP NULL,CONSTRAINT pay_loan_fk FOREIGN KEY(loan_id) REFERENCES loans(id) ON DELETE RESTRICT,CONSTRAINT pay_client_fk FOREIGN KEY(client_id) REFERENCES clients(id) ON DELETE RESTRICT,CONSTRAINT pay_user_fk FOREIGN KEY(created_by) REFERENCES users(id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE audit_logs (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,user_id BIGINT UNSIGNED NULL,action VARCHAR(80) NOT NULL,entity_type VARCHAR(80) NULL,entity_id BIGINT UNSIGNED NULL,metadata JSON NULL,ip VARCHAR(45) NULL,created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,INDEX audit_action(action),INDEX audit_entity(entity_type,entity_id),CONSTRAINT audit_user_fk FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE SET NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE user_invitations (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,user_id BIGINT UNSIGNED NOT NULL,token_hash VARCHAR(64) NOT NULL UNIQUE,expires_at TIMESTAMP NOT NULL,used_at TIMESTAMP NULL,created_by BIGINT UNSIGNED NOT NULL,created_at TIMESTAMP NULL,updated_at TIMESTAMP NULL,CONSTRAINT ui_user_fk FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE,CONSTRAINT ui_creator_fk FOREIGN KEY(created_by) REFERENCES users(id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE panel_notifications (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,public_id VARCHAR(26) NOT NULL UNIQUE,user_id BIGINT UNSIGNED NOT NULL,type VARCHAR(80) NOT NULL,title VARCHAR(160) NOT NULL,message TEXT NOT NULL,route_name VARCHAR(120) NULL,route_params JSON NULL,read_at TIMESTAMP NULL,created_at TIMESTAMP NULL,updated_at TIMESTAMP NULL,INDEX pn_user_read(user_id,read_at),CONSTRAINT pn_user_fk FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE loan_communications (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,public_id VARCHAR(26) NOT NULL UNIQUE,loan_request_id BIGINT UNSIGNED NOT NULL,type VARCHAR(50) NOT NULL,recipient VARCHAR(254) NOT NULL,subject VARCHAR(255) NOT NULL,status VARCHAR(20) NOT NULL DEFAULT 'pending',attempts INT UNSIGNED NOT NULL DEFAULT 0,sent_at TIMESTAMP NULL,last_attempt_at TIMESTAMP NULL,failure_class TEXT NULL,created_at TIMESTAMP NULL,updated_at TIMESTAMP NULL,INDEX lc_status(status),CONSTRAINT lc_request_fk FOREIGN KEY(loan_request_id) REFERENCES loan_requests(id) ON DELETE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO migrations(migration,batch) VALUES ('0001_01_01_000000_create_users_table',1),('0001_01_01_000001_create_cache_table',1),('0001_01_01_000002_create_jobs_table',1),('2026_08_11_000001_create_loan_requests_table',1),('2026_08_11_000002_create_contact_messages_table',1),('2026_08_11_100000_create_internal_platform_tables',1),('2026_08_11_110000_backfill_loan_request_identity_hashes',1),('2026_08_11_200000_add_security_and_communications',1);
SET FOREIGN_KEY_CHECKS=1;