CREATE TABLE IF NOT EXISTS tbl_users (
  int_user_id int(11) NOT NULL,
  txt_name varchar (100) not null,
  txt_password varchar(100) NOT NULL,
  db_role_type smallint(6) NOT NULL,
  allowed_login int(11) NOT NULL,
  dt_created datetime NULL,
  dt_modified datetime NULL,
  by_user INT(11) NULL,
  CONSTRAINT pk_user PRIMARY KEY (int_user_id)
);
create table if not exists tbl_type (
    int_type_id int not null AUTO_INCREMENT,
    int_category_id int not null,
    int_type int not null,
    txt_name varchar (100) not null,
    ysn_fixed boolean not null default 0,
    CONSTRAINT pk_type PRIMARY KEY (int_type_id)
);
CREATE TABLE IF NOT EXISTS tbl_organisation (
    int_organisation_id int not null,
    txt_name varchar (100) not null,
    txt_short_name varchar (25) not null,
	txt_contact_person varchar (60) not null,
	txt_address varchar (200) null,
	txt_mobile varchar (25) null,
    txt_mobile_whatsapp varchar (25) null,
	txt_email varchar (100) null,
	txt_gstn varchar(50) null,
	txt_pan varchar(25) null,
	blb_logo mediumblob null,
    CONSTRAINT pk_org PRIMARY KEY (int_organisation_id)
);
CREATE TABLE IF NOT EXISTS tbl_financial_years (
    int_financial_year_id int not null,
	dt_start datetime not null,
	dt_end datetime not null,
	ysn_current int default 0,
	int_reactor_count int null,
	int_coulumn_count int null,
	int_drum_count int null,
	int_purchase_count int null,
	int_sale_count int null,
	int_batch_no_count int null,
    CONSTRAINT pk_finance PRIMARY KEY (int_financial_year_id)
);
CREATE TABLE IF NOT EXISTS tbl_parties (
    int_party_id int not null,
    txt_name varchar (100) not null,
    txt_short_name varchar (25) not null,
	txt_contact_person varchar (60) null,
	txt_address varchar (200) null,
	txt_city varchar(25) null,
	txt_mobile varchar (25) null,
    txt_mobile_whatsapp varchar (25) null,
	txt_email varchar (100) null,
	txt_gstn varchar(50) null,
	txt_pan varchar(25) null,
	int_user_id int(11) NOT NULL,
    CONSTRAINT pk_party PRIMARY KEY (int_party_id),
    CONSTRAINT fk_party_user FOREIGN KEY (int_user_id) REFERENCES tbl_users(int_user_id)
);
CREATE TABLE IF NOT EXISTS tbl_products (
	int_product_id int not null,
    txt_product_name varchar (100) not null,
    txt_hsn_code varchar (25) not null,
    int_user_id int(11) NOT NULL,
	CONSTRAINT pk_prod PRIMARY KEY (int_product_id),
	CONSTRAINT fk_prod_user FOREIGN KEY (int_user_id) REFERENCES tbl_users(int_user_id)
);
CREATE TABLE IF NOT EXISTS tbl_production_units (
	int_production_unit_id int not null,
    txt_production_unit varchar (100) not null,
    int_production_unit_type int,
    int_user_id int(11) NOT NULL,
	CONSTRAINT pk_prod_unit PRIMARY KEY (int_production_unit_id),
	CONSTRAINT fk_prod_unit_user FOREIGN KEY (int_user_id) REFERENCES tbl_users(int_user_id)
);
CREATE TABLE IF NOT EXISTS tbl_storage_units (
	int_storage_unit_id int not null,
    txt_storage varchar (100) not null,
    int_storage_capacity int null,
    int_user_id int(11) NOT NULL,
	CONSTRAINT pk_storage PRIMARY KEY (int_storage_unit_id),
	CONSTRAINT fk_storage_user FOREIGN KEY (int_user_id) REFERENCES tbl_users(int_user_id)
);
CREATE TABLE IF NOT EXISTS tbl_purchase_order (
    int_purchase_order_id int not null,
    dt_transaction date not null,
    int_number int not null,
    txt_formatted_number varchar (25) not null,
    int_party_id int not null,
	int_product_id int not null,
    int_agent_party_id int null,
    txt_party_document_no varchar (100) null,
	dbl_rate decimal (15,2) not null default 0,
	dbl_total_gross_weight decimal (15,3) not null,
	dbl_total_tare_weight decimal (15,3) not null,
	dbl_total_net_weight decimal (15,3) not null,
	dbl_total_product_value decimal (15,2) not null default 0,
	dbl_total_freight_value decimal (15,2) not null default 0,
	dbl_total_tax_value decimal (15,2) not null default 0,
    txt_remarks varchar (250) null,   
	int_organisation_id int null,
	int_user_id int(11) NOT NULL,
	CONSTRAINT pk_purchase PRIMARY KEY (int_purchase_order_id),
	CONSTRAINT fk_purchase_party FOREIGN KEY (int_party_id) REFERENCES tbl_parties(int_party_id),
	CONSTRAINT fk_purchase_prod FOREIGN KEY (int_product_id) REFERENCES tbl_products(int_product_id),
	CONSTRAINT fk_purchase_agent_party FOREIGN KEY (int_agent_party_id) REFERENCES tbl_parties(int_party_id),
	CONSTRAINT fk_purchase_org FOREIGN KEY (int_organisation_id) REFERENCES tbl_organisation(int_organisation_id),
	CONSTRAINT fk_purchase_user FOREIGN KEY (int_user_id) REFERENCES tbl_users(int_user_id)
	
);
CREATE TABLE IF NOT EXISTS tbl_purchase_order_detail (
    int_purchase_order_detail_id int not null,
    int_purchase_order_id int not null,
    dbl_gross_weight decimal (15,3) not null,
	dbl_tare_weight decimal (15,3) not null,
	dbl_net_weight decimal (15,3) not null,
	int_user_id int(11) NOT NULL,
	CONSTRAINT pk_purchase_detail PRIMARY KEY (int_purchase_order_detail_id),
    CONSTRAINT fk_purchase_detail_purchase FOREIGN KEY (int_purchase_order_id) REFERENCES tbl_purchase_order(int_purchase_order_id),
    CONSTRAINT fk_purchase_detail_user FOREIGN KEY (int_user_id) REFERENCES tbl_users(int_user_id)
	
);
CREATE TABLE IF NOT EXISTS tbl_production (
    int_production_id int not null,
	int_production_unit_id int not null,
	txt_batch_no varchar(25) not null,
	dt_start datetime null,
	dt_end datetime null,
	int_organisation_id int null,
	txt_remarks varchar (250) null, 
	int_user_id int(11) NOT NULL,
	CONSTRAINT pk_production PRIMARY KEY (int_production_id),
    CONSTRAINT fk_production_unit FOREIGN KEY (int_production_unit_id) REFERENCES tbl_production_units(int_production_unit_id),
	CONSTRAINT fk_production_org FOREIGN KEY (int_organisation_id) REFERENCES tbl_organisation(int_organisation_id),
	CONSTRAINT fk_production_user FOREIGN KEY (int_user_id) REFERENCES tbl_users(int_user_id)
	
);
CREATE TABLE IF NOT EXISTS tbl_sales_order (
    int_sales_order_id int not null,
    dt_transaction date not null,
    int_number int not null,
    txt_formatted_number varchar (25) not null,
    int_party_id int not null,
	int_product_id int not null,
    int_agent_party_id int null,
	dbl_rate decimal (15,2) not null default 0,
	dbl_total_weight decimal (15,3) not null,
	dbl_total_product_value decimal (15,2) not null default 0,
	dbl_total_freight_value decimal (15,2) not null default 0,
	dbl_total_tax_value decimal (15,2) not null default 0,
    txt_remarks varchar (250) null,   
	int_organisation_id int null,
	int_user_id int(11) NOT NULL,
	CONSTRAINT pk_sales PRIMARY KEY (int_sales_order_id),
	CONSTRAINT fk_sales_party FOREIGN KEY (int_party_id) REFERENCES tbl_parties(int_party_id),
	CONSTRAINT fk_sales_prod FOREIGN KEY (int_product_id) REFERENCES tbl_products(int_product_id),
	CONSTRAINT fk_sales_agent_party FOREIGN KEY (int_agent_party_id) REFERENCES tbl_parties(int_party_id),
	CONSTRAINT fk_sales_org FOREIGN KEY (int_organisation_id) REFERENCES tbl_organisation(int_organisation_id),
	CONSTRAINT fk_sales_user FOREIGN KEY (int_user_id) REFERENCES tbl_users(int_user_id)
	
);

