--First need to add this module to the modules table
INSERT INTO `module` (`name`,`script`,`class`,`active`,`public`,`canreference`,`laymenInsert`) 
VALUES ('Accounting','modules/accounting/Accounting.inc','Accounting','1','1','0','0');


# Dump of table acc_account
# ------------------------------------------------------------

CREATE TABLE `acc_account` (
  `id` int(11) NOT NULL auto_increment,
  `code` varchar(11) default NULL,
  `name` varchar(255) default NULL,
  `accountCategoryID` int(11) NOT NULL,
  `type` enum('dr','cr') default NULL,
  `sequence` int(11) default NULL,
  `company` int(11) default NULL,
  `bankDetailsID` int(11) default NULL,
  `clientEntityID` int(11) default NULL,
  `supplierEntityID` int(11) default NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `name` (`name`),
  KEY `FK_accountCateogry_acc_account` (`accountCategoryID`)
);

--want to add a deleteable flag so that certain accounts can't be deleted! -- run this still!
ALTER TABLE `acc_account` ADD `deletable` int(1) DEFAULT NULL ;


# Dump of table acc_accountCategory
# ------------------------------------------------------------

CREATE TABLE `acc_accountCategory` (
  `id` int(11) NOT NULL auto_increment,
  `name` varchar(255) default NULL,
  `parent` int(11) NOT NULL,
  `description` varchar(255) default NULL,
  `company` int(11) NOT NULL default '0',
  `refNoStart` int(5) NOT NULL default '0',
  `refNoEnd` int(5) NOT NULL default '0',
  `seq` int(11) default NULL,
  `locked` char(1) default NULL,
  PRIMARY KEY  (`id`),
  KEY `FK_accountCateogry_accountCateogry` (`parent`),
  KEY `FK_entity_accountCateogry` (`company`)
);
                                                                
INSERT INTO `acc_accountCategory` (`id`,`name`,`parent`,`description`,`company`,`refNoStart`,`refNoEnd`,`seq`,`locked`) VALUES ('1','Assets','0','Asset Accounts','2','10000','19999','2','1');
INSERT INTO `acc_accountCategory` (`id`,`name`,`parent`,`description`,`company`,`refNoStart`,`refNoEnd`,`seq`,`locked`) VALUES ('2','Liability','0','Liability Accounts','2','20000','29999','3','1');
INSERT INTO `acc_accountCategory` (`id`,`name`,`parent`,`description`,`company`,`refNoStart`,`refNoEnd`,`seq`,`locked`) VALUES ('3','Equity','0','Equity Accounts','2','30000','39999','4','1');
INSERT INTO `acc_accountCategory` (`id`,`name`,`parent`,`description`,`company`,`refNoStart`,`refNoEnd`,`seq`,`locked`) VALUES ('4','Income','0','Revenue Accounts','2','40000','49999','5','1');
INSERT INTO `acc_accountCategory` (`id`,`name`,`parent`,`description`,`company`,`refNoStart`,`refNoEnd`,`seq`,`locked`) VALUES ('5','Short Term Liabilities','2','Short Term Liabilities','2','0','0',NULL,'1');
INSERT INTO `acc_accountCategory` (`id`,`name`,`parent`,`description`,`company`,`refNoStart`,`refNoEnd`,`seq`,`locked`) VALUES ('6','Expenses','0','Expense Accounts','2','60000','69999','7','1');
INSERT INTO `acc_accountCategory` (`id`,`name`,`parent`,`description`,`company`,`refNoStart`,`refNoEnd`,`seq`,`locked`) VALUES ('7','Debtors','10','Debtors','2','0','0',NULL,'1');
INSERT INTO `acc_accountCategory` (`id`,`name`,`parent`,`description`,`company`,`refNoStart`,`refNoEnd`,`seq`,`locked`) VALUES ('8','Drawings','0','Drawings','2','0','0',NULL,'1');
INSERT INTO `acc_accountCategory` (`id`,`name`,`parent`,`description`,`company`,`refNoStart`,`refNoEnd`,`seq`,`locked`) VALUES ('9','Fixed Assests','1','Fixed Assets','2','0','0','10','1');
INSERT INTO `acc_accountCategory` (`id`,`name`,`parent`,`description`,`company`,`refNoStart`,`refNoEnd`,`seq`,`locked`) VALUES ('10','Current Assets','1','Current Assets','2','0','0','11','1');
INSERT INTO `acc_accountCategory` (`id`,`name`,`parent`,`description`,`company`,`refNoStart`,`refNoEnd`,`seq`,`locked`) VALUES ('11','Long Term Liabilities','2','Long Term Liabilities','2','0','0','12','1');
INSERT INTO `acc_accountCategory` (`id`,`name`,`parent`,`description`,`company`,`refNoStart`,`refNoEnd`,`seq`,`locked`) VALUES ('12','Creditors','5','Creditors','2','0','0',NULL,'1');

