-- ============================================================
-- Zabibu Residence Management System
-- Database Schema + Seed Data
-- MySQL 8.0+ | UTF8MB4 | InnoDB
-- ============================================================

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+03:00";
SET NAMES utf8mb4;

CREATE DATABASE IF NOT EXISTS `zabibu_residence`
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

USE `zabibu_residence`;

-- ============================================================
-- 1. LOCATIONS
-- ============================================================

CREATE TABLE `regions` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(100) NOT NULL,
  `code` VARCHAR(10) DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE `districts` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `region_id` INT UNSIGNED NOT NULL,
  `name` VARCHAR(100) NOT NULL,
  FOREIGN KEY (`region_id`) REFERENCES `regions`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE `wards` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `district_id` INT UNSIGNED NOT NULL,
  `name` VARCHAR(100) NOT NULL,
  FOREIGN KEY (`district_id`) REFERENCES `districts`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ============================================================
-- 2. ROLES & PERMISSIONS (RBAC)
-- ============================================================

CREATE TABLE `roles` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(50) NOT NULL UNIQUE,
  `display_name` VARCHAR(100) NOT NULL,
  `description` TEXT DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE `permissions` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(100) NOT NULL UNIQUE,
  `display_name` VARCHAR(150) NOT NULL,
  `module` VARCHAR(50) NOT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE `role_permissions` (
  `role_id` INT UNSIGNED NOT NULL,
  `permission_id` INT UNSIGNED NOT NULL,
  PRIMARY KEY (`role_id`, `permission_id`),
  FOREIGN KEY (`role_id`) REFERENCES `roles`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`permission_id`) REFERENCES `permissions`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ============================================================
-- 3. USERS
-- ============================================================

CREATE TABLE `users` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `role_id` INT UNSIGNED NOT NULL,
  `first_name` VARCHAR(100) NOT NULL,
  `last_name` VARCHAR(100) NOT NULL,
  `email` VARCHAR(191) NOT NULL UNIQUE,
  `phone` VARCHAR(20) DEFAULT NULL,
  `password` VARCHAR(255) NOT NULL,
  `avatar` VARCHAR(255) DEFAULT NULL,
  `gender` ENUM('male','female','other') DEFAULT NULL,
  `date_of_birth` DATE DEFAULT NULL,
  `national_id` VARCHAR(30) DEFAULT NULL,
  `address` TEXT DEFAULT NULL,
  `region_id` INT UNSIGNED DEFAULT NULL,
  `district_id` INT UNSIGNED DEFAULT NULL,
  `ward_id` INT UNSIGNED DEFAULT NULL,
  `status` ENUM('active','inactive','suspended','pending') DEFAULT 'active',
  `email_verified_at` TIMESTAMP NULL DEFAULT NULL,
  `remember_token` VARCHAR(100) DEFAULT NULL,
  `password_reset_token` VARCHAR(100) DEFAULT NULL,
  `password_reset_expires` TIMESTAMP NULL DEFAULT NULL,
  `two_factor_secret` VARCHAR(100) DEFAULT NULL,
  `two_factor_enabled` TINYINT(1) DEFAULT 0,
  `last_login_at` TIMESTAMP NULL DEFAULT NULL,
  `last_login_ip` VARCHAR(45) DEFAULT NULL,
  `login_attempts` TINYINT UNSIGNED DEFAULT 0,
  `locked_until` TIMESTAMP NULL DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (`role_id`) REFERENCES `roles`(`id`),
  FOREIGN KEY (`region_id`) REFERENCES `regions`(`id`) ON DELETE SET NULL,
  FOREIGN KEY (`district_id`) REFERENCES `districts`(`id`) ON DELETE SET NULL,
  FOREIGN KEY (`ward_id`) REFERENCES `wards`(`id`) ON DELETE SET NULL,
  INDEX `idx_email` (`email`),
  INDEX `idx_phone` (`phone`),
  INDEX `idx_status` (`status`)
) ENGINE=InnoDB;

-- ============================================================
-- 4. PROPERTY TYPES & AMENITIES
-- ============================================================

CREATE TABLE `property_types` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(100) NOT NULL,
  `icon` VARCHAR(50) DEFAULT NULL,
  `description` TEXT DEFAULT NULL
) ENGINE=InnoDB;

CREATE TABLE `amenities` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(100) NOT NULL,
  `icon` VARCHAR(50) DEFAULT NULL,
  `category` VARCHAR(50) DEFAULT NULL
) ENGINE=InnoDB;

-- ============================================================
-- 5. PROPERTIES & BUILDINGS
-- ============================================================

CREATE TABLE `properties` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `owner_id` INT UNSIGNED DEFAULT NULL,
  `manager_id` INT UNSIGNED DEFAULT NULL,
  `property_type_id` INT UNSIGNED DEFAULT NULL,
  `property_code` VARCHAR(20) NOT NULL UNIQUE,
  `name` VARCHAR(200) NOT NULL,
  `slug` VARCHAR(200) NOT NULL UNIQUE,
  `description` TEXT DEFAULT NULL,
  `address` TEXT NOT NULL,
  `region_id` INT UNSIGNED DEFAULT NULL,
  `district_id` INT UNSIGNED DEFAULT NULL,
  `ward_id` INT UNSIGNED DEFAULT NULL,
  `area` VARCHAR(100) DEFAULT NULL,
  `latitude` DECIMAL(10,8) DEFAULT NULL,
  `longitude` DECIMAL(11,8) DEFAULT NULL,
  `total_floors` TINYINT UNSIGNED DEFAULT 1,
  `total_units` SMALLINT UNSIGNED DEFAULT 0,
  `year_built` YEAR DEFAULT NULL,
  `featured_image` VARCHAR(255) DEFAULT NULL,
  `is_featured` TINYINT(1) DEFAULT 0,
  `status` ENUM('active','inactive','maintenance','sold') DEFAULT 'active',
  `meta_title` VARCHAR(200) DEFAULT NULL,
  `meta_description` VARCHAR(300) DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (`owner_id`) REFERENCES `users`(`id`) ON DELETE SET NULL,
  FOREIGN KEY (`manager_id`) REFERENCES `users`(`id`) ON DELETE SET NULL,
  FOREIGN KEY (`property_type_id`) REFERENCES `property_types`(`id`) ON DELETE SET NULL,
  FOREIGN KEY (`region_id`) REFERENCES `regions`(`id`) ON DELETE SET NULL,
  INDEX `idx_slug` (`slug`),
  INDEX `idx_status` (`status`),
  INDEX `idx_featured` (`is_featured`)
) ENGINE=InnoDB;

