-- Homeispecto database schema
-- Run this once in phpMyAdmin (or the MySQL command line) on your Namecheap Stellar Plus database.

CREATE TABLE IF NOT EXISTS customers (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(150) NOT NULL DEFAULT '',
  phone VARCHAR(20) NOT NULL,
  email VARCHAR(150) NOT NULL DEFAULT '',
  address VARCHAR(255) NOT NULL DEFAULT '',
  pin VARCHAR(7) NOT NULL DEFAULT '',
  referred_by VARCHAR(150) NOT NULL DEFAULT '',
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY unique_phone (phone)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS reports (
  id INT AUTO_INCREMENT PRIMARY KEY,
  customer_id INT NOT NULL,
  token VARCHAR(64) NOT NULL,
  property_address VARCHAR(255) NOT NULL DEFAULT '',
  inspection_date DATE NULL,
  inspector_name VARCHAR(150) NOT NULL DEFAULT '',
  report_data LONGTEXT NOT NULL,
  status ENUM('active','revoked') NOT NULL DEFAULT 'active',
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY unique_token (token),
  FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Trade library (Phase 2, drag-on referral cards) — created now so the schema is ready when we build that piece
CREATE TABLE IF NOT EXISTS trades (
  id INT AUTO_INCREMENT PRIMARY KEY,
  business_name VARCHAR(150) NOT NULL,
  category VARCHAR(50) NOT NULL,
  phone VARCHAR(20) NOT NULL DEFAULT '',
  email VARCHAR(150) NOT NULL DEFAULT '',
  card_image_url VARCHAR(500) NOT NULL DEFAULT '',
  referral_count INT NOT NULL DEFAULT 0,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;