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


-- 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;

