-- MJ Marketplace MySQL/MariaDB compatible safe schema helpers.
-- Required because some MySQL versions do not support ALTER TABLE ... ADD COLUMN.
DROP PROCEDURE IF EXISTS mj_safe_add_column;
DROP PROCEDURE IF EXISTS mj_safe_add_index;
DELIMITER $$
CREATE PROCEDURE mj_safe_add_column(IN p_table VARCHAR(128), IN p_col VARCHAR(128), IN p_def TEXT)
BEGIN
  IF EXISTS (SELECT 1 FROM information_schema.TABLES WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = p_table LIMIT 1)
     AND NOT EXISTS (SELECT 1 FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = p_table AND COLUMN_NAME = p_col LIMIT 1) THEN
    SET @mj_sql = CONCAT('ALTER TABLE `', REPLACE(p_table,'`','``'), '` ADD COLUMN ', p_def);
    PREPARE mj_stmt FROM @mj_sql;
    EXECUTE mj_stmt;
    DEALLOCATE PREPARE mj_stmt;
  END IF;
END$$
CREATE PROCEDURE mj_safe_add_index(IN p_table VARCHAR(128), IN p_index VARCHAR(128), IN p_def TEXT)
BEGIN
  IF EXISTS (SELECT 1 FROM information_schema.TABLES WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = p_table LIMIT 1)
     AND NOT EXISTS (SELECT 1 FROM information_schema.STATISTICS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = p_table AND INDEX_NAME = p_index LIMIT 1) THEN
    SET @mj_sql = CONCAT('ALTER TABLE `', REPLACE(p_table,'`','``'), '` ', p_def);
    PREPARE mj_stmt FROM @mj_sql;
    EXECUTE mj_stmt;
    DEALLOCATE PREPARE mj_stmt;
  END IF;
END$$
DELIMITER ;

-- ============================================================
-- MJ Marketplace v3.0.4.1 Sample Demo Data
-- Users + Companies + Listings + Deals + Leads + Notifications
-- ------------------------------------------------------------
-- Run AFTER importing mj-marketplace-v3.0.4.1-full-database-only.sql
-- Safe to run multiple times: uses unique emails/slugs and NOT EXISTS checks.
-- Demo login password for all sample users/admin: Demo@123456
-- IMPORTANT: This sample keeps the locked rule: no customer-to-seller payment.
-- Deals/listings/companies are display + enquiry/contact only.
-- ============================================================

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
SET time_zone = '+08:00';