-- UPDATE `acc_accountCategory` SET `company` = (SELECT `companyID` FROM)

# Dump of table acc_balanceSheetAccountClassification
# ------------------------------------------------------------

CREATE TABLE `acc_balanceSheetAccountClassification` (
  `id` int(11) NOT NULL auto_increment,
  `name` varchar(255) default NULL,
  `seq` int(11) default NULL,
  `parentAccountClassificationID` int(11) default NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `name` (`name`)
) ;

INSERT INTO `acc_balanceSheetAccountClassification` (`id`,`name`,`seq`,`parentAccountClassificationID`) VALUES ('1','Asset','1','0');
INSERT INTO `acc_balanceSheetAccountClassification` (`id`,`name`,`seq`,`parentAccountClassificationID`) VALUES ('2','Short Term Asset','2','1');
INSERT INTO `acc_balanceSheetAccountClassification` (`id`,`name`,`seq`,`parentAccountClassificationID`) VALUES ('3','Long Term Asset','3','1');
INSERT INTO `acc_balanceSheetAccountClassification` (`id`,`name`,`seq`,`parentAccountClassificationID`) VALUES ('4','Liability','4','0');
INSERT INTO `acc_balanceSheetAccountClassification` (`id`,`name`,`seq`,`parentAccountClassificationID`) VALUES ('5','Short Term Liability','5','4');
INSERT INTO `acc_balanceSheetAccountClassification` (`id`,`name`,`seq`,`parentAccountClassificationID`) VALUES ('6','Long Term Liability','6','4');
INSERT INTO `acc_balanceSheetAccountClassification` (`id`,`name`,`seq`,`parentAccountClassificationID`) VALUES ('7','Equity','7','0');


# Dump of table acc_account_balanceSheetAccountClassification
# ------------------------------------------------------------

CREATE TABLE `acc_account_balanceSheetAccountClassification` (
  `balanceSheetAccountClassificationID` int(11) NOT NULL,
  `accountID` int(11) NOT NULL,
  PRIMARY KEY  (`balanceSheetAccountClassificationID`,`accountID`),
  KEY `FK_bSAccountCategory_account` (`accountID`)
) ;


# Dump of table acc_incomeStatementAccountClassification
# ------------------------------------------------------------

CREATE TABLE `acc_incomeStatementAccountClassification` (
  `id` int(11) default NULL
) ;

# Dump of table acc_account_incomeStatementAccountClassification
# ------------------------------------------------------------

CREATE TABLE `acc_account_incomeStatementAccountClassification` (
  `id` int(11) default NULL
) ;



# Dump of table acc_accounting
# ------------------------------------------------------------

CREATE TABLE `acc_accounting` (
  `id` int(11) NOT NULL auto_increment,
  `siteID` int(11) NOT NULL,
  `companyID` int(11) NOT NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `companyID` (`companyID`),
  KEY `FK_site_accounting` (`siteID`)
) ;




# Dump of table acc_bankDetails
# ------------------------------------------------------------

CREATE TABLE `acc_bankDetails` (
  `id` int(11) NOT NULL auto_increment,
  `accountNumber` int(11) default NULL,
  `branch` varchar(255) default NULL,
  `branchCode` varchar(255) default NULL,
  `bankName` varchar(255) default NULL,
  PRIMARY KEY  (`id`)
) ;



