CREATE TABLE IF NOT EXISTS categories (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    uuid CHAR(36) NOT NULL UNIQUE,
    name VARCHAR(120) NOT NULL,
    slug VARCHAR(140) NOT NULL UNIQUE,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL,
    deleted_at TIMESTAMP NULL,
    INDEX categories_active_slug (is_active, slug)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS products (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    uuid CHAR(36) NOT NULL UNIQUE,
    seller_type ENUM('RESIDENT', 'PROVIDER') NOT NULL,
    resident_id CHAR(36) NULL,
    provider_id CHAR(36) NULL,
    category_id BIGINT UNSIGNED NULL,
    title VARCHAR(255) NOT NULL,
    slug VARCHAR(280) NOT NULL UNIQUE,
    short_description VARCHAR(500) NULL,
    description TEXT NULL,
    price DECIMAL(12, 2) NOT NULL,
    currency CHAR(3) NOT NULL DEFAULT 'PEN',
    stock INT UNSIGNED NOT NULL DEFAULT 0,
    sku VARCHAR(64) NULL,
    status ENUM('DRAFT', 'PUBLISHED', 'ARCHIVED', 'SUSPENDED') NOT NULL DEFAULT 'DRAFT',
    visibility ENUM('PUBLIC', 'PRIVATE') NOT NULL DEFAULT 'PUBLIC',
    thumbnail_s3_key VARCHAR(512) NULL,
    images JSON NULL,
    attributes JSON NULL,
    view_count BIGINT UNSIGNED NOT NULL DEFAULT 0,
    published_at TIMESTAMP NULL,
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL,
    deleted_at TIMESTAMP NULL,
    INDEX products_seller_status_visibility (seller_type, status, visibility),
    INDEX products_resident_status (resident_id, status),
    INDEX products_provider_status (provider_id, status),
    INDEX products_category_status (category_id, status),
    INDEX products_price_currency (price, currency),
    INDEX products_published_at (published_at),
    INDEX products_view_count (view_count),
    INDEX products_created_at (created_at),
    CONSTRAINT products_category_fk FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS audit_logs (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    uuid CHAR(36) NOT NULL UNIQUE,
    action VARCHAR(80) NOT NULL,
    resource_type VARCHAR(40) NOT NULL,
    resource_uuid CHAR(36) NULL,
    actor_user_id VARCHAR(64) NOT NULL,
    actor_resident_id CHAR(36) NULL,
    metadata JSON NULL,
    ip_address VARCHAR(45) NOT NULL,
    user_agent VARCHAR(512) NOT NULL,
    created_at TIMESTAMP NULL,
    INDEX audit_logs_action (action),
    INDEX audit_logs_actor (actor_user_id),
    INDEX audit_logs_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS product_likes (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    product_uuid CHAR(36) NOT NULL,
    resident_id CHAR(36) NOT NULL,
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL,
    UNIQUE KEY product_likes_unique (product_uuid, resident_id),
    INDEX product_likes_product_created (product_uuid, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS product_comments (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    uuid CHAR(36) NOT NULL UNIQUE,
    product_uuid CHAR(36) NOT NULL,
    resident_id CHAR(36) NOT NULL,
    body TEXT NOT NULL,
    status ENUM('ACTIVE', 'DELETED') NOT NULL DEFAULT 'ACTIVE',
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL,
    deleted_at TIMESTAMP NULL,
    INDEX product_comments_product_status_created (product_uuid, status, created_at),
    INDEX product_comments_resident_created (resident_id, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;