-- BlueGreenPlanet — SQL Server setup
-- Run this against the Dev database on LUMPYSWRKLAPTOP
-- Requires: SQL Server 2016+ (for JSON support in migration step)
-- Auth:      Windows Authentication
-- Safe to re-run — all steps are idempotent.

USE Dev;
GO

-- ── Users ─────────────────────────────────────────────────────────
IF NOT EXISTS (SELECT 1 FROM sys.tables WHERE name = 'Users' AND schema_id = SCHEMA_ID('dbo'))
BEGIN
    CREATE TABLE dbo.Users (
        Id                  NVARCHAR(36)  NOT NULL,
        Username            NVARCHAR(100) NOT NULL,
        Email               NVARCHAR(255) NOT NULL,
        PasswordHash        NVARCHAR(500) NOT NULL,
        PasswordSalt        NVARCHAR(500) NOT NULL,
        Role                NVARCHAR(50)  NOT NULL  DEFAULT 'user',
        IsActive            BIT           NOT NULL  DEFAULT 1,
        CreatedAt           DATETIME2     NOT NULL  DEFAULT GETUTCDATE(),
        FirstName           NVARCHAR(100) NULL,
        LastName            NVARCHAR(100) NULL,
        Phone               NVARCHAR(50)  NULL,
        PhoneCountryCode    NVARCHAR(10)  NULL,
        Company             NVARCHAR(255) NULL,
        JobTitle            NVARCHAR(255) NULL,
        Department          NVARCHAR(255) NULL,
        IsPostalAddressSame BIT           NOT NULL  DEFAULT 1,
        TwoFactorEnabled    BIT           NOT NULL  DEFAULT 0,
        FailedLoginAttempts INT           NOT NULL  DEFAULT 0,
        LockoutUntil        DATETIME2     NULL,
        CONSTRAINT PK_Users          PRIMARY KEY (Id),
        CONSTRAINT UQ_Users_Username UNIQUE (Username),
        CONSTRAINT UQ_Users_Email    UNIQUE (Email)
    );
    PRINT 'Created Users table.';
END
ELSE
    PRINT 'Users table already exists — checking columns.';
GO

-- Rename PostalSameAsResidential → IsPostalAddressSame (one-time rename)
IF EXISTS (SELECT 1 FROM sys.columns WHERE object_id = OBJECT_ID('dbo.Users') AND name = 'PostalSameAsResidential')
BEGIN
    EXEC sp_rename 'dbo.Users.PostalSameAsResidential', 'IsPostalAddressSame', 'COLUMN';
    PRINT 'Renamed PostalSameAsResidential → IsPostalAddressSame.';
END
ELSE IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE object_id = OBJECT_ID('dbo.Users') AND name = 'IsPostalAddressSame')
BEGIN
    ALTER TABLE dbo.Users ADD IsPostalAddressSame BIT NOT NULL DEFAULT 1;
    PRINT 'Added IsPostalAddressSame column.';
END
GO

-- Add TwoFactorEnabled if missing
IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE object_id = OBJECT_ID('dbo.Users') AND name = 'TwoFactorEnabled')
BEGIN
    ALTER TABLE dbo.Users ADD TwoFactorEnabled BIT NOT NULL DEFAULT 0;
    PRINT 'Added TwoFactorEnabled column.';
END
GO

-- ── Sessions ──────────────────────────────────────────────────────
IF NOT EXISTS (SELECT 1 FROM sys.tables WHERE name = 'Sessions' AND schema_id = SCHEMA_ID('dbo'))
BEGIN
    CREATE TABLE dbo.Sessions (
        Id        NVARCHAR(36) NOT NULL,
        UserId    NVARCHAR(36) NOT NULL,
        CreatedAt DATETIME2    NOT NULL DEFAULT GETUTCDATE(),
        ExpiresAt DATETIME2    NOT NULL,
        CONSTRAINT PK_Sessions       PRIMARY KEY (Id),
        CONSTRAINT FK_Sessions_Users FOREIGN KEY (UserId)
            REFERENCES dbo.Users (Id) ON DELETE CASCADE
    );
    PRINT 'Created Sessions table.';
