/* ===================================================================
   Behbood Polymer Arman Toos — Commission Management System
   SQL Server Schema (normalized, with FK relationships and indexes)
   =================================================================== */

IF DB_ID('BehboodCommissionDB') IS NULL
BEGIN
    CREATE DATABASE BehboodCommissionDB;
END
GO

USE BehboodCommissionDB;
GO

/* -------------------------------------------------------------------
   Users — local (username/password) accounts and SSO-provisioned users
   ------------------------------------------------------------------- */
IF OBJECT_ID('dbo.Users', 'U') IS NULL
CREATE TABLE dbo.Users (
    UserId          INT IDENTITY(1,1) PRIMARY KEY,
    Username        NVARCHAR(100)   NOT NULL UNIQUE,
    PasswordHash    NVARCHAR(255)   NULL,               -- NULL for pure-SSO accounts
    FullName        NVARCHAR(150)   NULL,
    Email           NVARCHAR(150)   NULL,
    Role            NVARCHAR(30)    NOT NULL DEFAULT 'admin',
    OtpSecret       NVARCHAR(255)   NULL,                -- base32 secret shared with Android app
    IsActive        BIT             NOT NULL DEFAULT 1,
    CreatedAt       DATETIME2       NOT NULL DEFAULT SYSUTCDATETIME(),
    UpdatedAt       DATETIME2       NOT NULL DEFAULT SYSUTCDATETIME(),
    LastLoginAt     DATETIME2       NULL
);
GO

/* -------------------------------------------------------------------
   Customers
   ------------------------------------------------------------------- */
IF OBJECT_ID('dbo.Customers', 'U') IS NULL
CREATE TABLE dbo.Customers (
    CustomerId      INT IDENTITY(1,1) PRIMARY KEY,
    FullName        NVARCHAR(200)   NOT NULL,
    Phone           NVARCHAR(30)    NULL,
    NationalCode    NVARCHAR(20)    NULL,
    Address         NVARCHAR(400)   NULL,
    Notes           NVARCHAR(MAX)   NULL,
    IsActive        BIT             NOT NULL DEFAULT 1,
    CreatedBy       INT             NULL REFERENCES dbo.Users(UserId),
    CreatedAt       DATETIME2       NOT NULL DEFAULT SYSUTCDATETIME(),
    UpdatedAt       DATETIME2       NOT NULL DEFAULT SYSUTCDATETIME()
);
CREATE INDEX IX_Customers_FullName ON dbo.Customers(FullName);
GO

/* -------------------------------------------------------------------
   Transactions — deposits, each generates commission via percentage
   ------------------------------------------------------------------- */
IF OBJECT_ID('dbo.Transactions', 'U') IS NULL
CREATE TABLE dbo.Transactions (
    TransactionId   INT IDENTITY(1,1) PRIMARY KEY,
    CustomerId      INT             NOT NULL REFERENCES dbo.Customers(CustomerId) ON DELETE CASCADE,
    JalaliDate      NVARCHAR(10)    NOT NULL,           -- e.g. 1403/04/18
    GregorianDate   DATE            NOT NULL,           -- normalized for querying/sorting
    Amount          DECIMAL(18,2)   NOT NULL CHECK (Amount >= 0),
    CommissionPercent DECIMAL(5,2)  NOT NULL CHECK (CommissionPercent >= 0),
    CommissionAmount  DECIMAL(18,2) NOT NULL,            -- Amount * Percent / 100 (computed on save)
    CreatedBy       INT             NULL REFERENCES dbo.Users(UserId),
    CreatedAt       DATETIME2       NOT NULL DEFAULT SYSUTCDATETIME(),
    UpdatedAt       DATETIME2       NOT NULL DEFAULT SYSUTCDATETIME()
);
CREATE INDEX IX_Transactions_CustomerId ON dbo.Transactions(CustomerId);
CREATE INDEX IX_Transactions_GregorianDate ON dbo.Transactions(GregorianDate);
GO

/* -------------------------------------------------------------------
   Payments — Main and Miscellaneous
   ------------------------------------------------------------------- */
