-- =============================================================================
-- TSOFT 6.0 — Database Schema
-- Database: SHOP2026
-- Naming: existing SHOP2009 table/column names preserved exactly.
--         New tables/columns marked with -- (New)
-- =============================================================================

USE master;
GO

IF NOT EXISTS (SELECT name FROM sys.databases WHERE name = N'SHOP2026')
BEGIN
    CREATE DATABASE SHOP2026
        COLLATE Albanian_CI_AI;   -- Albanian locale, case-insensitive
END
GO

USE SHOP2026;
GO

-- =============================================================================
-- MASTER / REFERENCE TABLES
-- =============================================================================

IF OBJECT_ID('Kompania_tbl') IS NULL
CREATE TABLE Kompania_tbl (
    ID              INT             IDENTITY(1,1) PRIMARY KEY,
    KOMPANIA        NVARCHAR(100)   NOT NULL,
    NR_BIZNESIT     VARCHAR(50),
    NRTVSH          VARCHAR(50),
    Adresa          NVARCHAR(200),
    Tel             VARCHAR(50),
    Email           VARCHAR(100),
    Banka           NVARCHAR(100),
    XheroLlogaria   VARCHAR(50),
    Logo            VARBINARY(MAX)
);
GO

IF OBJECT_ID('DYQANET_TBL') IS NULL
CREATE TABLE DYQANET_TBL (
    ID      INT             IDENTITY(1,1) PRIMARY KEY,
    DYQANI  NVARCHAR(100)   NOT NULL,
    VENDI   NVARCHAR(100),
    ADRESA  NVARCHAR(200),
    Statusi BIT             DEFAULT 1,
    Projekt BIT             DEFAULT 0
);
GO

IF OBJECT_ID('VENDI') IS NULL
CREATE TABLE VENDI (
    ID          INT             IDENTITY(1,1) PRIMARY KEY,
    Emri        NVARCHAR(100)   NOT NULL,
    CountryCode VARCHAR(5)      DEFAULT 'XK'
);
GO

IF OBJECT_ID('NjesitMatese') IS NULL
CREATE TABLE NjesitMatese (
    ID      INT             IDENTITY(1,1) PRIMARY KEY,
    Njesia  NVARCHAR(40)    NOT NULL
);
GO

IF OBJECT_ID('GrupetEMallrave') IS NULL
CREATE TABLE GrupetEMallrave (
    ID       INT             IDENTITY(1,1) PRIMARY KEY,
    Grupi    NVARCHAR(200)   NOT NULL,
    ParentID INT             NULL REFERENCES GrupetEMallrave(ID)
);
GO

IF OBJECT_ID('EmratGjenerik') IS NULL
CREATE TABLE EmratGjenerik (
    ID   INT             IDENTITY(1,1) PRIMARY KEY,
    Emri NVARCHAR(200)   NOT NULL
);
GO

IF OBJECT_ID('ArtikujtKlasifikatori') IS NULL
CREATE TABLE ArtikujtKlasifikatori (
    ID   INT             IDENTITY(1,1) PRIMARY KEY,
    Emri NVARCHAR(200)   NOT NULL
);
GO

IF OBJECT_ID('LlojeteArtikujvetbl') IS NULL
CREATE TABLE LlojeteArtikujvetbl (
    ID   INT             IDENTITY(1,1) PRIMARY KEY,
    Emri NVARCHAR(100)   NOT NULL
);
GO

IF OBJECT_ID('AgjentetShites') IS NULL
CREATE TABLE AgjentetShites (
    ID         INT             IDENTITY(1,1) PRIMARY KEY,
    Emri       NVARCHAR(100)   NOT NULL,
    Tel        VARCHAR(50),
    Komisioni  DECIMAL(5,2)    DEFAULT 0,
    Statusi    BIT             DEFAULT 1
);
GO

IF OBJECT_ID('Parneret_Tbl') IS NULL
CREATE TABLE Parneret_Tbl (
    ID      INT             IDENTITY(1,1) PRIMARY KEY,
    Emri    NVARCHAR(100)   NOT NULL,
    NRF     VARCHAR(50),
    Adresa  NVARCHAR(200),
    Tel     VARCHAR(50),
    Email   VARCHAR(100),
    Statusi BIT             DEFAULT 1
);
GO

IF OBJECT_ID('LlojetEDokumenteve_tbl') IS NULL
CREATE TABLE LlojetEDokumenteve_tbl (
    ID   INT             IDENTITY(1,1) PRIMARY KEY,
    Emri NVARCHAR(50)    NOT NULL,
    Kodi VARCHAR(20)
);
GO

IF OBJECT_ID('MenyraPageses_tbl') IS NULL
CREATE TABLE MenyraPageses_tbl (
    ID     INT             IDENTITY(1,1) PRIMARY KEY,
    Emri   NVARCHAR(50)    NOT NULL,
    IsCash BIT             DEFAULT 0
);
GO

IF OBJECT_ID('Normativa_tbl') IS NULL
CREATE TABLE Normativa_tbl (
    ID          INT             IDENTITY(1,1) PRIMARY KEY,
    TVSH        DECIMAL(5,2)    NOT NULL,
    Pershkrimi  NVARCHAR(200),
    IsDefault   BIT             DEFAULT 0
);
GO

IF OBJECT_ID('Prodhuesit_tbl') IS NULL
CREATE TABLE Prodhuesit_tbl (
    ID   INT             IDENTITY(1,1) PRIMARY KEY,
    Emri NVARCHAR(100)   NOT NULL,
    Kodi VARCHAR(20),
    Aktiv BIT            DEFAULT 1
);
GO

IF OBJECT_ID('PLU_TBL') IS NULL
CREATE TABLE PLU_TBL (
    ID     INT             IDENTITY(1,1) PRIMARY KEY,
    IDN    INT             NOT NULL,   -- FK → ARTIKUJT
    PLU    VARCHAR(20)     NOT NULL,
    Dyqani INT             NULL        -- FK → DYQANET_TBL, NULL = all stores
);
GO

IF OBJECT_ID('Printeri_tbl') IS NULL
CREATE TABLE Printeri_tbl (
    ID                INT             IDENTITY(1,1) PRIMARY KEY,
    Dyqani            INT             NOT NULL,   -- FK → DYQANET_TBL
    LlojiPrinterit    TINYINT,        -- 1=Fiskal 2=Kupon 3=Etiketa 4=A4
    EmriPrinterit     NVARCHAR(100),
    Parametrat        NVARCHAR(500)   -- JSON: port, baud, kopje, etj.
);
GO

IF OBJECT_ID('Konfigurimet') IS NULL
CREATE TABLE Konfigurimet (
    Celes   VARCHAR(100)    PRIMARY KEY,
    Vlera   NVARCHAR(500)
);
GO

IF OBJECT_ID('Operatoret') IS NULL
CREATE TABLE Operatoret (
    ID                  INT             IDENTITY(1,1) PRIMARY KEY,
    Emri_dhe_Mbiemri    NVARCHAR(100)   NOT NULL,
    shifra              NVARCHAR(100)   NOT NULL,   -- SHA-256 hash
    dyqani              INT             NULL,        -- FK → DYQANET_TBL
    Roli                TINYINT         DEFAULT 1,  -- (New) 1=Arkatare 2=Menaxher 3=Admin
    Aktiv               BIT             DEFAULT 1   -- (New)
);
GO

IF COL_LENGTH('Operatoret', 'PasiQvalido') IS NULL
    ALTER TABLE Operatoret
    ADD PasiQvalido NVARCHAR(50) NULL;
GO

-- =============================================================================
-- PRODUCT TABLES
-- =============================================================================

IF OBJECT_ID('ARTIKUJT') IS NULL
CREATE TABLE ARTIKUJT (
    IDN                         INT             IDENTITY(1,1) PRIMARY KEY,
    PERSHKRIMI_I_ARTIKULLIT     NVARCHAR(200)   NOT NULL,
    NJESIA                      INT             NULL,   -- FK → NjesitMatese
    PAKETIMI                    DECIMAL(18,3)   DEFAULT 1,
    FURNIZUESI                  INT             NULL,   -- FK → FURNITORET
    HYRJE                       DECIMAL(18,3)   DEFAULT 0,
    DALJE                       DECIMAL(18,3)   DEFAULT 0,
    [ÇMIMI_FURNIZUES]           DECIMAL(18,4)   DEFAULT 0,
    RABATI                      DECIMAL(5,2)    DEFAULT 0,
    Dogana                      DECIMAL(18,4)   DEFAULT 0,
    BAZA                        DECIMAL(18,4)   DEFAULT 0,
    MAZHA                       DECIMAL(18,4)   DEFAULT 0,
    TVSH                        DECIMAL(5,2)    DEFAULT 0,
    [ÇMIMI_I_SHITJES]           DECIMAL(18,4)   DEFAULT 0,
    STOKU_MINIMAL               INT             DEFAULT 0,
    STATUSI                     BIT             DEFAULT 1,
    LogedBy                     NVARCHAR(100),
    EntryDate                   DATETIME,
    GrupiMallit                 INT             NULL,   -- FK → GrupetEMallrave
    Shpenzimet_Varse            DECIMAL(18,4)   DEFAULT 0,
    plu                         VARCHAR(20),
    -- (New columns)
    BARKODI_KRYESOR             VARCHAR(100),
    EmriGj                      INT             NULL,   -- FK → EmratGjenerik
    Klasifikatori               INT             NULL,   -- FK → ArtikujtKlasifikatori
    LlojiArtikullit             INT             NULL,   -- FK → LlojeteArtikujvetbl
    ProdhuesID                  INT             NULL,   -- FK → Prodhuesit_tbl
    [ÇMIMI_ME_SHUMICE]          DECIMAL(18,4)   DEFAULT 0,
    CMIMI_PAKETIM               DECIMAL(18,4)   DEFAULT 0,
    Kompleti                    INT             DEFAULT 0,
    Pesha                       DECIMAL(10,3)   DEFAULT 0,
    StyleNo                     VARCHAR(100),
    ExtraRabati                 DECIMAL(5,2)    NOT NULL DEFAULT 0,
    KodPeshojes                 VARCHAR(7),
    PluPeshojes                 VARCHAR(4)
);
GO

-- Rename columns for databases created before this fix
-- (code and the production SHOP databases use EmriGj / LlojiArtikullit / Kompleti)
IF COL_LENGTH('ARTIKUJT', 'EmriGjenerik') IS NOT NULL AND COL_LENGTH('ARTIKUJT', 'EmriGj') IS NULL
    EXEC sp_rename 'ARTIKUJT.EmriGjenerik', 'EmriGj', 'COLUMN';
