Nyd45md8

1BEGIN TRANSACTION;
2
3CREATE TABLE IF NOT EXISTS procedures (
4  id INTEGER PRIMARY KEY AUTOINCREMENT,
5  procedure_type TEXT COLLATE NOCASE,
6  user_id INTEGER,
7  company_id INTEGER
8);
9
10CREATE UNIQUE INDEX IF NOT EXISTS idx_procedures_id_unique ON procedures(id);
11
12CREATE TABLE IF NOT EXISTS contracts (
13          id INTEGER NOT NULL,
14          inherit_proc_id INTEGER,
15          title TEXT COLLATE NOCASE NOT NULL,
16          status_id INTEGER DEFAULT NULL,
17          company_id INTEGER NOT NULL,
18          user_id INTEGER,
19          contragent_id INTEGER,
20          products_count INTEGER,
21          total_sum NUMERIC,
22          currency TEXT,
23          start_date TIMESTAMP, -- дата создания договора
24          close_date TIMESTAMP, -- дата закрытия договора
25          created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
26          updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
27          deleted_at TIMESTAMP DEFAULT NULL,
28          FOREIGN KEY(id) REFERENCES procedures(id),
29          FOREIGN KEY(status_id) REFERENCES tender_status(id)
30        );
31		
32CREATE INDEX IF NOT EXISTS idx_contracts_company_id ON contracts(company_id);
33
34CREATE TABLE IF NOT EXISTS orders (
35          id INTEGER NOT NULL,
36          inherit_proc_id INTEGER,
37          title TEXT COLLATE NOCASE NOT NULL,
38          status_id INTEGER DEFAULT NULL,
39          company_id INTEGER NOT NULL,
40          user_id INTEGER,
41          contragent_id INTEGER,
42          products_count INTEGER,
43          start_date TIMESTAMP, -- дата поставки
44          price NUMERIC,
45          created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
46          updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
47          deleted_at TIMESTAMP DEFAULT NULL,
48          FOREIGN KEY(id) REFERENCES procedures(id),
49          FOREIGN KEY(status_id) REFERENCES tender_status(id)
50        );
51		
52CREATE INDEX IF NOT EXISTS idx_orders_company_id ON orders(company_id);
53
54PRAGMA foreign_keys=off;
55
56ALTER TABLE tenders RENAME TO _tenders_old;
57
58CREATE TABLE tenders(
59          title TEXT COLLATE NOCASE NOT NULL,
60          id INTEGER NOT NULL,
61          company_id INTEGER NOT NULL,
62          company_deleted INTEGER DEFAULT 0,
63          user_id INTEGER,
64          area_id TEXT(100),
65          anno TEXT,
66          pub_date TIMESTAMP, -- дата публикации
67          start_date TIMESTAMP, -- дата начала торгов
68          trade_date TIMESTAMP, -- дата начала переторжки
69          agreement_date TIMESTAMP, -- дата согласования
70          end_date TIMESTAMP, -- дата завершения торгов. ? Выставляется по факту закрытия тендера. Если тендер открыт, то это дата NULL ?
71          ship_date TIMESTAMP, -- дата поставки
72          close_date TIMESTAMP, -- дата закрытия процедуры
73          possible_close_date TIMESTAMP, -- дата согласования тендера ? Когда создается тендер эта дата указывается в качестве ПЛАНИРУЕМОЙ даты окончания тендера. Она может быть != фактической даты закрытия тендера - end_date ?
74          status_id INTEGER DEFAULT NULL,
75          currency TEXT,
76          type_id INTEGER,
77          trade_step INTEGER,
78          is_price_step_percent BOOLEAN,
79          price_step DECIMAL(5,2),
80          is_auction BOOLEAN,
81          is_sell BOOLEAN,
82          is_test_company BOOLEAN DEFAULT false,
83          is_blocked_company BOOLEAN DEFAULT false,
84          is_hidden BOOLEAN DEFAULT false,
85          show_participants_count BOOLEAN DEFAULT true,
86          participants_count INTEGER DEFAULT 0,
87          created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
88          updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
89          deleted_at TIMESTAMP DEFAULT NULL,
90          incoterms TEXT,
91          tare_type INTEGER,
92          delivery_address TEXT,
93          incomp BOOLEAN,
94          multi_winner BOOLEAN,
95          win_desc TEXT,
96          winner_id INTEGER,
97          ext_id INTEGER,
98          source TEXT,
99          ext_updated_at TIMESTAMP DEFAULT NULL,
100          ext_deleted_at TIMESTAMP DEFAULT NULL,
101          FOREIGN KEY(id) REFERENCES procedures(id),
102          FOREIGN KEY(status_id) REFERENCES tender_status(id)
103        );
104
105INSERT INTO tenders(id, title, company_id, company_deleted, area_id, anno, pub_date, start_date, trade_date, agreement_date, end_date, ship_date, close_date, possible_close_date, status_id, currency, type_id, trade_step, is_price_step_percent, price_step, is_auction, is_sell, is_test_company, is_blocked_company, participants_count, created_at, updated_at, deleted_at, ext_id, source, ext_updated_at, ext_deleted_at, incoterms, tare_type, delivery_address, incomp, multi_winner, user_id, is_hidden, show_participants_count, winner_id, win_desc)
106SELECT id, title, company_id, company_deleted, area_id, anno, pub_date, start_date, trade_date, agreement_date, end_date, ship_date, close_date, possible_close_date, status_id, currency, type_id, trade_step, is_price_step_percent, price_step, is_auction, is_sell, is_test_company, is_blocked_company, participants_count, created_at, updated_at, deleted_at, ext_id, source, ext_updated_at, ext_deleted_at, incoterms, tare_type, delivery_address, incomp, multi_winner, user_id, is_hidden, show_participants_count, winner_id, win_desc
107FROM _tenders_old;
108
109drop table _tenders_old;
110COMMIT;
111
112PRAGMA foreign_keys=on;
113
114CREATE INDEX IF NOT EXISTS idx_covering_offers_stat ON offers(tender_product_id, company_id)
115COMMIT;