CREATE TABLE IF NOT EXISTS users (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 name VARCHAR(150) NOT NULL,
 email VARCHAR(190) NOT NULL UNIQUE,
 password_hash VARCHAR(255) NOT NULL,
 role VARCHAR(30) NOT NULL DEFAULT 'admin',
 is_active TINYINT(1) NOT NULL DEFAULT 1,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS settings (
 `key` VARCHAR(100) PRIMARY KEY,
 `value` TEXT NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS customers (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 name VARCHAR(170) NOT NULL,
 email VARCHAR(190) NOT NULL UNIQUE,
 phone VARCHAR(45) DEFAULT NULL,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS license_plans (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 name VARCHAR(120) NOT NULL,
 description VARCHAR(400) DEFAULT NULL,
 duration_months SMALLINT UNSIGNED NOT NULL,
 price DECIMAL(12,2) NOT NULL,
 currency VARCHAR(10) NOT NULL DEFAULT 'USD',
 is_active TINYINT(1) NOT NULL DEFAULT 1,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS installations (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 installation_code VARCHAR(100) NOT NULL UNIQUE,
 customer_id BIGINT UNSIGNED DEFAULT NULL,
 edition VARCHAR(100) NOT NULL DEFAULT 'Standard',
 app_version VARCHAR(60) DEFAULT NULL,
 trial_started_at DATETIME DEFAULT NULL,
 trial_ends_at DATETIME DEFAULT NULL,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 INDEX idx_install_customer (customer_id),
 CONSTRAINT fk_install_customer FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS licenses (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 installation_id BIGINT UNSIGNED NOT NULL UNIQUE,
 plan_id BIGINT UNSIGNED DEFAULT NULL,
 activation_key_hash CHAR(64) NOT NULL UNIQUE,
 activation_key_enc TEXT NOT NULL,
 status VARCHAR(24) NOT NULL DEFAULT 'active',
 valid_from DATETIME NOT NULL,
 valid_until DATETIME NOT NULL,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 CONSTRAINT fk_license_install FOREIGN KEY (installation_id) REFERENCES installations(id),
 CONSTRAINT fk_license_plan FOREIGN KEY (plan_id) REFERENCES license_plans(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS payment_methods (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 name VARCHAR(130) NOT NULL,
 type VARCHAR(50) NOT NULL DEFAULT 'bank',
 account_name VARCHAR(160) DEFAULT NULL,
 account_number VARCHAR(160) DEFAULT NULL,
 transfer_code VARCHAR(150) DEFAULT NULL,
 instructions TEXT DEFAULT NULL,
 logo_path VARCHAR(250) DEFAULT NULL,
 qr_path VARCHAR(250) DEFAULT NULL,
 is_active TINYINT(1) NOT NULL DEFAULT 1,
 sort_order INT NOT NULL DEFAULT 0,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS orders (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 order_number VARCHAR(35) NOT NULL UNIQUE,
 public_token CHAR(48) NOT NULL UNIQUE,
 customer_id BIGINT UNSIGNED NOT NULL,
 installation_id BIGINT UNSIGNED NOT NULL,
 plan_id BIGINT UNSIGNED NOT NULL,
 plan_name VARCHAR(120) NOT NULL,
 duration_months SMALLINT UNSIGNED NOT NULL,
 amount DECIMAL(12,2) NOT NULL,
 currency VARCHAR(10) NOT NULL,
 status VARCHAR(32) NOT NULL DEFAULT 'pending_payment',
 admin_notes TEXT DEFAULT NULL,
 approved_by BIGINT UNSIGNED DEFAULT NULL,
 approved_at DATETIME DEFAULT NULL,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 INDEX idx_orders_customer (customer_id),
 INDEX idx_orders_install (installation_id),
 INDEX idx_orders_status (status),
 CONSTRAINT fk_order_customer FOREIGN KEY (customer_id) REFERENCES customers(id),
 CONSTRAINT fk_order_install FOREIGN KEY (installation_id) REFERENCES installations(id),
 CONSTRAINT fk_order_plan FOREIGN KEY (plan_id) REFERENCES license_plans(id),
 CONSTRAINT fk_order_admin FOREIGN KEY (approved_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS payments (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 order_id BIGINT UNSIGNED NOT NULL,
 method_id BIGINT UNSIGNED NOT NULL,
 reference VARCHAR(160) DEFAULT NULL,
 proof_path VARCHAR(255) DEFAULT NULL,
 status VARCHAR(30) NOT NULL DEFAULT 'submitted',
 reviewed_by BIGINT UNSIGNED DEFAULT NULL,
 reviewed_at DATETIME DEFAULT NULL,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 INDEX idx_payments_order (order_id),
 CONSTRAINT fk_payment_order FOREIGN KEY (order_id) REFERENCES orders(id),
 CONSTRAINT fk_payment_method FOREIGN KEY (method_id) REFERENCES payment_methods(id),
 CONSTRAINT fk_payment_admin FOREIGN KEY (reviewed_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS software_releases (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 version VARCHAR(65) NOT NULL,
 notes TEXT DEFAULT NULL,
 filename VARCHAR(255) NOT NULL,
 stored_path VARCHAR(255) NOT NULL,
 sha256 CHAR(64) NOT NULL,
 filesize BIGINT UNSIGNED NOT NULL,
 is_active TINYINT(1) NOT NULL DEFAULT 1,
 downloads BIGINT UNSIGNED NOT NULL DEFAULT 0,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS login_tokens (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 customer_id BIGINT UNSIGNED NOT NULL,
 token_hash CHAR(64) NOT NULL UNIQUE,
 expires_at DATETIME NOT NULL,
 used_at DATETIME DEFAULT NULL,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 INDEX idx_login_exp (expires_at),
 CONSTRAINT fk_login_customer FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS license_events (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 license_id BIGINT UNSIGNED DEFAULT NULL,
 installation_id BIGINT UNSIGNED NOT NULL,
 user_id BIGINT UNSIGNED DEFAULT NULL,
 event_type VARCHAR(70) NOT NULL,
 details TEXT DEFAULT NULL,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 INDEX idx_event_install (installation_id),
 CONSTRAINT fk_event_license FOREIGN KEY (license_id) REFERENCES licenses(id) ON DELETE SET NULL,
 CONSTRAINT fk_event_install FOREIGN KEY (installation_id) REFERENCES installations(id),
 CONSTRAINT fk_event_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS audit_logs (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 admin_id BIGINT UNSIGNED DEFAULT NULL,
 action VARCHAR(120) NOT NULL,
 entity_type VARCHAR(60) DEFAULT NULL,
 entity_id BIGINT UNSIGNED DEFAULT NULL,
 ip VARCHAR(45) DEFAULT NULL,
 details TEXT DEFAULT NULL,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 INDEX idx_audit_admin (admin_id),
 CONSTRAINT fk_audit_admin FOREIGN KEY (admin_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS software_gallery (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 title VARCHAR(140) NOT NULL,
 caption VARCHAR(350) DEFAULT NULL,
 file_path VARCHAR(255) NOT NULL,
 sort_order INT NOT NULL DEFAULT 0,
 is_active TINYINT(1) NOT NULL DEFAULT 1,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 INDEX idx_gallery_display (is_active,sort_order,id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;