GO
IF COL_LENGTH('ARTIKUJT', 'LlojiArtikultit') IS NOT NULL AND COL_LENGTH('ARTIKUJT', 'LlojiArtikullit') IS NULL
    EXEC sp_rename 'ARTIKUJT.LlojiArtikultit', 'LlojiArtikullit', 'COLUMN';
GO
IF COL_LENGTH('ARTIKUJT', 'SASIA_PAKETIM') IS NOT NULL AND COL_LENGTH('ARTIKUJT', 'Kompleti') IS NULL
    EXEC sp_rename 'ARTIKUJT.SASIA_PAKETIM', 'Kompleti', 'COLUMN';
GO

IF COL_LENGTH('ARTIKUJT', 'ExtraRabati') IS NULL
    ALTER TABLE ARTIKUJT
    ADD ExtraRabati DECIMAL(5,2) NOT NULL
        CONSTRAINT DF_ARTIKUJT_ExtraRabati DEFAULT 0;
GO

IF COL_LENGTH('ARTIKUJT', 'KodPeshojes') IS NULL
    ALTER TABLE ARTIKUJT ADD KodPeshojes VARCHAR(7) NULL;
GO

IF COL_LENGTH('ARTIKUJT', 'PluPeshojes') IS NULL
    ALTER TABLE ARTIKUJT ADD PluPeshojes VARCHAR(4) NULL;
GO

IF COL_LENGTH('NjesitMatese', 'Njesia') < 80
    ALTER TABLE NjesitMatese ALTER COLUMN Njesia NVARCHAR(40) NOT NULL;
GO

IF COL_LENGTH('GrupetEMallrave', 'Grupi') < 400
    ALTER TABLE GrupetEMallrave ALTER COLUMN Grupi NVARCHAR(200) NOT NULL;
GO

IF COL_LENGTH('EmratGjenerik', 'Emri') < 400
    ALTER TABLE EmratGjenerik ALTER COLUMN Emri NVARCHAR(200) NOT NULL;
GO

IF COL_LENGTH('ArtikujtKlasifikatori', 'Emri') < 400
    ALTER TABLE ArtikujtKlasifikatori ALTER COLUMN Emri NVARCHAR(200) NOT NULL;
GO

IF COL_LENGTH('LlojeteArtikujvetbl', 'Emri') < 200
    ALTER TABLE LlojeteArtikujvetbl ALTER COLUMN Emri NVARCHAR(100) NOT NULL;
GO

IF COL_LENGTH('Normativa_tbl', 'Pershkrimi') < 400
    ALTER TABLE Normativa_tbl ALTER COLUMN Pershkrimi NVARCHAR(200) NULL;
GO

IF COL_LENGTH('ARTIKUJT', 'PERSHKRIMI_I_ARTIKULLIT') < 400
    ALTER TABLE ARTIKUJT ALTER COLUMN PERSHKRIMI_I_ARTIKULLIT NVARCHAR(200) NOT NULL;
GO

IF COL_LENGTH('ARTIKUJT', 'LogedBy') < 200
    ALTER TABLE ARTIKUJT ALTER COLUMN LogedBy NVARCHAR(100) NULL;
GO

IF NOT EXISTS (
    SELECT 1 FROM sys.indexes
    WHERE name = 'IX_ARTIKUJT_FURNIZUESI_PERSHKRIMI'
      AND object_id = OBJECT_ID('ARTIKUJT')
)
    CREATE INDEX IX_ARTIKUJT_FURNIZUESI_PERSHKRIMI
    ON ARTIKUJT(FURNIZUESI, PERSHKRIMI_I_ARTIKULLIT);
GO

IF NOT EXISTS (
    SELECT 1 FROM sys.indexes
    WHERE name = 'IX_ARTIKUJT_FURNIZUESI_BARKODI'
      AND object_id = OBJECT_ID('ARTIKUJT')
)
    CREATE INDEX IX_ARTIKUJT_FURNIZUESI_BARKODI
    ON ARTIKUJT(FURNIZUESI, BARKODI_KRYESOR);
GO

IF NOT EXISTS (
    SELECT 1 FROM sys.indexes
    WHERE name = 'IX_ARTIKUJT_BARKODI_KRYESOR'
      AND object_id = OBJECT_ID('ARTIKUJT')
)
    CREATE INDEX IX_ARTIKUJT_BARKODI_KRYESOR
    ON ARTIKUJT(BARKODI_KRYESOR)
    INCLUDE (IDN, PERSHKRIMI_I_ARTIKULLIT, FURNIZUESI, STATUSI);
GO

IF NOT EXISTS (
    SELECT 1 FROM sys.indexes
    WHERE name = 'IX_ARTIKUJT_PLU'
      AND object_id = OBJECT_ID('ARTIKUJT')
)
    CREATE INDEX IX_ARTIKUJT_PLU
    ON ARTIKUJT(plu)
    INCLUDE (IDN, PERSHKRIMI_I_ARTIKULLIT, FURNIZUESI, STATUSI);
GO

IF OBJECT_ID('BARKODAT') IS NULL
CREATE TABLE BARKODAT (
    ID          INT             IDENTITY(1,1) PRIMARY KEY,
    IDN         INT             NOT NULL,   -- FK → ARTIKUJT
    BARKODI     VARCHAR(100)    NOT NULL,
    Primar      BIT             DEFAULT 0
);
GO

-- Rename IsPrimary → Primar for databases created before this fix
IF EXISTS (
    SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_NAME = 'BARKODAT' AND COLUMN_NAME = 'IsPrimary'
) AND NOT EXISTS (
    SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_NAME = 'BARKODAT' AND COLUMN_NAME = 'Primar'
)
    EXEC sp_rename 'BARKODAT.IsPrimary', 'Primar', 'COLUMN';
GO

IF NOT EXISTS (
    SELECT 1 FROM sys.indexes
    WHERE name = 'IX_BARKODAT_IDN_PRIMAR'
      AND object_id = OBJECT_ID('BARKODAT')
)
    CREATE INDEX IX_BARKODAT_IDN_PRIMAR
    ON BARKODAT(IDN, Primar, ID)
    INCLUDE (BARKODI);
GO

IF NOT EXISTS (
    SELECT 1 FROM sys.indexes
    WHERE name = 'IX_BARKODAT_BARKODI_IDN'
      AND object_id = OBJECT_ID('BARKODAT')
)
    CREATE INDEX IX_BARKODAT_BARKODI_IDN
    ON BARKODAT(BARKODI, IDN);
GO

IF OBJECT_ID('Cmimorja_tbl') IS NULL
CREATE TABLE Cmimorja_tbl (
    ID              INT             IDENTITY(1,1) PRIMARY KEY,
    IDN             INT             NOT NULL,   -- FK → ARTIKUJT
    VITI            INT,
    NR_FATURES      INT,
    CMIMI_VJETR     DECIMAL(18,4),
    CMIMI_RI        DECIMAL(18,4),
    KOSTOJA_VJETR   DECIMAL(18,4),
    MAZHA_RI        DECIMAL(18,4),
    DATA            DATETIME,
    LogedBy         NVARCHAR(50),
    ValidFrom       DATE,
    ValidTo         DATE            NULL,
    VALIDIMI        BIT             DEFAULT 0,
    Dyqani          INT             NULL   -- FK → DYQANET_TBL, NULL = all stores
);
GO

IF COL_LENGTH('Cmimorja_tbl', 'TVSH') IS NULL
    ALTER TABLE Cmimorja_tbl ADD TVSH DECIMAL(5,2) NULL;
GO

IF NOT EXISTS (
    SELECT 1 FROM sys.indexes
    WHERE name = 'IX_Cmimorja_Dokument'
      AND object_id = OBJECT_ID('Cmimorja_tbl')
)
    CREATE INDEX IX_Cmimorja_Dokument
    ON Cmimorja_tbl(VITI, NR_FATURES, ID)
    INCLUDE (IDN, CMIMI_VJETR, CMIMI_RI, MAZHA_RI, TVSH, VALIDIMI, ValidFrom, ValidTo);
GO

IF NOT EXISTS (
    SELECT 1 FROM sys.indexes
    WHERE name = 'IX_Cmimorja_ProductHistory'
      AND object_id = OBJECT_ID('Cmimorja_tbl')
)
    CREATE INDEX IX_Cmimorja_ProductHistory
    ON Cmimorja_tbl(IDN, VALIDIMI, DATA DESC, ID DESC)
    INCLUDE (NR_FATURES, VITI, Dyqani, LogedBy, CMIMI_VJETR, CMIMI_RI, MAZHA_RI);
GO

-- =============================================================================
-- COUNTERPARTY TABLES
-- =============================================================================

IF OBJECT_ID('tbleBLERESIT') IS NULL
CREATE TABLE tbleBLERESIT (
    ID                  INT             IDENTITY(1,1) PRIMARY KEY,
    EMRI_I_KOMPANIS     NVARCHAR(100)   NOT NULL,
    PRONARI             NVARCHAR(100),
    nr_i_biznesit       VARCHAR(50),
    BANKA               NVARCHAR(100),
    XHEROLLOGARIA       VARCHAR(50),
    Adresa              NVARCHAR(200),
    TEL                 VARCHAR(50),
    Vendi               INT             NULL,   -- FK → VENDI
    Dyqani              INT             NULL,   -- FK → DYQANET_TBL
    -- Loyalty columns added in V2, kept here
    Zbritja             DECIMAL(5,2)    DEFAULT 0,
    BonusKartela        VARCHAR(50)     NULL,
    LoyaltyPoints       DECIMAL(18,2)   DEFAULT 0,
    LoyaltyTier         INT             NULL,   -- FK → LoyaltyTiers_tbl
    AgjentiID           INT             NULL,   -- FK → AgjentetShites
    -- (New) customer portal
    PortalUsername      VARCHAR(100)    NULL,
    PortalPasswordHash  VARCHAR(100)    NULL,
    Aktiv               BIT             DEFAULT 1
);
GO

IF OBJECT_ID('FURNITORET') IS NULL
CREATE TABLE FURNITORET (
    ID              INT             IDENTITY(1,1) PRIMARY KEY,
    EMRI_I_KOMPANIS NVARCHAR(200)   NOT NULL,
    PRONARI         NVARCHAR(200),
    nr_i_biznesit   VARCHAR(50),
    NRF             VARCHAR(50),
    FJTVSH          VARCHAR(50),
    NRTVSH          VARCHAR(50),
    BANKA           NVARCHAR(200),
    XHEROLLOGARIA   VARCHAR(50),
    TEL             VARCHAR(50),
    ADRESA          NVARCHAR(400),
    Vendi           INT             NULL,   -- FK → VENDI
    AP              BIT             DEFAULT 0,
    Aktiv           BIT             DEFAULT 1
);
GO

