SET FOREIGN_KEY_CHECKS = 0;

CREATE TABLE IF NOT EXISTS users (
    id            VARCHAR(36)  NOT NULL PRIMARY KEY,
    email         VARCHAR(255) NOT NULL UNIQUE,
    username      VARCHAR(100) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    role          ENUM('parent','admin','super_admin') NOT NULL DEFAULT 'parent',
    created_at    TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at    TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted_at    TIMESTAMP    NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS guest_sessions (
    id             VARCHAR(36)  NOT NULL PRIMARY KEY,
    token_hash     VARCHAR(255) NOT NULL UNIQUE,
    ip_hash        VARCHAR(64)  NULL,
    user_agent     VARCHAR(500) NULL,
    children_count TINYINT      NOT NULL DEFAULT 0,
    created_at     TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
    expires_at     TIMESTAMP    NOT NULL,
    completed      TINYINT(1)   NOT NULL DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS children (
    id               VARCHAR(36)  NOT NULL PRIMARY KEY,
    name             VARCHAR(255) NOT NULL,
    user_id          VARCHAR(36)  NULL,
    guest_session_id VARCHAR(36)  NULL,
    created_at       TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at       TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted_at       TIMESTAMP    NULL,
    FOREIGN KEY (user_id)          REFERENCES users(id)          ON DELETE SET NULL,
    FOREIGN KEY (guest_session_id) REFERENCES guest_sessions(id) ON DELETE SET NULL,
    INDEX idx_user_id (user_id),
    INDEX idx_guest_session_id (guest_session_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS photos (
    id                   VARCHAR(36)  NOT NULL PRIMARY KEY,
    child_id             VARCHAR(36)  NOT NULL,
    storage_key          VARCHAR(500) NOT NULL,
    thumbnail_key        VARCHAR(500) NULL,
    original_filename    VARCHAR(255) NOT NULL,
    mime_type            VARCHAR(100) NOT NULL,
    file_size            INT          NOT NULL,
    upload_source        ENUM('registered','guest') NOT NULL DEFAULT 'registered',
    uploaded_by_user_id  VARCHAR(36)  NULL,
    uploaded_by_guest    VARCHAR(36)  NULL,
    created_at           TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
    deleted_at           TIMESTAMP    NULL,
    FOREIGN KEY (child_id)            REFERENCES children(id) ON DELETE CASCADE,
    FOREIGN KEY (uploaded_by_user_id) REFERENCES users(id)    ON DELETE SET NULL,
    INDEX idx_child_id (child_id),
    INDEX idx_deleted_at (deleted_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS audit_logs (
    id          VARCHAR(36)  NOT NULL PRIMARY KEY,
    actor_id    VARCHAR(36)  NULL,
    actor_role  VARCHAR(50)  NULL,
    action      VARCHAR(100) NOT NULL,
    target_type VARCHAR(50)  NOT NULL,
    target_id   VARCHAR(36)  NOT NULL,
    metadata    JSON         NULL,
    ip_hash     VARCHAR(64)  NULL,
    created_at  TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_actor_id (actor_id),
    INDEX idx_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS rate_limits (
    id           INT AUTO_INCREMENT PRIMARY KEY,
    identifier   VARCHAR(255) NOT NULL,
    action       VARCHAR(100) NOT NULL,
    attempts     INT          NOT NULL DEFAULT 1,
    window_start TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_identifier_action (identifier, action)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SET FOREIGN_KEY_CHECKS = 1;
