CREATE DATABASE IF NOT EXISTS pavapa_eagreement CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE pavapa_eagreement;

CREATE TABLE users (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 name VARCHAR(160) NOT NULL,
 email VARCHAR(190) NULL UNIQUE,
 phone VARCHAR(40) NULL,
 password_hash VARCHAR(255) NOT NULL,
 role ENUM('admin','agent') NOT NULL DEFAULT 'agent',
 status ENUM('active','inactive') NOT NULL DEFAULT 'active',
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE customers (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 full_name VARCHAR(160) NOT NULL,
 phone VARCHAR(40) NOT NULL,
 email VARCHAR(190) NULL,
 national_id VARCHAR(100) NULL,
 language VARCHAR(10) NOT NULL DEFAULT 'en',
 status ENUM('active','blocked') NOT NULL DEFAULT 'active',
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 INDEX(phone), INDEX(email)
) ENGINE=InnoDB;

CREATE TABLE properties (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 title VARCHAR(220) NOT NULL,
 property_type VARCHAR(80) NOT NULL DEFAULT 'House',
 address VARCHAR(255) NULL,
 city VARCHAR(100) NOT NULL DEFAULT 'Kigali',
 description TEXT NULL,
 price DECIMAL(15,2) NULL,
 status ENUM('available','rented','sold','inactive') NOT NULL DEFAULT 'available',
 created_by BIGINT UNSIGNED NULL,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 FOREIGN KEY(created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE agreements (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 agreement_number VARCHAR(40) NOT NULL UNIQUE,
 public_token CHAR(64) NOT NULL UNIQUE,
 agent_id BIGINT UNSIGNED NOT NULL,
 customer_id BIGINT UNSIGNED NOT NULL,
 property_id BIGINT UNSIGNED NOT NULL,
 title VARCHAR(220) NOT NULL,
 terms LONGTEXT NOT NULL,
 fee DECIMAL(15,2) NOT NULL DEFAULT 0,
 currency VARCHAR(10) NOT NULL DEFAULT 'RWF',
 visit_date DATE NOT NULL,
 visit_time TIME NULL,
 meeting_location VARCHAR(255) NULL,
 expires_at DATETIME NOT NULL,
 status ENUM('draft','sent','opened','otp_pending','accepted','visit_started','visit_completed','payment_pending','paid','disputed','cancelled','expired') NOT NULL DEFAULT 'draft',
 accepted_at DATETIME NULL,
 accepted_ip VARCHAR(45) NULL,
 accepted_user_agent TEXT NULL,
 accepted_version_id BIGINT UNSIGNED NULL,
 accepted_hash CHAR(64) NULL,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 FOREIGN KEY(agent_id) REFERENCES users(id),
 FOREIGN KEY(customer_id) REFERENCES customers(id),
 FOREIGN KEY(property_id) REFERENCES properties(id),
 INDEX(status), INDEX(customer_id), INDEX(agent_id)
) ENGINE=InnoDB;

CREATE TABLE agreement_versions (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 agreement_id BIGINT UNSIGNED NOT NULL,
 version_no INT NOT NULL,
 title VARCHAR(220) NOT NULL,
 terms LONGTEXT NOT NULL,
 fee DECIMAL(15,2) NOT NULL,
 visit_date DATE NOT NULL,
 visit_time TIME NULL,
 meeting_location VARCHAR(255) NULL,
 content_hash CHAR(64) NOT NULL,
 created_by BIGINT UNSIGNED NULL,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 FOREIGN KEY(agreement_id) REFERENCES agreements(id) ON DELETE CASCADE,
 FOREIGN KEY(created_by) REFERENCES users(id) ON DELETE SET NULL,
 UNIQUE(agreement_id,version_no)
) ENGINE=InnoDB;

ALTER TABLE agreements ADD CONSTRAINT fk_accepted_version FOREIGN KEY(accepted_version_id) REFERENCES agreement_versions(id) ON DELETE SET NULL;

CREATE TABLE otp_verifications (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 agreement_id BIGINT UNSIGNED NOT NULL,
 channel ENUM('phone','email') NOT NULL,
 destination VARCHAR(190) NOT NULL,
 code_hash CHAR(64) NOT NULL,
 attempts INT NOT NULL DEFAULT 0,
 expires_at DATETIME NOT NULL,
 verified_at DATETIME NULL,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 FOREIGN KEY(agreement_id) REFERENCES agreements(id) ON DELETE CASCADE,
 INDEX(agreement_id,channel)
) ENGINE=InnoDB;

CREATE TABLE agreement_acceptances (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 agreement_id BIGINT UNSIGNED NOT NULL,
 version_id BIGINT UNSIGNED NOT NULL,
 phone_verified TINYINT(1) NOT NULL DEFAULT 0,
 email_verified TINYINT(1) NOT NULL DEFAULT 0,
 accepted_at DATETIME NOT NULL,
 ip_address VARCHAR(45) NULL,
 user_agent TEXT NULL,
 document_hash CHAR(64) NOT NULL,
 FOREIGN KEY(agreement_id) REFERENCES agreements(id) ON DELETE CASCADE,
 FOREIGN KEY(version_id) REFERENCES agreement_versions(id),
 UNIQUE(agreement_id)
) ENGINE=InnoDB;

CREATE TABLE visits (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 agreement_id BIGINT UNSIGNED NOT NULL UNIQUE,
 scheduled_at DATETIME NULL,
 arrived_at DATETIME NULL,
 started_at DATETIME NULL,
 completed_at DATETIME NULL,
 customer_confirmed TINYINT(1) NOT NULL DEFAULT 0,
 customer_confirmed_at DATETIME NULL,
 notes TEXT NULL,
 FOREIGN KEY(agreement_id) REFERENCES agreements(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE payments (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 agreement_id BIGINT UNSIGNED NOT NULL,
 amount DECIMAL(15,2) NOT NULL,
 currency VARCHAR(10) NOT NULL DEFAULT 'RWF',
 method VARCHAR(50) NULL,
 transaction_reference VARCHAR(120) NULL,
 status ENUM('pending','paid','failed','refunded') NOT NULL DEFAULT 'pending',
 paid_at DATETIME NULL,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 FOREIGN KEY(agreement_id) REFERENCES agreements(id) ON DELETE CASCADE,
 INDEX(agreement_id,status)
) ENGINE=InnoDB;

CREATE TABLE disputes (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 agreement_id BIGINT UNSIGNED NOT NULL,
 opened_by ENUM('customer','agent','admin') NOT NULL,
 reason VARCHAR(120) NOT NULL,
 details TEXT NULL,
 status ENUM('open','under_review','resolved','rejected') NOT NULL DEFAULT 'open',
 resolution TEXT NULL,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 resolved_at DATETIME NULL,
 FOREIGN KEY(agreement_id) REFERENCES agreements(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE notifications (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 user_id BIGINT UNSIGNED NULL,
 customer_id BIGINT UNSIGNED NULL,
 type VARCHAR(80) NOT NULL,
 title VARCHAR(220) NOT NULL,
 message TEXT NOT NULL,
 read_at DATETIME NULL,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE,
 FOREIGN KEY(customer_id) REFERENCES customers(id) ON DELETE CASCADE,
 INDEX(read_at)
) ENGINE=InnoDB;

CREATE TABLE audit_logs (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 agreement_id BIGINT UNSIGNED NULL,
 actor_type ENUM('admin','agent','customer','system') NOT NULL,
 actor_id BIGINT UNSIGNED NULL,
 action VARCHAR(120) NOT NULL,
 details JSON NULL,
 ip_address VARCHAR(45) NULL,
 user_agent TEXT NULL,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 FOREIGN KEY(agreement_id) REFERENCES agreements(id) ON DELETE SET NULL,
 INDEX(agreement_id), INDEX(action), INDEX(created_at)
) ENGINE=InnoDB;

CREATE TABLE settings (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 setting_key VARCHAR(120) NOT NULL UNIQUE,
 setting_value LONGTEXT NULL,
 updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

INSERT INTO users(name,email,phone,password_hash,role) VALUES ('System Administrator','admin@pavapa.local','+250780000000', '$2y$12$NTgu9w81utuGNxlkqS.gje6J9uogWHzW6A3uij9S52g5PD03FXkXm', 'admin');
-- V1 default password: password (change immediately after first login)
INSERT INTO settings(setting_key,setting_value) VALUES
('company_name','Pavapa Real Estate'),
('company_phone','+250 780 000 000'),
('company_email','support@pavapa.local'),
('default_language','en');