-- Create DB script for Opus
-- Version 1
--
-- @DB must be replaced with real db name
--
--

DROP DATABASE IF EXISTS @DB;

CREATE DATABASE @DB;

USE @DB;

--
-- Table structure for table `acts`
--

CREATE TABLE acts (
  Act int(10) unsigned NOT NULL auto_increment,
  CustomerID int(10) unsigned NOT NULL default '0',
  WorkerID int(10) unsigned NOT NULL default '0',
  NumberAct int(10) unsigned NOT NULL default '0',
  DateAct date NOT NULL default '0000-00-00',
  NumberIndent varchar(25) default NULL,
  DateIndent date default NULL,
  DateReception date default NULL,
  PayType enum('Á/Í','ÂÎÇÂÐÀÒ','ÊÀÑÑÀ','ÏÐÎÈÇÂÎÄÑÒÂÎ','   ') NOT NULL default 'Á/Í',
  Store int(10) unsigned NOT NULL default '0',
  Notes varchar(255) default NULL,
  NumberDec varchar(25) default NULL,
  DateDec date default NULL,
  upsize_ts timestamp(14) NOT NULL,
  PRIMARY KEY  (Act),
  KEY CustomerID (CustomerID)
) TYPE=MyISAM;

--
-- Table structure for table `actsd`
--

CREATE TABLE actsd (
  Position int(10) unsigned NOT NULL auto_increment,
  ActID int(10) unsigned NOT NULL default '0',
  ProductID int(10) unsigned NOT NULL default '0',
  Cost double(16,2) NOT NULL default '0.00',
  Quantity double(16,4) NOT NULL default '0.0000',
  Store int(10) unsigned NOT NULL default '0',
  upsize_ts timestamp(14) NOT NULL,
  Tax double(16,3) NOT NULL default '0.18',
  PRIMARY KEY  (Position),
  KEY ActID (ActID),
  KEY ProductID (ProductID)
) TYPE=MyISAM;

--
-- Table structure for table `actsdd`
--

CREATE TABLE actsdd (
  Position int(10) unsigned NOT NULL auto_increment,
  PositionID int(10) unsigned NOT NULL default '0',
  MaterialID int(10) unsigned NOT NULL default '0',
  Cost double(16,2) NOT NULL default '0.00',
  Quantity double(16,4) NOT NULL default '0.0000',
  upsize_ts timestamp(14) NOT NULL,
  PRIMARY KEY (Position)
) TYPE=MyISAM;

--
-- Table structure for table `bills`
--

CREATE TABLE bills (
  Bill int(10) unsigned NOT NULL auto_increment,
  CustomerID int(10) unsigned NOT NULL default '0',
  WorkerID int(10) unsigned NOT NULL default '0',
  NumberBill int(10) unsigned NOT NULL default '0',
  DateBill date NOT NULL default '0000-00-00',
  NumberIndent varchar(25) default NULL,
  DateIndent date default NULL,
  DateReception date default NULL,
  PayType enum('Á/Í','ÊÀÑÑÀ','Í/Ð','   ') NOT NULL default 'Á/Í',
  Store int(10) unsigned NOT NULL default '0',
  Notes varchar(255) default NULL,
  upsize_ts timestamp(14) NOT NULL,
  PRIMARY KEY  (Bill)
) TYPE=MyISAM;

--
-- Table structure for table `billsd`
--

CREATE TABLE billsd (
  Position int(10) unsigned NOT NULL auto_increment,
  BillID int(10) unsigned NOT NULL default '0',
  MaterialID int(10) unsigned NOT NULL default '0',
  Cost double(16,2) NOT NULL default '0.00',
  Quantity double(16,4) NOT NULL default '0.0000',
  upsize_ts timestamp(14) NOT NULL,
  PRIMARY KEY  (Position)
) TYPE=MyISAM;

--
-- Table structure for table `customers`
--

CREATE TABLE customers (
  Customer int(10) unsigned NOT NULL auto_increment,
  PresentationID int(10) unsigned NOT NULL default '0',
  Name varchar(250) NOT NULL default '',
  Sity varchar(50) default NULL,
  Address varchar(50) default NULL,
  Fax varchar(25) default NULL,
  Telephon varchar(25) default NULL,
  Contact varchar(25) default NULL,
  KPP varchar(24) default NULL,
  Decount double(16,3) NOT NULL default '0.000',
  CurLimit double(16,2) NOT NULL default '0.00',
  Shop tinyint(1) NOT NULL default '0',
  Bank varchar(70) default NULL,
  INN varchar(25) default NULL,
  OKONH varchar(12) default NULL,
  OKPO varchar(12) default NULL,
  PC varchar(24) default NULL,
  KPC varchar(24) default NULL,
  BIK varchar(24) default NULL,
  Notes varchar(255) default NULL,
  upsize_ts timestamp(14) NOT NULL,
  PRIMARY KEY  (Customer)
) TYPE=MyISAM;

