-- ============================================================
-- ZIPO ADMIN SYSTEM - Complete Database
-- ============================================================
-- This file creates the database and ALL tables for the ZIPO
-- Property & Utility Management System.
--
-- HOW TO USE:
--   ✅ LOCAL phpMyAdmin:  Keep lines 1 & 2 (CREATE DATABASE + USE) UNCOMMENTED.
--   ✅ NAMECHEAP cPanel:  Your database is ALREADY created by cPanel.
--                         REMOVE (or comment out) the CREATE DATABASE & USE lines.
--                         Then import this file directly into your existing DB.
--
-- Default admin login: admin@zipo.com / password123
-- ============================================================

-- ------------------------------------------------------------------
-- CREATE DATABASE (UNCOMMENT ONLY FOR LOCAL USE)
-- ------------------------------------------------------------------


-- Disable FK checks so DROP TABLE works regardless of existing table order.
-- This prevents errors like "#3730 Cannot drop table referenced by FK constraint".
SET FOREIGN_KEY_CHECKS = 0;
SET UNIQUE_CHECKS = 0;

-- ------------------------------------------------------------------
-- MIGRATIONS TABLE (tracks which migrations have run)
-- ------------------------------------------------------------------
DROP TABLE IF EXISTS `migrations`;
CREATE TABLE `migrations` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `migration` VARCHAR(255) NOT NULL,
  `batch` INT NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------------
-- USERS
-- ------------------------------------------------------------------
DROP TABLE IF EXISTS `users`;
CREATE TABLE `users` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(255) NOT NULL,
  `full_name` VARCHAR(255) NULL DEFAULT NULL,
  `email` VARCHAR(255) NOT NULL,
  `phone` VARCHAR(255) NULL DEFAULT NULL,
  `role` VARCHAR(255) NOT NULL DEFAULT 'tenant',
  `account_number` VARCHAR(255) NULL DEFAULT NULL,
  `company` VARCHAR(255) NULL DEFAULT NULL,
  `address` VARCHAR(255) NULL DEFAULT NULL,
  `property_id` BIGINT UNSIGNED NULL DEFAULT NULL,
  `is_active` TINYINT(1) NOT NULL DEFAULT '1',
  `status` VARCHAR(255) NOT NULL DEFAULT 'active',
  `photo_url` VARCHAR(255) NULL DEFAULT NULL,
  `last_login_at` TIMESTAMP NULL DEFAULT NULL,
  `email_verified_at` TIMESTAMP NULL DEFAULT NULL,
  `password` VARCHAR(255) NOT NULL,
  `remember_token` VARCHAR(100) NULL DEFAULT NULL,
  `created_at` TIMESTAMP NULL DEFAULT NULL,
  `updated_at` TIMESTAMP NULL DEFAULT NULL,
