Database Tables

CREATE TABLE ExternalLeads (
    LeadId INT PRIMARY KEY IDENTITY(1,1),
    Mobile NVARCHAR(20) NOT NULL,
    Name NVARCHAR(100),
    Email NVARCHAR(100),
    StaffId INT,
    CallSourceId INT NOT NULL,
    Status NVARCHAR(50) NOT NULL DEFAULT 'NEW',
    IsActive BIT NOT NULL DEFAULT 1,
    CreatedDate DATETIME NOT NULL DEFAULT GETDATE(),
    ModifiedDate DATETIME NOT NULL DEFAULT GETDATE(),
    AICallStatus NVARCHAR(50) NULL,
    AIPostRetryCount INT DEFAULT 0,
    AIProviderType NVARCHAR(50),
    AICallId NVARCHAR(100),
    PostedToAIDate DATETIME NULL,
    LastAIAttempt DATETIME NULL,
    AIFailureReason NVARCHAR(MAX),
 
    FOREIGN KEY (StaffId) REFERENCES Staff(StaffId),
    FOREIGN KEY (CallSourceId) REFERENCES CallSource(SourceId)
);
 
CREATE INDEX idx_ExternalLeads_Mobile ON ExternalLeads(Mobile);
CREATE INDEX idx_ExternalLeads_StaffId ON ExternalLeads(StaffId);
CREATE INDEX idx_ExternalLeads_Status ON ExternalLeads(Status);
CREATE INDEX idx_ExternalLeads_IsActive ON ExternalLeads(IsActive);
CREATE INDEX idx_ExternalLeads_CreatedDate ON ExternalLeads(CreatedDate);
CREATE INDEX idx_ExternalLeads_AIStatus ON ExternalLeads(AICallStatus);

CREATE TABLE CallLog (
    LogId INT PRIMARY KEY IDENTITY(1,1),
    LeadId INT NOT NULL,
    StaffId INT,
    Mobile NVARCHAR(20),
    CallStatus NVARCHAR(50) NOT NULL,
        -- Values: ANSWERED, NOT_ANSWERED, INVALID, BUSY, DO_NOT_CALL
    Notes NVARCHAR(MAX),
    DurationSeconds INT DEFAULT 0,
    CallDate DATETIME NOT NULL,
    CreatedDate DATETIME NOT NULL DEFAULT GETDATE(),
 
    FOREIGN KEY (LeadId) REFERENCES ExternalLeads(LeadId),
    FOREIGN KEY (StaffId) REFERENCES Staff(StaffId)
);
 
CREATE INDEX idx_CallLog_LeadId ON CallLog(LeadId);
CREATE INDEX idx_CallLog_StaffId ON CallLog(StaffId);
CREATE INDEX idx_CallLog_CallDate ON CallLog(CallDate);
CREATE INDEX idx_CallLog_CallStatus ON CallLog(CallStatus);

CREATE TABLE FollowupCalls (
    FollowupId INT PRIMARY KEY IDENTITY(1,1),
    LeadId INT NOT NULL,
    StaffId INT NOT NULL,
    ReviewDateTime DATETIME NOT NULL,
        -- When this follow-up should be reviewed
    IsCompleted BIT NOT NULL DEFAULT 0,
    CompletedDate DATETIME NULL,
    Notes NVARCHAR(MAX),
    CreatedDate DATETIME NOT NULL DEFAULT GETDATE(),
    ModifiedDate DATETIME NOT NULL DEFAULT GETDATE(),
 
    FOREIGN KEY (LeadId) REFERENCES ExternalLeads(LeadId),
    FOREIGN KEY (StaffId) REFERENCES Staff(StaffId)
);
 
CREATE INDEX idx_FollowupCalls_StaffId ON FollowupCalls(StaffId);
CREATE INDEX idx_FollowupCalls_ReviewDateTime ON FollowupCalls(ReviewDateTime);
CREATE INDEX idx_FollowupCalls_IsCompleted ON FollowupCalls(IsCompleted);
CREATE INDEX idx_FollowupCalls_StaffReview ON FollowupCalls(StaffId, ReviewDateTime)
    WHERE IsCompleted = 0;

CREATE TABLE CallSource (
    SourceId INT PRIMARY KEY IDENTITY(1,1),
    SourceName NVARCHAR(100) NOT NULL,
        -- Values: Website, PurchasedList, Referral, Inbound, etc.
    DIDNumber NVARCHAR(20),
        -- Direct Inbound Dialing number if applicable
    IsActive BIT NOT NULL DEFAULT 1,
    ProjectId INT,
    CreatedDate DATETIME NOT NULL DEFAULT GETDATE(),
    ModifiedDate DATETIME NOT NULL DEFAULT GETDATE()
);
 
CREATE INDEX idx_CallSource_SourceName ON CallSource(SourceName);

CREATE TABLE WeeklyLeadHistory (
    HistoryId INT PRIMARY KEY IDENTITY(1,1),
    WeekStartDate DATETIME NOT NULL,
    StaffId INT NOT NULL,
    ManagerId INT,
    HierarchyLevel NVARCHAR(50) NOT NULL,
        -- Values: Telecaller, BDO, SeniorManager, GM, AVP
    NewLeadsAssigned INT NOT NULL DEFAULT 0,
    SVFixed INT NOT NULL DEFAULT 0,
    CallProgress INT NOT NULL DEFAULT 0,
    Trashed INT NOT NULL DEFAULT 0,
    NoResponse INT NOT NULL DEFAULT 0,
    FollowupCreated INT NOT NULL DEFAULT 0,
    FollowupCompleted INT NOT NULL DEFAULT 0,
    ConversionRate DECIMAL(5,2) NOT NULL DEFAULT 0,
    FollowupCompletionRate DECIMAL(5,2) NOT NULL DEFAULT 0,
    AvgResponseHours DECIMAL(8,2) NOT NULL DEFAULT 0,
    ActivityRate DECIMAL(5,2) NOT NULL DEFAULT 0,
    WasteRate DECIMAL(5,2) NOT NULL DEFAULT 0,
    CreatedDate DATETIME NOT NULL DEFAULT GETDATE(),
 
    FOREIGN KEY (StaffId) REFERENCES Staff(StaffId),
    FOREIGN KEY (ManagerId) REFERENCES Staff(StaffId)
);
 
