-- EstateFlow cPanel MySQL Database Schema
-- Import this SQL file in phpMyAdmin under your newly created cPanel database

SET FOREIGN_KEY_CHECKS = 0;

-- 1. Users Table
CREATE TABLE IF NOT EXISTS `users` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `username` VARCHAR(100) UNIQUE NOT NULL,
  `email` VARCHAR(150) UNIQUE NOT NULL,
  `password_hash` VARCHAR(255) NOT NULL,
  `name` VARCHAR(150) NOT NULL,
  `role` VARCHAR(50) DEFAULT 'Admin',
  `roles` TEXT DEFAULT '["administrator"]',
  `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Seed default admin user (Password: admin123)
INSERT INTO `users` (`username`, `email`, `password_hash`, `name`, `role`, `roles`) 
VALUES ('admin', 'admin@estateflow.com', '$2y$10$e0MYzXyjpJS7Pd0RVvHwHe1e.d.f48e4mN4G0/Qy3qH3f1e9c5Wye', 'EstateFlow Admin', 'Admin', '["administrator", "manage_settings", "manage_sales", "manage_estates"]')
ON DUPLICATE KEY UPDATE `id`=`id`;

-- Seed super admin user (Password: admin123)
INSERT INTO `users` (`username`, `email`, `password_hash`, `name`, `role`, `roles`) 
VALUES ('superadmin', 'superadmin@estateflow.com', '$2y$10$e0MYzXyjpJS7Pd0RVvHwHe1e.d.f48e4mN4G0/Qy3qH3f1e9c5Wye', 'Super Admin', 'Super Admin', '["superadmin", "administrator", "super_admin_access"]')
ON DUPLICATE KEY UPDATE `id`=`id`;

-- 2. Password Resets Table
CREATE TABLE IF NOT EXISTS `password_resets` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `email` VARCHAR(150) NOT NULL,
  `token` VARCHAR(100) NOT NULL,
  `expires_at` DATETIME NOT NULL,
  `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 3. Subscription Plans Table
CREATE TABLE IF NOT EXISTS `plans` (
  `id` VARCHAR(50) PRIMARY KEY,
  `name` VARCHAR(100) NOT NULL,
  `price` DECIMAL(10,2) DEFAULT 0.00,
  `interval` VARCHAR(20) DEFAULT 'month',
  `features` TEXT,
  `is_featured` TINYINT(1) DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `plans` (`id`, `name`, `price`, `interval`, `features`, `is_featured`) VALUES
('free', 'Starter Free', 0.00, 'month', '["Basic Property Tracking", "Up to 5 Estates", "Standard Reports"]', 0),
('pro', 'Pro Real Estate', 49.00, 'month', '["Unlimited Estates", "WhatsApp Payment Reminders", "AI Description Generator", "Multi-Agent Support"]', 1),
('enterprise', 'Enterprise Tier', 199.00, 'month', '["All Pro Features", "Dedicated cPanel Database Sync", "White-label Branding", "24/7 Priority Support"]', 0)
ON DUPLICATE KEY UPDATE `id`=`id`;

-- 4. Estates Table
CREATE TABLE IF NOT EXISTS `estates` (
  `id` VARCHAR(100) PRIMARY KEY,
  `name` VARCHAR(200) NOT NULL,
  `location` VARCHAR(255),
  `total_plots` INT DEFAULT 0,
  `plot_price` DECIMAL(15,2) DEFAULT 0.00,
  `status` VARCHAR(50) DEFAULT 'Active',
  `description` TEXT,
  `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 5. Clients Table
CREATE TABLE IF NOT EXISTS `clients` (
  `id` VARCHAR(100) PRIMARY KEY,
  `name` VARCHAR(200) NOT NULL,
  `email` VARCHAR(150),
  `phone` VARCHAR(50),
  `address` TEXT,
  `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 6. Sales Table
CREATE TABLE IF NOT EXISTS `sales` (
  `id` VARCHAR(100) PRIMARY KEY,
  `estate_id` VARCHAR(100),
  `client_id` VARCHAR(100),
  `customer_name` VARCHAR(200),
  `plot_number` VARCHAR(100),
  `total_amount` DECIMAL(15,2) DEFAULT 0.00,
  `amount_paid` DECIMAL(15,2) DEFAULT 0.00,
  `balance` DECIMAL(15,2) DEFAULT 0.00,
  `status` VARCHAR(50) DEFAULT 'Ongoing',
  `purchase_date` DATE,
  `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 7. Expenses Table
CREATE TABLE IF NOT EXISTS `expenses` (
  `id` VARCHAR(100) PRIMARY KEY,
  `title` VARCHAR(200) NOT NULL,
  `amount` DECIMAL(15,2) DEFAULT 0.00,
  `category` VARCHAR(100),
  `date` DATE,
  `description` TEXT,
  `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 8. Leads Table
CREATE TABLE IF NOT EXISTS `leads` (
  `id` VARCHAR(100) PRIMARY KEY,
  `name` VARCHAR(200) NOT NULL,
  `email` VARCHAR(150),
  `phone` VARCHAR(50),
  `status` VARCHAR(50) DEFAULT 'New',
  `priority` VARCHAR(50) DEFAULT 'Medium',
  `notes` TEXT,
  `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

SET FOREIGN_KEY_CHECKS = 1;