END
GO

-- ── Addresses ─────────────────────────────────────────────────────
IF NOT EXISTS (SELECT 1 FROM sys.tables WHERE name = 'Addresses' AND schema_id = SCHEMA_ID('dbo'))
BEGIN
    CREATE TABLE dbo.Addresses (
        Id          NVARCHAR(36)  NOT NULL,
        UserId      NVARCHAR(36)  NOT NULL,
        AddressType NVARCHAR(20)  NOT NULL,   -- 'Residential' | 'Postal'
        Line1       NVARCHAR(255) NULL,
        Line2       NVARCHAR(255) NULL,
        City        NVARCHAR(100) NULL,
        State       NVARCHAR(100) NULL,
        PostCode    NVARCHAR(20)  NULL,
        Country     NVARCHAR(100) NULL,
        CountryCode NVARCHAR(10)  NULL,
        CONSTRAINT PK_Addresses          PRIMARY KEY (Id),
        CONSTRAINT FK_Addresses_Users    FOREIGN KEY (UserId) REFERENCES dbo.Users (Id) ON DELETE CASCADE,
        CONSTRAINT UQ_Addresses_UserType UNIQUE (UserId, AddressType)
    );
    PRINT 'Created Addresses table.';
END
ELSE
    PRINT 'Addresses table already exists — skipped.';
GO

-- ── TwoFactorMethods ──────────────────────────────────────────────
IF NOT EXISTS (SELECT 1 FROM sys.tables WHERE name = 'TwoFactorMethods' AND schema_id = SCHEMA_ID('dbo'))
BEGIN
    CREATE TABLE dbo.TwoFactorMethods (
        Id         NVARCHAR(36)  NOT NULL,
        UserId     NVARCHAR(36)  NOT NULL,
        MethodType NVARCHAR(20)  NOT NULL,   -- 'email' | 'sms' | 'totp'
        Value      NVARCHAR(255) NULL,        -- email address, phone number, or 'Authenticator App'
        Secret     NVARCHAR(500) NULL,        -- TOTP base32 secret (nullable)
        IsPrimary  BIT           NOT NULL DEFAULT 0,
        CreatedAt  DATETIME2     NOT NULL DEFAULT GETUTCDATE(),
        CONSTRAINT PK_TwoFactorMethods       PRIMARY KEY (Id),
        CONSTRAINT FK_TwoFactorMethods_Users FOREIGN KEY (UserId) REFERENCES dbo.Users (Id) ON DELETE CASCADE
    );
    PRINT 'Created TwoFactorMethods table.';
END
ELSE
    PRINT 'TwoFactorMethods table already exists — skipped.';
GO

-- ── PaymentMethods ────────────────────────────────────────────────
IF NOT EXISTS (SELECT 1 FROM sys.tables WHERE name = 'PaymentMethods' AND schema_id = SCHEMA_ID('dbo'))
BEGIN
    CREATE TABLE dbo.PaymentMethods (
        Id             NVARCHAR(36)  NOT NULL,
        UserId         NVARCHAR(36)  NOT NULL,
        MethodType     NVARCHAR(20)  NOT NULL DEFAULT 'card',  -- 'card' | 'paypal'
        CardType       NVARCHAR(50)  NULL,
        CardholderName NVARCHAR(255) NULL,
        Last4          NVARCHAR(4)   NULL,
        ExpiryMonth    SMALLINT      NULL,
        ExpiryYear     SMALLINT      NULL,
        PaypalEmail    NVARCHAR(255) NULL,
        IsPrimary      BIT           NOT NULL DEFAULT 0,
        CreatedAt      DATETIME2     NOT NULL DEFAULT GETUTCDATE(),
        CONSTRAINT PK_PaymentMethods       PRIMARY KEY (Id),
        CONSTRAINT FK_PaymentMethods_Users FOREIGN KEY (UserId) REFERENCES dbo.Users (Id) ON DELETE CASCADE
    );
    PRINT 'Created PaymentMethods table.';