# Dump of table acc_chartOfAccounts
# ------------------------------------------------------------

CREATE TABLE `acc_chartOfAccounts` (
  `id` int(11) NOT NULL auto_increment,
  `refNo` int(5) NOT NULL,
  `name` varchar(255) NOT NULL,
  `description` varchar(255) default NULL,
  `type` enum('dr','cr') NOT NULL,
  `accountingEquationGroupID` int(5) NOT NULL,
  `companyID` int(11) NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `FK_accountingEquationGroup_chartOfAccounts` (`accountingEquationGroupID`),
  KEY `FK_entity_chartOfAccounts` (`companyID`)
) ;



# Dump of table acc_financialPeriod
# ------------------------------------------------------------

CREATE TABLE `acc_financialPeriod` (
  `id` int(11) default NULL,
  `start` date default NULL,
  `end` date default NULL,
  `company` int(11) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;




# Dump of table acc_generalLedger
# ------------------------------------------------------------

CREATE TABLE `acc_generalLedger` (
  `id` int(32) NOT NULL auto_increment,
  `accountDr` int(11) default NULL,
  `accountCr` int(11) default NULL,
  `amount` int(11) default NULL,
  `companyID` int(11) NOT NULL,
  `financialPeriodID` int(6) default NULL,
  `transactionDetailsID` int(11) default NULL,
  PRIMARY KEY  (`id`),
  KEY `FK_entity_generalLedger` (`companyID`)
) ;




# Dump of table acc_trainingRule
# ------------------------------------------------------------

CREATE TABLE `acc_trainingRule` (
  `id` int(11) NOT NULL auto_increment,
  `pattern` varchar(255) default NULL,
  `bankAccountID` int(11) default NULL,
  `contraAccountID` int(11) default NULL,
  `company` int(11) default NULL,
  `assignVat` int(1) default NULL,
  PRIMARY KEY  (`id`)
) ;


# Dump of table acc_transactionDetails
# ------------------------------------------------------------

CREATE TABLE `acc_transactionDetails` (
  `id` int(11) NOT NULL auto_increment,
  `date` datetime default NULL,
  `details` varchar(255) default NULL,
  `source` varchar(255) default NULL,
  `sourceType` enum('invoice','purchaseInvoice','bankStatement','creditNote') default NULL,
  PRIMARY KEY  (`id`)
) ;

# Dump of table acc_unassignedTransaction
# ------------------------------------------------------------

CREATE TABLE `acc_unassignedTransaction` (
  `id` int(11) NOT NULL auto_increment,
  `accountDr` int(11) default NULL,
  `accountCr` int(11) default NULL,
  `amount` int(11) default NULL,
  `companyID` int(11) default NULL,
  `date` datetime default NULL,
  `details` varchar(255) default NULL,
  `source` varchar(255) default NULL,
  `sourceType` enum('invoice','purchaseInvoice','bankStatement','creditNote') default NULL,
  PRIMARY KEY  (`id`)
);

#dump of account type table - this is merely a classification table that will allow us to classify accounts as for example bank, cash, expense and income accounts
CREATE TABLE `acc_accountClassification` (
  `id` int(11) NOT NULL auto_increment,
  `type` varchar(255) default NULL,
  `description` varchar(255) default NULL,
  PRIMARY KEY  (`id`)
) ;

INSERT INTO `acc_accountClassification` (`id`,`type`,`description`) VALUES ('1','BANK','Bank Account');
INSERT INTO `acc_accountClassification` (`id`,`type`,`description`) VALUES ('2','CASH','Cash Account');
INSERT INTO `acc_accountClassification` (`id`,`type`,`description`) VALUES ('3','EXPENSE','Expense Account');
INSERT INTO `acc_accountClassification` (`id`,`type`,`description`) VALUES ('4','INCOME','Income Account');

ALTER TABLE `acc_account` ADD `classificationCode` int(11) DEFAULT NULL ;


