SET NAMES utf8mb4;
SET time_zone = '+07:00';
SET FOREIGN_KEY_CHECKS = 0;

CREATE TABLE IF NOT EXISTS provinces (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    code VARCHAR(20) NULL UNIQUE,
    name VARCHAR(120) NOT NULL,
    status ENUM('ACTIVE','INACTIVE') NOT NULL DEFAULT 'ACTIVE',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_provinces_name (name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS districts (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    province_id INT UNSIGNED NOT NULL,
    code VARCHAR(20) NULL,
    name VARCHAR(120) NOT NULL,
    status ENUM('ACTIVE','INACTIVE') NOT NULL DEFAULT 'ACTIVE',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_district_code_province (province_id, code),
    INDEX idx_district_province (province_id),
    INDEX idx_district_name (name),
    CONSTRAINT fk_district_province FOREIGN KEY (province_id) REFERENCES provinces(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS wards (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    district_id INT UNSIGNED NOT NULL,
    code VARCHAR(20) NULL,
    name VARCHAR(120) NOT NULL,
    status ENUM('ACTIVE','INACTIVE') NOT NULL DEFAULT 'ACTIVE',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_ward_code_district (district_id, code),
    INDEX idx_ward_district (district_id),
    INDEX idx_ward_name (name),
    CONSTRAINT fk_ward_district FOREIGN KEY (district_id) REFERENCES districts(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS users (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    zalo_uid VARCHAR(100) NULL UNIQUE,
    phone VARCHAR(20) NULL UNIQUE,
    fullname VARCHAR(150) NOT NULL,
    avatar VARCHAR(500) NULL,
    province_id INT UNSIGNED NULL,
    district_id INT UNSIGNED NULL,
    ward_id INT UNSIGNED NULL,
    address VARCHAR(500) NULL,
    role ENUM('MEMBER','PARTNER','MODERATOR','ADMIN','SUPER_ADMIN') NOT NULL DEFAULT 'MEMBER',
    status ENUM('PENDING','ACTIVE','BLOCKED','PERMANENT_BAN') NOT NULL DEFAULT 'ACTIVE',
    trust_score DECIMAL(5,2) NOT NULL DEFAULT 100.00,
    commission_strikes TINYINT UNSIGNED NOT NULL DEFAULT 0,
    fraud_strikes TINYINT UNSIGNED NOT NULL DEFAULT 0,
    phone_verified_at DATETIME NULL,
    zalo_verified_at DATETIME NULL,
    last_login_at DATETIME NULL,
    subscription_expires_at DATETIME NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_users_status (status),
    INDEX idx_users_role (role),
    INDEX idx_users_location (province_id, district_id),
    INDEX idx_users_subscription_expiry (subscription_expires_at),
    CONSTRAINT fk_user_province FOREIGN KEY (province_id) REFERENCES provinces(id) ON DELETE SET NULL,
    CONSTRAINT fk_user_district FOREIGN KEY (district_id) REFERENCES districts(id) ON DELETE SET NULL,
    CONSTRAINT fk_user_ward FOREIGN KEY (ward_id) REFERENCES wards(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS api_tokens (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NOT NULL,
    token_hash CHAR(64) NOT NULL UNIQUE,
    device_name VARCHAR(150) NULL,
    ip_address VARCHAR(45) NULL,
    expires_at DATETIME NOT NULL,
    revoked_at DATETIME NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_tokens_user (user_id),
    INDEX idx_tokens_expiry (expires_at),
    CONSTRAINT fk_token_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS driver_profiles (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NOT NULL UNIQUE,
    taxi_company VARCHAR(150) NULL,
    driver_license_number VARCHAR(80) NULL,
    driver_license_image VARCHAR(500) NULL,
    verification_status ENUM('PENDING','VERIFIED','REJECTED','BLOCKED') NOT NULL DEFAULT 'PENDING',
    verification_note VARCHAR(500) NULL,
    verified_at DATETIME NULL,
    verified_by BIGINT UNSIGNED NULL,
    available_status ENUM('ONLINE','BUSY','OFFLINE') NOT NULL DEFAULT 'OFFLINE',
    last_location_lat DECIMAL(10,7) NULL,
    last_location_lng DECIMAL(10,7) NULL,
    last_location_at DATETIME NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_driver_verify (verification_status),
    INDEX idx_driver_available (available_status),
    CONSTRAINT fk_driver_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    CONSTRAINT fk_driver_verified_by FOREIGN KEY (verified_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS vehicle_types (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    code VARCHAR(50) NOT NULL UNIQUE,
    name VARCHAR(100) NOT NULL,
    seats SMALLINT UNSIGNED NOT NULL,
    icon VARCHAR(255) NULL,
    sort_order INT NOT NULL DEFAULT 0,
    status ENUM('ACTIVE','INACTIVE') NOT NULL DEFAULT 'ACTIVE',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS vehicles (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NOT NULL,
    vehicle_type_id INT UNSIGNED NOT NULL,
    brand VARCHAR(100) NOT NULL,
    model VARCHAR(100) NOT NULL,
    manufacture_year SMALLINT UNSIGNED NULL,
    plate_number VARCHAR(30) NOT NULL,
    color VARCHAR(60) NULL,
    seats SMALLINT UNSIGNED NOT NULL,
    image_front VARCHAR(500) NULL,
    image_back VARCHAR(500) NULL,
    registration_image VARCHAR(500) NULL,
    verification_status ENUM('PENDING','VERIFIED','REJECTED','BLOCKED') NOT NULL DEFAULT 'PENDING',
    verification_note VARCHAR(500) NULL,
    status ENUM('ACTIVE','INACTIVE','BLOCKED') NOT NULL DEFAULT 'ACTIVE',
    verified_at DATETIME NULL,
    verified_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_plate_number (plate_number),
    INDEX idx_vehicle_user (user_id),
    INDEX idx_vehicle_type_status (vehicle_type_id, status),
    CONSTRAINT fk_vehicle_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    CONSTRAINT fk_vehicle_type FOREIGN KEY (vehicle_type_id) REFERENCES vehicle_types(id) ON DELETE RESTRICT,
    CONSTRAINT fk_vehicle_verified_by FOREIGN KEY (verified_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS bank_accounts (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NOT NULL,
    bank_code VARCHAR(50) NOT NULL,
    bank_name VARCHAR(150) NOT NULL,
    account_number VARCHAR(50) NOT NULL,
    account_name VARCHAR(150) NOT NULL,
    is_default TINYINT(1) NOT NULL DEFAULT 0,
    status ENUM('ACTIVE','INACTIVE') NOT NULL DEFAULT 'ACTIVE',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_user_bank_account (user_id, bank_code, account_number),
    INDEX idx_bank_default (user_id, is_default, status),
    CONSTRAINT fk_bank_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS fare_rules (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    vehicle_type_id INT UNSIGNED NOT NULL,
    province_id INT UNSIGNED NULL,
    base_price BIGINT UNSIGNED NOT NULL DEFAULT 0,
    base_distance_km DECIMAL(8,2) NOT NULL DEFAULT 0.00,
    price_per_km BIGINT UNSIGNED NOT NULL DEFAULT 0,
    minimum_price BIGINT UNSIGNED NOT NULL DEFAULT 0,
    night_start TIME NULL,
    night_end TIME NULL,
    night_multiplier DECIMAL(5,2) NOT NULL DEFAULT 1.00,
    holiday_multiplier DECIMAL(5,2) NOT NULL DEFAULT 1.00,
    commission_rate DECIMAL(5,2) NULL,
    status ENUM('ACTIVE','INACTIVE') NOT NULL DEFAULT 'ACTIVE',
    priority 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_fare_vehicle_location (vehicle_type_id, province_id, status, priority),
    CONSTRAINT fk_fare_vehicle_type FOREIGN KEY (vehicle_type_id) REFERENCES vehicle_types(id) ON DELETE CASCADE,
    CONSTRAINT fk_fare_province FOREIGN KEY (province_id) REFERENCES provinces(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS fixed_routes (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    route_name VARCHAR(200) NOT NULL,
    pickup_province_id INT UNSIGNED NULL,
    pickup_district_id INT UNSIGNED NULL,
    pickup_zone VARCHAR(200) NULL,
    destination_province_id INT UNSIGNED NULL,
    destination_district_id INT UNSIGNED NULL,
    destination_zone VARCHAR(200) NULL,
    vehicle_type_id INT UNSIGNED NOT NULL,
    one_way_price BIGINT UNSIGNED NOT NULL,
    round_trip_price BIGINT UNSIGNED NULL,
    commission_rate DECIMAL(5,2) NULL,
    status ENUM('ACTIVE','INACTIVE') NOT NULL DEFAULT 'ACTIVE',
    priority 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_fixed_lookup (pickup_district_id, destination_district_id, vehicle_type_id, status, priority),
    CONSTRAINT fk_fixed_pickup_province FOREIGN KEY (pickup_province_id) REFERENCES provinces(id) ON DELETE SET NULL,
    CONSTRAINT fk_fixed_pickup_district FOREIGN KEY (pickup_district_id) REFERENCES districts(id) ON DELETE SET NULL,
    CONSTRAINT fk_fixed_destination_province FOREIGN KEY (destination_province_id) REFERENCES provinces(id) ON DELETE SET NULL,
    CONSTRAINT fk_fixed_destination_district FOREIGN KEY (destination_district_id) REFERENCES districts(id) ON DELETE SET NULL,
    CONSTRAINT fk_fixed_vehicle_type FOREIGN KEY (vehicle_type_id) REFERENCES vehicle_types(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS rides (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    code VARCHAR(30) NOT NULL UNIQUE,
    creator_id BIGINT UNSIGNED NOT NULL,
    driver_id BIGINT UNSIGNED NULL,
    vehicle_id BIGINT UNSIGNED NULL,
    vehicle_type_id INT UNSIGNED NOT NULL,
    pickup_address VARCHAR(500) NOT NULL,
    pickup_lat DECIMAL(10,7) NULL,
    pickup_lng DECIMAL(10,7) NULL,
    pickup_province_id INT UNSIGNED NULL,
    pickup_district_id INT UNSIGNED NULL,
    destination_address VARCHAR(500) NOT NULL,
    destination_lat DECIMAL(10,7) NULL,
    destination_lng DECIMAL(10,7) NULL,
    destination_province_id INT UNSIGNED NULL,
    destination_district_id INT UNSIGNED NULL,
    distance_km DECIMAL(10,2) NULL,
    duration_minutes INT UNSIGNED NULL,
    pickup_at DATETIME NOT NULL,
    passenger_name VARCHAR(150) NOT NULL,
    passenger_phone VARCHAR(20) NOT NULL,
    passenger_count SMALLINT UNSIGNED NOT NULL DEFAULT 1,
    suggested_price BIGINT UNSIGNED NOT NULL DEFAULT 0,
    listed_price BIGINT UNSIGNED NOT NULL,
    agreed_price BIGINT UNSIGNED NULL,
    commission_rate DECIMAL(5,2) NOT NULL,
    commission_amount BIGINT UNSIGNED NULL,
    fare_source ENUM('FIXED_ROUTE','PER_KM','MANUAL') NOT NULL DEFAULT 'PER_KM',
    fixed_route_id BIGINT UNSIGNED NULL,
    note TEXT NULL,
    status ENUM('OPEN','ACCEPTED','CONTACTED','ARRIVING','ARRIVED','IN_TRIP','COMPLETED','CUSTOMER_CANCELLED','DRIVER_CANCELLED','NO_SHOW','DISPUTED','CANCELLED','EXPIRED') NOT NULL DEFAULT 'OPEN',
    accepted_at DATETIME NULL,
    contacted_at DATETIME NULL,
    arriving_at DATETIME NULL,
    arrived_at DATETIME NULL,
    started_at DATETIME NULL,
    completed_at DATETIME NULL,
    cancelled_at DATETIME NULL,
    closed_at DATETIME NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_rides_feed (status, pickup_at, vehicle_type_id),
    INDEX idx_rides_creator (creator_id, created_at),
    INDEX idx_rides_driver (driver_id, status, pickup_at),
    INDEX idx_rides_pickup_area (pickup_province_id, pickup_district_id, status),
    INDEX idx_rides_destination_area (destination_province_id, destination_district_id),
    CONSTRAINT fk_ride_creator FOREIGN KEY (creator_id) REFERENCES users(id) ON DELETE RESTRICT,
    CONSTRAINT fk_ride_driver FOREIGN KEY (driver_id) REFERENCES users(id) ON DELETE SET NULL,
    CONSTRAINT fk_ride_vehicle FOREIGN KEY (vehicle_id) REFERENCES vehicles(id) ON DELETE SET NULL,
    CONSTRAINT fk_ride_vehicle_type FOREIGN KEY (vehicle_type_id) REFERENCES vehicle_types(id) ON DELETE RESTRICT,
    CONSTRAINT fk_ride_pickup_province FOREIGN KEY (pickup_province_id) REFERENCES provinces(id) ON DELETE SET NULL,
    CONSTRAINT fk_ride_pickup_district FOREIGN KEY (pickup_district_id) REFERENCES districts(id) ON DELETE SET NULL,
    CONSTRAINT fk_ride_destination_province FOREIGN KEY (destination_province_id) REFERENCES provinces(id) ON DELETE SET NULL,
    CONSTRAINT fk_ride_destination_district FOREIGN KEY (destination_district_id) REFERENCES districts(id) ON DELETE SET NULL,
    CONSTRAINT fk_ride_fixed_route FOREIGN KEY (fixed_route_id) REFERENCES fixed_routes(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS ride_events (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    ride_id BIGINT UNSIGNED NOT NULL,
    user_id BIGINT UNSIGNED NULL,
    event_type VARCHAR(80) NOT NULL,
    old_status VARCHAR(40) NULL,
    new_status VARCHAR(40) NULL,
    latitude DECIMAL(10,7) NULL,
    longitude DECIMAL(10,7) NULL,
    ip_address VARCHAR(45) NULL,
    user_agent VARCHAR(500) NULL,
    note TEXT NULL,
    meta_json JSON NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_ride_events_ride (ride_id, created_at),
    CONSTRAINT fk_event_ride FOREIGN KEY (ride_id) REFERENCES rides(id) ON DELETE CASCADE,
    CONSTRAINT fk_event_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS ride_cancellations (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    ride_id BIGINT UNSIGNED NOT NULL,
    reported_by BIGINT UNSIGNED NOT NULL,
    cancellation_type ENUM('CUSTOMER_CANCELLED','DRIVER_CANCELLED','NO_SHOW','OTHER') NOT NULL,
    reason_code VARCHAR(80) NULL,
    reason_text TEXT NULL,
    evidence_json JSON NULL,
    creator_confirmation ENUM('PENDING','CONFIRMED','REJECTED') NOT NULL DEFAULT 'PENDING',
    confirmed_at DATETIME NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_cancel_ride (ride_id),
    CONSTRAINT fk_cancel_ride FOREIGN KEY (ride_id) REFERENCES rides(id) ON DELETE CASCADE,
    CONSTRAINT fk_cancel_reporter FOREIGN KEY (reported_by) REFERENCES users(id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS commissions (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    ride_id BIGINT UNSIGNED NOT NULL UNIQUE,
    from_user_id BIGINT UNSIGNED NOT NULL,
    to_user_id BIGINT UNSIGNED NOT NULL,
    rate DECIMAL(5,2) NOT NULL,
    amount BIGINT UNSIGNED NOT NULL,
    status ENUM('WAITING_PAYMENT','PROOF_UPLOADED','CONFIRMED','OVERDUE','DISPUTED','WAIVED') NOT NULL DEFAULT 'WAITING_PAYMENT',
    due_at DATETIME NOT NULL,
    paid_at DATETIME NULL,
    confirmed_at DATETIME NULL,
    confirmed_by BIGINT UNSIGNED NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_commission_status_due (status, due_at),
    INDEX idx_commission_from (from_user_id, status),
    INDEX idx_commission_to (to_user_id, status),
    CONSTRAINT fk_commission_ride FOREIGN KEY (ride_id) REFERENCES rides(id) ON DELETE CASCADE,
    CONSTRAINT fk_commission_from FOREIGN KEY (from_user_id) REFERENCES users(id) ON DELETE RESTRICT,
    CONSTRAINT fk_commission_to FOREIGN KEY (to_user_id) REFERENCES users(id) ON DELETE RESTRICT,
    CONSTRAINT fk_commission_confirmed_by FOREIGN KEY (confirmed_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS commission_proofs (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    commission_id BIGINT UNSIGNED NOT NULL,
    user_id BIGINT UNSIGNED NOT NULL,
    image_path VARCHAR(500) NOT NULL,
    transaction_code VARCHAR(120) NULL,
    bank_name VARCHAR(150) NULL,
    amount BIGINT UNSIGNED NOT NULL,
    note VARCHAR(500) NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_proof_commission (commission_id, created_at),
    CONSTRAINT fk_proof_commission FOREIGN KEY (commission_id) REFERENCES commissions(id) ON DELETE CASCADE,
    CONSTRAINT fk_proof_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS disputes (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    ride_id BIGINT UNSIGNED NOT NULL,
    opened_by BIGINT UNSIGNED NOT NULL,
    against_user_id BIGINT UNSIGNED NULL,
    type ENUM('COMMISSION','FAKE_CANCEL','DRIVER_NO_SHOW','CUSTOMER_INFO','PAYMENT_PROOF','OTHER') NOT NULL,
    reason VARCHAR(250) NOT NULL,
    description TEXT NULL,
    status ENUM('OPEN','INVESTIGATING','RESOLVED','REJECTED') NOT NULL DEFAULT 'OPEN',
    resolution ENUM('NONE','OPENED_BY_WINS','AGAINST_USER_WINS','PARTIAL','NO_ACTION') NOT NULL DEFAULT 'NONE',
    admin_note TEXT NULL,
    resolved_by BIGINT UNSIGNED NULL,
    resolved_at DATETIME NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_dispute_status (status, created_at),
    INDEX idx_dispute_ride (ride_id),
    CONSTRAINT fk_dispute_ride FOREIGN KEY (ride_id) REFERENCES rides(id) ON DELETE CASCADE,
    CONSTRAINT fk_dispute_opened_by FOREIGN KEY (opened_by) REFERENCES users(id) ON DELETE RESTRICT,
    CONSTRAINT fk_dispute_against FOREIGN KEY (against_user_id) REFERENCES users(id) ON DELETE SET NULL,
    CONSTRAINT fk_dispute_resolved_by FOREIGN KEY (resolved_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS dispute_evidence (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    dispute_id BIGINT UNSIGNED NOT NULL,
    user_id BIGINT UNSIGNED NOT NULL,
    type ENUM('IMAGE','TEXT','LOCATION','CALL_LOG','OTHER') NOT NULL,
    content TEXT NULL,
    file_path VARCHAR(500) NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_dispute_evidence (dispute_id, created_at),
    CONSTRAINT fk_dispute_evidence_dispute FOREIGN KEY (dispute_id) REFERENCES disputes(id) ON DELETE CASCADE,
    CONSTRAINT fk_dispute_evidence_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS violations (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NOT NULL,
    ride_id BIGINT UNSIGNED NULL,
    type ENUM('PAYMENT_DELAY','COMMISSION_UNPAID','FAKE_CANCEL','NO_SHOW_DRIVER','STEAL_CUSTOMER','FAKE_PAYMENT_PROOF','ABUSE','FRAUD','OTHER') NOT NULL,
    severity ENUM('LOW','MEDIUM','HIGH','CRITICAL') NOT NULL,
    title VARCHAR(250) NOT NULL,
    description TEXT NULL,
    evidence_json JSON NULL,
    status ENUM('OPEN','CONFIRMED','REJECTED','RESOLVED') NOT NULL DEFAULT 'OPEN',
    created_by BIGINT UNSIGNED NULL,
    resolved_by BIGINT UNSIGNED NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    resolved_at DATETIME NULL,
    INDEX idx_violation_user (user_id, status, created_at),
    INDEX idx_violation_severity (severity, status),
    CONSTRAINT fk_violation_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    CONSTRAINT fk_violation_ride FOREIGN KEY (ride_id) REFERENCES rides(id) ON DELETE SET NULL,
    CONSTRAINT fk_violation_created_by FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL,
    CONSTRAINT fk_violation_resolved_by FOREIGN KEY (resolved_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS user_bans (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NOT NULL,
    type ENUM('RIDE_RECEIVE_BLOCK','TEMPORARY','PERMANENT') NOT NULL,
    reason VARCHAR(500) NOT NULL,
    start_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    end_at DATETIME NULL,
    created_by BIGINT UNSIGNED NULL,
    status ENUM('ACTIVE','LIFTED','EXPIRED') NOT NULL DEFAULT 'ACTIVE',
    lifted_by BIGINT UNSIGNED NULL,
    lifted_at DATETIME NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_ban_user_active (user_id, status, start_at, end_at),
    CONSTRAINT fk_ban_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    CONSTRAINT fk_ban_created_by FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL,
    CONSTRAINT fk_ban_lifted_by FOREIGN KEY (lifted_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS ratings (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    ride_id BIGINT UNSIGNED NOT NULL,
    from_user_id BIGINT UNSIGNED NOT NULL,
    to_user_id BIGINT UNSIGNED NOT NULL,
    rating TINYINT UNSIGNED NOT NULL,
    comment VARCHAR(1000) NULL,
    tags_json JSON NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_rating_once (ride_id, from_user_id, to_user_id),
    INDEX idx_rating_to (to_user_id, created_at),
    CONSTRAINT fk_rating_ride FOREIGN KEY (ride_id) REFERENCES rides(id) ON DELETE CASCADE,
    CONSTRAINT fk_rating_from FOREIGN KEY (from_user_id) REFERENCES users(id) ON DELETE CASCADE,
    CONSTRAINT fk_rating_to FOREIGN KEY (to_user_id) REFERENCES users(id) ON DELETE CASCADE,
    CONSTRAINT chk_rating_value CHECK (rating BETWEEN 1 AND 5)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS notifications (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NOT NULL,
    type VARCHAR(80) NOT NULL,
    title VARCHAR(200) NOT NULL,
    message TEXT NOT NULL,
    data_json JSON NULL,
    is_read TINYINT(1) NOT NULL DEFAULT 0,
    sent_zalo TINYINT(1) NOT NULL DEFAULT 0,
    sent_at DATETIME NULL,
    read_at DATETIME NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_notification_user (user_id, is_read, created_at),
    CONSTRAINT fk_notification_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS driver_service_areas (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NOT NULL,
    province_id INT UNSIGNED NOT NULL,
    district_id INT UNSIGNED NULL,
    radius_km DECIMAL(8,2) NOT NULL DEFAULT 30.00,
    status ENUM('ACTIVE','INACTIVE') NOT NULL DEFAULT 'ACTIVE',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_service_area (province_id, district_id, status),
    CONSTRAINT fk_area_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    CONSTRAINT fk_area_province FOREIGN KEY (province_id) REFERENCES provinces(id) ON DELETE CASCADE,
    CONSTRAINT fk_area_district FOREIGN KEY (district_id) REFERENCES districts(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


CREATE TABLE IF NOT EXISTS app_media (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    slot ENUM('LOGIN_HERO','HOME_TOP','HOME_MIDDLE','RIDES_TOP','RIDE_DETAIL_TOP','CREATE_TOP','NEWS_TOP','PROFILE_TOP','ADMIN_TOP') NOT NULL,
    title VARCHAR(180) NULL,
    subtitle VARCHAR(500) NULL,
    image_path VARCHAR(500) NOT NULL,
    image_url VARCHAR(1000) NULL,
    target_url VARCHAR(500) NULL,
    status ENUM('ACTIVE','INACTIVE') NOT NULL DEFAULT 'ACTIVE',
    sort_order INT NOT NULL DEFAULT 0,
    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,
    INDEX idx_app_media_slot (slot,status,sort_order),
    CONSTRAINT fk_app_media_admin 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 ride_proofs (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    ride_id BIGINT UNSIGNED NOT NULL,
    user_id BIGINT UNSIGNED NOT NULL,
    type ENUM('ACCEPTED','ARRIVED','COMPLETED','CUSTOMER_CANCEL','OTHER') NOT NULL,
    image_path VARCHAR(500) NOT NULL,
    note VARCHAR(500) NULL,
    latitude DECIMAL(10,7) NULL,
    longitude DECIMAL(10,7) NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_ride_proofs_ride (ride_id,type,created_at),
    CONSTRAINT fk_ride_proof_ride FOREIGN KEY (ride_id) REFERENCES rides(id) ON DELETE CASCADE,
    CONSTRAINT fk_ride_proof_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS settings (
    `key` VARCHAR(120) PRIMARY KEY,
    `value` TEXT NULL,
    `type` ENUM('STRING','INT','FLOAT','BOOL','JSON') NOT NULL DEFAULT 'STRING',
    description VARCHAR(500) NULL,
    updated_by BIGINT UNSIGNED NULL,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_setting_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 subscription_payment_intents (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NOT NULL,
    amount BIGINT UNSIGNED NOT NULL,
    payment_content VARCHAR(120) NOT NULL,
    duration_months INT UNSIGNED NOT NULL DEFAULT 1,
    duration_days INT UNSIGNED NOT NULL DEFAULT 0,
    status ENUM('WAITING','CONFIRMED','EXPIRED','CANCELLED') NOT NULL DEFAULT 'WAITING',
    confirmed_transaction_id VARCHAR(120) NULL,
    expires_at DATETIME NOT NULL,
    confirmed_at DATETIME NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_sub_intent_user (user_id,status,created_at),
    INDEX idx_sub_intent_expiry (status,expires_at),
    CONSTRAINT fk_sub_intent_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS subscription_transactions (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    intent_id BIGINT UNSIGNED NOT NULL,
    user_id BIGINT UNSIGNED NOT NULL,
    transaction_id VARCHAR(120) NOT NULL UNIQUE,
    amount BIGINT UNSIGNED NOT NULL,
    description TEXT NULL,
    transaction_date DATE NULL,
    raw_json JSON NULL,
    confirmed_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_sub_tx_user (user_id,confirmed_at),
    INDEX idx_sub_tx_intent (intent_id),
    CONSTRAINT fk_sub_tx_intent FOREIGN KEY (intent_id) REFERENCES subscription_payment_intents(id) ON DELETE RESTRICT,
    CONSTRAINT fk_sub_tx_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS subscription_extensions (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NOT NULL,
    source ENUM('PAYMENT','ADMIN') NOT NULL,
    months_added INT NOT NULL DEFAULT 0,
    days_added INT NOT NULL DEFAULT 0,
    old_expires_at DATETIME NULL,
    new_expires_at DATETIME NOT NULL,
    transaction_id VARCHAR(120) NULL,
    admin_id BIGINT UNSIGNED NULL,
    note VARCHAR(500) NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_sub_ext_user (user_id,created_at),
    CONSTRAINT fk_sub_ext_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    CONSTRAINT fk_sub_ext_admin FOREIGN KEY (admin_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS admin_logs (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    admin_id BIGINT UNSIGNED NULL,
    action VARCHAR(120) NOT NULL,
    target_type VARCHAR(100) NULL,
    target_id VARCHAR(100) NULL,
    old_data JSON NULL,
    new_data JSON NULL,
    ip_address VARCHAR(45) NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_admin_log_admin (admin_id, created_at),
    INDEX idx_admin_log_target (target_type, target_id),
    CONSTRAINT fk_admin_log_user FOREIGN KEY (admin_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO vehicle_types (code, name, seats, sort_order, status) VALUES
('CAR_4', 'Xe 4 chỗ', 4, 10, 'ACTIVE'),
('CAR_5', 'Xe 5 chỗ', 5, 20, 'ACTIVE'),
('CAR_7', 'Xe 7 chỗ', 7, 30, 'ACTIVE'),
('BUS_28', 'Xe 28 chỗ', 28, 40, 'ACTIVE')
ON DUPLICATE KEY UPDATE name = VALUES(name), seats = VALUES(seats), sort_order = VALUES(sort_order), status = VALUES(status);

INSERT INTO settings (`key`,`value`,`type`,description) VALUES
('commission.default_rate','5','FLOAT','Phần trăm hoa hồng mặc định'),
('commission.due_minutes','120','INT','Số phút phải thanh toán hoa hồng sau khi hoàn thành chuyến'),
('commission.max_unpaid_strikes','2','INT','Số lần không trả hoa hồng trước khi khóa nhận chuyến'),
('ride.accept_confirm_minutes','5','INT','Số phút tài xế cần xác nhận liên hệ sau khi nhận chuyến'),
('ride.open_expire_minutes','1440','INT','Thời gian chuyến OPEN tự hết hạn nếu phù hợp'),
('fare.max_discount_percent','20','FLOAT','Giới hạn giá đăng thấp hơn giá gợi ý'),
('fare.max_markup_percent','30','FLOAT','Giới hạn giá đăng cao hơn giá gợi ý'),
('branding.app_name','Sàn Chia Chuyến','STRING','Tên ứng dụng hiển thị cho người dùng'),
('branding.tagline','Kết nối tài xế · Chia sẻ chuyến','STRING','Dòng mô tả thương hiệu'),
('branding.logo_path','','STRING','Đường dẫn logo đã upload'),
('branding.primary_color','#0B8F69','STRING','Màu chủ đạo giao diện'),
('branding.logo_url','','STRING','Đường dẫn web đầy đủ của logo'),
('fare.suggested_enabled','1','BOOL','Bật hiển thị và tính giá tham khảo khi đăng chuyến'),
('maps.enabled','1','BOOL','Bật gợi ý địa chỉ và tính quãng đường tự động'),
('branding.zalo_login_title','Đăng nhập nhanh bằng Zalo','STRING','Tiêu đề màn hình đăng nhập Zalo'),
('branding.zalo_login_subtitle','Kết nối tài khoản Zalo để đăng và nhận chuyến an toàn.','STRING','Mô tả màn hình đăng nhập Zalo'),
('ride.accept_photo_required','1','BOOL','Bắt buộc ảnh minh chứng sau khi nhận chuyến'),
('ride.complete_photo_required','1','BOOL','Bắt buộc ảnh minh chứng trước khi hoàn thành chuyến')
ON DUPLICATE KEY UPDATE `value` = VALUES(`value`), `type` = VALUES(`type`), description = VALUES(description);

INSERT INTO fare_rules (vehicle_type_id, base_price, base_distance_km, price_per_km, minimum_price, commission_rate, status, priority)
SELECT id, 20000, 0, 9000, 100000, 5, 'ACTIVE', 0 FROM vehicle_types WHERE code='CAR_4'
AND NOT EXISTS (SELECT 1 FROM fare_rules fr WHERE fr.vehicle_type_id = vehicle_types.id AND fr.province_id IS NULL);

INSERT INTO fare_rules (vehicle_type_id, base_price, base_distance_km, price_per_km, minimum_price, commission_rate, status, priority)
SELECT id, 25000, 0, 10000, 120000, 5, 'ACTIVE', 0 FROM vehicle_types WHERE code='CAR_5'
AND NOT EXISTS (SELECT 1 FROM fare_rules fr WHERE fr.vehicle_type_id = vehicle_types.id AND fr.province_id IS NULL);

INSERT INTO fare_rules (vehicle_type_id, base_price, base_distance_km, price_per_km, minimum_price, commission_rate, status, priority)
SELECT id, 30000, 0, 11000, 150000, 5, 'ACTIVE', 0 FROM vehicle_types WHERE code='CAR_7'
AND NOT EXISTS (SELECT 1 FROM fare_rules fr WHERE fr.vehicle_type_id = vehicle_types.id AND fr.province_id IS NULL);

INSERT INTO fare_rules (vehicle_type_id, base_price, base_distance_km, price_per_km, minimum_price, commission_rate, status, priority)
SELECT id, 100000, 0, 20000, 500000, 5, 'ACTIVE', 0 FROM vehicle_types WHERE code='BUS_28'
AND NOT EXISTS (SELECT 1 FROM fare_rules fr WHERE fr.vehicle_type_id = vehicle_types.id AND fr.province_id IS NULL);


CREATE TABLE IF NOT EXISTS admin_credentials (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NOT NULL UNIQUE,
    username VARCHAR(80) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    failed_attempts SMALLINT UNSIGNED NOT NULL DEFAULT 0,
    locked_until DATETIME NULL,
    last_login_at DATETIME NULL,
    last_login_ip VARCHAR(45) NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_admin_credential_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


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;

INSERT INTO settings (`key`,`value`,`type`,description) VALUES
('share.public_base_url','','STRING','Tên miền dùng cho link chia sẻ chuyến'),
('share.zmini_base_url','','STRING','Link chia sẻ chuyến do Zalo cấp'),
('share.miniapp_deeplink_template','https://zalo.me/s/917670624895107048/?share={slug}','STRING','Mẫu đường dẫn mở đúng chuyến khi người dùng bấm link')
ON DUPLICATE KEY UPDATE description=VALUES(description);

SET FOREIGN_KEY_CHECKS = 1;


INSERT INTO settings (`key`,`value`,`type`,description) VALUES
('subscription.enabled','1','BOOL','Bắt buộc còn hạn sử dụng mới được đăng hoặc nhận chuyến'),
('subscription.price','500000','INT','Số tiền gia hạn'),
('subscription.duration_months','1','INT','Số tháng cộng khi thanh toán thành công'),
('subscription.duration_days','0','INT','Số ngày cộng thêm ngoài số tháng'),
('subscription.bank_code','MB','STRING','Mã ngân hàng dùng cho VietQR'),
('subscription.bank_name','MB Bank','STRING','Tên ngân hàng hiển thị'),
('subscription.bank_account','603788888','STRING','Số tài khoản nhận tiền'),
('subscription.bank_account_name','LE KHAC NHAT','STRING','Tên chủ tài khoản'),
('subscription.qr_base_url','https://img.vietqr.io/image','STRING','Địa chỉ tạo ảnh QR VietQR'),
('subscription.history_api_base_url','https://sieuthicode.com/historyapimbbankv3','STRING','Địa chỉ API lịch sử giao dịch, không bao gồm token'),
('subscription.history_api_token','','STRING','Token API lịch sử giao dịch - chỉ lưu ở backend'),
('subscription.history_timeout','15','INT','Timeout kiểm tra lịch sử ngân hàng (giây)'),
('ride.max_booking_days','30','INT','Số ngày tối đa được đặt chuyến trước')
ON DUPLICATE KEY UPDATE `type`=VALUES(`type`),description=VALUES(description);