-- ------------------------------------------------------------
-- v3.0.4.2.5 Sample Data Schema Safety Repair
-- This allows sample data upload on existing working websites where
-- some v3.0.4.x columns/tables were not present yet.
-- Safe to run multiple times. No data deleted.
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS mj_subscriptions (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT NULL,
  company_id BIGINT NULL,
  plan_name VARCHAR(120) NULL,
  status VARCHAR(40) DEFAULT 'active',
  starts_at DATETIME NULL,
  ends_at DATETIME NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CALL mj_safe_add_column('mj_companies', 'approved_at', 'approved_at DATETIME NULL');
CALL mj_safe_add_column('mj_companies', 'approved_by', 'approved_by BIGINT NULL');
CALL mj_safe_add_column('mj_companies', 'submitted_at', 'submitted_at DATETIME NULL');
CALL mj_safe_add_column('mj_companies', 'admin_note', 'admin_note TEXT NULL');
CALL mj_safe_add_column('mj_companies', 'rejection_reason', 'rejection_reason TEXT NULL');
CALL mj_safe_add_column('mj_companies', 'is_sponsored', 'is_sponsored TINYINT(1) NOT NULL DEFAULT 0');
CALL mj_safe_add_column('mj_companies', 'sponsored_until', 'sponsored_until DATETIME NULL');

CALL mj_safe_add_column('mj_deals', 'approved_at', 'approved_at DATETIME NULL');
CALL mj_safe_add_column('mj_deals', 'approved_by', 'approved_by BIGINT NULL');
CALL mj_safe_add_column('mj_deals', 'featured_until', 'featured_until DATETIME NULL');
CALL mj_safe_add_column('mj_deals', 'sponsored_until', 'sponsored_until DATETIME NULL');
CALL mj_safe_add_column('mj_deals', 'enquiry_count', 'enquiry_count INT NOT NULL DEFAULT 0');
CALL mj_safe_add_column('mj_deals', 'view_count', 'view_count INT NOT NULL DEFAULT 0');

CALL mj_safe_add_column('mj_listings', 'approved_at', 'approved_at DATETIME NULL');
CALL mj_safe_add_column('mj_listings', 'approved_by', 'approved_by BIGINT NULL');
CALL mj_safe_add_column('mj_listings', 'company_id', 'company_id BIGINT NULL');
CALL mj_safe_add_column('mj_listings', 'branch_id', 'branch_id BIGINT NULL');
CALL mj_safe_add_column('mj_listings', 'published_at', 'published_at DATETIME NULL');
CALL mj_safe_add_column('mj_listings', 'expires_at', 'expires_at DATETIME NULL');

CALL mj_safe_add_column('mj_subscriptions', 'user_id', 'user_id BIGINT NULL');
CALL mj_safe_add_column('mj_subscriptions', 'company_id', 'company_id BIGINT NULL');
CALL mj_safe_add_column('mj_subscriptions', 'plan_name', 'plan_name VARCHAR(120) NULL');
CALL mj_safe_add_column('mj_subscriptions', 'status', 'status VARCHAR(40) DEFAULT ''active''');
CALL mj_safe_add_column('mj_subscriptions', 'starts_at', 'starts_at DATETIME NULL');
CALL mj_safe_add_column('mj_subscriptions', 'ends_at', 'ends_at DATETIME NULL');
CALL mj_safe_add_column('mj_subscriptions', 'created_at', 'created_at DATETIME DEFAULT CURRENT_TIMESTAMP');

CREATE TABLE IF NOT EXISTS seller_leads (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  seller_user_id BIGINT UNSIGNED NOT NULL DEFAULT 0,
  source_type VARCHAR(30) NOT NULL DEFAULT 'listing',
  source_id BIGINT UNSIGNED DEFAULT NULL,
  source_title VARCHAR(255) DEFAULT NULL,
  country VARCHAR(100) DEFAULT NULL,
  customer_name VARCHAR(160) DEFAULT NULL,
  customer_email VARCHAR(190) DEFAULT NULL,
  customer_phone VARCHAR(80) DEFAULT NULL,
  customer_message TEXT DEFAULT NULL,
  status VARCHAR(40) NOT NULL DEFAULT 'new',
  priority VARCHAR(20) NOT NULL DEFAULT 'normal',
  followup_at DATETIME DEFAULT NULL,
  last_activity_at DATETIME DEFAULT NULL,
  is_read TINYINT(1) NOT NULL DEFAULT 0,
  ip_address VARCHAR(80) DEFAULT NULL,
  user_agent VARCHAR(255) DEFAULT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  KEY idx_seller_status_created (seller_user_id,status,created_at),
  KEY idx_source (source_type,source_id),
  KEY idx_customer_email (customer_email),
  KEY idx_followup (seller_user_id,followup_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS seller_lead_activities (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  lead_id BIGINT NOT NULL,
  user_id BIGINT NULL,
  user_type VARCHAR(20) DEFAULT 'seller',
  activity_type VARCHAR(40) NOT NULL DEFAULT 'note',
  note TEXT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_lead_created (lead_id,created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS mj_seller_leads LIKE seller_leads;
CREATE TABLE IF NOT EXISTS mj_seller_lead_activities LIKE seller_lead_activities;
CREATE TABLE IF NOT EXISTS mj_seller_lead_notes (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  lead_id BIGINT NOT NULL,
  user_id BIGINT NULL,
  note TEXT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_lead_created (lead_id,created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS mj_seller_lead_followups (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  lead_id BIGINT NOT NULL,
  user_id BIGINT NULL,
  followup_at DATETIME NULL,
  status VARCHAR(40) DEFAULT 'pending',
  note TEXT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_lead_followup (lead_id,followup_at),
  KEY idx_status_followup (status,followup_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;



-- ------------------------------------------------------------
-- v3.0.4.2.16 Sample Data FK Safety Fix
-- Adds NULL guards for batch-resumed sample data so child rows
-- never insert NULL parent IDs when MySQL @variables are rehydrated.
-- ------------------------------------------------------------

SET @demo_password_hash := '$2y$12$NGleMQJMvmHnNNEplea./e2sXmoIvcSxlotv3bjv4ecDJsMgh3vQG';

-- ------------------------------------------------------------
-- 1) Countries / States / Cities needed for sample data
-- ------------------------------------------------------------
INSERT INTO mj_countries
(iso2, iso3, name, slug, flag_emoji, currency_code, phone_code, timezone_default, language_default, tax_name, tax_rate, is_active, sort_order)
VALUES
('SG','SGP','Singapore','singapore','🇸🇬','SGD','+65','Asia/Singapore','en','GST',9.000,1,1),
('MY','MYS','Malaysia','malaysia','🇲🇾','MYR','+60','Asia/Kuala_Lumpur','en','SST',0.000,1,2),
('IN','IND','India','india','🇮🇳','INR','+91','Asia/Kolkata','en','GST',18.000,1,3)
ON DUPLICATE KEY UPDATE
name=VALUES(name), iso3=VALUES(iso3), flag_emoji=VALUES(flag_emoji), currency_code=VALUES(currency_code), phone_code=VALUES(phone_code), timezone_default=VALUES(timezone_default), tax_name=VALUES(tax_name), tax_rate=VALUES(tax_rate), is_active=1;

SELECT id INTO @country_sg FROM mj_countries WHERE iso2='SG' LIMIT 1;
SELECT id INTO @country_my FROM mj_countries WHERE iso2='MY' LIMIT 1;
SELECT id INTO @country_in FROM mj_countries WHERE iso2='IN' LIMIT 1;

INSERT INTO mj_states (country_id, name, state_code, slug, timezone, latitude, longitude, is_active)
SELECT @country_sg, 'Singapore', 'SG-01', 'singapore', 'Asia/Singapore', 1.35208300, 103.81983900, 1
WHERE @country_sg IS NOT NULL AND NOT EXISTS (SELECT 1 FROM mj_states WHERE country_id=@country_sg AND slug='singapore');

INSERT INTO mj_states (country_id, name, state_code, slug, timezone, latitude, longitude, is_active)
SELECT @country_my, 'Kuala Lumpur', 'MY-14', 'kuala-lumpur', 'Asia/Kuala_Lumpur', 3.13900300, 101.68685500, 1
WHERE @country_my IS NOT NULL AND NOT EXISTS (SELECT 1 FROM mj_states WHERE country_id=@country_my AND slug='kuala-lumpur');

INSERT INTO mj_states (country_id, name, state_code, slug, timezone, latitude, longitude, is_active)
SELECT @country_my, 'Johor', 'MY-01', 'johor', 'Asia/Kuala_Lumpur', 1.48536800, 103.76181500, 1
WHERE @country_my IS NOT NULL AND NOT EXISTS (SELECT 1 FROM mj_states WHERE country_id=@country_my AND slug='johor');

INSERT INTO mj_states (country_id, name, state_code, slug, timezone, latitude, longitude, is_active)
SELECT @country_in, 'Tamil Nadu', 'IN-TN', 'tamil-nadu', 'Asia/Kolkata', 11.12712300, 78.65689100, 1
WHERE @country_in IS NOT NULL AND NOT EXISTS (SELECT 1 FROM mj_states WHERE country_id=@country_in AND slug='tamil-nadu');

SELECT id INTO @state_sg FROM mj_states WHERE country_id=@country_sg AND slug='singapore' LIMIT 1;
SELECT id INTO @state_kl FROM mj_states WHERE country_id=@country_my AND slug='kuala-lumpur' LIMIT 1;
SELECT id INTO @state_johor FROM mj_states WHERE country_id=@country_my AND slug='johor' LIMIT 1;
SELECT id INTO @state_tn FROM mj_states WHERE country_id=@country_in AND slug='tamil-nadu' LIMIT 1;

INSERT INTO mj_cities (country_id, state_id, name, slug, timezone, latitude, longitude, is_active)
SELECT @country_sg, @state_sg, 'Singapore', 'singapore', 'Asia/Singapore', 1.35208300, 103.81983900, 1
WHERE @country_sg IS NOT NULL AND @state_sg IS NOT NULL AND NOT EXISTS (SELECT 1 FROM mj_cities WHERE country_id=@country_sg AND state_id=@state_sg AND slug='singapore');

INSERT INTO mj_cities (country_id, state_id, name, slug, timezone, latitude, longitude, is_active)
SELECT @country_sg, @state_sg, 'Choa Chu Kang', 'choa-chu-kang', 'Asia/Singapore', 1.38540000, 103.74420000, 1
WHERE @country_sg IS NOT NULL AND @state_sg IS NOT NULL AND NOT EXISTS (SELECT 1 FROM mj_cities WHERE country_id=@country_sg AND state_id=@state_sg AND slug='choa-chu-kang');

INSERT INTO mj_cities (country_id, state_id, name, slug, timezone, latitude, longitude, is_active)
SELECT @country_my, @state_kl, 'Kuala Lumpur', 'kuala-lumpur', 'Asia/Kuala_Lumpur', 3.13900300, 101.68685500, 1
WHERE @country_my IS NOT NULL AND @state_kl IS NOT NULL AND NOT EXISTS (SELECT 1 FROM mj_cities WHERE country_id=@country_my AND state_id=@state_kl AND slug='kuala-lumpur');

INSERT INTO mj_cities (country_id, state_id, name, slug, timezone, latitude, longitude, is_active)
SELECT @country_my, @state_johor, 'Johor Bahru', 'johor-bahru', 'Asia/Kuala_Lumpur', 1.49270000, 103.74140000, 1
WHERE @country_my IS NOT NULL AND @state_johor IS NOT NULL AND NOT EXISTS (SELECT 1 FROM mj_cities WHERE country_id=@country_my AND state_id=@state_johor AND slug='johor-bahru');

INSERT INTO mj_cities (country_id, state_id, name, slug, timezone, latitude, longitude, is_active)
SELECT @country_in, @state_tn, 'Chennai', 'chennai', 'Asia/Kolkata', 13.08268000, 80.27071800, 1
WHERE @country_in IS NOT NULL AND @state_tn IS NOT NULL AND NOT EXISTS (SELECT 1 FROM mj_cities WHERE country_id=@country_in AND state_id=@state_tn AND slug='chennai');

SELECT id INTO @city_sg FROM mj_cities WHERE country_id=@country_sg AND state_id=@state_sg AND slug='singapore' LIMIT 1;
SELECT id INTO @city_cck FROM mj_cities WHERE country_id=@country_sg AND state_id=@state_sg AND slug='choa-chu-kang' LIMIT 1;
SELECT id INTO @city_kl FROM mj_cities WHERE country_id=@country_my AND state_id=@state_kl AND slug='kuala-lumpur' LIMIT 1;
SELECT id INTO @city_jb FROM mj_cities WHERE country_id=@country_my AND state_id=@state_johor AND slug='johor-bahru' LIMIT 1;
SELECT id INTO @city_chennai FROM mj_cities WHERE country_id=@country_in AND state_id=@state_tn AND slug='chennai' LIMIT 1;

-- ------------------------------------------------------------
-- 2) Demo admin + users
-- ------------------------------------------------------------
INSERT INTO mj_admin_users (name, email, password_hash, role, country_id, is_active)
VALUES ('Demo Super Admin', 'admin.demo@mjmarketplace.test', @demo_password_hash, 'super_admin', @country_sg, 1)
ON DUPLICATE KEY UPDATE name=VALUES(name), password_hash=VALUES(password_hash), role=VALUES(role), country_id=VALUES(country_id), is_active=1;

SELECT id INTO @admin_demo FROM mj_admin_users WHERE email='admin.demo@mjmarketplace.test' LIMIT 1;

INSERT INTO mj_users
(name, email, mobile, password_hash, country_id, state_id, city_id, timezone, preferred_currency, trust_score, is_email_verified, is_mobile_verified, is_active)
VALUES
('Demo Seller Singapore', 'seller.sg@mjmarketplace.test', '+6581513060', @demo_password_hash, @country_sg, @state_sg, @city_sg, 'Asia/Singapore', 'SGD', 88, 1, 1, 1),
('Demo Seller Malaysia', 'seller.my@mjmarketplace.test', '+60123456789', @demo_password_hash, @country_my, @state_kl, @city_kl, 'Asia/Kuala_Lumpur', 'MYR', 82, 1, 1, 1),
('Demo Seller India', 'seller.in@mjmarketplace.test', '+919876543210', @demo_password_hash, @country_in, @state_tn, @city_chennai, 'Asia/Kolkata', 'INR', 79, 1, 1, 1),
('Demo Customer Buyer', 'buyer.demo@mjmarketplace.test', '+6588889999', @demo_password_hash, @country_sg, @state_sg, @city_cck, 'Asia/Singapore', 'SGD', 60, 1, 1, 1),
('Demo Business Owner', 'owner.demo@mjmarketplace.test', '+6580001111', @demo_password_hash, @country_sg, @state_sg, @city_sg, 'Asia/Singapore', 'SGD', 75, 1, 1, 1)
ON DUPLICATE KEY UPDATE
name=VALUES(name), mobile=VALUES(mobile), password_hash=VALUES(password_hash), country_id=VALUES(country_id), state_id=VALUES(state_id), city_id=VALUES(city_id), timezone=VALUES(timezone), preferred_currency=VALUES(preferred_currency), is_active=1;

SELECT id INTO @seller_sg FROM mj_users WHERE email='seller.sg@mjmarketplace.test' LIMIT 1;
SELECT id INTO @seller_my FROM mj_users WHERE email='seller.my@mjmarketplace.test' LIMIT 1;
SELECT id INTO @seller_in FROM mj_users WHERE email='seller.in@mjmarketplace.test' LIMIT 1;
SELECT id INTO @buyer_demo FROM mj_users WHERE email='buyer.demo@mjmarketplace.test' LIMIT 1;
SELECT id INTO @owner_demo FROM mj_users WHERE email='owner.demo@mjmarketplace.test' LIMIT 1;

-- ------------------------------------------------------------
-- 3) Categories
-- ------------------------------------------------------------
INSERT INTO mj_categories (parent_id, type, name, slug, icon, requires_approval, is_active, sort_order)
VALUES
(NULL,'marketplace','Electronics & IT','electronics-it','fa-laptop',0,1,10),
(NULL,'marketplace','Home Services','home-services','fa-home',0,1,20),
(NULL,'marketplace','Data Recovery','data-recovery','fa-hard-drive',0,1,30),
(NULL,'company','IT Services','it-services','fa-server',1,1,10),
(NULL,'company','Cleaning Services','cleaning-services','fa-broom',1,1,20),
(NULL,'company','Beauty & Wellness','beauty-wellness','fa-spa',1,1,30),
(NULL,'service','Repair Services','repair-services','fa-screwdriver-wrench',0,1,10)
ON DUPLICATE KEY UPDATE name=VALUES(name), icon=VALUES(icon), is_active=1, sort_order=VALUES(sort_order);

SELECT id INTO @cat_electronics FROM mj_categories WHERE type='marketplace' AND slug='electronics-it' LIMIT 1;
SELECT id INTO @cat_home FROM mj_categories WHERE type='marketplace' AND slug='home-services' LIMIT 1;
SELECT id INTO @cat_data FROM mj_categories WHERE type='marketplace' AND slug='data-recovery' LIMIT 1;
SELECT id INTO @cat_company_it FROM mj_categories WHERE type='company' AND slug='it-services' LIMIT 1;
SELECT id INTO @cat_company_cleaning FROM mj_categories WHERE type='company' AND slug='cleaning-services' LIMIT 1;
SELECT id INTO @cat_company_beauty FROM mj_categories WHERE type='company' AND slug='beauty-wellness' LIMIT 1;

-- ------------------------------------------------------------
-- 4) Companies + branches + services
-- ------------------------------------------------------------
INSERT INTO mj_companies
(user_id, category_id, country_id, state_id, city_id, timezone, name, slug, registration_no, logo, cover_image, description, phone, whatsapp, email, website, address, latitude, longitude, status, is_verified, verification_status, is_featured, is_sponsored, sponsored_until, rating_avg, rating_count, trust_score, profile_score, views, submitted_at, approved_at, approved_by)
SELECT @seller_sg, @cat_company_it, @country_sg, @state_sg, @city_sg, 'Asia/Singapore', 'MJ Data Recovery Singapore', 'mj-data-recovery-singapore', '202400001A', 'uploads/demo/company-data-logo.png', 'uploads/demo/company-data-cover.jpg', 'Professional data recovery for hard disk, SSD, RAID, NAS and laptop storage. Demo company for marketplace testing.', '+6581513060', '+6581513060', 'data.sg@mjmarketplace.test', 'https://example.com/data-recovery-sg', '1 Demo Street, Singapore 000001', 1.35208300, 103.81983900, 'active', 1, 'verified', 1, 1, DATE_ADD(NOW(), INTERVAL 30 DAY), 4.80, 42, 92, 95, 1280, NOW(), NOW(), @admin_demo
WHERE @admin_demo IS NOT NULL AND @cat_company_it IS NOT NULL AND @city_sg IS NOT NULL AND @country_sg IS NOT NULL AND @seller_sg IS NOT NULL AND @state_sg IS NOT NULL AND NOT EXISTS (SELECT 1 FROM mj_companies WHERE slug='mj-data-recovery-singapore');

INSERT INTO mj_companies
(user_id, category_id, country_id, state_id, city_id, timezone, name, slug, registration_no, logo, cover_image, description, phone, whatsapp, email, website, address, latitude, longitude, status, is_verified, verification_status, is_featured, is_sponsored, sponsored_until, rating_avg, rating_count, trust_score, profile_score, views, submitted_at, approved_at, approved_by)
SELECT @seller_my, @cat_company_cleaning, @country_my, @state_kl, @city_kl, 'Asia/Kuala_Lumpur', 'KL Home Cleaning Pro', 'kl-home-cleaning-pro', 'MY-DEMO-1002', 'uploads/demo/company-cleaning-logo.png', 'uploads/demo/company-cleaning-cover.jpg', 'Home, office and move-in cleaning service in Kuala Lumpur. Demo company for enquiries and leads.', '+60123456789', '+60123456789', 'cleaning.kl@mjmarketplace.test', 'https://example.com/kl-cleaning', 'Bukit Bintang, Kuala Lumpur', 3.14780000, 101.71130000, 'active', 1, 'verified', 1, 0, NULL, 4.60, 31, 84, 90, 740, NOW(), NOW(), @admin_demo
WHERE @admin_demo IS NOT NULL AND @cat_company_cleaning IS NOT NULL AND @city_kl IS NOT NULL AND @country_my IS NOT NULL AND @seller_my IS NOT NULL AND @state_kl IS NOT NULL AND NOT EXISTS (SELECT 1 FROM mj_companies WHERE slug='kl-home-cleaning-pro');

INSERT INTO mj_companies
(user_id, category_id, country_id, state_id, city_id, timezone, name, slug, registration_no, logo, cover_image, description, phone, whatsapp, email, website, address, latitude, longitude, status, is_verified, verification_status, is_featured, is_sponsored, sponsored_until, rating_avg, rating_count, trust_score, profile_score, views, submitted_at, approved_at, approved_by)
SELECT @seller_in, @cat_company_beauty, @country_in, @state_tn, @city_chennai, 'Asia/Kolkata', 'Chennai Beauty Clinic', 'chennai-beauty-clinic-demo', 'IN-DEMO-3003', 'uploads/demo/company-beauty-logo.png', 'uploads/demo/company-beauty-cover.jpg', 'Aesthetic and wellness clinic demo profile. Enquiry only, no customer payment through marketplace.', '+919876543210', '+919876543210', 'beauty.chennai@mjmarketplace.test', 'https://example.com/chennai-beauty', 'T Nagar, Chennai', 13.04180000, 80.23410000, 'active', 1, 'verified', 0, 0, NULL, 4.50, 18, 80, 82, 420, NOW(), NOW(), @admin_demo
WHERE @admin_demo IS NOT NULL AND @cat_company_beauty IS NOT NULL AND @city_chennai IS NOT NULL AND @country_in IS NOT NULL AND @seller_in IS NOT NULL AND @state_tn IS NOT NULL AND NOT EXISTS (SELECT 1 FROM mj_companies WHERE slug='chennai-beauty-clinic-demo');

SELECT id INTO @company_data FROM mj_companies WHERE slug='mj-data-recovery-singapore' LIMIT 1;
SELECT id INTO @company_clean FROM mj_companies WHERE slug='kl-home-cleaning-pro' LIMIT 1;
SELECT id INTO @company_beauty FROM mj_companies WHERE slug='chennai-beauty-clinic-demo' LIMIT 1;

INSERT INTO mj_company_branches (company_id, country_id, state_id, city_id, name, timezone, phone, whatsapp, email, address, opening_hours_json, is_main, is_active)
SELECT @company_data, @country_sg, @state_sg, @city_sg, 'Singapore Main Service Centre', 'Asia/Singapore', '+6581513060', '+6581513060', 'data.sg@mjmarketplace.test', '1 Demo Street, Singapore 000001', '{"mon_fri":"09:00-18:00","sat":"10:00-14:00"}', 1, 1
WHERE @city_sg IS NOT NULL AND @company_data IS NOT NULL AND @country_sg IS NOT NULL AND @state_sg IS NOT NULL AND NOT EXISTS (SELECT 1 FROM mj_company_branches WHERE company_id=@company_data AND name='Singapore Main Service Centre');

INSERT INTO mj_company_branches (company_id, country_id, state_id, city_id, name, timezone, phone, whatsapp, email, address, opening_hours_json, is_main, is_active)
SELECT @company_clean, @country_my, @state_kl, @city_kl, 'KL City Branch', 'Asia/Kuala_Lumpur', '+60123456789', '+60123456789', 'cleaning.kl@mjmarketplace.test', 'Bukit Bintang, Kuala Lumpur', '{"daily":"08:00-20:00"}', 1, 1
WHERE @city_kl IS NOT NULL AND @company_clean IS NOT NULL AND @country_my IS NOT NULL AND @state_kl IS NOT NULL AND NOT EXISTS (SELECT 1 FROM mj_company_branches WHERE company_id=@company_clean AND name='KL City Branch');

INSERT INTO mj_company_services (company_id, name, price_from, duration_minutes, description, is_bookable)
SELECT @company_data, 'Free Data Recovery Diagnosis', 0.00, 30, 'Check hard disk/SSD/NAS condition and advise recovery options.', 0
WHERE @company_data IS NOT NULL AND NOT EXISTS (SELECT 1 FROM mj_company_services WHERE company_id=@company_data AND name='Free Data Recovery Diagnosis');

INSERT INTO mj_company_services (company_id, name, price_from, duration_minutes, description, is_bookable)
SELECT @company_clean, '4 Hours Home Cleaning', 120.00, 240, 'General home cleaning service by demo provider.', 1
WHERE @company_clean IS NOT NULL AND NOT EXISTS (SELECT 1 FROM mj_company_services WHERE company_id=@company_clean AND name='4 Hours Home Cleaning');

INSERT INTO mj_company_services (company_id, name, price_from, duration_minutes, description, is_bookable)
SELECT @company_beauty, 'Skin Consultation', 50.00, 45, 'Demo consultation service for clinic enquiries.', 1
WHERE @company_beauty IS NOT NULL AND NOT EXISTS (SELECT 1 FROM mj_company_services WHERE company_id=@company_beauty AND name='Skin Consultation');

-- ------------------------------------------------------------
-- 5) Listings
-- ------------------------------------------------------------
INSERT INTO mj_listings
(user_id, company_id, category_id, country_id, state_id, city_id, timezone, listing_type, title, slug, description, price, currency_code, condition_label, brand, model, status, is_featured, is_urgent, views, quality_score, expires_at, published_at, custom_fields_json)
SELECT @seller_sg, @company_data, @cat_data, @country_sg, @state_sg, @city_sg, 'Asia/Singapore', 'service', 'Hard Disk Data Recovery Service Singapore', 'hard-disk-data-recovery-service-singapore-demo', 'Recover data from faulty hard disk, SSD, laptop, RAID and NAS. Demo listing for testing enquiries.', 80.00, 'SGD', 'Service', 'All Brands', 'HDD/SSD/NAS', 'active', 1, 1, 680, 95, DATE_ADD(NOW(), INTERVAL 60 DAY), NOW(), '{"demo":"yes","enquiry_only":"yes"}'
WHERE @cat_data IS NOT NULL AND @city_sg IS NOT NULL AND @company_data IS NOT NULL AND @country_sg IS NOT NULL AND @seller_sg IS NOT NULL AND @state_sg IS NOT NULL AND NOT EXISTS (SELECT 1 FROM mj_listings WHERE slug='hard-disk-data-recovery-service-singapore-demo');

INSERT INTO mj_listings
(user_id, company_id, category_id, country_id, state_id, city_id, timezone, listing_type, title, slug, description, price, currency_code, condition_label, brand, model, status, is_featured, is_urgent, views, quality_score, expires_at, published_at, custom_fields_json)
SELECT @seller_sg, @company_data, @cat_electronics, @country_sg, @state_sg, @city_cck, 'Asia/Singapore', 'product', 'Used Lenovo ThinkPad Business Laptop', 'used-lenovo-thinkpad-business-laptop-demo', 'Clean used Lenovo ThinkPad laptop for office and school. Demo listing only.', 399.00, 'SGD', 'Used - Good', 'Lenovo', 'ThinkPad', 'active', 0, 0, 245, 82, DATE_ADD(NOW(), INTERVAL 45 DAY), NOW(), '{"ram":"16GB","storage":"512GB SSD"}'
WHERE @cat_electronics IS NOT NULL AND @city_cck IS NOT NULL AND @company_data IS NOT NULL AND @country_sg IS NOT NULL AND @seller_sg IS NOT NULL AND @state_sg IS NOT NULL AND NOT EXISTS (SELECT 1 FROM mj_listings WHERE slug='used-lenovo-thinkpad-business-laptop-demo');

INSERT INTO mj_listings
(user_id, company_id, category_id, country_id, state_id, city_id, timezone, listing_type, title, slug, description, price, currency_code, condition_label, brand, model, status, is_featured, is_urgent, views, quality_score, expires_at, published_at, custom_fields_json)
SELECT @seller_my, @company_clean, @cat_home, @country_my, @state_kl, @city_kl, 'Asia/Kuala_Lumpur', 'service', 'Move In Move Out Cleaning Kuala Lumpur', 'move-in-move-out-cleaning-kuala-lumpur-demo', 'Professional cleaning before moving in or after moving out. Demo service listing.', 180.00, 'MYR', 'Service', 'KL Home Cleaning Pro', 'Cleaning', 'active', 1, 0, 390, 88, DATE_ADD(NOW(), INTERVAL 55 DAY), NOW(), '{"area":"Kuala Lumpur","team_size":"2-4"}'
WHERE @cat_home IS NOT NULL AND @city_kl IS NOT NULL AND @company_clean IS NOT NULL AND @country_my IS NOT NULL AND @seller_my IS NOT NULL AND @state_kl IS NOT NULL AND NOT EXISTS (SELECT 1 FROM mj_listings WHERE slug='move-in-move-out-cleaning-kuala-lumpur-demo');

INSERT INTO mj_listings
(user_id, company_id, category_id, country_id, state_id, city_id, timezone, listing_type, title, slug, description, price, currency_code, condition_label, brand, model, status, is_featured, is_urgent, views, quality_score, expires_at, published_at, custom_fields_json)
SELECT @seller_in, @company_beauty, @cat_home, @country_in, @state_tn, @city_chennai, 'Asia/Kolkata', 'service', 'Skin Care Consultation Chennai', 'skin-care-consultation-chennai-demo', 'Demo beauty and wellness consultation listing for company enquiry flow.', 1200.00, 'INR', 'Service', 'Chennai Beauty Clinic', 'Consultation', 'active', 0, 0, 158, 78, DATE_ADD(NOW(), INTERVAL 40 DAY), NOW(), '{"demo":"yes"}'
WHERE @cat_home IS NOT NULL AND @city_chennai IS NOT NULL AND @company_beauty IS NOT NULL AND @country_in IS NOT NULL AND @seller_in IS NOT NULL AND @state_tn IS NOT NULL AND NOT EXISTS (SELECT 1 FROM mj_listings WHERE slug='skin-care-consultation-chennai-demo');

SELECT id INTO @listing_data FROM mj_listings WHERE slug='hard-disk-data-recovery-service-singapore-demo' LIMIT 1;
SELECT id INTO @listing_laptop FROM mj_listings WHERE slug='used-lenovo-thinkpad-business-laptop-demo' LIMIT 1;
SELECT id INTO @listing_clean FROM mj_listings WHERE slug='move-in-move-out-cleaning-kuala-lumpur-demo' LIMIT 1;
SELECT id INTO @listing_beauty FROM mj_listings WHERE slug='skin-care-consultation-chennai-demo' LIMIT 1;

INSERT INTO mj_listing_images (listing_id, image_path, sort_order)
SELECT @listing_data, 'uploads/demo/listing-data-recovery.jpg', 1
WHERE @listing_data IS NOT NULL AND NOT EXISTS (SELECT 1 FROM mj_listing_images WHERE listing_id=@listing_data AND image_path='uploads/demo/listing-data-recovery.jpg');
INSERT INTO mj_listing_images (listing_id, image_path, sort_order)
SELECT @listing_laptop, 'uploads/demo/listing-laptop.jpg', 1
WHERE @listing_laptop IS NOT NULL AND NOT EXISTS (SELECT 1 FROM mj_listing_images WHERE listing_id=@listing_laptop AND image_path='uploads/demo/listing-laptop.jpg');
INSERT INTO mj_listing_images (listing_id, image_path, sort_order)
SELECT @listing_clean, 'uploads/demo/listing-cleaning.jpg', 1
WHERE @listing_clean IS NOT NULL AND NOT EXISTS (SELECT 1 FROM mj_listing_images WHERE listing_id=@listing_clean AND image_path='uploads/demo/listing-cleaning.jpg');

-- ------------------------------------------------------------
-- 6) Deals - enquiry/contact only, no customer-to-seller payment
-- ------------------------------------------------------------
INSERT INTO mj_deals
(user_id, company_id, category_id, country_id, city_id, deal_type, title, slug, short_description, description, image_path, original_price, deal_price, discount_percent, price_note, currency_code, start_at, end_at, status, is_featured, is_sponsored, featured_until, sponsored_until, contact_phone, contact_whatsapp, contact_email, terms, quality_score, view_count, enquiry_count, approved_by, approved_at)
SELECT @seller_sg, @company_data, @cat_data, @country_sg, @city_sg, 'service', 'Free Diagnosis for Data Recovery Case', 'free-diagnosis-data-recovery-singapore-demo', 'Submit enquiry for free diagnosis before recovery.', 'Demo deal. Customer sends enquiry only. Seller/user pays admin for sponsored/featured placement, not customer payment.', 'uploads/demo/deal-data-recovery.jpg', 80.00, 0.00, 100.00, 'Free diagnosis, recovery quote after inspection', 'SGD', NOW(), DATE_ADD(NOW(), INTERVAL 30 DAY), 'active', 1, 1, DATE_ADD(NOW(), INTERVAL 14 DAY), DATE_ADD(NOW(), INTERVAL 14 DAY), '+6581513060', '+6581513060', 'data.sg@mjmarketplace.test', 'Enquiry only. No online customer payment through marketplace.', 95, 455, 3, @admin_demo, NOW()
WHERE @admin_demo IS NOT NULL AND @cat_data IS NOT NULL AND @city_sg IS NOT NULL AND @company_data IS NOT NULL AND @country_sg IS NOT NULL AND @seller_sg IS NOT NULL AND NOT EXISTS (SELECT 1 FROM mj_deals WHERE slug='free-diagnosis-data-recovery-singapore-demo');

INSERT INTO mj_deals
(user_id, company_id, category_id, country_id, city_id, deal_type, title, slug, short_description, description, image_path, original_price, deal_price, discount_percent, price_note, currency_code, start_at, end_at, status, is_featured, is_sponsored, featured_until, sponsored_until, contact_phone, contact_whatsapp, contact_email, terms, quality_score, view_count, enquiry_count, approved_by, approved_at)
SELECT @seller_my, @company_clean, @cat_home, @country_my, @city_kl, 'service', '4 Hours Home Cleaning Promo KL', 'four-hours-home-cleaning-promo-kl-demo', 'Send enquiry for promotional cleaning slot.', 'Demo deal for lead/contact flow only. Customer contacts seller, no customer-to-seller payment module.', 'uploads/demo/deal-cleaning.jpg', 180.00, 120.00, 33.33, 'Pay directly offline after confirmation if seller allows; platform only handles enquiry.', 'MYR', NOW(), DATE_ADD(NOW(), INTERVAL 45 DAY), 'active', 1, 0, DATE_ADD(NOW(), INTERVAL 10 DAY), NULL, '+60123456789', '+60123456789', 'cleaning.kl@mjmarketplace.test', 'Subject to slot availability.', 88, 310, 2, @admin_demo, NOW()
WHERE @admin_demo IS NOT NULL AND @cat_home IS NOT NULL AND @city_kl IS NOT NULL AND @company_clean IS NOT NULL AND @country_my IS NOT NULL AND @seller_my IS NOT NULL AND NOT EXISTS (SELECT 1 FROM mj_deals WHERE slug='four-hours-home-cleaning-promo-kl-demo');

INSERT INTO mj_deals
(user_id, company_id, category_id, country_id, city_id, deal_type, title, slug, short_description, description, image_path, original_price, deal_price, discount_percent, price_note, currency_code, start_at, end_at, status, is_featured, is_sponsored, featured_until, sponsored_until, contact_phone, contact_whatsapp, contact_email, terms, quality_score, view_count, enquiry_count, approved_by, approved_at)
SELECT @seller_in, @company_beauty, @cat_company_beauty, @country_in, @city_chennai, 'service', 'Skin Consultation Intro Offer', 'skin-consultation-intro-offer-chennai-demo', 'Intro offer for clinic enquiry testing.', 'Demo deal for company detail and lead enquiry flow.', 'uploads/demo/deal-beauty.jpg', 2000.00, 1200.00, 40.00, 'Enquiry first, appointment confirmation by clinic.', 'INR', NOW(), DATE_ADD(NOW(), INTERVAL 30 DAY), 'active', 0, 0, NULL, NULL, '+919876543210', '+919876543210', 'beauty.chennai@mjmarketplace.test', 'Clinic confirmation required.', 80, 190, 1, @admin_demo, NOW()
WHERE @admin_demo IS NOT NULL AND @cat_company_beauty IS NOT NULL AND @city_chennai IS NOT NULL AND @company_beauty IS NOT NULL AND @country_in IS NOT NULL AND @seller_in IS NOT NULL AND NOT EXISTS (SELECT 1 FROM mj_deals WHERE slug='skin-consultation-intro-offer-chennai-demo');

SELECT id INTO @deal_data FROM mj_deals WHERE slug='free-diagnosis-data-recovery-singapore-demo' LIMIT 1;
SELECT id INTO @deal_clean FROM mj_deals WHERE slug='four-hours-home-cleaning-promo-kl-demo' LIMIT 1;
SELECT id INTO @deal_beauty FROM mj_deals WHERE slug='skin-consultation-intro-offer-chennai-demo' LIMIT 1;

-- ------------------------------------------------------------
-- 7) Enquiries + unified seller leads + CRM customers
-- ------------------------------------------------------------
INSERT INTO mj_listing_enquiries
(listing_id, seller_user_id, company_id, customer_user_id, name, email, phone, preferred_contact, message, status, ip_address, user_agent)
SELECT @listing_data, @seller_sg, @company_data, @buyer_demo, 'John Tan', 'john.tan.demo@example.com', '+6591112222', 'whatsapp', 'I need recovery for a clicking hard disk. Please contact me.', 'new', '127.0.0.1', 'MJ Sample Data'
WHERE @buyer_demo IS NOT NULL AND @company_data IS NOT NULL AND @listing_data IS NOT NULL AND @seller_sg IS NOT NULL AND NOT EXISTS (SELECT 1 FROM mj_listing_enquiries WHERE listing_id=@listing_data AND email='john.tan.demo@example.com');

INSERT INTO mj_listing_enquiries
(listing_id, seller_user_id, company_id, customer_user_id, name, email, phone, preferred_contact, message, status, ip_address, user_agent)
SELECT @listing_clean, @seller_my, @company_clean, NULL, 'Aisyah Demo', 'aisyah.demo@example.com', '+60198887777', 'phone', 'Need move out cleaning next weekend.', 'contacted', '127.0.0.1', 'MJ Sample Data'
WHERE @company_clean IS NOT NULL AND @listing_clean IS NOT NULL AND @seller_my IS NOT NULL AND NOT EXISTS (SELECT 1 FROM mj_listing_enquiries WHERE listing_id=@listing_clean AND email='aisyah.demo@example.com');

INSERT INTO mj_company_enquiries
(company_id, owner_user_id, customer_user_id, name, email, phone, service_interested, preferred_contact, message, status, ip_address, user_agent)
SELECT @company_data, @seller_sg, @buyer_demo, 'Ravi Kumar', 'ravi.demo@example.com', '+6582223333', 'NAS Recovery', 'email', 'Can you recover data from Synology NAS?', 'new', '127.0.0.1', 'MJ Sample Data'
WHERE @buyer_demo IS NOT NULL AND @company_data IS NOT NULL AND @seller_sg IS NOT NULL AND NOT EXISTS (SELECT 1 FROM mj_company_enquiries WHERE company_id=@company_data AND email='ravi.demo@example.com');

INSERT INTO mj_deal_enquiries
(deal_id, seller_user_id, company_id, customer_user_id, name, email, phone, preferred_datetime, message, status, source, ip_address, user_agent)
SELECT @deal_data, @seller_sg, @company_data, @buyer_demo, 'Nur Aini', 'nur.demo@example.com', '+6583334444', 'Tomorrow 3 PM', 'I want to check my external drive under free diagnosis.', 'new', 'deal_page', '127.0.0.1', 'MJ Sample Data'
WHERE @buyer_demo IS NOT NULL AND @company_data IS NOT NULL AND @deal_data IS NOT NULL AND @seller_sg IS NOT NULL AND NOT EXISTS (SELECT 1 FROM mj_deal_enquiries WHERE deal_id=@deal_data AND email='nur.demo@example.com');

INSERT INTO mj_deal_enquiries
(deal_id, seller_user_id, company_id, customer_user_id, name, email, phone, preferred_datetime, message, status, source, ip_address, user_agent)
SELECT @deal_clean, @seller_my, @company_clean, NULL, 'Lim Demo', 'lim.demo@example.com', '+60175556666', 'Saturday morning', 'Is the 4-hour cleaning promo available for condo?', 'new', 'deal_page', '127.0.0.1', 'MJ Sample Data'
WHERE @company_clean IS NOT NULL AND @deal_clean IS NOT NULL AND @seller_my IS NOT NULL AND NOT EXISTS (SELECT 1 FROM mj_deal_enquiries WHERE deal_id=@deal_clean AND email='lim.demo@example.com');

INSERT INTO seller_leads
(seller_user_id, source_type, source_id, source_title, country, customer_name, customer_email, customer_phone, customer_message, status, priority, followup_at, last_activity_at, is_read, ip_address, user_agent)
SELECT @seller_sg, 'listing', @listing_data, 'Hard Disk Data Recovery Service Singapore', 'Singapore', 'John Tan', 'john.tan.demo@example.com', '+6591112222', 'I need recovery for a clicking hard disk. Please contact me.', 'new', 'high', DATE_ADD(NOW(), INTERVAL 1 DAY), NOW(), 0, '127.0.0.1', 'MJ Sample Data'
WHERE @listing_data IS NOT NULL AND @seller_sg IS NOT NULL AND NOT EXISTS (SELECT 1 FROM seller_leads WHERE seller_user_id=@seller_sg AND source_type='listing' AND source_id=@listing_data AND customer_email='john.tan.demo@example.com');

INSERT INTO seller_leads
(seller_user_id, source_type, source_id, source_title, country, customer_name, customer_email, customer_phone, customer_message, status, priority, followup_at, last_activity_at, is_read, ip_address, user_agent)
SELECT @seller_sg, 'company', @company_data, 'MJ Data Recovery Singapore', 'Singapore', 'Ravi Kumar', 'ravi.demo@example.com', '+6582223333', 'Can you recover data from Synology NAS?', 'new', 'normal', DATE_ADD(NOW(), INTERVAL 2 DAY), NOW(), 0, '127.0.0.1', 'MJ Sample Data'
WHERE @company_data IS NOT NULL AND @seller_sg IS NOT NULL AND NOT EXISTS (SELECT 1 FROM seller_leads WHERE seller_user_id=@seller_sg AND source_type='company' AND source_id=@company_data AND customer_email='ravi.demo@example.com');

INSERT INTO seller_leads
(seller_user_id, source_type, source_id, source_title, country, customer_name, customer_email, customer_phone, customer_message, status, priority, followup_at, last_activity_at, is_read, ip_address, user_agent)
SELECT @seller_sg, 'deal', @deal_data, 'Free Diagnosis for Data Recovery Case', 'Singapore', 'Nur Aini', 'nur.demo@example.com', '+6583334444', 'I want to check my external drive under free diagnosis.', 'new', 'high', DATE_ADD(NOW(), INTERVAL 1 DAY), NOW(), 0, '127.0.0.1', 'MJ Sample Data'
WHERE @deal_data IS NOT NULL AND @seller_sg IS NOT NULL AND NOT EXISTS (SELECT 1 FROM seller_leads WHERE seller_user_id=@seller_sg AND source_type='deal' AND source_id=@deal_data AND customer_email='nur.demo@example.com');

INSERT INTO seller_leads
(seller_user_id, source_type, source_id, source_title, country, customer_name, customer_email, customer_phone, customer_message, status, priority, followup_at, last_activity_at, is_read, ip_address, user_agent)
SELECT @seller_my, 'deal', @deal_clean, '4 Hours Home Cleaning Promo KL', 'Malaysia', 'Lim Demo', 'lim.demo@example.com', '+60175556666', 'Is the 4-hour cleaning promo available for condo?', 'new', 'normal', DATE_ADD(NOW(), INTERVAL 3 DAY), NOW(), 0, '127.0.0.1', 'MJ Sample Data'
WHERE @deal_clean IS NOT NULL AND @seller_my IS NOT NULL AND NOT EXISTS (SELECT 1 FROM seller_leads WHERE seller_user_id=@seller_my AND source_type='deal' AND source_id=@deal_clean AND customer_email='lim.demo@example.com');

INSERT INTO seller_leads
(seller_user_id, source_type, source_id, source_title, country, customer_name, customer_email, customer_phone, customer_message, status, priority, followup_at, last_activity_at, is_read, ip_address, user_agent)
SELECT @seller_my, 'listing', @listing_clean, 'Move In Move Out Cleaning Kuala Lumpur', 'Malaysia', 'Aisyah Demo', 'aisyah.demo@example.com', '+60198887777', 'Need move out cleaning next weekend.', 'contacted', 'normal', DATE_ADD(NOW(), INTERVAL 4 DAY), NOW(), 1, '127.0.0.1', 'MJ Sample Data'
WHERE @listing_clean IS NOT NULL AND @seller_my IS NOT NULL AND NOT EXISTS (SELECT 1 FROM seller_leads WHERE seller_user_id=@seller_my AND source_type='listing' AND source_id=@listing_clean AND customer_email='aisyah.demo@example.com');

SELECT id INTO @lead_john FROM seller_leads WHERE seller_user_id=@seller_sg AND customer_email='john.tan.demo@example.com' LIMIT 1;
SELECT id INTO @lead_ravi FROM seller_leads WHERE seller_user_id=@seller_sg AND customer_email='ravi.demo@example.com' LIMIT 1;
SELECT id INTO @lead_nur FROM seller_leads WHERE seller_user_id=@seller_sg AND customer_email='nur.demo@example.com' LIMIT 1;
SELECT id INTO @lead_lim FROM seller_leads WHERE seller_user_id=@seller_my AND customer_email='lim.demo@example.com' LIMIT 1;

INSERT INTO seller_lead_activities (lead_id, user_id, user_type, activity_type, note, old_status, new_status, followup_at)
SELECT @lead_john, @seller_sg, 'user', 'note', 'Demo lead created from listing enquiry. Follow up by WhatsApp.', NULL, 'new', DATE_ADD(NOW(), INTERVAL 1 DAY)
WHERE @lead_john IS NOT NULL AND NOT EXISTS (SELECT 1 FROM seller_lead_activities WHERE lead_id=@lead_john AND note='Demo lead created from listing enquiry. Follow up by WhatsApp.');

INSERT INTO seller_lead_activities (lead_id, user_id, user_type, activity_type, note, old_status, new_status, followup_at)
SELECT @lead_lim, @seller_my, 'user', 'status_change', 'Demo contacted lead for cleaning promo.', 'new', 'contacted', DATE_ADD(NOW(), INTERVAL 4 DAY)
WHERE @lead_lim IS NOT NULL AND NOT EXISTS (SELECT 1 FROM seller_lead_activities WHERE lead_id=@lead_lim AND note='Demo contacted lead for cleaning promo.');

INSERT INTO mj_customers
(owner_user_id, company_id, customer_user_id, name, email, phone, whatsapp, country_id, city_id, source_type, source_id, source_label, status, lead_score, tags, last_activity_at, next_followup_at, private_note)
VALUES
(@seller_sg, @company_data, @buyer_demo, 'John Tan', 'john.tan.demo@example.com', '+6591112222', '+6591112222', @country_sg, @city_sg, 'listing', @listing_data, 'Hard Disk Data Recovery Service Singapore', 'new', 80, 'demo,hot,data-recovery', NOW(), DATE_ADD(NOW(), INTERVAL 1 DAY), 'Demo CRM customer from listing enquiry.'),
(@seller_sg, @company_data, @buyer_demo, 'Ravi Kumar', 'ravi.demo@example.com', '+6582223333', '+6582223333', @country_sg, @city_sg, 'company', @company_data, 'MJ Data Recovery Singapore', 'new', 70, 'demo,nas', NOW(), DATE_ADD(NOW(), INTERVAL 2 DAY), 'Demo CRM customer from company enquiry.'),
(@seller_my, @company_clean, NULL, 'Lim Demo', 'lim.demo@example.com', '+60175556666', '+60175556666', @country_my, @city_kl, 'deal', @deal_clean, '4 Hours Home Cleaning Promo KL', 'new', 65, 'demo,cleaning', NOW(), DATE_ADD(NOW(), INTERVAL 3 DAY), 'Demo CRM customer from deal enquiry.')
ON DUPLICATE KEY UPDATE status=VALUES(status), lead_score=VALUES(lead_score), last_activity_at=VALUES(last_activity_at), next_followup_at=VALUES(next_followup_at), private_note=VALUES(private_note);

-- ------------------------------------------------------------
-- 8) Chat sample
-- ------------------------------------------------------------
INSERT INTO mj_chats (listing_id, company_id, buyer_id, seller_id, last_message, last_message_at, status)
SELECT @listing_data, NULL, @buyer_demo, @seller_sg, 'Hi, can you check my hard disk today?', NOW(), 'active'
WHERE @buyer_demo IS NOT NULL AND @listing_data IS NOT NULL AND @seller_sg IS NOT NULL AND NOT EXISTS (SELECT 1 FROM mj_chats WHERE listing_id=@listing_data AND buyer_id=@buyer_demo AND seller_id=@seller_sg);

SELECT id INTO @chat_data FROM mj_chats WHERE listing_id=@listing_data AND buyer_id=@buyer_demo AND seller_id=@seller_sg LIMIT 1;

INSERT INTO mj_chat_messages (chat_id, sender_id, message, risk_score, is_read)
SELECT @chat_data, @buyer_demo, 'Hi, can you check my hard disk today?', 0, 0
WHERE @chat_data IS NOT NULL AND NOT EXISTS (SELECT 1 FROM mj_chat_messages WHERE chat_id=@chat_data AND sender_id=@buyer_demo AND message='Hi, can you check my hard disk today?');

-- ------------------------------------------------------------
-- 9) Notifications for user and admin
-- ------------------------------------------------------------
INSERT INTO mj_notifications
(user_id, admin_id, user_type, title, message, body, type, priority, link, action_url, url, is_read, is_popup_shown, meta_json)
SELECT @seller_sg, NULL, 'user', 'New lead received', 'John Tan sent an enquiry for Hard Disk Data Recovery Service Singapore.', 'John Tan sent an enquiry for Hard Disk Data Recovery Service Singapore.', 'lead_new', 'high', 'my-leads.php', 'my-leads.php', 'my-leads.php', 0, 0, '{"sample":"yes","source":"listing"}'
WHERE @seller_sg IS NOT NULL AND NOT EXISTS (SELECT 1 FROM mj_notifications WHERE user_id=@seller_sg AND title='New lead received' AND type='lead_new' AND message LIKE 'John Tan%');