CREATE TABLE IF NOT EXISTS tbl_stock_movement (
    int_stock_movement_id int not null,
    dt_transaction date not null,
	int_product_id int not null,
	dbl_quantity decimal (15,3) not null,
	int_source_purchase_id int null,
	int_source_storage_unit_id int null,
	int_source_production_id int null,
	txt_source_drum_tag varchar(25) null,
	int_opening_id int null,
	int_target_sales_order_id int null,
	int_target_storage_unit_id int null,
	int_target_production_id int null,
	txt_target_drum_tag varchar(25) null,
	txt_remarks varchar (250) null, 
	int_organisation_id int null,
	int_user_id int(11) NOT NULL,
	CONSTRAINT pk_stock_mov PRIMARY KEY (int_stock_movement_id),
	CONSTRAINT fk_stock_mov_prod FOREIGN KEY (int_product_id) REFERENCES tbl_products(int_product_id),
	CONSTRAINT fk_stock_mov_purchase FOREIGN KEY (int_source_purchase_id) REFERENCES tbl_purchase_order(int_purchase_order_id),
	CONSTRAINT fk_stock_mov_source_storage FOREIGN KEY (int_source_storage_unit_id) REFERENCES tbl_storage_units(int_storage_unit_id),
	CONSTRAINT fk_stock_mov_production FOREIGN KEY (int_source_production_id) REFERENCES tbl_production(int_production_id),
	CONSTRAINT fk_stock_mov_sales FOREIGN KEY (int_target_sales_order_id) REFERENCES tbl_sales_order(int_sales_order_id),
	CONSTRAINT fk_stock_mov_target_storage FOREIGN KEY (int_target_storage_unit_id) REFERENCES tbl_storage_units(int_storage_unit_id),
	CONSTRAINT fk_stock_mov_target_production FOREIGN KEY (int_target_production_id) REFERENCES tbl_production(int_production_id),
	CONSTRAINT fk_stock_mov_org FOREIGN KEY (int_organisation_id) REFERENCES tbl_organisation(int_organisation_id),
	CONSTRAINT fk_stock_mov_user FOREIGN KEY (int_user_id) REFERENCES tbl_users(int_user_id)
);