CREATE TABLE `property_images` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `property_id` INT UNSIGNED NOT NULL,
  `unit_id` INT UNSIGNED DEFAULT NULL,
  `image_path` VARCHAR(255) NOT NULL,
  `caption` VARCHAR(200) DEFAULT NULL,
  `sort_order` TINYINT UNSIGNED DEFAULT 0,
  `is_primary` TINYINT(1) DEFAULT 0,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`property_id`) REFERENCES `properties`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE `property_amenities` (
  `property_id` INT UNSIGNED NOT NULL,
  `amenity_id` INT UNSIGNED NOT NULL,
  PRIMARY KEY (`property_id`,`amenity_id`),
  FOREIGN KEY (`property_id`) REFERENCES `properties`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`amenity_id`) REFERENCES `amenities`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE `property_documents` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `property_id` INT UNSIGNED NOT NULL,
  `title` VARCHAR(200) NOT NULL,
  `file_path` VARCHAR(255) NOT NULL,
  `file_type` VARCHAR(50) DEFAULT NULL,
  `uploaded_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`property_id`) REFERENCES `properties`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE `buildings` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `property_id` INT UNSIGNED NOT NULL,
  `name` VARCHAR(200) NOT NULL,
  `code` VARCHAR(20) DEFAULT NULL,
  `floors` TINYINT UNSIGNED DEFAULT 1,
  `description` TEXT DEFAULT NULL,
  `status` ENUM('active','inactive','maintenance') DEFAULT 'active',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (`property_id`) REFERENCES `properties`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE `units` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `building_id` INT UNSIGNED NOT NULL,
  `property_id` INT UNSIGNED NOT NULL,
  `unit_number` VARCHAR(20) NOT NULL,
  `unit_code` VARCHAR(30) NOT NULL UNIQUE,
  `qr_code` VARCHAR(255) DEFAULT NULL,
  `floor` TINYINT UNSIGNED DEFAULT 1,
  `unit_type` ENUM('apartment','room','studio','villa','hostel','office','shop','warehouse','other') DEFAULT 'apartment',
  `bedrooms` TINYINT UNSIGNED DEFAULT 1,
  `bathrooms` TINYINT UNSIGNED DEFAULT 1,
  `floor_size` DECIMAL(8,2) DEFAULT NULL COMMENT 'in sqm',
  `monthly_rent` DECIMAL(12,2) NOT NULL DEFAULT 0,
  `annual_rent` DECIMAL(12,2) DEFAULT NULL,
  `security_deposit` DECIMAL(12,2) DEFAULT NULL,
  `is_furnished` TINYINT(1) DEFAULT 0,
  `has_parking` TINYINT(1) DEFAULT 0,
  `has_wifi` TINYINT(1) DEFAULT 0,
  `has_ac` TINYINT(1) DEFAULT 0,
  `has_water` TINYINT(1) DEFAULT 1,
  `has_electricity` TINYINT(1) DEFAULT 1,
  `pets_allowed` TINYINT(1) DEFAULT 0,
  `description` TEXT DEFAULT NULL,
  `status` ENUM('available','occupied','maintenance','reserved','inactive') DEFAULT 'available',
  `available_from` DATE DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (`building_id`) REFERENCES `buildings`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`property_id`) REFERENCES `properties`(`id`) ON DELETE CASCADE,
  INDEX `idx_status` (`status`),
  INDEX `idx_unit_code` (`unit_code`)
) ENGINE=InnoDB;

-- ============================================================
-- 6. TENANTS
-- ============================================================

CREATE TABLE `tenants` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `user_id` INT UNSIGNED DEFAULT NULL,
  `tenant_code` VARCHAR(20) NOT NULL UNIQUE,
  `first_name` VARCHAR(100) NOT NULL,
  `last_name` VARCHAR(100) NOT NULL,
  `email` VARCHAR(191) DEFAULT NULL,
  `phone` VARCHAR(20) NOT NULL,
  `phone_alt` VARCHAR(20) DEFAULT NULL,
  `date_of_birth` DATE DEFAULT NULL,
  `gender` ENUM('male','female','other') DEFAULT NULL,
  `nationality` VARCHAR(50) DEFAULT 'Tanzanian',
  `national_id` VARCHAR(30) DEFAULT NULL,
  `passport_number` VARCHAR(30) DEFAULT NULL,
  `drivers_license` VARCHAR(30) DEFAULT NULL,
  `occupation` VARCHAR(100) DEFAULT NULL,
  `employer` VARCHAR(150) DEFAULT NULL,
  `employer_phone` VARCHAR(20) DEFAULT NULL,
  `monthly_income` DECIMAL(12,2) DEFAULT NULL,
  `emergency_contact_name` VARCHAR(150) DEFAULT NULL,
  `emergency_contact_phone` VARCHAR(20) DEFAULT NULL,
  `emergency_contact_relation` VARCHAR(50) DEFAULT NULL,
  `photo` VARCHAR(255) DEFAULT NULL,
  `id_document` VARCHAR(255) DEFAULT NULL,
  `address` TEXT DEFAULT NULL,
  `region_id` INT UNSIGNED DEFAULT NULL,
  `district_id` INT UNSIGNED DEFAULT NULL,
  `notes` TEXT DEFAULT NULL,
  `status` ENUM('active','inactive','blacklisted') DEFAULT 'active',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE SET NULL,
  FOREIGN KEY (`region_id`) REFERENCES `regions`(`id`) ON DELETE SET NULL,
  INDEX `idx_tenant_code` (`tenant_code`),
  INDEX `idx_phone` (`phone`)
) ENGINE=InnoDB;

-- ============================================================
-- 7. LEASES
-- ============================================================

CREATE TABLE `leases` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `lease_number` VARCHAR(30) NOT NULL UNIQUE,
  `tenant_id` INT UNSIGNED NOT NULL,
  `unit_id` INT UNSIGNED NOT NULL,
  `property_id` INT UNSIGNED NOT NULL,
  `start_date` DATE NOT NULL,
  `end_date` DATE NOT NULL,
  `monthly_rent` DECIMAL(12,2) NOT NULL,
  `security_deposit` DECIMAL(12,2) DEFAULT 0,
  `advance_payment` DECIMAL(12,2) DEFAULT 0,
  `payment_frequency` ENUM('monthly','quarterly','semi_annual','annual','custom') DEFAULT 'monthly',
  `payment_due_day` TINYINT UNSIGNED DEFAULT 1 COMMENT 'Day of month rent is due',
  `grace_period_days` TINYINT UNSIGNED DEFAULT 7,
  `late_fee_percent` DECIMAL(5,2) DEFAULT 0,
  `late_fee_flat` DECIMAL(10,2) DEFAULT 0,
  `vat_applicable` TINYINT(1) DEFAULT 0,
  `discount_amount` DECIMAL(10,2) DEFAULT 0,
  `utilities_included` TINYINT(1) DEFAULT 0,
  `terms_conditions` TEXT DEFAULT NULL,
  `status` ENUM('active','expired','terminated','pending','renewed') DEFAULT 'pending',
  `auto_renew` TINYINT(1) DEFAULT 0,
  `renewal_notice_days` TINYINT UNSIGNED DEFAULT 30,
  `terminated_at` TIMESTAMP NULL DEFAULT NULL,
  `termination_reason` TEXT DEFAULT NULL,
  `contract_pdf` VARCHAR(255) DEFAULT NULL,
  `signed_at` TIMESTAMP NULL DEFAULT NULL,
  `created_by` INT UNSIGNED DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (`tenant_id`) REFERENCES `tenants`(`id`),
  FOREIGN KEY (`unit_id`) REFERENCES `units`(`id`),
  FOREIGN KEY (`property_id`) REFERENCES `properties`(`id`),
  FOREIGN KEY (`created_by`) REFERENCES `users`(`id`) ON DELETE SET NULL,
  INDEX `idx_status` (`status`),
  INDEX `idx_dates` (`start_date`,`end_date`)
) ENGINE=InnoDB;

-- ============================================================
-- 8. BOOKINGS
-- ============================================================

CREATE TABLE `bookings` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `booking_ref` VARCHAR(20) NOT NULL UNIQUE,
  `unit_id` INT UNSIGNED NOT NULL,
  `property_id` INT UNSIGNED NOT NULL,
  `tenant_id` INT UNSIGNED DEFAULT NULL,
  `visitor_name` VARCHAR(200) NOT NULL,
  `visitor_email` VARCHAR(191) DEFAULT NULL,
  `visitor_phone` VARCHAR(20) NOT NULL,
  `visitor_id_type` VARCHAR(50) DEFAULT NULL,
  `visitor_id_document` VARCHAR(255) DEFAULT NULL,
  `desired_move_in` DATE DEFAULT NULL,
  `desired_lease_period` TINYINT UNSIGNED DEFAULT 12,
  `booking_fee` DECIMAL(10,2) DEFAULT 0,
  `payment_status` ENUM('unpaid','paid','refunded','partial') DEFAULT 'unpaid',
  `status` ENUM('pending','approved','rejected','cancelled','checked_in','checked_out') DEFAULT 'pending',
  `notes` TEXT DEFAULT NULL,
  `admin_notes` TEXT DEFAULT NULL,
  `processed_by` INT UNSIGNED DEFAULT NULL,
  `processed_at` TIMESTAMP NULL DEFAULT NULL,
  `check_in_at` TIMESTAMP NULL DEFAULT NULL,
  `check_out_at` TIMESTAMP NULL DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (`unit_id`) REFERENCES `units`(`id`),
  FOREIGN KEY (`property_id`) REFERENCES `properties`(`id`),
  FOREIGN KEY (`tenant_id`) REFERENCES `tenants`(`id`) ON DELETE SET NULL,
  FOREIGN KEY (`processed_by`) REFERENCES `users`(`id`) ON DELETE SET NULL,
  INDEX `idx_booking_ref` (`booking_ref`),
  INDEX `idx_status` (`status`)
) ENGINE=InnoDB;

-- ============================================================
-- 9. INVOICES & PAYMENTS
-- ============================================================

CREATE TABLE `invoices` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `invoice_number` VARCHAR(30) NOT NULL UNIQUE,
  `lease_id` INT UNSIGNED NOT NULL,
  `tenant_id` INT UNSIGNED NOT NULL,
  `unit_id` INT UNSIGNED NOT NULL,
  `period_from` DATE NOT NULL,
  `period_to` DATE NOT NULL,
  `due_date` DATE NOT NULL,
  `rent_amount` DECIMAL(12,2) NOT NULL,
  `discount` DECIMAL(10,2) DEFAULT 0,
  `vat_amount` DECIMAL(10,2) DEFAULT 0,
  `late_fee` DECIMAL(10,2) DEFAULT 0,
  `other_charges` DECIMAL(10,2) DEFAULT 0,
  `total_amount` DECIMAL(12,2) NOT NULL,
  `paid_amount` DECIMAL(12,2) DEFAULT 0,
  `balance` DECIMAL(12,2) GENERATED ALWAYS AS (`total_amount` - `paid_amount`) STORED,
  `status` ENUM('draft','sent','paid','partial','overdue','cancelled') DEFAULT 'draft',
  `notes` TEXT DEFAULT NULL,
  `created_by` INT UNSIGNED DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (`lease_id`) REFERENCES `leases`(`id`),
  FOREIGN KEY (`tenant_id`) REFERENCES `tenants`(`id`),
  FOREIGN KEY (`unit_id`) REFERENCES `units`(`id`),
  FOREIGN KEY (`created_by`) REFERENCES `users`(`id`) ON DELETE SET NULL,
  INDEX `idx_status` (`status`),
  INDEX `idx_due_date` (`due_date`)
) ENGINE=InnoDB;

CREATE TABLE `payment_methods` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(100) NOT NULL,
  `type` ENUM('mobile_money','bank_transfer','card','cash','pos','qr_code','other') NOT NULL,
  `provider` VARCHAR(100) DEFAULT NULL,
  `account_number` VARCHAR(50) DEFAULT NULL,
  `account_name` VARCHAR(100) DEFAULT NULL,
  `is_active` TINYINT(1) DEFAULT 1,
  `sort_order` TINYINT UNSIGNED DEFAULT 0,
  `icon` VARCHAR(100) DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE `payments` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `payment_ref` VARCHAR(30) NOT NULL UNIQUE,
  `invoice_id` INT UNSIGNED DEFAULT NULL,
  `lease_id` INT UNSIGNED DEFAULT NULL,
  `tenant_id` INT UNSIGNED NOT NULL,
  `unit_id` INT UNSIGNED DEFAULT NULL,
  `payment_method_id` INT UNSIGNED DEFAULT NULL,
  `amount` DECIMAL(12,2) NOT NULL,
  `currency` VARCHAR(5) DEFAULT 'TZS',
  `transaction_id` VARCHAR(100) DEFAULT NULL COMMENT 'External gateway transaction ID',
  `gateway_response` JSON DEFAULT NULL,
  `payment_date` DATE NOT NULL,
  `payment_for` ENUM('rent','deposit','booking_fee','maintenance','penalty','utility','other') DEFAULT 'rent',
  `period_from` DATE DEFAULT NULL,
  `period_to` DATE DEFAULT NULL,
  `notes` TEXT DEFAULT NULL,
  `receipt_number` VARCHAR(30) DEFAULT NULL UNIQUE,
  `status` ENUM('pending','completed','failed','refunded','cancelled') DEFAULT 'completed',
  `received_by` INT UNSIGNED DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (`invoice_id`) REFERENCES `invoices`(`id`) ON DELETE SET NULL,
  FOREIGN KEY (`lease_id`) REFERENCES `leases`(`id`) ON DELETE SET NULL,
  FOREIGN KEY (`tenant_id`) REFERENCES `tenants`(`id`),
  FOREIGN KEY (`unit_id`) REFERENCES `units`(`id`) ON DELETE SET NULL,
  FOREIGN KEY (`payment_method_id`) REFERENCES `payment_methods`(`id`) ON DELETE SET NULL,
  FOREIGN KEY (`received_by`) REFERENCES `users`(`id`) ON DELETE SET NULL,
  INDEX `idx_payment_ref` (`payment_ref`),
  INDEX `idx_tenant` (`tenant_id`),
  INDEX `idx_date` (`payment_date`)
) ENGINE=InnoDB;

CREATE TABLE `expenses` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `property_id` INT UNSIGNED DEFAULT NULL,
  `unit_id` INT UNSIGNED DEFAULT NULL,
  `category` VARCHAR(100) NOT NULL,
  `description` TEXT NOT NULL,
  `amount` DECIMAL(12,2) NOT NULL,
  `expense_date` DATE NOT NULL,
  `receipt` VARCHAR(255) DEFAULT NULL,
  `vendor` VARCHAR(150) DEFAULT NULL,
  `approved_by` INT UNSIGNED DEFAULT NULL,
  `status` ENUM('pending','approved','rejected') DEFAULT 'approved',
  `created_by` INT UNSIGNED DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (`property_id`) REFERENCES `properties`(`id`) ON DELETE SET NULL,
  FOREIGN KEY (`unit_id`) REFERENCES `units`(`id`) ON DELETE SET NULL,
  FOREIGN KEY (`created_by`) REFERENCES `users`(`id`) ON DELETE SET NULL,
  FOREIGN KEY (`approved_by`) REFERENCES `users`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB;

-- ============================================================
-- 10. MAINTENANCE
-- ============================================================

CREATE TABLE `maintenance_requests` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `request_number` VARCHAR(20) NOT NULL UNIQUE,
  `unit_id` INT UNSIGNED NOT NULL,
  `property_id` INT UNSIGNED NOT NULL,
  `tenant_id` INT UNSIGNED DEFAULT NULL,
  `title` VARCHAR(200) NOT NULL,
  `description` TEXT NOT NULL,
  `category` VARCHAR(100) DEFAULT NULL,
  `priority` ENUM('emergency','high','medium','low') DEFAULT 'medium',
  `status` ENUM('open','in_progress','on_hold','completed','cancelled') DEFAULT 'open',
  `assigned_to` INT UNSIGNED DEFAULT NULL,
  `technician_name` VARCHAR(150) DEFAULT NULL,
  `estimated_cost` DECIMAL(10,2) DEFAULT 0,
  `actual_cost` DECIMAL(10,2) DEFAULT 0,
  `materials_cost` DECIMAL(10,2) DEFAULT 0,
  `started_at` TIMESTAMP NULL DEFAULT NULL,
  `completed_at` TIMESTAMP NULL DEFAULT NULL,
  `notes` TEXT DEFAULT NULL,
  `images` JSON DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (`unit_id`) REFERENCES `units`(`id`),
  FOREIGN KEY (`property_id`) REFERENCES `properties`(`id`),
  FOREIGN KEY (`tenant_id`) REFERENCES `tenants`(`id`) ON DELETE SET NULL,
  FOREIGN KEY (`assigned_to`) REFERENCES `users`(`id`) ON DELETE SET NULL,
  INDEX `idx_status` (`status`),
  INDEX `idx_priority` (`priority`)
) ENGINE=InnoDB;

-- ============================================================
-- 11. NOTIFICATIONS & LOGS
-- ============================================================

CREATE TABLE `notifications` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `user_id` INT UNSIGNED DEFAULT NULL,
  `tenant_id` INT UNSIGNED DEFAULT NULL,
  `type` ENUM('email','sms','whatsapp','push','in_app') NOT NULL,
  `subject` VARCHAR(200) DEFAULT NULL,
  `message` TEXT NOT NULL,
  `status` ENUM('pending','sent','failed','read') DEFAULT 'pending',
  `sent_at` TIMESTAMP NULL DEFAULT NULL,
  `read_at` TIMESTAMP NULL DEFAULT NULL,
  `reference_type` VARCHAR(50) DEFAULT NULL,
  `reference_id` INT UNSIGNED DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE SET NULL,
  FOREIGN KEY (`tenant_id`) REFERENCES `tenants`(`id`) ON DELETE SET NULL,
  INDEX `idx_user` (`user_id`),
  INDEX `idx_status` (`status`)
) ENGINE=InnoDB;

CREATE TABLE `sms_logs` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `phone` VARCHAR(20) NOT NULL,
  `message` TEXT NOT NULL,
  `gateway` VARCHAR(50) DEFAULT NULL,
  `message_id` VARCHAR(100) DEFAULT NULL,
  `status` ENUM('pending','sent','delivered','failed') DEFAULT 'pending',
  `cost` DECIMAL(8,4) DEFAULT 0,
  `sent_at` TIMESTAMP NULL DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE `email_logs` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `to_email` VARCHAR(191) NOT NULL,
  `to_name` VARCHAR(150) DEFAULT NULL,
  `subject` VARCHAR(300) NOT NULL,
  `body` LONGTEXT DEFAULT NULL,
  `status` ENUM('pending','sent','failed','bounced') DEFAULT 'pending',
  `message_id` VARCHAR(200) DEFAULT NULL,
  `error` TEXT DEFAULT NULL,
  `sent_at` TIMESTAMP NULL DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE `audit_logs` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `user_id` INT UNSIGNED DEFAULT NULL,
  `action` VARCHAR(100) NOT NULL,
  `model` VARCHAR(100) DEFAULT NULL,
  `model_id` INT UNSIGNED DEFAULT NULL,
  `old_values` JSON DEFAULT NULL,
  `new_values` JSON DEFAULT NULL,
  `ip_address` VARCHAR(45) DEFAULT NULL,
  `user_agent` TEXT DEFAULT NULL,
  `url` VARCHAR(500) DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX `idx_user` (`user_id`),
  INDEX `idx_model` (`model`,`model_id`)
) ENGINE=InnoDB;

CREATE TABLE `login_logs` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `user_id` INT UNSIGNED DEFAULT NULL,
  `email` VARCHAR(191) DEFAULT NULL,
  `ip_address` VARCHAR(45) DEFAULT NULL,
  `user_agent` TEXT DEFAULT NULL,
  `status` ENUM('success','failed','blocked') NOT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX `idx_user` (`user_id`),
  INDEX `idx_ip` (`ip_address`)
) ENGINE=InnoDB;

-- ============================================================
-- 12. SETTINGS & SYSTEM
-- ============================================================

CREATE TABLE `settings` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `key` VARCHAR(100) NOT NULL UNIQUE,
  `value` TEXT DEFAULT NULL,
  `group` VARCHAR(50) DEFAULT 'general',
  `type` VARCHAR(20) DEFAULT 'text',
  `label` VARCHAR(150) DEFAULT NULL,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE `news` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `title` VARCHAR(300) NOT NULL,
  `slug` VARCHAR(300) NOT NULL UNIQUE,
  `excerpt` TEXT DEFAULT NULL,
  `body` LONGTEXT DEFAULT NULL,
  `image` VARCHAR(255) DEFAULT NULL,
  `category` VARCHAR(100) DEFAULT NULL,
  `author_id` INT UNSIGNED DEFAULT NULL,
  `is_published` TINYINT(1) DEFAULT 1,
  `views` INT UNSIGNED DEFAULT 0,
  `published_at` TIMESTAMP NULL DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (`author_id`) REFERENCES `users`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE `testimonials` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(150) NOT NULL,
  `title` VARCHAR(100) DEFAULT NULL,
  `avatar` VARCHAR(255) DEFAULT NULL,
  `message` TEXT NOT NULL,
  `rating` TINYINT UNSIGNED DEFAULT 5,
  `is_active` TINYINT(1) DEFAULT 1,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE `faqs` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `question` TEXT NOT NULL,
  `answer` TEXT NOT NULL,
  `category` VARCHAR(100) DEFAULT 'general',
  `sort_order` TINYINT UNSIGNED DEFAULT 0,
  `is_active` TINYINT(1) DEFAULT 1,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE `newsletter_subscribers` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `email` VARCHAR(191) NOT NULL UNIQUE,
  `name` VARCHAR(150) DEFAULT NULL,
  `status` ENUM('active','unsubscribed') DEFAULT 'active',
  `subscribed_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE `api_tokens` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `user_id` INT UNSIGNED NOT NULL,
  `token` VARCHAR(500) NOT NULL,
  `refresh_token` VARCHAR(500) DEFAULT NULL,
  `expires_at` TIMESTAMP NULL DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
  INDEX `idx_user` (`user_id`)
) ENGINE=InnoDB;