INSERT INTO mj_notifications
(user_id, admin_id, user_type, title, message, body, type, priority, link, action_url, url, is_read, is_popup_shown, meta_json)
SELECT @seller_sg, NULL, 'user', 'New deal enquiry', 'Nur Aini sent an enquiry for Free Diagnosis for Data Recovery Case.', 'Nur Aini sent an enquiry for Free Diagnosis for Data Recovery Case.', 'deal_enquiry', 'high', 'my-leads.php', 'my-leads.php', 'my-leads.php', 0, 0, '{"sample":"yes","source":"deal"}'
WHERE @seller_sg IS NOT NULL AND NOT EXISTS (SELECT 1 FROM mj_notifications WHERE user_id=@seller_sg AND title='New deal enquiry' AND type='deal_enquiry' AND message LIKE 'Nur Aini%');

INSERT INTO mj_notifications
(user_id, admin_id, user_type, title, message, body, type, priority, link, action_url, url, is_read, is_popup_shown, meta_json)
SELECT @seller_my, NULL, 'user', 'New cleaning deal enquiry', 'Lim Demo sent an enquiry for 4 Hours Home Cleaning Promo KL.', 'Lim Demo sent an enquiry for 4 Hours Home Cleaning Promo KL.', 'deal_enquiry', 'normal', 'my-leads.php', 'my-leads.php', 'my-leads.php', 0, 0, '{"sample":"yes","source":"deal"}'
WHERE @seller_my IS NOT NULL AND NOT EXISTS (SELECT 1 FROM mj_notifications WHERE user_id=@seller_my AND title='New cleaning deal enquiry' AND type='deal_enquiry' AND message LIKE 'Lim Demo%');