--
-- Table structure for table `exchange`
--

CREATE TABLE exchange (
  DateExchange date NOT NULL default '0000-00-00',
  USD double(16,2) NOT NULL default '0.00',
  DM double(16,2) NOT NULL default '0.00',
  upsize_ts timestamp(14) NOT NULL,
  PRIMARY KEY  (DateExchange)
) TYPE=MyISAM;

--
-- Table structure for table `infofirm`
--

CREATE TABLE infofirm (
  InfoID int(10) unsigned NOT NULL auto_increment,
  InfoKey varchar(50) NOT NULL default '',
  InfoVal varchar(250) default NULL,
  upsize_ts timestamp(14) NOT NULL,
  PRIMARY KEY  (InfoID)
) TYPE=MyISAM;

--
-- Table structure for table `materials`
--

CREATE TABLE materials (
  Material int(10) unsigned NOT NULL auto_increment,
  Category varchar(25) NOT NULL default '',
  Title varchar(50) NOT NULL default '',
  Model varchar(25) NOT NULL default '',
  Size varchar(25) default NULL,
  Unit varchar(10) NOT NULL default '',
  Weight double(16,3) NOT NULL default '0.000',
  Meter double(16,3) NOT NULL default '0.000',
  OKDP varchar(12) default NULL,
  Condition varchar(24) default NULL,
  Hidden tinyint(1) NOT NULL default '0',
  Notes varchar(255) default NULL,
  upsize_ts timestamp(14) NOT NULL,
  PRIMARY KEY  (Material)
) TYPE=MyISAM;

--
-- Table structure for table `outlays`
--

CREATE TABLE outlays (
  Outlay int(10) unsigned NOT NULL auto_increment,
  NumberOutlay varchar(25) NOT NULL default '',
  DateOutlay date default NULL,
  WorkerID int(10) unsigned NOT NULL default '0',
  PayType enum('ÑÏÈÑÀÍÈÅ','   ') NOT NULL default 'ÑÏÈÑÀÍÈÅ',
  Store int(10) unsigned NOT NULL default '0',
  Notes varchar(255) default NULL,
  upsize_ts timestamp(14) NOT NULL,
  PRIMARY KEY  (Outlay)
) TYPE=MyISAM;

--
-- Table structure for table `outlaysd`
--

CREATE TABLE outlaysd (
  Position int(10) unsigned NOT NULL auto_increment,
  OutlayID int(10) unsigned NOT NULL default '0',
  MaterialID int(10) unsigned NOT NULL default '0',
  Quantity double(16,4) NOT NULL default '0.0000',
  Cost double(16,2) NOT NULL default '0.00',
  upsize_ts timestamp(14) NOT NULL,
  PRIMARY KEY  (Position)
) TYPE=MyISAM;

--
-- Table structure for table `payments`
--

CREATE TABLE payments (
  Payment int(10) unsigned NOT NULL auto_increment,
  CustomerID int(10) unsigned NOT NULL default '0',
  WorkerID int(10) unsigned NOT NULL default '0',
  NumberPayment varchar(25) default NULL,
  DatePayment date default NULL,
  DateRegistration date default NULL,
  PayType enum('Á/Í','ÑÁÅÐÁÀÍÊ','ÊÀÑÑÀ','ÊÀÐÒÀ','ÑÏÈÑÀÍÈÅ','ÂÎÇÂÐÀÒ','   ') NOT NULL default 'Á/Í',
  Cost double(16,2) NOT NULL default '0.00',
  Notes varchar(255) default NULL,
  upsize_ts timestamp(14) NOT NULL,
  PRIMARY KEY  (Payment),
  KEY CustomerID (CustomerID)
) TYPE=MyISAM;

--
-- Table structure for table `pays`
--

