-- =============================================
-- ZALO BOT INTEGRATION TABLES
-- Tables for storing Zalo user information and messages
-- =============================================

-- Table: ZaloUsers
-- Store Zalo user information from Bot Creator
IF NOT EXISTS (SELECT * FROM sys.tables WHERE name = 'ZaloUsers')
BEGIN
    CREATE TABLE ZaloUsers (
        ID INT PRIMARY KEY IDENTITY(1,1),
        UserID NVARCHAR(255) UNIQUE NOT NULL,
        UserName NVARCHAR(255),
        IsActive BIT DEFAULT 1,
        CreatedAt DATETIME DEFAULT GETDATE(),
        LastMessageAt DATETIME DEFAULT GETDATE(),
        
        -- Additional fields
        PhoneNumber NVARCHAR(20),
        Email NVARCHAR(255),
        Department NVARCHAR(255),
        Position NVARCHAR(255),
        Notes NVARCHAR(MAX)
    );
    
    -- Indexes
    CREATE INDEX IX_ZaloUsers_UserID ON ZaloUsers(UserID);
    CREATE INDEX IX_ZaloUsers_IsActive ON ZaloUsers(IsActive);
    CREATE INDEX IX_ZaloUsers_LastMessageAt ON ZaloUsers(LastMessageAt);
    
    PRINT 'Table ZaloUsers created successfully';
END
ELSE
BEGIN
    PRINT 'Table ZaloUsers already exists';
END
GO

-- Table: ZaloMessages
-- Store message history
IF NOT EXISTS (SELECT * FROM sys.tables WHERE name = 'ZaloMessages')
BEGIN
    CREATE TABLE ZaloMessages (
        ID INT PRIMARY KEY IDENTITY(1,1),
        UserID NVARCHAR(255) NOT NULL,
        Message NVARCHAR(MAX),
        MessageType NVARCHAR(50) DEFAULT 'text', -- text, image, file, etc.
        Direction NVARCHAR(10) DEFAULT 'incoming', -- incoming or outgoing
        MessageTime DATETIME DEFAULT GETDATE(),
        IsRead BIT DEFAULT 0,
        
        -- Foreign key reference
        CONSTRAINT FK_ZaloMessages_UserID FOREIGN KEY (UserID) 
            REFERENCES ZaloUsers(UserID)
    );
    
    -- Indexes
    CREATE INDEX IX_ZaloMessages_UserID ON ZaloMessages(UserID);
    CREATE INDEX IX_ZaloMessages_MessageTime ON ZaloMessages(MessageTime);
    CREATE INDEX IX_ZaloMessages_Direction ON ZaloMessages(Direction);
    
    PRINT 'Table ZaloMessages created successfully';
END
ELSE
BEGIN
    PRINT 'Table ZaloMessages already exists';
END
GO

-- Table: ZaloNotifications
-- Store sent notifications log
IF NOT EXISTS (SELECT * FROM sys.tables WHERE name = 'ZaloNotifications')
BEGIN
    CREATE TABLE ZaloNotifications (
        ID INT PRIMARY KEY IDENTITY(1,1),
        UserID NVARCHAR(255) NOT NULL,
        NotificationType NVARCHAR(100), -- incoming_document, outgoing_document, approval_needed, etc.
        DocumentID INT,
        DocumentNumber NVARCHAR(255),
        Message NVARCHAR(MAX),
        SentAt DATETIME DEFAULT GETDATE(),
        Status NVARCHAR(50) DEFAULT 'sent', -- sent, failed, pending
        ErrorMessage NVARCHAR(MAX),
        
        -- Foreign key reference
        CONSTRAINT FK_ZaloNotifications_UserID FOREIGN KEY (UserID) 
            REFERENCES ZaloUsers(UserID)
    );
    
    -- Indexes
    CREATE INDEX IX_ZaloNotifications_UserID ON ZaloNotifications(UserID);
    CREATE INDEX IX_ZaloNotifications_SentAt ON ZaloNotifications(SentAt);
    CREATE INDEX IX_ZaloNotifications_Status ON ZaloNotifications(Status);
    CREATE INDEX IX_ZaloNotifications_DocumentID ON ZaloNotifications(DocumentID);
    
    PRINT 'Table ZaloNotifications created successfully';
END
ELSE
BEGIN
    PRINT 'Table ZaloNotifications already exists';
END
GO

-- Table: ZaloUserSettings
-- Store user notification preferences
IF NOT EXISTS (SELECT * FROM sys.tables WHERE name = 'ZaloUserSettings')
BEGIN
    CREATE TABLE ZaloUserSettings (
        ID INT PRIMARY KEY IDENTITY(1,1),
        UserID NVARCHAR(255) UNIQUE NOT NULL,
        EnableIncomingNotifications BIT DEFAULT 1,
        EnableOutgoingNotifications BIT DEFAULT 1,
        EnableApprovalNotifications BIT DEFAULT 1,
        NotificationStartTime TIME DEFAULT '08:00:00',
        NotificationEndTime TIME DEFAULT '18:00:00',
        EnableWeekendNotifications BIT DEFAULT 0,
        UpdatedAt DATETIME DEFAULT GETDATE(),
        
        -- Foreign key reference
        CONSTRAINT FK_ZaloUserSettings_UserID FOREIGN KEY (UserID) 
            REFERENCES ZaloUsers(UserID)
    );
    
    -- Index
    CREATE INDEX IX_ZaloUserSettings_UserID ON ZaloUserSettings(UserID);
    
    PRINT 'Table ZaloUserSettings created successfully';