-- ============================================================
-- TRIGGERS
-- ============================================================

DELIMITER $$

CREATE TRIGGER `after_payment_insert`
AFTER INSERT ON `payments`
FOR EACH ROW
BEGIN
  IF NEW.invoice_id IS NOT NULL THEN
    UPDATE `invoices`
    SET `paid_amount` = `paid_amount` + NEW.amount,
        `status` = CASE
          WHEN (`paid_amount` + NEW.amount) >= `total_amount` THEN 'paid'
          WHEN (`paid_amount` + NEW.amount) > 0 THEN 'partial'
          ELSE `status`
        END,
        `updated_at` = NOW()
    WHERE `id` = NEW.invoice_id;
  END IF;
END$$

CREATE TRIGGER `after_lease_activate`
AFTER UPDATE ON `leases`
FOR EACH ROW
BEGIN
  IF NEW.status = 'active' AND OLD.status != 'active' THEN
    UPDATE `units` SET `status` = 'occupied', `updated_at` = NOW()
    WHERE `id` = NEW.unit_id;
  END IF;
  IF (NEW.status = 'expired' OR NEW.status = 'terminated') AND OLD.status = 'active' THEN
    UPDATE `units` SET `status` = 'available', `updated_at` = NOW()
    WHERE `id` = NEW.unit_id;
  END IF;
