-- Migration: Add campaigns table
-- Description: Creates table for managing product and vendor campaigns

-- campaigns table
CREATE TABLE IF NOT EXISTS campaigns (
    id INT AUTO_INCREMENT PRIMARY KEY,
    campaignId VARCHAR(50) UNIQUE NOT NULL,
    title VARCHAR(255) NOT NULL,
    description TEXT,
    startDate DATETIME NOT NULL,
    endDate DATETIME NOT NULL,
    endTime VARCHAR(10), -- format HH:mm
    imageUrl VARCHAR(500),
    eligibleProducts TEXT, -- comma-separated product IDs or JSON array
    targetVendors TEXT, -- comma-separated vendor IDs or JSON array
    isActive BOOLEAN DEFAULT TRUE,
    createdAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- service_categories table (already exists in 001, but ensuring it has required fields)
-- If it already exists, we skip creation.
-- The 001_create_tables.sql had service_categories.

-- service_items table
CREATE TABLE IF NOT EXISTS service_items (
    id INT AUTO_INCREMENT PRIMARY KEY,
    serviceId VARCHAR(50) UNIQUE NOT NULL,
    categoryId VARCHAR(100) NOT NULL,
    name VARCHAR(255) NOT NULL,
    nameBn VARCHAR(255),
    description TEXT,
    iconUrl VARCHAR(500),
    targetType VARCHAR(50), -- e.g. "activity", "url", "category"
    targetAction VARCHAR(255),
    isActive BOOLEAN DEFAULT TRUE,
    displayOrder INT DEFAULT 0,
    createdAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (categoryId) REFERENCES service_categories(category_id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;