USE clubix;

CREATE TABLE IF NOT EXISTS user_tokens (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  organization_id BIGINT UNSIGNED NOT NULL,
  authenticatable_id BIGINT UNSIGNED NOT NULL,
  guard ENUM('internal', 'parent', 'member') NOT NULL DEFAULT 'internal',
  token_hash CHAR(64) NOT NULL,
  last_used_at DATETIME NULL,
  expires_at DATETIME NULL,
  revoked_at DATETIME NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_user_tokens_hash (token_hash),
  KEY idx_user_tokens_auth (guard, authenticatable_id),
  KEY idx_user_tokens_org (organization_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS push_devices (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  organization_id BIGINT UNSIGNED NOT NULL,
  guard ENUM('internal', 'parent', 'member') NOT NULL DEFAULT 'internal',
  authenticatable_id BIGINT UNSIGNED NOT NULL,
  user_id BIGINT UNSIGNED NULL,
  parent_account_id BIGINT UNSIGNED NULL,
  member_account_id BIGINT UNSIGNED NULL,
  platform ENUM('ios', 'android', 'web') NOT NULL DEFAULT 'web',
  provider ENUM('fcm', 'apns', 'webpush', 'unknown') NOT NULL DEFAULT 'unknown',
  device_uuid VARCHAR(190) NOT NULL,
  device_name VARCHAR(190) NULL,
  device_model VARCHAR(190) NULL,
  app_version VARCHAR(50) NULL,
  os_version VARCHAR(50) NULL,
  locale VARCHAR(20) NULL,
  push_token VARCHAR(255) NOT NULL,
  notifications_enabled TINYINT(1) NOT NULL DEFAULT 1,
  last_seen_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  last_registered_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  revoked_at DATETIME NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_push_devices_actor_device (organization_id, guard, authenticatable_id, device_uuid),
  KEY idx_push_devices_token (push_token),
  KEY idx_push_devices_actor (organization_id, guard, authenticatable_id),
  KEY idx_push_devices_active (organization_id, revoked_at, notifications_enabled),
  KEY idx_push_devices_platform (platform, provider)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS subscription_plans (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  code VARCHAR(80) NOT NULL,
  name VARCHAR(120) NOT NULL,
  description VARCHAR(255) NULL,
  max_members INT UNSIGNED NOT NULL DEFAULT 0,
  monthly_price DECIMAL(10, 2) NOT NULL DEFAULT 0.00,
  monthly_price_eur DECIMAL(10, 2) NULL DEFAULT NULL,
  currency_code CHAR(3) NOT NULL DEFAULT 'RSD',
  warning_threshold_percent TINYINT UNSIGNED NOT NULL DEFAULT 90,
  sort_order INT UNSIGNED NOT NULL DEFAULT 0,
  active TINYINT(1) NOT NULL DEFAULT 1,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  deleted_at TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_subscription_plans_code (code),
  KEY idx_subscription_plans_active (active, deleted_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS organization_subscriptions (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  organization_id BIGINT UNSIGNED NOT NULL,
  subscription_plan_id BIGINT UNSIGNED NOT NULL,
  status ENUM('trial', 'pending', 'active', 'overdue', 'paused', 'cancelled', 'complimentary') NOT NULL DEFAULT 'active',
  starts_at DATE NULL,
  ends_at DATE NULL,
  price_amount DECIMAL(10, 2) NOT NULL DEFAULT 0.00,
  currency_code CHAR(3) NOT NULL DEFAULT 'RSD',
  billing_mode ENUM('invoice_qr', 'online', 'complimentary') NOT NULL DEFAULT 'invoice_qr',
  trial_ends_at DATE NULL,
  next_billing_at DATE NULL,
  last_paid_at DATE NULL,
  billing_grace_days INT UNSIGNED NOT NULL DEFAULT 3,
  member_limit_override INT UNSIGNED NULL,
  note VARCHAR(255) NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  deleted_at TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (id),
  KEY idx_organization_subscriptions_org (organization_id, deleted_at),
  KEY idx_organization_subscriptions_plan (subscription_plan_id),
  CONSTRAINT fk_organization_subscriptions_org
    FOREIGN KEY (organization_id) REFERENCES organizations (id)
    ON UPDATE CASCADE ON DELETE CASCADE,
  CONSTRAINT fk_organization_subscriptions_plan
    FOREIGN KEY (subscription_plan_id) REFERENCES subscription_plans (id)
    ON UPDATE CASCADE ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS subscription_billing_entries (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  organization_id BIGINT UNSIGNED NOT NULL,
  organization_subscription_id BIGINT UNSIGNED NOT NULL,
  period_start DATE NOT NULL,
  period_end DATE NOT NULL,
  due_date DATE NOT NULL,
  amount DECIMAL(10, 2) NOT NULL DEFAULT 0.00,
  currency_code CHAR(3) NOT NULL DEFAULT 'RSD',
  status ENUM('pending', 'paid', 'overdue', 'cancelled', 'complimentary') NOT NULL DEFAULT 'pending',
  billing_mode ENUM('invoice_qr', 'online', 'complimentary') NOT NULL DEFAULT 'invoice_qr',
  reference_code VARCHAR(120) NOT NULL,
  paid_at DATE NULL,
  note VARCHAR(255) NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  deleted_at TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_subscription_billing_period (organization_subscription_id, due_date),
  UNIQUE KEY uq_subscription_billing_reference (reference_code),
  KEY idx_subscription_billing_org_status (organization_id, status, due_date),
  KEY idx_subscription_billing_due (due_date, status),
  CONSTRAINT fk_subscription_billing_entries_org
    FOREIGN KEY (organization_id) REFERENCES organizations (id)
    ON UPDATE CASCADE ON DELETE CASCADE,
  CONSTRAINT fk_subscription_billing_entries_subscription
    FOREIGN KEY (organization_subscription_id) REFERENCES organization_subscriptions (id)
    ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SET @sql = IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'subscription_plans' AND COLUMN_NAME = 'monthly_price_eur') = 0, 'ALTER TABLE subscription_plans ADD COLUMN monthly_price_eur DECIMAL(10,2) NULL DEFAULT NULL AFTER monthly_price', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql = IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'organization_subscriptions' AND COLUMN_NAME = 'billing_mode') = 0, 'ALTER TABLE organization_subscriptions ADD COLUMN billing_mode ENUM(''invoice_qr'', ''online'', ''complimentary'') NOT NULL DEFAULT ''invoice_qr'' AFTER currency_code', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
SET @sql = IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'organization_subscriptions' AND COLUMN_NAME = 'trial_ends_at') = 0, 'ALTER TABLE organization_subscriptions ADD COLUMN trial_ends_at DATE NULL AFTER billing_mode', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
SET @sql = IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'organization_subscriptions' AND COLUMN_NAME = 'next_billing_at') = 0, 'ALTER TABLE organization_subscriptions ADD COLUMN next_billing_at DATE NULL AFTER trial_ends_at', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
SET @sql = IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'organization_subscriptions' AND COLUMN_NAME = 'last_paid_at') = 0, 'ALTER TABLE organization_subscriptions ADD COLUMN last_paid_at DATE NULL AFTER next_billing_at', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
SET @sql = IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'organization_subscriptions' AND COLUMN_NAME = 'billing_grace_days') = 0, 'ALTER TABLE organization_subscriptions ADD COLUMN billing_grace_days INT UNSIGNED NOT NULL DEFAULT 3 AFTER last_paid_at', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
SET @sql = IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'organization_subscriptions' AND COLUMN_NAME = 'member_limit_override') = 0, 'ALTER TABLE organization_subscriptions ADD COLUMN member_limit_override INT UNSIGNED NULL AFTER billing_grace_days', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
ALTER TABLE organization_subscriptions
  MODIFY COLUMN status ENUM('trial', 'pending', 'active', 'overdue', 'paused', 'cancelled', 'complimentary') NOT NULL DEFAULT 'active';