END$$

DELIMITER ;

-- ============================================================
-- SEED DATA
-- ============================================================

-- Regions (Tanzania)
INSERT INTO `regions` (`id`,`name`,`code`) VALUES
(1,'Dar es Salaam','DSM'),(2,'Arusha','ARU'),(3,'Mwanza','MWZ'),
(4,'Dodoma','DOD'),(5,'Mbeya','MBY'),(6,'Morogoro','MOR'),
(7,'Tanga','TAN'),(8,'Zanzibar North','ZNB'),(9,'Zanzibar South','ZNS'),
(10,'Kilimanjaro','KIL'),(11,'Iringa','IRI'),(12,'Moshi','MSH');

-- Districts
INSERT INTO `districts` (`region_id`,`name`) VALUES
(1,'Ilala'),(1,'Kinondoni'),(1,'Temeke'),(1,'Ubungo'),(1,'Kigamboni'),
(2,'Arusha City'),(2,'Meru'),(2,'Monduli'),
(3,'Nyamagana'),(3,'Ilemela'),
(4,'Dodoma City'),(4,'Bahi'),
(5,'Mbeya City'),(5,'Rungwe'),
(6,'Morogoro Urban'),(6,'Kilosa'),
(7,'Tanga City'),(7,'Pangani'),
(10,'Moshi Urban'),(10,'Moshi Rural');