IF COL_LENGTH('FURNITORET', 'EMRI_I_KOMPANIS') < 400
    ALTER TABLE FURNITORET ALTER COLUMN EMRI_I_KOMPANIS NVARCHAR(200) NOT NULL;
GO

IF COL_LENGTH('FURNITORET', 'PRONARI') < 400
    ALTER TABLE FURNITORET ALTER COLUMN PRONARI NVARCHAR(200) NULL;
GO

IF COL_LENGTH('FURNITORET', 'BANKA') < 400
    ALTER TABLE FURNITORET ALTER COLUMN BANKA NVARCHAR(200) NULL;
GO

IF COL_LENGTH('FURNITORET', 'ADRESA') < 800
    ALTER TABLE FURNITORET ALTER COLUMN ADRESA NVARCHAR(400) NULL;
GO

IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'IX_FURNITORET_EMRI' AND object_id = OBJECT_ID('FURNITORET'))
    CREATE INDEX IX_FURNITORET_EMRI
    ON FURNITORET(EMRI_I_KOMPANIS);
GO

-- =============================================================================
-- INVENTORY TABLES
-- =============================================================================

IF OBJECT_ID('Stoku_tbl') IS NULL
CREATE TABLE Stoku_tbl (
    IDN                     INT             NOT NULL,   -- FK → ARTIKUJT
    Dyqani                  INT             NOT NULL,   -- FK → DYQANET_TBL
    stoku                   DECIMAL(18,3)   DEFAULT 0,
    [ÇMIMI_I_SHITJES]       DECIMAL(18,4)   DEFAULT 0,
    STOKU_MINIMAL           INT             DEFAULT 0,
    FURNIZUESI              INT             NULL,
    PRIMARY KEY (IDN, Dyqani)
);
GO

IF NOT EXISTS (
    SELECT 1 FROM sys.indexes
    WHERE name = 'IX_Stoku_Dyqani_Stoku'
      AND object_id = OBJECT_ID('Stoku_tbl')
)
CREATE INDEX IX_Stoku_Dyqani_Stoku
ON Stoku_tbl(Dyqani, stoku, IDN);
GO

IF OBJECT_ID('NIVELIZIMET_TBL') IS NULL
CREATE TABLE NIVELIZIMET_TBL (
    ID              INT             IDENTITY(1,1) PRIMARY KEY,
    NR_DOKUMENTIT   INT             NOT NULL,
    VITI            INT,
    DATA            DATE            NOT NULL,
    Dyqani          INT             NOT NULL,   -- FK → DYQANET_TBL
    OperatoriID     INT,                         -- FK → Operatoret
    Arsyeja         NVARCHAR(200),
    VALIDIMI        BIT             DEFAULT 0
);
GO

IF OBJECT_ID('NIVELIZIMET_DETALI') IS NULL   -- (New)
CREATE TABLE NIVELIZIMET_DETALI (
    ID              INT             IDENTITY(1,1) PRIMARY KEY,
    NivelizimetID   INT             NOT NULL,   -- FK → NIVELIZIMET_TBL
    IDN             INT             NOT NULL,   -- FK → ARTIKUJT
    SasiaeSistemit  DECIMAL(18,3)   DEFAULT 0,
    SasiaENumruar   DECIMAL(18,3)   DEFAULT 0,
    Diferenca       DECIMAL(18,3)   DEFAULT 0,
    Kostoja         DECIMAL(18,4)   DEFAULT 0
);
GO

IF OBJECT_ID('HistoriaArtikullit') IS NULL
CREATE TABLE HistoriaArtikullit (
    ID              INT             IDENTITY(1,1) PRIMARY KEY,
    IDN             INT             NOT NULL,   -- FK → ARTIKUJT
    Fusha           NVARCHAR(50),
    VleraVjetr      NVARCHAR(200),
    VleraRe         NVARCHAR(200),
    DataNdryshimit  DATETIME,
    LogedBy         NVARCHAR(50)
);
GO

IF OBJECT_ID('StokuFIFO') IS NULL   -- (New — FIFO cost layers)
CREATE TABLE StokuFIFO (
    ID                  INT             IDENTITY(1,1) PRIMARY KEY,
    IDN                 INT             NOT NULL,   -- FK → ARTIKUJT
    Dyqani              INT             NOT NULL,   -- FK → DYQANET_TBL
    HyrjaDetaliID       INT             NULL,        -- FK → HYRJA_E_MALLIT
    SasiaOrigjinale     DECIMAL(18,3),
    SasiaMbetur         DECIMAL(18,3),
    Kostoja             DECIMAL(18,5),
    AfatiSkadences      DATE            NULL,
    NrSerial            VARCHAR(50),
    DataHyrjes          DATETIME2
);
GO

-- =============================================================================
-- SALES TABLES
-- =============================================================================

IF OBJECT_ID('Paragonat') IS NULL
CREATE TABLE Paragonat (
    NR_I_FATURES        INT             NOT NULL,
    VITI                INT             NOT NULL,
    Vlera               DECIMAL(18,2)   DEFAULT 0,
    Vlerapatvsh         DECIMAL(18,2)   DEFAULT 0,
    Ora                 DATETIME,
    Dyqani              INT             NOT NULL,   -- FK → DYQANET_TBL
    BLERESI             INT             NULL,        -- FK → tbleBLERESIT
    MENYRA_E_PAGESES    INT             NULL,        -- FK → MenyraPageses_tbl
    LogedBy             NVARCHAR(50),
    -- (New columns)
    LIRIMI              DECIMAL(5,2)    DEFAULT 0,
    Cash                DECIMAL(18,2)   DEFAULT 0,
    Kusuri              DECIMAL(18,2)   DEFAULT 0,
    IsFiscal            BIT             DEFAULT 0,
    PRIMARY KEY (NR_I_FATURES, VITI, Dyqani)
);
GO

IF OBJECT_ID('KasaShitjetParkuara') IS NULL
CREATE TABLE KasaShitjetParkuara (
    ID                  UNIQUEIDENTIFIER NOT NULL PRIMARY KEY,
    Dyqani              INT              NOT NULL,
    OperatoriID         INT              NULL,
    Operatori           NVARCHAR(100)    NOT NULL DEFAULT '',
    BleresiID           INT              NULL,
    BleresiEmri         NVARCHAR(200)    NULL,
    Data                DATE             NOT NULL,
    Lirimi              DECIMAL(5,2)     NOT NULL DEFAULT 0,
    KrijuarMe           DATETIME2        NOT NULL DEFAULT SYSDATETIME()
);
GO

IF OBJECT_ID('KasaShitjetParkuaraRreshtat') IS NULL
CREATE TABLE KasaShitjetParkuaraRreshtat (
    ID                  BIGINT           IDENTITY(1,1) PRIMARY KEY,
    ShportaID           UNIQUEIDENTIFIER NOT NULL,
    Nr                  INT              NOT NULL,
    IDN                 INT              NOT NULL,
    Barkodi             VARCHAR(100)     NULL,
    Emertimi            NVARCHAR(250)    NULL,
    Njesia              INT              NULL,
    NjesiaEmri          NVARCHAR(50)     NULL,
    GrupiMallit         INT              NULL,
    Sasia               DECIMAL(18,3)    NOT NULL,
    Cmimi               DECIMAL(18,4)    NOT NULL,
    CmimiShitjes        DECIMAL(18,4)    NOT NULL DEFAULT 0,
    CmimiShumice        DECIMAL(18,4)    NOT NULL DEFAULT 0,
    Kompleti            INT              NOT NULL DEFAULT 0,
    Rabati              DECIMAL(5,2)     NOT NULL DEFAULT 0,
    ExtraRabati         DECIMAL(5,2)     NOT NULL DEFAULT 0,
    TVSH                DECIMAL(5,2)     NOT NULL DEFAULT 0,
    BAZA                DECIMAL(18,4)    NOT NULL DEFAULT 0,
    StokuPara           DECIMAL(18,3)    NOT NULL DEFAULT 0,
    CONSTRAINT FK_KasaShitjetParkuaraRreshtat_Shporta
        FOREIGN KEY (ShportaID) REFERENCES KasaShitjetParkuara(ID) ON DELETE CASCADE
);
GO

IF NOT EXISTS (
    SELECT 1 FROM sys.indexes
    WHERE name = 'IX_KasaShitjetParkuara_Dyqani_KrijuarMe'
      AND object_id = OBJECT_ID('KasaShitjetParkuara')
)
CREATE INDEX IX_KasaShitjetParkuara_Dyqani_KrijuarMe
ON KasaShitjetParkuara(Dyqani, KrijuarMe DESC);
GO

IF NOT EXISTS (
    SELECT 1 FROM sys.indexes
    WHERE name = 'IX_KasaShitjetParkuaraRreshtat_ShportaID'
      AND object_id = OBJECT_ID('KasaShitjetParkuaraRreshtat')
)
CREATE INDEX IX_KasaShitjetParkuaraRreshtat_ShportaID
ON KasaShitjetParkuaraRreshtat(ShportaID, Nr);
GO

IF OBJECT_ID('DALJA_E_MALLIT') IS NULL
CREATE TABLE DALJA_E_MALLIT (
    ID                  INT             IDENTITY(1,1) PRIMARY KEY,
    IDN                 INT             NOT NULL,   -- FK → ARTIKUJT
    Bar                 VARCHAR(100),
    EMERTIMI            NVARCHAR(100),
    NR_I_FATURES        INT,
    VITI                INT,
    DATA                DATE,
    Dyqani              INT,                         -- FK → DYQANET_TBL
    Njesia              INT,                         -- FK → NjesitMatese
    SASIA               DECIMAL(18,3)   DEFAULT 0,
    BAZA                DECIMAL(18,4)   DEFAULT 0,
    TVSH                DECIMAL(5,2)    DEFAULT 0,
    Rabati              DECIMAL(5,2)    DEFAULT 0,
    ExtraRabati         DECIMAL(5,2)    DEFAULT 0,
    Qmimiparabat        DECIMAL(18,4)   DEFAULT 0,
    [ÇMIMI]             DECIMAL(18,4)   DEFAULT 0,
    LIRIMI              DECIMAL(5,2)    DEFAULT 0,
    MENYRA_E_PAGESES    INT,
    STORNO              BIT             DEFAULT 0,
    LogedBy             NVARCHAR(50),
    EntryDate           DATETIME,
    BLERESI             INT             NULL,
    VALIDIMI            BIT             DEFAULT 0,
    stoku               DECIMAL(18,3)   DEFAULT 0,
    Cash                DECIMAL(18,2)   DEFAULT 0,
    GrupiMallit         INT             NULL,
    LlojiDokumentit     INT             NULL,
    DeriteParagoni      BIT             DEFAULT 1,
    ID_local            INT             NULL,
    AgjentiID           INT             NULL,
    StokuFIFO_ID        INT             NULL        -- (New) FK → StokuFIFO
);
GO