ALTER TABLE subscription_billing_entries
  MODIFY COLUMN status ENUM('pending', 'paid', 'overdue', 'cancelled', 'complimentary') NOT NULL DEFAULT 'pending',
  MODIFY COLUMN billing_mode ENUM('invoice_qr', 'online', 'complimentary') NOT NULL DEFAULT 'invoice_qr';

INSERT INTO subscription_plans (code, name, description, max_members, monthly_price, currency_code, warning_threshold_percent, sort_order, active)
SELECT * FROM (
  SELECT 'starter' AS code, 'Paket 1' AS name, 'Osnovni paket za manje organizacije.' AS description, 50 AS max_members, 0.00 AS monthly_price, 'RSD' AS currency_code, 90 AS warning_threshold_percent, 1 AS sort_order, 1 AS active
  UNION ALL
  SELECT 'growth', 'Paket 2', 'Srednji paket za rast broja članova.', 150, 0.00, 'RSD', 90, 2, 1
  UNION ALL
  SELECT 'scale', 'Paket 3', 'Najveći paket za organizacije sa velikim brojem članova.', 500, 0.00, 'RSD', 90, 3, 1
) seeded
WHERE NOT EXISTS (SELECT 1 FROM subscription_plans existing WHERE existing.code = seeded.code);

