-- ═══════════════════════════════════════════════════════
--  PropEase — Multi-Seller DB Setup
--  Run once on your MySQL database
-- ═══════════════════════════════════════════════════════

-- 1. Create sellers table
CREATE TABLE IF NOT EXISTS sellers (
    id            INT AUTO_INCREMENT PRIMARY KEY,
    name          VARCHAR(255)  DEFAULT NULL,
    email         VARCHAR(255)  DEFAULT NULL,
    phone         VARCHAR(20)   DEFAULT NULL,
    google_id     VARCHAR(255)  DEFAULT NULL,
    password_hash VARCHAR(255)  DEFAULT NULL,
    created_at    TIMESTAMP     DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_email    (email),
    UNIQUE KEY uq_phone    (phone),
    UNIQUE KEY uq_google   (google_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 2. Add seller_id column to listings (skip if already exists)
ALTER TABLE listings
    ADD COLUMN IF NOT EXISTS seller_id INT DEFAULT NULL AFTER id,
    ADD INDEX IF NOT EXISTS idx_seller_id (seller_id);

-- 3. Fix existing listings with NULL status
UPDATE listings SET status = 'active' WHERE status IS NULL;

-- Done! New sellers register via login.php.
-- Existing listings with seller_id = NULL are visible to all
-- (backward compatible until you re-assign them manually).