-
Notifications
You must be signed in to change notification settings - Fork 0
Expand file tree
/
Copy pathseed.sql
More file actions
175 lines (136 loc) · 13.1 KB
/
seed.sql
File metadata and controls
175 lines (136 loc) · 13.1 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
-- seed.sql
-- Sample data for manual UI testing.
-- Assumes migrate.sql (or the Go server migration) has already run.
-- Safe to re-run: uses INSERT OR IGNORE / ON CONFLICT DO NOTHING.
PRAGMA foreign_keys=ON;
-- ─── Currencies ───────────────────────────────────────────────────────────────
INSERT INTO currency (id, code, short_text) VALUES ('VND', 'VND', 'Đồng Việt Nam') ON CONFLICT DO NOTHING;
INSERT INTO currency (id, code, short_text) VALUES ('USD', 'USD', 'Đô la Mỹ') ON CONFLICT DO NOTHING;
INSERT INTO currency (id, code, short_text) VALUES ('EUR', 'EUR', 'Euro') ON CONFLICT DO NOTHING;
-- ─── Item Groups ──────────────────────────────────────────────────────────────
INSERT INTO item_group (id, code, short_text) VALUES ('HH', 'HH', 'Hàng hóa') ON CONFLICT DO NOTHING;
INSERT INTO item_group (id, code, short_text) VALUES ('NVL', 'NVL', 'Nguyên vật liệu') ON CONFLICT DO NOTHING;
INSERT INTO item_group (id, code, short_text) VALUES ('SP', 'SP', 'Sản phẩm') ON CONFLICT DO NOTHING;
INSERT INTO item_group (id, code, short_text) VALUES ('DV', 'DV', 'Dịch vụ') ON CONFLICT DO NOTHING;
-- ─── Account Categories ───────────────────────────────────────────────────────
INSERT INTO account_category (id, code, short_text) VALUES (0, 'CN', 'Công Nợ') ON CONFLICT DO NOTHING;
INSERT INTO account_category (id, code, short_text) VALUES (1, 'DV', 'Thuế GTGT đầu vào') ON CONFLICT DO NOTHING;
INSERT INTO account_category (id, code, short_text) VALUES (2, 'DR', 'Thuế GTGT đầu ra') ON CONFLICT DO NOTHING;
INSERT INTO account_category (id, code, short_text) VALUES (3, 'NK', 'Thuế GTGT nhập khẩu') ON CONFLICT DO NOTHING;
INSERT INTO account_category (id, code, short_text) VALUES (4, 'PB', 'Chi phí chờ phân bổ') ON CONFLICT DO NOTHING;
INSERT INTO account_category (id, code, short_text) VALUES (5, 'VT', 'Vật tư hàng hóa') ON CONFLICT DO NOTHING;
INSERT INTO account_category (id, code, short_text) VALUES (6, 'SX', 'Sản xuất dở dang') ON CONFLICT DO NOTHING;
INSERT INTO account_category (id, code, short_text) VALUES (7, 'TS', 'Tài sản cố định') ON CONFLICT DO NOTHING;
INSERT INTO account_category (id, code, short_text) VALUES (8, 'HM', 'Hao mòn TSCĐ') ON CONFLICT DO NOTHING;
INSERT INTO account_category (id, code, short_text) VALUES (9, 'TV', 'Tiền vay') ON CONFLICT DO NOTHING;
INSERT INTO account_category (id, code, short_text) VALUES (10, 'DT', 'Doanh thu') ON CONFLICT DO NOTHING;
INSERT INTO account_category (id, code, short_text) VALUES (11, 'CP', 'Chi phí trực tiếp') ON CONFLICT DO NOTHING;
INSERT INTO account_category (id, code, short_text) VALUES (12, 'KT', 'Kế toán') ON CONFLICT DO NOTHING;
INSERT INTO account_category (id, code, short_text) VALUES (13, 'NB', 'Ngoài bảng') ON CONFLICT DO NOTHING;
-- ─── Tenants ──────────────────────────────────────────────────────────────────
INSERT INTO tenants (id, name, tax_code, legal_name, address, accounting_regime, vat_method, vat_quarterly, email, phone)
VALUES (1, 'Công ty TNHH Vibe Tech', '0123456789', 'CÔNG TY TNHH VIBE TECH',
'123 Nguyễn Huệ, Quận 1, TP.HCM', 'QD48', 'khau_tru', 0,
'[email protected]', '0901234567')
ON CONFLICT DO NOTHING;
INSERT INTO tenants (id, name, tax_code, legal_name, address, accounting_regime, vat_method, vat_quarterly, email, phone)
VALUES (2, 'Công ty CP Mẫu Demo', '9876543210', 'CÔNG TY CỔ PHẦN MẪU DEMO',
'456 Lê Lợi, Quận 3, TP.HCM', 'QD48', 'truc_tiep', 1,
'[email protected]', '0987654321')
ON CONFLICT DO NOTHING;
-- ─── User ↔ Tenant links ──────────────────────────────────────────────────────
-- Users (id=1 admin, id=2 testuser) are inserted by reseed.sh with dynamically
-- generated bcrypt hashes. Do not add hardcoded password hashes here.
-- admin owns both tenants
INSERT INTO user_tenants (user_id, tenant_id, is_tenant_admin) VALUES (1, 1, 1) ON CONFLICT DO NOTHING;
INSERT INTO user_tenants (user_id, tenant_id, is_tenant_admin) VALUES (1, 2, 1) ON CONFLICT DO NOTHING;
-- testuser belongs to tenant 1 only (not tenant admin)
INSERT INTO user_tenants (user_id, tenant_id, is_tenant_admin) VALUES (2, 1, 0) ON CONFLICT DO NOTHING;
-- ─── Account Codes (tenant 1) ─────────────────────────────────────────────────
INSERT INTO account_code (id, code, short_text, is_detail, group_id, is_general_ledger, structure, currency_id, is_disabled, tenant_id)
VALUES ('111', '111', 'Tiền mặt VNĐ', '1', '0', '1', 3, 'VND', '0', 1) ON CONFLICT DO NOTHING;
INSERT INTO account_code (id, code, short_text, is_detail, group_id, is_general_ledger, structure, currency_id, is_disabled, tenant_id)
VALUES ('112', '112', 'Tiền gửi ngân hàng', '1', '0', '1', 3, 'VND', '0', 1) ON CONFLICT DO NOTHING;
INSERT INTO account_code (id, code, short_text, is_detail, group_id, is_general_ledger, structure, currency_id, is_disabled, tenant_id)
VALUES ('131', '131', 'Phải thu khách hàng', '1', '0', '1', 3, 'VND', '0', 1) ON CONFLICT DO NOTHING;
INSERT INTO account_code (id, code, short_text, is_detail, group_id, is_general_ledger, structure, currency_id, is_disabled, tenant_id)
VALUES ('331', '331', 'Phải trả nhà cung cấp', '1', '0', '1', 3, 'VND', '0', 1) ON CONFLICT DO NOTHING;
INSERT INTO account_code (id, code, short_text, is_detail, group_id, is_general_ledger, structure, currency_id, is_disabled, tenant_id)
VALUES ('511', '511', 'Doanh thu bán hàng', '1', '10', '1', 3, 'VND', '0', 1) ON CONFLICT DO NOTHING;
INSERT INTO account_code (id, code, short_text, is_detail, group_id, is_general_ledger, structure, currency_id, is_disabled, tenant_id)
VALUES ('632', '632', 'Giá vốn hàng bán', '1', '11', '1', 3, 'VND', '0', 1) ON CONFLICT DO NOTHING;
INSERT INTO account_code (id, code, short_text, is_detail, group_id, is_general_ledger, structure, currency_id, is_disabled, tenant_id)
VALUES ('33311', '33311', 'Thuế GTGT phải nộp', '1', '2', '1', 5, 'VND', '0', 1) ON CONFLICT DO NOTHING;
INSERT INTO account_code (id, code, short_text, is_detail, group_id, is_general_ledger, structure, currency_id, is_disabled, tenant_id)
VALUES ('1331', '1331', 'Thuế GTGT được khấu trừ', '1', '1', '1', 4, 'VND', '0', 1) ON CONFLICT DO NOTHING;
-- Account Codes (tenant 2)
INSERT INTO account_code (id, code, short_text, is_detail, group_id, is_general_ledger, structure, currency_id, is_disabled, tenant_id)
VALUES ('111', '111', 'Tiền mặt VNĐ', '1', '0', '1', 3, 'VND', '0', 2) ON CONFLICT DO NOTHING;
INSERT INTO account_code (id, code, short_text, is_detail, group_id, is_general_ledger, structure, currency_id, is_disabled, tenant_id)
VALUES ('112', '112', 'Tiền gửi ngân hàng', '1', '0', '1', 3, 'VND', '0', 2) ON CONFLICT DO NOTHING;
INSERT INTO account_code (id, code, short_text, is_detail, group_id, is_general_ledger, structure, currency_id, is_disabled, tenant_id)
VALUES ('511', '511', 'Doanh thu bán hàng', '1', '10', '1', 3, 'VND', '0', 2) ON CONFLICT DO NOTHING;
-- ─── Document Types (tenant 1) ────────────────────────────────────────────────
INSERT INTO document_type (id, code, short_text, tenant_id, num_count, auto_increase, template, num_of_copy, auto_print, trans_account)
VALUES ('PT', 'PT', 'Phiếu Thu', 1, 0, '1', 'PT_{YYYY}_{NNN}', 2, '0', '111')
ON CONFLICT DO NOTHING;
INSERT INTO document_type (id, code, short_text, tenant_id, num_count, auto_increase, template, num_of_copy, auto_print, trans_account)
VALUES ('PC', 'PC', 'Phiếu Chi', 1, 0, '1', 'PC_{YYYY}_{NNN}', 2, '0', '111')
ON CONFLICT DO NOTHING;
INSERT INTO document_type (id, code, short_text, tenant_id, num_count, auto_increase, template, num_of_copy, auto_print, trans_account)
VALUES ('BNH', 'BNH', 'Bán hàng', 1, 0, '1', 'BNH_{YYYY}_{NNN}', 1, '1', '131')
ON CONFLICT DO NOTHING;
INSERT INTO document_type (id, code, short_text, tenant_id, num_count, auto_increase, template, num_of_copy, auto_print, trans_account)
VALUES ('MNH', 'MNH', 'Mua hàng', 1, 0, '1', 'MNH_{YYYY}_{NNN}', 1, '0', '331')
ON CONFLICT DO NOTHING;
-- Document Types (tenant 2)
INSERT INTO document_type (id, code, short_text, tenant_id, num_count, auto_increase, template, num_of_copy, auto_print, trans_account)
VALUES ('PT', 'PT', 'Phiếu Thu', 2, 0, '1', 'PT_{YYYY}_{NNN}', 2, '0', '111')
ON CONFLICT DO NOTHING;
-- ─── Partner Groups (tenants 1 & 2) ──────────────────────────────────────────
-- partner_group.id is a single-column PK (not composite), so IDs are unique across all tenants.
INSERT INTO partner_group (id, code, short_text, tenant_id, account_id) VALUES ('KH', 'KH', 'Khách hàng', 1, '131') ON CONFLICT DO NOTHING;
INSERT INTO partner_group (id, code, short_text, tenant_id, account_id) VALUES ('NCC','NCC','Nhà cung cấp', 1, '331') ON CONFLICT DO NOTHING;
INSERT INTO partner_group (id, code, short_text, tenant_id, account_id) VALUES ('NV', 'NV', 'Nhân viên', 1, '334') ON CONFLICT DO NOTHING;
INSERT INTO partner_group (id, code, short_text, tenant_id, account_id) VALUES ('NH', 'NH', 'Ngân hàng', 1, '112') ON CONFLICT DO NOTHING;
-- ─── Doctype Accounts (tenant 1) ──────────────────────────────────────────────
-- Phiếu Thu: Nợ TK 111 / Có TK 131
INSERT INTO doctype_account (doctype_id, account_id, account_side, tenant_id)
VALUES ('PT', '111', 'debit', 1);
INSERT INTO doctype_account (doctype_id, account_id, account_side, tenant_id)
VALUES ('PT', '131', 'credit', 1);
-- Phiếu Chi: Nợ TK 331 / Có TK 111
INSERT INTO doctype_account (doctype_id, account_id, account_side, tenant_id)
VALUES ('PC', '331', 'debit', 1);
INSERT INTO doctype_account (doctype_id, account_id, account_side, tenant_id)
VALUES ('PC', '111', 'credit', 1);
-- Bán hàng: Nợ TK 131 / Có TK 511 (doanh thu) + Có TK 33311 (thuế)
INSERT INTO doctype_account (doctype_id, account_id, account_side, tenant_id)
VALUES ('BNH', '131', 'debit', 1);
INSERT INTO doctype_account (doctype_id, account_id, account_side, tenant_id)
VALUES ('BNH', '511', 'credit', 1);
INSERT INTO doctype_account (doctype_id, account_id, account_side, tenant_id)
VALUES ('BNH', '33311', 'credit', 1);
-- Mua hàng: Nợ TK 632 / Nợ TK 1331 / Có TK 331
INSERT INTO doctype_account (doctype_id, account_id, account_side, tenant_id)
VALUES ('MNH', '632', 'debit', 1);
INSERT INTO doctype_account (doctype_id, account_id, account_side, tenant_id)
VALUES ('MNH', '1331', 'debit', 1);
INSERT INTO doctype_account (doctype_id, account_id, account_side, tenant_id)
VALUES ('MNH', '331', 'credit', 1);
-- ─── Measure Units (tenant 1) ─────────────────────────────────────────────────
INSERT INTO measure_unit (id, code, short_text, tenant_id) VALUES ('KG', 'KG', 'Kilogram', 1) ON CONFLICT DO NOTHING;
INSERT INTO measure_unit (id, code, short_text, tenant_id) VALUES ('G', 'G', 'Gram', 1) ON CONFLICT DO NOTHING;
INSERT INTO measure_unit (id, code, short_text, tenant_id) VALUES ('CAI', 'CAI', 'Cái', 1) ON CONFLICT DO NOTHING;
INSERT INTO measure_unit (id, code, short_text, tenant_id) VALUES ('HOP', 'HOP', 'Hộp', 1) ON CONFLICT DO NOTHING;
INSERT INTO measure_unit (id, code, short_text, tenant_id) VALUES ('THUNG','THUNG','Thùng', 1) ON CONFLICT DO NOTHING;
INSERT INTO measure_unit (id, code, short_text, tenant_id) VALUES ('L', 'L', 'Lít', 1) ON CONFLICT DO NOTHING;
-- ─── Unit Conversions (tenant 1) ──────────────────────────────────────────────
INSERT INTO unit_convert (id, from_unit_id, to_unit_id, factor, tenant_id)
VALUES ('KG-G-1', 'KG', 'G', 1000, 1) ON CONFLICT DO NOTHING;
INSERT INTO unit_convert (id, from_unit_id, to_unit_id, factor, tenant_id)
VALUES ('G-KG-1', 'G', 'KG', 0.001, 1) ON CONFLICT DO NOTHING;
INSERT INTO unit_convert (id, from_unit_id, to_unit_id, factor, tenant_id)
VALUES ('THUNG-HOP-1', 'THUNG','HOP', 12, 1) ON CONFLICT DO NOTHING;
INSERT INTO unit_convert (id, from_unit_id, to_unit_id, factor, tenant_id)
VALUES ('HOP-THUNG-1', 'HOP', 'THUNG',0.083, 1) ON CONFLICT DO NOTHING;