/*
Require Screens
1 - Account Schedule entry screen
2 - Account Sub Schedule screen
3 - Account Code and mapping screen
4 - Mapping Service and Item with accounts, cost centre and module
5 - Voucher entry with party and bank
6 – Reconciliation for Bank statement
7 - Reports - Trial Balance, GL, Journal Print, Cash Book, Receipt Payment, Bank Book, P/L, Balance Sheet
*/

-- --------------------------------------------------------
-- Table structure for account_type - Type of accounts
-- --------------------------------------------------------
DROP TABLE IF EXISTS account_type;
CREATE TABLE account_type  (
  id int NOT NULL AUTO_INCREMENT,
  account_type_name varchar(50) NOT NULL,
  created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id) USING BTREE
) ENGINE = InnoDB;


-- --------------------------------------------------------------------
-- Table structure for account_schedule - List of account schedule
-- --------------------------------------------------------------------
DROP TABLE IF EXISTS account_schedule;
CREATE TABLE account_schedule  (
	id int NOT NULL AUTO_INCREMENT,
	account_type_id int NOT NULL, /*Table - account_type - ID*/
	account_schedule_name varchar(200) NOT NULL,
	is_system	enum('Y', 'N') DEFAULT 'N' NOT NULL,
	is_active	enum('Y', 'N') DEFAULT 'Y' NOT NULL,
	created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
	created_by int NOT NULL,
	updated_at datetime NULL DEFAULT NULL,
	updated_by int NULL DEFAULT NULL,
  PRIMARY KEY (id) USING BTREE
) ENGINE = InnoDB;


-- --------------------------------------------------------------------------------
-- Table structure for account_sub_schedule - List of account sub scheudule
-- --------------------------------------------------------------------------------
DROP TABLE IF EXISTS account_sub_schedule;
CREATE TABLE account_sub_schedule  (
	id int NOT NULL AUTO_INCREMENT,
	account_type_id int NOT NULL, /*Table - account_type - ID*/
	account_schedule_id int NOT NULL, /*Table - account_schedule - ID*/
	account_sub_schedule_name varchar(200) NOT NULL,
	is_system	enum('Y', 'N') DEFAULT 'N' NOT NULL,
	is_active	enum('Y', 'N') DEFAULT 'Y' NOT NULL,
	created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
	created_by int NOT NULL,
	updated_at datetime NULL DEFAULT NULL,
	updated_by int NULL DEFAULT NULL,
  PRIMARY KEY (id) USING BTREE
) ENGINE = InnoDB;


-- --------------------------------------------------------------------------------
-- Table structure for account_heads - all type of accounts
-- --------------------------------------------------------------------------------
DROP TABLE IF EXISTS account_heads;
CREATE TABLE account_heads  (
	id int NOT NULL AUTO_INCREMENT,
	account_type_id int NOT NULL, /*Table - account_type - ID*/
	account_schedule_id int NOT NULL, /*Table - account_schedule - ID*/
	account_sub_schedule_id int NULL DEFAULT NULL,
	account_code INT NOT NULL,
	account_head_name varchar(200) NOT NULL,
	is_system enum('Y', 'N') DEFAULT 'N' NOT NULL,
	is_active enum('Y', 'N') DEFAULT 'Y' NOT NULL,
	created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
	created_by int NOT NULL,
	updated_at datetime NULL DEFAULT NULL,
	updated_by int NULL DEFAULT NULL,
  PRIMARY KEY (id) USING BTREE
) ENGINE = InnoDB;




-- --------------------------------------------------------------------------------
-- Table structure for org_branches - all company
-- --------------------------------------------------------------------------------
DROP TABLE IF EXISTS companies;
CREATE TABLE org_companies  (
	id int NOT NULL AUTO_INCREMENT,
	org_code varchar(50) NOT NULL, /*Lookup*/
	company_name varchar(200) NOT NULL,
	is_active	enum('Y', 'N') DEFAULT 'Y' NOT NULL,
	created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
	created_by int NOT NULL,
	updated_at datetime NULL DEFAULT NULL,
	updated_by int NULL DEFAULT NULL,
  PRIMARY KEY (id) USING BTREE
) ENGINE = InnoDB;


-- --------------------------------------------------------------------------------
-- Table structure for org_branches - all branches
-- --------------------------------------------------------------------------------
DROP TABLE IF EXISTS org_branches;
CREATE TABLE org_branches  (
	id int NOT NULL AUTO_INCREMENT,
	org_code varchar(50) NOT NULL, /*Lookup*/
	company_id int not null,
	branch_name varchar(200) NOT NULL,
	is_active	enum('Y', 'N') DEFAULT 'Y' NOT NULL,
	created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
	created_by int NOT NULL,
	updated_at datetime NULL DEFAULT NULL,
	updated_by int NULL DEFAULT NULL,
  PRIMARY KEY (id) USING BTREE
) ENGINE = InnoDB;


-- --------------------------------------------------------------------------------
-- Table structure for branchwise_cost_centres - all branchwise cost centres
-- --------------------------------------------------------------------------------
DROP TABLE IF EXISTS branchwise_cost_centres;
CREATE TABLE branchwise_cost_centres (
	id int NOT NULL AUTO_INCREMENT,
	org_branche_id	int NOT NULL,
	cost_center_name varchar(200) NOT NULL,
	is_active	enum('Y', 'N') DEFAULT 'Y' NOT NULL,
	created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
	created_by int NOT NULL,
	updated_at datetime NULL DEFAULT NULL,
	updated_by int NULL DEFAULT NULL,
  PRIMARY KEY (id) USING BTREE
) ENGINE = InnoDB;


