CREATE DATABASE IF NOT EXISTS real_estate CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE real_estate;

CREATE TABLE IF NOT EXISTS leads (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(150) NOT NULL,
    mobile VARCHAR(40) NOT NULL,
    location VARCHAR(160) NOT NULL,
    remarks TEXT NOT NULL,
    category VARCHAR(50) NOT NULL,
    requirement_type VARCHAR(150) NOT NULL,
    deal_side VARCHAR(20) NOT NULL,
    lead_date DATE NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS lead_media (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    lead_id INT UNSIGNED NOT NULL,
    media_type VARCHAR(20) NOT NULL,
    file_name VARCHAR(255) NOT NULL,
    file_path VARCHAR(255) NOT NULL,
    file_url VARCHAR(255) NOT NULL,
    size_label VARCHAR(50) DEFAULT '',
    dimension_label VARCHAR(80) DEFAULT '',
    is_compressed TINYINT(1) DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_lead_media_lead
        FOREIGN KEY (lead_id) REFERENCES leads(id) ON DELETE CASCADE
);

DELETE FROM lead_media;
DELETE FROM leads;

INSERT INTO leads (
    id,
    name,
    mobile,
    location,
    remarks,
    category,
    requirement_type,
    deal_side,
    lead_date
) VALUES
    (
        1,
        'Rafiqul Islam',
        '01711220033',
        'New Town, Kolkata',
        'Family buyer, south-facing preferred, lift and parking needed.',
        'Flat',
        '3 BHK Flat',
        'Buyer',
        '2026-04-24'
    ),
    (
        2,
        'Sultana Realty',
        '01844778899',
        'Madhyamgram',
        'Seller lead for a ready building. Wants serious buyer only.',
        'Building',
        'Readymade 2 Floor Building',
        'Seller',
        '2026-04-22'
    ),
    (
        3,
        'Amit Dey',
        '01920445566',
        'Barasat',
        'Needs 3 katha land near main road for future home project.',
        'Land',
        '3 Katha Land',
        'Buyer',
        '2026-04-25'
    ),
    (
        4,
        'Nadia Property Point',
        '01611889977',
        'Rajarhat',
        'Seller has site photos and expects fast closing within one month.',
        'Flat',
        '2 BHK Flat',
        'Seller',
        '2026-04-23'
    ),
    (
        5,
        'Imran Hossain',
        '01555991122',
        'Dum Dum',
        'Looking for construction cost estimate and ready 1 floor option both.',
        'Building',
        'Construction Cost Estimate',
        'Buyer',
        '2026-04-25'
    );

INSERT INTO lead_media (
    lead_id,
    media_type,
    file_name,
    file_path,
    file_url,
    size_label,
    dimension_label,
    is_compressed
) VALUES
    (
        1,
        'image',
        'flat_front_view.jpg',
        'uploads/1/flat_front_view.jpg',
        '/real_estate/php_api/uploads/1/flat_front_view.jpg',
        '1.4 MB',
        '1600 x 900 px',
        1
    ),
    (
        2,
        'video',
        'building_walkthrough.mp4',
        'uploads/2/building_walkthrough.mp4',
        '/real_estate/php_api/uploads/2/building_walkthrough.mp4',
        '12.8 MB',
        'Video file',
        0
    ),
    (
        3,
        'image',
        'land_plot_map.jpg',
        'uploads/3/land_plot_map.jpg',
        '/real_estate/php_api/uploads/3/land_plot_map.jpg',
        '980 KB',
        '1400 x 900 px',
        1
    ),
    (
        4,
        'image',
        'seller_flat_living_room.jpg',
        'uploads/4/seller_flat_living_room.jpg',
        '/real_estate/php_api/uploads/4/seller_flat_living_room.jpg',
        '1.1 MB',
        '1600 x 1000 px',
        1
    ),
    (
        5,
        'image',
        'estimate_reference_plan.jpg',
        'uploads/5/estimate_reference_plan.jpg',
        '/real_estate/php_api/uploads/5/estimate_reference_plan.jpg',
        '860 KB',
        '1280 x 820 px',
        0
    );