IF OBJECT_ID('DALJA_E_MALLIT_Komenti') IS NULL
CREATE TABLE DALJA_E_MALLIT_Komenti (
    ID              INT             IDENTITY(1,1) PRIMARY KEY,
    NR_I_FATURES    INT,
    VITI            INT,
    Dyqani          INT,
    Komenti         NVARCHAR(500)
);
GO

IF NOT EXISTS (
    SELECT 1 FROM sys.indexes
    WHERE name = 'IX_DALJA_SalesByPayment'
      AND object_id = OBJECT_ID('DALJA_E_MALLIT')
)
CREATE INDEX IX_DALJA_SalesByPayment
ON DALJA_E_MALLIT(VALIDIMI, LlojiDokumentit, DATA, Dyqani)
INCLUDE (NR_I_FATURES, MENYRA_E_PAGESES, SASIA, [ÇMIMI], LogedBy, IDN);
GO

IF NOT EXISTS (
    SELECT 1 FROM sys.indexes
    WHERE name = 'IX_DALJA_DailySalesPage'
      AND object_id = OBJECT_ID('DALJA_E_MALLIT')
)
CREATE INDEX IX_DALJA_DailySalesPage
ON DALJA_E_MALLIT(LogedBy, VALIDIMI, LlojiDokumentit, DATA, EntryDate, NR_I_FATURES, ID)
INCLUDE (Dyqani, IDN);
GO

IF NOT EXISTS (
    SELECT 1 FROM sys.indexes
    WHERE name = 'IX_DALJA_DatedStock'
      AND object_id = OBJECT_ID('DALJA_E_MALLIT')
)
CREATE INDEX IX_DALJA_DatedStock
ON DALJA_E_MALLIT(VALIDIMI, DATA, Dyqani, IDN)
INCLUDE (SASIA);
GO

IF COL_LENGTH('DALJA_E_MALLIT_Komenti', 'LlojiDokumentit') IS NULL
    ALTER TABLE DALJA_E_MALLIT_Komenti ADD LlojiDokumentit INT NULL;
GO

IF OBJECT_ID('Eksporti') IS NULL
CREATE TABLE Eksporti (
    ID              INT             IDENTITY(1,1) PRIMARY KEY,
    IDN             INT             NOT NULL,
    NR_I_FATURES    INT,
    VITI            INT,
    DATA            DATE,
    Dyqani          INT,
    SASIA           DECIMAL(18,3)   DEFAULT 0,
    [ÇMIMI]         DECIMAL(18,4)   DEFAULT 0,
    TVSH            DECIMAL(5,2)    DEFAULT 0,
    BLERESI         INT             NULL,
    VALIDIMI        BIT             DEFAULT 0,
    LogedBy         NVARCHAR(50),
    EntryDate       DATETIME
);
GO

IF OBJECT_ID('DaljeInterne') IS NULL
CREATE TABLE DaljeInterne (
    ID              INT             IDENTITY(1,1) PRIMARY KEY,
    NR_DOKUMENTIT   INT,
    VITI            INT,
    DATA            DATE,
    IDN             INT             NOT NULL,
    Dyqani          INT,
    SASIA           DECIMAL(18,3)   DEFAULT 0,
    Arsyeja         NVARCHAR(200),
    OperatoriID     INT,
    VALIDIMI        BIT             DEFAULT 0,
    LogedBy         NVARCHAR(50)
);
GO

IF COL_LENGTH('dbo.DaljeInterne', 'Kosto') IS NULL
    ALTER TABLE dbo.DaljeInterne ADD Kosto DECIMAL(18,4) NOT NULL
        CONSTRAINT DF_DaljeInterne_Kosto DEFAULT 0;
GO

IF COL_LENGTH('dbo.DaljeInterne', 'TVSH') IS NULL
    ALTER TABLE dbo.DaljeInterne ADD TVSH DECIMAL(5,2) NOT NULL
        CONSTRAINT DF_DaljeInterne_TVSH DEFAULT 0;
GO

IF COL_LENGTH('dbo.DaljeInterne', 'QmimiShitjes') IS NULL
    ALTER TABLE dbo.DaljeInterne ADD QmimiShitjes DECIMAL(18,4) NOT NULL
        CONSTRAINT DF_DaljeInterne_QmimiShitjes DEFAULT 0;
GO

IF COL_LENGTH('dbo.DaljeInterne', 'LlojiDokumentit') IS NULL
    ALTER TABLE dbo.DaljeInterne ADD LlojiDokumentit INT NULL;
GO

IF COL_LENGTH('dbo.DaljeInterne', 'DataRegjistrimit') IS NULL
    ALTER TABLE dbo.DaljeInterne ADD DataRegjistrimit DATETIME NULL;
GO

IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'IX_DALJE_INTERNE_DOC' AND object_id = OBJECT_ID('DaljeInterne'))
    CREATE INDEX IX_DALJE_INTERNE_DOC
    ON DaljeInterne(VITI, NR_DOKUMENTIT, ID)
    INCLUDE (DATA, Dyqani, IDN, SASIA, VALIDIMI);
GO

-- =============================================================================
-- PURCHASE TABLES
-- =============================================================================

IF OBJECT_ID('HYRJA_E_MALLIT') IS NULL
CREATE TABLE HYRJA_E_MALLIT (
    ID                  INT             IDENTITY(1,1) PRIMARY KEY,
    IDN                 INT             NOT NULL,
    Bar                 VARCHAR(100),
    Emertimi            NVARCHAR(100),
    FURNITORI           INT,
    NR_I_HYRJES         INT,
    VITI                INT,
    DATA                DATE,
    Dyqani              INT,
    PrejDyqanit         INT             NULL,
    Njesia              INT,
    SASIA               DECIMAL(18,3)   DEFAULT 0,
    QmimiDollar         DECIMAL(18,5)   DEFAULT 0,
    Qmimi_Baze          DECIMAL(18,5)   DEFAULT 0,
    RABATI              DECIMAL(5,2)    DEFAULT 0,
    Transporti          DECIMAL(18,2)   DEFAULT 0,
    Korrekcion          DECIMAL(18,2)   DEFAULT 0,
    Dogana              DECIMAL(18,4)   DEFAULT 0,
    ShpenzimetVarse     DECIMAL(18,5)   DEFAULT 0,
    Kostoja             DECIMAL(18,5)   DEFAULT 0,
    MAZHA               DECIMAL(18,4)   DEFAULT 0,
    TVSH                DECIMAL(5,2)    DEFAULT 0,
    [ÇMIMI_I_SHITJES]   DECIMAL(18,4)   DEFAULT 0,
    VALIDIMI            BIT             DEFAULT 0,
    LogedBy             NVARCHAR(50),
    EntryDate           DATETIME,
    Import              BIT             DEFAULT 0,
    NR_Dudit            VARCHAR(50),
    NR_I_FATURES        VARCHAR(50),
    VleraFatures        DECIMAL(18,2)   DEFAULT 0,
    KursiValutor        DECIMAL(10,5)   DEFAULT 1,
    Regjistrimi         BIT             DEFAULT 0,
    LlojiDokumentit     INT             NULL,
    Shpenzimet          DECIMAL(18,2)   DEFAULT 0,
    AfatiSkadences      DATE            NULL,       -- (New)
    NrSerial            VARCHAR(50),               -- (New)
    StokuFIFO_ID        INT             NULL        -- (New) FK → StokuFIFO
);
GO

IF COL_LENGTH('dbo.HYRJA_E_MALLIT', 'PrejDyqanit') IS NULL
    ALTER TABLE dbo.HYRJA_E_MALLIT ADD PrejDyqanit INT NULL;
GO

IF COL_LENGTH('dbo.HYRJA_E_MALLIT', 'PranuarNgaDyqani') IS NULL
    ALTER TABLE dbo.HYRJA_E_MALLIT ADD PranuarNgaDyqani BIT NOT NULL
        CONSTRAINT DF_HYRJA_E_MALLIT_PranuarNgaDyqani DEFAULT 0;
GO

IF COL_LENGTH('HYRJA_E_MALLIT', 'ExtraRabati') IS NULL
    ALTER TABLE HYRJA_E_MALLIT
    ADD ExtraRabati DECIMAL(5,2) NOT NULL
        CONSTRAINT DF_HYRJA_E_MALLIT_ExtraRabati DEFAULT 0;
GO

IF COL_LENGTH('HYRJA_E_MALLIT', 'Komenti') IS NULL
    ALTER TABLE HYRJA_E_MALLIT
    ADD Komenti NVARCHAR(1000) NULL;
GO

IF COL_LENGTH('HYRJA_E_MALLIT', 'HyrjePerjashtuar') IS NULL
    ALTER TABLE HYRJA_E_MALLIT
    ADD HyrjePerjashtuar BIT NOT NULL
        CONSTRAINT DF_HYRJA_E_MALLIT_HyrjePerjashtuar DEFAULT 0;
GO

IF COL_LENGTH('HYRJA_E_MALLIT', 'AfatiSkadences') IS NULL
    ALTER TABLE HYRJA_E_MALLIT
    ADD AfatiSkadences DATE NULL;
GO

IF COL_LENGTH('HYRJA_E_MALLIT', 'NrSerial') IS NULL
    ALTER TABLE HYRJA_E_MALLIT
    ADD NrSerial VARCHAR(50) NULL;
GO

IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'IX_HYRJA_PICKER' AND object_id = OBJECT_ID('HYRJA_E_MALLIT'))
    CREATE INDEX IX_HYRJA_PICKER
    ON HYRJA_E_MALLIT(VITI, NR_I_HYRJES, FURNITORI)
    INCLUDE (DATA, NR_I_FATURES, VALIDIMI, LlojiDokumentit);
GO

IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'IX_HYRJA_DOC_LOAD' AND object_id = OBJECT_ID('HYRJA_E_MALLIT'))
    CREATE INDEX IX_HYRJA_DOC_LOAD
    ON HYRJA_E_MALLIT(VITI, NR_I_HYRJES, LlojiDokumentit, ID);
GO

IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'IX_HYRJA_SUPPLIER_INVOICE' AND object_id = OBJECT_ID('HYRJA_E_MALLIT'))
    CREATE INDEX IX_HYRJA_SUPPLIER_INVOICE
    ON HYRJA_E_MALLIT(FURNITORI, VITI, NR_I_FATURES, NR_I_HYRJES)
    INCLUDE (LlojiDokumentit, VALIDIMI);