PRIMARY KEY (`id`),
  UNIQUE KEY `users_email_unique` (`email`),
  UNIQUE KEY `users_account_number_unique` (`account_number`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------------
-- PASSWORD RESET TOKENS
-- ------------------------------------------------------------------
DROP TABLE IF EXISTS `password_reset_tokens`;
CREATE TABLE `password_reset_tokens` (
  `email` VARCHAR(255) NOT NULL,
  `token` VARCHAR(255) NOT NULL,
  `created_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------------
-- SESSIONS
-- ------------------------------------------------------------------
DROP TABLE IF EXISTS `sessions`;
CREATE TABLE `sessions` (
  `id` VARCHAR(255) NOT NULL,
  `user_id` BIGINT UNSIGNED NULL DEFAULT NULL,
  `ip_address` VARCHAR(45) NULL DEFAULT NULL,
  `user_agent` TEXT NULL,
  `payload` LONGTEXT NOT NULL,
  `last_activity` INT NOT NULL,
  PRIMARY KEY (`id`),
  KEY `sessions_user_id_index` (`user_id`),
  KEY `sessions_last_activity_index` (`last_activity`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------------
-- CACHE
-- ------------------------------------------------------------------
DROP TABLE IF EXISTS `cache`;
CREATE TABLE `cache` (
  `key` VARCHAR(255) NOT NULL,
  `value` MEDIUMTEXT NOT NULL,
  `expiration` BIGINT NOT NULL,
  PRIMARY KEY (`key`),
  KEY `cache_expiration_index` (`expiration`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------------
-- CACHE LOCKS
-- ------------------------------------------------------------------
DROP TABLE IF EXISTS `cache_locks`;
CREATE TABLE `cache_locks` (
  `key` VARCHAR(255) NOT NULL,
  `owner` VARCHAR(255) NOT NULL,
  `expiration` BIGINT NOT NULL,
  PRIMARY KEY (`key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------------
-- JOBS
-- ------------------------------------------------------------------
DROP TABLE IF EXISTS `jobs`;
CREATE TABLE `jobs` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `queue` VARCHAR(255) NOT NULL,
  `payload` LONGTEXT NOT NULL,
  `attempts` SMALLINT UNSIGNED NOT NULL,
  `reserved_at` INT UNSIGNED NULL DEFAULT NULL,
  `available_at` INT UNSIGNED NOT NULL,
  `created_at` INT UNSIGNED NOT NULL,
  PRIMARY KEY (`id`),
  KEY `jobs_queue_index` (`queue`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------------
-- JOB BATCHES
-- ------------------------------------------------------------------
DROP TABLE IF EXISTS `job_batches`;
CREATE TABLE `job_batches` (
  `id` VARCHAR(255) NOT NULL,
  `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 DEFAULT NULL,
  `created_at` INT NOT NULL,
  `finished_at` INT NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------------
-- FAILED JOBS
-- ------------------------------------------------------------------
DROP TABLE IF EXISTS `failed_jobs`;
CREATE TABLE `failed_jobs` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `uuid` VARCHAR(255) NOT NULL,
  `connection` TEXT NOT NULL,
  `queue` TEXT NOT NULL,
  `payload` LONGTEXT NOT NULL,
  `exception` LONGTEXT NOT NULL,
  `failed_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `failed_jobs_uuid_unique` (`uuid`),
  KEY `failed_jobs_connection_queue_failed_at_index` (`connection`(255), `queue`(255), `failed_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------------
-- PROPERTIES
-- ------------------------------------------------------------------
DROP TABLE IF EXISTS `properties`;
CREATE TABLE `properties` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `landlord_id` BIGINT UNSIGNED NULL DEFAULT NULL,
  `user_id` BIGINT UNSIGNED NOT NULL,
  `name` VARCHAR(255) NOT NULL,
  `address` TEXT NULL,
  `description` TEXT NULL,
  `city` VARCHAR(255) NULL DEFAULT NULL,
  `total_units` INT NOT NULL DEFAULT '0',
  `occupied_units` INT NOT NULL DEFAULT '0',
  `status` VARCHAR(255) NOT NULL DEFAULT 'active',
  `created_at` TIMESTAMP NULL DEFAULT NULL,
  `updated_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `properties_user_id_foreign` (`user_id`),
KEY `properties_landlord_id_foreign` (`landlord_id`),
  CONSTRAINT `properties_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `properties_landlord_id_foreign` FOREIGN KEY (`landlord_id`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Add the users.property_id foreign key now that `properties` exists
-- (resolves the users <-> properties circular dependency)
ALTER TABLE `users`
  ADD KEY `users_property_id_foreign` (`property_id`),
  ADD CONSTRAINT `users_property_id_foreign` FOREIGN KEY (`property_id`) REFERENCES `properties` (`id`) ON DELETE SET NULL;

-- ------------------------------------------------------------------
-- UNITS
-- ------------------------------------------------------------------
DROP TABLE IF EXISTS `units`;
CREATE TABLE `units` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `property_id` BIGINT UNSIGNED NOT NULL,
  `tenant_id` BIGINT UNSIGNED NULL DEFAULT NULL,
  `user_id` BIGINT UNSIGNED NULL DEFAULT NULL,
  `unit_number` VARCHAR(255) NOT NULL,
  `name` VARCHAR(255) NULL DEFAULT NULL,
  `description` TEXT NULL,
  `rent_amount` DECIMAL(12,2) NOT NULL DEFAULT '0.00',
  `monthly_rent` DECIMAL(12,2) NOT NULL DEFAULT '0.00',
  `status` ENUM('occupied','vacant','maintenance') NOT NULL DEFAULT 'vacant',
  `created_at` TIMESTAMP NULL DEFAULT NULL,
  `updated_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `units_property_id_foreign` (`property_id`),
  KEY `units_user_id_foreign` (`user_id`),
  KEY `units_tenant_id_foreign` (`tenant_id`),
  CONSTRAINT `units_property_id_foreign` FOREIGN KEY (`property_id`) REFERENCES `properties` (`id`) ON DELETE CASCADE,
  CONSTRAINT `units_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL,
  CONSTRAINT `units_tenant_id_foreign` FOREIGN KEY (`tenant_id`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------------
-- METERS
-- ------------------------------------------------------------------
DROP TABLE IF EXISTS `meters`;
CREATE TABLE `meters` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `unit_id` BIGINT UNSIGNED NULL DEFAULT NULL,
  `user_id` BIGINT UNSIGNED NULL DEFAULT NULL,
  `meter_number` VARCHAR(255) NOT NULL,
  `meter_type` VARCHAR(255) NOT NULL DEFAULT 'submeter',
  `type` VARCHAR(255) NOT NULL DEFAULT 'submeter',
  `location` VARCHAR(255) NULL DEFAULT NULL,
  `status` VARCHAR(255) NOT NULL DEFAULT 'active',
  `balance` DECIMAL(12,2) NOT NULL DEFAULT '0.00',
  `voltage` DECIMAL(10,2) NOT NULL DEFAULT '0.00',
  `current` DECIMAL(10,2) NOT NULL DEFAULT '0.00',
  `power` DECIMAL(10,2) NOT NULL DEFAULT '0.00',
  `relay_status` INT NOT NULL DEFAULT '1',
  `battery` INT NOT NULL DEFAULT '100',
  `battery_level` INT NOT NULL DEFAULT '100',
  `last_reading_kwh` DECIMAL(12,2) NOT NULL DEFAULT '0.00',
  `last_reading_at` TIMESTAMP NULL DEFAULT NULL,
  `created_at` TIMESTAMP NULL DEFAULT NULL,
  `updated_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `meters_meter_number_unique` (`meter_number`),
  KEY `meters_unit_id_foreign` (`unit_id`),
  KEY `meters_user_id_foreign` (`user_id`),
  CONSTRAINT `meters_unit_id_foreign` FOREIGN KEY (`unit_id`) REFERENCES `units` (`id`) ON DELETE SET NULL,
  CONSTRAINT `meters_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------------
-- METER READINGS
-- ------------------------------------------------------------------
DROP TABLE IF EXISTS `meter_readings`;
CREATE TABLE `meter_readings` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `meter_id` BIGINT UNSIGNED NOT NULL,
  `voltage` DECIMAL(10,2) NOT NULL DEFAULT '0.00',
  `current` DECIMAL(10,2) NOT NULL DEFAULT '0.00',
  `power` DECIMAL(10,2) NOT NULL DEFAULT '0.00',
  `energy` DECIMAL(10,2) NOT NULL DEFAULT '0.00',
  `balance` DECIMAL(12,2) NOT NULL DEFAULT '0.00',
  `relay_on` TINYINT(1) NOT NULL DEFAULT '1',
  `read_at` TIMESTAMP NOT NULL,
  `created_at` TIMESTAMP NULL DEFAULT NULL,
  `updated_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `meter_readings_meter_id_foreign` (`meter_id`),
  CONSTRAINT `meter_readings_meter_id_foreign` FOREIGN KEY (`meter_id`) REFERENCES `meters` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------------
-- RENT SCHEDULES
-- ------------------------------------------------------------------
DROP TABLE IF EXISTS `rent_schedules`;
CREATE TABLE `rent_schedules` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `unit_id` BIGINT UNSIGNED NOT NULL,
  `tenant_id` BIGINT UNSIGNED NOT NULL,
  `landlord_id` BIGINT UNSIGNED NOT NULL,
  `property_id` BIGINT UNSIGNED NOT NULL,
  `monthly_rent` DECIMAL(12,2) NOT NULL,
  `due_day` TINYINT NOT NULL DEFAULT '1',
  `grace_period_days` TINYINT NOT NULL DEFAULT '5',
  `late_fee_amount` DECIMAL(12,2) NOT NULL DEFAULT '0.00',
  `lease_start_date` DATE NOT NULL,
  `lease_end_date` DATE NULL DEFAULT NULL,
  `status` ENUM('active','ended','suspended') NOT NULL DEFAULT 'active',
  `includes_water` TINYINT(1) NOT NULL DEFAULT '0',
  `includes_electricity` TINYINT(1) NOT NULL DEFAULT '0',
  `water_amount` DECIMAL(12,2) NOT NULL DEFAULT '0.00',
  `electricity_amount` DECIMAL(12,2) NOT NULL DEFAULT '0.00',
  `notes` TEXT NULL,
  `created_at` TIMESTAMP NULL DEFAULT NULL,
  `updated_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `rent_schedules_tenant_id_status_index` (`tenant_id`, `status`),
  KEY `rent_schedules_landlord_id_status_index` (`landlord_id`, `status`),
  KEY `rent_schedules_unit_id_status_index` (`unit_id`, `status`),
  KEY `rent_schedules_unit_id_foreign` (`unit_id`),
  KEY `rent_schedules_landlord_id_foreign` (`landlord_id`),
  KEY `rent_schedules_property_id_foreign` (`property_id`),
  CONSTRAINT `rent_schedules_unit_id_foreign` FOREIGN KEY (`unit_id`) REFERENCES `units` (`id`) ON DELETE CASCADE,
  CONSTRAINT `rent_schedules_tenant_id_foreign` FOREIGN KEY (`tenant_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `rent_schedules_landlord_id_foreign` FOREIGN KEY (`landlord_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `rent_schedules_property_id_foreign` FOREIGN KEY (`property_id`) REFERENCES `properties` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------------
-- RENT INVOICES
-- ------------------------------------------------------------------
DROP TABLE IF EXISTS `rent_invoices`;
CREATE TABLE `rent_invoices` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `rent_schedule_id` BIGINT UNSIGNED NOT NULL,
  `unit_id` BIGINT UNSIGNED NOT NULL,
  `tenant_id` BIGINT UNSIGNED NOT NULL,
  `landlord_id` BIGINT UNSIGNED NOT NULL,
  `property_id` BIGINT UNSIGNED NOT NULL,
  `invoice_number` VARCHAR(255) NOT NULL,
  `period_month` VARCHAR(255) NOT NULL,
  `due_date` DATE NOT NULL,
  `rent_amount` DECIMAL(12,2) NOT NULL,
  `late_fee` DECIMAL(12,2) NOT NULL DEFAULT '0.00',
  `water_amount` DECIMAL(12,2) NOT NULL DEFAULT '0.00',
  `electricity_amount` DECIMAL(12,2) NOT NULL DEFAULT '0.00',
  `other_charges` DECIMAL(12,2) NOT NULL DEFAULT '0.00',
  `total_amount` DECIMAL(12,2) NOT NULL,
  `other_charges_notes` TEXT NULL,
  `amount_paid` DECIMAL(12,2) NOT NULL DEFAULT '0.00',
  `balance_due` DECIMAL(12,2) NOT NULL,
  `status` ENUM('unpaid','partial','paid','overdue','waived') NOT NULL DEFAULT 'unpaid',
  `paid_at` TIMESTAMP NULL DEFAULT NULL,
  `late_fee_applied` TINYINT(1) NOT NULL DEFAULT '0',
  `late_fee_applied_at` TIMESTAMP NULL DEFAULT NULL,
  `notes` TEXT NULL,
  `created_at` TIMESTAMP NULL DEFAULT NULL,
  `updated_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `rent_invoices_invoice_number_unique` (`invoice_number`),
  UNIQUE KEY `unique_invoice_per_period` (`rent_schedule_id`, `period_month`),
  KEY `rent_invoices_tenant_id_status_index` (`tenant_id`, `status`),
  KEY `rent_invoices_landlord_id_period_month_index` (`landlord_id`, `period_month`),
  KEY `rent_invoices_status_due_date_index` (`status`, `due_date`),
  KEY `rent_invoices_unit_id_foreign` (`unit_id`),
  KEY `rent_invoices_landlord_id_foreign` (`landlord_id`),
  KEY `rent_invoices_property_id_foreign` (`property_id`),
  CONSTRAINT `rent_invoices_rent_schedule_id_foreign` FOREIGN KEY (`rent_schedule_id`) REFERENCES `rent_schedules` (`id`) ON DELETE CASCADE,
  CONSTRAINT `rent_invoices_unit_id_foreign` FOREIGN KEY (`unit_id`) REFERENCES `units` (`id`) ON DELETE CASCADE,
  CONSTRAINT `rent_invoices_tenant_id_foreign` FOREIGN KEY (`tenant_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `rent_invoices_landlord_id_foreign` FOREIGN KEY (`landlord_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `rent_invoices_property_id_foreign` FOREIGN KEY (`property_id`) REFERENCES `properties` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------------
-- TRANSACTIONS
-- ------------------------------------------------------------------
DROP TABLE IF EXISTS `transactions`;
CREATE TABLE `transactions` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` BIGINT UNSIGNED NULL DEFAULT NULL,
  `transaction_number` VARCHAR(255) NOT NULL,
  `reference` VARCHAR(255) NULL DEFAULT NULL,
  `from_user_id` BIGINT UNSIGNED NULL DEFAULT NULL,
  `from_name` VARCHAR(255) NULL DEFAULT NULL,
  `to_user_id` BIGINT UNSIGNED NULL DEFAULT NULL,
  `to_name` VARCHAR(255) NULL DEFAULT NULL,
  `meter_id` BIGINT UNSIGNED NULL DEFAULT NULL,
  `rent_invoice_id` BIGINT UNSIGNED NULL DEFAULT NULL,
  `type` VARCHAR(255) NOT NULL,
  `purpose` VARCHAR(255) NULL DEFAULT NULL,
  `amount` DECIMAL(12,2) NOT NULL,
  `tax_amount` DECIMAL(12,2) NOT NULL DEFAULT '0.00',
  `tax_percentage` DECIMAL(5,2) NOT NULL DEFAULT '0.00',
  `net_amount` DECIMAL(12,2) NOT NULL DEFAULT '0.00',
  `provider` VARCHAR(255) NULL DEFAULT NULL,
  `phone_number` VARCHAR(255) NULL DEFAULT NULL,
  `status` VARCHAR(255) NOT NULL DEFAULT 'completed',
  `notes` TEXT NULL,
  `transaction_date` TIMESTAMP NULL DEFAULT NULL,
  `created_at` TIMESTAMP NULL DEFAULT NULL,
  `updated_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `transactions_transaction_number_unique` (`transaction_number`),
  KEY `transactions_from_user_id_foreign` (`from_user_id`),
  KEY `transactions_to_user_id_foreign` (`to_user_id`),
  KEY `transactions_meter_id_foreign` (`meter_id`),
  KEY `transactions_rent_invoice_id_foreign` (`rent_invoice_id`),
  KEY `transactions_user_id_foreign` (`user_id`),
  CONSTRAINT `transactions_from_user_id_foreign` FOREIGN KEY (`from_user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL,
  CONSTRAINT `transactions_to_user_id_foreign` FOREIGN KEY (`to_user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL,
  CONSTRAINT `transactions_meter_id_foreign` FOREIGN KEY (`meter_id`) REFERENCES `meters` (`id`) ON DELETE SET NULL,
  CONSTRAINT `transactions_rent_invoice_id_foreign` FOREIGN KEY (`rent_invoice_id`) REFERENCES `rent_invoices` (`id`) ON DELETE SET NULL,
  CONSTRAINT `transactions_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------------
-- ALERTS
-- ------------------------------------------------------------------
DROP TABLE IF EXISTS `alerts`;
CREATE TABLE `alerts` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` BIGINT UNSIGNED NOT NULL,
  `type` VARCHAR(255) NOT NULL,
  `message` TEXT NOT NULL,
  `priority` VARCHAR(255) NOT NULL DEFAULT 'medium',
  `is_read` TINYINT(1) NOT NULL DEFAULT '0',
  `alert_date` TIMESTAMP NULL DEFAULT NULL,
  `created_at` TIMESTAMP NULL DEFAULT NULL,
  `updated_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `alerts_user_id_foreign` (`user_id`),
  CONSTRAINT `alerts_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------------
-- WALLETS
-- ------------------------------------------------------------------
DROP TABLE IF EXISTS `wallets`;
CREATE TABLE `wallets` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` BIGINT UNSIGNED NOT NULL,
  `balance` DECIMAL(14,2) NOT NULL DEFAULT '0.00',
  `total_collected` DECIMAL(14,2) NOT NULL DEFAULT '0.00',
  `total_tax_deducted` DECIMAL(14,2) NOT NULL DEFAULT '0.00',
  `total_spent_on_uedcl` DECIMAL(14,2) NOT NULL DEFAULT '0.00',
  `momo_number` VARCHAR(255) NULL DEFAULT NULL,
  `created_at` TIMESTAMP NULL DEFAULT NULL,
  `updated_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `wallets_user_id_foreign` (`user_id`),
  CONSTRAINT `wallets_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------------
-- SUPPORT TICKETS
-- ------------------------------------------------------------------
DROP TABLE IF EXISTS `support_tickets`;
CREATE TABLE `support_tickets` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `ticket_number` VARCHAR(255) NOT NULL,
  `user_id` BIGINT UNSIGNED NOT NULL,
  `subject` VARCHAR(255) NOT NULL,
  `description` TEXT NULL,
  `priority` VARCHAR(255) NOT NULL DEFAULT 'medium',
  `status` VARCHAR(255) NOT NULL DEFAULT 'open',
  `created_at` TIMESTAMP NULL DEFAULT NULL,
  `updated_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `support_tickets_ticket_number_unique` (`ticket_number`),
  KEY `support_tickets_user_id_foreign` (`user_id`),
  CONSTRAINT `support_tickets_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------------
-- SYSTEM CONFIGS
-- ------------------------------------------------------------------
DROP TABLE IF EXISTS `system_configs`;
CREATE TABLE `system_configs` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `key` VARCHAR(255) NOT NULL,
  `value` TEXT NOT NULL,
  `created_at` TIMESTAMP NULL DEFAULT NULL,
  `updated_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `system_configs_key_unique` (`key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------------
-- DISCONNECTION LOGS
-- ------------------------------------------------------------------
DROP TABLE IF EXISTS `disconnection_logs`;
CREATE TABLE `disconnection_logs` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` BIGINT UNSIGNED NULL DEFAULT NULL,
  `tenant_id` BIGINT UNSIGNED NULL DEFAULT NULL,
  `unit_id` BIGINT UNSIGNED NULL DEFAULT NULL,
  `action_by` BIGINT UNSIGNED NULL DEFAULT NULL,
  `reason` VARCHAR(255) NULL DEFAULT NULL,
  `status` VARCHAR(255) NOT NULL DEFAULT 'active',
  `disconnected_at` TIMESTAMP NULL DEFAULT NULL,
  `reconnected_at` TIMESTAMP NULL DEFAULT NULL,
  `created_at` TIMESTAMP NULL DEFAULT NULL,
  `updated_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `disconnection_logs_user_id_foreign` (`user_id`),
  KEY `disconnection_logs_tenant_id_foreign` (`tenant_id`),
  KEY `disconnection_logs_unit_id_foreign` (`unit_id`),
  KEY `disconnection_logs_action_by_foreign` (`action_by`),
  CONSTRAINT `disconnection_logs_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL,
  CONSTRAINT `disconnection_logs_tenant_id_foreign` FOREIGN KEY (`tenant_id`) REFERENCES `users` (`id`) ON DELETE SET NULL,
  CONSTRAINT `disconnection_logs_unit_id_foreign` FOREIGN KEY (`unit_id`) REFERENCES `units` (`id`) ON DELETE SET NULL,
  CONSTRAINT `disconnection_logs_action_by_foreign` FOREIGN KEY (`action_by`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------------
-- PERSONAL ACCESS TOKENS (Laravel Sanctum)
-- ------------------------------------------------------------------
DROP TABLE IF EXISTS `personal_access_tokens`;
CREATE TABLE `personal_access_tokens` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `tokenable_type` VARCHAR(255) NOT NULL,
  `tokenable_id` BIGINT UNSIGNED NOT NULL,
  `name` VARCHAR(255) NOT NULL,
  `token` VARCHAR(64) NOT NULL,
  `abilities` TEXT NULL,
  `last_used_at` TIMESTAMP NULL DEFAULT NULL,
  `expires_at` TIMESTAMP NULL DEFAULT NULL,
  `created_at` TIMESTAMP NULL DEFAULT NULL,
  `updated_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `personal_access_tokens_token_unique` (`token`),
  KEY `personal_access_tokens_tokenable_type_tokenable_id_index` (`tokenable_type`, `tokenable_id`),
  KEY `personal_access_tokens_expires_at_index` (`expires_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

-- ------------------------------------------------------------------
-- SYSTEM CONFIG DEFAULTS
-- ------------------------------------------------------------------
INSERT INTO `system_configs` (`key`, `value`, `created_at`, `updated_at`) VALUES
('tax_percentage', '2.0', NOW(), NOW()),
('unit_price_per_kwh', '120', NOW(), NOW()),
('landlord_uedcl_meter', '4521987', NOW(), NOW()),
('uedcl_ussd_code', '*185*4*1*1*2*', NOW(), NOW()),
('nwsc_ussd_code', '*162#', NOW(), NOW()),
('flutterwave_secret_key', '', NOW(), NOW()),
('flutterwave_base_url', 'https://api.flutterwave.com/v3', NOW(), NOW()),
('meter_hmac_secret', 'zipo-meter-hmac-secret-change-in-production', NOW(), NOW()),
('water_unit_price', '4500', NOW(), NOW()),
('low_credit_threshold_kwh', '5.0', NOW(), NOW());

-- ------------------------------------------------------------------
-- DEFAULT ADMIN USER
-- Password: password123  (bcrypt hash)
-- ------------------------------------------------------------------
INSERT INTO `users` (`id`, `name`, `full_name`, `email`, `phone`, `role`,
  `status`, `is_active`, `password`, `email_verified_at`, `created_at`, `updated_at`) VALUES
(1, 'System Admin', 'System Administrator', 'admin@zipo.com', '+256700100200', 'super_admin',
  'active', 1, '$2y$12$LTCMQJpCiHOz1bH2S3qD6OqPHRq2NpaJQkc5rF1t0kZAXi5wQ.c4y', NOW(), NOW(), NOW());

-- ------------------------------------------------------------------
-- DEFAULT ADMIN WALLET
-- ------------------------------------------------------------------
INSERT INTO `wallets` (`user_id`, `balance`, `total_collected`, `total_tax_deducted`, `total_spent_on_uedcl`, `created_at`, `updated_at`) VALUES
(1, 15000000.00, 50000000.00, 3500000.00, 18000000.00, NOW(), NOW());

-- ------------------------------------------------------------------
-- MIGRATIONS TABLE (mark all migrations as "already run")
-- ------------------------------------------------------------------
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),
('2024_01_01_000001_create_properties_table', 1),
('2024_01_01_000002_create_units_table', 1),
('2024_01_01_000003_create_meters_table', 1),
('2024_01_01_000004_create_meter_readings_table', 1),
('2024_01_01_000005_create_transactions_table', 1),
('2024_01_01_000006_create_alerts_table', 1),
('2024_01_01_000007_create_wallets_table', 1),
('2024_01_01_000008_create_support_tickets_table', 1),
('2024_01_01_000009_create_system_configs_table', 1),
('2024_01_01_000010_add_custom_fields_to_users_table', 1),
('2024_01_01_000011_create_disconnection_logs_table', 1),
('2024_01_01_000012_fix_schema_mismatches', 1),
('2026_07_26_160112_create_personal_access_tokens_table', 1),
('2026_08_01_000001_create_rent_schedules_table', 1),
('2026_08_01_000002_create_rent_invoices_table', 1);

-- Re-enable foreign key & unique checks
SET FOREIGN_KEY_CHECKS = 1;
SET UNIQUE_CHECKS = 1;

-- ============================================================
-- DONE! Database schema and seed data created successfully.
-- ============================================================