-- ═══════════════════════════════════════════════════════
--  PropEase — Buyer Tables Setup
--  Run once on study24_propeased database
-- ═══════════════════════════════════════════════════════

-- 1. Buyers table
CREATE TABLE IF NOT EXISTS buyers (
    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_buyer_email  (email),
    UNIQUE KEY uq_buyer_phone  (phone),
    UNIQUE KEY uq_buyer_google (google_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 2. Buyer activity table (likes, wishlist, skips, chats, contacts, shares)
CREATE TABLE IF NOT EXISTS buyer_activity (
    id         INT AUTO_INCREMENT PRIMARY KEY,
    buyer_id   INT NOT NULL,
    listing_id INT NOT NULL,
    action     ENUM('view','like','wishlist','skip','chat','contact','share') NOT NULL,
    state      TINYINT DEFAULT 1,   -- 1=active, 0=removed
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_activity (buyer_id, listing_id, action),
    INDEX idx_buyer   (buyer_id),
    INDEX idx_listing (listing_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;