GO

IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'IX_HYRJA_DATED_STOCK' AND object_id = OBJECT_ID('HYRJA_E_MALLIT'))
    CREATE INDEX IX_HYRJA_DATED_STOCK
    ON HYRJA_E_MALLIT(VALIDIMI, DATA, IDN)
    INCLUDE (SASIA, Dyqani, LlojiDokumentit, PrejDyqanit);
GO

IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'IX_HYRJA_TRANSFER_DOC' AND object_id = OBJECT_ID('HYRJA_E_MALLIT'))
    CREATE INDEX IX_HYRJA_TRANSFER_DOC
    ON HYRJA_E_MALLIT(LlojiDokumentit, VITI, NR_I_HYRJES, PrejDyqanit, ID)
    INCLUDE (Dyqani, DATA, SASIA, VALIDIMI, PranuarNgaDyqani);
GO

IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'IX_HYRJA_EXPIRY_FIFO' AND object_id = OBJECT_ID('HYRJA_E_MALLIT'))
    CREATE INDEX IX_HYRJA_EXPIRY_FIFO
    ON HYRJA_E_MALLIT(IDN, ID DESC)
    INCLUDE (SASIA, AfatiSkadences);
GO

IF OBJECT_ID('Hyrja_Mallit_LOCAL') IS NULL
CREATE TABLE Hyrja_Mallit_LOCAL (
    ID                  INT             IDENTITY(1,1) PRIMARY KEY,
    IDN                 INT             NOT NULL,
    Bar                 VARCHAR(100),
    Emertimi            NVARCHAR(100),
    FURNITORI           INT,
    NR_I_HYRJES         INT,
    VITI                INT,
    DATA                DATE,
    Dyqani              INT,
    Njesia              INT,
    Stoku               DECIMAL(18,3)   DEFAULT 0,
    SASIAREGJISTRIM     DECIMAL(18,3)   DEFAULT 0,
    SASIA               DECIMAL(18,3)   DEFAULT 0,
    Qmimi_Baze          DECIMAL(18,5)   DEFAULT 0,
    RABATI              DECIMAL(5,2)    DEFAULT 0,
    Transporti          DECIMAL(18,2)   DEFAULT 0,
    Korrekcion          DECIMAL(18,2)   DEFAULT 0,
    Dogana              DECIMAL(18,4)   DEFAULT 0,
    ShpenzimetVarse     DECIMAL(18,5)   DEFAULT 0,
    Kostoja             DECIMAL(18,5)   DEFAULT 0,
    MAZHA               DECIMAL(18,4)   DEFAULT 0,
    TVSH                DECIMAL(5,2)    DEFAULT 0,
    [ÇMIMI_I_SHITJES]   DECIMAL(18,4)   DEFAULT 0,
    VALIDIMI            BIT             DEFAULT 0,
    LogedBy             NVARCHAR(50),
    EntryDate           DATETIME,
    LlojiDokumentit     INT             NULL
);
GO

IF COL_LENGTH('dbo.Hyrja_Mallit_LOCAL', 'Stoku') IS NULL
    ALTER TABLE dbo.Hyrja_Mallit_LOCAL ADD Stoku DECIMAL(18,3) NOT NULL
        CONSTRAINT DF_Hyrja_Mallit_LOCAL_Stoku DEFAULT 0;
GO

IF COL_LENGTH('dbo.Hyrja_Mallit_LOCAL', 'SASIAREGJISTRIM') IS NULL
    ALTER TABLE dbo.Hyrja_Mallit_LOCAL ADD SASIAREGJISTRIM DECIMAL(18,3) NOT NULL
        CONSTRAINT DF_Hyrja_Mallit_LOCAL_SASIAREGJISTRIM DEFAULT 0;
GO

IF COL_LENGTH('dbo.Hyrja_Mallit_LOCAL', 'Regjistrimi') IS NULL
    ALTER TABLE dbo.Hyrja_Mallit_LOCAL ADD Regjistrimi BIT NOT NULL
        CONSTRAINT DF_Hyrja_Mallit_LOCAL_Regjistrimi DEFAULT 0;
GO

IF OBJECT_ID('Hyrja_per_Njesi') IS NULL
CREATE TABLE Hyrja_per_Njesi (
    ID              INT             IDENTITY(1,1) PRIMARY KEY,
    IDN             INT             NOT NULL,
    NJESIA          INT,
    SASIA           DECIMAL(18,3)   DEFAULT 0,
    NR_I_HYRJES     INT,
    VITI            INT,
    DATA            DATE,
    Dyqani          INT,
    LogedBy         NVARCHAR(50)
);
GO

-- =============================================================================
-- TRANSFERS & INTERNAL MOVEMENTS
-- =============================================================================

IF OBJECT_ID('BartjeInterne') IS NULL
CREATE TABLE BartjeInterne (
    ID              INT             IDENTITY(1,1) PRIMARY KEY,
    NR_DOKUMENTIT   INT             NOT NULL,
    VITI            INT,
    DATA            DATE,
    DyqaniNga       INT,   -- FK → DYQANET_TBL
    DyqaniDeri      INT,   -- FK → DYQANET_TBL
    OperatoriID     INT,
    VALIDIMI        BIT             DEFAULT 0,
    KerkonAprovim   BIT             DEFAULT 0,
    Komenti         NVARCHAR(500)
);
GO

IF OBJECT_ID('BartjeInterne_Detali') IS NULL   -- (New)
CREATE TABLE BartjeInterne_Detali (
    ID          INT             IDENTITY(1,1) PRIMARY KEY,
    BartjaID    INT             NOT NULL,
    IDN         INT             NOT NULL,
    SASIA       DECIMAL(18,3)   DEFAULT 0,
    Kostoja     DECIMAL(18,4)   DEFAULT 0,
    [ÇMIMI_I_SHITJES] DECIMAL(18,4) DEFAULT 0,
    TVSH        DECIMAL(5,2)    DEFAULT 0
);
GO

-- =============================================================================
-- QUOTATIONS & ORDERS
-- =============================================================================

IF OBJECT_ID('OfertaMallit') IS NULL
CREATE TABLE OfertaMallit (
    ID              INT             IDENTITY(1,1) PRIMARY KEY,
    NR_OFERTËS      INT             NOT NULL,
    VITI            INT,
    DATA            DATE,
    DataSkadimit    DATE,
    BLERESI         INT,   -- FK → tbleBLERESIT
    Dyqani          INT,   -- FK → DYQANET_TBL
    OperatoriID     INT,
    LIRIMI          DECIMAL(5,2)    DEFAULT 0,
    TotaliGruto     DECIMAL(18,2)   DEFAULT 0,
    Statusi         TINYINT         DEFAULT 0,
    Komenti         NVARCHAR(1000)
);
GO

IF OBJECT_ID('Oferta_Fletedergesa') IS NULL
CREATE TABLE Oferta_Fletedergesa (
    ID          INT             IDENTITY(1,1) PRIMARY KEY,
    OfertaID    INT             NOT NULL,   -- FK → OfertaMallit
    IDN         INT             NOT NULL,
    SASIA       DECIMAL(18,3)   DEFAULT 0,
    [ÇMIMI]     DECIMAL(18,4)   DEFAULT 0,
    LIRIMI      DECIMAL(5,2)    DEFAULT 0,
    TVSH        DECIMAL(5,2)    DEFAULT 0,
    Totali      DECIMAL(18,2)   DEFAULT 0
);
GO

IF OBJECT_ID('Porosit') IS NULL
CREATE TABLE Porosit (
    ID              INT             IDENTITY(1,1) PRIMARY KEY,
    NR_POROSISE     INT             NOT NULL,
    VITI            INT,
    DATA            DATE,
    FURNITORI       INT,   -- FK → FURNITORET
    Dyqani          INT,
    OperatoriID     INT,
    TotaliGruto     DECIMAL(18,2)   DEFAULT 0,
    Statusi         TINYINT         DEFAULT 0,
    Komenti         NVARCHAR(500)
);
GO

IF OBJECT_ID('Porosit_Detali') IS NULL   -- (New)
CREATE TABLE Porosit_Detali (
    ID          INT             IDENTITY(1,1) PRIMARY KEY,
    PorosiID    INT             NOT NULL,
    IDN         INT             NOT NULL,
    SASIA       DECIMAL(18,3)   DEFAULT 0,
    [ÇMIMI]     DECIMAL(18,4)   DEFAULT 0,
    SasiaPranuar DECIMAL(18,3)  DEFAULT 0
);
GO

-- =============================================================================
-- FINANCIAL TABLES
-- =============================================================================

IF OBJECT_ID('KARTELA_E_BLERESIT') IS NULL
CREATE TABLE KARTELA_E_BLERESIT (
    ID              INT             IDENTITY(1,1) PRIMARY KEY,
    IDBLERESI       INT             NOT NULL,
    DATA            DATE            NOT NULL,
    NR_FATURES      INT,
    VITI            INT,
    KREDI           DECIMAL(18,2)   DEFAULT 0,
    DEBI            DECIMAL(18,2)   DEFAULT 0,
    DYQANI          INT,
    LlojiDokumentit INT             NULL,
    AgjentiID       INT             NULL,
    Cash            DECIMAL(18,2)   DEFAULT 0,
    Eksporti        BIT             DEFAULT 0,
    Komenti         NVARCHAR(200)
);
GO

IF OBJECT_ID('KARTELA_E_BLERESIT_Histori') IS NULL
CREATE TABLE KARTELA_E_BLERESIT_Histori (
    ID              INT             IDENTITY(1,1) PRIMARY KEY,
    IDBLERESI       INT,
    DATA            DATE,
    NR_FATURES      INT,
    VITI            INT,
    KREDI           DECIMAL(18,2),
    DEBI            DECIMAL(18,2),
    DYQANI          INT,
    LlojiDokumentit INT,
    DataNdryshimit  DATETIME,
    Veprimi         VARCHAR(10)
);
GO

IF OBJECT_ID('KARTELA_E_FURNITORIT') IS NULL
CREATE TABLE KARTELA_E_FURNITORIT (
    ID              INT             IDENTITY(1,1) PRIMARY KEY,
    IDFURNITOR      INT             NOT NULL,
    DATA            DATE            NOT NULL,
    NR_FATURES      INT,
    VITI            INT,
    KREDI           DECIMAL(18,2)   DEFAULT 0,
    DEBI            DECIMAL(18,2)   DEFAULT 0,
    DYQANI          INT,
    LlojiDokumentit INT             NULL,
    cash            DECIMAL(18,2)   DEFAULT 0
);
GO

IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'IX_KARTELA_FURNITORIT_DOC' AND object_id = OBJECT_ID('KARTELA_E_FURNITORIT'))
    CREATE INDEX IX_KARTELA_FURNITORIT_DOC
    ON KARTELA_E_FURNITORIT(VITI, NR_FATURES, LlojiDokumentit, DYQANI)
    INCLUDE (IDFURNITOR);
GO

IF OBJECT_ID('KARTELA_E_FURNITORIT_Histori') IS NULL
CREATE TABLE KARTELA_E_FURNITORIT_Histori (
    ID              INT             IDENTITY(1,1) PRIMARY KEY,
    IDFURNITOR      INT,
    DATA            DATE,
    NR_FATURES      INT,
    VITI            INT,
    KREDI           DECIMAL(18,2),
    DEBI            DECIMAL(18,2),
    DYQANI          INT,
    DataNdryshimit  DATETIME,
    Veprimi         VARCHAR(10)
);
GO

IF OBJECT_ID('KARTELA_E_TVSHS') IS NULL
CREATE TABLE KARTELA_E_TVSHS (
    ID              INT             IDENTITY(1,1) PRIMARY KEY,
    Periudha        VARCHAR(7),
    TVSH            DECIMAL(5,2),
    BazaTatimore    DECIMAL(18,2)   DEFAULT 0,
    VleraTVSH       DECIMAL(18,2)   DEFAULT 0,
    Drejtimi        TINYINT,        -- 1=Input(blerje) 2=Output(shitje)
    NrFatures       INT,
    LlojiDoc        TINYINT,
    VITI            INT,
    DYQANI          INT
);
GO

IF OBJECT_ID('KARTELA_E_TVSHS_Histori') IS NULL
CREATE TABLE KARTELA_E_TVSHS_Histori (
    ID              INT             IDENTITY(1,1) PRIMARY KEY,
    Periudha        VARCHAR(7),
    TVSH            DECIMAL(5,2),
    BazaTatimore    DECIMAL(18,2),
    VleraTVSH       DECIMAL(18,2),
    NrFatures       INT,
    DYQANI          INT,
    DataNdryshimit  DATETIME,
    Veprimi         VARCHAR(10)
);
GO

IF OBJECT_ID('Shpenzimet_Llojet') IS NULL
CREATE TABLE Shpenzimet_Llojet (
    ID       INT             IDENTITY(1,1) PRIMARY KEY,
    Emri     NVARCHAR(100)   NOT NULL,
    ParentID INT             NULL
);
GO

IF OBJECT_ID('Shpenzimettbl') IS NULL
CREATE TABLE Shpenzimettbl (
    ID                  INT             IDENTITY(1,1) PRIMARY KEY,
    LlojiID             INT,
    Vlera               DECIMAL(18,2)   DEFAULT 0,
    DATA                DATE,
    Pershkrimi          NVARCHAR(200),
    FURNITORI           INT             NULL,
    MENYRA_E_PAGESES    INT             NULL,
    DYQANI              INT,
    OperatoriID         INT,
    TVSH                DECIMAL(5,2)    DEFAULT 0,
    Investim            BIT             NOT NULL DEFAULT 0
);
GO

IF COL_LENGTH('dbo.Shpenzimettbl', 'Investim') IS NULL
    ALTER TABLE dbo.Shpenzimettbl
        ADD Investim BIT NOT NULL CONSTRAINT DF_Shpenzimettbl_Investim DEFAULT(0) WITH VALUES;
GO

IF COL_LENGTH('dbo.Shpenzimettbl', 'NR_SHpen') IS NULL
    ALTER TABLE dbo.Shpenzimettbl ADD NR_SHpen INT NULL;
GO

IF COL_LENGTH('dbo.Shpenzimettbl', 'NrFatures') IS NULL
    ALTER TABLE dbo.Shpenzimettbl ADD NrFatures NVARCHAR(50) NULL;
GO

IF COL_LENGTH('dbo.Shpenzimettbl', 'EmriBiznesit') IS NULL
    ALTER TABLE dbo.Shpenzimettbl ADD EmriBiznesit NVARCHAR(200) NULL;
GO

IF COL_LENGTH('dbo.Shpenzimettbl', 'PartneriID') IS NULL
    ALTER TABLE dbo.Shpenzimettbl ADD PartneriID INT NULL;
GO

IF COL_LENGTH('dbo.Shpenzimettbl', 'LlojiShpenzimitID') IS NULL
    ALTER TABLE dbo.Shpenzimettbl ADD LlojiShpenzimitID INT NULL;
GO

IF COL_LENGTH('dbo.Shpenzimettbl', 'Komenti') IS NULL
    ALTER TABLE dbo.Shpenzimettbl ADD Komenti NVARCHAR(400) NULL;
GO

IF COL_LENGTH('dbo.Shpenzimettbl', 'BlerjaPaTVSH') IS NULL
    ALTER TABLE dbo.Shpenzimettbl ADD BlerjaPaTVSH DECIMAL(18,2) NULL;
GO

IF COL_LENGTH('dbo.Shpenzimettbl', 'BlerjaMeTVSH') IS NULL
    ALTER TABLE dbo.Shpenzimettbl ADD BlerjaMeTVSH DECIMAL(18,2) NULL;
GO

IF COL_LENGTH('dbo.Shpenzimettbl', 'Cash') IS NULL
    ALTER TABLE dbo.Shpenzimettbl ADD Cash INT NULL;
GO

IF COL_LENGTH('dbo.Shpenzimettbl', 'DataPagese') IS NULL
    ALTER TABLE dbo.Shpenzimettbl ADD DataPagese DATE NULL;
GO

IF COL_LENGTH('dbo.Shpenzimet_Llojet', 'DYQANI') IS NULL
    ALTER TABLE dbo.Shpenzimet_Llojet ADD DYQANI INT NULL;
GO

UPDATE s SET
    NR_SHpen = ISNULL(s.NR_SHpen,s.ID),
    PartneriID = ISNULL(s.PartneriID,s.FURNITORI),
    LlojiShpenzimitID = ISNULL(s.LlojiShpenzimitID,s.LlojiID),
    EmriBiznesit = ISNULL(NULLIF(s.EmriBiznesit,''),p.Emri),
    BlerjaPaTVSH = ISNULL(s.BlerjaPaTVSH,CASE WHEN ISNULL(s.TVSH,0)=0 THEN ISNULL(s.Vlera,0) ELSE 0 END),
    BlerjaMeTVSH = ISNULL(s.BlerjaMeTVSH,CASE WHEN ISNULL(s.TVSH,0)>0 THEN ISNULL(s.Vlera,0) ELSE 0 END),
    Cash = ISNULL(s.Cash,s.MENYRA_E_PAGESES),
    DataPagese = ISNULL(s.DataPagese,s.DATA)
FROM dbo.Shpenzimettbl s
LEFT JOIN dbo.Parneret_Tbl p ON p.ID=s.FURNITORI
WHERE s.NR_SHpen IS NULL OR s.PartneriID IS NULL OR s.LlojiShpenzimitID IS NULL
   OR s.EmriBiznesit IS NULL OR s.BlerjaPaTVSH IS NULL OR s.BlerjaMeTVSH IS NULL
   OR s.Cash IS NULL OR s.DataPagese IS NULL;
GO

IF OBJECT_ID('KARTELA_E_SHPENZIMEVE') IS NULL
CREATE TABLE KARTELA_E_SHPENZIMEVE (
    ID              INT             IDENTITY(1,1) PRIMARY KEY,
    ShpenzimetID    INT,
    DATA            DATE            NOT NULL,
    Vlera           DECIMAL(18,2)   DEFAULT 0,
    DYQANI          INT
);
GO

UPDATE k SET
    k.DATA = ISNULL(s.DATA,k.DATA),
    k.Vlera = ISNULL(s.Vlera,0),
    k.DYQANI = s.DYQANI
FROM dbo.KARTELA_E_SHPENZIMEVE k
INNER JOIN dbo.Shpenzimettbl s ON s.ID=k.ShpenzimetID
WHERE k.DATA<>s.DATA OR ISNULL(k.Vlera,0)<>ISNULL(s.Vlera,0)
   OR ISNULL(k.DYQANI,0)<>ISNULL(s.DYQANI,0);

INSERT INTO dbo.KARTELA_E_SHPENZIMEVE(ShpenzimetID,DATA,Vlera,DYQANI)
SELECT s.ID,ISNULL(s.DATA,CONVERT(date,GETDATE())),ISNULL(s.Vlera,0),s.DYQANI
FROM dbo.Shpenzimettbl s
WHERE NOT EXISTS (
    SELECT 1 FROM dbo.KARTELA_E_SHPENZIMEVE k WHERE k.ShpenzimetID=s.ID
);
GO

IF OBJECT_ID('KARTELA_E_SHPENZIMEVE_Histori') IS NULL
CREATE TABLE KARTELA_E_SHPENZIMEVE_Histori (
    ID              INT             IDENTITY(1,1) PRIMARY KEY,
    ShpenzimetID    INT,
    DATA            DATE,
    Vlera           DECIMAL(18,2),
    DYQANI          INT,
    DataNdryshimit  DATETIME,
    Veprimi         VARCHAR(10)
);
GO

IF OBJECT_ID('Kartelat_TeNdryshme') IS NULL
CREATE TABLE Kartelat_TeNdryshme (
    ID          INT             IDENTITY(1,1) PRIMARY KEY,
    PartnerID   INT             NULL,
    DATA        DATE            NOT NULL,
    KREDI       DECIMAL(18,2)   DEFAULT 0,
    DEBI        DECIMAL(18,2)   DEFAULT 0,
    Pershkrimi  NVARCHAR(200),
    LlojiDoc    TINYINT,
    DYQANI      INT,
    Cash        DECIMAL(18,2)   DEFAULT 0
);
GO

IF COL_LENGTH('dbo.Kartelat_TeNdryshme', 'Cash') IS NULL
    ALTER TABLE dbo.Kartelat_TeNdryshme
        ADD Cash DECIMAL(18,2) NOT NULL CONSTRAINT DF_Kartelat_TeNdryshme_Cash DEFAULT(0) WITH VALUES;
GO

IF OBJECT_ID('Kartelat_TeNdryshme_Histori') IS NULL
CREATE TABLE Kartelat_TeNdryshme_Histori (
    ID              INT             IDENTITY(1,1) PRIMARY KEY,
    PartnerID       INT,
    DATA            DATE,
    KREDI           DECIMAL(18,2),
    DEBI            DECIMAL(18,2),
    DYQANI          INT,
    DataNdryshimit  DATETIME,
    Veprimi         VARCHAR(10)
);
GO