END
ELSE
BEGIN
    PRINT 'Table ZaloUserSettings already exists';
END
GO

-- Insert sample data (optional)
-- IF NOT EXISTS (SELECT * FROM ZaloUsers WHERE UserID = '70c8847eaf3d46631f2c')
-- BEGIN
--     INSERT INTO ZaloUsers (UserID, UserName, IsActive)
--     VALUES ('70c8847eaf3d46631f2c', 'Test User', 1);
--     
--     PRINT 'Sample user inserted';
-- END
-- GO

-- View: Active Zalo Users
IF EXISTS (SELECT * FROM sys.views WHERE name = 'vw_ActiveZaloUsers')
    DROP VIEW vw_ActiveZaloUsers;
GO

CREATE VIEW vw_ActiveZaloUsers AS
SELECT 
    u.ID,
    u.UserID,
    u.UserName,
    u.PhoneNumber,
    u.Email,
    u.Department,
    u.Position,
    u.CreatedAt,
    u.LastMessageAt,
    DATEDIFF(day, u.LastMessageAt, GETDATE()) AS DaysSinceLastMessage,
    s.EnableIncomingNotifications,
    s.EnableOutgoingNotifications,
    s.EnableApprovalNotifications
FROM ZaloUsers u
LEFT JOIN ZaloUserSettings s ON u.UserID = s.UserID
WHERE u.IsActive = 1;
GO

PRINT 'View vw_ActiveZaloUsers created successfully';
GO

-- Stored Procedure: Get users for notification
IF EXISTS (SELECT * FROM sys.procedures WHERE name = 'sp_GetZaloUsersForNotification')
    DROP PROCEDURE sp_GetZaloUsersForNotification;
GO

CREATE PROCEDURE sp_GetZaloUsersForNotification
    @NotificationType NVARCHAR(100)
AS
BEGIN
    SET NOCOUNT ON;
    
    SELECT 
        u.UserID,
        u.UserName,
        u.Email
    FROM ZaloUsers u
    LEFT JOIN ZaloUserSettings s ON u.UserID = s.UserID
    WHERE u.IsActive = 1
    AND (
        (@NotificationType = 'incoming_document' AND ISNULL(s.EnableIncomingNotifications, 1) = 1)
        OR (@NotificationType = 'outgoing_document' AND ISNULL(s.EnableOutgoingNotifications, 1) = 1)
        OR (@NotificationType = 'approval_needed' AND ISNULL(s.EnableApprovalNotifications, 1) = 1)
    );
END
GO

PRINT 'Stored procedure sp_GetZaloUsersForNotification created successfully';
GO

-- Stored Procedure: Log notification
IF EXISTS (SELECT * FROM sys.procedures WHERE name = 'sp_LogZaloNotification')
    DROP PROCEDURE sp_LogZaloNotification;
GO

CREATE PROCEDURE sp_LogZaloNotification
    @UserID NVARCHAR(255),
    @NotificationType NVARCHAR(100),
    @DocumentID INT = NULL,
    @DocumentNumber NVARCHAR(255) = NULL,
    @Message NVARCHAR(MAX),
    @Status NVARCHAR(50) = 'sent',
    @ErrorMessage NVARCHAR(MAX) = NULL
AS
BEGIN
    SET NOCOUNT ON;
    
    INSERT INTO ZaloNotifications (
        UserID, NotificationType, DocumentID, DocumentNumber, 
        Message, Status, ErrorMessage, SentAt
    )
    VALUES (
        @UserID, @NotificationType, @DocumentID, @DocumentNumber,
        @Message, @Status, @ErrorMessage, GETDATE()
    );
    
    SELECT SCOPE_IDENTITY() AS NotificationID;
END
GO

PRINT 'Stored procedure sp_LogZaloNotification created successfully';
GO

PRINT '';
PRINT '===========================================';
PRINT 'ZALO BOT TABLES INSTALLATION COMPLETE!';
PRINT '===========================================';
PRINT '';
PRINT 'Created tables:';
PRINT '  - ZaloUsers';
PRINT '  - ZaloMessages';
PRINT '  - ZaloNotifications';
PRINT '  - ZaloUserSettings';
PRINT '';
PRINT 'Created views:';
PRINT '  - vw_ActiveZaloUsers';
PRINT '';
PRINT 'Created procedures:';
PRINT '  - sp_GetZaloUsersForNotification';
PRINT '  - sp_LogZaloNotification';
PRINT '';

