

CREATE TABLE market_assesment.individuals (
	individual_id int4 NOT NULL DEFAULT nextval('market_assesment.individuals_individual_id_seq'::regclass),
	name_surname varchar(255) NOT NULL,
	experience_level varchar(255) NULL,
	contact varchar(255) NOT NULL,
	profession varchar(255) NULL,
	education varchar(255) NULL,
	company_training varchar(255) NULL,
	training_interest varchar(255) NULL,
	registration_id int4 NULL,
	CONSTRAINT individuals_experience_level_check CHECK((experience_level)::text = ANY ((ARRAY['Студент'::character varying, 'Професионалец'::character varying])::text[])),
	CONSTRAINT individuals_pkey PRIMARY KEY (individual_id),
	CONSTRAINT individuals_registration_id_fk FOREIGN KEY (registration_id) REFERENCES market_assesment.registrations(registration_id)
)
WITH (
	OIDS=FALSE
);

CREATE TABLE market_assesment.organizations (
	organization_id int4 NOT NULL DEFAULT nextval('market_assesment.organizations_organization_id_seq'::regclass),
	org_name varchar(255) NOT NULL,
	org_type varchar(255) NOT NULL,
	register_number varchar(255) NOT NULL,
	employee_size int4 NULL,
	contact varchar(255) NOT NULL,
	database_in_use varchar(255) NULL,
	registration_id int4 NULL,
	CONSTRAINT organizations_pkey PRIMARY KEY (organization_id),
	CONSTRAINT organizations_registration_id_fk FOREIGN KEY (registration_id) REFERENCES market_assesment.registrations(registration_id)
)
WITH (
	OIDS=FALSE
);

CREATE TABLE market_assesment.registrations (
	registration_id int4 NOT NULL DEFAULT nextval('market_assesment.registrations_registration_id_seq'::regclass),
	username varchar(80) NOT NULL,
	password varchar(255) NOT NULL,
	register_date timestamp NULL DEFAULT now(),
	user_type varchar(13) NULL,
	CONSTRAINT registrations_pkey PRIMARY KEY (registration_id),
	CONSTRAINT registrations_user_type_check CHECK((user_type)::text = ANY ((ARRAY['Организација'::character varying, 'Поединец'::character varying])::text[])),
	CONSTRAINT registrations_username_key UNIQUE (username)
)
WITH (
	OIDS=FALSE
);

CREATE TABLE market_assesment.services (
	service_id int4 NOT NULL DEFAULT nextval('market_assesment.services_service_id_seq'::regclass),
	service_direction varchar(4) NOT NULL,
	service_name varchar(255) NULL,
	service_type varchar(255) NOT NULL,
	database_name varchar(255) NULL,
	database_type varchar(255) NULL,
	dbms_version varchar(255) NULL,
	service_description text NULL,
	salary varchar(255) NULL,
	status varchar(8) NOT NULL,
	start_date date NOT NULL,
	end_date date NULL,
	price varchar(255) NULL,
	organization_id int4 NULL,
	individual_id int4 NULL,
	CONSTRAINT services_pkey PRIMARY KEY (service_id),
	CONSTRAINT services_status_check CHECK((status)::text = ANY ((ARRAY['Активна'::character varying, 'Неактивна'::character varying])::text[])),
	CONSTRAINT services_individual_fkey FOREIGN KEY (individual_id) REFERENCES market_assesment.individuals(individual_id),
	CONSTRAINT services_organization_fkey FOREIGN KEY (organization_id) REFERENCES market_assesment.organizations(organization_id)
)
WITH (
	OIDS=FALSE
);

CREATE TABLE market_assesment.users (
	user_id int4 NOT NULL DEFAULT nextval('market_assesment.users_user_id_seq'::regclass),
	last_login timestamp NULL DEFAULT now(),
	CONSTRAINT users_pkey PRIMARY KEY (user_id)
)
WITH (
	OIDS=FALSE
);