CREATE DATABASE IF NOT EXISTS video_engagement
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

USE video_engagement;

CREATE TABLE videos (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(255) NOT NULL,
  english_video_url TEXT NOT NULL,
  arabic_video_url TEXT NOT NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE video_events (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  video_id INT UNSIGNED NOT NULL,
  language ENUM('english','arabic') NOT NULL,
  played_minutes DECIMAL(10,2) NOT NULL DEFAULT 0,
  started_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  completed_at DATETIME NULL,
  doctor_id VARCHAR(100) NULL,
  doctor_name VARCHAR(255) NULL,
  country VARCHAR(100) NULL,
  organization_id VARCHAR(100) NULL,
  organization_name VARCHAR(255) NULL,
  ip_address VARCHAR(45) NULL,
  user_agent VARCHAR(500) NULL,
  referrer VARCHAR(1000) NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_video_id (video_id),
  INDEX idx_language (language),
  INDEX idx_started_at (started_at),
  INDEX idx_doctor_id (doctor_id),
  INDEX idx_country (country),
  INDEX idx_org_id (organization_id),
  CONSTRAINT fk_video_events_video
    FOREIGN KEY (video_id) REFERENCES videos(id)
    ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE admin_users (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(100) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO videos (title, english_video_url, arabic_video_url, is_active)
VALUES
('Sample Medical Video', 'https://example.com/english.mp4', 'https://example.com/arabic.mp4', 1);
