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)
)


create table entity_activity (
	mid int PRIMARY KEY,
	license_no int,
	act_code int,
	act_desc varchar(100)  CHARACTER SET utf8 COLLATE utf8_bin,
	act_ct_cd varchar(10),
	act_ct_dsc varchar(100)  CHARACTER SET utf8 COLLATE utf8_bin,
	act_lns_dt datetime,
	act_cancl_dt datetime,
	act_stat_id varchar(10)  CHARACTER SET utf8 COLLATE utf8_bin,
	act_stat_desc varchar(100)  CHARACTER SET utf8 COLLATE utf8_bin);

ALTER TABLE `entity_activity` ADD INDEX `license_no` (`license_no`);
ALTER TABLE `entity_activity` ADD INDEX `act_code` (`act_code`);

create table entity_activity_amnd (
	mid int PRIMARY KEY,
	license_no int,
	trans_no int,
	act_code int,
	act_desc varchar(100)  CHARACTER SET utf8 COLLATE utf8_bin,
	act_ct_cd varchar(10),
	act_ct_dsc varchar(100)  CHARACTER SET utf8 COLLATE utf8_bin,
	act_lns_dt datetime,
	act_cancl_dt datetime,
	act_stat_id varchar(10)  CHARACTER SET utf8 COLLATE utf8_bin,
	act_stat_desc varchar(100)  CHARACTER SET utf8 COLLATE utf8_bin);

ALTER TABLE `entity_activity_amnd` ADD INDEX `license_no` (`license_no`);
ALTER TABLE `entity_activity_amnd` ADD INDEX `trans_no` (`trans_no`);
ALTER TABLE `entity_activity_amnd` ADD INDEX `act_code` (`act_code`);

create table entity_license (
	mid int PRIMARY KEY,
	license_no int,
	license_dt datetime,
	area_id int,
	area_name_e varchar(40) CHARACTER SET utf8 COLLATE utf8_bin,
	sub_area_id int,
	sub_area_name_e varchar(40) CHARACTER SET utf8 COLLATE utf8_bin,
	lgl_typ_cd int,
	lgl_typ_dsc  varchar(100)  CHARACTER SET utf8 COLLATE utf8_bin,
	lns_st_cd int,
	lns_st_dsc  varchar(100)  CHARACTER SET utf8 COLLATE utf8_bin,
	longitude float,
	latitude float,
	unit_type_id int,
	unit_type_dsc varchar(100)  CHARACTER SET utf8 COLLATE utf8_bin,
	address varchar(200)  CHARACTER SET utf8 COLLATE utf8_bin)

ALTER TABLE `entity_license` ADD INDEX `license_no` (`license_no`);
ALTER TABLE `entity_license` ADD INDEX `license_dt` (`license_dt`);
ALTER TABLE `entity_license` ADD INDEX `area_id` (`area_id`);
ALTER TABLE `entity_license` ADD INDEX `sub_area_id` (`sub_area_id`);
ALTER TABLE `entity_license` ADD INDEX `lgl_typ_cd` (`lgl_typ_cd`);
ALTER TABLE `entity_license` ADD INDEX `lns_st_cd` (`lns_st_cd`);
ALTER TABLE `entity_license` ADD INDEX `unit_type_id` (`unit_type_id`);


create table entity_license_amd (
	mid int PRIMARY KEY,
	license_no int,
	trans_no int,
	trans_dt date,
	license_dt date,
	license_rnw_dt date,
	area_id int,
	area_name_e varchar(40) CHARACTER SET utf8 COLLATE utf8_bin,
	sub_area_id int,
	sub_area_name_e varchar(40) CHARACTER SET utf8 COLLATE utf8_bin,
	lgl_typ_cd int,
	lgl_typ_dsc  varchar(100)  CHARACTER SET utf8 COLLATE utf8_bin,
	lns_st_cd int,
	lns_st_dsc  varchar(100)  CHARACTER SET utf8 COLLATE utf8_bin,
	longitude float,
	latitude float,
	acqusition varchar(1),
	capital_chng varchar(1));

ALTER TABLE `entity_license_amd` ADD INDEX `license_no` (`license_no`);
ALTER TABLE `entity_license_amd` ADD INDEX `license_dt` (`license_dt`);
ALTER TABLE `entity_license_amd` ADD INDEX `license_rnw_dt` (`license_rnw_dt`);
ALTER TABLE `entity_license_amd` ADD INDEX `trans_no` (`trans_no`);
ALTER TABLE `entity_license_amd` ADD INDEX `trans_dt` (`trans_dt`);
ALTER TABLE `entity_license_amd` ADD INDEX `area_id` (`area_id`);
ALTER TABLE `entity_license_amd` ADD INDEX `sub_area_id` (`sub_area_id`);
ALTER TABLE `entity_license_amd` ADD INDEX `lgl_typ_cd` (`lgl_typ_cd`);
ALTER TABLE `entity_license_amd` ADD INDEX `lns_st_cd` (`lns_st_cd`);


create table entity_partner (
	mid int PRIMARY KEY,
	license_no int,
	ptr_no int,
	ptr_dt date,
	ptr_cncl_dt date,
	ptr_stat_id int,
	ptr_stat_dsc_e varchar(15)  CHARACTER SET utf8 COLLATE utf8_bin);

ALTER TABLE `entity_partner` ADD INDEX `license_no` (`license_no`);
ALTER TABLE `entity_partner` ADD INDEX `ptr_no` (`ptr_no`);
ALTER TABLE `entity_partner` ADD INDEX `ptr_dt` (`ptr_dt`);
ALTER TABLE `entity_partner` ADD INDEX `ptr_cncl_dt` (`ptr_cncl_dt`);
ALTER TABLE `entity_partner` ADD INDEX `ptr_stat_id` (`ptr_stat_id`);


create table entity_partner_amd (
	mid int PRIMARY KEY,
	license_no int,
	trans_no int,
	ptr_no int,
	ptr_dt date,
	ptr_cncl_dt date,
	ptr_stat_id int,
	ptr_stat_dsc_e varchar(15)  CHARACTER SET utf8 COLLATE utf8_bin);

ALTER TABLE `entity_partner_amd` ADD INDEX `license_no` (`license_no`);
ALTER TABLE `entity_partner_amd` ADD INDEX `trans_no` (`trans_no`);
ALTER TABLE `entity_partner_amd` ADD INDEX `ptr_no` (`ptr_no`);
ALTER TABLE `entity_partner_amd` ADD INDEX `ptr_dt` (`ptr_dt`);
ALTER TABLE `entity_partner_amd` ADD INDEX `ptr_cncl_dt` (`ptr_cncl_dt`);
ALTER TABLE `entity_partner_amd` ADD INDEX `ptr_stat_id` (`ptr_stat_id`);