UPDATE subscription_plans
SET name = 'Starter',
    description = 'Za male klubove koji tek kreću.',
    monthly_price = 2000.00,
    monthly_price_eur = 15.00,
    currency_code = 'RSD',
    sort_order = 1,
    active = 1
WHERE code = 'starter';

UPDATE subscription_plans
SET name = 'Klub',
    description = 'Za aktivne klubove sa svim funkcijama.',
    monthly_price = 6000.00,
    monthly_price_eur = 50.00,
    currency_code = 'RSD',
    sort_order = 2,
    active = 1
WHERE code = 'growth';

UPDATE subscription_plans
SET name = 'Pro',
    description = 'Za veće organizacije bez kompromisa.',
    monthly_price = 8000.00,
    monthly_price_eur = 70.00,
    currency_code = 'RSD',
    sort_order = 3,
    active = 1
WHERE code = 'scale';

ALTER TABLE user_tokens MODIFY guard ENUM('internal', 'parent', 'member') NOT NULL DEFAULT 'internal';
SET @sql = IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'user_tokens' AND COLUMN_NAME = 'last_used_at') = 0, 'ALTER TABLE user_tokens ADD COLUMN last_used_at DATETIME NULL AFTER token_hash', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
SET @sql = IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'user_tokens' AND COLUMN_NAME = 'expires_at') = 0, 'ALTER TABLE user_tokens ADD COLUMN expires_at DATETIME NULL AFTER last_used_at', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

