CREATE TABLE [tenant]
(
    [id] INT PRIMARY KEY IDENTITY(1,1),
    [property_spv_id] INT,
    [status] NVARCHAR(50),
    [start_date] DATETIME2,
    [end_date] DATETIME2,
    [created_at] DATETIME2,
    [updated_at] DATETIME2,
    FOREIGN KEY ([property_id]) REFERENCES [property]([property_spv_id])
);

CREATE TABLE [user_tenant]
(
    [user_tenant_id] INT IDENTITY(1,1) PRIMARY KEY,
    [user_tenant_property_id] INT,
    [user_id] INT,
    [created_on] DATETIME2,
    [updated_on] DATETIME2,
    [role] NVARCHAR(50) DEFAULT 'PRIMARY',
    FOREIGN KEY ([user_tenant_property_id]) REFERENCES [tenant]([id]),
    FOREIGN KEY ([user_id]) REFERENCES [users]([user_id])
);

CREATE TABLE [user_tenant_role]
(
    [user_role_id] INT IDENTITY(1,1) PRIMARY KEY,
    [role_id] INT,
    [user_id] INT,
    [is_active] BIT DEFAULT 1,
    [created_on] DATETIME2,
    [created_by] UNIQUEIDENTIFIER NULL,
    [updated_on] DATETIME2,
    [updated_by] UNIQUEIDENTIFIER NULL,
    FOREIGN KEY ([role_id]) REFERENCES [role]([role_id]),
    FOREIGN KEY ([user_id]) REFERENCES [users]([user_id])
);

CREATE TABLE [property_rent]
(
    [id] INT PRIMARY KEY IDENTITY(1,1),
    [property_spv_id] INT,
    [rent_price] INT,
    [status] NVARCHAR(50) DEFAULT 'ACTIVE',
    [created_at] DATETIME,
    [updated_at] DATETIME,
    [effective_date] DATETIME,
    FOREIGN KEY ([property_spv_id]) REFERENCES [property]([property_spv_id])
);

CREATE TABLE maintenance_type (
  id INT PRIMARY KEY IDENTITY(1,1),
  title VARCHAR(255),
  descriptions VARCHAR(255),
  file_id INT,
  status VARCHAR(255) DEFAULT 'Active',
  created_at DATETIME2,
  updated_at DATETIME2,
  FOREIGN KEY (file_id) REFERENCES media_files(id)
)

CREATE TABLE [maintenance_requests]
(
    [id] INT PRIMARY KEY  IDENTITY(1,1),
    [user_tenant_id] INT,
    [maintenance_type_id] INT,
    [urgency] NVARCHAR(50) DEFAULT 'MEDIUM',
    [created_by] INT,
    [status] NVARCHAR(50) DEFAULT 'OPEN',
    [request_date] DATETIME,
    [appointment_date] DATETIME,
    [created_at] DATETIME,
    [updated_at] DATETIME,
    [attachment_id] INT NULL,
    FOREIGN KEY ([user_tenant_id]) REFERENCES [user_tenant]([user_tenant_id]),
    FOREIGN KEY ([maintenance_type_id]) REFERENCES [maintenance_type]([id]),
    FOREIGN KEY ([attachment_id]) REFERENCES [media_files]([id])
);

CREATE TABLE [media_files]
(
    [id] INT PRIMARY KEY  IDENTITY(1,1),
    [file_name] VARCHAR(255),
    [mime] VARCHAR(50),
    [file_size] INT,
    [file_path] VARCHAR(255) NULL,
    [s3_key] VARCHAR(255) NULL,
    [created_at] DATETIME2 NOT NULL,
    [updated_on] DATETIME2 NOT NULL
);

CREATE TABLE maintenance_comments (
  id INT PRIMARY KEY IDENTITY(1,1),
  maintenance_request_id INT,
  comment VARCHAR(255),
  commenter_id INT,
  created_at DATETIME2,
  updated_at DATETIME2,
  attachment_id INT,
  FOREIGN KEY (maintenance_request_id) REFERENCES maintenance_requests(id),
  FOREIGN KEY (commenter_id) REFERENCES users(user_id),
  FOREIGN KEY (attachment_id) REFERENCES media_files(id)
);

CREATE TABLE user_tenant_invitations (
  id INT PRIMARY KEY IDENTITY(1,1),
  email VARCHAR(128),
  user_tenant_id INT,
  FOREIGN KEY (user_tenant_id) REFERENCES tenant(id)
);

CREATE TABLE reward_activity (
  id INT PRIMARY KEY IDENTITY(1,1),
  subscriber_id INT,
  activity VARCHAR(255),
  description VARCHAR(255),
  rewardPoints VARCHAR(255),
  type VARCHAR(255),
  created_on DATETIME2 DEFAULT GETDATE(),
  updated_on DATETIME2 DEFAULT GETDATE(),
  FOREIGN KEY (subscriber_id) REFERENCES subscriber(id)
);

CREATE TABLE brand_gift_card_request (
  id INT PRIMARY KEY IDENTITY(1,1),
  user_id INT,
  brand VARCHAR(255),
  is_gifted_by_team BIT DEFAULT 1,
  created_on DATETIME2 DEFAULT GETDATE(),
  updated_on DATETIME2 DEFAULT GETDATE(),
  FOREIGN KEY (user_id) REFERENCES users(user_id)
);

CREATE TABLE user_give_back_organization (
  id INT PRIMARY KEY IDENTITY(1,1),
  organization_name VARCHAR(255),
  user_id INT,
  created_on DATETIME DEFAULT GETDATE(),
  updated_on DATETIME DEFAULT GETDATE(),
  FOREIGN KEY (user_id) REFERENCES users(user_id)
);

Alter table  loyalty_nfts
Add is_Profile_Nft Bit DEFAULT 0;

Alter table subscriber
Add token_wallet_address varchar(250)