CREATE TABLE paymentmethods (id INT PRIMARY KEY UNIQUE, title VARCHAR, cash INT);
CREATE TABLE barcodes (product_id INT, barcode VARCHAR);
CREATE TABLE changes (id INTEGER PRIMARY KEY AUTOINCREMENT UNIQUE, rm VARCHAR DEFAULT '', time_start INT, time_end INT, cash_start DOUBLE DEFAULT (0), cash_end DOUBLE DEFAULT (0));
CREATE TABLE productcategories (id INT PRIMARY KEY UNIQUE, parent_id INT, title VARCHAR, color VARCHAR DEFAULT transparent, date_delete INT, date_update INT, img VARCHAR DEFAULT '', icon VARCHAR DEFAULT '', show INT DEFAULT(1), kiosk_noshow INT DEFAULT(0));
CREATE TABLE products (id INT PRIMARY KEY UNIQUE, code VARCHAR, ppr INT DEFAULT (1), fast INT DEFAULT (0), title VARCHAR, short_title VARCHAR, img TEXT, category_id INT, stock DOUBLE DEFAULT(0), price DOUBLE, buy_price DOUBLE DEFAULT(0), vat INT, vat_type INT, quantity DOUBLE DEFAULT (1), marking INT DEFAULT(0), marking_group VARCHAR DEFAULT '', egais INT DEFAULT(0), alcocode VARCHAR DEFAULT '', date_delete INT, date_update INT, print_group VARCHAR DEFAULT '', related TEXT DEFAULT '', max_discount DOUBLE DEFAULT (0), add_bonus INT DEFAULT(0), product_description TEXT DEFAULT '', stop INT DEFAULT(0), combo_data TEXT DEFAULT '', partner_id INT DEFAULT(0), units INT DEFAULT(0), sno VARCHAR DEFAULT '0', print_group_id INT DEFAULT(0), parent_id INT DEFAULT(0), multiple_sale INT DEFAULT(0), cocktail TEXT DEFAULT '', opt_price_1 DOUBLE DEFAULT (0), opt_price_2 DOUBLE DEFAULT (0), opt_price_3 DOUBLE DEFAULT (0), partner_type INT DEFAULT (5), other_data TEXT DEFAULT '', country_id INT DEFAULT(0), country_alpha2 VARCHAR DEFAULT '', gtd VARCHAR DEFAULT '', plu TEXT);
CREATE TABLE receipts (id INTEGER PRIMARY KEY AUTOINCREMENT UNIQUE, user_id INT, rm VARCHAR, store INT, table_id INTEGER DEFAULT (0), changes INT, changes_data TEXT, time DOUBLE, type INT, payment VARCHAR, sum DOUBLE, client VARCHAR, card VARCHAR, partner_id INT, create_receipt INT DEFAULT (0), description TEXT, lines_count INT, discount DOUBLE, bonus_pay DOUBLE DEFAULT(0), remote INT DEFAULT(0), executor INT DEFAULT(0), nofr INT DEFAULT (0), order_number VARCHAR DEFAULT '', slip TEXT DEFAULT '', bonus_add DOUBLE DEFAULT(0), synch INT DEFAULT(0), egais_sign TEXT DEFAULT '', pan TEXT DEFAULT '', transid TEXT DEFAULT '', authcode TEXT DEFAULT '', epay INT DEFAULT(0), sbp_order_id VARCHAR DEFAULT '', fd TEXT DEFAULT '', fp TEXT DEFAULT '', fn TEXT DEFAULT '', correct_type INT DEFAULT(0), osn_number VARCHAR DEFAULT '', osn_date VARCHAR DEFAULT '', kkm_time INT DEFAULT(0), sno VARCHAR DEFAULT '', other_data TEXT DEFAULT '', psr INT DEFAULT (4));
CREATE TABLE receiptlines (id INTEGER PRIMARY KEY AUTOINCREMENT UNIQUE, receipt_id INT, product_id INT, product_title VARCHAR, price DOUBLE, price0 DOUBLE, vat INT, vat_type INT, count DOUBLE, discount INT, line_discount INT, description TEXT, loyalty INT DEFAULT(0), mark TEXT DEFAULT '', kitchen INT(1) DEFAULT(0), bar INT(1) DEFAULT(0), other_info TEXT DEFAULT '', parent_id INT DEFAULT (0), turn INT DEFAULT(1), create_order DOUBLE DEFAULT(0), complite_order DOUBLE DEFAULT(0), issued_order DOUBLE DEFAULT(0), writeoff_bonus DOUBLE (0), combo_id INT DEFAULT(0), units_fr INT DEFAULT(255), barcodes VARCHAR DEFAULT '', print_group_id INT DEFAULT(0), smp INTEGER DEFAULT(0), mrp INTEGER DEFAULT(0), reqId VARCHAR DEFAULT '', reqTimestamp VARCHAR DEFAULT '0', certificate TEXT DEFAULT '', loyalty_id INT DEFAULT(0), inst VARCHAR DEFAULT '', version VARCHAR DEFAULT '');
CREATE TABLE linesset (id INTEGER PRIMARY KEY AUTOINCREMENT UNIQUE, txt TEXT, barcode VARCHAR, title VARCHAR DEFAULT '');
CREATE TABLE synchronization (id INTEGER PRIMARY KEY, time_start VARCHAR, time_update VARCHAR, tickets_update VARCHAR, users_update VARCHAR, partners_update VARCHAR, partners_timer VARCHAR, auto_sinch INT, method INT, loyalty VARCHAR, fiscalization VARCHAR, logs VARCHAR DEFAULT '0', ordersstatuses VARCHAR DEFAULT '0', clients_scales VARCHAR DEFAULT '0');
INSERT INTO synchronization (id, time_start, time_update, tickets_update, users_update, partners_update, auto_sinch, method, loyalty, fiscalization, partners_timer) VALUES (1, '0', '0', '0', '0', '0', 0, 0, '0', '0', '0');
CREATE TABLE fr_settings (id INTEGER PRIMARY KEY, printer VARCHAR, rm VARCHAR DEFAULT '', rm_name TEXT DEFAULT '', header TEXT, footer TEXT, url VARCHAR, pays_type VARCHAR, sno VARCHAR, active INT, type INT, driver VARCHAR, version VARCHAR, auto_incassation INT DEFAULT (0), line_by_line INT(1) DEFAULT(0), fix_user VARCHAR, fix_inn VARCHAR, one_line INT DEFAULT(1), free_title VARCHAR DEFAULT 'Свободная продажа', free_vat INT DEFAULT(-1), sno_types VARCHAR DEFAULT '', print_group_id INT DEFAULT(0), price_programm_z INT DEFAULT (1), psr VARCHAR DEFAULT '["4"]');
INSERT INTO fr_settings (id, printer, header, footer, url, pays_type, sno, active, type, driver, version, auto_incassation, line_by_line, fix_user, fix_inn, one_line, free_title) VALUES (1, 'kkm', '', '', 'http://127.0.0.1:8888/req', '{"1":"0","2":"1"}', '01', '0', '1', '', '1.05', 0, 0, '', '', 1, 'Свободная продажа'), (2, 'pre', '', '', 'http://127.0.0.1:8888/req', '', '', '0', '', '', '', 0, 0, '', '', 1, 'Свободная продажа'), (3, 'kitchen', 'p;#date# #time# Зак: #receipt_number##kkm_br##kkm_user#;', '', 'http://127.0.0.1:8888/req', '', '', '0', '', '', '', 0, 1, '', '', 1, 'Свободная продажа'), (4, 'bar', 'p;#date# #time# Зак: #receipt_number##kkm_br##kkm_user#;', '', 'http://127.0.0.1:8888/req', '', '', '0', '', '', '', 0, 0, '', '', 1, 'Свободная продажа'), (5, 'pricetags', '', '', 'http://127.0.0.1:8888/req', '', '', '0', '', '', '', 0, 0, '', '', 1, 'Свободная продажа');
CREATE TABLE usergroups (id INT PRIMARY KEY UNIQUE, title VARCHAR, module VARCHAR);
CREATE TABLE users (id INT PRIMARY KEY UNIQUE, name VARCHAR, short_name VARCHAR, group_id INT, pass VARCHAR, card VARCHAR, date_delete INT, hash VARCHAR, inn VARCHAR, type INT DEFAULT(1), kiosk INT DEFAULT(0));
CREATE TABLE settings (name VARCHAR UNIQUE, value TEXT);
INSERT INTO settings (name, value) VALUES ('auto_fullscreen', '1'), ('virtual_keyboard', '1'), ('cleaning', '0'), ('cafe_mode', '0'), ('auto_fiscalization', '0'), ('check_chz', '0'), ('timezone', 'Europe/Moscow'), ('check_mark', '2'), ('check_mrc', '1'), ('egais', '0'), ('check_egais', '0'), ('auto_logout', '0'), ('descriptions_pattern', ''), ('card_code_pr', '29'), ('weight_code_pr', '28'), ('worker_code_pr', '27'), ('product_menu', '0'), ('view_all_receipts', '0'), ('lines_of_list', '14'), ('modifiers_window', '1'), ('dark_theme', '1'), ('round_kop', '0'), ('executor', '0'), ('send_orders', '1'), ('orders_device', '1'), ('animation', '1'), ('url_egais', 'http://localhost:8080'),('kiosk_type', 'landing'), ('kiosk_search', '1'), ('kiosk_theme', 'modern-business');
CREATE TABLE logs (id INTEGER PRIMARY KEY AUTOINCREMENT UNIQUE, txt TEXT, read INTEGER DEFAULT (0), time DOUBLE);
CREATE TABLE partners (card VARCHAR, partner_id INT PRIMARY KEY UNIQUE, group_id INT DEFAULT (0), name VARCHAR, email VARCHAR, phone VARCHAR, discount INT, date_delete INT, loyalty_type INT DEFAULT (1), bonus DOUBLE DEFAULT (0), bonus_type INT DEFAULT (1), bonus_pay INT DEFAULT (0), bonus_summ INT DEFAULT (0), bonus_bonus INT DEFAULT (0), bonus_add_sets INT DEFAULT(1), ignore_max_discount INT DEFAULT(0), inn VARCHAR DEFAULT '', ie INT DEFAULT(0), lowername VARCHAR DEFAULT '');
CREATE TABLE cd_settings (id INTEGER PRIMARY KEY, rm VARCHAR DEFAULT '', active INT, url VARCHAR, close_txt TEXT, seller_txt TEXT, driver VARCHAR);
INSERT INTO cd_settings (id, active, url, close_txt, seller_txt) VALUES (1, '0', 'http://127.0.0.1:8889/req', 'p;       Касса;      закрыта;', 'p;     Следующий;     покупатель;');
CREATE TABLE scales_settings (name VARCHAR, value VARCHAR, rm VARCHAR DEFAULT '');
INSERT INTO scales_settings (name, value) VALUES ('active', '0'), ('driver', ''), ('url', '');
CREATE TABLE rooms (id INTEGER PRIMARY KEY AUTOINCREMENT UNIQUE, title VARCHAR UNIQUE, w INT DEFAULT (1), l INT DEFAULT (1));
CREATE TABLE tables (id INTEGER PRIMARY KEY AUTOINCREMENT UNIQUE, room INTEGER, title VARCHAR, x INT, y INT);
CREATE TABLE loyalty (id INTEGER PRIMARY KEY, date_start INT, date_end INT, title VARCHAR, d1 TEXT, d2 TEXT, d3 TEXT, d4 TEXT, d5 TEXT, d6 TEXT, d7 TEXT, client INT, data TEXT, partnergroup TEXT DEFAULT "", ignor_maxdiscount INT DEFAULT(0), action_type INT DEFAULT(1), certificates TEXT DEFAULT '');
CREATE TABLE discounts (value DOUBLE UNIQUE);
CREATE TABLE ordersstatus (id INT UNIQUE, title VARCHAR);
CREATE TABLE epays_settings (id INTEGER PRIMARY KEY, active INT DEFAULT (0), driver VARCHAR DEFAULT '', title VARCHAR DEFAULT '', driver_config TEXT DEFAULT '', selected INT DEFAULT(0));
INSERT INTO epays_settings (active, driver, title, driver_config, selected) VALUES ('1', 'drivers/epays/autonomus', 'Автономный терминал', '1234567890', '0');
CREATE TABLE taps (id INTEGER PRIMARY KEY AUTOINCREMENT UNIQUE, title VARCHAR UNIQUE, product_title VARCHAR, mark VARCHAR, volume DOUBLE, expiration INTEGER, connection_time INTEGER, connection INTEGER DEFAULT (0), current_volume DOUBLE DEFAULT(0), product_id INT DEFAULT(0));
CREATE TABLE tapsproducts (tap_id INTEGER, product_id INTEGER);
CREATE TABLE chz_cdn (host VARCHAR UNIQUE PRIMARY KEY, code INTEGER, avgTimeMs DOUBLE, time_request DOUBLE, time_update  DOUBLE);
CREATE TABLE egais_marks (mark VARCHAR UNIQUE, FormB VARCHAR, product_id INT DEFAULT(0), current_volume DOUBLE DEFAULT(0), capacity DOUBLE DEFAULT(0), chpok INT DEFAULT(0));
CREATE TABLE certificates2receipts (certificate TEXT, receipt_id  INTEGER, product_id  INTEGER);
CREATE TABLE egais_formb (product_id INTEGER, FormB TEXT, count DOUBLE, beer_capacity DOUBLE DEFAULT(0));
CREATE TABLE driver_settings (path VARCHAR, device VARCHAR, print_group_id INT, name VARCHAR, value TEXT);
CREATE TABLE device_synchronization (id INTEGER PRIMARY KEY, serial_number TEXT, type TEXT, time VARCHAR DEFAULT '0', driver_config TEXT);
CREATE TABLE clients_scales (id INTEGER PRIMARY KEY, driver TEXT, title TEXT, active INTEGER DEFAULT (0), driver_config TEXT);
CREATE INDEX clients_scales_active ON clients_scales (active);
CREATE INDEX serial_number ON device_synchronization (serial_number);
CREATE INDEX device_type ON device_synchronization (type);
CREATE INDEX device_time ON device_synchronization (time);
CREATE UNIQUE INDEX driver_id ON driver_settings (path, device, print_group_id, name);
CREATE INDEX formb_product_id ON egais_formb (product_id);
CREATE UNIQUE INDEX FormB ON egais_formb (FormB);
CREATE INDEX formb_count ON egais_formb (count);
CREATE UNIQUE INDEX c2r ON certificates2receipts (certificate, receipt_id, product_id);
CREATE INDEX 'marks_product_id' ON egais_marks (product_id);
CREATE INDEX 'marks_chpok' ON egais_marks (chpok);
CREATE INDEX taps_mark ON taps (mark);
CREATE INDEX connection ON taps (connection);
CREATE INDEX tap_id ON tapsproducts (tap_id);
CREATE INDEX tapsproduct_id ON tapsproducts (product_id);
CREATE UNIQUE INDEX tapproduct ON tapsproducts (tap_id, product_id);
CREATE INDEX selected ON epays_settings (selected);
CREATE INDEX date_start ON loyalty (date_start);
CREATE INDEX date_end ON loyalty (date_end);
CREATE INDEX d1 ON loyalty (d1);
CREATE INDEX d2 ON loyalty (d2);
CREATE INDEX d3 ON loyalty (d3);
CREATE INDEX d4 ON loyalty (d4);
CREATE INDEX d5 ON loyalty (d5);
CREATE INDEX d6 ON loyalty (d6);
CREATE INDEX d7 ON loyalty (d7);
CREATE INDEX client ON loyalty (client);
CREATE INDEX partnergroup ON loyalty (partnergroup);
CREATE INDEX data ON loyalty (data);
CREATE INDEX barcode ON barcodes (barcode);
CREATE INDEX product_id ON barcodes (product_id);
CREATE INDEX id_pay ON paymentmethods (id);
CREATE INDEX category_id ON products (category_id);
CREATE INDEX code ON products (code);
CREATE INDEX title ON products (title);
CREATE INDEX ppr ON products (ppr);
CREATE INDEX date_delete ON products (date_delete);
CREATE INDEX id_prod ON products (id);
CREATE INDEX marking ON products (marking);
CREATE INDEX egais ON products (egais);
CREATE INDEX stop ON products (stop);
CREATE INDEX date_delete_cat ON productcategories (date_delete);
CREATE INDEX parent_id ON productcategories (parent_id);
CREATE INDEX id ON productcategories (id);
CREATE INDEX kiosk_noshow ON productcategories (kiosk_noshow)
CREATE INDEX date_delete_users ON users (date_delete);
CREATE INDEX card ON users (card);
CREATE INDEX id_users ON users (id);
CREATE INDEX type ON users (type);
CREATE INDEX id_usergroups ON usergroups (id);
CREATE INDEX line_product_id ON receiptlines (product_id);
CREATE INDEX line_receipt_id ON receiptlines (receipt_id);
CREATE INDEX line_parent_id ON receiptlines (parent_id);
CREATE INDEX line_turn ON receiptlines (turn);
CREATE INDEX certificate ON receiptlines (certificate);
CREATE INDEX loyalty_id ON receiptlines (loyalty_id);
CREATE INDEX receipt_id ON receipts (id);
CREATE INDEX receipt_synch ON receipts (synch);
CREATE INDEX receipt_type ON receipts (type);
CREATE INDEX receipt_time ON receipts (time);
CREATE INDEX change_id ON receipts (changes);
CREATE INDEX remote ON receipts (remote);
CREATE INDEX nofr ON receipts (nofr);
CREATE INDEX changes_rm ON changes (rm);
CREATE UNIQUE INDEX coordinates ON tables (room, x, y);
CREATE UNIQUE INDEX table_title ON tables (room, title);
CREATE UNIQUE INDEX rm ON fr_settings (printer, rm, print_group_id);
CREATE INDEX fr_print_group_id ON fr_settings (print_group_id);
CREATE INDEX cd_rm ON cd_settings (rm);
CREATE UNIQUE INDEX scales_rm ON scales_settings (name, rm);
CREATE INDEX active ON epays_settings (active);
CREATE INDEX combo_id ON receiptlines (combo_id);
CREATE INDEX linesset_barcode ON linesset (barcode);
CREATE INDEX linesset_title ON linesset (title);
CREATE INDEX print_group_id ON receiptlines (print_group_id);
CREATE INDEX lines_receipt_id ON receiptlines (receipt_id);
CREATE INDEX kitchen ON receiptlines (kitchen);
CREATE INDEX bar ON receiptlines (bar);
CREATE INDEX products_parent_id ON products (parent_id);
CREATE INDEX partners_partner_id ON partners (partner_id);
CREATE INDEX partners_card ON partners (card);
CREATE INDEX partners_email ON partners (phone);
CREATE INDEX inn ON partners (inn);
CREATE INDEX lowername ON partners (lowername);
CREATE INDEX plu ON products (plu);
