-- ============================================================
--  sim_root_setup.sql
--  Run this file once to set up the sim_root database.
--  Import via phpMyAdmin or: mysql -u root -p < sim_root_setup.sql
-- ============================================================

CREATE DATABASE IF NOT EXISTS `sim_root`
  DEFAULT CHARACTER SET latin1
  COLLATE latin1_swedish_ci;

USE `sim_root`;

-- ─────────────────────────────────────────────────────────────
--  passmain  –  login users & permissions
-- ─────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `passmain` (
  `code`      mediumint    NOT NULL AUTO_INCREMENT,
  `user`      varchar(20)  NOT NULL,
  `password`  varchar(15)  NOT NULL,
  `usertype`  bigint       NOT NULL DEFAULT 2,   -- 1 = admin, 2 = normal
  `superflag` int          NOT NULL DEFAULT 0,
  `spltype`   bigint       NOT NULL DEFAULT 0,   -- 1 = special company access
  -- Permissions ('' = denied, '1' = allowed)
  `supplier`  varchar(1)   NOT NULL DEFAULT '',
  `customer`  varchar(1)   NOT NULL DEFAULT '',
  `container` varchar(1)   NOT NULL DEFAULT '',
  `item`      varchar(1)   NOT NULL DEFAULT '',
  `agent`     varchar(1)   NOT NULL DEFAULT '',
  `port`      varchar(1)   NOT NULL DEFAULT '',
  `bank`      varchar(1)   NOT NULL DEFAULT '',
  `cash`      varchar(1)   NOT NULL DEFAULT '',
  `expense`   varchar(1)   NOT NULL DEFAULT '',
  `unit`      varchar(1)   NOT NULL DEFAULT '',
  `openbal`   varchar(1)   NOT NULL DEFAULT '',
  `purchase`  varchar(1)   NOT NULL DEFAULT '',
  `sale`      varchar(1)   NOT NULL DEFAULT '',
  `inward`    varchar(1)   NOT NULL DEFAULT '',
  `outward`   varchar(1)   NOT NULL DEFAULT '',
  `damaged`   varchar(1)   NOT NULL DEFAULT '',
  `excess`    varchar(1)   NOT NULL DEFAULT '',
  `bankrecp`  varchar(1)   NOT NULL DEFAULT '',
  `bankpay`   varchar(1)   NOT NULL DEFAULT '',
  `cashrecp`  varchar(1)   NOT NULL DEFAULT '',
  `cashpay`   varchar(1)   NOT NULL DEFAULT '',
  `contra`    varchar(1)   NOT NULL DEFAULT '',
  `journal`   varchar(1)   NOT NULL DEFAULT '',
  `stock`     varchar(1)   NOT NULL DEFAULT '',
  `ledger`    varchar(1)   NOT NULL DEFAULT '',
  `reports`   varchar(1)   NOT NULL DEFAULT '',
  PRIMARY KEY (`code`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

-- Default admin user  (change password after first login)
INSERT INTO `passmain`
  (`user`, `password`, `usertype`, `superflag`, `spltype`,
   `supplier`,`customer`,`container`,`item`,`agent`,`port`,
   `bank`,`cash`,`expense`,`unit`,`openbal`,
   `purchase`,`sale`,`inward`,`outward`,`damaged`,`excess`,
   `bankrecp`,`bankpay`,`cashrecp`,`cashpay`,`contra`,`journal`,
   `stock`,`ledger`,`reports`)
VALUES
  ('admin', 'admin123', 1, 1, 0,
   '1','1','1','1','1','1',
   '1','1','1','1','1',
   '1','1','1','1','1','1',
   '1','1','1','1','1','1',
   '1','1','1');


-- ─────────────────────────────────────────────────────────────
--  company  –  company master
-- ─────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `company` (
  `code`     bigint UNSIGNED NOT NULL AUTO_INCREMENT,
  `name`     varchar(50)     NOT NULL,
  `pname`    varchar(50)     NOT NULL DEFAULT '',
  `add1`     varchar(50)     DEFAULT NULL,
  `add2`     varchar(50)     DEFAULT NULL,
  `city`     varchar(25)     DEFAULT NULL,
  `pin`      varchar(10)     DEFAULT NULL,
  `country`  varchar(25)     DEFAULT NULL,
  `tel`      varchar(25)     DEFAULT NULL,
  `mobile`   varchar(15)     DEFAULT NULL,
  `email`    varchar(50)     DEFAULT NULL,
  `compchng` bigint          NOT NULL DEFAULT 0,
  PRIMARY KEY (`code`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

INSERT INTO `company` (`code`, `name`, `pname`, `city`, `country`, `compchng`) VALUES
(1, 'SEA IMPORT CO.', 'Owner Name', 'Mumbai', 'India', 0);


-- ─────────────────────────────────────────────────────────────
--  compyear  –  financial year master
-- ─────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `compyear` (
  `id`        int         NOT NULL AUTO_INCREMENT,
  `cono`      bigint      NOT NULL,
  `finyear`   varchar(8)  NOT NULL,
  `yearname`  varchar(50) NOT NULL,
  `startdate` date        NOT NULL,
  `lastdate`  date        NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

INSERT INTO `compyear` (`cono`, `finyear`, `yearname`, `startdate`, `lastdate`) VALUES
(1, '2627', '2026-2027', '2026-04-01', '2027-03-31');


-- ─────────────────────────────────────────────────────────────
--  usercomp  –  user → company + year access mapping
-- ─────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `usercomp` (
  `srl`       bigint      NOT NULL AUTO_INCREMENT,
  `username`  bigint      NOT NULL,
  `spltype`   varchar(1)  NOT NULL DEFAULT '',
  `cono`      bigint      NOT NULL,
  `cname`     varchar(50) NOT NULL,
  `finyear`   varchar(8)  NOT NULL,
  `yearname`  varchar(50) NOT NULL,
  `startdate` date        NOT NULL,
  `lastdate`  date        NOT NULL,
  PRIMARY KEY (`srl`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

-- ─────────────────────────────────────────────────────────────
--  customer  –  quick customer lookup (root-level, cross-year)
-- ─────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `customer` (
  `srl`    bigint       NOT NULL AUTO_INCREMENT,
  `name`   varchar(100) NOT NULL,
  `mobile` varchar(20)  NOT NULL DEFAULT '',
  PRIMARY KEY (`srl`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;


-- ============================================================
--  Create the first year database:  sim_2627
--  (one database per financial year; add more as years roll over)
-- ============================================================

CREATE DATABASE IF NOT EXISTS `sim_2627`
  DEFAULT CHARACTER SET latin1
  COLLATE latin1_swedish_ci;

USE `sim_2627`;

-- ─────────────────────────────────────────────────────────────
--  grpmast  –  account group master (full aden schema)
--  SIM-specific overrides: 030=Shipper 040=Agent 050=CHA 060=Expense
--  Capital Account → 070 (frees 050 for CHA)
-- ─────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `grpmast` (
  `grpcode`  varchar(10) NOT NULL,
  `name`     varchar(50) NOT NULL,
  `type`     varchar(1)  NOT NULL DEFAULT '',
  `subtype`  varchar(2)  NOT NULL DEFAULT '',
  `add_info` varchar(1)  NOT NULL DEFAULT 'N',
  `initial`  varchar(5)  NOT NULL DEFAULT '',
  `headcode` smallint    NOT NULL DEFAULT 0,
  PRIMARY KEY (`grpcode`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

INSERT INTO `grpmast` (`grpcode`, `name`, `type`, `subtype`, `add_info`, `initial`, `headcode`) VALUES
-- Standard accounting groups (from aden reference)
('001', 'BANK BALANCES',               'A', 'CA', 'Y', 'BNK', 2),
('002', 'CASH BALANCES',               'A', 'CA', 'Y', 'CSH', 2),
('004', 'PURCHASES',                   'E', 'TE', 'N', 'PUR', 15),
('005', 'SALES',                       'I', 'TI', 'N', 'SAL', 13),
('006', 'PURCHASE RETURNS',            'I', 'TI', 'N', 'PRR', 13),
('007', 'SALE RETURNS',                'E', 'TE', 'N', 'SLR', 15),
('008', 'CLOSING STOCK',               'A', 'CA', 'N', 'STK', 2),
('009', 'SUNDRY CREDITORS (SUPPLIER)', 'L', 'CL', 'Y', 'SCR', 9),
('010', 'SUNDRY DEBTORS (CUSTOMER)',   'A', 'CA', 'Y', 'SDR', 2),
('011', 'INCOME',                      'I', 'I',  'N', 'INC', 12),
('012', 'EXPENSE',                     'E', 'E',  'N', 'EXP', 14),
('013', 'PETTY CASH',                  'A', 'CA', 'Y', 'PET', 2),
('014', 'PROVISIONS',                  'L', 'CL', 'N', 'PRO', 9),
('015', 'CHEQUES IN HAND',             'A', 'CA', 'Y', 'CHQ', 2),
('016', 'TRANSPORTER',                 'L', 'CL', 'Y', 'TRP', 0),
('017', 'ACCOUNT PAYABLE',             'L', 'CL', 'N', 'ACP', 9),
('018', 'FINALISATION',                'A', 'OA', 'N', 'FIN', 6),
('019', 'OPENING STOCK',               'E', 'TE', 'N', 'OST', 15),
('020', 'DUTIES & TAXES',              'L', 'CL', 'N', 'DT',  9),
('021', 'SUNDRY CREDITOR FOR EXPENSE', 'L', 'CL', 'Y', 'SCE', 9),
('028', 'DRIVER',                      'L', 'CL', 'Y', 'DRV', 9),
-- SIM-specific groups
('030', 'SHIPPER',                     'L', 'CL', 'Y', 'SHP', 9),
('040', 'AGENT',                       'L', 'CL', 'Y', 'AGT', 9),
('050', 'CHA',                         'L', 'CL', 'Y', 'CHA', 9),
('060', 'DIRECT EXPENSE',              'E', 'E',  'N', 'DEX', 14),
('070', 'CAPITAL ACCOUNT',             'L', 'SF', 'N', 'CAP', 8),
-- Additional groups from aden
('053', 'LOAN TAKEN',                  'L', 'LL', 'N', 'LT',  10),
('054', 'COMMISSION',                  'E', 'E',  'Y', 'COM', 14),
('055', 'TELEPHONE',                   'E', 'E',  'Y', 'TEL', 14),
('056', 'TRIP EXPENSE',                'E', 'E',  'Y', 'TRP', 14),
('057', 'GODOWN RENT',                 'E', 'E',  'Y', 'GR',  14),
('058', 'SECURED LOAN',                'L', 'LL', 'N', 'SLN', 10),
('059', 'LOAN AND ADVANCE',            'A', 'LA', 'N', 'LAA', 4),
('061', 'BRANCH DIVISION',             'A', 'OA', 'N', 'BD',  2),
('062', 'DEPOSITS',                    'A', 'CA', 'Y', 'DE',  2),
('063', 'FIXED ASSETS',                'A', 'AI', 'Y', 'FA',  1);


-- ─────────────────────────────────────────────────────────────
--  accmast  –  accounts master (all accountable parties)
-- ─────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `accmast` (
  `accode`    bigint       NOT NULL AUTO_INCREMENT,
  `grpcode`   varchar(10)  NOT NULL DEFAULT '',
  `name`      varchar(100) NOT NULL,
  `add1`      varchar(50)  DEFAULT NULL,
  `add2`      varchar(50)  DEFAULT NULL,
  `add3`      varchar(50)  DEFAULT NULL,
  `city`      varchar(30)  DEFAULT NULL,
  `pin`       varchar(10)  DEFAULT NULL,
  `state`     varchar(30)  DEFAULT NULL,
  `statecode` varchar(5)   DEFAULT NULL,
  `country`   varchar(30)  DEFAULT NULL,
  `tel`       varchar(25)  DEFAULT NULL,
  `mobile`    varchar(20)  DEFAULT NULL,
  `mobile2`   varchar(20)  DEFAULT NULL,
  `email`     varchar(50)  DEFAULT NULL,
  `website`   varchar(50)  DEFAULT NULL,
  `contpers`  varchar(50)  DEFAULT NULL,
  `gstno`     varchar(20)  DEFAULT NULL,
  `openbal`   double       NOT NULL DEFAULT 0,
  `dc`        varchar(1)   NOT NULL DEFAULT 'D',
  PRIMARY KEY (`accode`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

-- Fixed system accounts (accode < 50) — from aden reference
INSERT INTO `accmast` (`accode`, `grpcode`, `name`, `openbal`, `dc`) VALUES
(1,  '011', 'DISCOUNT TAKEN',      0, 'D'),
(2,  '012', 'EXCISE DUTY',         0, 'D'),
(3,  '002', 'CASH ON HAND',        0, 'D'),
(4,  '012', 'MISCELLANEOUS',       0, 'D'),
(5,  '020', 'VALUE ADDED TAX',     0, 'D'),
(6,  '020', 'SERVICE TAX',         0, 'D'),
(7,  '070', 'CAPITAL ACCOUNT',     0, 'D'),
(11, '004', 'CASH PURCHASE',       0, 'D'),
(12, '004', 'CREDIT PURCHASE',     0, 'D'),
(14, '005', 'CASH SALE',           0, 'C'),
(15, '005', 'CREDIT SALE',         0, 'C'),
(23, '008', 'CLOSING STOCK',       0, 'D'),
(24, '019', 'OPENING STOCK',       0, 'D'),
(28, '012', 'CREDIT TO INVESTORS', 0, 'D');

ALTER TABLE `accmast` AUTO_INCREMENT = 51;


-- ─────────────────────────────────────────────────────────────
--  item  –  item / product master
-- ─────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `item` (
  `code`    bigint        NOT NULL AUTO_INCREMENT,
  `name`    varchar(100)  NOT NULL,
  `unit`    varchar(20)   NOT NULL DEFAULT '',
  `opening` double        NOT NULL DEFAULT 0,
  `cost`    double        NOT NULL DEFAULT 0,
  `mrp`     double        NOT NULL DEFAULT 0,
  `msp`     double        NOT NULL DEFAULT 0,
  `barcode` varchar(50)   NOT NULL DEFAULT '',
  PRIMARY KEY (`code`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;


-- ─────────────────────────────────────────────────────────────
--  unit  –  units of measurement
-- ─────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `unit` (
  `code` bigint      NOT NULL AUTO_INCREMENT,
  `name` varchar(30) NOT NULL,
  PRIMARY KEY (`code`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

INSERT INTO `unit` (`name`) VALUES ('PCS'), ('KG'), ('CTN'), ('MTR'), ('LTR');


-- ─────────────────────────────────────────────────────────────
--  container  –  container master
-- ─────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `container` (
  `code`      bigint       NOT NULL AUTO_INCREMENT,
  `contno`    varchar(30)  NOT NULL DEFAULT '',
  `size`      varchar(10)  NOT NULL DEFAULT '',
  `supplier`  bigint       NOT NULL DEFAULT 0,
  `port`      varchar(50)  NOT NULL DEFAULT '',
  `arrdate`   date         DEFAULT NULL,
  `status`    varchar(20)  NOT NULL DEFAULT 'Pending',
  `remarks`   varchar(100) NOT NULL DEFAULT '',
  PRIMARY KEY (`code`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;


-- ─────────────────────────────────────────────────────────────
--  purmain / purtran  –  purchase header + detail
-- ─────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `purmain` (
  `vouchno`  bigint       NOT NULL AUTO_INCREMENT,
  `vouchdt`  date         NOT NULL,
  `party`    bigint       NOT NULL DEFAULT 0,
  `invno`    varchar(30)  NOT NULL DEFAULT '',
  `invdt`    date         DEFAULT NULL,
  `type`     varchar(1)   NOT NULL DEFAULT 'C',
  `totqty`   double       NOT NULL DEFAULT 0,
  `subtotal` double       NOT NULL DEFAULT 0,
  `discount` double       NOT NULL DEFAULT 0,
  `netamt`   double       NOT NULL DEFAULT 0,
  `remarks`  varchar(100) NOT NULL DEFAULT '',
  `complete` date         NOT NULL DEFAULT '0000-00-00',
  PRIMARY KEY (`vouchno`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE IF NOT EXISTS `purtran` (
  `srl`      bigint  NOT NULL AUTO_INCREMENT,
  `vouchno`  bigint  NOT NULL,
  `item`     bigint  NOT NULL DEFAULT 0,
  `qty`      double  NOT NULL DEFAULT 0,
  `unit`     varchar(20) NOT NULL DEFAULT '',
  `rate`     double  NOT NULL DEFAULT 0,
  `amount`   double  NOT NULL DEFAULT 0,
  `disc`     double  NOT NULL DEFAULT 0,
  `total`    double  NOT NULL DEFAULT 0,
  PRIMARY KEY (`srl`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;


-- ─────────────────────────────────────────────────────────────
--  salemain / saletr  –  sale header + detail
-- ─────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `salemain` (
  `vouchno`  bigint       NOT NULL AUTO_INCREMENT,
  `vouchdt`  date         NOT NULL,
  `party`    bigint       NOT NULL DEFAULT 0,
  `invno`    varchar(30)  NOT NULL DEFAULT '',
  `type`     varchar(1)   NOT NULL DEFAULT 'C',
  `totqty`   double       NOT NULL DEFAULT 0,
  `subtotal` double       NOT NULL DEFAULT 0,
  `discount` double       NOT NULL DEFAULT 0,
  `netamt`   double       NOT NULL DEFAULT 0,
  `remarks`  varchar(100) NOT NULL DEFAULT '',
  PRIMARY KEY (`vouchno`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE IF NOT EXISTS `saletr` (
  `srl`     bigint  NOT NULL AUTO_INCREMENT,
  `vouchno` bigint  NOT NULL,
  `item`    bigint  NOT NULL DEFAULT 0,
  `qty`     double  NOT NULL DEFAULT 0,
  `unit`    varchar(20) NOT NULL DEFAULT '',
  `rate`    double  NOT NULL DEFAULT 0,
  `amount`  double  NOT NULL DEFAULT 0,
  `disc`    double  NOT NULL DEFAULT 0,
  `total`   double  NOT NULL DEFAULT 0,
  PRIMARY KEY (`srl`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;


-- ─────────────────────────────────────────────────────────────
--  transacn  –  stock transaction register (all modules)
-- ─────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `transacn` (
  `srl`      bigint      NOT NULL AUTO_INCREMENT,
  `finyear`  varchar(8)  NOT NULL DEFAULT '',
  `source`   varchar(5)  NOT NULL DEFAULT '',   -- PU, SA, IW, OW, DM, EX
  `vouchno`  bigint      NOT NULL DEFAULT 0,
  `vouchdt`  date        NOT NULL,
  `item`     bigint      NOT NULL DEFAULT 0,
  `qty`      double      NOT NULL DEFAULT 0,
  `rate`     double      NOT NULL DEFAULT 0,
  `complete` date        NOT NULL DEFAULT '0000-00-00',
  PRIMARY KEY (`srl`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;


-- ─────────────────────────────────────────────────────────────
--  actran  –  accounting double-entry ledger
-- ─────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `actran` (
  `srl`     bigint      NOT NULL AUTO_INCREMENT,
  `source`  varchar(5)  NOT NULL DEFAULT '',
  `vouchno` bigint      NOT NULL DEFAULT 0,
  `vouchdt` date        NOT NULL,
  `accode`  bigint      NOT NULL DEFAULT 0,
  `dr`      double      NOT NULL DEFAULT 0,
  `cr`      double      NOT NULL DEFAULT 0,
  `narr`    varchar(100) NOT NULL DEFAULT '',
  PRIMARY KEY (`srl`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;


-- ─────────────────────────────────────────────────────────────
--  cashrecp / cashpay / bankrecp / bankpay
-- ─────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `cashrecp` (
  `vouchno` bigint       NOT NULL AUTO_INCREMENT,
  `vouchdt` date         NOT NULL,
  `accode`  bigint       NOT NULL DEFAULT 0,
  `cashac`  bigint       NOT NULL DEFAULT 0,
  `amount`  double       NOT NULL DEFAULT 0,
  `narr`    varchar(100) NOT NULL DEFAULT '',
  PRIMARY KEY (`vouchno`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE IF NOT EXISTS `cashpay` (
  `vouchno` bigint       NOT NULL AUTO_INCREMENT,
  `vouchdt` date         NOT NULL,
  `accode`  bigint       NOT NULL DEFAULT 0,
  `cashac`  bigint       NOT NULL DEFAULT 0,
  `amount`  double       NOT NULL DEFAULT 0,
  `narr`    varchar(100) NOT NULL DEFAULT '',
  PRIMARY KEY (`vouchno`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE IF NOT EXISTS `bankrecp` (
  `vouchno` bigint       NOT NULL AUTO_INCREMENT,
  `vouchdt` date         NOT NULL,
  `accode`  bigint       NOT NULL DEFAULT 0,
  `bankac`  bigint       NOT NULL DEFAULT 0,
  `chqno`   varchar(20)  NOT NULL DEFAULT '',
  `chqdt`   date         DEFAULT NULL,
  `amount`  double       NOT NULL DEFAULT 0,
  `narr`    varchar(100) NOT NULL DEFAULT '',
  PRIMARY KEY (`vouchno`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE IF NOT EXISTS `bankpay` (
  `vouchno` bigint       NOT NULL AUTO_INCREMENT,
  `vouchdt` date         NOT NULL,
  `accode`  bigint       NOT NULL DEFAULT 0,
  `bankac`  bigint       NOT NULL DEFAULT 0,
  `chqno`   varchar(20)  NOT NULL DEFAULT '',
  `chqdt`   date         DEFAULT NULL,
  `amount`  double       NOT NULL DEFAULT 0,
  `narr`    varchar(100) NOT NULL DEFAULT '',
  PRIMARY KEY (`vouchno`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;


-- ─────────────────────────────────────────────────────────────
--  contra  –  bank ↔ cash transfer
-- ─────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `contra` (
  `vouchno` bigint       NOT NULL AUTO_INCREMENT,
  `vouchdt` date         NOT NULL,
  `fromac`  bigint       NOT NULL DEFAULT 0,
  `toac`    bigint       NOT NULL DEFAULT 0,
  `amount`  double       NOT NULL DEFAULT 0,
  `narr`    varchar(100) NOT NULL DEFAULT '',
  PRIMARY KEY (`vouchno`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;


-- ─────────────────────────────────────────────────────────────
--  jv  –  journal voucher
-- ─────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `jv` (
  `vouchno` bigint       NOT NULL AUTO_INCREMENT,
  `vouchdt` date         NOT NULL,
  `narr`    varchar(100) NOT NULL DEFAULT '',
  PRIMARY KEY (`vouchno`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE IF NOT EXISTS `jvtran` (
  `srl`     bigint  NOT NULL AUTO_INCREMENT,
  `vouchno` bigint  NOT NULL,
  `accode`  bigint  NOT NULL DEFAULT 0,
  `dr`      double  NOT NULL DEFAULT 0,
  `cr`      double  NOT NULL DEFAULT 0,
  PRIMARY KEY (`srl`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;


-- ─────────────────────────────────────────────────────────────
--  inward / outward / damaged / excess
-- ─────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `inward` (
  `vouchno` bigint       NOT NULL AUTO_INCREMENT,
  `vouchdt` date         NOT NULL,
  `contno`  bigint       NOT NULL DEFAULT 0,
  `remarks` varchar(100) NOT NULL DEFAULT '',
  PRIMARY KEY (`vouchno`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE IF NOT EXISTS `inwardtr` (
  `srl`     bigint  NOT NULL AUTO_INCREMENT,
  `vouchno` bigint  NOT NULL,
  `item`    bigint  NOT NULL DEFAULT 0,
  `qty`     double  NOT NULL DEFAULT 0,
  `unit`    varchar(20) NOT NULL DEFAULT '',
  PRIMARY KEY (`srl`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE IF NOT EXISTS `outward` (
  `vouchno` bigint       NOT NULL AUTO_INCREMENT,
  `vouchdt` date         NOT NULL,
  `party`   bigint       NOT NULL DEFAULT 0,
  `remarks` varchar(100) NOT NULL DEFAULT '',
  PRIMARY KEY (`vouchno`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE IF NOT EXISTS `outwardtr` (
  `srl`     bigint  NOT NULL AUTO_INCREMENT,
  `vouchno` bigint  NOT NULL,
  `item`    bigint  NOT NULL DEFAULT 0,
  `qty`     double  NOT NULL DEFAULT 0,
  `unit`    varchar(20) NOT NULL DEFAULT '',
  PRIMARY KEY (`srl`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE IF NOT EXISTS `damaged` (
  `vouchno` bigint       NOT NULL AUTO_INCREMENT,
  `vouchdt` date         NOT NULL,
  `remarks` varchar(100) NOT NULL DEFAULT '',
  PRIMARY KEY (`vouchno`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE IF NOT EXISTS `damagedtr` (
  `srl`     bigint  NOT NULL AUTO_INCREMENT,
  `vouchno` bigint  NOT NULL,
  `item`    bigint  NOT NULL DEFAULT 0,
  `qty`     double  NOT NULL DEFAULT 0,
  `unit`    varchar(20) NOT NULL DEFAULT '',
  PRIMARY KEY (`srl`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE IF NOT EXISTS `excess` (
  `vouchno` bigint       NOT NULL AUTO_INCREMENT,
  `vouchdt` date         NOT NULL,
  `remarks` varchar(100) NOT NULL DEFAULT '',
  PRIMARY KEY (`vouchno`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE IF NOT EXISTS `excesstr` (
  `srl`     bigint  NOT NULL AUTO_INCREMENT,
  `vouchno` bigint  NOT NULL,
  `item`    bigint  NOT NULL DEFAULT 0,
  `qty`     double  NOT NULL DEFAULT 0,
  `unit`    varchar(20) NOT NULL DEFAULT '',
  PRIMARY KEY (`srl`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

-- ============================================================
--  END OF SETUP
-- ============================================================

-- ─────────────────────────────────────────────────────────────
--  If you already imported this file before the accmast/grpmast
--  expansion, run these ALTER statements manually in phpMyAdmin:
-- ─────────────────────────────────────────────────────────────
/*
USE `sim_2627`;

CREATE TABLE IF NOT EXISTS `grpmast` (
  `grpcode` varchar(10) NOT NULL,
  `name`    varchar(50) NOT NULL,
  `type`    varchar(1)  NOT NULL DEFAULT 'A',
  PRIMARY KEY (`grpcode`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

INSERT IGNORE INTO `grpmast` (`grpcode`, `name`, `type`) VALUES
('001','BANK','A'),('002','CASH','A'),('009','SUPPLIER','L'),
('010','CUSTOMER','A'),('030','SHIPPER','L'),
('040','AGENT','L'),('050','CHA','L'),('060','EXPENSE','E');

ALTER TABLE `accmast`
  ADD COLUMN IF NOT EXISTS `add2`      varchar(50)  DEFAULT NULL AFTER `add1`,
  ADD COLUMN IF NOT EXISTS `add3`      varchar(50)  DEFAULT NULL AFTER `add2`,
  ADD COLUMN IF NOT EXISTS `pin`       varchar(10)  DEFAULT NULL AFTER `city`,
  ADD COLUMN IF NOT EXISTS `state`     varchar(30)  DEFAULT NULL AFTER `pin`,
  ADD COLUMN IF NOT EXISTS `statecode` varchar(5)   DEFAULT NULL AFTER `state`,
  ADD COLUMN IF NOT EXISTS `country`   varchar(30)  DEFAULT NULL AFTER `statecode`,
  ADD COLUMN IF NOT EXISTS `tel`       varchar(25)  DEFAULT NULL AFTER `country`,
  ADD COLUMN IF NOT EXISTS `mobile2`   varchar(20)  DEFAULT NULL AFTER `mobile`,
  ADD COLUMN IF NOT EXISTS `website`   varchar(50)  DEFAULT NULL AFTER `email`,
  ADD COLUMN IF NOT EXISTS `contpers`  varchar(50)  DEFAULT NULL AFTER `website`,
  ADD COLUMN IF NOT EXISTS `gstno`     varchar(20)  DEFAULT NULL AFTER `contpers`;
*/

-- ─────────────────────────────────────────────────────────────
--  If you already imported this file before the grpmast/accmast
--  fixed-accounts expansion, run this block manually instead:
-- ─────────────────────────────────────────────────────────────
/*
USE `sim_2627`;

ALTER TABLE `grpmast`
  ADD COLUMN IF NOT EXISTS `subtype`  varchar(2) NOT NULL DEFAULT '' AFTER `type`,
  ADD COLUMN IF NOT EXISTS `add_info` varchar(1) NOT NULL DEFAULT 'N' AFTER `subtype`,
  ADD COLUMN IF NOT EXISTS `initial`  varchar(5) NOT NULL DEFAULT '' AFTER `add_info`,
  ADD COLUMN IF NOT EXISTS `headcode` smallint   NOT NULL DEFAULT 0  AFTER `initial`;

INSERT IGNORE INTO `grpmast` (`grpcode`, `name`, `type`, `subtype`, `add_info`, `initial`, `headcode`) VALUES
('001', 'BANK BALANCES',               'A', 'CA', 'Y', 'BNK', 2),
('002', 'CASH BALANCES',               'A', 'CA', 'Y', 'CSH', 2),
('004', 'PURCHASES',                   'E', 'TE', 'N', 'PUR', 15),
('005', 'SALES',                       'I', 'TI', 'N', 'SAL', 13),
('006', 'PURCHASE RETURNS',            'I', 'TI', 'N', 'PRR', 13),
('007', 'SALE RETURNS',                'E', 'TE', 'N', 'SLR', 15),
('008', 'CLOSING STOCK',               'A', 'CA', 'N', 'STK', 2),
('009', 'SUNDRY CREDITORS (SUPPLIER)', 'L', 'CL', 'Y', 'SCR', 9),
('010', 'SUNDRY DEBTORS (CUSTOMER)',   'A', 'CA', 'Y', 'SDR', 2),
('011', 'INCOME',                      'I', 'I',  'N', 'INC', 12),
('012', 'EXPENSE',                     'E', 'E',  'N', 'EXP', 14),
('013', 'PETTY CASH',                  'A', 'CA', 'Y', 'PET', 2),
('014', 'PROVISIONS',                  'L', 'CL', 'N', 'PRO', 9),
('015', 'CHEQUES IN HAND',             'A', 'CA', 'Y', 'CHQ', 2),
('016', 'TRANSPORTER',                 'L', 'CL', 'Y', 'TRP', 0),
('017', 'ACCOUNT PAYABLE',             'L', 'CL', 'N', 'ACP', 9),
('018', 'FINALISATION',                'A', 'OA', 'N', 'FIN', 6),
('019', 'OPENING STOCK',               'E', 'TE', 'N', 'OST', 15),
('020', 'DUTIES & TAXES',              'L', 'CL', 'N', 'DT',  9),
('021', 'SUNDRY CREDITOR FOR EXPENSE', 'L', 'CL', 'Y', 'SCE', 9),
('028', 'DRIVER',                      'L', 'CL', 'Y', 'DRV', 9),
('030', 'SHIPPER',                     'L', 'CL', 'Y', 'SHP', 9),
('040', 'AGENT',                       'L', 'CL', 'Y', 'AGT', 9),
('050', 'CHA',                         'L', 'CL', 'Y', 'CHA', 9),
('060', 'DIRECT EXPENSE',              'E', 'E',  'N', 'DEX', 14),
('070', 'CAPITAL ACCOUNT',             'L', 'SF', 'N', 'CAP', 8),
('053', 'LOAN TAKEN',                  'L', 'LL', 'N', 'LT',  10),
('054', 'COMMISSION',                  'E', 'E',  'Y', 'COM', 14),
('055', 'TELEPHONE',                   'E', 'E',  'Y', 'TEL', 14),
('056', 'TRIP EXPENSE',                'E', 'E',  'Y', 'TRP', 14),
('057', 'GODOWN RENT',                 'E', 'E',  'Y', 'GR',  14),
('058', 'SECURED LOAN',                'L', 'LL', 'N', 'SLN', 10),
('059', 'LOAN AND ADVANCE',            'A', 'LA', 'N', 'LAA', 4),
('061', 'BRANCH DIVISION',             'A', 'OA', 'N', 'BD',  2),
('062', 'DEPOSITS',                    'A', 'CA', 'Y', 'DE',  2),
('063', 'FIXED ASSETS',                'A', 'AI', 'Y', 'FA',  1);

-- Update existing SIM groups if they were inserted with the old simplified names
UPDATE `grpmast` SET `name`='SUNDRY CREDITORS (SUPPLIER)', `subtype`='CL', `add_info`='Y', `initial`='SCR', `headcode`=9 WHERE `grpcode`='009';
UPDATE `grpmast` SET `name`='SUNDRY DEBTORS (CUSTOMER)',   `subtype`='CA', `add_info`='Y', `initial`='SDR', `headcode`=2 WHERE `grpcode`='010';
UPDATE `grpmast` SET `name`='BANK BALANCES', `subtype`='CA', `add_info`='Y', `initial`='BNK', `headcode`=2 WHERE `grpcode`='001';
UPDATE `grpmast` SET `name`='CASH BALANCES', `subtype`='CA', `add_info`='Y', `initial`='CSH', `headcode`=2 WHERE `grpcode`='002';
UPDATE `grpmast` SET `name`='SHIPPER', `subtype`='CL', `add_info`='Y', `initial`='SHP', `headcode`=9 WHERE `grpcode`='030';
UPDATE `grpmast` SET `name`='AGENT',   `subtype`='CL', `add_info`='Y', `initial`='AGT', `headcode`=9 WHERE `grpcode`='040';
UPDATE `grpmast` SET `name`='CHA',     `subtype`='CL', `add_info`='Y', `initial`='CHA', `headcode`=9 WHERE `grpcode`='050';
UPDATE `grpmast` SET `name`='DIRECT EXPENSE', `subtype`='E', `add_info`='N', `initial`='DEX', `headcode`=14 WHERE `grpcode`='060';

-- Fixed system accounts (accode < 50)
INSERT IGNORE INTO `accmast` (`accode`, `grpcode`, `name`, `openbal`, `dc`) VALUES
(1,  '011', 'DISCOUNT TAKEN',      0, 'D'),
(2,  '012', 'EXCISE DUTY',         0, 'D'),
(3,  '002', 'CASH ON HAND',        0, 'D'),
(4,  '012', 'MISCELLANEOUS',       0, 'D'),
(5,  '020', 'VALUE ADDED TAX',     0, 'D'),
(6,  '020', 'SERVICE TAX',         0, 'D'),
(7,  '070', 'CAPITAL ACCOUNT',     0, 'D'),
(11, '004', 'CASH PURCHASE',       0, 'D'),
(12, '004', 'CREDIT PURCHASE',     0, 'D'),
(14, '005', 'CASH SALE',           0, 'C'),
(15, '005', 'CREDIT SALE',         0, 'C'),
(23, '008', 'CLOSING STOCK',       0, 'D'),
(24, '019', 'OPENING STOCK',       0, 'D'),
(28, '012', 'CREDIT TO INVESTORS', 0, 'D');

ALTER TABLE `accmast` AUTO_INCREMENT = 51;
*/
