-- ============================================================
-- Ragreen - Migration v2: missing-parts batch
-- Additive only, fully idempotent - safe to run on your existing
-- production database, and safe to re-run if interrupted or repeated.
-- Import via phpMyAdmin: select playpulse_ragreen, then Import this file.
--
-- Written using the information_schema + PREPARE/EXECUTE pattern instead
-- of "ADD COLUMN IF NOT EXISTS" - that syntax needs MySQL 8.0.29+ / newer
-- MariaDB and isn't supported on many shared-hosting MySQL versions. This
-- pattern works on effectively any MySQL 5.6+/MariaDB server.
-- ============================================================

-- ---------- 1. Generic admin-configurable settings (rewards formula, market scope, OAuth keys) ----------
CREATE TABLE IF NOT EXISTS settings (
    setting_key   VARCHAR(100) PRIMARY KEY,
    setting_value TEXT,
    updated_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

INSERT IGNORE INTO settings (setting_key, setting_value) VALUES
  ('points_per_dollar_spent', '1'),
  ('points_per_new_listing', '2'),
  ('market_country', ''),
  ('market_city', ''),
  ('oauth_google_client_id', ''),
  ('oauth_google_client_secret', ''),
  ('oauth_facebook_app_id', ''),
  ('oauth_facebook_app_secret', '');

-- ---------- 2. Categories: admin CRUD needs a soft-delete flag ----------
SET @dbname = DATABASE();

SET @stmt = (SELECT IF(
  (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = @dbname AND TABLE_NAME = 'categories' AND COLUMN_NAME = 'is_active') = 0,
  'ALTER TABLE categories ADD COLUMN is_active TINYINT(1) NOT NULL DEFAULT 1',
  'SELECT 1'
));
PREPARE stmt FROM @stmt; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- ---------- 3. Geo: structured country/city on profiles and products ----------
SET @stmt = (SELECT IF(
  (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = @dbname AND TABLE_NAME = 'personal_profiles' AND COLUMN_NAME = 'country') = 0,
  'ALTER TABLE personal_profiles ADD COLUMN country VARCHAR(100) DEFAULT NULL',
  'SELECT 1'
));
PREPARE stmt FROM @stmt; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @stmt = (SELECT IF(
  (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = @dbname AND TABLE_NAME = 'business_profiles' AND COLUMN_NAME = 'country') = 0,
  'ALTER TABLE business_profiles ADD COLUMN country VARCHAR(100) DEFAULT NULL',
  'SELECT 1'
));
PREPARE stmt FROM @stmt; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @stmt = (SELECT IF(
  (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = @dbname AND TABLE_NAME = 'products' AND COLUMN_NAME = 'country') = 0,
  'ALTER TABLE products ADD COLUMN country VARCHAR(100) DEFAULT NULL',
  'SELECT 1'
));
PREPARE stmt FROM @stmt; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @stmt = (SELECT IF(
  (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = @dbname AND TABLE_NAME = 'products' AND COLUMN_NAME = 'city') = 0,
  'ALTER TABLE products ADD COLUMN city VARCHAR(100) DEFAULT NULL',
  'SELECT 1'
));
PREPARE stmt FROM @stmt; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @stmt = (SELECT IF(
  (SELECT COUNT(*) FROM information_schema.STATISTICS WHERE TABLE_SCHEMA = @dbname AND TABLE_NAME = 'products' AND INDEX_NAME = 'idx_products_geo') = 0,
  'ALTER TABLE products ADD INDEX idx_products_geo (country, city)',
  'SELECT 1'
));
PREPARE stmt FROM @stmt; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- ---------- 4. OAuth: columns for future Google/Facebook sign-in (code wired later) ----------
SET @stmt = (SELECT IF(
  (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = @dbname AND TABLE_NAME = 'users' AND COLUMN_NAME = 'oauth_provider') = 0,
  'ALTER TABLE users ADD COLUMN oauth_provider VARCHAR(20) DEFAULT NULL',
  'SELECT 1'
));
PREPARE stmt FROM @stmt; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @stmt = (SELECT IF(
  (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = @dbname AND TABLE_NAME = 'users' AND COLUMN_NAME = 'oauth_id') = 0,
  'ALTER TABLE users ADD COLUMN oauth_id VARCHAR(191) DEFAULT NULL',
  'SELECT 1'
));
PREPARE stmt FROM @stmt; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @stmt = (SELECT IF(
  (SELECT COUNT(*) FROM information_schema.STATISTICS WHERE TABLE_SCHEMA = @dbname AND TABLE_NAME = 'users' AND INDEX_NAME = 'idx_users_oauth') = 0,
  'ALTER TABLE users ADD UNIQUE INDEX idx_users_oauth (oauth_provider, oauth_id)',
  'SELECT 1'
));
PREPARE stmt FROM @stmt; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- Password becomes optional for OAuth-only accounts. MODIFY has no
-- "IF NOT EXISTS" equivalent, but re-running it with the same definition
-- is harmless either way (idempotent by nature, not just by guard).
ALTER TABLE users MODIFY password_hash VARCHAR(255) NULL;

-- ---------- 5. Rewards catalog + redemptions ----------
CREATE TABLE IF NOT EXISTS rewards (
    id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    title       VARCHAR(150) NOT NULL,
    description VARCHAR(255) DEFAULT NULL,
    points_cost INT UNSIGNED NOT NULL,
    is_active   TINYINT(1) NOT NULL DEFAULT 1,
    created_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS reward_redemptions (
    id               INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id          INT UNSIGNED NOT NULL,
    reward_id        INT UNSIGNED NOT NULL,
    points_spent     INT UNSIGNED NOT NULL,
    redemption_code  VARCHAR(20) NOT NULL UNIQUE,
    status           ENUM('pending', 'fulfilled', 'cancelled') NOT NULL DEFAULT 'pending',
    created_at       DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (reward_id) REFERENCES rewards(id) ON DELETE RESTRICT
) ENGINE=InnoDB;

INSERT IGNORE INTO rewards (id, title, description, points_cost) VALUES
  (1, '$2 off your next order', 'Show the code to any seller at pickup.', 20),
  (2, '$5 off your next order', 'Show the code to any seller at pickup.', 45),
  (3, 'Featured listing boost (7 days)', 'Business accounts only - highlights your listing.', 60);

-- ---------- 6. Bulk CSV import log (business/admin visibility into past imports) ----------
CREATE TABLE IF NOT EXISTS product_import_batches (
    id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    owner_id        INT UNSIGNED NOT NULL,
    filename        VARCHAR(255) NOT NULL,
    rows_total      INT UNSIGNED NOT NULL DEFAULT 0,
    rows_created    INT UNSIGNED NOT NULL DEFAULT 0,
    rows_failed     INT UNSIGNED NOT NULL DEFAULT 0,
    error_summary   TEXT,
    created_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (owner_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ---------- 7. Pin-based location (lat/lng) for vendors and personal accounts ----------
SET @stmt = (SELECT IF(
  (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = @dbname AND TABLE_NAME = 'business_profiles' AND COLUMN_NAME = 'latitude') = 0,
  'ALTER TABLE business_profiles ADD COLUMN latitude DECIMAL(10,7) DEFAULT NULL, ADD COLUMN longitude DECIMAL(10,7) DEFAULT NULL',
  'SELECT 1'
));
PREPARE stmt FROM @stmt; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @stmt = (SELECT IF(
  (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = @dbname AND TABLE_NAME = 'personal_profiles' AND COLUMN_NAME = 'latitude') = 0,
  'ALTER TABLE personal_profiles ADD COLUMN latitude DECIMAL(10,7) DEFAULT NULL, ADD COLUMN longitude DECIMAL(10,7) DEFAULT NULL',
  'SELECT 1'
));
PREPARE stmt FROM @stmt; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- ---------- 8. Product reviews & ratings ----------
CREATE TABLE IF NOT EXISTS product_reviews (
    id           INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    product_id   INT UNSIGNED NOT NULL,
    reviewer_id  INT UNSIGNED NOT NULL,
    order_id     INT UNSIGNED NOT NULL,
    rating       TINYINT UNSIGNED NOT NULL,
    comment      TEXT,
    created_at   DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at   DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE,
    FOREIGN KEY (reviewer_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE,
    UNIQUE KEY uniq_review_per_product_reviewer (product_id, reviewer_id)
) ENGINE=InnoDB;

-- ---------- 9. Item requests ("wanted" board - buyers ask for items not currently listed) ----------
CREATE TABLE IF NOT EXISTS item_requests (
    id                INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    requester_id      INT UNSIGNED NOT NULL,
    item_name         VARCHAR(191) NOT NULL,
    category_id       INT UNSIGNED DEFAULT NULL,
    description       TEXT,
    desired_quantity  INT UNSIGNED NOT NULL DEFAULT 1,
    status            ENUM('open', 'fulfilled', 'cancelled') NOT NULL DEFAULT 'open',
    created_at        DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (requester_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE SET NULL
) ENGINE=InnoDB;

-- ---------- 10. Email verification ----------
CREATE TABLE IF NOT EXISTS email_verifications (
    id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id     INT UNSIGNED NOT NULL,
    token_hash  VARCHAR(255) NOT NULL,
    expires_at  DATETIME NOT NULL,
    created_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- CRITICAL: back-fill every EXISTING account as already verified before this
-- feature goes live. Without this, every account created before today -
-- including your own admin account and any real users who've already
-- signed up - would be locked out of login the moment this deploys, since
-- they registered before verification emails existed and never got one.
-- Only accounts created AFTER this migration runs will need to verify.
UPDATE users SET email_verified_at = NOW() WHERE email_verified_at IS NULL;

-- ---------- 11. SMTP settings (admin-editable, no .env file access needed) ----------
INSERT IGNORE INTO settings (setting_key, setting_value) VALUES
  ('smtp_host', ''),
  ('smtp_port', '587'),
  ('smtp_username', ''),
  ('smtp_password', ''),
  ('smtp_encryption', 'tls'),
  ('smtp_from_email', ''),
  ('smtp_from_name', 'Ragreen');

-- ---------- 12. Per-item fulfillment status (sale isn't final until the seller confirms) ----------
-- A checkout only RESERVES stock and creates a pending order - it is NOT a
-- completed sale. Each seller must explicitly mark their own line items as
-- fulfilled (received payment + handed over the goods in person) or
-- cancelled (buyer never showed up / fell through). Since one order can
-- span multiple independent sellers, this status lives per line item, not
-- just on the order as a whole.
SET @stmt = (SELECT IF(
  (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = @dbname AND TABLE_NAME = 'order_items' AND COLUMN_NAME = 'status') = 0,
  "ALTER TABLE order_items ADD COLUMN status ENUM('pending','fulfilled','cancelled') NOT NULL DEFAULT 'pending'",
  'SELECT 1'
));
PREPARE stmt FROM @stmt; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- Existing orders were created under the old "checkout = final sale" model,
-- so back-fill them as already fulfilled/completed - only NEW orders after
-- this migration go through the pending -> fulfilled flow. Without this,
-- every order placed before today would suddenly show as "awaiting
-- confirmation" and sellers would be asked to re-confirm sales that already
-- happened and were already paid for.
UPDATE order_items SET status = 'fulfilled' WHERE status = 'pending';
UPDATE orders SET status = 'completed' WHERE status = 'confirmed';

-- ---------- 13. Admin-managed ads ----------
-- One ad "slot" definition per row. A placement_key can have several active
-- rows (rotation) - the front-end picks/cycles among them at render time.
CREATE TABLE IF NOT EXISTS ad_placements (
    id                INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    placement_key     VARCHAR(64) NOT NULL,
    ad_type           ENUM('google', 'custom') NOT NULL,
    width             SMALLINT UNSIGNED NOT NULL,
    height            SMALLINT UNSIGNED NOT NULL,
    google_ad_slot    VARCHAR(64) DEFAULT NULL,
    custom_image_path VARCHAR(255) DEFAULT NULL,
    custom_link_url   VARCHAR(500) DEFAULT NULL,
    custom_alt_text   VARCHAR(255) DEFAULT NULL,
    is_active         TINYINT(1) NOT NULL DEFAULT 1,
    display_order     INT NOT NULL DEFAULT 0,
    created_at        DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at        DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_placement_active (placement_key, is_active)
) ENGINE=InnoDB;

INSERT IGNORE INTO settings (setting_key, setting_value) VALUES ('adsense_client_id', '');

-- ---------- 14. Admin-curated featured products/vendors ----------
SET @stmt = (SELECT IF(
  (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = @dbname AND TABLE_NAME = 'products' AND COLUMN_NAME = 'is_featured') = 0,
  "ALTER TABLE products ADD COLUMN is_featured TINYINT(1) NOT NULL DEFAULT 0, ADD COLUMN featured_until DATE DEFAULT NULL",
  'SELECT 1'
));
PREPARE stmt FROM @stmt; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @stmt = (SELECT IF(
  (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = @dbname AND TABLE_NAME = 'users' AND COLUMN_NAME = 'is_featured_vendor') = 0,
  "ALTER TABLE users ADD COLUMN is_featured_vendor TINYINT(1) NOT NULL DEFAULT 0",
  'SELECT 1'
));
PREPARE stmt FROM @stmt; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- ---------- 15. Business subscription tracking (billing not live yet) ----------
-- No payment processing here (per RFQ scope exclusions) - just status
-- tracking so the free-trial period can be communicated now and admin can
-- manage it manually once real billing is switched on later.
SET @stmt = (SELECT IF(
  (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = @dbname AND TABLE_NAME = 'business_profiles' AND COLUMN_NAME = 'subscription_status') = 0,
  "ALTER TABLE business_profiles ADD COLUMN subscription_status ENUM('trial','active','expired','cancelled') NOT NULL DEFAULT 'trial', ADD COLUMN subscription_plan VARCHAR(64) DEFAULT NULL, ADD COLUMN trial_ends_at DATE DEFAULT NULL, ADD COLUMN subscription_notes TEXT DEFAULT NULL",
  'SELECT 1'
));
PREPARE stmt FROM @stmt; EXECUTE stmt; DEALLOCATE PREPARE stmt;

INSERT IGNORE INTO settings (setting_key, setting_value) VALUES
  ('business_trial_days', '90'),
  ('subscriptions_enabled', '0');

-- Back-fill: every EXISTING business gets a trial window starting from
-- today (not their original signup date - simplest, most generous
-- interpretation for accounts that predate this feature entirely).
SET @trial_days = (SELECT CAST(setting_value AS UNSIGNED) FROM settings WHERE setting_key = 'business_trial_days');
UPDATE business_profiles bp
JOIN users u ON u.id = bp.user_id
SET bp.trial_ends_at = DATE_ADD(CURDATE(), INTERVAL @trial_days DAY)
WHERE bp.trial_ends_at IS NULL AND u.account_type = 'business';

-- ---------- 16. Google ad test mode ----------
-- Lets a Google-type ad slot use Google's own official test-ad mechanism
-- (data-adtest="on") - renders a visible placeholder creative to verify
-- the whole integration (script load, ad unit sizing, positioning) works,
-- without needing real approved ads to be live yet. Still requires a real
-- AdSense client ID in Settings - this only controls what Google serves
-- once the account itself is real, it can't fabricate a working account.
SET @stmt = (SELECT IF(
  (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = @dbname AND TABLE_NAME = 'ad_placements' AND COLUMN_NAME = 'is_test_mode') = 0,
  "ALTER TABLE ad_placements ADD COLUMN is_test_mode TINYINT(1) NOT NULL DEFAULT 0",
  'SELECT 1'
));
PREPARE stmt FROM @stmt; EXECUTE stmt; DEALLOCATE PREPARE stmt;
