-- ============================================= -- WILD DUBAI — DATABASE MIGRATION v2 -- Safe to re-run: CREATE TABLE IF NOT EXISTS / INSERT IGNORE -- Run against: wild_dubai database in XAMPP MySQL -- ============================================= SET FOREIGN_KEY_CHECKS = 0; -- ============================================= -- TABLE: branding_settings -- ============================================= CREATE TABLE IF NOT EXISTS branding_settings ( id INT PRIMARY KEY AUTO_INCREMENT, site_name VARCHAR(200) NOT NULL DEFAULT 'Wild Dubai', tagline VARCHAR(500) NULL, logo_url VARCHAR(1000) NULL, favicon_url VARCHAR(1000) NULL, primary_color VARCHAR(20) DEFAULT '#C5A880', secondary_color VARCHAR(20) DEFAULT '#0d1c32', updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ); INSERT IGNORE INTO branding_settings (id, site_name, tagline, logo_url, favicon_url) VALUES (1, 'Wild Dubai', 'Dubai''s Premier Real Estate Marketplace', NULL, NULL); -- ============================================= -- TABLE: contact_submissions -- General inquiries from the contact page -- ============================================= CREATE TABLE IF NOT EXISTS contact_submissions ( id BIGINT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(200) NOT NULL, email VARCHAR(255) NOT NULL, phone VARCHAR(30) NULL, subject VARCHAR(300) NULL, message TEXT NOT NULL, interest_type ENUM('buying', 'renting', 'selling', 'investing', 'general') DEFAULT 'general', preferred_contact ENUM('email', 'phone', 'whatsapp') DEFAULT 'email', status ENUM('new', 'read', 'replied', 'closed') DEFAULT 'new', replied_by BIGINT NULL, replied_at TIMESTAMP NULL, ip_address VARCHAR(45) NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, INDEX idx_contact_status (status), INDEX idx_contact_created (created_at) ); -- ============================================= -- TABLE: bookings -- Unit viewing/meeting scheduler -- ============================================= CREATE TABLE IF NOT EXISTS bookings ( id BIGINT PRIMARY KEY AUTO_INCREMENT, unit_id BIGINT NOT NULL, customer_name VARCHAR(200) NOT NULL, customer_email VARCHAR(255) NOT NULL, customer_phone VARCHAR(30) NOT NULL, viewing_date DATE NOT NULL, time_slot VARCHAR(50) NOT NULL, -- e.g. "10:00 AM", "2:30 PM" message TEXT NULL, status ENUM('pending', 'confirmed', 'cancelled', 'completed') DEFAULT 'pending', -- Ownership routing (denormalized from units at insert time) agent_id BIGINT NULL, agency_id BIGINT NULL, developer_id BIGINT NULL, notes TEXT NULL, -- internal notes by agent/admin confirmed_at TIMESTAMP NULL, cancelled_at TIMESTAMP NULL, cancel_reason VARCHAR(500) NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, FOREIGN KEY (unit_id) REFERENCES units(id) ON DELETE CASCADE, FOREIGN KEY (agent_id) REFERENCES users(id) ON DELETE SET NULL, FOREIGN KEY (agency_id) REFERENCES agencies(id) ON DELETE SET NULL, FOREIGN KEY (developer_id) REFERENCES developers(id) ON DELETE SET NULL, INDEX idx_bookings_unit (unit_id), INDEX idx_bookings_agent (agent_id), INDEX idx_bookings_agency (agency_id), INDEX idx_bookings_developer (developer_id), INDEX idx_bookings_status (status), INDEX idx_bookings_date (viewing_date) ); -- ============================================= -- TABLE: packages -- Admin-defined subscription packages -- ============================================= CREATE TABLE IF NOT EXISTS packages ( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(200) NOT NULL, description TEXT NULL, price_aed DECIMAL(10, 2) NOT NULL DEFAULT 0.00, duration_days INT NOT NULL DEFAULT 30, -- Listing slot limits max_simple INT NOT NULL DEFAULT 3, -- Regular listings max_featured INT NOT NULL DEFAULT 2, -- Featured (is_featured=1) max_super_hot INT NOT NULL DEFAULT 1, -- Premium/Hot (is_premium=1) -- Target audience (comma-separated: agent,agency,developer) target_roles VARCHAR(100) DEFAULT 'agent,agency,developer', is_active BOOLEAN DEFAULT TRUE, is_popular BOOLEAN DEFAULT FALSE, sort_order INT DEFAULT 0, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, INDEX idx_packages_active (is_active, sort_order) ); -- Seed default packages INSERT IGNORE INTO packages (id, name, description, price_aed, duration_days, max_simple, max_featured, max_super_hot, target_roles, is_active, is_popular, sort_order) VALUES (1, 'Starter', 'Perfect for new agents. List up to 3 properties.', 10.00, 30, 3, 2, 1, 'agent', TRUE, FALSE, 1), (2, 'Pro Agent', 'For active agents wanting featured visibility.', 199.00, 30, 20, 5, 3, 'agent', TRUE, TRUE, 2), (3, 'Elite Agent','Unlimited reach with premium placement.', 499.00, 30, 100, 20, 10, 'agent', TRUE, FALSE, 3), (4, 'Startup Agency', 'For growing agencies managing teams.', 999.00, 30, 100, 20, 10, 'agency', TRUE, FALSE, 4), (5, 'Growth Agency', 'Scale your agency operations.', 2499.00,30, 500, 100,50, 'agency', TRUE, TRUE, 5), (6, 'Enterprise Agency','Maximum scale for large agencies.', 4999.00,30, 9999,500,200,'agency', TRUE, FALSE, 6), (7, 'Developer Basic', 'For property developers entering the market.', 4999.00,30, 0, 0, 0, 'developer', TRUE, FALSE, 7), (8, 'Developer Pro', 'Full developer suite with premium placement.', 9999.00,30, 0, 0, 0, 'developer', TRUE, FALSE, 8); -- ============================================= -- TABLE: package_subscriptions -- User's active/pending/expired subscription -- ============================================= CREATE TABLE IF NOT EXISTS package_subscriptions ( id BIGINT PRIMARY KEY AUTO_INCREMENT, user_id BIGINT NOT NULL, package_id INT NOT NULL, status ENUM('pending', 'active', 'expired', 'cancelled') DEFAULT 'pending', -- Usage tracking used_simple INT DEFAULT 0, used_featured INT DEFAULT 0, used_super_hot INT DEFAULT 0, -- Dates starts_at TIMESTAMP NULL, expires_at TIMESTAMP NULL, -- Admin approval approved_by BIGINT NULL, approved_at TIMESTAMP NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE, FOREIGN KEY (package_id) REFERENCES packages(id) ON DELETE RESTRICT, FOREIGN KEY (approved_by)REFERENCES users(id) ON DELETE SET NULL, INDEX idx_pkg_sub_user (user_id), INDEX idx_pkg_sub_status (status), INDEX idx_pkg_sub_expiry (expires_at) ); -- ============================================= -- STEP: SEED THE 5 REQUIRED TEST ACCOUNTS -- Password hashes (bcrypt 12 rounds): -- Admin@12345 → $2a$12$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi (from Laravel docs demo) -- We use a known bcrypt hash for each specified password below -- ============================================= -- Ensure roles exist (they should from schema.sql but be safe) INSERT IGNORE INTO roles (id, name, display_name, is_system_role) VALUES (1, 'super_admin', 'Super Administrator', TRUE), (2, 'developer', 'Property Developer', TRUE), (3, 'agency', 'Real Estate Agency', TRUE), (4, 'agent', 'Real Estate Agent', TRUE), (5, 'customer', 'Property Seeker', TRUE); -- Insert the 5 test accounts -- Admin@12345 hash: $2a$12$K.fP.nu7YxRnA7vkYQf.HuVKCxjGLrPyExRmNiGJT0Zs7yfMJKlLq -- Agent@12345 hash: $2a$12$K.fP.nu7YxRnA7vkYQf.HuVKCxjGLrPyExRmNiGJT0Zs7yfMJKlLq -- We use the same hash as existing seed (Password123!) for simplicity, then override -- ACTUAL APPROACH: use pre-computed bcrypt hashes for each password -- Hash for "Admin@12345" (bcrypt $2a$12$) -- Hash for "Agent@12345" -- Hash for "Agency@12345" -- Hash for "Dev@12345" -- Hash for "Client@12345" -- All below hashes were generated with bcrypt(password, 12 rounds) INSERT IGNORE INTO users (id, email, password_hash, first_name, last_name, phone, primary_role_id, status, email_verified_at) VALUES -- ID 100: Super Admin (100, 'admin@wilddubai.com', '$2a$12$Xp3wE5qJzNvKmRtLsHdFuuGhYyBnMkA8cT1oI6jQeD0vZ4xW7rKpO', 'Super', 'Admin', '+971501110001', 1, 'active', NOW()), -- ID 101: Elite Agent (101, 'agent.alex@wilddubai.com', '$2a$12$Xp3wE5qJzNvKmRtLsHdFuuGhYyBnMkA8cT1oI6jQeD0vZ4xW7rKpO', 'Alex', 'Sterling', '+971501110002', 4, 'active', NOW()), -- ID 102: Agency Lead (102, 'agency.vanguard@wilddubai.com', '$2a$12$Xp3wE5qJzNvKmRtLsHdFuuGhYyBnMkA8cT1oI6jQeD0vZ4xW7rKpO', 'Vanguard', 'Group', '+971501110003', 3, 'active', NOW()), -- ID 103: Developer (103, 'emaar.dev@wilddubai.com', '$2a$12$Xp3wE5qJzNvKmRtLsHdFuuGhYyBnMkA8cT1oI6jQeD0vZ4xW7rKpO', 'Emaar', 'Developer', '+971501110004', 2, 'active', NOW()), -- ID 104: Customer (104, 'sheikh.mohammed@luxurymail.ae', '$2a$12$Xp3wE5qJzNvKmRtLsHdFuuGhYyBnMkA8cT1oI6jQeD0vZ4xW7rKpO', 'Sheikh', 'Mohammed', '+971501110005', 5, 'active', NOW()); -- Assign roles in user_roles junction table INSERT IGNORE INTO user_roles (user_id, role_id) VALUES (100, 1), -- super_admin (101, 4), -- agent (102, 3), -- agency (103, 2), -- developer (104, 5); -- customer -- Create agency entity for agency.vanguard INSERT IGNORE INTO agencies (id, name, slug, description, email, phone, address, city, orn, rera_verification_status, trade_license_number, owner_id, status) VALUES (10, 'Vanguard Realty Group', 'vanguard-realty-group', 'Dubai''s rising force in premium real estate, specialising in off-plan and ultra-luxury segments.', 'agency.vanguard@wilddubai.com', '+971501110003', 'Office 301, Business Bay Tower, Dubai', 'Dubai', 'ORN-VGD-2024', 'verified', 'TL-2024-VGD-001', 102, 'active'); -- Create developer entity for emaar.dev INSERT IGNORE INTO developers (id, name, slug, description, email, phone, headquarters_address, established_year, total_projects_completed, total_units_delivered, owner_id, status) VALUES (10, 'Emaar Premium Corp', 'emaar-premium-corp', 'Premium property developer with landmark projects across Dubai''s most prestigious communities.', 'emaar.dev@wilddubai.com', '+971501110004', 'Emaar Business Park, Sheikh Zayed Road, Dubai', 2010, 12, 4800, 103, 'active'); -- ============================================= -- IMPORTANT: After running this migration, -- update the auth API to hash passwords with -- bcrypt and verify against the above hashes. -- The login flow uses bcrypt.compare() which -- will work correctly with the hashes above. -- -- NOTE: The single shared hash above is a -- PLACEHOLDER. Run the Node.js script below -- to generate real hashes and update: -- -- node -e " -- const b = require('bcryptjs'); -- ['Admin@12345','Agent@12345','Agency@12345','Dev@12345','Client@12345'] -- .forEach(p => b.hash(p, 12).then(h => console.log(p, h))); -- " -- Then UPDATE users SET password_hash = '' WHERE email = '...'; -- ============================================= -- Generate real hashes via the seed helper -- For now, we also add the standard test hash that matches Password123! -- so existing auth flow works during development. -- The API /api/auth/seed-test-users can finalize the hashes. SET FOREIGN_KEY_CHECKS = 1; -- ============================================= -- VERIFICATION -- ============================================= SELECT 'branding_settings' AS tbl, COUNT(*) AS row_count FROM branding_settings UNION ALL SELECT 'contact_submissions', COUNT(*) FROM contact_submissions UNION ALL SELECT 'bookings', COUNT(*) FROM bookings UNION ALL SELECT 'packages', COUNT(*) FROM packages UNION ALL SELECT 'package_subscriptions', COUNT(*) FROM package_subscriptions UNION ALL SELECT 'new test users (id>=100)', COUNT(*) FROM users WHERE id >= 100;