IF OBJECT_ID('Arka_tbl') IS NULL
CREATE TABLE Arka_tbl (
    ID          INT             IDENTITY(1,1) PRIMARY KEY,
    DYQANI      INT             NOT NULL,
    DATA        DATE            NOT NULL,
    Vlera       DECIMAL(18,2)   DEFAULT 0,
    Drejtimi    TINYINT,        -- 1=Hyrje 2=Dalje
    LlojiDoc    TINYINT,
    NR_FATURES  INT             NULL,
    Pershkrimi  NVARCHAR(200),
    OperatoriID INT
);
GO

IF OBJECT_ID('TVSH') IS NULL
CREATE TABLE TVSH (
    ID              INT             IDENTITY(1,1) PRIMARY KEY,
    Periudha        VARCHAR(7),
    BazaShitjes     DECIMAL(18,2)   DEFAULT 0,
    TVSHShitjes     DECIMAL(18,2)   DEFAULT 0,
    BazaBlerjes     DECIMAL(18,2)   DEFAULT 0,
    TVSHBlerjes     DECIMAL(18,2)   DEFAULT 0,
    VITI            INT,
    DYQANI          INT
);
GO

IF OBJECT_ID('TATIMET') IS NULL
CREATE TABLE TATIMET (
    ID          INT             IDENTITY(1,1) PRIMARY KEY,
    EmriTatimit NVARCHAR(100),
    Norma       DECIMAL(5,2),
    DATA        DATE,
    Vlera       DECIMAL(18,2)   DEFAULT 0,
    DYQANI      INT
);
GO

IF OBJECT_ID('Kontot_tbl') IS NULL
CREATE TABLE Kontot_tbl (
    ID          INT             IDENTITY(1,1) PRIMARY KEY,
    NrKontit    VARCHAR(20),
    Emri        NVARCHAR(100),
    LlojiKontit TINYINT,
    Saldo       DECIMAL(18,2)   DEFAULT 0
);
GO

IF OBJECT_ID('Obligimet') IS NULL
CREATE TABLE Obligimet (
    ID              INT             IDENTITY(1,1) PRIMARY KEY,
    BLERESI         INT             NULL,
    FURNITORI       INT             NULL,
    NR_FATURES      VARCHAR(20),
    Vlera           DECIMAL(18,2)   DEFAULT 0,
    DataSkadimit    DATE,
    Paguar          BIT             DEFAULT 0,
    DataPagimit     DATE            NULL
);
GO

IF OBJECT_ID('FSHIRJET') IS NULL
CREATE TABLE FSHIRJET (
    ID              INT             IDENTITY(1,1) PRIMARY KEY,
    NR_FATURES      INT,
    LlojiDokumentit INT,
    DataOrigjinale  DATE,
    DataFshirjes    DATETIME,
    FshiruarNga     NVARCHAR(50),
    Arsyeja         NVARCHAR(200),
    DYQANI          INT,
    IDN             INT,
    Sasia           DECIMAL(18,3),
    Qmimi           DECIMAL(18,4),
    OperatoriID     INT
);
GO

IF COL_LENGTH('dbo.FSHIRJET', 'IDN') IS NULL ALTER TABLE dbo.FSHIRJET ADD IDN INT NULL;
IF COL_LENGTH('dbo.FSHIRJET', 'Sasia') IS NULL ALTER TABLE dbo.FSHIRJET ADD Sasia DECIMAL(18,3) NULL;
IF COL_LENGTH('dbo.FSHIRJET', 'Qmimi') IS NULL ALTER TABLE dbo.FSHIRJET ADD Qmimi DECIMAL(18,4) NULL;
IF COL_LENGTH('dbo.FSHIRJET', 'OperatoriID') IS NULL ALTER TABLE dbo.FSHIRJET ADD OperatoriID INT NULL;
GO

IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name='IX_FSHIRJET_REPORT' AND object_id=OBJECT_ID('dbo.FSHIRJET'))
    CREATE INDEX IX_FSHIRJET_REPORT ON dbo.FSHIRJET(DataFshirjes, DYQANI, OperatoriID) INCLUDE (IDN, Sasia, Qmimi);
GO

IF OBJECT_ID('AfatetEskadences') IS NULL
CREATE TABLE AfatetEskadences (
    ID              INT             IDENTITY(1,1) PRIMARY KEY,
    IDN             INT             NOT NULL,
    AfatiSkadences  DATE,
    SASIA           DECIMAL(18,3)   DEFAULT 0,
    DYQANI          INT
);
GO

IF OBJECT_ID('Pershtatjet') IS NULL
CREATE TABLE Pershtatjet (
    ID                  INT             IDENTITY(1,1) PRIMARY KEY,
    IDN                 INT             NULL,
    LlojiPershtatjes    TINYINT,
    Vlera               DECIMAL(18,4)   DEFAULT 0,
    Arsyeja             NVARCHAR(200),
    DATA                DATETIME,
    LogedBy             NVARCHAR(50),
    Dyqani              INT
);
GO

IF OBJECT_ID('KMSH') IS NULL
CREATE TABLE KMSH (
    ID      INT             IDENTITY(1,1) PRIMARY KEY,
    IDN     INT             NOT NULL,
    Kostoja DECIMAL(18,4)   DEFAULT 0,
    DATA    DATETIME,
    Dyqani  INT,
    LogedBy NVARCHAR(50)
);
GO

-- =============================================================================
-- PRODUCTION TABLES
-- =============================================================================

IF OBJECT_ID('Prodhimi_tbl') IS NULL
CREATE TABLE Prodhimi_tbl (
    ID              INT             IDENTITY(1,1) PRIMARY KEY,
    NR_POROSISE     INT             NOT NULL,
    VITI            INT,
    DATA            DATE,
    IDN             INT             NOT NULL,
    SasiaPlani      DECIMAL(18,3)   DEFAULT 0,
    SasiaProdhuar   DECIMAL(18,3)   DEFAULT 0,
    KostoPrNjesi    DECIMAL(18,4)   DEFAULT 0,
    DYQANI          INT,
    Statusi         TINYINT         DEFAULT 0,
    OperatoriID     INT
);
GO

IF OBJECT_ID('ProdhimiLendaPar_tbl') IS NULL
CREATE TABLE ProdhimiLendaPar_tbl (
    ID          INT             IDENTITY(1,1) PRIMARY KEY,
    ProdhimiID  INT             NOT NULL,
    IDN         INT             NOT NULL,
    SasiaPlani  DECIMAL(18,3)   DEFAULT 0,
    SasiaAktuale DECIMAL(18,3)  DEFAULT 0,
    Kostoja     DECIMAL(18,4)   DEFAULT 0
);
GO

IF OBJECT_ID('Normativat_BOM') IS NULL   -- (New — BOM/Recipes)
CREATE TABLE Normativat_BOM (
    ID                  INT             IDENTITY(1,1) PRIMARY KEY,
    IDN_Perfundimtar    INT             NOT NULL,   -- FK → ARTIKUJT (finished product)
    IDN_LendaPare       INT             NOT NULL,   -- FK → ARTIKUJT (raw material)
    SasiaNe1Njesi       DECIMAL(18,5)   DEFAULT 0,
    NjesiaID            INT             NULL
);
GO

IF OBJECT_ID('LendaPare_tbl') IS NULL
CREATE TABLE LendaPare_tbl (
    ID              INT             IDENTITY(1,1) PRIMARY KEY,
    FURNITORI       INT,
    IDN             INT             NOT NULL,
    SASIA           DECIMAL(18,3)   DEFAULT 0,
    [Çmimi]         DECIMAL(18,4)   DEFAULT 0,
    Vlera           DECIMAL(18,2)   DEFAULT 0,
    DataDhenies     DATE,
    DataKthimit     DATE            NULL,
    Kthyer          BIT             DEFAULT 0,
    DYQANI          INT
);
GO

-- =============================================================================
-- LOYALTY TABLES
-- =============================================================================

IF OBJECT_ID('LoyaltyTiers_tbl') IS NULL
CREATE TABLE LoyaltyTiers_tbl (
    ID              INT             IDENTITY(1,1) PRIMARY KEY,
    EmriTierit      NVARCHAR(50)    NOT NULL,
    MinPiket        DECIMAL(18,2)   DEFAULT 0,
    ZbritjaPercent  DECIMAL(5,2)    DEFAULT 0,
    Ngjyra          VARCHAR(20)     DEFAULT '#808080'
);
GO

-- =============================================================================
-- SCANNER / REGISTRATION TABLES
-- =============================================================================

IF OBJECT_ID('RegjistrimiMallit') IS NULL
CREATE TABLE RegjistrimiMallit (
    ID              INT             IDENTITY(1,1) PRIMARY KEY,
    NR_REGJISTRIMIT INT,
    VITI            INT,
    DATA            DATE,
    Dyqani          INT,
    OperatoriID     INT,
    VALIDIMI        BIT             DEFAULT 0,
    LogedBy         NVARCHAR(50)
);
GO

IF OBJECT_ID('RegjistrimiMallitDetale') IS NULL
CREATE TABLE RegjistrimiMallitDetale (
    ID              INT             IDENTITY(1,1) PRIMARY KEY,
    RegjistrimiID   INT             NOT NULL,
    IDN             INT             NOT NULL,
    BARKODI         VARCHAR(100),
    SASIA           DECIMAL(18,3)   DEFAULT 0
);
GO

IF OBJECT_ID('RegjistrimiMallitListat') IS NULL
CREATE TABLE RegjistrimiMallitListat (
    ID          INT             IDENTITY(1,1) PRIMARY KEY,
    Emri        NVARCHAR(100),
    DATA        DATETIME,
    Dyqani      INT,
    OperatoriID INT
);
GO

IF OBJECT_ID('RegjistrimiMallitListatDetale') IS NULL
CREATE TABLE RegjistrimiMallitListatDetale (
    ID          INT             IDENTITY(1,1) PRIMARY KEY,
    ListaID     INT             NOT NULL,
    IDN         INT             NOT NULL,
    BARKODI     VARCHAR(100),
    SasiaPlani  DECIMAL(18,3)   DEFAULT 0
);
GO

IF OBJECT_ID('PDALista') IS NULL
CREATE TABLE PDALista (
    ID      INT             IDENTITY(1,1) PRIMARY KEY,
    Emri    NVARCHAR(100),
    IP      VARCHAR(50),
    Aktiv   BIT             DEFAULT 1
);
GO

-- =============================================================================
-- SYSTEM TABLES
-- =============================================================================

