CREATE TABLE IF NOT EXISTS CoreAdmins (
  AutoNo INT AUTO_INCREMENT PRIMARY KEY,
  Name VARCHAR(150) NOT NULL,
  Email VARCHAR(200) NOT NULL UNIQUE,
  PasswordHash VARCHAR(255) NOT NULL,
  RoleCode VARCHAR(50) NOT NULL DEFAULT 'OWNER',
  TwoFactorSecret VARCHAR(64) NULL,
  TwoFactorEnabled TINYINT(1) NOT NULL DEFAULT 0,
  TwoFactorMethod VARCHAR(30) NOT NULL DEFAULT 'authenticator',
  EmailOtpHash VARCHAR(255) NULL,
  EmailOtpExpiresOn DATETIME NULL,
  IsActive TINYINT(1) NOT NULL DEFAULT 1,
  LastLoginOn DATETIME NULL,
  CreatedOn DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS CoreApps (
  AutoNo INT AUTO_INCREMENT PRIMARY KEY,
  AppCode VARCHAR(50) NOT NULL UNIQUE,
  AppName VARCHAR(200) NOT NULL,
  AppType VARCHAR(100) NULL,
  CurrentVersion VARCHAR(50) NULL,
  IsActive TINYINT(1) NOT NULL DEFAULT 1,
  CreatedOn DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS CoreAppFeatures (
  AutoNo INT AUTO_INCREMENT PRIMARY KEY,
  AppCode VARCHAR(50) NOT NULL,
  FeatureCode VARCHAR(100) NOT NULL,
  FeatureName VARCHAR(200) NOT NULL,
  Description TEXT NULL,
  IsActive TINYINT(1) NOT NULL DEFAULT 1,
  CreatedOn DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_app_feature (AppCode, FeatureCode)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS CoreCustomers (
  AutoNo INT AUTO_INCREMENT PRIMARY KEY,
  CustomerCode VARCHAR(50) NOT NULL UNIQUE,
  CustomerName VARCHAR(200) NOT NULL,
  ContactPerson VARCHAR(200) NULL,
  Phone VARCHAR(50) NULL,
  WhatsApp VARCHAR(50) NULL,
  Email VARCHAR(200) NULL,
  City VARCHAR(100) NULL,
  Notes TEXT NULL,
  IsActive TINYINT(1) NOT NULL DEFAULT 1,
  CreatedOn DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS CoreLicenses (
  AutoNo INT AUTO_INCREMENT PRIMARY KEY,
  LicenseId VARCHAR(100) NOT NULL UNIQUE,
  AppCode VARCHAR(50) NOT NULL,
  CustomerCode VARCHAR(50) NOT NULL,
  LicenseTitle VARCHAR(200) NULL,
  LicenseKey VARCHAR(200) NOT NULL UNIQUE,
  Status VARCHAR(50) NOT NULL DEFAULT 'active',
  AllowedDomainsJson LONGTEXT NULL,
  StartsOn DATETIME NULL,
  EndsOn DATETIME NULL,
  PriceAmount DECIMAL(18,2) NULL,
  PriceCurrency VARCHAR(20) NULL DEFAULT 'PKR',
  BillingCycle VARCHAR(50) NULL,
  MaxInstallations INT NOT NULL DEFAULT 1,
  MaxUsers INT NULL,
  MaxBranches INT NULL,
  MaxProducts INT NULL,
  Notes TEXT NULL,
  CreatedOn DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UpdatedOn DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  KEY idx_license_app_customer (AppCode, CustomerCode),
  KEY idx_license_status (Status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS CoreLicensePolicies (
  AutoNo INT AUTO_INCREMENT PRIMARY KEY,
  LicenseId VARCHAR(100) NOT NULL UNIQUE,
  AllowFirstActivationFree TINYINT(1) NOT NULL DEFAULT 0,
  FreeTrialDays INT NOT NULL DEFAULT 14,
  TokenValidDays INT NOT NULL DEFAULT 40,
  RenewAfterHours INT NOT NULL DEFAULT 96,
  RetryAfterMinutes INT NOT NULL DEFAULT 360,
  EnableReminders TINYINT(1) NOT NULL DEFAULT 1,
  ReminderDaysBeforeExpiry INT NOT NULL DEFAULT 15,
  ShowTrialScreenEveryLogin TINYINT(1) NOT NULL DEFAULT 1,
  ShowWarningEveryLogin TINYINT(1) NOT NULL DEFAULT 1,
  EmergencyUnlockDays INT NOT NULL DEFAULT 7,
  MaxEmergencyUnlocksPerMonth INT NOT NULL DEFAULT 2,
  AllowManualGrace TINYINT(1) NOT NULL DEFAULT 1,
  ManualGraceDays INT NOT NULL DEFAULT 3,
  PolicyJson LONGTEXT NULL,
  CreatedOn DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UpdatedOn DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS CoreLicenseFeatures (
  AutoNo INT AUTO_INCREMENT PRIMARY KEY,
  LicenseId VARCHAR(100) NOT NULL,
  FeatureCode VARCHAR(100) NOT NULL,
  FeatureName VARCHAR(200) NULL,
  IsEnabled TINYINT(1) NOT NULL DEFAULT 1,
  FeatureLimit INT NULL,
  ExtraJson LONGTEXT NULL,
  CreatedOn DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_license_feature (LicenseId, FeatureCode)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS CoreLicenseLimits (
  AutoNo INT AUTO_INCREMENT PRIMARY KEY,
  LicenseId VARCHAR(100) NOT NULL,
  LimitCode VARCHAR(100) NOT NULL,
  LimitValue DECIMAL(18,2) NULL,
  ExtraJson LONGTEXT NULL,
  CreatedOn DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_license_limit (LicenseId, LimitCode)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS CoreLicenseInstallations (
  AutoNo INT AUTO_INCREMENT PRIMARY KEY,
  InstallId VARCHAR(100) NOT NULL UNIQUE,
  LicenseId VARCHAR(100) NOT NULL,
  AppCode VARCHAR(50) NOT NULL,
  CustomerCode VARCHAR(50) NOT NULL,
  Domain VARCHAR(255) NULL,
  BasePath VARCHAR(100) NULL,
  FingerprintHash CHAR(64) NOT NULL,
  FirstSeenOn DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  LastSeenOn DATETIME NULL,
  LastTokenIssuedOn DATETIME NULL,
  LastTokenExpiresOn DATETIME NULL,
  LastIp VARCHAR(100) NULL,
  LastUserAgent VARCHAR(500) NULL,
  LastAppVersion VARCHAR(50) NULL,
  Status VARCHAR(50) NOT NULL DEFAULT 'active',
  SuspiciousScore INT NOT NULL DEFAULT 0,
  CreatedOn DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UpdatedOn DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  KEY idx_install_license (LicenseId),
  KEY idx_install_status (Status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS CoreLicenseScreens (
  AutoNo INT AUTO_INCREMENT PRIMARY KEY,
  LicenseId VARCHAR(100) NOT NULL UNIQUE,
  SupportPhone VARCHAR(50) NULL,
  SupportWhatsApp VARCHAR(50) NULL,
  SupportEmail VARCHAR(200) NULL,
  SupportUrl VARCHAR(500) NULL,
  TrialTitle VARCHAR(200) NULL,
  TrialMessage TEXT NULL,
  PaymentDueTitle VARCHAR(200) NULL,
  PaymentDueMessage TEXT NULL,
  WarningTitle VARCHAR(200) NULL,
  WarningMessage TEXT NULL,
  SuspendedTitle VARCHAR(200) NULL,
  SuspendedMessage TEXT NULL,
  ExpiredTitle VARCHAR(200) NULL,
  ExpiredMessage TEXT NULL,
  BlockedTitle VARCHAR(200) NULL,
  BlockedMessage TEXT NULL,
  StolenTitle VARCHAR(200) NULL,
  StolenMessage TEXT NULL,
  MaintenanceTitle VARCHAR(200) NULL,
  MaintenanceMessage TEXT NULL,
  ManualGraceTitle VARCHAR(200) NULL,
  ManualGraceMessage TEXT NULL,
  ThemeJson LONGTEXT NULL,
  CreatedOn DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UpdatedOn DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS CoreLicenseLeases (
  AutoNo INT AUTO_INCREMENT PRIMARY KEY,
  LeaseId VARCHAR(100) NOT NULL UNIQUE,
  LicenseId VARCHAR(100) NOT NULL,
  InstallId VARCHAR(100) NOT NULL,
  AppCode VARCHAR(50) NOT NULL,
  SignedToken LONGTEXT NOT NULL,
  TokenHash CHAR(64) NOT NULL,
  Status VARCHAR(50) NOT NULL,
  IssuedAt DATETIME NOT NULL,
  RenewAfter DATETIME NOT NULL,
  ExpiresAt DATETIME NOT NULL,
  CreatedOn DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_token_hash (TokenHash),
  KEY idx_lease_install (InstallId),
  KEY idx_lease_expiry (ExpiresAt)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS CoreEmergencyChallenges (
  AutoNo INT AUTO_INCREMENT PRIMARY KEY,
  ChallengeId VARCHAR(100) NOT NULL UNIQUE,
  ChallengeCode VARCHAR(255) NOT NULL UNIQUE,
  LicenseId VARCHAR(100) NULL,
  InstallId VARCHAR(100) NULL,
  AppCode VARCHAR(50) NULL,
  FingerprintHash CHAR(64) NULL,
  Domain VARCHAR(255) NULL,
  Nonce VARCHAR(100) NOT NULL,
  UsedOn DATETIME NULL,
  ExpiresOn DATETIME NOT NULL,
  CreatedOn DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS CoreEmergencyTokens (
  AutoNo INT AUTO_INCREMENT PRIMARY KEY,
  EmergencyId VARCHAR(100) NOT NULL UNIQUE,
  ChallengeId VARCHAR(100) NOT NULL,
  LicenseId VARCHAR(100) NOT NULL,
  InstallId VARCHAR(100) NOT NULL,
  ResponseCode VARCHAR(255) NOT NULL,
  SignedToken LONGTEXT NOT NULL,
  ExpiresOn DATETIME NOT NULL,
  CreatedBy VARCHAR(200) NULL,
  CreatedOn DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS CoreOneTimeLinks (
  AutoNo INT AUTO_INCREMENT PRIMARY KEY,
  LinkId VARCHAR(100) NOT NULL UNIQUE,
  LicenseId VARCHAR(100) NOT NULL,
  InstallId VARCHAR(100) NULL,
  SecretHash CHAR(64) NOT NULL,
  Purpose VARCHAR(100) NOT NULL DEFAULT 'emergency_unlock',
  IsUsed TINYINT(1) NOT NULL DEFAULT 0,
  UsedOn DATETIME NULL,
  ExpiresOn DATETIME NOT NULL,
  CreatedOn DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS CoreSuspiciousEvents (
  AutoNo INT AUTO_INCREMENT PRIMARY KEY,
  EventType VARCHAR(100) NOT NULL,
  Severity VARCHAR(30) NOT NULL DEFAULT 'medium',
  LicenseId VARCHAR(100) NULL,
  InstallId VARCHAR(100) NULL,
  AppCode VARCHAR(50) NULL,
  Message VARCHAR(500) NULL,
  DataJson LONGTEXT NULL,
  CreatedOn DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_suspicious_created (CreatedOn)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS CoreNotifications (
  AutoNo INT AUTO_INCREMENT PRIMARY KEY,
  NotificationKey VARCHAR(190) NOT NULL UNIQUE,
  Severity VARCHAR(30) NOT NULL DEFAULT 'info',
  Title VARCHAR(200) NOT NULL,
  Message VARCHAR(700) NULL,
  AppCode VARCHAR(50) NULL,
  LicenseId VARCHAR(100) NULL,
  InstallId VARCHAR(100) NULL,
  Domain VARCHAR(255) NULL,
  IsRead TINYINT(1) NOT NULL DEFAULT 0,
  CreatedOn DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UpdatedOn DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  KEY idx_notification_read_created (IsRead, CreatedOn),
  KEY idx_notification_install (InstallId)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS CoreAuditLogs (
  AutoNo INT AUTO_INCREMENT PRIMARY KEY,
  ActorEmail VARCHAR(200) NULL,
  ActionName VARCHAR(150) NOT NULL,
  EntityType VARCHAR(100) NULL,
  EntityId VARCHAR(100) NULL,
  IpAddress VARCHAR(100) NULL,
  DataJson LONGTEXT NULL,
  CreatedOn DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_audit_created (CreatedOn)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS CoreSigningKeys (
  AutoNo INT AUTO_INCREMENT PRIMARY KEY,
  KeyId VARCHAR(100) NOT NULL UNIQUE,
  PublicKeyBase64 TEXT NOT NULL,
  PrivateKeyPath LONGTEXT NOT NULL,
  Algorithm VARCHAR(50) NOT NULL DEFAULT 'Ed25519',
  IsActive TINYINT(1) NOT NULL DEFAULT 1,
  CreatedOn DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  RetiredOn DATETIME NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS CoreSystemSettings (
  SettingKey VARCHAR(100) PRIMARY KEY,
  SettingValue LONGTEXT NULL,
  UpdatedOn DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT IGNORE INTO CoreSystemSettings (SettingKey, SettingValue) VALUES
('trial_free_days', '14'),
('mail_enabled', '0'),
('email_all_events', '0'),
('digest_hours', '5'),
('mail_driver', 'smtp'),
('smtp_port', '587'),
('smtp_encryption', 'tls');


-- Multi-server authority network migration
CREATE TABLE IF NOT EXISTS CoreAuthorityServers (
  AutoNo INT AUTO_INCREMENT PRIMARY KEY,
  ServerId VARCHAR(80) NOT NULL UNIQUE,
  ServerName VARCHAR(200) NOT NULL,
  ServerUrl VARCHAR(500) NOT NULL UNIQUE,
  Role VARCHAR(50) NOT NULL DEFAULT 'secondary',
  Status VARCHAR(50) NOT NULL DEFAULT 'active',
  PriorityNo INT NOT NULL DEFAULT 100,
  PublicKeyBase64 TEXT NULL,
  LastSeenAtUtc DATETIME NULL,
  LastSyncAtUtc DATETIME NULL,
  SyncVersion BIGINT NOT NULL DEFAULT 0,
  IsActive TINYINT(1) NOT NULL DEFAULT 1,
  CreatedAtUtc DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UpdatedAtUtc DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS CoreAuthorityControl (
  Id INT PRIMARY KEY DEFAULT 1,
  PreferredPrimaryServerId VARCHAR(80) NOT NULL,
  ActivePrimaryServerId VARCHAR(80) NOT NULL,
  ServerListVersion BIGINT NOT NULL DEFAULT 1,
  ServerListJson LONGTEXT NULL,
  ServerListSignature TEXT NULL,
  ServerListKeyId VARCHAR(100) NULL,
  LocalSyncVersion BIGINT NOT NULL DEFAULT 1,
  LastPromotedAtUtc DATETIME NULL,
  PromotedBy VARCHAR(200) NULL,
  RecoveryMode TINYINT(1) NOT NULL DEFAULT 0,
  LastError TEXT NULL,
  CreatedAtUtc DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UpdatedAtUtc DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS CoreAuthorityChangeLog (
  AutoNo BIGINT AUTO_INCREMENT PRIMARY KEY,
  ChangeId VARCHAR(120) NOT NULL UNIQUE,
  EntityType VARCHAR(100) NOT NULL,
  EntityId VARCHAR(150) NOT NULL,
  ActionType VARCHAR(80) NOT NULL,
  OldJson LONGTEXT NULL,
  NewJson LONGTEXT NULL,
  SyncVersion BIGINT NOT NULL,
  ChangedAtUtc DATETIME NOT NULL,
  ChangedBy VARCHAR(200) NULL,
  ChangedByServer VARCHAR(80) NOT NULL,
  SyncedToServersJson LONGTEXT NULL,
  SyncStatus VARCHAR(50) NOT NULL DEFAULT 'pending',
  LastSyncError TEXT NULL,
  KEY idx_authority_change_version (SyncVersion),
  KEY idx_authority_change_status (SyncStatus)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS CoreAuthoritySyncLog (
  AutoNo BIGINT AUTO_INCREMENT PRIMARY KEY,
  SourceServerId VARCHAR(80) NOT NULL,
  TargetServerId VARCHAR(80) NOT NULL,
  SyncStartedAtUtc DATETIME NOT NULL,
  SyncFinishedAtUtc DATETIME NULL,
  Status VARCHAR(50) NOT NULL,
  RecordsPushed INT NOT NULL DEFAULT 0,
  RecordsPulled INT NOT NULL DEFAULT 0,
  ErrorMessage TEXT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS CoreAuthorityNonces (
  Nonce VARCHAR(120) PRIMARY KEY,
  ServerId VARCHAR(80) NULL,
  UsedAtUtc DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO CoreAuthorityServers (ServerId,ServerName,ServerUrl,Role,Status,PriorityNo,IsActive) VALUES
('P1','Parallaxware Primary','https://parallaxware.pk/coreapp','preferred_primary','active',10,1),
('S1','IPOS Backup','https://ipos.com.pk/capp','secondary','active',20,1)
ON DUPLICATE KEY UPDATE ServerName=VALUES(ServerName), ServerUrl=VALUES(ServerUrl), PriorityNo=VALUES(PriorityNo);

INSERT IGNORE INTO CoreAuthorityControl (Id,PreferredPrimaryServerId,ActivePrimaryServerId,ServerListVersion,LocalSyncVersion)
VALUES (1,'P1','P1',1,1);

ALTER TABLE CoreApps ADD COLUMN SyncVersion BIGINT NOT NULL DEFAULT 0;
ALTER TABLE CoreApps ADD COLUMN ChangeId VARCHAR(120) NULL;
ALTER TABLE CoreApps ADD COLUMN UpdatedAtUtc DATETIME NULL;
ALTER TABLE CoreApps ADD COLUMN UpdatedByServer VARCHAR(80) NULL;
ALTER TABLE CoreApps ADD COLUMN IsDeleted TINYINT(1) NOT NULL DEFAULT 0;
ALTER TABLE CoreApps ADD COLUMN DeletedAtUtc DATETIME NULL;
ALTER TABLE CoreApps ADD COLUMN SyncStatus VARCHAR(50) NOT NULL DEFAULT 'synced';

ALTER TABLE CoreCustomers ADD COLUMN SyncVersion BIGINT NOT NULL DEFAULT 0;
ALTER TABLE CoreCustomers ADD COLUMN ChangeId VARCHAR(120) NULL;
ALTER TABLE CoreCustomers ADD COLUMN UpdatedAtUtc DATETIME NULL;
ALTER TABLE CoreCustomers ADD COLUMN UpdatedByServer VARCHAR(80) NULL;
ALTER TABLE CoreCustomers ADD COLUMN IsDeleted TINYINT(1) NOT NULL DEFAULT 0;
ALTER TABLE CoreCustomers ADD COLUMN DeletedAtUtc DATETIME NULL;
ALTER TABLE CoreCustomers ADD COLUMN SyncStatus VARCHAR(50) NOT NULL DEFAULT 'synced';

ALTER TABLE CoreLicenses ADD COLUMN SyncVersion BIGINT NOT NULL DEFAULT 0;
ALTER TABLE CoreLicenses ADD COLUMN ChangeId VARCHAR(120) NULL;
ALTER TABLE CoreLicenses ADD COLUMN UpdatedAtUtc DATETIME NULL;
ALTER TABLE CoreLicenses ADD COLUMN UpdatedByServer VARCHAR(80) NULL;
ALTER TABLE CoreLicenses ADD COLUMN IsDeleted TINYINT(1) NOT NULL DEFAULT 0;
ALTER TABLE CoreLicenses ADD COLUMN DeletedAtUtc DATETIME NULL;
ALTER TABLE CoreLicenses ADD COLUMN SyncStatus VARCHAR(50) NOT NULL DEFAULT 'synced';

ALTER TABLE CoreLicenseInstallations ADD COLUMN SyncVersion BIGINT NOT NULL DEFAULT 0;
ALTER TABLE CoreLicenseInstallations ADD COLUMN ChangeId VARCHAR(120) NULL;
ALTER TABLE CoreLicenseInstallations ADD COLUMN UpdatedAtUtc DATETIME NULL;
ALTER TABLE CoreLicenseInstallations ADD COLUMN UpdatedByServer VARCHAR(80) NULL;
ALTER TABLE CoreLicenseInstallations ADD COLUMN IsDeleted TINYINT(1) NOT NULL DEFAULT 0;
ALTER TABLE CoreLicenseInstallations ADD COLUMN DeletedAtUtc DATETIME NULL;
ALTER TABLE CoreLicenseInstallations ADD COLUMN SyncStatus VARCHAR(50) NOT NULL DEFAULT 'synced';


INSERT INTO CoreAdmins (Name, Email, PasswordHash, RoleCode, IsActive) VALUES ('Owner', 'muhammadyousaf9846@gmail.com', '$2y$10$a/kbp6fnkE7cnsGoH28LQ.P3QSEIxG1fQ9m8DCKKdaFSxHKJMeBJG', 'OWNER', 1) ON DUPLICATE KEY UPDATE Name=VALUES(Name), PasswordHash=VALUES(PasswordHash), RoleCode='OWNER', IsActive=1;


-- Security hardening tables

CREATE TABLE IF NOT EXISTS CoreTokenRevocations (
  AutoNo BIGINT AUTO_INCREMENT PRIMARY KEY,
  RevocationId VARCHAR(120) NOT NULL UNIQUE,
  LicenseId VARCHAR(80) NULL,
  InstallId VARCHAR(120) NULL,
  TokenHash VARCHAR(128) NULL,
  Reason VARCHAR(255) NULL,
  RevokedByAdminId INT NULL,
  RevokedByServer VARCHAR(80) NULL,
  RevokedAtUtc DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  ExpiresAtUtc DATETIME NULL,
  IsActive TINYINT(1) NOT NULL DEFAULT 1,
  SyncVersion BIGINT NOT NULL DEFAULT 0,
  ChangeId VARCHAR(120) NULL,
  UpdatedAtUtc DATETIME NULL,
  UpdatedByServer VARCHAR(80) NULL,
  IsDeleted TINYINT(1) NOT NULL DEFAULT 0,
  DeletedAtUtc DATETIME NULL,
  SyncStatus VARCHAR(50) NOT NULL DEFAULT 'synced',
  KEY idx_revocation_license (LicenseId),
  KEY idx_revocation_install (InstallId),
  KEY idx_revocation_token (TokenHash),
  KEY idx_revocation_active (IsActive)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS CoreLoginAttempts (
  AutoNo BIGINT AUTO_INCREMENT PRIMARY KEY,
  Email VARCHAR(190) NULL,
  IpAddress VARCHAR(64) NULL,
  UserAgent VARCHAR(255) NULL,
  WasSuccessful TINYINT(1) NOT NULL DEFAULT 0,
  FailureReason VARCHAR(255) NULL,
  AttemptedAtUtc DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  AdminId INT NULL,
  SessionId VARCHAR(128) NULL,
  KEY idx_login_attempt_email (Email),
  KEY idx_login_attempt_ip (IpAddress),
  KEY idx_login_attempt_time (AttemptedAtUtc)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS CoreIpAllowlist (
  AutoNo BIGINT AUTO_INCREMENT PRIMARY KEY,
  IpCidr VARCHAR(80) NOT NULL,
  Label VARCHAR(150) NULL,
  Scope VARCHAR(50) NOT NULL DEFAULT 'admin',
  IsActive TINYINT(1) NOT NULL DEFAULT 1,
  CreatedByAdminId INT NULL,
  CreatedAtUtc DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UpdatedAtUtc DATETIME NULL,
  Notes TEXT NULL,
  UNIQUE KEY uq_ip_allowlist_scope (IpCidr, Scope),
  KEY idx_ip_allowlist_active (IsActive)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS CoreKeyRotationHistory (
  AutoNo BIGINT AUTO_INCREMENT PRIMARY KEY,
  RotationId VARCHAR(120) NOT NULL UNIQUE,
  OldKeyId VARCHAR(120) NULL,
  NewKeyId VARCHAR(120) NOT NULL,
  Algorithm VARCHAR(50) NULL,
  RotatedByAdminId INT NULL,
  RotatedByServer VARCHAR(80) NULL,
  RotatedAtUtc DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  Reason VARCHAR(255) NULL,
  PublicKeyBase64 LONGTEXT NULL,
  SyncVersion BIGINT NOT NULL DEFAULT 0,
  ChangeId VARCHAR(120) NULL,
  UpdatedAtUtc DATETIME NULL,
  UpdatedByServer VARCHAR(80) NULL,
  IsDeleted TINYINT(1) NOT NULL DEFAULT 0,
  DeletedAtUtc DATETIME NULL,
  SyncStatus VARCHAR(50) NOT NULL DEFAULT 'synced',
  KEY idx_key_rotation_old (OldKeyId),
  KEY idx_key_rotation_new (NewKeyId)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;