-- -----------------------------------------------------------------------------------------------------
-- Table structure for account_service_item_map - list of account headss mapped with service and items
-- -----------------------------------------------------------------------------------------------------
/*account_map_lavel for diffrent modules
Lab - 1 - lab_department, 2 - lab_sub_department, 3 - lab_analyte_master
Service(IPD,OPD,Emergency) - 1 - service_category, 2 - service_sub_categories, 3 - service_items
Item - 1-item_sub_category, 2 -medicine_group 3 - pharmacy_items
Expenses - 
*/

DROP TABLE IF EXISTS account_service_item_map;
CREATE TABLE account_service_item_map  (
	id int NOT NULL AUTO_INCREMENT,
	account_service_map_code	varchar(200) NOT NULL,
	account_head_id int NULL DEFAULT NULL, /*Table - account_heads - ID */
	cost_centre_id int  NOT NULL,
	account_map_desc varchar(200) NOT NULL,
	account_map_type enum('M', 'G'), /*M-Module wise account code, G-General Account code*/
	account_map_module_id	int NOT NULL, /*Table - permission - ID*/
	account_map_lavel enum('1', '2', '3', '4') NOT NULL, /*1-category, 2-subcategory, 3-actual item/service, 4-use default account code */
  	has_multiple enum('Y','N') DEFAULT 'Y' NOT NULL,
	is_active	enum('Y', 'N') DEFAULT 'Y' NOT NULL,
	is_system	enum('Y', 'N') DEFAULT 'N' NOT NULL,
	created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
	created_by int NOT NULL,
	updated_at datetime NULL DEFAULT NULL,
	updated_by int NULL DEFAULT NULL,
  PRIMARY KEY (id) USING BTREE
) ENGINE = InnoDB;


DROP TABLE IF EXISTS account_service_item_map_details;
CREATE TABLE account_service_item_map_details  (
	id int NOT NULL AUTO_INCREMENT,
	account_service_item_map_id int NULL DEFAULT NULL, /*Table - account_service_item_map - ID */
	account_map_lavel_id	int NOT NULL,
	is_active	enum('Y', 'N') DEFAULT 'Y' NOT NULL,
	created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
	created_by int NOT NULL,
	updated_at datetime NULL DEFAULT NULL,
	updated_by int NULL DEFAULT NULL,
	PRIMARY KEY (id) USING BTREE
) ENGINE = InnoDB;


-- --------------------------------------
-- Table structure for voucher_narration
-- --------------------------------------
DROP TABLE IF EXISTS voucher_narration;
CREATE TABLE voucher_narration  (
	id int NOT NULL AUTO_INCREMENT,
	voucher_date date NOT NULL,
	voucher_number	varchar(100)  NOT NULL, /*Auto Generated unique ID*/
	narration varchar(500) NOT NULL,
	transaction_type enum('Cs','Tr','Cl') NOT NULL, /* C-Cash, A-Adjustment, L-Clearing*/
	voucher_status enum('U','A','D', 'R') NOT NULL, 
	is_balance_generated enum('Y', 'N') NOT NULL DEFAULT 'N',
	reference_id int NOT NULL DEFAULT 0,
	reference_table varchar(255) DEFAULT NULL,
	created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
	created_by int NOT NULL,
	updated_at datetime NULL DEFAULT NULL,
	updated_by int NULL DEFAULT NULL,
	approve_at datetime NULL DEFAULT NULL,
	approve_by int NULL DEFAULT NULL,
	PRIMARY KEY (id) USING BTREE
) ENGINE = InnoDB;


-- --------------------------------------
-- Table structure for voucher_details
-- --------------------------------------
DROP TABLE IF EXISTS voucher_details;
CREATE TABLE voucher_details  (
	id int NOT NULL AUTO_INCREMENT,
	voucher_narration_id	int NOT NULL, /*Table-voucher_narration-ID*/
	account_head_id int NOT NULL, /* Table-account_heads-ID*/
	voucher_amount decimal(20, 2) NOT NULL,
	debit_credit_flag enum('C','D') NOT NULL, /* C-credit D-debit*/
	cost_centre_id int  NOT NULL,
	created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
	created_by int NOT NULL,
	updated_at datetime NULL DEFAULT NULL,
	updated_by int NULL DEFAULT NULL,
	PRIMARY KEY (id) USING BTREE
) ENGINE = InnoDB;


-- --------------------------------------
-- Table structure for voucher_party_details
-- --------------------------------------
DROP TABLE IF EXISTS voucher_party_details;
CREATE TABLE voucher_party_details  (
	id int NOT NULL AUTO_INCREMENT,
	voucher_detail_id	int NOT NULL, /*Table-voucher_details-ID*/
	party_type enum ('C', 'V') DEFAULT NULL, /*C-Customer, V-Vendor*/
	party_category enum	('P', 'S', 'F', 'B') DEFAULT NULL, /*P-Patient, S-Suplier, F-Staff, B-Bank*/
	party_id int NULL,
	created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
	created_by int NOT NULL,
	updated_at datetime NULL DEFAULT NULL,
	updated_by int NULL DEFAULT NULL,
	PRIMARY KEY (id) USING BTREE
) ENGINE = InnoDB;


-- ----------------------------
-- Table structure for acc_balance
-- ----------------------------
DROP TABLE IF EXISTS acc_balance;
CREATE TABLE acc_balance  (
  balance_date date NOT NULL,
  acc_code int NOT NULL,
  balance_amount decimal(20, 2) NOT NULL,
  PRIMARY KEY (balance_date, acc_code) USING BTREE
) ENGINE = InnoDB;

