-- SQL script to initialize the database
-- 
-- $Id$

drop table if exists attributes cascade;
create table attributes (
	id integer primary key,
	name text);

drop table if exists items cascade;
create table items (
	id integer primary key,
	name text,
	ql integer,
	icon integer,
	uniq boolean,
	nodrop boolean,
	special boolean,
	value integer,
	category integer,
	slots integer,
	usable_by integer,
	breeds integer,
	professions integer,
	false_prof boolean,
	factions integer,
	expansions integer,
	equip_time float,
	description text);

drop table if exists weapons cascade;
create table weapons (
	id integer primary key,
	min_damage integer,
	max_damage integer,
	crit_damage integer,
	attack_time float,
	recharge_time float,
	range integer,
	ammo_type integer,
	armor_type integer,
	burst_delay float,
	fullauto_delay float,
	max_attack_skill integer,
	multiwield_req integer,
	init_skill integer,
	burst boolean,
	flingshot boolean,
	fullauto boolean,
	aimedshot boolean,
	sneakattack boolean,
	fastattack boolean,
	brawl boolean,
	dimach boolean,
	foreign key (id) references items(id),
	foreign key (armor_type) references attributes(id),
	foreign key (init_skill) references attributes(id));

drop table if exists nanos cascade;
create table nanos (
	id integer primary key,
	nanoline integer,
	nano_cost integer,
	ncu_cost integer,
	attack_time float,
	recharge_time float,
	duration float,
	range integer,
	stacking_order integer,
	break_on_attack integer,
	break_on_debuff integer,
	break_on_nano integer,
	foreign key (id) references items(id));

drop table if exists perks cascade;
create table perks (
	id integer primary key,
	perkid integer unique,
	tier integer,
	kind integer,
	foreign key (id) references items(id));

drop table if exists item_words cascade;
create table item_words (
	item integer not null,
	word varchar(50) not null,
	unique (item, word),
	foreign key (item) references items(id));
create index item_words_word on item_words(word);

drop table if exists item_interps cascade;
create table item_interps (
	low_id integer unique,
	high_id integer unique,
	foreign key (high_id) references items(id),
	foreign key (low_id) references items(id));

drop table if exists item_requires cascade;
create table item_requires (
	item integer,
	attr integer,
	value integer,
	unique (item, attr),
	foreign key (item) references items(id),
	foreign key (attr) references attributes(id));
create index item_requires_item on item_requires(item);
create index item_requires_attr on item_requires(attr);

drop table if exists item_modifiers cascade;
create table item_modifiers (
	item integer,
	attr integer,
	value integer,
	unique (item, attr),
	foreign key (item) references items(id),
	foreign key (attr) references attributes(id));
create index item_modifiers_item on item_modifiers(item);
create index item_modifiers_attr on item_modifiers(attr);

drop table if exists item_links cascade;
create table item_links (
	item integer,
	relation text,
	target integer,
	detail text,
	foreign key (item) references items(id),
	foreign key (target) references items(id));
create index item_links_item on item_links (item);
create index item_links_target on item_links (target);

drop table if exists item_effects cascade;
create table item_effects (
	item integer,
	effect text,
	value1 integer,
	value2 integer,
	foreign key (item) references items(id));
create index item_effects_item on item_effects(item);
