create database arabdemo_ded;
grant all on arabdemo_ded.* to "arabdemo_ded"@"localhost" identified by "ded909";

use arabdemo_ded;


create table mst_activities (
	act_id	int auto_increment primary key,
	act_desc_en	varchar(100),
	act_desc_ar	varchar(100)  CHARACTER SET utf8 COLLATE utf8_bin
	);

create table mst_subactivities (
	subact_id	int auto_increment primary key,
	act_id	int,
	subact_desc_en	varchar(200),
	subact_desc_ar	varchar(200)  CHARACTER SET utf8 COLLATE utf8_bin
	);

create table mst_area (
	area_id	int auto_increment primary key,
	area_desc_en	varchar(50),
	area_desc_ar	varchar(50)  CHARACTER SET utf8 COLLATE utf8_bin
	);

create table mst_subarea (
	subarea_id 	int auto_increment primary key,
	area_id	int(11),
	subarea_name_ar	varchar(100),
	subarea_name_en	varchar(100)  CHARACTER SET utf8 COLLATE utf8_bin
	);

create table business_entities (
	entity_id	int auto_increment primary key,
	enitity_name_en	varchar(100),
	enitity_name_ar	varchar(100)  CHARACTER SET utf8 COLLATE utf8_bin,
	formation_date	date,
	act_id	int,
	area_id	int,
	subarea_id 	int,
	comm_id	int,
	legalform_id	int,
	legalstat_id	int,
	date_updt	datetime
	);

create table entities_history (
	hist_id int auto_increment primary key,
	entity_id	int,
	transactions_id	int,
	histdate	date,
	);

create table mst_transactions (
	transactions_id	int auto_increment primary key,
	transactions_desc_en	varchar(100),
	transactions_desc_ar	varchar(100)  CHARACTER SET utf8 COLLATE utf8_bin
	);

create table mst_legal_form (
	legalform_id	int auto_increment primary key,
	legal_desc_en	varchar(100),
	legal_desc_ar	varchar(100)  CHARACTER SET utf8 COLLATE utf8_bin
	);

create table mst_legal_status (
	legalstat_id	int auto_increment primary key,
	legalstat_desc_en	varchar(100),
	legalstat_desc_ar	varchar(100)  CHARACTER SET utf8 COLLATE utf8_bin
	);

create table mst_community (
	comm_id	int auto_increment primary key,
	comm_num int,
	comm_name_en	varchar(100),
	comm_name_ar	varchar(100)  CHARACTER SET utf8 COLLATE utf8_bin,
	population	int,
	area	int,
	density	int,
	tbe	int,
	rsv	int,
	ren	int,
	rex	int,
	agr	int,
	cagr	int,
	ma	int,
	reiv	int,
	rxiv	int,
	svl3y	int,
	svl5y	int
	);

CREATE TABLE mst_user (
  uid int(11) NOT NULL AUTO_INCREMENT,
  username varchar(30) DEFAULT NULL,
  pass varchar(120) DEFAULT NULL,
  userfullname varchar(60) DEFAULT NULL,
  utype tinyint(4) DEFAULT '2',
  dt_update timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  active tinyint(4) NOT NULL DEFAULT '1',
  PRIMARY KEY (uid)
)