-- Wards (Dar es Salaam sample)
INSERT INTO `wards` (`district_id`,`name`) VALUES
(1,'Kariakoo'),(1,'Gerezani'),(1,'Kinondoni'),(2,'Mwananyamala'),
(2,'Sinza'),(2,'Kijitonyama'),(2,'Msasani'),(3,'Temeke'),(3,'Mbagala'),
(4,'Ubungo'),(4,'Makuburi'),(4,'Kimara');

-- Roles
INSERT INTO `roles` (`id`,`name`,`display_name`,`description`) VALUES
(1,'super_admin','Super Administrator','Full system access'),
(2,'property_owner','Property Owner','Manages own properties'),
(3,'property_manager','Property Manager','Manages buildings and operations'),
(4,'accountant','Accountant','Manages payments and reports'),
(5,'receptionist','Receptionist','Walk-in bookings and check-in/out'),
(6,'tenant','Tenant','Tenant portal access'),
(7,'visitor','Visitor','Public website access');

-- Permissions
INSERT INTO `permissions` (`name`,`display_name`,`module`) VALUES
('properties.view','View Properties','property'),
('properties.create','Create Properties','property'),
('properties.edit','Edit Properties','property'),
('properties.delete','Delete Properties','property'),
('tenants.view','View Tenants','tenant'),
('tenants.create','Create Tenants','tenant'),
('tenants.edit','Edit Tenants','tenant'),
('tenants.delete','Delete Tenants','tenant'),
('payments.view','View Payments','payment'),
('payments.create','Create Payments','payment'),
('payments.delete','Delete Payments','payment'),
('reports.view','View Reports','report'),
('reports.export','Export Reports','report'),
('users.manage','Manage Users','admin'),
('settings.manage','Manage Settings','admin'),
('bookings.manage','Manage Bookings','booking'),
('maintenance.manage','Manage Maintenance','maintenance');

-- Role Permissions (super_admin gets all)
INSERT INTO `role_permissions` (`role_id`,`permission_id`)
SELECT 1, id FROM `permissions`;

-- Property Owner permissions
INSERT INTO `role_permissions` (`role_id`,`permission_id`)
SELECT 2, id FROM `permissions` WHERE `module` IN ('property','tenant','payment','report','booking');

-- Property Manager permissions
INSERT INTO `role_permissions` (`role_id`,`permission_id`)
SELECT 3, id FROM `permissions` WHERE `module` IN ('property','tenant','maintenance','booking');

-- Accountant permissions
INSERT INTO `role_permissions` (`role_id`,`permission_id`)
SELECT 4, id FROM `permissions` WHERE `module` IN ('payment','report');

-- Property Types
INSERT INTO `property_types` (`name`,`icon`,`description`) VALUES
('Apartment','bi-building','Multi-unit residential apartment'),
('House','bi-house','Standalone residential house'),
('Villa','bi-house-heart','Luxury villa'),
('Studio','bi-camera','Single-room studio unit'),
('Hostel','bi-people','Shared dormitory style hostel'),
('Office','bi-briefcase','Commercial office space'),
('Shop','bi-shop','Commercial retail shop'),
('Warehouse','bi-box-seam','Industrial warehouse');

-- Amenities
INSERT INTO `amenities` (`name`,`icon`,`category`) VALUES
('WiFi Internet','bi-wifi','connectivity'),
('Air Conditioning','bi-snow','comfort'),
('Swimming Pool','bi-water','recreation'),
('Gym / Fitness Center','bi-activity','recreation'),
('Security Guard','bi-shield-check','security'),
('CCTV Cameras','bi-camera-video','security'),
('Electric Fence','bi-lightning','security'),
('Backup Generator','bi-battery-charging','utilities'),
('Borehole Water','bi-droplet','utilities'),
('Solar Power','bi-sun','utilities'),
('Parking Space','bi-car-front','transport'),
('Garden','bi-tree','outdoor'),
('Balcony','bi-window','features'),
('Elevator/Lift','bi-arrow-up-square','features'),
('Laundry Room','bi-bootstrap','features'),
('Kids Playground','bi-balloon','recreation'),
('Satellite TV','bi-tv','entertainment'),
('24hr Security','bi-clock','security'),
('Pets Friendly','bi-heart','lifestyle'),
('Furnished','bi-lamp','features');

-- Payment Methods
INSERT INTO `payment_methods` (`name`,`type`,`provider`,`is_active`,`sort_order`) VALUES
('M-Pesa Tanzania','mobile_money','Vodacom',1,1),
('Airtel Money','mobile_money','Airtel',1,2),
('Tigo Pesa','mobile_money','MIC Tanzania',1,3),
('HaloPesa','mobile_money','Halotel',1,4),
('Mixx by Yas','mobile_money','Yas',1,5),
('CRDB Bank Transfer','bank_transfer','CRDB Bank',1,6),
('NMB Bank Transfer','bank_transfer','NMB Bank',1,7),
('NBC Bank Transfer','bank_transfer','NBC Bank',1,8),
('Exim Bank Transfer','bank_transfer','Exim Bank',1,9),
('Cash Payment','cash',NULL,1,10),
('POS/Card Payment','pos',NULL,1,11),
('QR Code Payment','qr_code','Selcom',1,12),
('Selcom','card','Selcom Pay',1,13),
('AzamPay','card','AzamPay',1,14);