IF OBJECT_ID('dbo.Payments', 'U') IS NULL
CREATE TABLE dbo.Payments (
    PaymentId       INT IDENTITY(1,1) PRIMARY KEY,
    CustomerId      INT             NOT NULL REFERENCES dbo.Customers(CustomerId) ON DELETE CASCADE,
    PaymentType     NVARCHAR(20)    NOT NULL CHECK (PaymentType IN ('main','misc')),
    JalaliDate      NVARCHAR(10)    NOT NULL,
    GregorianDate   DATE            NOT NULL,
    Amount          DECIMAL(18,2)   NOT NULL CHECK (Amount >= 0),
    Description     NVARCHAR(400)   NULL,
    CreatedBy       INT             NULL REFERENCES dbo.Users(UserId),
    CreatedAt       DATETIME2       NOT NULL DEFAULT SYSUTCDATETIME(),
    UpdatedAt       DATETIME2       NOT NULL DEFAULT SYSUTCDATETIME()
);
CREATE INDEX IX_Payments_CustomerId ON dbo.Payments(CustomerId);
CREATE INDEX IX_Payments_Type ON dbo.Payments(PaymentType);
GO

/* -------------------------------------------------------------------
   AuthKeys — one-time SSO handoff tokens issued by the PHP website
   (also stores the token hash for replay detection even though the
   token itself is a signed+encrypted JWT-like structure)
   ------------------------------------------------------------------- */
IF OBJECT_ID('dbo.AuthKeys', 'U') IS NULL
CREATE TABLE dbo.AuthKeys (
    AuthKeyId       INT IDENTITY(1,1) PRIMARY KEY,
    TokenHash       CHAR(64)        NOT NULL UNIQUE,     -- SHA-256 of the raw token, for replay checks
    Username        NVARCHAR(100)   NOT NULL,
    IssuedAt        DATETIME2       NOT NULL,
    ExpiresAt       DATETIME2       NOT NULL,
    UsedAt          DATETIME2       NULL,                -- set the first (and only) time it's redeemed
    SourceIp        NVARCHAR(64)    NULL,
    CreatedAt       DATETIME2       NOT NULL DEFAULT SYSUTCDATETIME()
);
CREATE INDEX IX_AuthKeys_Username ON dbo.AuthKeys(Username);
CREATE INDEX IX_AuthKeys_ExpiresAt ON dbo.AuthKeys(ExpiresAt);
GO

/* -------------------------------------------------------------------
   OtpVerificationLogs — every OTP attempt, for audit + rate limiting
   ------------------------------------------------------------------- */
IF OBJECT_ID('dbo.OtpVerificationLogs', 'U') IS NULL
CREATE TABLE dbo.OtpVerificationLogs (
    LogId           INT IDENTITY(1,1) PRIMARY KEY,
    UserId          INT             NULL REFERENCES dbo.Users(UserId),
    Username        NVARCHAR(100)   NOT NULL,
    SubmittedCode   CHAR(6)         NOT NULL,
    IsSuccess       BIT             NOT NULL,
    SourceIp        NVARCHAR(64)    NULL,
    UserAgent       NVARCHAR(400)   NULL,
    CreatedAt       DATETIME2       NOT NULL DEFAULT SYSUTCDATETIME()
);
CREATE INDEX IX_OtpLogs_Username ON dbo.OtpVerificationLogs(Username);
CREATE INDEX IX_OtpLogs_CreatedAt ON dbo.OtpVerificationLogs(CreatedAt);
GO

/* -------------------------------------------------------------------
   AuthAuditLogs — general authentication attempt audit trail
   (standard login, SSO handoff, 2FA — success and failure)
   ------------------------------------------------------------------- */
IF OBJECT_ID('dbo.AuthAuditLogs', 'U') IS NULL
CREATE TABLE dbo.AuthAuditLogs (
    AuditId         INT IDENTITY(1,1) PRIMARY KEY,
    EventType       NVARCHAR(40)    NOT NULL,   -- 'login_success' | 'login_failure' | 'sso_success' | 'sso_failure' | 'otp_success' | 'otp_failure' | 'logout'
    Username        NVARCHAR(100)   NULL,
    SourceIp        NVARCHAR(64)    NULL,
    UserAgent       NVARCHAR(400)   NULL,
    Detail          NVARCHAR(400)   NULL,
    CreatedAt       DATETIME2       NOT NULL DEFAULT SYSUTCDATETIME()
);
CREATE INDEX IX_AuthAudit_CreatedAt ON dbo.AuthAuditLogs(CreatedAt);
GO