CREATE TABLE IF NOT EXISTS login_attempts (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  ip VARCHAR(64) NOT NULL,
  guard ENUM('internal', 'parent', 'member') NOT NULL DEFAULT 'internal',
  attempted_email VARCHAR(190) NULL,
  attempted_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  success TINYINT(1) NOT NULL DEFAULT 0,
  PRIMARY KEY (id),
  KEY idx_login_attempts_ip_guard (ip, guard, attempted_at),
  KEY idx_login_attempts_success (success, attempted_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS email_log (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  organization_id BIGINT UNSIGNED NOT NULL,
  recipient_email VARCHAR(190) NOT NULL,
  template VARCHAR(100) NOT NULL,
  subject VARCHAR(190) NOT NULL,
  status ENUM('sent', 'failed') NOT NULL DEFAULT 'sent',
  sent_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  error_message VARCHAR(255) NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_email_log_org_sent (organization_id, sent_at),
  KEY idx_email_log_org_status (organization_id, status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS membership_reminders (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  organization_id BIGINT UNSIGNED NOT NULL,
  membership_fee_id BIGINT UNSIGNED NOT NULL,
  reminder_type ENUM('overdue', 'upcoming') NOT NULL DEFAULT 'overdue',
  recipient_email VARCHAR(190) NOT NULL,
  sent_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_membership_reminders_unique (organization_id, membership_fee_id, reminder_type, recipient_email),
  KEY idx_membership_reminders_sent (organization_id, reminder_type, sent_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS cron_log (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  task VARCHAR(120) NOT NULL,
  started_at DATETIME NOT NULL,
  finished_at DATETIME NULL,
  status ENUM('running', 'success', 'failed') NOT NULL DEFAULT 'running',
  message VARCHAR(255) NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_cron_log_task_started (task, started_at),
  KEY idx_cron_log_status (status, started_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS member_accounts (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  organization_id BIGINT UNSIGNED NOT NULL,
  member_id BIGINT UNSIGNED NOT NULL,
  email VARCHAR(190) NOT NULL,
  password_hash VARCHAR(255) NOT NULL,
  last_login_at DATETIME NULL,
  status ENUM('pending', 'active', 'inactive', 'locked') NOT NULL DEFAULT 'pending',
  email_verified_at DATETIME NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  deleted_at TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_member_accounts_org_member (organization_id, member_id),
  UNIQUE KEY uq_member_accounts_org_email (organization_id, email),
  KEY idx_member_accounts_status (organization_id, status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SET @sql = IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'organizations' AND COLUMN_NAME = 'type') = 0, 'ALTER TABLE organizations ADD COLUMN type ENUM(''kud'', ''sports_club'', ''music_school'', ''dance_school'', ''other'') NOT NULL DEFAULT ''other'' AFTER name', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
SET @sql = IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'organizations' AND COLUMN_NAME = 'pib') = 0, 'ALTER TABLE organizations ADD COLUMN pib VARCHAR(30) NULL AFTER phone', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
SET @sql = IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'organizations' AND COLUMN_NAME = 'mb') = 0, 'ALTER TABLE organizations ADD COLUMN mb VARCHAR(30) NULL AFTER pib', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
SET @sql = IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'organizations' AND COLUMN_NAME = 'logo') = 0, 'ALTER TABLE organizations ADD COLUMN logo VARCHAR(255) NULL AFTER mb', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
SET @sql = IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'organizations' AND COLUMN_NAME = 'active') = 0, 'ALTER TABLE organizations ADD COLUMN active TINYINT(1) NOT NULL DEFAULT 1 AFTER logo', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
SET @sql = IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'organizations' AND COLUMN_NAME = 'contact_person') = 0, 'ALTER TABLE organizations ADD COLUMN contact_person VARCHAR(150) NULL AFTER phone', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
SET @sql = IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'organizations' AND COLUMN_NAME = 'contact_role') = 0, 'ALTER TABLE organizations ADD COLUMN contact_role VARCHAR(120) NULL AFTER contact_person', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
SET @sql = IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'organizations' AND COLUMN_NAME = 'contact_email') = 0, 'ALTER TABLE organizations ADD COLUMN contact_email VARCHAR(190) NULL AFTER contact_role', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
SET @sql = IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'organizations' AND COLUMN_NAME = 'contact_phone') = 0, 'ALTER TABLE organizations ADD COLUMN contact_phone VARCHAR(50) NULL AFTER contact_email', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
SET @sql = IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'organizations' AND COLUMN_NAME = 'note') = 0, 'ALTER TABLE organizations ADD COLUMN note TEXT NULL AFTER active', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql = IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'users' AND COLUMN_NAME = 'password_reset_token') = 0, 'ALTER TABLE users ADD COLUMN password_reset_token VARCHAR(190) NULL AFTER status', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
SET @sql = IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'users' AND COLUMN_NAME = 'password_reset_expires_at') = 0, 'ALTER TABLE users ADD COLUMN password_reset_expires_at DATETIME NULL AFTER password_reset_token', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
SET @sql = IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'users' AND COLUMN_NAME = 'password_reset_sent_at') = 0, 'ALTER TABLE users ADD COLUMN password_reset_sent_at DATETIME NULL AFTER password_reset_expires_at', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
SET @sql = IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'member_accounts' AND COLUMN_NAME = 'password_reset_token') = 0, 'ALTER TABLE member_accounts ADD COLUMN password_reset_token VARCHAR(190) NULL AFTER status', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
SET @sql = IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'member_accounts' AND COLUMN_NAME = 'password_reset_expires_at') = 0, 'ALTER TABLE member_accounts ADD COLUMN password_reset_expires_at DATETIME NULL AFTER password_reset_token', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
SET @sql = IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'member_accounts' AND COLUMN_NAME = 'password_reset_sent_at') = 0, 'ALTER TABLE member_accounts ADD COLUMN password_reset_sent_at DATETIME NULL AFTER password_reset_expires_at', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
SET @sql = IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'parent_accounts' AND COLUMN_NAME = 'password_reset_token') = 0, 'ALTER TABLE parent_accounts ADD COLUMN password_reset_token VARCHAR(190) NULL AFTER status', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
SET @sql = IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'parent_accounts' AND COLUMN_NAME = 'password_reset_expires_at') = 0, 'ALTER TABLE parent_accounts ADD COLUMN password_reset_expires_at DATETIME NULL AFTER password_reset_token', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
SET @sql = IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'parent_accounts' AND COLUMN_NAME = 'password_reset_sent_at') = 0, 'ALTER TABLE parent_accounts ADD COLUMN password_reset_sent_at DATETIME NULL AFTER password_reset_expires_at', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql = IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'organization_settings' AND COLUMN_NAME = 'bank_account') = 0, 'ALTER TABLE organization_settings ADD COLUMN bank_account VARCHAR(80) NULL AFTER app_name', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
SET @sql = IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'organization_settings' AND COLUMN_NAME = 'payment_model') = 0, 'ALTER TABLE organization_settings ADD COLUMN payment_model VARCHAR(20) NULL AFTER bank_account', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
SET @sql = IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'organization_settings' AND COLUMN_NAME = 'payment_reference_template') = 0, 'ALTER TABLE organization_settings ADD COLUMN payment_reference_template VARCHAR(190) NULL AFTER payment_model', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
SET @sql = IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'organization_settings' AND COLUMN_NAME = 'payment_recipient_name') = 0, 'ALTER TABLE organization_settings ADD COLUMN payment_recipient_name VARCHAR(190) NULL AFTER payment_reference_template', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
SET @sql = IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'organization_settings' AND COLUMN_NAME = 'default_payment_purpose') = 0, 'ALTER TABLE organization_settings ADD COLUMN default_payment_purpose VARCHAR(190) NULL AFTER payment_recipient_name', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
SET @sql = IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'organization_settings' AND COLUMN_NAME = 'default_membership_fee') = 0, 'ALTER TABLE organization_settings ADD COLUMN default_membership_fee DECIMAL(10,2) NOT NULL DEFAULT 0.00 AFTER payment_recipient_name', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
SET @sql = IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'organization_settings' AND COLUMN_NAME = 'opening_balance_amount') = 0, 'ALTER TABLE organization_settings ADD COLUMN opening_balance_amount DECIMAL(12,2) NOT NULL DEFAULT 0.00 AFTER default_membership_fee', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
SET @sql = IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'organization_settings' AND COLUMN_NAME = 'opening_balance_date') = 0, 'ALTER TABLE organization_settings ADD COLUMN opening_balance_date DATE NULL AFTER opening_balance_amount', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
SET @sql = IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'organization_settings' AND COLUMN_NAME = 'opening_balance_note') = 0, 'ALTER TABLE organization_settings ADD COLUMN opening_balance_note VARCHAR(255) NULL AFTER opening_balance_date', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
SET @sql = IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'organization_settings' AND COLUMN_NAME = 'membership_due_day') = 0, 'ALTER TABLE organization_settings ADD COLUMN membership_due_day TINYINT UNSIGNED NOT NULL DEFAULT 10 AFTER opening_balance_note', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
SET @sql = IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'organization_settings' AND COLUMN_NAME = 'notify_after_days') = 0, 'ALTER TABLE organization_settings ADD COLUMN notify_after_days INT UNSIGNED NOT NULL DEFAULT 7 AFTER membership_due_day', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
SET @sql = IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'organization_settings' AND COLUMN_NAME = 'enable_parent_portal') = 0, 'ALTER TABLE organization_settings ADD COLUMN enable_parent_portal TINYINT(1) NOT NULL DEFAULT 1 AFTER notify_after_days', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
SET @sql = IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'organization_settings' AND COLUMN_NAME = 'enable_attendance_module') = 0, 'ALTER TABLE organization_settings ADD COLUMN enable_attendance_module TINYINT(1) NOT NULL DEFAULT 1 AFTER enable_parent_portal', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
SET @sql = IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'organization_settings' AND COLUMN_NAME = 'enable_equipment_module') = 0, 'ALTER TABLE organization_settings ADD COLUMN enable_equipment_module TINYINT(1) NOT NULL DEFAULT 1 AFTER enable_attendance_module', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
SET @sql = IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'organization_settings' AND COLUMN_NAME = 'messaging_enabled') = 0, 'ALTER TABLE organization_settings ADD COLUMN messaging_enabled TINYINT(1) NOT NULL DEFAULT 0 AFTER enable_equipment_module', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
SET @sql = IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'organization_settings' AND COLUMN_NAME = 'messaging_channels') = 0, 'ALTER TABLE organization_settings ADD COLUMN messaging_channels VARCHAR(190) NULL AFTER messaging_enabled', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
SET @sql = IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'organization_settings' AND COLUMN_NAME = 'reminders_enabled') = 0, 'ALTER TABLE organization_settings ADD COLUMN reminders_enabled TINYINT(1) NOT NULL DEFAULT 1 AFTER messaging_channels', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
SET @sql = IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'organization_settings' AND COLUMN_NAME = 'reminder_days_overdue') = 0, 'ALTER TABLE organization_settings ADD COLUMN reminder_days_overdue INT UNSIGNED NOT NULL DEFAULT 3 AFTER reminders_enabled', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
SET @sql = IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'organization_settings' AND COLUMN_NAME = 'reminder_days_before') = 0, 'ALTER TABLE organization_settings ADD COLUMN reminder_days_before INT UNSIGNED NOT NULL DEFAULT 3 AFTER reminder_days_overdue', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