-- Users (passwords: Admin@1234 = hashed)
INSERT INTO `users` (`role_id`,`first_name`,`last_name`,`email`,`phone`,`password`,`gender`,`status`,`email_verified_at`) VALUES
(1,'Super','Admin','admin@zabiburesidence.co.tz','+255700000001','$2y$12$LKIpUjLNlT0MXb2q7F8KaO5ZbGVQ8P6NGrTqBkVFXc2IWPdGD3UDi','male','active',NOW()),
(2,'Amina','Hassan','owner@zabiburesidence.co.tz','+255700000002','$2y$12$LKIpUjLNlT0MXb2q7F8KaO5ZbGVQ8P6NGrTqBkVFXc2IWPdGD3UDi','female','active',NOW()),
(3,'Juma','Kilonzo','manager@zabiburesidence.co.tz','+255700000003','$2y$12$LKIpUjLNlT0MXb2q7F8KaO5ZbGVQ8P6NGrTqBkVFXc2IWPdGD3UDi','male','active',NOW()),
(4,'Fatuma','Mwangi','accountant@zabiburesidence.co.tz','+255700000004','$2y$12$LKIpUjLNlT0MXb2q7F8KaO5ZbGVQ8P6NGrTqBkVFXc2IWPdGD3UDi','female','active',NOW()),
(5,'Peter','Makundi','receptionist@zabiburesidence.co.tz','+255700000005','$2y$12$LKIpUjLNlT0MXb2q7F8KaO5ZbGVQ8P6NGrTqBkVFXc2IWPdGD3UDi','male','active',NOW()),
(6,'John','Mwamba','tenant1@zabiburesidence.co.tz','+255700000006','$2y$12$LKIpUjLNlT0MXb2q7F8KaO5ZbGVQ8P6NGrTqBkVFXc2IWPdGD3UDi','male','active',NOW()),
(6,'Grace','Odhiambo','tenant2@zabiburesidence.co.tz','+255700000007','$2y$12$LKIpUjLNlT0MXb2q7F8KaO5ZbGVQ8P6NGrTqBkVFXc2IWPdGD3UDi','female','active',NOW());

-- Properties
INSERT INTO `properties` (`owner_id`,`manager_id`,`property_type_id`,`property_code`,`name`,`slug`,`description`,`address`,`region_id`,`district_id`,`ward_id`,`area`,`latitude`,`longitude`,`total_floors`,`total_units`,`year_built`,`is_featured`,`status`) VALUES
(2,3,1,'ZBR-001','Zabibu Gardens Apartment','zabibu-gardens-apartment','A modern luxury apartment complex in the heart of Dar es Salaam with stunning city views, 24-hour security, and world-class amenities.','Plot 45, Msasani Peninsula, Kinondoni',1,2,7,'Msasani',-6.7714,39.2551,8,48,2020,1,'active'),
(2,3,2,'ZBR-002','Zabibu Executive Villas','zabibu-executive-villas','Exclusive executive villas with private gardens, swimming pools, and garage. Perfect for families seeking luxury and privacy.','Masaki Area, Plot 12, Kinondoni',1,2,7,'Masaki',-6.7604,39.2598,2,6,2019,1,'active'),
(2,3,1,'ZBR-003','Zabibu City Apartments','zabibu-city-apartments','Affordable modern apartments in Kariakoo commercial district. Ideal for young professionals.','Lindi Street, Kariakoo, Ilala',1,1,1,'Kariakoo',-6.8174,39.2808,5,30,2021,0,'active'),
(2,3,4,'ZBR-004','Zabibu Studio Suites','zabibu-studio-suites','Contemporary studio apartments with full amenities near Mlimani City. Ideal for singles and students.','Sam Nujoma Road, Ubungo',1,4,10,'Ubungo',-6.7802,39.2185,4,20,2022,1,'active'),
(2,3,5,'ZBR-005','Zabibu Student Hostel','zabibu-student-hostel','Budget-friendly hostel for university students with shared facilities and high-speed WiFi.','University Road, Ubungo',1,4,10,'UDSM Area',-6.7719,39.2125,3,60,2018,0,'active');

-- Property Amenities
INSERT INTO `property_amenities` (`property_id`,`amenity_id`) VALUES
(1,1),(1,2),(1,3),(1,4),(1,5),(1,6),(1,8),(1,9),(1,11),(1,13),(1,14),(1,17),
(2,1),(2,2),(2,3),(2,5),(2,6),(2,7),(2,11),(2,12),(2,14),
(3,1),(3,5),(3,6),(3,11),(3,17),
(4,1),(4,2),(4,5),(4,17),(4,11),
(5,1),(5,5),(5,6),(5,17),(5,19);

-- Buildings
INSERT INTO `buildings` (`property_id`,`name`,`code`,`floors`,`status`) VALUES
(1,'Block A','BLK-A',8,'active'),
(1,'Block B','BLK-B',8,'active'),
(2,'Villa Block','VBL',2,'active'),
(3,'Main Building','MAIN',5,'active'),
(4,'Studio Tower','STW',4,'active'),
(5,'Hostel Building','HST',3,'active');

-- Units (sample)
INSERT INTO `units` (`building_id`,`property_id`,`unit_number`,`unit_code`,`floor`,`unit_type`,`bedrooms`,`bathrooms`,`floor_size`,`monthly_rent`,`annual_rent`,`security_deposit`,`is_furnished`,`has_parking`,`has_wifi`,`has_ac`,`has_water`,`has_electricity`,`status`) VALUES
(1,1,'A101','ZBR-001-A101',1,'apartment',2,2,85.00,800000,9600000,1600000,1,1,1,1,1,1,'occupied'),
(1,1,'A102','ZBR-001-A102',1,'apartment',2,2,85.00,800000,9600000,1600000,1,1,1,1,1,1,'available'),
(1,1,'A201','ZBR-001-A201',2,'apartment',3,2,110.00,1200000,14400000,2400000,1,1,1,1,1,1,'occupied'),
(1,1,'A202','ZBR-001-A202',2,'apartment',3,2,110.00,1200000,14400000,2400000,1,1,1,1,1,1,'available'),
(2,1,'B101','ZBR-001-B101',1,'apartment',1,1,55.00,600000,7200000,1200000,1,0,1,1,1,1,'available'),
(2,1,'B201','ZBR-001-B201',2,'apartment',2,1,70.00,700000,8400000,1400000,1,1,1,1,1,1,'occupied'),
(3,2,'V001','ZBR-002-V001',1,'villa',4,3,250.00,3500000,42000000,7000000,1,1,1,1,1,1,'occupied'),
(3,2,'V002','ZBR-002-V002',1,'villa',4,3,250.00,3500000,42000000,7000000,1,1,1,1,1,1,'available'),
(3,2,'V003','ZBR-002-V003',1,'villa',3,2,200.00,2800000,33600000,5600000,1,1,1,1,1,1,'available'),
(4,3,'M101','ZBR-003-M101',1,'apartment',2,1,65.00,450000,5400000,900000,0,0,1,0,1,1,'occupied'),
(4,3,'M102','ZBR-003-M102',1,'apartment',2,1,65.00,450000,5400000,900000,0,0,1,0,1,1,'available'),
(4,3,'M201','ZBR-003-M201',2,'apartment',3,2,90.00,650000,7800000,1300000,0,1,1,0,1,1,'available'),
(5,4,'ST101','ZBR-004-ST101',1,'studio',1,1,35.00,350000,4200000,700000,1,0,1,1,1,1,'occupied'),
(5,4,'ST102','ZBR-004-ST102',1,'studio',1,1,35.00,350000,4200000,700000,1,0,1,1,1,1,'available'),
(5,4,'ST201','ZBR-004-ST201',2,'studio',1,1,40.00,400000,4800000,800000,1,0,1,1,1,1,'available'),
(6,5,'H101','ZBR-005-H101',1,'room',1,0,15.00,150000,1800000,300000,0,0,1,0,1,1,'occupied'),
(6,5,'H102','ZBR-005-H102',1,'room',1,0,15.00,150000,1800000,300000,0,0,1,0,1,1,'occupied'),
(6,5,'H201','ZBR-005-H201',2,'room',1,0,15.00,150000,1800000,300000,0,0,1,0,1,1,'available');

