-- ==========================================================
-- 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,
    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;

INSERT INTO settings (`key`,`value`,`type`,description) VALUES
('branding.primary_color', '#1727b8', 'STRING', 'Màu chủ đạo giao diện Mini App V6'),
('share.public_base_url', '', 'STRING', 'Base URL short link do backend quản lý, ví dụ https://taxi.example.com/s'),
('share.zmini_base_url', '', 'STRING', 'Link zmini.app thật do Zalo cấp (nếu có). V6 sẽ gắn ?share=SLUG'),
('upload.help', 'JPG, PNG, WEBP, GIF; tối đa theo UPLOAD_MAX_BYTES', 'STRING', 'Gợi ý định dạng upload trong admin')
ON DUPLICATE KEY UPDATE description=VALUES(description);

-- Tự cấu hình deeplink theo Mini App ID của bản V5 nguồn. Nếu DB đã có một
-- deeplink riêng thì giữ nguyên; nếu giá trị cũ đang rỗng thì V6 tự điền.
INSERT INTO settings (`key`,`value`,`type`,description) VALUES
('share.miniapp_deeplink_template', 'https://zalo.me/s/917670624895107048/?share={slug}', 'STRING', 'Deeplink mở đúng chuyến. Hỗ trợ {slug} và {ride_id}')
ON DUPLICATE KEY UPDATE
    value = IF(`value` IS NULL OR TRIM(`value`) = '', VALUES(`value`), `value`),
    description = VALUES(description);