CREATE INDEX idx_WeeklyLeadHistory_StaffId ON WeeklyLeadHistory(StaffId);
CREATE INDEX idx_WeeklyLeadHistory_WeekStart ON WeeklyLeadHistory(WeekStartDate);
CREATE INDEX idx_WeeklyLeadHistory_HierarchyLevel ON WeeklyLeadHistory(HierarchyLevel);
CREATE INDEX idx_WeeklyLeadHistory_StaffWeek ON WeeklyLeadHistory(StaffId, WeekStartDate);

CREATE TABLE AICallLog (
    AILogId INT PRIMARY KEY IDENTITY(1,1),
    LeadId INT NOT NULL,
    ProviderId NVARCHAR(50) NOT NULL,
        -- AI provider: VoiceAPI, Twilio, CustomProvider
    CallId NVARCHAR(100) NOT NULL UNIQUE,
        -- Provider's call identifier
    Status NVARCHAR(50) NOT NULL,
        -- Values: INITIATED, IN_PROGRESS, COMPLETED, FAILED
    DurationSeconds INT DEFAULT 0,
    RecordingUrl NVARCHAR(MAX),
    TranscriptText NVARCHAR(MAX),
    CallResult NVARCHAR(50),
        -- Values: INTERESTED, NOT_INTERESTED, CALLBACK, ERROR
    CreatedDate DATETIME NOT NULL DEFAULT GETDATE(),
    CompletedDate DATETIME NULL,
 
    FOREIGN KEY (LeadId) REFERENCES ExternalLeads(LeadId)
);
 
CREATE INDEX idx_AICallLog_LeadId ON AICallLog(LeadId);
CREATE INDEX idx_AICallLog_ProviderId ON AICallLog(ProviderId);
CREATE INDEX idx_AICallLog_Status ON AICallLog(Status);
CREATE INDEX idx_AICallLog_CreatedDate ON AICallLog(CreatedDate);

CREATE TABLE TeleLeaderMapping (
    MappingId INT PRIMARY KEY IDENTITY(1,1),
    LeaderId INT NOT NULL,
        -- Manager (BDO, Senior Manager, GM, AVP)
    StaffId INT NOT NULL,
        -- Direct report (TC or BDO)
    IsActive BIT NOT NULL DEFAULT 1,
    CreatedDate DATETIME NOT NULL DEFAULT GETDATE(),
    ModifiedDate DATETIME NOT NULL DEFAULT GETDATE(),
 
    FOREIGN KEY (LeaderId) REFERENCES Staff(StaffId),
    FOREIGN KEY (StaffId) REFERENCES Staff(StaffId),
 
    CONSTRAINT chk_SelfMapping CHECK (LeaderId <> StaffId)
);
 
CREATE INDEX idx_TeleLeaderMapping_LeaderId ON TeleLeaderMapping(LeaderId);
CREATE INDEX idx_TeleLeaderMapping_StaffId ON TeleLeaderMapping(StaffId);
CREATE INDEX idx_TeleLeaderMapping_Active ON TeleLeaderMapping(IsActive);

CREATE TABLE Staff (
    StaffId INT PRIMARY KEY IDENTITY(1,1),
    StaffName NVARCHAR(100) NOT NULL,
    Email NVARCHAR(100) UNIQUE,
    Phone NVARCHAR(20),
    Role NVARCHAR(50),
        -- Values: Telecaller, BDO, SeniorManager, GM, AVP
    IsActive BIT NOT NULL DEFAULT 1,
    CreatedDate DATETIME NOT NULL DEFAULT GETDATE(),
    ModifiedDate DATETIME NOT NULL DEFAULT GETDATE()
);
 
CREATE INDEX idx_Staff_StaffName ON Staff(StaffName);
CREATE INDEX idx_Staff_Role ON Staff(Role);

-- Archive old data to separate table
CREATE TABLE ExternalLeads_Archive (
    -- Same schema as ExternalLeads
);
 
-- Monthly job to archive leads older than 2 years
INSERT INTO ExternalLeads_Archive
SELECT * FROM ExternalLeads
WHERE DATEDIFF(YEAR, CreatedDate, GETDATE()) > 2
AND IsActive = 0;
 
DELETE FROM ExternalLeads
WHERE DATEDIFF(YEAR, CreatedDate, GETDATE()) > 2
AND IsActive = 0;

-- Optimize for most common queries
ALTER TABLE ExternalLeads
    ADD CONSTRAINT pk_ExternalLeads PRIMARY KEY CLUSTERED (LeadId);
 
ALTER TABLE CallLog
    ADD CONSTRAINT pk_CallLog PRIMARY KEY CLUSTERED (LeadId, CallDate);
 
ALTER TABLE FollowupCalls
    ADD CONSTRAINT pk_FollowupCalls PRIMARY KEY CLUSTERED (StaffId, ReviewDateTime);

-- For "due soon" follow-up query
CREATE NONCLUSTERED INDEX idx_FollowupDueSoon
ON FollowupCalls (StaffId, IsCompleted)
INCLUDE (ReviewDateTime, FollowupId, LeadId)
WHERE IsCompleted = 0;