-- Tenants
INSERT INTO `tenants` (`user_id`,`tenant_code`,`first_name`,`last_name`,`email`,`phone`,`date_of_birth`,`gender`,`nationality`,`national_id`,`occupation`,`employer`,`monthly_income`,`emergency_contact_name`,`emergency_contact_phone`,`emergency_contact_relation`,`address`,`region_id`,`status`) VALUES
(6,'TNT-001','John','Mwamba','tenant1@zabiburesidence.co.tz','+255700000006','1988-05-15','male','Tanzanian','19880515-12345-00001','Software Engineer','Vodacom Tanzania',4500000,'Mary Mwamba','+255700111001','Wife','Plot 12, Kinondoni',1,'active'),
(7,'TNT-002','Grace','Odhiambo','tenant2@zabiburesidence.co.tz','+255700000007','1992-09-22','female','Kenyan','A12345678','Teacher','Aga Khan School',3000000,'Daniel Odhiambo','+255700111002','Husband','Msasani, Kinondoni',1,'active'),
(NULL,'TNT-003','Mohammed','Rashid',NULL,'+255700000008','1985-03-10','male','Tanzanian','19850310-12345-00003','Businessman','Self Employed',8000000,'Fatuma Rashid','+255700111003','Wife','Kariakoo, Ilala',1,'active'),
(NULL,'TNT-004','Sylvia','Kimaro',NULL,'+255700000009','1995-07-18','female','Tanzanian','19950718-12345-00004','University Student','UDSM',NULL,'George Kimaro','+255700111004','Father','Ubungo, DSM',1,'active'),
(NULL,'TNT-005','Hassan','Abdullah',NULL,'+255700000010','1979-11-30','male','Tanzanian','19791130-12345-00005','Civil Servant','Government of Tanzania',5500000,'Aisha Abdullah','+255700111005','Wife','Temeke, DSM',1,'active');

-- Leases
INSERT INTO `leases` (`lease_number`,`tenant_id`,`unit_id`,`property_id`,`start_date`,`end_date`,`monthly_rent`,`security_deposit`,`advance_payment`,`payment_frequency`,`payment_due_day`,`grace_period_days`,`late_fee_percent`,`vat_applicable`,`status`,`created_by`) VALUES
('LSE-2024-001',1,1,1,'2024-01-01','2024-12-31',800000,1600000,800000,'monthly',1,7,5.00,0,'active',1),
('LSE-2024-002',2,3,1,'2024-03-01','2025-02-28',1200000,2400000,1200000,'monthly',1,7,5.00,0,'active',1),
('LSE-2024-003',3,7,2,'2024-02-01','2025-01-31',3500000,7000000,3500000,'monthly',1,7,5.00,0,'active',1),
('LSE-2024-004',4,13,4,'2024-04-01','2025-03-31',350000,700000,350000,'monthly',5,5,0.00,0,'active',1),
('LSE-2024-005',5,16,5,'2024-01-15','2024-07-14',150000,300000,150000,'monthly',15,7,0.00,0,'expired',1);

-- Invoices (sample)
INSERT INTO `invoices` (`invoice_number`,`lease_id`,`tenant_id`,`unit_id`,`period_from`,`period_to`,`due_date`,`rent_amount`,`discount`,`vat_amount`,`late_fee`,`other_charges`,`total_amount`,`paid_amount`,`status`,`created_by`) VALUES
('INV-2024-001',1,1,1,'2024-06-01','2024-06-30','2024-06-01',800000,0,0,0,0,800000,800000,'paid',1),
('INV-2024-002',1,1,1,'2024-07-01','2024-07-31','2024-07-01',800000,0,0,0,0,800000,800000,'paid',1),
('INV-2024-003',2,2,3,'2024-06-01','2024-06-30','2024-06-01',1200000,0,0,0,0,1200000,1200000,'paid',1),
('INV-2024-004',3,3,7,'2024-06-01','2024-06-30','2024-06-01',3500000,0,0,0,0,3500000,3500000,'paid',1),
('INV-2024-005',1,1,1,'2024-08-01','2024-08-31','2024-08-01',800000,0,0,0,0,800000,0,'overdue',1),
('INV-2024-006',4,4,13,'2024-06-05','2024-07-04','2024-06-05',350000,0,0,0,0,350000,350000,'paid',1);

-- Payments
INSERT INTO `payments` (`payment_ref`,`invoice_id`,`lease_id`,`tenant_id`,`unit_id`,`payment_method_id`,`amount`,`currency`,`transaction_id`,`payment_date`,`payment_for`,`period_from`,`period_to`,`receipt_number`,`status`,`received_by`) VALUES
('PAY-2024-001',1,1,1,1,1,800000,'TZS','MP123456789','2024-06-01','rent','2024-06-01','2024-06-30','RCP-2024-001','completed',5),
('PAY-2024-002',2,1,1,1,1,800000,'TZS','MP123456790','2024-07-01','rent','2024-07-01','2024-07-31','RCP-2024-002','completed',5),
('PAY-2024-003',3,2,2,3,2,1200000,'TZS','AM987654321','2024-06-01','rent','2024-06-01','2024-06-30','RCP-2024-003','completed',5),
('PAY-2024-004',4,3,3,7,10,3500000,'TZS',NULL,'2024-06-01','rent','2024-06-01','2024-06-30','RCP-2024-004','completed',5),
('PAY-2024-005',6,4,4,13,3,350000,'TZS','TP111222333','2024-06-05','rent','2024-06-05','2024-07-04','RCP-2024-005','completed',5);

-- Bookings
INSERT INTO `bookings` (`booking_ref`,`unit_id`,`property_id`,`visitor_name`,`visitor_email`,`visitor_phone`,`desired_move_in`,`desired_lease_period`,`booking_fee`,`payment_status`,`status`,`notes`) VALUES
('BKG-2024-001',2,1,'Ahmed Salim','ahmed@email.com','+255712345678','2024-09-01',12,50000,'paid','approved','Interested in 2BR apartment'),
('BKG-2024-002',8,2,'Claire Mushi','claire@email.com','+255722345678','2024-10-01',6,100000,'paid','pending','Looking for executive villa for family'),
('BKG-2024-003',11,3,'Patrick Ngowi','patrick@email.com','+255732345678','2024-09-15',12,30000,'unpaid','pending','Budget apartment needed'),
('BKG-2024-004',14,4,'Lucy Mramba','lucy@email.com','+255742345678','2024-08-01',6,20000,'paid','approved','Studio near university');