END
ELSE
BEGIN
    PRINT 'PaymentMethods table already exists — checking columns.';
    IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE object_id = OBJECT_ID('dbo.PaymentMethods') AND name = 'MethodType')
    BEGIN
        ALTER TABLE dbo.PaymentMethods ADD MethodType NVARCHAR(20) NOT NULL DEFAULT 'card';
        PRINT 'Added MethodType column to PaymentMethods.';
    END
    IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE object_id = OBJECT_ID('dbo.PaymentMethods') AND name = 'PaypalEmail')
    BEGIN
        ALTER TABLE dbo.PaymentMethods ADD PaypalEmail NVARCHAR(255) NULL;
        PRINT 'Added PaypalEmail column to PaymentMethods.';
    END
END
GO

-- ── Migrate JSON column data → new tables ─────────────────────────
-- Wrapped in EXEC() so SQL Server only parses them at runtime,
-- after the IF guard confirms the column exists.

-- Residential addresses
IF EXISTS (SELECT 1 FROM sys.columns WHERE object_id = OBJECT_ID('dbo.Users') AND name = 'ResidentialAddressJson')
BEGIN
    EXEC('
        INSERT INTO dbo.Addresses (Id, UserId, AddressType, Line1, Line2, City, State, PostCode, Country, CountryCode)
        SELECT
            LOWER(CONVERT(NVARCHAR(36), NEWID())), u.Id, ''Residential'',
            NULLIF(JSON_VALUE(u.ResidentialAddressJson, ''$.line1''),       ''''),
            NULLIF(JSON_VALUE(u.ResidentialAddressJson, ''$.line2''),       ''''),
            NULLIF(JSON_VALUE(u.ResidentialAddressJson, ''$.city''),        ''''),
            NULLIF(JSON_VALUE(u.ResidentialAddressJson, ''$.state''),       ''''),
            NULLIF(JSON_VALUE(u.ResidentialAddressJson, ''$.postcode''),    ''''),
            NULLIF(JSON_VALUE(u.ResidentialAddressJson, ''$.country''),     ''''),
            NULLIF(JSON_VALUE(u.ResidentialAddressJson, ''$.countryCode''), '''')
        FROM dbo.Users u
        WHERE u.ResidentialAddressJson IS NOT NULL
          AND NOT EXISTS (SELECT 1 FROM dbo.Addresses a WHERE a.UserId = u.Id AND a.AddressType = ''Residential'');
    ');
    PRINT 'Migrated residential addresses.';
END
GO

-- Postal addresses
IF EXISTS (SELECT 1 FROM sys.columns WHERE object_id = OBJECT_ID('dbo.Users') AND name = 'PostalAddressJson')
BEGIN
    EXEC('
        INSERT INTO dbo.Addresses (Id, UserId, AddressType, Line1, Line2, City, State, PostCode, Country, CountryCode)
        SELECT
            LOWER(CONVERT(NVARCHAR(36), NEWID())), u.Id, ''Postal'',
            NULLIF(JSON_VALUE(u.PostalAddressJson, ''$.line1''),       ''''),
            NULLIF(JSON_VALUE(u.PostalAddressJson, ''$.line2''),       ''''),
            NULLIF(JSON_VALUE(u.PostalAddressJson, ''$.city''),        ''''),
            NULLIF(JSON_VALUE(u.PostalAddressJson, ''$.state''),       ''''),
            NULLIF(JSON_VALUE(u.PostalAddressJson, ''$.postcode''),    ''''),
            NULLIF(JSON_VALUE(u.PostalAddressJson, ''$.country''),     ''''),
            NULLIF(JSON_VALUE(u.PostalAddressJson, ''$.countryCode''), '''')
        FROM dbo.Users u
        WHERE u.PostalAddressJson IS NOT NULL
          AND NOT EXISTS (SELECT 1 FROM dbo.Addresses a WHERE a.UserId = u.Id AND a.AddressType = ''Postal'');
    ');
    PRINT 'Migrated postal addresses.';
END
GO

-- TwoFactor methods + enabled flag
IF EXISTS (SELECT 1 FROM sys.columns WHERE object_id = OBJECT_ID('dbo.Users') AND name = 'TwoFactorJson')
BEGIN
    EXEC('
        UPDATE dbo.Users
        SET TwoFactorEnabled = CASE WHEN JSON_VALUE(TwoFactorJson, ''$.enabled'') = ''true'' THEN 1 ELSE 0 END
        WHERE TwoFactorJson IS NOT NULL;

        INSERT INTO dbo.TwoFactorMethods (Id, UserId, MethodType, Value, Secret, IsPrimary, CreatedAt)
        SELECT
            COALESCE(NULLIF(m.id, ''''), LOWER(CONVERT(NVARCHAR(36), NEWID()))),
            u.Id, m.type, m.value, m.secret,
            CASE WHEN m.isPrimary = ''true'' THEN 1 ELSE 0 END,
            CASE WHEN ISDATE(m.createdAt) = 1 THEN CAST(m.createdAt AS DATETIME2) ELSE GETUTCDATE() END
        FROM dbo.Users u
        CROSS APPLY OPENJSON(JSON_QUERY(u.TwoFactorJson, ''$.methods'')) WITH (
            id        NVARCHAR(36)  ''$.id'',
            type      NVARCHAR(20)  ''$.type'',
            value     NVARCHAR(255) ''$.value'',
            secret    NVARCHAR(500) ''$.secret'',
            isPrimary NVARCHAR(10)  ''$.isPrimary'',
            createdAt NVARCHAR(50)  ''$.createdAt''
        ) m
        WHERE u.TwoFactorJson IS NOT NULL
          AND JSON_QUERY(u.TwoFactorJson, ''$.methods'') IS NOT NULL
          AND NOT EXISTS (SELECT 1 FROM dbo.TwoFactorMethods t WHERE t.UserId = u.Id);
    ');
    PRINT 'Migrated 2FA methods.';
END
GO

-- Payment methods
IF EXISTS (SELECT 1 FROM sys.columns WHERE object_id = OBJECT_ID('dbo.Users') AND name = 'PaymentMethodsJson')
BEGIN
    EXEC('
        INSERT INTO dbo.PaymentMethods (Id, UserId, CardType, CardholderName, Last4, ExpiryMonth, ExpiryYear, IsPrimary, CreatedAt)
        SELECT
            COALESCE(NULLIF(m.id, ''''), LOWER(CONVERT(NVARCHAR(36), NEWID()))),
            u.Id, m.cardType, m.cardholderName, m.last4,
            TRY_CAST(m.expiryMonth AS SMALLINT),
            TRY_CAST(m.expiryYear  AS SMALLINT),
            CASE WHEN m.isPrimary = ''true'' THEN 1 ELSE 0 END,
            CASE WHEN ISDATE(m.createdAt) = 1 THEN CAST(m.createdAt AS DATETIME2) ELSE GETUTCDATE() END
        FROM dbo.Users u
        CROSS APPLY OPENJSON(u.PaymentMethodsJson) WITH (
            id             NVARCHAR(36)  ''$.id'',
            cardType       NVARCHAR(50)  ''$.cardType'',
            cardholderName NVARCHAR(255) ''$.cardholderName'',
            last4          NVARCHAR(4)   ''$.last4'',
            expiryMonth    NVARCHAR(10)  ''$.expiryMonth'',
            expiryYear     NVARCHAR(10)  ''$.expiryYear'',
            isPrimary      NVARCHAR(10)  ''$.isPrimary'',
            createdAt      NVARCHAR(50)  ''$.createdAt''
        ) m
        WHERE u.PaymentMethodsJson IS NOT NULL
          AND NOT EXISTS (SELECT 1 FROM dbo.PaymentMethods p WHERE p.UserId = u.Id);
    ');
    PRINT 'Migrated payment methods.';
END
GO

-- ── Drop old JSON columns ─────────────────────────────────────────
IF EXISTS (SELECT 1 FROM sys.columns WHERE object_id = OBJECT_ID('dbo.Users') AND name = 'ResidentialAddressJson')
BEGIN ALTER TABLE dbo.Users DROP COLUMN ResidentialAddressJson; PRINT 'Dropped ResidentialAddressJson.'; END
GO
IF EXISTS (SELECT 1 FROM sys.columns WHERE object_id = OBJECT_ID('dbo.Users') AND name = 'PostalAddressJson')
BEGIN ALTER TABLE dbo.Users DROP COLUMN PostalAddressJson; PRINT 'Dropped PostalAddressJson.'; END
GO
IF EXISTS (SELECT 1 FROM sys.columns WHERE object_id = OBJECT_ID('dbo.Users') AND name = 'TwoFactorJson')
BEGIN ALTER TABLE dbo.Users DROP COLUMN TwoFactorJson; PRINT 'Dropped TwoFactorJson.'; END
GO
IF EXISTS (SELECT 1 FROM sys.columns WHERE object_id = OBJECT_ID('dbo.Users') AND name = 'PaymentMethodsJson')
BEGIN ALTER TABLE dbo.Users DROP COLUMN PaymentMethodsJson; PRINT 'Dropped PaymentMethodsJson.'; END
GO

-- ── Invoices ──────────────────────────────────────────────────────
IF NOT EXISTS (SELECT 1 FROM sys.tables WHERE name = 'Invoices' AND schema_id = SCHEMA_ID('dbo'))
BEGIN
    CREATE TABLE dbo.Invoices (
        Id            NVARCHAR(36)   NOT NULL,
        InvoiceNumber NVARCHAR(20)   NOT NULL,
        UserId        NVARCHAR(36)   NOT NULL,
        Description   NVARCHAR(1000) NULL,
        Amount        DECIMAL(12,2)  NOT NULL,
        Status        NVARCHAR(20)   NOT NULL DEFAULT 'pending',  -- 'pending' | 'paid' | 'partial' | 'void'
        DueDate       DATE           NULL,
        Notes         NVARCHAR(2000) NULL,
        CreatedBy     NVARCHAR(36)   NULL,     -- admin userId
        CreatedAt     DATETIME2      NOT NULL DEFAULT GETUTCDATE(),
        UpdatedAt     DATETIME2      NOT NULL DEFAULT GETUTCDATE(),
        CONSTRAINT PK_Invoices          PRIMARY KEY (Id),
        CONSTRAINT UQ_Invoices_Number   UNIQUE (InvoiceNumber),
        CONSTRAINT FK_Invoices_Users    FOREIGN KEY (UserId) REFERENCES dbo.Users (Id) ON DELETE CASCADE
    );
    PRINT 'Created Invoices table.';
END
ELSE
    PRINT 'Invoices table already exists — skipped.';
GO

-- ── Payments ──────────────────────────────────────────────────────
IF NOT EXISTS (SELECT 1 FROM sys.tables WHERE name = 'Payments' AND schema_id = SCHEMA_ID('dbo'))
BEGIN
    CREATE TABLE dbo.Payments (
        Id                    NVARCHAR(36)   NOT NULL,
        UserId                NVARCHAR(36)   NOT NULL,
        Amount                DECIMAL(12,2)  NOT NULL,
        Reference             NVARCHAR(255)  NULL,
        Message               NVARCHAR(1000) NULL,
        PaymentMethodSnapshot NVARCHAR(500)  NULL,   -- e.g. "Visa ••••4242" or "PayPal user@x.com"
        InvoiceId             NVARCHAR(36)   NULL,
        Status                NVARCHAR(20)   NOT NULL DEFAULT 'completed',  -- 'completed' | 'failed' | 'refunded'
        CreatedAt             DATETIME2      NOT NULL DEFAULT GETUTCDATE(),
        CONSTRAINT PK_Payments          PRIMARY KEY (Id),
        CONSTRAINT FK_Payments_Users    FOREIGN KEY (UserId)    REFERENCES dbo.Users    (Id) ON DELETE CASCADE,
        CONSTRAINT FK_Payments_Invoices FOREIGN KEY (InvoiceId) REFERENCES dbo.Invoices (Id) ON DELETE NO ACTION
    );
    PRINT 'Created Payments table.';
END
ELSE
    PRINT 'Payments table already exists — skipped.';
GO

PRINT 'Setup complete.';
GO