INSERT INTO mj_notifications
(user_id, admin_id, user_type, title, message, body, type, priority, link, action_url, url, is_read, is_popup_shown, meta_json)
SELECT @admin_demo, @admin_demo, 'admin', 'Admin: new marketplace lead', 'A new listing lead was created for MJ Data Recovery Singapore.', 'A new listing lead was created for MJ Data Recovery Singapore.', 'admin_lead_new', 'normal', 'admin/leads.php', 'admin/leads.php', 'admin/leads.php', 0, 0, '{"sample":"yes"}'
WHERE @admin_demo IS NOT NULL AND NOT EXISTS (SELECT 1 FROM mj_notifications WHERE admin_id=@admin_demo AND title='Admin: new marketplace lead' AND type='admin_lead_new');

-- ------------------------------------------------------------
-- 10) Sample account preferences / wallets / subscription foundation
-- ------------------------------------------------------------
INSERT INTO mj_wallets (user_id, balance, currency_code)
VALUES (@seller_sg, 120.00, 'SGD'), (@seller_my, 250.00, 'MYR'), (@seller_in, 5000.00, 'INR')
ON DUPLICATE KEY UPDATE balance=VALUES(balance), currency_code=VALUES(currency_code);

INSERT INTO mj_subscriptions (user_id, company_id, plan_name, status, starts_at, ends_at)
SELECT @seller_sg, @company_data, 'Demo Seller Pro Plan', 'active', NOW(), DATE_ADD(NOW(), INTERVAL 30 DAY)
WHERE @company_data IS NOT NULL AND @seller_sg IS NOT NULL AND NOT EXISTS (SELECT 1 FROM mj_subscriptions WHERE user_id=@seller_sg AND company_id=@company_data AND plan_name='Demo Seller Pro Plan');

INSERT INTO mj_subscriptions (user_id, company_id, plan_name, status, starts_at, ends_at)
SELECT @seller_my, @company_clean, 'Demo Company Basic Plan', 'active', NOW(), DATE_ADD(NOW(), INTERVAL 30 DAY)
WHERE @company_clean IS NOT NULL AND @seller_my IS NOT NULL AND NOT EXISTS (SELECT 1 FROM mj_subscriptions WHERE user_id=@seller_my AND company_id=@company_clean AND plan_name='Demo Company Basic Plan');

SET FOREIGN_KEY_CHECKS = 1;

-- ------------------------------------------------------------
-- Demo summary
-- ------------------------------------------------------------
SELECT 'MJ Marketplace v3.0.4.1 sample data installed' AS result;
SELECT 'Demo password for all sample users/admin: Demo@123456' AS demo_password;
SELECT 'Admin login: admin.demo@mjmarketplace.test' AS admin_login;
SELECT 'Seller SG: seller.sg@mjmarketplace.test | Seller MY: seller.my@mjmarketplace.test | Buyer: buyer.demo@mjmarketplace.test' AS demo_users;