-- Maintenance Requests
INSERT INTO `maintenance_requests` (`request_number`,`unit_id`,`property_id`,`tenant_id`,`title`,`description`,`category`,`priority`,`status`,`technician_name`,`estimated_cost`,`actual_cost`) VALUES
('MNT-2024-001',1,1,1,'Water Leak in Bathroom','There is a water leak under the sink in the main bathroom. Water is dripping constantly.','Plumbing','high','completed','Juma Fundi',50000,45000),
('MNT-2024-002',3,1,2,'Air Conditioner Not Working','The bedroom AC unit stopped working. Makes a strange noise when turned on.','HVAC','medium','in_progress','Ahmed Bomba',150000,0),
('MNT-2024-003',7,2,3,'Broken Gate Remote','The remote control for the gate is not working. Cannot access parking.','Electrical','low','open',NULL,20000,0),
('MNT-2024-004',13,4,4,'WiFi Not Working','Internet connection is very slow and keeps disconnecting.','IT/Network','medium','completed','ICT Team',0,0),
('MNT-2024-005',1,1,1,'Power Socket Sparking','One of the power sockets in the living room is sparking when plugged.','Electrical','emergency','in_progress','Hamisi Electric',80000,0);

-- Settings
INSERT INTO `settings` (`key`,`value`,`group`,`type`,`label`) VALUES
('company_name','Zabibu Residence','general','text','Company Name'),
('company_tagline','Your Home, Our Priority','general','text','Tagline'),
('company_email','info@zabiburesidence.co.tz','general','email','Company Email'),
('company_phone','+255 22 200 0001','general','text','Company Phone'),
('company_phone2','+255 784 000 001','general','text','Company Phone 2'),
('company_address','Msasani Peninsula, Kinondoni, Dar es Salaam, Tanzania','general','text','Company Address'),
('company_website','https://www.zabiburesidence.co.tz','general','url','Website URL'),
('currency','TZS','billing','text','Currency Code'),
('currency_symbol','TZS','billing','text','Currency Symbol'),
('vat_rate','18','billing','number','VAT Rate (%)'),
('invoice_prefix','INV','billing','text','Invoice Prefix'),
('receipt_prefix','RCP','billing','text','Receipt Prefix'),
('late_fee_days','7','billing','number','Late Fee Grace Days'),
('timezone','Africa/Dar_es_Salaam','general','text','Timezone'),
('date_format','d/m/Y','general','text','Date Format'),
('meta_title','Zabibu Residence - Premium Rental Management Tanzania','seo','text','Default Meta Title'),
('meta_description','Find and manage premium rental properties in Tanzania. Apartments, villas, studios and more.','seo','text','Meta Description'),
('facebook_url','https://facebook.com/zabiburesidence','social','url','Facebook URL'),
('twitter_url','https://twitter.com/zabiburesidence','social','url','Twitter URL'),
('instagram_url','https://instagram.com/zabiburesidence','social','url','Instagram URL'),
('whatsapp_number','+255784000001','social','text','WhatsApp Number'),
('google_maps_key','','integrations','text','Google Maps API Key'),
('smtp_host','smtp.gmail.com','email','text','SMTP Host'),
('smtp_port','587','email','number','SMTP Port'),
('smtp_username','','email','email','SMTP Username'),
('smtp_password','','email','password','SMTP Password');

-- Testimonials
INSERT INTO `testimonials` (`name`,`title`,`message`,`rating`,`is_active`) VALUES
('Amina Saleh','Tenant - Msasani','I have been living in Zabibu Gardens for 2 years now. The management is excellent, maintenance requests are handled quickly, and the facilities are world class.',5,1),
('John Kimani','Property Owner','Zabibu Residence has managed my properties since 2022. Collections are on time, reports are detailed, and the system is very easy to use.',5,1),
('Grace Mushi','Tenant - Masaki','The booking process was so easy. I booked online, paid via M-Pesa, and moved in within a week. Highly recommended!',5,1),
('David Ngowi','Business Owner','Rented office space through Zabibu. Professional team, fair pricing, and excellent support.',4,1),
('Fatuma Hassan','Tenant - Kariakoo','Very affordable apartments with great security. I feel safe here. The online portal to pay rent via mobile money is very convenient.',5,1);

-- FAQs
INSERT INTO `faqs` (`question`,`answer`,`category`,`sort_order`,`is_active`) VALUES
('How do I book a property?','You can book a property online through our website. Search for available properties, select your preferred unit, fill in the booking form, and pay the booking fee via mobile money or bank transfer.',  'booking',1,1),
('What payment methods are accepted?','We accept M-Pesa, Airtel Money, Tigo Pesa, HaloPesa, bank transfer (CRDB, NMB, NBC), cash, and card payments.',  'payment',2,1),
('How much is the security deposit?','Security deposit is typically equal to one to two months rent depending on the property. It is fully refundable at the end of tenancy subject to property condition.',  'payment',3,1),
('Can I pay rent online?','Yes! Tenants can log into the tenant portal and pay rent via M-Pesa push, Airtel Money, Tigo Pesa, or bank transfer directly from the website.',  'payment',4,1),
('How do I report a maintenance issue?','Log into your tenant portal, click on Maintenance, describe your issue, set the priority, and submit. Our team will respond within 24 hours for normal requests and immediately for emergencies.',  'maintenance',5,1),
('Are the apartments furnished?','Some of our apartments are fully furnished while others are unfurnished. You can filter by furnished/unfurnished in the search page.',  'general',6,1),
('Do you allow pets?','Pet policy varies by property. Some properties allow small pets with an additional deposit. Check the property listing or contact us for details.',  'general',7,1),
('How long is the minimum lease?','The minimum lease period is 6 months for most units. Some properties offer monthly rolling contracts.',  'leases',8,1);

-- News
INSERT INTO `news` (`title`,`slug`,`excerpt`,`body`,`category`,`author_id`,`is_published`,`published_at`) VALUES
('New Luxury Apartments Now Available in Msasani','new-luxury-apartments-msasani','Zabibu Residence is proud to announce the launch of brand new 2 and 3 bedroom luxury apartments in the prestigious Msasani Peninsula area.','<p>Zabibu Residence is proud to announce the launch of brand new 2 and 3 bedroom luxury apartments in the prestigious Msasani Peninsula area. These units feature premium finishes, fully equipped kitchens, balconies with sea views, and access to all premium amenities including swimming pool, gym, and 24-hour security.</p>','announcements',1,1,NOW()),
('Tips for First-Time Renters in Tanzania','tips-first-time-renters-tanzania','Renting a home for the first time can be overwhelming. Here are our top tips for first-time renters in Tanzania to help you find your perfect home.','<p>Renting a home for the first time can be an exciting but overwhelming experience. Here are our top tips to help you navigate the rental market in Tanzania successfully.</p><p><strong>1. Set a realistic budget</strong> - Your rent should not exceed 30% of your monthly income.</p><p><strong>2. Inspect before you sign</strong> - Always visit the property before signing any lease agreement.</p>','tips',1,1,NOW()),
('Pay Rent Easily with M-Pesa and Airtel Money','pay-rent-mpesa-airtel-money','Zabibu Residence now supports seamless rent payment via M-Pesa Tanzania and Airtel Money. Simply log into your tenant portal and pay with one click.','<p>We are pleased to announce that Zabibu Residence now supports full mobile money integration for rent payments. Tenants can now pay their monthly rent using M-Pesa, Airtel Money, Tigo Pesa, and HaloPesa directly from the tenant portal without leaving home.</p>','news',1,1,NOW());

COMMIT;
