CREATE TABLE IF NOT EXISTS accounts (
    id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name TEXT NOT NULL UNIQUE,
    password TEXT NOT NULL,
    gm_level BIGINT NOT NULL DEFAULT 0,
    locked INTEGER NOT NULL DEFAULT FALSE,
    banned INTEGER NOT NULL DEFAULT FALSE,
    play_key_id INTEGER NOT NULL DEFAULT 0,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP(),
    mute_expire BIGINT NOT NULL DEFAULT 0
);

CREATE TABLE IF NOT EXISTS charinfo (
    id BIGINT NOT NULL PRIMARY KEY,
    account_id INTEGER NOT NULL REFERENCES accounts(id),
    name TEXT NOT NULL,
    pending_name TEXT NOT NULL,
    needs_rename INTEGER NOT NULL DEFAULT FALSE,
    prop_clone_id BIGINT AUTO_INCREMENT UNIQUE,
    last_login BIGINT NOT NULL DEFAULT 0,
    permission_map BIGINT NOT NULL DEFAULT 0
);

CREATE TABLE IF NOT EXISTS charxml (
    id BIGINT NOT NULL PRIMARY KEY REFERENCES charinfo(id),
    xml_data TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS command_log (
    id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
    character_id BIGINT NOT NULL REFERENCES charinfo(id),
    command TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS friends (
    player_id BIGINT NOT NULL REFERENCES charinfo(id),
    friend_id BIGINT NOT NULL REFERENCES charinfo(id),
    best_friend INTEGER NOT NULL DEFAULT FALSE,

    PRIMARY KEY (player_id, friend_id)
);

CREATE TABLE IF NOT EXISTS leaderboard (
    id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
    game_id INTEGER NOT NULL DEFAULT 0,
    last_played DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP(),
    character_id BIGINT NOT NULL REFERENCES charinfo(id),
    time BIGINT NOT NULL,
    score BIGINT NOT NULL DEFAULT 0
);

CREATE TABLE IF NOT EXISTS mail (
    id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
    sender_id INTEGER NOT NULL DEFAULT 0,
    sender_name TEXT NOT NULL DEFAULT '',
    receiver_id BIGINT NOT NULL REFERENCES charinfo(id),
    receiver_name TEXT NOT NULL,
    time_sent BIGINT NOT NULL,
    subject TEXT NOT NULL,
    body TEXT NOT NULL,
    attachment_id BIGINT NOT NULL DEFAULT 0,
    attachment_lot INTEGER NOT NULL DEFAULT 0,
    attachment_subkey BIGINT NOT NULL DEFAULT 0,
    attachment_count INTEGER NOT NULL DEFAULT 0,
    was_read INTEGER NOT NULL DEFAULT FALSE
);

CREATE TABLE IF NOT EXISTS object_id_tracker (
    last_object_id BIGINT NOT NULL DEFAULT 0 PRIMARY KEY
);

CREATE TABLE IF NOT EXISTS pet_names (
    id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    pet_name TEXT NOT NULL,
    approved INTEGER NOT NULL
);

CREATE TABLE IF NOT EXISTS play_keys (
    id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
    key_string TEXT NOT NULL UNIQUE,
    key_uses INTEGER NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP(),
    active INTEGER NOT NULL DEFAULT TRUE
);

CREATE TABLE IF NOT EXISTS properties (
    id BIGINT NOT NULL PRIMARY KEY,
    owner_id BIGINT NOT NULL REFERENCES charinfo(id),
    template_id INTEGER NOT NULL,
    clone_id BIGINT REFERENCES charinfo(prop_clone_id),
    name TEXT NOT NULL,
    description TEXT NOT NULL,
    rent_amount INTEGER NOT NULL,
    rent_due BIGINT NOT NULL,
    privacy_option INTEGER NOT NULL,
    mod_approved INTEGER NOT NULL DEFAULT FALSE,
    last_updated BIGINT NOT NULL,
    time_claimed BIGINT NOT NULL,
    rejection_reason TEXT NOT NULL,
    reputation BIGINT NOT NULL,
    zone_id INTEGER NOT NULL
);

CREATE TABLE IF NOT EXISTS ugc (
    id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
    account_id INTEGER NOT NULL REFERENCES accounts(id),
    character_id BIGINT NOT NULL REFERENCES charinfo(id),
    is_optimized INTEGER NOT NULL DEFAULT FALSE,
    lxfml BLOB NOT NULL,
    bake_ao INTEGER NOT NULL DEFAULT FALSE,
    filename TEXT NOT NULL DEFAULT ('')
);

CREATE TABLE IF NOT EXISTS properties_contents (
    id BIGINT NOT NULL PRIMARY KEY,
    property_id BIGINT NOT NULL REFERENCES properties(id),
    ugc_id INTEGER NULL REFERENCES ugc(id),
    lot INTEGER NOT NULL,
    x DOUBLE NOT NULL,
    y DOUBLE NOT NULL,
    z DOUBLE NOT NULL,
    rx DOUBLE NOT NULL,
    ry DOUBLE NOT NULL,
    rz DOUBLE NOT NULL,
    rw DOUBLE NOT NULL
);

CREATE TABLE IF NOT EXISTS activity_log (
    id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
    character_id BIGINT NOT NULL REFERENCES charinfo(id),
    activity INTEGER NOT NULL,
    time BIGINT NOT NULL,
    map_id INTEGER NOT NULL
);

CREATE TABLE IF NOT EXISTS bug_reports (
    id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
    body TEXT NOT NULL,
    client_version TEXT NOT NULL,
    other_player_id TEXT NOT NULL,
    selection TEXT NOT NULL,
    submitted DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP()
);

CREATE TABLE IF NOT EXISTS servers (
    id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name TEXT NOT NULL,
    ip TEXT NOT NULL,
    port INTEGER NOT NULL,
    state INTEGER NOT NULL,
    version INTEGER NOT NULL DEFAULT 0
);