-- ═══════════════════════════════════════════════════════════════
-- Zufiti Physical Advertising Marketplace – Full Schema
-- MariaDB 11.x  |  UTF-8mb4  |  InnoDB
-- ═══════════════════════════════════════════════════════════════
SET NAMES utf8mb4;
SET foreign_key_checks = 0;

-- ─── Tenants ────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS tenants (
    id            CHAR(36)      NOT NULL DEFAULT (UUID()),
    name          VARCHAR(120)  NOT NULL,
    slug          VARCHAR(80)   NOT NULL UNIQUE,
    domain        VARCHAR(253)  NULL UNIQUE,
    plan          ENUM('starter','growth','enterprise') NOT NULL DEFAULT 'starter',
    settings      JSON          NOT NULL DEFAULT ('{}'),
    is_active     TINYINT(1)   NOT NULL DEFAULT 1,
    created_at    DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at    DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    INDEX idx_tenants_slug (slug),
    INDEX idx_tenants_domain (domain)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─── Users ──────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS users (
    id                CHAR(36)     NOT NULL DEFAULT (UUID()),
    tenant_id         CHAR(36)     NOT NULL,
    email             VARCHAR(254) NOT NULL,
    password_hash     VARCHAR(60)  NOT NULL,
    role              ENUM('admin','owner','advertiser') NOT NULL DEFAULT 'advertiser',
    first_name        VARCHAR(80)  NOT NULL,
    last_name         VARCHAR(80)  NOT NULL,
    phone             VARCHAR(30)  NULL,
    avatar_url        VARCHAR(500) NULL,
    is_active         TINYINT(1)  NOT NULL DEFAULT 1,
    is_verified       TINYINT(1)  NOT NULL DEFAULT 0,
    verify_token      VARCHAR(128) NULL,
    last_login_at     DATETIME     NULL,
    created_at        DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at        DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uq_users_tenant_email (tenant_id, email),
    INDEX idx_users_tenant (tenant_id),
    INDEX idx_users_role (role),
    CONSTRAINT fk_users_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─── Refresh Tokens ─────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS refresh_tokens (
    id          CHAR(36)    NOT NULL DEFAULT (UUID()),
    user_id     CHAR(36)    NOT NULL,
    token_hash  VARCHAR(64) NOT NULL UNIQUE,
    expires_at  DATETIME    NOT NULL,
    revoked_at  DATETIME    NULL,
    created_at  DATETIME    NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    INDEX idx_rt_user (user_id),
    INDEX idx_rt_expires (expires_at),
    CONSTRAINT fk_rt_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─── Properties ─────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS properties (
    id                  CHAR(36)        NOT NULL DEFAULT (UUID()),
    tenant_id           CHAR(36)        NOT NULL,
    owner_id            CHAR(36)        NOT NULL,
    title               VARCHAR(160)    NOT NULL,
    description         TEXT            NULL,
    type                ENUM('residential','commercial','billboard','transit','vehicle') NOT NULL,
    address_line1       VARCHAR(200)    NOT NULL,
    address_line2       VARCHAR(200)    NULL,
    city                VARCHAR(100)    NOT NULL,
    state_province      VARCHAR(100)    NOT NULL,
    postal_code         VARCHAR(20)     NOT NULL,
    country_code        CHAR(2)         NOT NULL DEFAULT 'US',
    latitude            DECIMAL(10,8)   NOT NULL,
    longitude           DECIMAL(11,8)   NOT NULL,
    traffic_score       TINYINT         NOT NULL DEFAULT 50 COMMENT '0-100',
    daily_impressions   INT             NOT NULL DEFAULT 0,
    is_active           TINYINT(1)     NOT NULL DEFAULT 1,
    is_featured         TINYINT(1)     NOT NULL DEFAULT 0,
    featured_until      DATETIME        NULL,
    demand_score        DECIMAL(5,2)    NOT NULL DEFAULT 1.00,
    conversion_rate     DECIMAL(5,4)    NOT NULL DEFAULT 0.0000,
    total_bookings      INT             NOT NULL DEFAULT 0,
    status              ENUM('draft','pending_review','active','suspended') NOT NULL DEFAULT 'draft',
    created_at          DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at          DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    INDEX idx_props_tenant (tenant_id),
    INDEX idx_props_owner (owner_id),
    INDEX idx_props_geo (latitude, longitude),
    INDEX idx_props_type (type),
    INDEX idx_props_status (status),
    INDEX idx_props_traffic (traffic_score),
    FULLTEXT INDEX ft_props_search (title, description),
    CONSTRAINT fk_props_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_props_owner FOREIGN KEY (owner_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─── Property Images ────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS property_images (
    id            CHAR(36)    NOT NULL DEFAULT (UUID()),
    tenant_id     CHAR(36)    NOT NULL,
    property_id   CHAR(36)    NOT NULL,
    filename      VARCHAR(255) NOT NULL,
    original_name VARCHAR(255) NOT NULL,
    mime_type     VARCHAR(80)  NOT NULL,
    size_bytes    INT          NOT NULL,
    width_px      SMALLINT     NULL,
    height_px     SMALLINT     NULL,
    is_primary    TINYINT(1)  NOT NULL DEFAULT 0,
    sort_order    TINYINT      NOT NULL DEFAULT 0,
    created_at    DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    INDEX idx_pimg_property (property_id),
    CONSTRAINT fk_pimg_property FOREIGN KEY (property_id) REFERENCES properties(id) ON DELETE CASCADE,
    CONSTRAINT fk_pimg_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─── Ad Slots ───────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS ad_slots (
    id                      CHAR(36)        NOT NULL DEFAULT (UUID()),
    tenant_id               CHAR(36)        NOT NULL,
    property_id             CHAR(36)        NOT NULL,
    name                    VARCHAR(120)    NOT NULL,
    description             TEXT            NULL,
    width_ft                DECIMAL(8,2)    NULL,
    height_ft               DECIMAL(8,2)    NULL,
    width_px                INT             NULL,
    height_px               INT             NULL,
    quantity                SMALLINT        NOT NULL DEFAULT 1,
    available_quantity      SMALLINT        NOT NULL DEFAULT 1,
    pricing_model           ENUM('fixed','time_based','dynamic','auction') NOT NULL DEFAULT 'fixed',
    base_price              DECIMAL(12,2)   NOT NULL,
    min_price               DECIMAL(12,2)   NULL,
    max_price               DECIMAL(12,2)   NULL,
    price_per_day           DECIMAL(12,2)   NULL,
    price_per_week          DECIMAL(12,2)   NULL,
    price_per_month         DECIMAL(12,2)   NULL,
    dynamic_pricing_enabled TINYINT(1)     NOT NULL DEFAULT 0,
    auction_enabled         TINYINT(1)     NOT NULL DEFAULT 0,
    min_booking_days        SMALLINT        NOT NULL DEFAULT 1,
    max_booking_days        SMALLINT        NULL,
    is_active               TINYINT(1)     NOT NULL DEFAULT 1,
    created_at              DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at              DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    INDEX idx_slots_property (property_id),
    INDEX idx_slots_tenant (tenant_id),
    INDEX idx_slots_pricing (pricing_model),
    CONSTRAINT fk_slots_property FOREIGN KEY (property_id) REFERENCES properties(id) ON DELETE CASCADE,
    CONSTRAINT fk_slots_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─── Bookings ───────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS bookings (
    id                  CHAR(36)        NOT NULL DEFAULT (UUID()),
    tenant_id           CHAR(36)        NOT NULL,
    ad_slot_id          CHAR(36)        NOT NULL,
    advertiser_id       CHAR(36)        NOT NULL,
    campaign_id         CHAR(36)        NULL,
    start_date          DATE            NOT NULL,
    end_date            DATE            NOT NULL,
    days_count          SMALLINT        NOT NULL,
    base_price          DECIMAL(12,2)   NOT NULL,
    demand_multiplier   DECIMAL(5,3)    NOT NULL DEFAULT 1.000,
    time_multiplier     DECIMAL(5,3)    NOT NULL DEFAULT 1.000,
    location_multiplier DECIMAL(5,3)    NOT NULL DEFAULT 1.000,
    subtotal            DECIMAL(12,2)   NOT NULL,
    advertiser_fee      DECIMAL(12,2)   NOT NULL DEFAULT 0.00,
    host_fee            DECIMAL(12,2)   NOT NULL DEFAULT 0.00,
    platform_fee        DECIMAL(12,2)   NOT NULL DEFAULT 0.00,
    total_amount        DECIMAL(12,2)   NOT NULL,
    owner_payout        DECIMAL(12,2)   NOT NULL,
    status              ENUM('pending','approved','active','completed','cancelled','disputed') NOT NULL DEFAULT 'pending',
    approval_notes      TEXT            NULL,
    cancelled_at        DATETIME        NULL,
    cancellation_reason TEXT            NULL,
    expires_at          DATETIME        NULL,
    created_at          DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at          DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    INDEX idx_bookings_slot (ad_slot_id),
    INDEX idx_bookings_advertiser (advertiser_id),
    INDEX idx_bookings_tenant (tenant_id),
    INDEX idx_bookings_dates (start_date, end_date),
    INDEX idx_bookings_status (status),
    INDEX idx_bookings_campaign (campaign_id),
    CONSTRAINT fk_bookings_slot FOREIGN KEY (ad_slot_id) REFERENCES ad_slots(id),
    CONSTRAINT fk_bookings_advertiser FOREIGN KEY (advertiser_id) REFERENCES users(id),
    CONSTRAINT fk_bookings_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─── Creatives ──────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS creatives (
    id              CHAR(36)    NOT NULL DEFAULT (UUID()),
    tenant_id       CHAR(36)    NOT NULL,
    booking_id      CHAR(36)    NOT NULL,
    advertiser_id   CHAR(36)    NOT NULL,
    filename        VARCHAR(255) NOT NULL,
    original_name   VARCHAR(255) NOT NULL,
    mime_type       VARCHAR(80) NOT NULL,
    size_bytes      INT         NOT NULL,
    width_px        INT         NULL,
    height_px       INT         NULL,
    file_hash       VARCHAR(64) NOT NULL,
    status          ENUM('pending','approved','rejected') NOT NULL DEFAULT 'pending',
    rejection_reason TEXT        NULL,
    approved_at     DATETIME    NULL,
    created_at      DATETIME    NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    INDEX idx_creatives_booking (booking_id),
    INDEX idx_creatives_advertiser (advertiser_id),
    CONSTRAINT fk_creatives_booking FOREIGN KEY (booking_id) REFERENCES bookings(id) ON DELETE CASCADE,
    CONSTRAINT fk_creatives_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─── Messages ───────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS messages (
    id          CHAR(36)    NOT NULL DEFAULT (UUID()),
    tenant_id   CHAR(36)    NOT NULL,
    booking_id  CHAR(36)    NOT NULL,
    sender_id   CHAR(36)    NOT NULL,
    body        TEXT        NOT NULL,
    is_read     TINYINT(1) NOT NULL DEFAULT 0,
    created_at  DATETIME    NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    INDEX idx_msgs_booking (booking_id),
    INDEX idx_msgs_sender (sender_id),
    INDEX idx_msgs_tenant (tenant_id),
    CONSTRAINT fk_msgs_booking FOREIGN KEY (booking_id) REFERENCES bookings(id) ON DELETE CASCADE,
    CONSTRAINT fk_msgs_sender FOREIGN KEY (sender_id) REFERENCES users(id) ON DELETE CASCADE,
    CONSTRAINT fk_msgs_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─── Payments ───────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS payments (
    id                  CHAR(36)        NOT NULL DEFAULT (UUID()),
    tenant_id           CHAR(36)        NOT NULL,
    booking_id          CHAR(36)        NOT NULL,
    payer_id            CHAR(36)        NOT NULL,
    amount              DECIMAL(12,2)   NOT NULL,
    currency            CHAR(3)         NOT NULL DEFAULT 'USD',
    type                ENUM('charge','refund','payout') NOT NULL,
    status              ENUM('pending','held','released','refunded','failed') NOT NULL DEFAULT 'pending',
    escrow_released_at  DATETIME        NULL,
    gateway_ref         VARCHAR(128)    NULL,
    gateway_data        JSON            NULL,
    notes               TEXT            NULL,
    created_at          DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at          DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    INDEX idx_payments_booking (booking_id),
    INDEX idx_payments_payer (payer_id),
    INDEX idx_payments_status (status),
    INDEX idx_payments_tenant (tenant_id),
    CONSTRAINT fk_payments_booking FOREIGN KEY (booking_id) REFERENCES bookings(id),
    CONSTRAINT fk_payments_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─── Auctions ───────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS auctions (
    id                  CHAR(36)        NOT NULL DEFAULT (UUID()),
    tenant_id           CHAR(36)        NOT NULL,
    ad_slot_id          CHAR(36)        NOT NULL,
    start_date          DATE            NOT NULL,
    end_date            DATE            NOT NULL,
    starts_at           DATETIME        NOT NULL,
    ends_at             DATETIME        NOT NULL,
    reserve_price       DECIMAL(12,2)   NOT NULL,
    buy_now_price       DECIMAL(12,2)   NULL,
    bid_increment       DECIMAL(12,2)   NOT NULL DEFAULT 5.00,
    current_price       DECIMAL(12,2)   NOT NULL,
    winning_bid_id      CHAR(36)        NULL,
    status              ENUM('scheduled','live','ended','cancelled') NOT NULL DEFAULT 'scheduled',
    created_at          DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at          DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    INDEX idx_auctions_slot (ad_slot_id),
    INDEX idx_auctions_status (status),
    INDEX idx_auctions_ends (ends_at),
    CONSTRAINT fk_auctions_slot FOREIGN KEY (ad_slot_id) REFERENCES ad_slots(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─── Bids ───────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS bids (
    id              CHAR(36)        NOT NULL DEFAULT (UUID()),
    tenant_id       CHAR(36)        NOT NULL,
    auction_id      CHAR(36)        NOT NULL,
    bidder_id       CHAR(36)        NOT NULL,
    amount          DECIMAL(12,2)   NOT NULL,
    is_winning      TINYINT(1)     NOT NULL DEFAULT 0,
    is_buy_now      TINYINT(1)     NOT NULL DEFAULT 0,
    created_at      DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    INDEX idx_bids_auction (auction_id),
    INDEX idx_bids_bidder (bidder_id),
    INDEX idx_bids_amount (amount),
    CONSTRAINT fk_bids_auction FOREIGN KEY (auction_id) REFERENCES auctions(id) ON DELETE CASCADE,
    CONSTRAINT fk_bids_bidder FOREIGN KEY (bidder_id) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─── Campaigns ──────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS campaigns (
    id                  CHAR(36)        NOT NULL DEFAULT (UUID()),
    tenant_id           CHAR(36)        NOT NULL,
    advertiser_id       CHAR(36)        NOT NULL,
    name                VARCHAR(160)    NOT NULL,
    description         TEXT            NULL,
    status              ENUM('draft','active','paused','completed','cancelled') NOT NULL DEFAULT 'draft',
    total_budget        DECIMAL(12,2)   NOT NULL,
    spent_amount        DECIMAL(12,2)   NOT NULL DEFAULT 0.00,
    daily_budget        DECIMAL(12,2)   NULL,
    start_date          DATE            NOT NULL,
    end_date            DATE            NOT NULL,
    geo_targets         JSON            NULL COMMENT 'Array of {lat,lng,radius_km}',
    time_targets        JSON            NULL COMMENT '{days:[0-6], hours:[0-23]}',
    auto_renew          TINYINT(1)     NOT NULL DEFAULT 0,
    rules               JSON            NULL COMMENT 'Programmatic buying rules',
    created_at          DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at          DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    INDEX idx_campaigns_advertiser (advertiser_id),
    INDEX idx_campaigns_status (status),
    INDEX idx_campaigns_tenant (tenant_id),
    CONSTRAINT fk_campaigns_advertiser FOREIGN KEY (advertiser_id) REFERENCES users(id),
    CONSTRAINT fk_campaigns_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─── Tracking Links ─────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS tracking_links (
    id              CHAR(36)    NOT NULL DEFAULT (UUID()),
    tenant_id       CHAR(36)    NOT NULL,
    booking_id      CHAR(36)    NOT NULL,
    short_code      VARCHAR(16) NOT NULL UNIQUE,
    destination_url VARCHAR(2000) NULL,
    qr_filename     VARCHAR(255) NULL,
    scan_count      INT         NOT NULL DEFAULT 0,
    created_at      DATETIME    NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    INDEX idx_tlinks_booking (booking_id),
    INDEX idx_tlinks_code (short_code),
    CONSTRAINT fk_tlinks_booking FOREIGN KEY (booking_id) REFERENCES bookings(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─── Scan Events ────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS scan_events (
    id              BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    tracking_link_id CHAR(36)       NOT NULL,
    ip_address      VARCHAR(45)     NULL,
    user_agent      TEXT            NULL,
    referrer        VARCHAR(2000)   NULL,
    scanned_at      DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    INDEX idx_scans_link (tracking_link_id),
    INDEX idx_scans_at (scanned_at),
    CONSTRAINT fk_scans_link FOREIGN KEY (tracking_link_id) REFERENCES tracking_links(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─── Audit Logs ─────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS audit_logs (
    id              BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    tenant_id       CHAR(36)        NULL,
    user_id         CHAR(36)        NULL,
    action          VARCHAR(100)    NOT NULL,
    resource_type   VARCHAR(80)     NOT NULL,
    resource_id     CHAR(36)        NULL,
    old_values      JSON            NULL,
    new_values      JSON            NULL,
    ip_address      VARCHAR(45)     NULL,
    user_agent      VARCHAR(500)    NULL,
    created_at      DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    INDEX idx_audit_tenant (tenant_id),
    INDEX idx_audit_user (user_id),
    INDEX idx_audit_resource (resource_type, resource_id),
    INDEX idx_audit_action (action),
    INDEX idx_audit_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SET foreign_key_checks = 1;