ALTER TABLE notification_templates MODIFY channel ENUM('email', 'sms', 'push', 'in_app', 'viber', 'whatsapp') NOT NULL DEFAULT 'email';
ALTER TABLE notification_rules MODIFY channel ENUM('email', 'sms', 'push', 'in_app', 'viber', 'whatsapp') NOT NULL DEFAULT 'email';
ALTER TABLE notifications MODIFY channel ENUM('email', 'sms', 'push', 'in_app', 'viber', 'whatsapp') NOT NULL DEFAULT 'email';
SET @sql = IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'notifications' AND COLUMN_NAME = 'read_at') = 0, 'ALTER TABLE notifications ADD COLUMN read_at DATETIME NULL AFTER sent_at', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql = IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'locations' AND COLUMN_NAME = 'address') = 0, 'ALTER TABLE locations ADD COLUMN address VARCHAR(190) NULL AFTER name', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
SET @sql = IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'locations' AND COLUMN_NAME = 'note') = 0, 'ALTER TABLE locations ADD COLUMN note TEXT NULL AFTER phone', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
SET @sql = IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'locations' AND COLUMN_NAME = 'active') = 0, 'ALTER TABLE locations ADD COLUMN active TINYINT(1) NOT NULL DEFAULT 1 AFTER note', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql = IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'members' AND COLUMN_NAME = 'birth_date') = 0, 'ALTER TABLE members ADD COLUMN birth_date DATE NULL AFTER last_name', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
SET @sql = IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'members' AND COLUMN_NAME = 'address') = 0, 'ALTER TABLE members ADD COLUMN address VARCHAR(190) NULL AFTER email', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
SET @sql = IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'members' AND COLUMN_NAME = 'note') = 0, 'ALTER TABLE members ADD COLUMN note TEXT NULL AFTER joined_at', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
SET @sql = IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'members' AND COLUMN_NAME = 'membership_fee') = 0, 'ALTER TABLE members ADD COLUMN membership_fee DECIMAL(10,2) NULL AFTER status', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql = IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'parents' AND COLUMN_NAME = 'address') = 0, 'ALTER TABLE parents ADD COLUMN address VARCHAR(190) NULL AFTER email', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
SET @sql = IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'parents' AND COLUMN_NAME = 'note') = 0, 'ALTER TABLE parents ADD COLUMN note TEXT NULL AFTER address', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql = IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'groups' AND COLUMN_NAME = 'description') = 0, 'ALTER TABLE `groups` ADD COLUMN description TEXT NULL AFTER name', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
SET @sql = IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'groups' AND COLUMN_NAME = 'default_membership_fee') = 0, 'ALTER TABLE `groups` ADD COLUMN default_membership_fee DECIMAL(10,2) NULL AFTER description', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
SET @sql = IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'groups' AND COLUMN_NAME = 'active') = 0, 'ALTER TABLE `groups` ADD COLUMN active TINYINT(1) NOT NULL DEFAULT 1 AFTER default_membership_fee', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql = IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'member_parents' AND COLUMN_NAME = 'relation') = 0, 'ALTER TABLE member_parents ADD COLUMN relation ENUM(''mother'', ''father'', ''guardian'', ''other'') NOT NULL DEFAULT ''other'' AFTER parent_id', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
SET @sql = IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'member_parents' AND COLUMN_NAME = 'is_primary') = 0, 'ALTER TABLE member_parents ADD COLUMN is_primary TINYINT(1) NOT NULL DEFAULT 0 AFTER relation', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql = IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'instructors' AND COLUMN_NAME = 'note') = 0, 'ALTER TABLE instructors ADD COLUMN note TEXT NULL AFTER phone', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
SET @sql = IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'instructors' AND COLUMN_NAME = 'active') = 0, 'ALTER TABLE instructors ADD COLUMN active TINYINT(1) NOT NULL DEFAULT 1 AFTER note', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql = IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'group_members' AND COLUMN_NAME = 'active') = 0, 'ALTER TABLE group_members ADD COLUMN active TINYINT(1) NOT NULL DEFAULT 1 AFTER left_at', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
SET @sql = IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'group_members' AND COLUMN_NAME = 'is_primary') = 0, 'ALTER TABLE group_members ADD COLUMN is_primary TINYINT(1) NOT NULL DEFAULT 0 AFTER active', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

