Repository navigation
Expand file tree
/
Copy pathinit-database.sql
More file actions
1740 lines (1643 loc) · 70.8 KB
/
Copy pathinit-database.sql
File metadata and controls
1740 lines (1643 loc) · 70.8 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
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
852
853
854
855
856
857
858
859
860
861
862
863
864
865
866
867
868
869
870
871
872
873
874
875
876
877
878
879
880
881
882
883
884
885
886
887
888
889
890
891
892
893
894
895
896
897
898
899
900
901
902
903
904
905
906
907
908
909
910
911
912
913
914
915
916
917
918
919
920
921
922
923
924
925
926
927
928
929
930
931
932
933
934
935
936
937
938
939
940
941
942
943
944
945
946
947
948
949
950
951
952
953
954
955
956
957
958
959
960
961
962
963
964
965
966
967
968
969
970
971
972
973
974
975
976
977
978
979
980
981
982
983
984
985
986
987
988
989
990
991
992
993
994
995
996
997
998
999
1000
-- ============================================================
-- RETAILOPS V2.1 - COMPLETE DATABASE INITIALIZATION
-- Run ONCE on fresh SQL Server (idempotent - safe to re-run)
-- ============================================================
USE master;
GO
-- 1. Create Database
IF NOT EXISTS (SELECT * FROM sys.databases WHERE name = 'RetailOps')
BEGIN
CREATE DATABASE RetailOps
COLLATE SQL_Latin1_General_CP1_CI_AS;
PRINT 'Created database: RetailOps';
END
ELSE
BEGIN
PRINT 'Database RetailOps already exists';
END
GO
USE RetailOps;
GO
-- 2. Helper Function: GetEnvDate (timezone-aware)
IF OBJECT_ID(N'dbo.GetEnvDate', N'FN') IS NULL
BEGIN
EXEC('CREATE FUNCTION dbo.GetEnvDate() RETURNS DATETIME2 AS BEGIN RETURN GETUTCDATE() END');
PRINT 'Created stub GetEnvDate';
END
-- Only apply the timezone offset on a genuinely fresh server. When the function
-- already exists (e.g. it was created by the Node API's own connection init and
-- is referenced by default constraints), ALTER FUNCTION would be blocked, so we
-- keep the existing definition.
IF OBJECT_ID(N'dbo.GetEnvDate', N'FN') IS NULL
BEGIN
-- Update with actual offset (IST = +330 minutes)
DECLARE @offsetMins INT = 330;
DECLARE @getEnvDateSql NVARCHAR(MAX) = N'
ALTER FUNCTION dbo.GetEnvDate()
RETURNS DATETIME2 AS BEGIN
RETURN DATEADD(minute, ' + CAST(@offsetMins AS NVARCHAR(10)) + N', GETUTCDATE())
END
';
EXEC(@getEnvDateSql);
PRINT 'Updated GetEnvDate with IST offset';
END
GO
-- 3. SchemaMigrations table (for migration tracking)
IF OBJECT_ID(N'dbo.SchemaMigrations', N'U') IS NULL
BEGIN
CREATE TABLE SchemaMigrations (
Id VARCHAR(50) PRIMARY KEY,
Name NVARCHAR(200) NOT NULL,
AppliedAt DATETIME2 NOT NULL DEFAULT dbo.GetEnvDate()
);
CREATE UNIQUE INDEX UX_SchemaMigrations_Name ON SchemaMigrations(Name);
PRINT 'Created SchemaMigrations';
END
GO
-- ============================================================
-- CORE TABLES: USERS, ROLES, PERMISSIONS
-- ============================================================
-- Roles
IF OBJECT_ID(N'dbo.Roles', N'U') IS NULL
BEGIN
CREATE TABLE Roles (
Id VARCHAR(50) PRIMARY KEY,
Name NVARCHAR(100) NOT NULL UNIQUE,
DisplayName NVARCHAR(200) NOT NULL,
Description NVARCHAR(MAX),
Level INT NOT NULL DEFAULT 0,
Color NVARCHAR(20) DEFAULT '#4F46E5',
IsSystem BIT NOT NULL DEFAULT 0,
IsActive BIT NOT NULL DEFAULT 1,
CreatedAt DATETIME2 NOT NULL DEFAULT dbo.GetEnvDate(),
UpdatedAt DATETIME2 NOT NULL DEFAULT dbo.GetEnvDate()
);
PRINT 'Created Roles';
END
GO
-- Permissions
IF OBJECT_ID(N'dbo.Permissions', N'U') IS NULL
BEGIN
CREATE TABLE Permissions (
Id VARCHAR(50) PRIMARY KEY,
Name NVARCHAR(100) NOT NULL UNIQUE,
DisplayName NVARCHAR(200) NOT NULL,
Description NVARCHAR(MAX),
Category NVARCHAR(50) NOT NULL,
Action NVARCHAR(50) NOT NULL,
CreatedAt DATETIME2 NOT NULL DEFAULT dbo.GetEnvDate()
);
PRINT 'Created Permissions';
END
GO
-- RolePermissions
IF OBJECT_ID(N'dbo.RolePermissions', N'U') IS NULL
BEGIN
CREATE TABLE RolePermissions (
RoleId VARCHAR(50) NOT NULL,
PermissionId VARCHAR(50) NOT NULL,
PRIMARY KEY (RoleId, PermissionId),
CONSTRAINT FK_RolePermissions_Role FOREIGN KEY (RoleId) REFERENCES Roles(Id) ON DELETE CASCADE,
CONSTRAINT FK_RolePermissions_Permission FOREIGN KEY (PermissionId) REFERENCES Permissions(Id) ON DELETE CASCADE
);
PRINT 'Created RolePermissions';
END
GO
-- Users
IF OBJECT_ID(N'dbo.Users', N'U') IS NULL
BEGIN
CREATE TABLE Users (
Id VARCHAR(50) PRIMARY KEY,
Email NVARCHAR(255) NOT NULL UNIQUE,
PasswordHash NVARCHAR(500) NULL,
Password NVARCHAR(500) NULL,
FirstName NVARCHAR(100) NOT NULL,
LastName NVARCHAR(100) NOT NULL,
Avatar NVARCHAR(500),
Phone NVARCHAR(50),
RoleId VARCHAR(50),
IsActive BIT NOT NULL DEFAULT 1,
IsOnline BIT NOT NULL DEFAULT 0,
LastSeen DATETIME2,
LastLoginAt DATETIME2,
LoginAttempts INT DEFAULT 0,
LockUntil DATETIME2,
FailedLoginAttempts INT DEFAULT 0,
LockedUntil DATETIME2,
ForcePasswordReset BIT DEFAULT 0,
PasswordChangedAt DATETIME2 DEFAULT dbo.GetEnvDate(),
PasswordExpiresAt DATETIME2,
MustChangePassword BIT DEFAULT 0,
IsFirstLogin BIT NOT NULL DEFAULT 1,
FirstLoginAt DATETIME2,
SetupCompletedAt DATETIME2,
SecurityPolicyAccepted BIT NOT NULL DEFAULT 0,
CurrentTeam VARCHAR(50) NULL,
IsEmailVerified BIT NOT NULL DEFAULT 0,
Preferences NVARCHAR(MAX) NULL,
OtpSentCountToday INT NOT NULL DEFAULT 0,
OtpResetDate DATE NULL,
LastOtpSentAt DATETIME2 NULL,
CometChatUid NVARCHAR(100) NULL,
ExtraPermissions NVARCHAR(MAX) NULL,
ExcludedPermissions NVARCHAR(MAX) NULL,
TwoFactorEnabled BIT DEFAULT 0,
TwoFactorSecret NVARCHAR(100),
TrustedDevices NVARCHAR(MAX),
RefreshToken NVARCHAR(500),
RefreshTokenExpiry DATETIME2,
CreatedAt DATETIME2 NOT NULL DEFAULT dbo.GetEnvDate(),
UpdatedAt DATETIME2 NOT NULL DEFAULT dbo.GetEnvDate(),
CONSTRAINT FK_Users_Role FOREIGN KEY (RoleId) REFERENCES Roles(Id)
);
CREATE INDEX IX_Users_Email ON Users(Email);
CREATE INDEX IX_Users_RoleId ON Users(RoleId);
CREATE INDEX IX_Users_IsActive ON Users(IsActive);
PRINT 'Created Users';
END
GO
-- UserSellers (access control)
IF OBJECT_ID(N'dbo.UserSellers', N'U') IS NULL
BEGIN
CREATE TABLE UserSellers (
UserId VARCHAR(50) NOT NULL,
SellerId VARCHAR(50) NOT NULL,
PRIMARY KEY (UserId, SellerId),
CONSTRAINT FK_UserSellers_User FOREIGN KEY (UserId) REFERENCES Users(Id) ON DELETE CASCADE
);
PRINT 'Created UserSellers';
END
GO
-- PasswordHistory
IF OBJECT_ID(N'dbo.PasswordHistory', N'U') IS NULL
BEGIN
CREATE TABLE PasswordHistory (
Id VARCHAR(50) PRIMARY KEY,
UserId VARCHAR(50) NOT NULL,
PasswordHash NVARCHAR(500) NOT NULL,
CreatedAt DATETIME2 NOT NULL DEFAULT dbo.GetEnvDate(),
CONSTRAINT FK_PasswordHistory_User FOREIGN KEY (UserId) REFERENCES Users(Id) ON DELETE CASCADE
);
CREATE INDEX IX_PasswordHistory_UserId ON PasswordHistory(UserId);
PRINT 'Created PasswordHistory';
END
GO
-- OtpVerifications
IF OBJECT_ID(N'dbo.OtpVerifications', N'U') IS NULL
BEGIN
CREATE TABLE OtpVerifications (
Id INT IDENTITY(1,1) PRIMARY KEY,
UserId VARCHAR(50) NOT NULL,
Email NVARCHAR(255) NOT NULL,
OtpHash NVARCHAR(500) NOT NULL,
Purpose NVARCHAR(20) NOT NULL DEFAULT 'LOGIN',
IpAddress NVARCHAR(50) NULL,
UserAgent NVARCHAR(500) NULL,
Attempts INT NOT NULL DEFAULT 0,
MaxAttempts INT NOT NULL DEFAULT 3,
IsUsed BIT NOT NULL DEFAULT 0,
CreatedAt DATETIME2 NOT NULL DEFAULT dbo.GetEnvDate(),
ExpiresAt DATETIME2 NOT NULL,
UsedAt DATETIME2 NULL,
CONSTRAINT FK_OtpVerifications_User FOREIGN KEY (UserId) REFERENCES Users(Id) ON DELETE CASCADE
);
CREATE INDEX IX_OtpVerifications_UserId ON OtpVerifications(UserId);
CREATE INDEX IX_OtpVerifications_ExpiresAt ON OtpVerifications(ExpiresAt);
CREATE INDEX IX_OtpVerifications_Valid ON OtpVerifications(UserId, Purpose, IsUsed, ExpiresAt);
PRINT 'Created OtpVerifications';
END
GO
-- OtpAuditLog
IF OBJECT_ID(N'dbo.OtpAuditLog', N'U') IS NULL
BEGIN
CREATE TABLE OtpAuditLog (
Id INT IDENTITY(1,1) PRIMARY KEY,
UserId VARCHAR(50) NULL,
Email NVARCHAR(255) NULL,
Action NVARCHAR(50) NOT NULL,
Status NVARCHAR(20) NOT NULL,
Reason NVARCHAR(500) NULL,
IpAddress NVARCHAR(50) NULL,
UserAgent NVARCHAR(500) NULL,
CreatedAt DATETIME2 NOT NULL DEFAULT dbo.GetEnvDate()
);
CREATE INDEX IX_OtpAuditLog_UserId ON OtpAuditLog(UserId);
CREATE INDEX IX_OtpAuditLog_CreatedAt ON OtpAuditLog(CreatedAt);
PRINT 'Created OtpAuditLog';
END
GO
-- TokenBlacklist
IF OBJECT_ID(N'dbo.TokenBlacklist', N'U') IS NULL
BEGIN
CREATE TABLE TokenBlacklist (
Id VARCHAR(50) PRIMARY KEY,
TokenHash NVARCHAR(500) NOT NULL,
UserId VARCHAR(50) NOT NULL,
Type NVARCHAR(20) NOT NULL,
ExpiresAt DATETIME2 NOT NULL,
CreatedAt DATETIME2 NOT NULL DEFAULT dbo.GetEnvDate(),
CONSTRAINT FK_TokenBlacklist_User FOREIGN KEY (UserId) REFERENCES Users(Id) ON DELETE CASCADE
);
CREATE INDEX IX_TokenBlacklist_UserId ON TokenBlacklist(UserId);
CREATE INDEX IX_TokenBlacklist_ExpiresAt ON TokenBlacklist(ExpiresAt);
PRINT 'Created TokenBlacklist';
END
GO
-- TrustedDevices
IF OBJECT_ID(N'dbo.TrustedDevices', N'U') IS NULL
BEGIN
CREATE TABLE TrustedDevices (
Id NVARCHAR(100) PRIMARY KEY,
UserId VARCHAR(50) NOT NULL,
DeviceId NVARCHAR(400) NOT NULL,
DeviceFingerprint NVARCHAR(128) NOT NULL,
DeviceName NVARCHAR(400),
UserAgent NVARCHAR(1000),
IpAddress NVARCHAR(100),
LastUsedAt DATETIME2 NULL,
ExpiresAt DATETIME2 NOT NULL,
CreatedAt DATETIME2 NOT NULL DEFAULT dbo.GetEnvDate(),
IsRevoked BIT NOT NULL DEFAULT 0,
CONSTRAINT FK_TrustedDevices_User FOREIGN KEY (UserId) REFERENCES Users(Id) ON DELETE CASCADE
);
CREATE INDEX IX_TrustedDevices_UserId ON TrustedDevices(UserId);
CREATE INDEX IX_TrustedDevices_Fingerprint ON TrustedDevices(DeviceFingerprint);
CREATE INDEX IX_TrustedDevices_DeviceId ON TrustedDevices(DeviceId);
PRINT 'Created TrustedDevices';
END
GO
-- SystemLogs
IF OBJECT_ID(N'dbo.SystemLogs', N'U') IS NULL
BEGIN
CREATE TABLE SystemLogs (
Id VARCHAR(50) PRIMARY KEY,
Type NVARCHAR(50) NOT NULL,
EntityType NVARCHAR(50),
EntityId VARCHAR(50),
EntityTitle NVARCHAR(500),
UserId VARCHAR(50),
Description NVARCHAR(MAX),
Metadata NVARCHAR(MAX),
IpAddress NVARCHAR(50),
UserAgent NVARCHAR(500),
Severity NVARCHAR(20) DEFAULT 'INFO',
CreatedAt DATETIME2 NOT NULL DEFAULT dbo.GetEnvDate()
);
CREATE INDEX IX_SystemLogs_Type_CreatedAt ON SystemLogs(Type, CreatedAt DESC);
CREATE INDEX IX_SystemLogs_Entity ON SystemLogs(EntityType, EntityId);
CREATE INDEX IX_SystemLogs_UserId ON SystemLogs(UserId);
PRINT 'Created SystemLogs';
END
GO
-- ============================================================
-- SELLERS & MARKETPLACES
-- ============================================================
IF OBJECT_ID(N'dbo.Sellers', N'U') IS NULL
BEGIN
CREATE TABLE Sellers (
Id VARCHAR(50) PRIMARY KEY,
SellerId NVARCHAR(100) NOT NULL UNIQUE,
Name NVARCHAR(255) NOT NULL,
Email NVARCHAR(255),
Marketplace NVARCHAR(50) NOT NULL DEFAULT 'amazon.in',
OctoparseId NVARCHAR(100),
OctoparseTaskId NVARCHAR(100),
OctoparseTaskName NVARCHAR(255),
IsActive BIT NOT NULL DEFAULT 1,
IsPriority BIT NULL,
ScrapeLimit INT NULL,
LiveSyncClientId NVARCHAR(500) NULL,
LiveSyncClientSecret NVARCHAR(500) NULL,
PartnerTag NVARCHAR(200) NULL,
LiveSyncEnabled BIT NULL,
[Plan] NVARCHAR(500) NULL,
LastScrapedAt DATETIME2,
LastSyncAt DATETIME2,
SyncStatus NVARCHAR(50) DEFAULT 'PENDING',
Credentials NVARCHAR(MAX),
Settings NVARCHAR(MAX),
CreatedAt DATETIME2 NOT NULL DEFAULT dbo.GetEnvDate(),
UpdatedAt DATETIME2 NOT NULL DEFAULT dbo.GetEnvDate()
);
CREATE INDEX IX_Sellers_Marketplace_Active ON Sellers(Marketplace, IsActive);
CREATE INDEX IX_Sellers_OctoparseId ON Sellers(OctoparseId);
PRINT 'Created Sellers';
END
GO
-- ============================================================
-- ASINS & PRODUCT DATA
-- ============================================================
IF OBJECT_ID(N'dbo.Asins', N'U') IS NULL
BEGIN
CREATE TABLE Asins (
Id VARCHAR(50) PRIMARY KEY,
AsinCode NVARCHAR(30) NOT NULL,
SellerId VARCHAR(50) NOT NULL,
Sku NVARCHAR(100),
Title NVARCHAR(MAX),
Brand NVARCHAR(255),
Category NVARCHAR(255),
SubCategory NVARCHAR(255),
Price DECIMAL(18,2) DEFAULT 0,
Mrp DECIMAL(18,2) DEFAULT 0,
CurrentPrice DECIMAL(18,2) DEFAULT 0,
Bsr INT,
SubBsr INT,
SubBSRCategory NVARCHAR(255),
LqsScore DECIMAL(5,2) DEFAULT 0,
LQSGrade NVARCHAR(10) DEFAULT 'F',
LQSDetails NVARCHAR(MAX),
TitleScore DECIMAL(5,2) DEFAULT 0,
BulletScore DECIMAL(5,2) DEFAULT 0,
ImageScore DECIMAL(5,2) DEFAULT 0,
DescriptionScore DECIMAL(5,2) DEFAULT 0,
TitleIssues NVARCHAR(MAX),
BulletIssues NVARCHAR(MAX),
ImageIssues NVARCHAR(MAX),
DescriptionIssues NVARCHAR(MAX),
Rating DECIMAL(3,2) DEFAULT 0,
ReviewCount INT DEFAULT 0,
HasAplus BIT DEFAULT 0,
AplusDetails NVARCHAR(MAX),
ImagesCount INT DEFAULT 0,
MainImageUrl NVARCHAR(1000),
StockLevel INT,
SoldBy NVARCHAR(255),
Status NVARCHAR(30) DEFAULT 'ACTIVE',
LastScrapedAt DATETIME2,
CreatedAt DATETIME2 NOT NULL DEFAULT dbo.GetEnvDate(),
UpdatedAt DATETIME2 NOT NULL DEFAULT dbo.GetEnvDate(),
CONSTRAINT FK_Asins_Seller FOREIGN KEY (SellerId) REFERENCES Sellers(Id) ON DELETE CASCADE
);
CREATE UNIQUE INDEX UX_Asins_AsinCode_Seller ON Asins(AsinCode, SellerId);
CREATE INDEX IX_Asins_Seller_Status ON Asins(SellerId, Status);
CREATE INDEX IX_Asins_LastScrapedAt ON Asins(LastScrapedAt);
PRINT 'Created Asins';
END
GO
-- AsinHistory
IF OBJECT_ID(N'dbo.AsinHistory', N'U') IS NULL
BEGIN
CREATE TABLE AsinHistory (
Id VARCHAR(50) PRIMARY KEY,
AsinId VARCHAR(50) NOT NULL,
AsinCode NVARCHAR(30) NOT NULL,
SellerId VARCHAR(50) NOT NULL,
Date DATETIME2 NOT NULL,
Price DECIMAL(18,2),
BSR INT,
Rating DECIMAL(3,2),
ReviewCount INT,
StockLevel INT,
LQS DECIMAL(5,2),
CreatedAt DATETIME2 NOT NULL DEFAULT dbo.GetEnvDate(),
CONSTRAINT FK_AsinHistory_Asin FOREIGN KEY (AsinId) REFERENCES Asins(Id) ON DELETE CASCADE
);
CREATE INDEX IX_AsinHistory_AsinId_Date ON AsinHistory(AsinId, Date DESC);
CREATE INDEX IX_AsinHistory_Seller_Date ON AsinHistory(SellerId, Date DESC);
PRINT 'Created AsinHistory';
END
GO
-- AsinWeekHistory
IF OBJECT_ID(N'dbo.AsinWeekHistory', N'U') IS NULL
BEGIN
CREATE TABLE AsinWeekHistory (
Id VARCHAR(50) PRIMARY KEY,
AsinId VARCHAR(50) NOT NULL,
AsinCode NVARCHAR(30) NOT NULL,
SellerId VARCHAR(50) NOT NULL,
WeekStartDate DATETIME2 NOT NULL,
AvgPrice DECIMAL(18,2),
AvgBSR INT,
AvgRating DECIMAL(3,2),
TotalReviews INT,
AvgLQS DECIMAL(5,2),
CreatedAt DATETIME2 NOT NULL DEFAULT dbo.GetEnvDate(),
CONSTRAINT FK_AsinWeekHistory_Asin FOREIGN KEY (AsinId) REFERENCES Asins(Id) ON DELETE CASCADE
);
CREATE INDEX IX_AsinWeekHistory_AsinId_Week ON AsinWeekHistory(AsinId, WeekStartDate DESC);
PRINT 'Created AsinWeekHistory';
END
GO
-- AsinScrapeData
IF OBJECT_ID(N'dbo.AsinScrapeData', N'U') IS NULL
BEGIN
CREATE TABLE AsinScrapeData (
Id VARCHAR(50) PRIMARY KEY,
Asin NVARCHAR(20) NOT NULL,
SellerId VARCHAR(50) NOT NULL,
Rating DECIMAL(3, 2) DEFAULT 0,
ReviewCount INT DEFAULT 0,
HasAplus BIT DEFAULT 0,
AplusDetails NVARCHAR(MAX) NULL,
Title NVARCHAR(MAX) NULL,
Price DECIMAL(18, 2) DEFAULT 0,
BSR INT NULL,
RatingBreakdown NVARCHAR(MAX) NULL,
LastScrapedAt DATETIME2 NULL,
ScrapeDurationMs INT DEFAULT 0,
CreatedAt DATETIME2 NOT NULL DEFAULT dbo.GetEnvDate(),
UpdatedAt DATETIME2 NOT NULL DEFAULT dbo.GetEnvDate()
);
CREATE UNIQUE INDEX UX_AsinScrapeData_Asin_Seller ON AsinScrapeData(Asin, SellerId);
CREATE INDEX IX_AsinScrapeData_SellerId ON AsinScrapeData(SellerId);
CREATE INDEX IX_AsinScrapeData_LastScrapedAt ON AsinScrapeData(LastScrapedAt);
PRINT 'Created AsinScrapeData';
END
GO
-- AsinTable
IF OBJECT_ID(N'dbo.AsinTable', N'U') IS NULL
BEGIN
CREATE TABLE AsinTable (
Id VARCHAR(50) PRIMARY KEY,
AsinCode NVARCHAR(30) NOT NULL,
SellerId VARCHAR(50) NOT NULL,
Title NVARCHAR(MAX),
Category NVARCHAR(255),
SubCategory NVARCHAR(255),
Brand NVARCHAR(255),
Price DECIMAL(18,2),
BSR INT,
LQS DECIMAL(5,2),
Status NVARCHAR(30),
ImagesCount INT DEFAULT 0,
CreatedAt DATETIME2 NOT NULL DEFAULT dbo.GetEnvDate(),
UpdatedAt DATETIME2 NOT NULL DEFAULT dbo.GetEnvDate()
);
CREATE INDEX IX_AsinTable_Category_Seller ON AsinTable(Category, SellerId);
PRINT 'Created AsinTable';
END
GO
-- ============================================================
-- ADVERTISING & PERFORMANCE
-- ============================================================
IF OBJECT_ID(N'dbo.AdsPerformance', N'U') IS NULL
BEGIN
CREATE TABLE AdsPerformance (
Id VARCHAR(50) PRIMARY KEY,
Asin NVARCHAR(30) NOT NULL,
SellerId VARCHAR(50) NOT NULL,
Date DATETIME2 NOT NULL,
ReportType NVARCHAR(50) DEFAULT 'SP',
AdSales DECIMAL(18,2) DEFAULT 0,
AdSpend DECIMAL(18,2) DEFAULT 0,
Orders INT DEFAULT 0,
OrganicSales DECIMAL(18,2) DEFAULT 0,
Impressions BIGINT DEFAULT 0,
Clicks BIGINT DEFAULT 0,
Ctr DECIMAL(5,2) DEFAULT 0,
Cpc DECIMAL(18,2) DEFAULT 0,
Roas DECIMAL(18,2) DEFAULT 0,
Acos DECIMAL(5,2) DEFAULT 0,
CreatedAt DATETIME2 NOT NULL DEFAULT dbo.GetEnvDate(),
CONSTRAINT FK_AdsPerformance_Seller FOREIGN KEY (SellerId) REFERENCES Sellers(Id) ON DELETE CASCADE
);
CREATE INDEX IX_AdsPerformance_Asin_Date ON AdsPerformance(Asin, Date DESC);
CREATE INDEX IX_AdsPerformance_Seller_Date ON AdsPerformance(SellerId, Date DESC);
PRINT 'Created AdsPerformance';
END
GO
IF OBJECT_ID(N'dbo.GmsDailyPerformance', N'U') IS NULL
BEGIN
CREATE TABLE GmsDailyPerformance (
Id VARCHAR(50) PRIMARY KEY,
Asin NVARCHAR(30) NOT NULL,
SellerId VARCHAR(50) NOT NULL,
Date DATETIME2 NOT NULL,
OrderedUnits INT DEFAULT 0,
OrderedRevenue DECIMAL(18,2) DEFAULT 0,
ReturnsUnits INT DEFAULT 0,
ReturnsRevenue DECIMAL(18,2) DEFAULT 0,
NetUnits INT DEFAULT 0,
NetRevenue DECIMAL(18,2) DEFAULT 0,
Sessions BIGINT DEFAULT 0,
PageViews BIGINT DEFAULT 0,
ConversionRate DECIMAL(5,2) DEFAULT 0,
CreatedAt DATETIME2 NOT NULL DEFAULT dbo.GetEnvDate(),
CONSTRAINT FK_GmsDailyPerformance_Seller FOREIGN KEY (SellerId) REFERENCES Sellers(Id) ON DELETE CASCADE
);
CREATE INDEX IX_GmsDailyPerformance_Asin_Date ON GmsDailyPerformance(Asin, Date DESC);
CREATE INDEX IX_GmsDailyPerformance_Seller_Date ON GmsDailyPerformance(SellerId, Date DESC);
PRINT 'Created GmsDailyPerformance';
END
GO
-- ============================================================
-- SCHEDULED RUNS & PIPELINE
-- ============================================================
IF OBJECT_ID(N'dbo.ScheduledRuns', N'U') IS NULL
BEGIN
CREATE TABLE ScheduledRuns (
Id VARCHAR(50) PRIMARY KEY,
RunType NVARCHAR(50) NOT NULL,
Status NVARCHAR(30) NOT NULL DEFAULT 'PENDING',
TriggeredBy VARCHAR(50),
TriggeredByName NVARCHAR(255),
StartedAt DATETIME2,
CompletedAt DATETIME2,
TotalSellers INT DEFAULT 0,
ProcessedSellers INT DEFAULT 0,
FailedSellers INT DEFAULT 0,
TotalAsins INT DEFAULT 0,
ProcessedAsins INT DEFAULT 0,
ErrorMessage NVARCHAR(MAX),
Metadata NVARCHAR(MAX),
CreatedAt DATETIME2 NOT NULL DEFAULT dbo.GetEnvDate()
);
CREATE INDEX IX_ScheduledRuns_StartedAt ON ScheduledRuns(StartedAt DESC);
CREATE INDEX IX_ScheduledRuns_Status ON ScheduledRuns(Status);
PRINT 'Created ScheduledRuns';
END
GO
IF OBJECT_ID(N'dbo.ScheduledRunDetails', N'U') IS NULL
BEGIN
CREATE TABLE ScheduledRunDetails (
Id VARCHAR(50) PRIMARY KEY,
RunId VARCHAR(50) NOT NULL,
SellerId VARCHAR(50) NOT NULL,
Status NVARCHAR(30) NOT NULL DEFAULT 'PENDING',
AsinsCount INT DEFAULT 0,
ProcessedCount INT DEFAULT 0,
ErrorMessage NVARCHAR(MAX),
StartTime DATETIME2,
EndTime DATETIME2,
CreatedAt DATETIME2 NOT NULL DEFAULT dbo.GetEnvDate(),
CONSTRAINT FK_ScheduledRunDetails_Run FOREIGN KEY (RunId) REFERENCES ScheduledRuns(Id) ON DELETE CASCADE,
CONSTRAINT FK_ScheduledRunDetails_Seller FOREIGN KEY (SellerId) REFERENCES Sellers(Id) ON DELETE CASCADE
);
CREATE INDEX IX_ScheduledRunDetails_RunId ON ScheduledRunDetails(RunId);
CREATE INDEX IX_ScheduledRunDetails_SellerId ON ScheduledRunDetails(SellerId);
PRINT 'Created ScheduledRunDetails';
END
GO
-- LiveSyncRunLogs
IF OBJECT_ID(N'dbo.LiveSyncRunLogs', N'U') IS NULL
BEGIN
CREATE TABLE LiveSyncRunLogs (
Id VARCHAR(50) PRIMARY KEY,
BatchId VARCHAR(50) NULL,
TriggerType NVARCHAR(20) NOT NULL DEFAULT 'MANUAL',
Source NVARCHAR(30) NOT NULL DEFAULT 'SELLER',
TriggeredById VARCHAR(50) NULL,
TriggeredByName NVARCHAR(255) NULL,
SellerId VARCHAR(50) NULL,
SellerName NVARCHAR(255) NULL,
Marketplace NVARCHAR(50) NULL,
Status NVARCHAR(20) NOT NULL DEFAULT 'RUNNING',
TotalAsins INT NOT NULL DEFAULT 0,
SuccessCount INT NOT NULL DEFAULT 0,
FailedCount INT NOT NULL DEFAULT 0,
FailedAsinCodes NVARCHAR(MAX) NULL,
Errors NVARCHAR(MAX) NULL,
DurationMs INT NULL,
StartedAt DATETIME2 NOT NULL DEFAULT dbo.GetEnvDate(),
CompletedAt DATETIME2 NULL,
Metadata NVARCHAR(MAX) NULL
);
CREATE INDEX IX_LiveSyncRunLogs_StartedAt ON LiveSyncRunLogs(StartedAt DESC);
CREATE INDEX IX_LiveSyncRunLogs_Seller ON LiveSyncRunLogs(SellerId);
CREATE INDEX IX_LiveSyncRunLogs_TriggerType ON LiveSyncRunLogs(TriggerType);
CREATE INDEX IX_LiveSyncRunLogs_Batch ON LiveSyncRunLogs(BatchId);
PRINT 'Created LiveSyncRunLogs';
END
GO
IF OBJECT_ID(N'dbo.LiveSyncRunAsinLogs', N'U') IS NULL
BEGIN
CREATE TABLE LiveSyncRunAsinLogs (
Id VARCHAR(50) PRIMARY KEY,
RunId VARCHAR(50) NOT NULL,
AsinCode NVARCHAR(30) NULL,
AsinId VARCHAR(50) NULL,
Status NVARCHAR(20) NOT NULL,
ErrorMessage NVARCHAR(MAX) NULL,
LoggedAt DATETIME2 NOT NULL DEFAULT dbo.GetEnvDate()
);
CREATE INDEX IX_LiveSyncRunAsinLogs_RunId ON LiveSyncRunAsinLogs(RunId);
CREATE INDEX IX_LiveSyncRunAsinLogs_AsinCode ON LiveSyncRunAsinLogs(AsinCode);
PRINT 'Created LiveSyncRunAsinLogs';
END
GO
-- ScrapeJobHistory
IF OBJECT_ID(N'dbo.ScrapeJobHistory', N'U') IS NULL
BEGIN
CREATE TABLE ScrapeJobHistory (
Id VARCHAR(50) PRIMARY KEY,
JobId NVARCHAR(100) NOT NULL,
SellerId VARCHAR(50) NOT NULL,
UserId VARCHAR(50) NULL,
Status NVARCHAR(50) NOT NULL,
TotalAsins INT DEFAULT 0,
Processed INT DEFAULT 0,
Successful INT DEFAULT 0,
Failed INT DEFAULT 0,
StartTime DATETIME2 NULL,
EndTime DATETIME2 NULL,
ErrorMessage NVARCHAR(MAX) NULL,
CreatedAt DATETIME2 NOT NULL DEFAULT dbo.GetEnvDate()
);
CREATE INDEX IX_ScrapeJobHistory_SellerId ON ScrapeJobHistory(SellerId);
CREATE INDEX IX_ScrapeJobHistory_JobId ON ScrapeJobHistory(JobId);
CREATE INDEX IX_ScrapeJobHistory_CreatedAt ON ScrapeJobHistory(CreatedAt);
PRINT 'Created ScrapeJobHistory';
END
GO
IF OBJECT_ID(N'dbo.ScrapeJobDetails', N'U') IS NULL
BEGIN
CREATE TABLE ScrapeJobDetails (
Id VARCHAR(50) PRIMARY KEY,
JobId NVARCHAR(100) NOT NULL,
Asin NVARCHAR(20) NOT NULL,
Success BIT NOT NULL,
Rating DECIMAL(3, 2) DEFAULT 0,
ReviewCount INT DEFAULT 0,
HasAplus BIT DEFAULT 0,
ErrorMessage NVARCHAR(MAX) NULL,
DurationMs INT DEFAULT 0,
ScrapedAt DATETIME2 NOT NULL DEFAULT dbo.GetEnvDate()
);
CREATE INDEX IX_ScrapeJobDetails_JobId ON ScrapeJobDetails(JobId);
CREATE INDEX IX_ScrapeJobDetails_Asin ON ScrapeJobDetails(Asin);
CREATE INDEX IX_ScrapeJobDetails_Success ON ScrapeJobDetails(Success);
PRINT 'Created ScrapeJobDetails';
END
GO
-- ============================================================
-- PEMS (TaskFlow) - ALL PHASES
-- ============================================================
-- TaskSlaPolicies
IF OBJECT_ID(N'dbo.TaskSlaPolicies', N'U') IS NULL
BEGIN
CREATE TABLE TaskSlaPolicies (
Id VARCHAR(50) PRIMARY KEY,
Name NVARCHAR(255) NOT NULL,
Description NVARCHAR(MAX) NULL,
SlaHours INT NOT NULL DEFAULT 48,
WarningHours NVARCHAR(MAX) NULL,
BusinessHoursOnly BIT NOT NULL DEFAULT 1,
Timezone NVARCHAR(50) NOT NULL DEFAULT 'Asia/Kolkata',
ExcludeWeekends BIT NOT NULL DEFAULT 1,
HolidayCalendar NVARCHAR(100) NULL,
EscalationRules NVARCHAR(MAX) NOT NULL,
NotificationChannels NVARCHAR(MAX) NOT NULL,
IsDefault BIT NOT NULL DEFAULT 0,
IsActive BIT NOT NULL DEFAULT 1,
CreatedBy VARCHAR(50) NULL,
CreatedAt DATETIME2 NOT NULL DEFAULT dbo.GetEnvDate(),
UpdatedAt DATETIME2 NOT NULL DEFAULT dbo.GetEnvDate()
);
CREATE UNIQUE INDEX UX_TaskSlaPolicies_Default ON TaskSlaPolicies(IsDefault) WHERE IsDefault = 1;
CREATE INDEX IX_TaskSlaPolicies_Active ON TaskSlaPolicies(IsActive);
PRINT 'Created TaskSlaPolicies';
END
GO
-- TaskTemplates
IF OBJECT_ID(N'dbo.TaskTemplates', N'U') IS NULL
BEGIN
CREATE TABLE TaskTemplates (
Id VARCHAR(24) PRIMARY KEY,
Code NVARCHAR(20) NOT NULL UNIQUE,
Name NVARCHAR(255) NOT NULL,
Description NVARCHAR(MAX) NULL,
Category NVARCHAR(100) NOT NULL DEFAULT 'GENERAL',
Department NVARCHAR(100) NOT NULL DEFAULT 'OPERATIONS',
DefaultAssigneeRole NVARCHAR(50) NOT NULL DEFAULT 'brand_manager',
DefaultReviewerRole NVARCHAR(50) NOT NULL DEFAULT 'reviewer',
DefaultSlaPolicyId VARCHAR(50) NULL,
DefaultPriority NVARCHAR(20) NOT NULL DEFAULT 'MEDIUM',
DefaultSlaHours INT NOT NULL DEFAULT 48,
Frequency NVARCHAR(20) NOT NULL DEFAULT 'ONE_TIME',
CronExpression NVARCHAR(100) NULL,
BusinessDaysOnly BIT NOT NULL DEFAULT 1,
ExcludeHolidays BIT NOT NULL DEFAULT 1,
HolidayCalendar NVARCHAR(50) NULL,
Timezone NVARCHAR(50) NOT NULL DEFAULT 'Asia/Kolkata',
SubtaskDefinitions NVARCHAR(MAX) NULL,
ActivityDefinitions NVARCHAR(MAX) NULL,
Tags NVARCHAR(MAX) NULL,
IsActive BIT NOT NULL DEFAULT 1,
CreatedBy VARCHAR(50) NULL,
CreatedAt DATETIME2 NOT NULL DEFAULT dbo.GetEnvDate(),
UpdatedAt DATETIME2 NOT NULL DEFAULT dbo.GetEnvDate(),
TemplateVersion INT NOT NULL DEFAULT 1,
ExecutionComplexity NVARCHAR(20) DEFAULT 'MEDIUM',
EstimatedExecutionMinutes INT DEFAULT 60,
AutoAssignEnabled BIT NOT NULL DEFAULT 0,
ReviewRequired BIT NOT NULL DEFAULT 1,
EscalationHours INT DEFAULT 24,
CriticalityScore INT DEFAULT 5,
AutomationEligible BIT NOT NULL DEFAULT 0,
TemplateOwnerId VARCHAR(50),
LastGeneratedAt DATETIME2 NULL,
NextScheduledDate DATETIME2 NULL,
CONSTRAINT FK_TaskTemplates_SlaPolicy FOREIGN KEY (DefaultSlaPolicyId) REFERENCES TaskSlaPolicies(Id)
);
CREATE INDEX IX_TaskTemplates_Active ON TaskTemplates(IsActive);
CREATE INDEX IX_TaskTemplates_Category ON TaskTemplates(Category);
CREATE INDEX IX_TaskTemplates_Department ON TaskTemplates(Department);
CREATE INDEX IX_TaskTemplates_FrequencyActive ON TaskTemplates(Frequency, IsActive) INCLUDE (NextScheduledDate);
PRINT 'Created TaskTemplates';
END
GO
-- TaskInstances
IF OBJECT_ID(N'dbo.TaskInstances', N'U') IS NULL
BEGIN
CREATE TABLE TaskInstances (
Id VARCHAR(50) PRIMARY KEY,
Code NVARCHAR(20) NOT NULL UNIQUE,
TemplateId VARCHAR(24) NULL,
Title NVARCHAR(255) NOT NULL,
Description NVARCHAR(MAX) NULL,
Status NVARCHAR(20) NOT NULL DEFAULT 'CREATED',
AssigneeId VARCHAR(50) NULL,
ReviewerId VARCHAR(50) NULL,
AssigneeRole NVARCHAR(50) NULL,
ReviewerRole NVARCHAR(50) NULL,
SellerId VARCHAR(50) NULL,
Department NVARCHAR(100) NOT NULL DEFAULT 'OPERATIONS',
Priority NVARCHAR(20) NOT NULL DEFAULT 'MEDIUM',
Frequency NVARCHAR(50) NULL DEFAULT 'ONE_TIME',
SlaHours INT NOT NULL DEFAULT 48,
SlaPolicyId VARCHAR(50) NULL,
DueDate DATETIME2 NULL,
AssignedAt DATETIME2 NULL,
StartedAt DATETIME2 NULL,
SubmittedAt DATETIME2 NULL,
ReviewedAt DATETIME2 NULL,
CompletedAt DATETIME2 NULL,
SlaStatus NVARCHAR(20) NOT NULL DEFAULT 'WITHIN_SLA',
SlaBreachedAt DATETIME2 NULL,
EscalationLevel INT NOT NULL DEFAULT 0,
Tags NVARCHAR(MAX) NULL,
CreatedBy VARCHAR(50) NULL,
CreatedAt DATETIME2 NOT NULL DEFAULT dbo.GetEnvDate(),
UpdatedAt DATETIME2 NOT NULL DEFAULT dbo.GetEnvDate(),
SubTaskCount INT DEFAULT 0,
ActivityCount INT DEFAULT 0,
CompletedSubTasks INT DEFAULT 0,
ProgressPct DECIMAL(5,2) DEFAULT 0,
AssigneeName NVARCHAR(200) DEFAULT '',
ReviewerName NVARCHAR(200) DEFAULT '',
ApprovalLevel NVARCHAR(20) DEFAULT 'single',
BackupReviewerId VARCHAR(50),
ApproverCount INT DEFAULT 0,
RequiredApprovals INT DEFAULT 1,
WeightedProgressPct DECIMAL(5,2) DEFAULT 0,
LastSlaWarningAt DATETIME2 NULL,
LastSlaBreachAt DATETIME2 NULL,
CONSTRAINT FK_TaskInstances_Template FOREIGN KEY (TemplateId) REFERENCES TaskTemplates(Id),
CONSTRAINT FK_TaskInstances_SlaPolicy FOREIGN KEY (SlaPolicyId) REFERENCES TaskSlaPolicies(Id),
CONSTRAINT FK_TaskInstances_Assignee FOREIGN KEY (AssigneeId) REFERENCES Users(Id),
CONSTRAINT FK_TaskInstances_Reviewer FOREIGN KEY (ReviewerId) REFERENCES Users(Id)
);
CREATE INDEX IX_TaskInstances_Status ON TaskInstances(Status);
CREATE INDEX IX_TaskInstances_AssigneeId ON TaskInstances(AssigneeId);
CREATE INDEX IX_TaskInstances_ReviewerId ON TaskInstances(ReviewerId);
CREATE INDEX IX_TaskInstances_SellerId ON TaskInstances(SellerId);
CREATE INDEX IX_TaskInstances_DueDate ON TaskInstances(DueDate);
CREATE INDEX IX_TaskInstances_Status_DueDate ON TaskInstances(Status, DueDate);
CREATE INDEX IX_TaskInstances_SlaStatus ON TaskInstances(SlaStatus);
CREATE INDEX IX_TaskInstances_CreatedAt ON TaskInstances(CreatedAt);
CREATE INDEX IX_TaskInstances_Assignee_Status ON TaskInstances(AssigneeId, Status);
CREATE INDEX IX_TaskInstances_FrequencyStatus ON TaskInstances(Frequency, Status) INCLUDE (Department, DueDate);
CREATE INDEX IX_PemsInstance_Status ON TaskInstances(Status) INCLUDE (SellerId, AssigneeId, DueDate);
CREATE INDEX IX_PemsInstance_Seller ON TaskInstances(SellerId) INCLUDE (Status, DueDate);
CREATE INDEX IX_PemsInstance_AssignedTo ON TaskInstances(AssigneeId) INCLUDE (Status, DueDate);
CREATE INDEX IX_PemsInstance_Reviewer ON TaskInstances(ReviewerId) INCLUDE (Status);
CREATE INDEX IX_PemsInstance_DueDate ON TaskInstances(DueDate) INCLUDE (Status, SlaStatus);
CREATE INDEX IX_PemsInstance_Template ON TaskInstances(TemplateId);
CREATE INDEX IX_PemsInstance_Department ON TaskInstances(Department) INCLUDE (Status, DueDate);
CREATE INDEX IX_PemsInstances_FrequencyStatus ON TaskInstances(Frequency, Status) INCLUDE (Department, DueDate);
CREATE INDEX IX_TaskInstances_Assignee_Due_Status ON TaskInstances(AssigneeId, DueDate, Status) INCLUDE (TemplateId, Title, Priority, SlaHours, ReviewerId, CreatedAt);
CREATE INDEX IX_TaskInstances_Reviewer_Status ON TaskInstances(ReviewerId, Status) INCLUDE (AssigneeId, Title, DueDate, Priority, SlaHours, CreatedAt);
CREATE INDEX IX_TaskInstances_Template_Status ON TaskInstances(TemplateId, Status) INCLUDE (AssigneeId, ReviewerId, DueDate, Priority, SlaHours, CreatedAt);
PRINT 'Created TaskInstances';
END
GO
-- TaskSubTasks
IF OBJECT_ID(N'dbo.TaskSubTasks', N'U') IS NULL
BEGIN
CREATE TABLE TaskSubTasks (
Id VARCHAR(50) PRIMARY KEY,
TaskId VARCHAR(50) NOT NULL,
Code NVARCHAR(20) NOT NULL,
Title NVARCHAR(255) NOT NULL,
Description NVARCHAR(MAX) NULL,
Status NVARCHAR(20) NOT NULL DEFAULT 'PENDING',
SortOrder INT NOT NULL DEFAULT 0,
ExpectedOutput NVARCHAR(MAX) NULL,
AssigneeId VARCHAR(50) NULL,
CompletedAt DATETIME2 NULL,
CreatedAt DATETIME2 NOT NULL DEFAULT dbo.GetEnvDate(),
UpdatedAt DATETIME2 NOT NULL DEFAULT dbo.GetEnvDate(),
WeightagePct DECIMAL(5,2) DEFAULT 0,
IsMandatory BIT NOT NULL DEFAULT 1,
ReviewRequired BIT NOT NULL DEFAULT 0,
OwnerType NVARCHAR(50) DEFAULT 'Brand Manager',
CONSTRAINT FK_TaskSubTasks_Task FOREIGN KEY (TaskId) REFERENCES TaskInstances(Id) ON DELETE CASCADE,
CONSTRAINT FK_TaskSubTasks_Assignee FOREIGN KEY (AssigneeId) REFERENCES Users(Id)
);
CREATE INDEX IX_TaskSubTasks_TaskId ON TaskSubTasks(TaskId);
CREATE INDEX IX_TaskSubTasks_AssigneeId ON TaskSubTasks(AssigneeId);
CREATE INDEX IX_TaskSubTasks_Status ON TaskSubTasks(Status);
CREATE INDEX IX_PemsSubTask_Instance ON TaskSubTasks(TaskId);
PRINT 'Created TaskSubTasks';
END
GO
-- TaskActivities
IF OBJECT_ID(N'dbo.TaskActivities', N'U') IS NULL
BEGIN
CREATE TABLE TaskActivities (
Id VARCHAR(50) PRIMARY KEY,
SubTaskId VARCHAR(50) NOT NULL,
TaskId VARCHAR(50) NOT NULL,
StepNo INT NOT NULL,
Title NVARCHAR(255) NOT NULL,
Instructions NVARCHAR(MAX) NULL,
ExpectedOutput NVARCHAR(MAX) NULL,
IsMandatory BIT NOT NULL DEFAULT 1,
IsCompleted BIT NOT NULL DEFAULT 0,
CompletedAt DATETIME2 NULL,
CompletedBy VARCHAR(50) NULL,
CreatedAt DATETIME2 NOT NULL DEFAULT dbo.GetEnvDate(),
UpdatedAt DATETIME2 NOT NULL DEFAULT dbo.GetEnvDate(),
CONSTRAINT FK_TaskActivities_SubTask FOREIGN KEY (SubTaskId) REFERENCES TaskSubTasks(Id),
CONSTRAINT FK_TaskActivities_Task FOREIGN KEY (TaskId) REFERENCES TaskInstances(Id) ON DELETE CASCADE,
CONSTRAINT FK_TaskActivities_CompletedBy FOREIGN KEY (CompletedBy) REFERENCES Users(Id)
);
CREATE INDEX IX_TaskActivities_SubTaskId ON TaskActivities(SubTaskId);
CREATE INDEX IX_TaskActivities_TaskId ON TaskActivities(TaskId);
CREATE INDEX IX_TaskActivities_IsCompleted ON TaskActivities(IsCompleted);
CREATE INDEX IX_PemsActivity_Instance ON TaskActivities(TaskId);
PRINT 'Created TaskActivities';
END
GO
-- TaskAttachments
IF OBJECT_ID(N'dbo.TaskAttachments', N'U') IS NULL
BEGIN
CREATE TABLE TaskAttachments (
Id VARCHAR(50) PRIMARY KEY,
TaskId VARCHAR(50) NOT NULL,
SubTaskId VARCHAR(50) NULL,
ActivityId VARCHAR(50) NULL,
FileName NVARCHAR(255) NOT NULL,
FileUrl NVARCHAR(500) NOT NULL,
FileType NVARCHAR(50) NOT NULL DEFAULT 'FILE',
FileSize BIGINT NULL,
MimeType NVARCHAR(100) NULL,
Remarks NVARCHAR(MAX) NULL,
UploadedBy VARCHAR(50) NOT NULL,
UploadedByName NVARCHAR(255) NULL,
UploadedAt DATETIME2 NOT NULL DEFAULT dbo.GetEnvDate(),
CONSTRAINT FK_TaskAttachments_Task FOREIGN KEY (TaskId) REFERENCES TaskInstances(Id) ON DELETE CASCADE,
CONSTRAINT FK_TaskAttachments_SubTask FOREIGN KEY (SubTaskId) REFERENCES TaskSubTasks(Id),
CONSTRAINT FK_TaskAttachments_Activity FOREIGN KEY (ActivityId) REFERENCES TaskActivities(Id),
CONSTRAINT FK_TaskAttachments_UploadedBy FOREIGN KEY (UploadedBy) REFERENCES Users(Id)
);
CREATE INDEX IX_TaskAttachments_TaskId ON TaskAttachments(TaskId);
CREATE INDEX IX_TaskAttachments_SubTaskId ON TaskAttachments(SubTaskId);
CREATE INDEX IX_TaskAttachments_ActivityId ON TaskAttachments(ActivityId);
PRINT 'Created TaskAttachments';
END
GO
-- TaskComments
IF OBJECT_ID(N'dbo.TaskComments', N'U') IS NULL
BEGIN
CREATE TABLE TaskComments (
Id VARCHAR(50) PRIMARY KEY,
TaskId VARCHAR(50) NOT NULL,
SubTaskId VARCHAR(50) NULL,
AuthorId VARCHAR(50) NOT NULL,
Content NVARCHAR(MAX) NOT NULL,
IsInternal BIT NOT NULL DEFAULT 0,
ParentCommentId VARCHAR(50) NULL,
CreatedAt DATETIME2 NOT NULL DEFAULT dbo.GetEnvDate(),
UpdatedAt DATETIME2 NULL,
DeletedAt DATETIME2 NULL,
CONSTRAINT FK_TaskComments_Task FOREIGN KEY (TaskId) REFERENCES TaskInstances(Id) ON DELETE CASCADE,
CONSTRAINT FK_TaskComments_SubTask FOREIGN KEY (SubTaskId) REFERENCES TaskSubTasks(Id),
CONSTRAINT FK_TaskComments_Author FOREIGN KEY (AuthorId) REFERENCES Users(Id),
CONSTRAINT FK_TaskComments_Parent FOREIGN KEY (ParentCommentId) REFERENCES TaskComments(Id)
);
CREATE INDEX IX_TaskComments_TaskId ON TaskComments(TaskId);
CREATE INDEX IX_TaskComments_SubTaskId ON TaskComments(SubTaskId);
CREATE INDEX IX_TaskComments_AuthorId ON TaskComments(AuthorId);
CREATE INDEX IX_TaskComments_CreatedAt ON TaskComments(CreatedAt);
PRINT 'Created TaskComments';
END
GO
-- TaskAuditLogs
IF OBJECT_ID(N'dbo.TaskAuditLogs', N'U') IS NULL
BEGIN
CREATE TABLE TaskAuditLogs (
Id VARCHAR(50) PRIMARY KEY,
TaskId VARCHAR(50) NOT NULL,
Action NVARCHAR(50) NOT NULL,
FromStatus NVARCHAR(20) NULL,
ToStatus NVARCHAR(20) NULL,
ActorId VARCHAR(50) NULL,
ActorName NVARCHAR(255) NULL,
Details NVARCHAR(MAX) NULL,
Metadata NVARCHAR(MAX) NULL,
CreatedAt DATETIME2 NOT NULL DEFAULT dbo.GetEnvDate(),
CONSTRAINT FK_TaskAuditLogs_Task FOREIGN KEY (TaskId) REFERENCES TaskInstances(Id) ON DELETE CASCADE
);
CREATE INDEX IX_TaskAuditLogs_TaskId_CreatedAt ON TaskAuditLogs(TaskId, CreatedAt DESC);
CREATE INDEX IX_TaskAuditLogs_Action ON TaskAuditLogs(Action);
CREATE INDEX IX_TaskAuditLogs_ActorId ON TaskAuditLogs(ActorId);
CREATE INDEX IX_PemsTaskEvents_TaskVersion ON TaskAuditLogs(TaskId, CreatedAt);
PRINT 'Created TaskAuditLogs';
END
GO
-- TaskWatchers
IF OBJECT_ID(N'dbo.TaskWatchers', N'U') IS NULL
BEGIN
CREATE TABLE TaskWatchers (
Id VARCHAR(50) PRIMARY KEY,
TaskId VARCHAR(50) NOT NULL,