CREATE TABLE pays (
  Pay int(10) unsigned NOT NULL auto_increment,
  CustomerID int(10) unsigned NOT NULL default '0',
  WorkerID int(10) unsigned NOT NULL default '0',
  NumberPay int(10) unsigned NOT NULL default '0',
  DatePay date NOT NULL default '0000-00-00',
  Total double(16,2) NOT NULL default '0.00',
  Purpose varchar(255) default NULL,
  Queue varchar(10) default NULL,
  Period date default NULL,
  PayType enum('Á/Í','   ') NOT NULL default 'Á/Í',
  upsize_ts timestamp(14) NOT NULL,
  PRIMARY KEY  (Pay)
) TYPE=MyISAM;

--
-- Table structure for table `presentations`
--

CREATE TABLE presentations (
  Presentation int(10) unsigned NOT NULL auto_increment,
  Event varchar(25) NOT NULL default 'Unknown',
  DateShow date NOT NULL default '1995-01-01',
  Cost double(16,2) NOT NULL default '0.00',
  Notes varchar(80) default NULL,
  upsize_ts timestamp(14) NOT NULL,
  PRIMARY KEY  (Presentation)
) TYPE=MyISAM;

--
-- Table structure for table `products`
--

CREATE TABLE products (
  Product int(10) unsigned NOT NULL auto_increment,
  Category varchar(25) NOT NULL default '',
  Serial varchar(50) NOT NULL default '',
  Title varchar(50) NOT NULL default '',
  Material varchar(25) default NULL,
  Size varchar(25) default NULL,
  Unit varchar(10) NOT NULL default '',
  CostSales double(16,2) NOT NULL default '0.00',
  Currency char(3) NOT NULL default '',
  Weight double(16,3) NOT NULL default '0.000',
  UnitPack double(16,4) NOT NULL default '0.0000',
  UnitPalette double(16,4) NOT NULL default '0.0000',
  Condition varchar(128) default NULL,
  Article int(10) unsigned default NULL,
  Hidden tinyint(1) NOT NULL default '0',
  OKDP varchar(20) default NULL,
  Notes varchar(255) default NULL,
  upsize_ts timestamp(14) NOT NULL,
  Tax double(16,3) NOT NULL default '0.18',
  PRIMARY KEY  (Product)
) TYPE=MyISAM;

--
-- Table structure for table `releases`
--

CREATE TABLE releases (
  Release int(10) unsigned NOT NULL auto_increment,
  ScoreID int(10) unsigned NOT NULL default '0',
  NumRelease int(10) unsigned NOT NULL default '0',
  DateRelease date default NULL,
  upsize_ts timestamp(14) NOT NULL,
  DateRelReg date default NULL,
  PRIMARY KEY  (Release)
) TYPE=MyISAM;

--
-- Table structure for table `releasesd`
--

CREATE TABLE releasesd (
  Position int(10) unsigned NOT NULL auto_increment,
  ReleaseID int(10) unsigned NOT NULL default '0',
  ProductID int(10) unsigned NOT NULL default '0',
  Cost double(16,2) NOT NULL default '0.00',
  Quantity double(16,4) NOT NULL default '0.0000',
  upsize_ts timestamp(14) NOT NULL,
  Tax double(16,3) NOT NULL default '0.18',
  PRIMARY KEY  (Position)
) TYPE=MyISAM;

--
-- Table structure for table `scores`
--

CREATE TABLE scores (
  Score int(10) unsigned NOT NULL auto_increment,
  CustomerID int(10) unsigned NOT NULL default '0',
  WorkerID int(10) unsigned NOT NULL default '0',
  NumScore int(10) unsigned NOT NULL default '0',
  DateExchangeID date NOT NULL default '0000-00-00',
  DateBill date default NULL,
  DateReception date default NULL,
  DateBuh date default NULL,
  PayType enum('Á/Í','ÂÎÇÂÐÀÒ','ÊÀÐÒÀ','ÊÀÑÑÀ','ÐÅÇÅÐÂ','ÐÅÀËÈÇÀÖÈß ÍÀË','ÐÅÀËÈÇÀÖÈß Á/Í','ÑÁÅÐÁÀÍÊ','   ') NOT NULL default 'Á/Í',
  Decount double(16,3) NOT NULL default '0.000',
  NSP double(16,3) NOT NULL default '0.000',
  Store int(10) unsigned NOT NULL default '0',
  Notes varchar(255) default NULL,
  upsize_ts timestamp(14) NOT NULL,
  PRIMARY KEY  (Score),
  KEY CustomerID (CustomerID)
) TYPE=MyISAM;

--
-- Table structure for table `scoresd`
--

