-- ELITES BUSINESS LISTING operational modules
CREATE TABLE IF NOT EXISTS businesses (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, owner_id INT UNSIGNED NOT NULL,
 name VARCHAR(180) NOT NULL, category VARCHAR(120), location VARCHAR(255), phone VARCHAR(50),
 description TEXT, status VARCHAR(30) NOT NULL DEFAULT 'pending', plan VARCHAR(40) NOT NULL DEFAULT 'Free',
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 INDEX(owner_id,status)
);
CREATE TABLE IF NOT EXISTS business_views (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,business_id BIGINT UNSIGNED NOT NULL,viewer_user_id INT UNSIGNED NULL,created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,INDEX(business_id,created_at));
CREATE TABLE IF NOT EXISTS business_enquiries (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,business_id BIGINT UNSIGNED NOT NULL,user_id INT UNSIGNED NULL,subject VARCHAR(180),message TEXT NOT NULL,status VARCHAR(30) DEFAULT 'new',created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,INDEX(business_id,status,created_at));
CREATE TABLE IF NOT EXISTS reviews (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,business_id BIGINT UNSIGNED NOT NULL,user_id INT UNSIGNED NULL,rating TINYINT UNSIGNED NOT NULL,review TEXT NOT NULL,status VARCHAR(30) DEFAULT 'pending',created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,INDEX(business_id,status));
CREATE TABLE IF NOT EXISTS business_products (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,business_id BIGINT UNSIGNED NOT NULL,name VARCHAR(180) NOT NULL,description TEXT,price DECIMAL(14,2),currency CHAR(3) DEFAULT 'NGN',image_url VARCHAR(500),active TINYINT(1) DEFAULT 1,created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP);
CREATE TABLE IF NOT EXISTS subscriptions (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,user_id INT UNSIGNED NOT NULL,business_id BIGINT UNSIGNED NULL,plan VARCHAR(40) NOT NULL,status VARCHAR(30) DEFAULT 'active',starts_at DATETIME NOT NULL,ends_at DATETIME NULL,payment_reference VARCHAR(120),created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,INDEX(user_id,status),INDEX(business_id,status));