IF OBJECT_ID('Sinkronizimi_tbl') IS NULL
CREATE TABLE Sinkronizimi_tbl (
    ID                  INT             IDENTITY(1,1) PRIMARY KEY,
    DYQANI              INT,
    Tabela              VARCHAR(100),
    DataSinkronizimit   DATETIME2,
    Statusi             TINYINT         DEFAULT 0,
    Gabimi              NVARCHAR(500)   NULL
);
GO

IF OBJECT_ID('SinkronizimiQuery') IS NULL
CREATE TABLE SinkronizimiQuery (
    ID          INT             IDENTITY(1,1) PRIMARY KEY,
    Emri        NVARCHAR(100),
    SQL         NVARCHAR(MAX),
    Drejtimi    TINYINT         -- 1=Push 2=Pull
);
GO

IF OBJECT_ID('OperatoriSystem') IS NULL
CREATE TABLE OperatoriSystem (
    ID          INT             IDENTITY(1,1) PRIMARY KEY,
    OperatoriID INT,
    DATA        DATETIME,
    Veprimi     NVARCHAR(200)
);
GO

IF OBJECT_ID('OperatoriSystemObjektet') IS NULL
CREATE TABLE OperatoriSystemObjektet (
    ID   INT             IDENTITY(1,1) PRIMARY KEY,
    Emri NVARCHAR(100),
    Kodi VARCHAR(50)
);
GO

IF OBJECT_ID('OperatoriSystemQasjet') IS NULL
CREATE TABLE OperatoriSystemQasjet (
    ID          INT             IDENTITY(1,1) PRIMARY KEY,
    OperatoriID INT,
    ObjektiID   INT,
    Lexim       BIT             DEFAULT 0,
    Shkrim      BIT             DEFAULT 0,
    Fshirje     BIT             DEFAULT 0
);
GO

IF OBJECT_ID('AQuery') IS NULL
CREATE TABLE AQuery (
    ID          INT             IDENTITY(1,1) PRIMARY KEY,
    Emri        NVARCHAR(100),
    SQL         NVARCHAR(MAX),
    KrijuarNga  NVARCHAR(50)
);
GO

IF OBJECT_ID('Verzionet') IS NULL
CREATE TABLE Verzionet (
    ID              INT             IDENTITY(1,1) PRIMARY KEY,
    Versioni        NVARCHAR(20),
    DataLeshimit    DATE,
    Pershkrimi      NVARCHAR(500)
);
GO

IF OBJECT_ID('TblLicenca') IS NULL
CREATE TABLE TblLicenca (
    ID              INT             IDENTITY(1,1) PRIMARY KEY,
    LicensaKey      VARCHAR(200),
    DataSkadimit    DATE,
    NrDyqaneve      INT             DEFAULT 1,
    Aktiv           BIT             DEFAULT 1
);
GO

-- (New) Schema version tracking
IF OBJECT_ID('SchemaVersions') IS NULL
CREATE TABLE SchemaVersions (
    ID          INT             IDENTITY(1,1) PRIMARY KEY,
    Versioni    INT             NOT NULL,
    AplikuarNe  DATETIME2       DEFAULT GETDATE(),
    Pershkrimi  NVARCHAR(200)
);
GO

-- (New) Full audit log
IF OBJECT_ID('AuditLog') IS NULL
CREATE TABLE AuditLog (
    ID              INT             IDENTITY(1,1) PRIMARY KEY,
    Tabela          NVARCHAR(100),
    RecordID        INT,
    Veprimi         VARCHAR(10),    -- INSERT/UPDATE/DELETE
    VleratVjetra    NVARCHAR(MAX),
    VleratRe        NVARCHAR(MAX),
    NdryshimNga     NVARCHAR(50),
    NdryshimNe      DATETIME2       DEFAULT GETDATE()
);
GO

IF NOT EXISTS (
    SELECT 1 FROM sys.indexes
    WHERE name = 'IX_AuditLog_TableRecordDate'
      AND object_id = OBJECT_ID('AuditLog')
)
    CREATE INDEX IX_AuditLog_TableRecordDate
    ON AuditLog(Tabela, RecordID, NdryshimNe DESC)
    INCLUDE (Veprimi, NdryshimNga);
GO

IF OBJECT_ID('InvoiceImportTemplates') IS NULL
CREATE TABLE InvoiceImportTemplates (
    ID                          INT             IDENTITY(1,1) PRIMARY KEY,
    SupplierId                  INT             NOT NULL,
    Name                        NVARCHAR(150)   NULL,
    Delimiter                   NVARCHAR(5)     NOT NULL DEFAULT ',',
    HeaderRow                   INT             NOT NULL DEFAULT 1,
    FirstDataRow                INT             NOT NULL DEFAULT 2,
    BarcodeColumn               NVARCHAR(120)   NULL,
    ProductCodeColumn           NVARCHAR(120)   NULL,
    DescriptionColumn           NVARCHAR(120)   NULL,
    QuantityColumn              NVARCHAR(120)   NULL,
    UnitsPerPackageColumn       NVARCHAR(120)   NULL,
    UnitPriceWithoutVatColumn   NVARCHAR(120)   NULL,
    UnitPriceWithVatColumn      NVARCHAR(120)   NULL,
    DiscountPercentColumn       NVARCHAR(120)   NULL,
    ExtraDiscountPercentColumn  NVARCHAR(120)   NULL,
    VatPercentColumn            NVARCHAR(120)   NULL,
    LineTotalColumn             NVARCHAR(120)   NULL,
    Active                      BIT             NOT NULL DEFAULT 1,
    CreatedAt                   DATETIME        NOT NULL DEFAULT GETDATE(),
    UpdatedAt                   DATETIME        NULL,
    CreatedBy                   NVARCHAR(50)    NULL,
    UpdatedBy                   NVARCHAR(50)    NULL
);
GO

IF NOT EXISTS (
    SELECT 1 FROM sys.indexes
    WHERE name = 'IX_InvoiceImportTemplates_Supplier_Active'
      AND object_id = OBJECT_ID('InvoiceImportTemplates')
)
CREATE INDEX IX_InvoiceImportTemplates_Supplier_Active
ON InvoiceImportTemplates(SupplierId, Active, ID DESC);
GO

IF OBJECT_ID('InvoiceImportCache') IS NULL
CREATE TABLE InvoiceImportCache (
    ID              INT             IDENTITY(1,1) PRIMARY KEY,
    SupplierId      INT             NOT NULL,
    FileHash        NVARCHAR(128)   NOT NULL,
    FileName        NVARCHAR(260)   NULL,
    InvoiceNumber   NVARCHAR(80)    NULL,
    InvoiceJson     NVARCHAR(MAX)   NOT NULL,
    CreatedAt       DATETIME        NOT NULL DEFAULT GETDATE(),
    UpdatedAt       DATETIME        NULL,
    CreatedBy       NVARCHAR(50)    NULL,
    UpdatedBy       NVARCHAR(50)    NULL
);
GO

IF NOT EXISTS (
    SELECT 1 FROM sys.indexes
    WHERE name = 'IX_InvoiceImportCache_Supplier_FileHash'
      AND object_id = OBJECT_ID('InvoiceImportCache')
)
CREATE UNIQUE INDEX IX_InvoiceImportCache_Supplier_FileHash
ON InvoiceImportCache(SupplierId, FileHash);
GO

IF OBJECT_ID('InvoiceSupplierInstructions') IS NULL
CREATE TABLE InvoiceSupplierInstructions (
    ID              INT             IDENTITY(1,1) PRIMARY KEY,
    SupplierId      INT             NOT NULL,
    Instructions    NVARCHAR(MAX)   NOT NULL DEFAULT '',
    CreatedAt       DATETIME        NOT NULL DEFAULT GETDATE(),
    UpdatedAt       DATETIME        NULL,
    CreatedBy       NVARCHAR(50)    NULL,
    UpdatedBy       NVARCHAR(50)    NULL
);
GO

IF NOT EXISTS (
    SELECT 1 FROM sys.indexes
    WHERE name = 'IX_InvoiceSupplierInstructions_Supplier'
      AND object_id = OBJECT_ID('InvoiceSupplierInstructions')
)
CREATE UNIQUE INDEX IX_InvoiceSupplierInstructions_Supplier
ON InvoiceSupplierInstructions(SupplierId);
GO

-- =============================================================================
-- DEFAULT SEED DATA
-- =============================================================================

IF NOT EXISTS (SELECT 1 FROM MenyraPageses_tbl)
BEGIN
    INSERT INTO MenyraPageses_tbl (Emri, IsCash) VALUES
        (N'Kesh', 1),
        (N'Kartë Bankare', 0),
        (N'Transferë Bankare', 0),
        (N'Çek', 0);
END
GO

IF NOT EXISTS (SELECT 1 FROM Normativa_tbl)
BEGIN
    INSERT INTO Normativa_tbl (TVSH, Pershkrimi, IsDefault) VALUES
        (0,  N'Pa TVSH (0%)',  0),
        (8,  N'TVSH 8%',       0),
        (18, N'TVSH 18%',      1);
END
GO

IF NOT EXISTS (SELECT 1 FROM NjesitMatese)
BEGIN
    INSERT INTO NjesitMatese (Njesia) VALUES
        (N'copë'), (N'kg'), (N'litër'), (N'metër'),
        (N'paketë'), (N'kuti'), (N'palë'), (N'set');
END
GO

IF NOT EXISTS (SELECT 1 FROM LoyaltyTiers_tbl)
BEGIN
    INSERT INTO LoyaltyTiers_tbl (EmriTierit, MinPiket, ZbritjaPercent, Ngjyra) VALUES
        (N'Bronz',  0,    2,  '#CD7F32'),
        (N'Argjend', 500,  5,  '#C0C0C0'),
        (N'Ar',     1500,  10, '#FFD700');
END
GO

IF NOT EXISTS (SELECT 1 FROM Konfigurimet WHERE Celes = 'FiscalPrinterEnabled')
BEGIN
    INSERT INTO Konfigurimet (Celes, Vlera) VALUES
        ('FiscalPrinterEnabled',    '0'),
        ('FiscalPrinterBrand',      'GEKOS'),
        ('AllowNegativeStock',      '0'),
        ('AllowPriceChange',        '1'),
        ('AllowDiscount',           '1'),
        ('FifoEnabled',             '0'),
        ('InvoiceCopies',           '1'),
        ('PointsPerUnit',           '0.1'),
        ('AppVersion',              '6.0.0');
END
GO

IF NOT EXISTS (SELECT 1 FROM SchemaVersions)
    INSERT INTO SchemaVersions (Versioni, Pershkrimi) VALUES (1, 'Initial SHOP2026 schema');
GO