CREATE TABLE scoresd (
  Position int(10) unsigned NOT NULL auto_increment,
  ScoreID int(10) unsigned NOT NULL default '0',
  ProductID int(10) unsigned NOT NULL default '0',
  Cost double(16,2) NOT NULL default '0.00',
  Quantity double(16,4) NOT NULL default '0.0000',
  upsize_ts timestamp(14) NOT NULL,
  Tax double(16,3) NOT NULL default '0.18',
  PRIMARY KEY  (Position),
  KEY ScoreID (ScoreID),
  KEY ProductID (ProductID)
) TYPE=MyISAM;

--
-- Table structure for table `workers`
--

CREATE TABLE workers (
  Worker int(10) unsigned NOT NULL auto_increment,
  Name1 varchar(20) NOT NULL default '',
  Name2 varchar(20) default NULL,
  Name3 varchar(20) default NULL,
  Nic varchar(20) NOT NULL default 'Guest',
  Passwd varchar(14) NOT NULL default 'opus',
  Rights int(10) unsigned NOT NULL default '0',
  DateOffice date default NULL,
  Birthday date default NULL,
  Address varchar(100) default NULL,
  Password varchar(100) default NULL,
  TelephonW varchar(25) default NULL,
  TelephonH varchar(25) default NULL,
  Seat varchar(25) default NULL,
  Salary double(16,2) NOT NULL default '0.00',
  Notes varchar(255) default NULL,
  upsize_ts timestamp(14) NOT NULL,
  PRIMARY KEY  (Worker)
) TYPE=MyISAM;

--
-- optional content for tabels  
--

INSERT INTO workers (Name1, Name2, Nic, Passwd, Rights) VALUES ('Àäìèíèñòðàòîð', 'Àäìèí', 'Admin', 'opus', 31);
INSERT INTO presentations (Event, DateShow) VALUES ('ÍÅÈÇÂÅÑÒÍÎ', '1995-01-01');
INSERT INTO infofirm (InfoKey) VALUES ('Íàçâàíèå ïðåäïðèÿòèÿ');
INSERT INTO infofirm (InfoKey) VALUES ('Àäðåñ ïðåäïðèÿòèÿ');
INSERT INTO infofirm (InfoKey) VALUES ('Òåëåôîí');
INSERT INTO infofirm (InfoKey) VALUES ('Ôàêñ');
INSERT INTO infofirm (InfoKey) VALUES ('Ýëåêòðîííàÿ ïî÷òà');
INSERT INTO infofirm (InfoKey) VALUES ('Ñàéò');
INSERT INTO infofirm (InfoKey) VALUES ('Äèðåêòîð');
INSERT INTO infofirm (InfoKey) VALUES ('Ãëàâíûé áóõãàëòåð');
INSERT INTO infofirm (InfoKey) VALUES ('Íàëîã ñ ïðîäàæ (0.5)');
INSERT INTO infofirm (InfoKey) VALUES ('Áàíê');
INSERT INTO infofirm (InfoKey) VALUES ('Îòäåëåíèå áàíêà');
INSERT INTO infofirm (InfoKey) VALUES ('ÈÍÍ');
INSERT INTO infofirm (InfoKey) VALUES ('Ð.ñ÷åò');
INSERT INTO infofirm (InfoKey) VALUES ('Ê.ñ÷åò');
INSERT INTO infofirm (InfoKey) VALUES ('ÁÈÊ');
INSERT INTO infofirm (InfoKey) VALUES ('ÊÏÏ');
INSERT INTO infofirm (InfoKey) VALUES ('ÎÊÓÄ');
INSERT INTO infofirm (InfoKey) VALUES ('Ñõåìà [0 | 1]');
INSERT INTO infofirm (InfoKey) VALUES ('Òîðãîâàÿ íàöåíêà (0.35)');
INSERT INTO infofirm (InfoKey) VALUES ('Íà÷àëüíèê ïðîèçâîäñòâà');
INSERT INTO infofirm (InfoKey) VALUES ('Çàâ.ñêëàäîì');
INSERT INTO infofirm (InfoKey) VALUES ('Îòïóñê íàêëàäíûìè');
INSERT INTO infofirm (InfoKey) VALUES ('ÎÊÏÎ');
INSERT INTO infofirm (InfoKey) VALUES ('ÎÊÄÏ');
INSERT INTO infofirm (InfoKey) VALUES ('Íå ïðîâåðÿòü îïëàòó [0 | 1]');