UPDATE group_members gm
JOIN (
  SELECT organization_id, member_id, MIN(id) AS primary_row_id
  FROM group_members
  WHERE active = 1 OR status = 'active'
  GROUP BY organization_id, member_id
) first_group
  ON first_group.primary_row_id = gm.id
SET gm.is_primary = 1
WHERE gm.is_primary = 0;

CREATE TABLE IF NOT EXISTS attendance_sessions (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  organization_id BIGINT UNSIGNED NOT NULL,
  group_id BIGINT UNSIGNED NOT NULL,
  instructor_id BIGINT UNSIGNED NULL,
  session_date DATE NOT NULL,
  start_time TIME NULL,
  notes VARCHAR(500) NULL,
  created_by BIGINT UNSIGNED NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_attendance_sessions_org_group_date (organization_id, group_id, session_date),
  UNIQUE KEY uq_attendance_sessions_org_id (organization_id, id),
  KEY idx_attendance_sessions_org_date (organization_id, session_date),
  KEY idx_attendance_sessions_instructor (organization_id, instructor_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SET @sql = IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'attendance_sessions' AND INDEX_NAME = 'uq_attendance_sessions_org_id') = 0, 'ALTER TABLE attendance_sessions ADD UNIQUE KEY uq_attendance_sessions_org_id (organization_id, id)', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql = IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'attendance_sessions' AND CONSTRAINT_NAME = 'fk_attendance_sessions_organization') = 0, 'ALTER TABLE attendance_sessions ADD CONSTRAINT fk_attendance_sessions_organization FOREIGN KEY (organization_id) REFERENCES organizations (id) ON UPDATE CASCADE ON DELETE CASCADE', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
