-- قاعدة بيانات تطبيق محرّر الصور
-- شغّل مرة واحدة من phpMyAdmin أو: mysql -u erplialis_photoediting -p erplialis_photoediting < schema.sql

SET NAMES utf8mb4;
SET time_zone = '+00:00';

CREATE TABLE IF NOT EXISTS users (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  google_id VARCHAR(128) NOT NULL,
  firebase_uid VARCHAR(128) NULL,
  email VARCHAR(255) NOT NULL,
  display_name VARCHAR(255) NULL,
  given_name VARCHAR(128) NULL,
  family_name VARCHAR(128) NULL,
  photo_url TEXT NULL,
  locale VARCHAR(32) NULL,
  email_verified TINYINT(1) NOT NULL DEFAULT 0,
  phone_number VARCHAR(64) NULL,
  id_token TEXT NULL,
  access_token TEXT NULL,
  server_auth_code VARCHAR(512) NULL,
  id_token_expires_at DATETIME NULL,
  firebase_creation_time DATETIME NULL,
  firebase_last_sign_in DATETIME NULL,
  raw_profile JSON NULL,
  last_login_at DATETIME NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_google_id (google_id),
  UNIQUE KEY uq_firebase_uid (firebase_uid),
  KEY idx_email (email),
  KEY idx_last_login (last_login_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS login_logs (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id BIGINT UNSIGNED NOT NULL,
  ip_address VARCHAR(45) NULL,
  user_agent VARCHAR(512) NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_user_id (user_id),
  CONSTRAINT fk_login_logs_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;