CREATE TABLE public.ip (
    id SERIAL PRIMARY KEY,
    address character varying(45) UNIQUE NOT NULL,
    connections integer DEFAULT 1,
    kicks integer DEFAULT 0,
    bans integer DEFAULT 0,
    is_banned smallint DEFAULT 0,
    banner_id integer,
    reason character varying(121)

);
ALTER TABLE public.ip OWNER TO "rpfw-dev";

CREATE TABLE public."user" (
    id SERIAL PRIMARY KEY,
    name character varying(24) UNIQUE NOT NULL,
    level smallint DEFAULT 1,
    password character varying(128) NOT NULL,
    webaccount_id integer,
    last_ip_address character varying(45) NOT NULL,
    kicks integer DEFAULT 0,
    bans integer DEFAULT 0,
    is_banned integer DEFAULT 0,
    jailed smallint DEFAULT 0,
    created timestamp DEFAULT NOW()::timestamp,

    FOREIGN KEY (last_ip_address) REFERENCES ip (address)
);
ALTER TABLE public."user" OWNER TO "rpfw-dev";
ALTER TABLE public.ip ADD CONSTRAINT bannerfk FOREIGN KEY (banner_id) REFERENCES "user" (id);

CREATE TABLE public.user_ip (
    ip_id integer NOT NULL REFERENCES ip (id) ON UPDATE CASCADE ON DELETE CASCADE,
    user_id integer NOT NULL REFERENCES "user" (id) ON UPDATE CASCADE ON DELETE CASCADE,
    updated timestamp DEFAULT NOW()::timestamp,
    
    CONSTRAINT user_ip_pkey PRIMARY KEY (ip_id, user_id)
);
ALTER TABLE public.user_ip OWNER TO "rpfw-dev";

CREATE TABLE public.ip_bans (
    id SERIAL PRIMARY KEY,
    ip_id integer NOT NULL,
    created timestamp DEFAULT NOW()::timestamp,
    expires timestamp DEFAULT NOW()::timestamp + interval '30 days',
    reason character varying(121) NOT NULL,
    banner integer,

    FOREIGN KEY (ip_id) REFERENCES ip (id) ON UPDATE CASCADE ON DELETE CASCADE,
    FOREIGN KEY (banner) REFERENCES "user" (id) ON UPDATE CASCADE
);
ALTER TABLE public.ip_bans OWNER TO "rpfw-dev";

CREATE TABLE public.user_bans (
    id SERIAL PRIMARY KEY,
    user_id integer NOT NULL,
    created timestamp DEFAULT NOW()::timestamp,
    expires timestamp DEFAULT NOW()::timestamp + interval '30 days',
    reason character varying(121) NOT NULL,
    banner integer,

    FOREIGN KEY (user_id) REFERENCES "user" (id) ON UPDATE CASCADE ON DELETE CASCADE,
    FOREIGN KEY (banner) REFERENCES "user" (id) ON UPDATE CASCADE
);
ALTER TABLE public.ip_bans OWNER TO "rpfw-dev";

CREATE TABLE public.user_kicks (
    id SERIAL PRIMARY KEY,
    user_id integer NOT NULL,
    created timestamp DEFAULT NOW()::timestamp,
    reason character varying(121) NOT NULL,
    kicker integer,

    FOREIGN KEY (user_id) REFERENCES "user" (id) ON UPDATE CASCADE ON DELETE CASCADE,
    FOREIGN KEY (kicker) REFERENCES "user" (id) ON UPDATE CASCADE
);
ALTER TABLE public.ip_bans OWNER TO "rpfw-dev";

CREATE TABLE public."character" (
    id SERIAL PRIMARY KEY,
    user_id integer NOT NULL,
    name character varying(24) UNIQUE NOT NULL,
    skin_id smallint NOT NULL DEFAULT 134,
    cash integer DEFAULT 0,
    health real DEFAULT 100,
    armour real DEFAULT 100,
    jailed smallint DEFAULT 0,
    pos_x real DEFAULT -144.0328, 
    pos_y real DEFAULT 1225.0564,
    pos_z real DEFAULT 19.8992,
    rotation real DEFAULT 175.5507,
    created timestamp DEFAULT NOW()::timestamp,

    FOREIGN KEY (user_id) REFERENCES "user" (id) ON UPDATE CASCADE ON DELETE CASCADE
);
ALTER TABLE public."character" OWNER TO "rpfw-dev";