SET @sql = IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'attendance_sessions' AND CONSTRAINT_NAME = 'fk_attendance_sessions_group') = 0, 'ALTER TABLE attendance_sessions ADD CONSTRAINT fk_attendance_sessions_group FOREIGN KEY (organization_id, group_id) REFERENCES `groups` (organization_id, id) ON UPDATE CASCADE ON DELETE CASCADE', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
SET @sql = IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'attendance_sessions' AND CONSTRAINT_NAME = 'fk_attendance_sessions_instructor') = 0, 'ALTER TABLE attendance_sessions ADD CONSTRAINT fk_attendance_sessions_instructor FOREIGN KEY (organization_id, instructor_id) REFERENCES instructors (organization_id, id) ON UPDATE CASCADE ON DELETE RESTRICT', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
SET @sql = IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'attendance_sessions' AND CONSTRAINT_NAME = 'fk_attendance_sessions_created_by') = 0, 'ALTER TABLE attendance_sessions ADD CONSTRAINT fk_attendance_sessions_created_by FOREIGN KEY (organization_id, created_by) REFERENCES users (organization_id, id) ON UPDATE CASCADE ON DELETE RESTRICT', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

CREATE TABLE IF NOT EXISTS attendance_records (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  organization_id BIGINT UNSIGNED NOT NULL,
  session_id BIGINT UNSIGNED NOT NULL,
  member_id BIGINT UNSIGNED NOT NULL,
  status ENUM('present', 'absent', 'late', 'excused') NOT NULL DEFAULT 'absent',
  note VARCHAR(300) NULL,
  marked_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_attendance_records_org_session_member (organization_id, session_id, member_id),
  KEY idx_attendance_records_member (organization_id, member_id),
  KEY idx_attendance_records_status (organization_id, status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SET @sql = IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'attendance_records' AND CONSTRAINT_NAME = 'fk_attendance_records_organization') = 0, 'ALTER TABLE attendance_records ADD CONSTRAINT fk_attendance_records_organization FOREIGN KEY (organization_id) REFERENCES organizations (id) ON UPDATE CASCADE ON DELETE CASCADE', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
SET @sql = IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'attendance_records' AND CONSTRAINT_NAME = 'fk_attendance_records_session') = 0, 'ALTER TABLE attendance_records ADD CONSTRAINT fk_attendance_records_session FOREIGN KEY (organization_id, session_id) REFERENCES attendance_sessions (organization_id, id) ON UPDATE CASCADE ON DELETE CASCADE', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
SET @sql = IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'attendance_records' AND CONSTRAINT_NAME = 'fk_attendance_records_member') = 0, 'ALTER TABLE attendance_records ADD CONSTRAINT fk_attendance_records_member FOREIGN KEY (organization_id, member_id) REFERENCES members (organization_id, id) ON UPDATE CASCADE ON DELETE CASCADE', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql = IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'equipment' AND COLUMN_NAME = 'code') = 0, 'ALTER TABLE equipment ADD COLUMN code VARCHAR(100) NULL AFTER size', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
SET @sql = IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'equipment' AND COLUMN_NAME = 'description') = 0, 'ALTER TABLE equipment ADD COLUMN description TEXT NULL AFTER code', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
SET @sql = IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'equipment' AND COLUMN_NAME = 'total_quantity') = 0, 'ALTER TABLE equipment ADD COLUMN total_quantity INT UNSIGNED NOT NULL DEFAULT 1 AFTER description', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
SET @sql = IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'equipment' AND COLUMN_NAME = 'active') = 0, 'ALTER TABLE equipment ADD COLUMN active TINYINT(1) NOT NULL DEFAULT 1 AFTER total_quantity', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql = IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'equipment_assignments' AND COLUMN_NAME = 'deposit_amount') = 0, 'ALTER TABLE equipment_assignments ADD COLUMN deposit_amount DECIMAL(10,2) NOT NULL DEFAULT 0.00 AFTER condition_on_return', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql = IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'expenses' AND COLUMN_NAME = 'document_file') = 0, 'ALTER TABLE expenses ADD COLUMN document_file VARCHAR(255) NULL AFTER description', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql = IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'notifications' AND COLUMN_NAME = 'membership_fee_id') = 0, 'ALTER TABLE notifications ADD COLUMN membership_fee_id BIGINT UNSIGNED NULL AFTER member_id', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

CREATE TABLE IF NOT EXISTS parent_messages (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  organization_id BIGINT UNSIGNED NOT NULL,
  parent_account_id BIGINT UNSIGNED NOT NULL,
  parent_id BIGINT UNSIGNED NOT NULL,
  member_id BIGINT UNSIGNED NULL,
  subject VARCHAR(190) NULL,
  body TEXT NOT NULL,
  status ENUM('new', 'read', 'archived') NOT NULL DEFAULT 'new',
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  deleted_at TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (id),
  KEY idx_parent_messages_parent (organization_id, parent_account_id, created_at),
  KEY idx_parent_messages_member (organization_id, member_id),
  KEY idx_parent_messages_status (organization_id, status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;