-- ==========================================================
-- ZALO TAXI V6 - NANG CAP TU V5, KHONG XOA DU LIEU CU
-- Backup database truoc khi chay.
-- ==========================================================

CREATE TABLE IF NOT EXISTS news_posts (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(220) NOT NULL,
    slug VARCHAR(220) NOT NULL,
    excerpt TEXT NULL,
    content LONGTEXT NOT NULL,
    cover_image_path VARCHAR(500) NULL,
    cover_image_url VARCHAR(1000) NULL,
    category VARCHAR(120) NOT NULL DEFAULT 'Tin tức',
    view_count INT UNSIGNED NOT NULL DEFAULT 0,
    status ENUM('DRAFT','PUBLISHED','HIDDEN') NOT NULL DEFAULT 'DRAFT',
    published_at DATETIME NULL,
    created_by BIGINT UNSIGNED NULL,
    updated_by BIGINT UNSIGNED NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_news_slug (slug),
    KEY idx_news_status (status, published_at),
    CONSTRAINT fk_news_created_by FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL,
    CONSTRAINT fk_news_updated_by FOREIGN KEY (updated_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS share_links (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    slug VARCHAR(40) NOT NULL,
    target_type VARCHAR(40) NOT NULL,
    target_id BIGINT UNSIGNED NOT NULL,
    title VARCHAR(220) NULL,
    meta_json JSON NULL,
    click_count INT UNSIGNED NOT NULL DEFAULT 0,
    last_clicked_at DATETIME NULL,
    created_by BIGINT UNSIGNED NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_share_slug (slug),
    UNIQUE KEY uq_share_target (target_type, target_id),
    CONSTRAINT fk_share_created_by FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS notification_campaigns (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(200) NOT NULL,
    message TEXT NOT NULL,
    target_type ENUM('ALL','USER_IDS') NOT NULL DEFAULT 'ALL',
    user_ids_json JSON NULL,
    sent_count INT UNSIGNED NOT NULL DEFAULT 0,
    created_by BIGINT UNSIGNED NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    KEY idx_notification_campaigns_created (created_at),
    CONSTRAINT fk_notification_campaigns_created_by FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- V6 có thêm vị trí banner. MODIFY giữ nguyên dữ liệu hiện tại.
ALTER TABLE app_media MODIFY COLUMN slot ENUM(
    'LOGIN_HERO','HOME_TOP','HOME_MIDDLE','RIDES_TOP','RIDE_DETAIL_TOP',
    'CREATE_TOP','NEWS_TOP','PROFILE_TOP','ADMIN_TOP'
) NOT NULL;

SET @db := DATABASE();
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
